Snowflake

Snowflake Convert Array to Rows – Methods and Examples

Snowflake supports the array functions. You can insert an array of values such as integer, characters, etc to the Snowflake table. In this article, we will check how to convert Snowflake array type into rows using table functions. Snowflake Convert Array to Rows When working with arrays in Snowflake, you often need to expand array elements into multiple rows. The recommended method to convert an array of integer or characters to rows is to use the table function. We will use the FLATTEN function for the demonstration. Snowflake FLATTEN Function…

Continue ReadingSnowflake Convert Array to Rows – Methods and Examples
Comments Off on Snowflake Convert Array to Rows – Methods and Examples
December 26, 2019
Snowflake

Export Snowflake Table using Python – Example

Snowflake cloud data warehouse provides support for many connectors. For example, Python connector, Spark connector, etc. In my previous articles, we have seen how to use Python connectors, JDBC and ODBC drivers to connect to Snowflake. In this article, we will check how to export Snowflake table using Python with an example. Export Snowflake Table using Python As mentioned, there are many methods such as Python connector, JDBC or ODBC drivers get data from Snowflake tables. Related Articles, How to use Snowflake Python Connector and Example Use Snowflake Python Connector…

Continue ReadingExport Snowflake Table using Python – Example
Comments Off on Export Snowflake Table using Python – Example
December 24, 2019
Snowflake

Snowflake Sequence – How to Create and Use it?

A Sequence is a named object in an individual Snowflake schema and database. These sequences are used to generate unique numbers. You can use sequence to generate unique numbers that can be used as surrogate key values for primary key values. Snowflake Sequence Overview A sequence value can represent a 64-bit two’s complement integer. You can use it wherever you would use numeric values. Snowflake supports user sequences for the four integer types: byteint, smallint, integer, and bigint. For example, you can use sequences in unique columns, primary key columns, etc. You…

Continue ReadingSnowflake Sequence – How to Create and Use it?
Comments Off on Snowflake Sequence – How to Create and Use it?
December 23, 2019
Snowflake

Export Snowflake Table Data to Local CSV format

The exporting tables to local system is one of the common requirements. You may need to export Snowflake table to analyze the data or transport it to a different team. You can export the Snowflake schema in different ways, you can use COPY command, or Snowsql command options. In this articles, we will check how to export Snowflake table data to a local CSV format. Using the COPY command may be the fastest method. But, it will unload tables to S3 location. You need to have an AWS subscription to…

Continue ReadingExport Snowflake Table Data to Local CSV format
Comments Off on Export Snowflake Table Data to Local CSV format
December 22, 2019
Snowflake

Snowflake Extract Date using Regular Expression Functions

In my other article, we have seen how to extract number or numeric values from a string. The other common requirement is to extract date from a string data. For example, you may get requirement to extract date of birth from the string field. Snowflake supports various date functions to validate a date value. In this article, we will check how to extract date from a string field using Snowflake regular expression functions. Extract Date using Snowflake Regular Expression Functions In my other article, we have discussed on Snowflake regular…

Continue ReadingSnowflake Extract Date using Regular Expression Functions
Comments Off on Snowflake Extract Date using Regular Expression Functions
December 21, 2019
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