Snowflake

Snowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT

Snowflake set operators are used to combine similar resultset from two or more SELECT statements. The data type of the two or more result sets should also match, otherwise explicit type cast has to be used to convert the result set to required data types. Types of Snowflake Set Operators Cloud data warehouse supports the three types of set operators: UNION and UNION ALLINTERSECT EXCEPT or MINUS Snowflake Set Operators Syntax Below is the syntax of Set operators in Snowflake: query { UNION [ ALL ] | INTERSECT | EXCEPT…

Continue ReadingSnowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT
Comments Off on Snowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT
December 18, 2019
Snowflake

Identify and Remove Duplicate Records from Snowflake Table

Snowflake supports primary, foreign key(s) and unique key(s), but, does not enforce them. Snowflake table allows you to insert duplicate rows. There are chances that some application may insert the records multiple times. There are several methods you can use to de-duplicate the snowflake tables. In this article, we will check how to identify and remove duplicate records from Snowflake table. Remove Duplicate Records from Snowflake Table There are many methods that you can use to remove the duplicate records from the Snowflake table. For example, use the DISTINCT keyword…

Continue ReadingIdentify and Remove Duplicate Records from Snowflake Table
Comments Off on Identify and Remove Duplicate Records from Snowflake Table
December 18, 2019
Snowflake

Snowflake WITH Clause Syntax, Usage and Examples

Snowflake WITH Clause is an optional clause that always precedes SELECT clause in the query statements or construct. The WITH clause usually contains a sub query that is defined as a temporary table similar to View definition. Each sub query in the WITH clause is associated with the name, an optional list of a column names, and a query that evaluates to a table. The query expression is usually a select statement. In this article, we will check Snowflake WITH clause syntax, usage, types of WITH clause with some examples.…

Continue ReadingSnowflake WITH Clause Syntax, Usage and Examples
Comments Off on Snowflake WITH Clause Syntax, Usage and Examples
December 17, 2019
Snowflake

Snowflake Regular Expression Functions and Examples

The regular expressions are commonly used functions in programming languages such as Python, Java, R, etc. The Snowflake regular expression functions identify the precise pattern of the characters in given string. Regular expressions are commonly used in validating strings, for example, extracting numbers from the string values, etc. In this article, we will check the supported Regular expression functions in Snowflake. Snowflake Regular Expression Functions The regular expression functions are string functions that match a given regular expression. These functions are commonly called as a 'regex' functions. Below are some…

Continue ReadingSnowflake Regular Expression Functions and Examples
Comments Off on Snowflake Regular Expression Functions and Examples
December 17, 2019
Snowflake

How to use Snowflake Python Connector and Example

Snowflake is a cloud data warehouse environment and provides support for many major programming languages that uses JDBC or ODBC drivers. You can use Snowflake provided connector to connect your application. In this article, we will check one of the such connectors. i.e Python connector for Snowflake. Snowflake Python Connector The Snowflake Connector for Python provides an interface for developing Python applications that can connect to cloud data warehouse and perform all standard operations.  The connector is a pure python package that can be used to connect your application to…

Continue ReadingHow to use Snowflake Python Connector and Example
Comments Off on How to use Snowflake Python Connector and Example
December 14, 2019
Snowflake

Execute SQL Script File using Snowflake Snowsql Variable Substitution

Snowflake allows variable substitution using -D or --variable options. The variable substitution is one of the nice feature that allows you to pass values to the script dynamically. For example, pass month value to the SQL script. Many modern day databases support the variable substitution. In this article, we will check how to a execute SQL script file using Snowflake Snowsql variable substitution with an example. Snowflake Snowsql Variable Substitution Snowsql supports the variable substitution with the help of -D or --variable option. You can use that defined variable inside…

Continue ReadingExecute SQL Script File using Snowflake Snowsql Variable Substitution
Comments Off on Execute SQL Script File using Snowflake Snowsql Variable Substitution
December 13, 2019
Snowflake

Access Snowflake using Snowsql without Password Prompt – Snowsql Environment Variables

There are many ways you can connect to Snowflake cloud data warehouse server. The options include Snowsql, JDBC and ODBC drivers, python connector, Spark connectors, etc. In this article, we will discuss how to access Snowflake using Snowsql without password prompt. We will also check Snowsql environment variables. Access Snowflake using Snowsql without Password Prompt Password less access is useful when your application requires to connect to Snowflake frequently. There are multiple options that you can use to achieve password less communication to Snowflake server. Export Snowflake Database Server Environmental…

Continue ReadingAccess Snowflake using Snowsql without Password Prompt – Snowsql Environment Variables
Comments Off on Access Snowflake using Snowsql without Password Prompt – Snowsql Environment Variables
December 13, 2019
Snowflake

Snowsql Command Line Options and Examples

SnowSQL is a Python based command line interface to connect Snowflake from Windows, Linux, and Mac OS. The SnowSQL is an interactive terminal for Snowflake. You can use it to execute queries, create database objects and perform some of the admin tasks. In this article, we will check how to use Snowsql, what are the SnowSQL command line options. Snowsql Command Usage You can use following command to connect to Snowflake using Snowsql. snowsql -a accountName -u userName -d databaseName -s schemaName For example, consider following command to connect Snowflake…

Continue ReadingSnowsql Command Line Options and Examples
Comments Off on Snowsql Command Line Options and Examples
December 12, 2019
Snowflake

What is Snowflake Lateral Join and How to use it?

The lateral join in Snowflakes is similar to that of Hive lateral views. Lateral joins in snowflake behaves more like a correlated sub queries than normal snowflake joins. In this article, we will check what is Snowflake lateral join and how to use it. We will also check the lateral view with FLATTEN keyword and an example. What is Snowflake Lateral Join? In Snowflake, lateral keyword allows an in-line view to reference columns from a table expression that precedes that in-line view, but in some cases it may not refer…

Continue ReadingWhat is Snowflake Lateral Join and How to use it?
Comments Off on What is Snowflake Lateral Join and How to use it?
December 12, 2019
Snowflake

Different Snowflake Join Types and Examples

SQL Join is a clause in your query that is used for combining specific fields from two or more tables based on the common columns available. Snowflake joins are different from the set operators. Joins are used to combine rows from multiple tables. In this article, we will learn about different Snowflake join types with some examples. Test Data Following tables will be used to demonstrate different join types available in Snowflake cloud data warehouse system. S_STUDENTS +----+------+-----------+ | ID | NAME | CITY | |----+------+-----------| | 1 | AAA…

Continue ReadingDifferent Snowflake Join Types and Examples
Comments Off on Different Snowflake Join Types and Examples
December 11, 2019