Snowflake

Snowflake Extract Numbers using Regular Expression Functions

There are various requirements to extract numbers from string value. This requirement comes when you work with different data source. For example, heterogeneous data may contain many unwanted values and requirements will be to get only numbers or numeric values. Snowflake supports many built-in and regular expression functions. In this article, we will check how to extract numbers or numeric values from a string using Snowflake regular expression functions. Extract Numbers using Snowflake Regular Expression Functions In my other article, we have discussed on Snowflake regular expressions. We will use…

Continue ReadingSnowflake Extract Numbers using Regular Expression Functions
Comments Off on Snowflake Extract Numbers using Regular Expression Functions
December 21, 2019
Snowflake

Snowflake NOT NULL Constraint and Syntax

Similar to most of the MPP databases, Snowflake cloud database allows you to define constraints. The Snowflake 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 Snowflake NOT NULL constraint, its syntax and usage. Snowflake NOT NULL Constraint Constraints other than NOT NULL are created as disabled. Snowflake enforces only NOT NULL. You can create NOT NULL constraint while creating tables in the cloud database. A Snowflake table can have multiple NOT…

Continue ReadingSnowflake NOT NULL Constraint and Syntax
Comments Off on Snowflake NOT NULL Constraint and Syntax
December 20, 2019
Snowflake

Snowflake Unique Key Constraint and Syntax

Similar to Snowflake primary key and foreign key, unique key constraint is informational only. It is not enforced when you insert rows to the table. The unique key metadata information is used by the Snowflake optimizer to come up with the optimal execution plan. Snowflake Unique Key Constraint You can create Unique key constraint while creating tables in the Snowflake cloud database but it will not be enforced while loading tables. The Snowflake query planner uses these constraints to create a better query execution plan. Similar to the foreign key,…

Continue ReadingSnowflake Unique Key Constraint and Syntax
Comments Off on Snowflake Unique Key Constraint and Syntax
December 20, 2019
Snowflake

Snowflake Foreign Key Constraint and Syntax

Similar to the primary key constraint, the Snowflake foreign key constraint is also information only. It is not enforced when you insert the data into a table. Constraints other than NOT NULL are created as disabled. Snowflake Foreign Key Constraint You can create the foreign key while creating tables on the Snowflake cloud data warehouse. But, foreign key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the foreign keys to create an optimal execution plan. A table can have multiple unique…

Continue ReadingSnowflake Foreign Key Constraint and Syntax
Comments Off on Snowflake Foreign Key Constraint and Syntax
December 20, 2019
Snowflake

Snowflake Primary Key Constraint and Syntax

For the compatibility with other databases, Snowflake provides the primary key constraint. The primary key constraint is informational only; It is not enforced when you insert the data into a table. Snowflake supports referential integrity constraints such as primary key, foreign key, unique key, and NOT NULL. Snowflake Primary Key Constraint You can create the primary key while creating tables on the Snowflake cloud data warehouse. But, primary key will not be enforced when you load the tables. However, constraints provide valuable metadata. The optimizer uses the primary key to…

Continue ReadingSnowflake Primary Key Constraint and Syntax
Comments Off on Snowflake Primary Key Constraint and Syntax
December 20, 2019
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
General

Teradata ROWNUM Pseudocolumn Alternative and Examples

The relational databases like Oracle supports the ROWNUM pseudo column. If you are from Oracle background, you may have used ROWNUM extensively to limit the output result set. But, There is no ROWNUM in Teradata. In this article, we will check Teradata ROWNUM pseudocolumn alternative with some examples. Teradata ROWNUM Pseudocolumn Alternative A ROWNUM is a pseudo column, which indicates the row number in a result set retrieved by a SQL query. It starts by assigning 1 to the first row and increments the ROWNUM value with each subsequent row returned. In the databases…

Continue ReadingTeradata ROWNUM Pseudocolumn Alternative and Examples
Comments Off on Teradata ROWNUM Pseudocolumn Alternative and Examples
December 17, 2019