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…

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

Rows Affected by Last Snowflake SQL Query – Example

Many modern databases such as Teradata, SQL Server, etc. support system variables to get rows affected by the last DML command. Then the row count is used to make certain decision to continue execution or abort it. Snowflake scripting provides system variables that hold the information of records affected by an INSERT, UPDATE or DELETE queries in the current session. In this article, we will check how to get rows affected by last Snowflake SQL Query. Rows Affected by Last Snowflake DML Statement Rows Affected by Last Snowflake SQL Query After each DML command is…

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

Blocks in Snowflake Scripting – Examples

Snowflake scripting allows you to write procedural code within the Snowflake scripting block. In this article, we will check how to use blocks in a Snowflake scripting. Blocks in Snowflake Scripting Blocks in Snowflake Scripting Block in a Snowflake scripting is defined by a keywords such as DECLARE, BEGIN, EXCEPTION and END. You usually write your procedural code into different part. Structure of Block in a Snowflake Scripting Snowflake scripting block has the following basic structure: DECLARE ... variable declarations, ... ... cursor declarations, etc ... BEGIN ... Snowflake Scripting…

Continue ReadingBlocks in Snowflake Scripting – Examples
Comments Off on Blocks in Snowflake Scripting – 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 Understanding Cursors in Snowflake How use Cursors in Snowflake Scripting? Declaring Cursors Opening Cursors Fetching Data from Cursors Closing Cursors Snowflake…

Continue ReadingSnowflake Scripting Cursor Syntax and Examples
2 Comments
October 4, 2022
General

DBT – Export Snowflake Table to S3 Bucket

dbt stands for data build tool is a data transformation tool that enables data analysts and engineers to transform data in a cloud analytics warehouse. dbt basically focuses on the Transformation part in ELT (Extract, Load, Transform) processes. It supports cloud data warehouses such as Snowflake, Redshift, etc. In this article, we will check how to export your Snowflake table to S3 bucket from dbt. DBT - Export Snowflake Table to S3 Bucket What is dbt? Before going into details on exporting Snowflake table to S3 bucket using DBT, let us…

Continue ReadingDBT – Export Snowflake Table to S3 Bucket
Comments Off on DBT – Export Snowflake Table to S3 Bucket
September 30, 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. In this article, we will check Snowflake scripting control structures such as IF, WHILE, FOR, REPEAT and LOOP. Snowflake Scripting Control Structures Snowflake Scripting Control Structures The best part about Snowflake is it supports both SQL and JavaScript as a programming language to write stored procedures and user defined functions. Till now, it was supported only JavaScript to write stored procedures, but now they started supporting Snowflake script much similar to PL/SQL…

Continue ReadingSnowflake Scripting Control Structures – IF, WHILE, FOR, REPEAT, LOOP
2 Comments
September 21, 2022
GCP BigQuery

Google BigQuery GROUP BY CUBE Alternative and Example

In my other BigQuery related articles, we have seen BigQuery grouping sets alternative, BigQuery control flow statements, NVL and NVL2 alternatives in BigQuery, cursors in BigQuery, etc. In this article, we will check one of the important GROUP BY extensions GROUP BY CUBE alternative in Google BigQuery. Google BigQuery GROUP BY CUBE SQL GROUP BY CUBE in Google BigQuery GROUP BY CUBE is an extension of the GROUP BY clause similar to GROUP BY ROLLUP and GROUPING SETS which is used to analyzes data by grouping it into multiple dimensions. In addition to…

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

Google BigQuery Grouping Sets Alternative and Example

In my other BigQuery related articles, we have seen BigQuery control flow statements, NVL and NVL2 alternatives in BigQuery, cursors in BigQuery, etc. In this article, we will check one of the important GROUP BY extensions GROUPING SETS alternative in Google BigQuery. Google BigQuery Grouping Sets SQL GROUPING SETS in Google BigQuery Many modern day analytics databases support GROUPING SETS. The GROUPING SETS option in SQL gives you an ability to combine multiple GROUP BY clauses into one GROUP BY clause. By definition, a grouping set is a group of…

Continue ReadingGoogle BigQuery Grouping Sets Alternative and Example
Comments Off on Google BigQuery Grouping Sets Alternative and Example
September 15, 2022
Snowflake

Oracle DML LOG ERROR Alternative in Snowflake

Oracle database supports many user friendly error handling features that make the developer's life easier when working on DML statements such as INSERT, UPDATE, DELETE, MERGE. One of the such features is dml error logging. Using this feature, your DML statements will continue by logging errors in the error table created using the DBMS_ERRLOG package. This feature is not yet available in Snowflake. In this article, we will check Oracle DML LOG ERROR alternative methods in Snowflake scripting. Oracle DML Error Logging With Oracle DML error logging feature, you can…

Continue ReadingOracle DML LOG ERROR Alternative in Snowflake
Comments Off on Oracle DML LOG ERROR Alternative in Snowflake
August 29, 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…

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