Redshift

Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL

Amazon Redshift is a data warehousing service provided by Amazon Web Services (AWS). It allows users to store and analyze large amounts of data in a scalable and cost-effective manner. Amazon AWS Redshift RSQL is a command-line client for interacting with Amazon Redshift clusters and databases. Redshift RSQL is similar to Teradata BTEQ and is used to interact with the data stored in a Redshift cluster. In this article, we will check Amazon Redshift RSQL control statements such as IF, ELSE, GOT, LABEL,etc. Redshift RSQL Control Statements Amazon Redshift RSQL…

Comments Off on Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL
January 27, 2023
Snowflake

How to Create Synonym in Snowflake?

Synonyms in relational databases allows you to create easily names for long table, view names, or any other objects such as sequence, procedure, function, materialized view. Databases such as Netezza, Oracle support creating and managing synonyms. The synonyms provide an alternate way of referencing tables or views that present in the current or other databases. The Snowflake database does not support creating synonym yet. In this article, we will check an alternate method similar to create synonym in Snowflake. How to Create Synonym in Snowflake? The Snowflake cloud database does…

Comments Off on How to Create Synonym in Snowflake?
December 5, 2022
Apache Spark

Spark SQL Count Distinct Window Function

Windows are commonly used analytical functions in a Spark SQL query. The COUNT is one of such a windows functions that will allow you to count over certain window. Many relational databases such as Oracle support COUNT window function with distinct keyword. However, Spark SQL does not support count distinct window function. Spark SQL Count Distinct Window Function Spark SQL supports count window functions. However, the COUNT window function with distinct keyword is not supported as of now. Following is the example of an Oracle COUNT window function with distinct…

Comments Off on Spark SQL Count Distinct Window Function
November 29, 2022
Snowflake

Rows Affected by Last Snowflake SQL Query – Example

Need to find out how many rows were inserted, updated, or deleted after running a query in Snowflake? Just like other major databases, Snowflake gives you a direct way to track these changes using built-in system variables. Let's look at how you can easily get the rows affected by your last Snowflake SQL query to build better scripts and workflows. Rows Affected by Last Snowflake DML Statement Page Content Introduction Rows Affected by Last Snowflake SQL Query Example: Getting Rows Affected by an INSERT Query Example: Getting Rows Affected by…

Comments Off on Rows Affected by Last Snowflake SQL Query – Example
November 16, 2022
Snowflake

Blocks in Snowflake Scripting: Complete Guide with Examples

If you are writing procedural code in Snowflake like loops, branching logic, or error handling you will use blocks in Snowflake scripting. A block is the fundamental structure that groups your SQL and programmatic logic together. In this guide, we will break down exactly how to structure a block, the difference between stored procedures and anonymous blocks, and look at fully runnable examples you can use right away. Blocks in Snowflake Scripting Page Contents Blocks in Snowflake Scripting Types of Blocks in Snowflake Scripting Block in a Stored Procedure Anonymous…

Comments Off on Blocks in Snowflake Scripting: Complete Guide with Examples
November 9, 2022
Snowflake

Snowflake Scripting Cursor Syntax and Examples

In my other article, we have discussed Snowflake scripting control structures and loops to manipulate Snowflake data. In this article, let us discuss how to define and use the Snowflake scripting cursor within a stored procedure and anonymous block. Snowflake also support JavaScript to write stored procedures, but it does not support cursor when JavaScript used. You can use the JavaScript result as a cursor alternative. Page Content Introduction What is Cursor in Snowflake Scripting? How use Cursors in Snowflake Scripting? Declaring Cursors Opening Cursors Fetching Data from Cursors Closing…

2 Comments
October 4, 2022
Snowflake

Snowflake Scripting Control Structures – IF, WHILE, FOR, REPEAT, LOOP

In my other Snowflake article, we have checked how JavaScript control structure works in Snowflake JavaScript stored procedures. Snowflake Scripting makes it incredibly easy to manage conditional logic and iterations directly within your stored procedures and anonymous blocks. By mastering Snowflake scripting control structures like IF, WHILE, FOR, REPEAT, and LOOP, you can seamlessly migrate existing PL/SQL logic and build robust data pipelines. Let's dive into how these branching and looping structures work with simple, real-world examples. Snowflake Scripting Control Structures Page Content Introduction Snowflake Scripting Control Structures Branching Structures:…

2 Comments
September 21, 2022
GCP BigQuery

Google BigQuery GROUP BY CUBE: Native Example and Alternative

If you are looking to perform multi-dimensional data analysis, the Google BigQuery GROUP BY CUBE function is exactly what you need. While developers previously had to rely on a complex UNION ALL alternative, BigQuery now natively supports this advanced aggregation feature. In this post, we will look at how to use the new native CUBE function and also review the legacy alternative for older codebases. Google BigQuery GROUP BY CUBE Page Content Introduction SQL GROUP BY CUBE in Google BigQuery GROUP BY CUBE BigQuery Alternative Using UNION ALL Conclusion Introduction…

Comments Off on Google BigQuery GROUP BY CUBE: Native Example and Alternative
September 15, 2022
GCP BigQuery

Google BigQuery GROUPING SETS: Native Example and Alternative

If you are looking to perform multi-dimensional data analysis, the Google BigQuery GROUPING SETS function is exactly what you need. While developers previously had to rely on a complex UNION ALL workaround, BigQuery now natively supports this advanced aggregation feature. In this post, we will look at how to use the new native GROUPING SETS function and also review the legacy alternative for older codebases. Google BigQuery Grouping Sets Page Content Introduction SQL GROUPING SETS in Google BigQuery GROUPING SETS BigQuery Alternative Using UNION ALL Conclusion Introduction In my other…

Comments Off on Google BigQuery GROUPING SETS: Native Example and Alternative
September 15, 2022
Redshift

Amazon Redshift Delete with Join Syntax and Examples

Data is an integral part of decision making system. Many application reply on a data to make business decisions. In the relational database world, data is stored in the form of tables. Today, we have unlimited storage, but cost is also high to manage storage. Data is collected over the time and it may or may not be accurate. You may have to clean the data by deleting unwanted records or purge the old data from a system that is outdated. In this article, we will discuss Amazon Redshift delete…

Comments Off on Amazon Redshift Delete with Join Syntax and Examples
July 6, 2022