Snowflake

Snowflake Pad Zeros – LPAD and RPAD with Examples

Snowflake supports many useful string functions. The pad functions are most commonly used string functions in databases. The Snowflake pad functions are commonly used to pad characters such as zeros. You can use the pad functions to add or remove the characters either at the beginning or end of an expression or column values. In this article, we will Snowflake pad functions such as LPAD and RPAD with an example to pad zeros. Snowflake Pad Functions As mentioned earlier, Snowflake supports a lot of string manipulation functions. Among them, the…

Continue ReadingSnowflake Pad Zeros – LPAD and RPAD with Examples
Comments Off on Snowflake Pad Zeros – LPAD and RPAD with Examples
December 30, 2019
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
BigData

Hive Extract Numbers using Regular Expression Functions

In my other article, we have seen how to extract date values from a string using Hive regular expressions. The regular expression function is sometime called as regex. The other common uses of regular expression is to extract the numeric values. For example, extract area code or phone numbers from the string data. In this article, we will check how to extract numbers using regular expression functions in Apache Hive. Extract Numbers using Hive Regular Expression Functions When you work on different data sources, you may get requirement to extract…

Continue ReadingHive Extract Numbers using Regular Expression Functions
Comments Off on Hive Extract Numbers using Regular Expression Functions
December 25, 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
BigData

Hive Extract Date using Regular Expression Functions

In my other articles, we have seen how regular expression functions are used to check if a string is numeric. The other common requirements is to extract the date field from the string using Hive regular expression functions. For example, you may get requirement to get date value from the string field. Apache Hive support many functionalities that you can use to extract date type. But, using a regular expression function to extract date from a string is the easiest method. Extract Date using Hive Regular Expression Functions There are…

Continue ReadingHive Extract Date using Regular Expression Functions
Comments Off on Hive Extract Date using Regular Expression Functions
December 23, 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