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
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
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
Redshift

Redshift WHERE Clause with Multiple Columns

Many relational databases such as Teradata and Oracle supports the multiple columns in WHERE clause. You use two or more columns in predicates. When you want to migrate to cloud databases such as Amazon Redshift, you always look for compatible SQL patterns on the target side. In this article, we will check one of such pattern that is Amazon Redshift WHERE clause predicate with multiple columns. What is a WHERE Clause? Before going into details, first let us check what is a where clause in general. The WHERE clause contains…

Continue ReadingRedshift WHERE Clause with Multiple Columns
Comments Off on Redshift WHERE Clause with Multiple Columns
July 6, 2022
Redshift

How to Create a Materialized View in Redshift?

Many organizations are using Redshift as their data warehouse house and they are using Redshift for reporting or for dashboard queries. Imagine if applications query often must perform complex queries on large tables. For example, consider a reporting SELECT statement that performs multi-table joins and aggregations on tables that contain billions of records. You can address this issue using materialized views in Amazon Redshift. In this article, we will check how to create a materialized view in Amazon Redshift. Materialized View in Redshift What are Materialized Views in Redshift? A materialized…

Continue ReadingHow to Create a Materialized View in Redshift?
Comments Off on How to Create a Materialized View in Redshift?
June 16, 2022
Redshift

How to Choose Correct Compression Encode in Redshift?

Data is a key aspect of any company's decision making process. It helps organizations to make smart decision using trends such as purchase, growth area and other high-level functionalities. Organizations grow by making smart decisions so does data. As a typical company’s size of data has grown exponentially, it’s become even more critical to optimize data storage. The size of data not only affects storage and cost, it also affects the performance of your query. The typical query performance is always directly proportional to the size of data because smaller…

Continue ReadingHow to Choose Correct Compression Encode in Redshift?
Comments Off on How to Choose Correct Compression Encode in Redshift?
March 17, 2022
Redshift

How to Query JSON Data in Redshift? – Examples

Similar to many cloud data warehouses such as Snowflake, Amazon Redshift supports many json functions to perform operations on json such as validating json data, parsing json values, etc. You can use these json functions to query the JSON data stored in a varchar column. In this article, we will check how to query JSON data in an Amazon Redshift with an example. Query JSON Data in Redshift Amazon Redshift do not contain any special data type to store JSON string. You can store JSON value in SUPER data type.…

Continue ReadingHow to Query JSON Data in Redshift? – Examples
Comments Off on How to Query JSON Data in Redshift? – Examples
March 4, 2022
Redshift

How to Create External Tables in Amazon Redshift?

Amazon Redshift External tables allow you to access files stored in S3 storage as a regular table. You can join the Redshift external table with a database tables such as permanent or temporary table to get required information. You can also perform a complex transformation involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables. There are many situations in which you need to access the…

Continue ReadingHow to Create External Tables in Amazon Redshift?
Comments Off on How to Create External Tables in Amazon Redshift?
March 2, 2022