Snowflake

Snowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions

The pattern matching conditions in Snowflake are used to search a string for a given pattern. You can search for the string by matching particular patterns. Snowflake Pattern Matching A pattern-matching operator searches a string for a pattern specified in the conditional expression and returns either Boolean (true/ false) or matching value if it finds a match. These conditions are particularly important when you need to search string patterns in your database column values. Pattern matching conditions are mainly used in WHERE conditions. Following are the commonly used pattern matching…

Continue ReadingSnowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions
Comments Off on Snowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions
January 30, 2020
Redshift

Redshift NOT NULL Constraint, Syntax and Examples

Similar to most of the MPP databases such as Snowflake, the Amazon Redshift database allows you to define constraints. The Redshift database does not enforce constraints like primary key, foreign key and unique key. But, it does enforce the NOT NULL constraint. In this article, we will check Redshift NOT NULL constraint, its syntax and usage. Redshift NOT NULL Constraint Constraints other than NOT NULL are created as disabled. Amazon Redshift enforces only NOT NULL. You can create NOT NULL constraint while creating tables. Redshift NOT NULL Constraint Syntax There…

Continue ReadingRedshift NOT NULL Constraint, Syntax and Examples
Comments Off on Redshift NOT NULL Constraint, Syntax and Examples
January 27, 2020
Apache Spark

SQL Merge Operation Using Pyspark – UPSERT Example

In the relational databases such as Snowflake, Netezza, Oracle, etc, Merge statement is used to manipulate the data stored in the table. In this article, we will check how to SQL Merge operation simulation using Pyspark. The method is same in Scala with little modification. SQL Merge Statement The MERGE command in relational databases, allows you to update old records and insert new records simultaneously. This command is sometimes called UPSERT (UPdate and inSERT command). Following is the sample merge statement available in RDBMS. merge into merge_test using merge_test2 on…

Continue ReadingSQL Merge Operation Using Pyspark – UPSERT Example
1 Comment
January 27, 2020
General

SQL and Hive GROUP BY Alternative-Example

It is common to write the queries using GROUP BY and HAVING clause to group records or rows. Group by clause use columns in Hive or relational database tables for grouping particular column values mentioned with the group by. But, GROUP BY and DISTINCT operations are costly. It is applicable to both Hive and relational databases. But, in some cases, you can rewrite the queries to remove GROUP BY clause. In this article, we will check what are GROUP BY alternative methods available in Hive and SQL. SQL and Hive…

Continue ReadingSQL and Hive GROUP BY Alternative-Example
Comments Off on SQL and Hive GROUP BY Alternative-Example
January 24, 2020
Snowflake

Generate Snowflake Objects DDL using GET_DDL Function

Snowflake is a fully managed cloud data warehouse solution provided on AWS, Azure and GCP. You don't have to manage hardware's and your only task is to manage databases and tables that you create as part of your project development. In this article, we will check one of the administrative tasks, generate DDL for Snowflake objects such as view, tables DDL using built-in GET_DDL function. Snowflake Objects DDL using GET_DDL Function Snowflake provides many useful functions to make developers and administrators task easy. One of such function is GET_DDL function,…

Continue ReadingGenerate Snowflake Objects DDL using GET_DDL Function
Comments Off on Generate Snowflake Objects DDL using GET_DDL Function
January 22, 2020
Snowflake

Snowflake Control Structures – IF, DO, WHILE, FOR

The best part about Snowflake is it supports JavaScript as a programming language to write stored procedures and user defined functions. The Stored procedure uses JavaScript to combine SQL with control structures such as branching and looping. In this article, we will check Snowflake branching and looping control structures. Snowflake Control Structures You can use two types of control structures inside stored procedures and user defined functions. Following are the Snowflake control structures Branching Structures - Sometimes called conditional control structures Looping Structures - Sometimes called Iterative control structures. Branching…

Continue ReadingSnowflake Control Structures – IF, DO, WHILE, FOR
Comments Off on Snowflake Control Structures – IF, DO, WHILE, FOR
January 19, 2020
Snowflake

Snowflake REPLACE Function, Usage and Examples

Just like translate function, replace function is also one of the widely used string functions in Snowflake. The replace function is commonly used to manipulate the strings or expression. For example, replace the part of sub-string across the input string or expression. In this article, we will check replace function, its syntax, usage with some examples. Snowflake REPLACE Function In general, SQL replace function replaces each instance of a pattern in the input with the value in the string replacement. Snowflake replace removes all occurrences of a specified substring, and…

Continue ReadingSnowflake REPLACE Function, Usage and Examples
Comments Off on Snowflake REPLACE Function, Usage and Examples
January 19, 2020
Snowflake

Snowflake TRANSLATE Function, Usage and Examples

There are many situations in data warehouse where you need to replace the one character with another. For example, replace special character such as symbol in the input expression with a space or remove it. In this article, we will check how to use the Snowflake TRANSLATE function to replace characters with some examples. Snowflake TRANSLATE Function In general, you can use translate function to translate or replace one or more characters into another set of characters. Snowflake supports the translate function, which performs the same job as translate function…

Continue ReadingSnowflake TRANSLATE Function, Usage and Examples
Comments Off on Snowflake TRANSLATE Function, Usage and Examples
January 19, 2020
Snowflake

Snowflake Dynamic SQL Queries and Examples

When you migrate the data from other relational databases such as Netezza, Oracle, Redshift, etc, you will find the many queries which are generated on the fly and executed. These kind of queries are called dynamic queries. In the data warehouse, there may be a situation where you want to generate dynamic SQL queries based on your requirement. In this article, we will check how to generate and execute dynamic SQL queries in Snowflake with an example. Snowflake Dynamic SQL Queries Snowflake supports generating and executing dynamic queries in stored…

Continue ReadingSnowflake Dynamic SQL Queries and Examples
Comments Off on Snowflake Dynamic SQL Queries and Examples
January 17, 2020
Snowflake

Snowflake Stored Procedures, Syntax, Limitations and Examples

To support migration from other relational databases, Snowflake supports the stored procedures. In this article, we will check Snowflake stored procedures, syntax, usage and restrictions with some examples. Stored procedure combines the complex SQL business logic with procedural statements. Snowflake Stored Procedures Overview Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's and same time handle the exceptions if any in your data or custom exception handling. For example, suppose that you want to clean up a database by deleting data older than a…

Continue ReadingSnowflake Stored Procedures, Syntax, Limitations and Examples
Comments Off on Snowflake Stored Procedures, Syntax, Limitations and Examples
January 17, 2020