Snowflake

Snowflake Update Join Syntax – Update using other Table

In the database, data is stored in the tables. Data is collected from various sources. Data is collected over the specific period of time and it may or may not be accurate at the time of loading. In some cases, you may want to update the table by taking data from other another table over same or other database on the same server. In this article, we will check Snowflake Update Join Syntax and example on how to update a table with data from another table. Snowflake Update Join Table…

Continue ReadingSnowflake Update Join Syntax – Update using other Table
Comments Off on Snowflake Update Join Syntax – Update using other Table
December 27, 2019
Snowflake

Snowflake Cumulative SUM and AVERAGE – Examples

Most of the analytical databases such as Netezza, Teradata, Oracle, Vertica allow you to use windows function to calculate running total or average. In this article, we will check how to use analytic functions with windows specification to calculate Snowflake Cumulative Sum (running total) or cumulative average with some examples. Snowflake Cumulative SUM and AVERAGE The cumulative sum or running total is one of the interesting problems in the databases where you have to calculate the sum or average using current result and previous (or next) row value. Snowflake Cumulative…

Continue ReadingSnowflake Cumulative SUM and AVERAGE – Examples
Comments Off on Snowflake Cumulative SUM and AVERAGE – Examples
December 27, 2019
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