Snowflake

Snowflake Split String on Delimiter-Functions and Examples

The split string is one of the common requirements in many relational database and programming languages. For example, get username from the string. Many RDBMS provides the functions or methods to split string on a delimiter and extract required text from an array. In this article, we will check Snowflake functions to split string on a delimiter. Snowflake Split String In many relational databases such as Netezza, PostgreSQL, etc, you can use array functions to extract records from split string result. But, Snowflake supports many useful split functions that you…

Comments Off on Snowflake Split String on Delimiter-Functions and Examples
January 8, 2020
Snowflake

How to Export Snowflake Data to JSON Format? -Example

The JSON format is one of the widely used file formats to store data that you want to transmit to another server or location. Most of the web applications use JSON to exchange the application information. For example, provide parameter values, credentials, etc. In this article, we will check how to export Snowflake data to json format with some examples. What is JSON file? Before jumping into the methods to export Snowflake table in JSON format, first, let us check what is JSON file? The JSON, or JavaScript Object Notation, is a minimal, readable format for structuring data. One…

Comments Off on How to Export Snowflake Data to JSON Format? -Example
January 7, 2020
BigData

Apache Hive Set Operators: UNION and UNION ALL

You can use the Apache Hive set operators to combine similar data sets from two or more SELECT statements into a single result set . Here the similar data set literally mean, the data type of the result set should also match. Otherwise, you have to explicitly convert type using type conversion functions. Hive Set Operators Hadoop Hive supports following set operators. UNION [DISTINCT] UNION ALL Hive versions prior to 1.2.0 only support UNION ALL (bag union), in which duplicate rows are not eliminated. Starting version 1.2.0, Hive includes optional ALL keywords.…

Comments Off on Apache Hive Set Operators: UNION and UNION ALL
January 7, 2020
Snowflake

Snowflake Extract Function Usage and Examples

The extract function in Snowflake extracts specified date or time part from the date, time, or timestamp fields. This function retrieves the sub part such as day, month, year,etc. The extract function is equivalent to date_part() function. In this article, we will check how to use extract and date_part function to get specified sub part. Snowflake Extract Function The extract function is very useful when you are working with various data sources. Sometimes, you may get requirement to extract part of timestamp field such as date and pass it to…

Comments Off on Snowflake Extract Function Usage and Examples
January 6, 2020
BigData

How to Write Hive Conditional Join and Example

The join in Hive is used to combine the records from multiple tables based on the joining condition. The joining condition can be on the common columns between participating tables. Hadoop Hive supports various join types. You may get the requirement to choose the joining values based on certain conditions. For example, you can have OR condition in the joining condition. In this article, we will check how to write Conditional Join in Hive with some examples. Hive Conditional Join You never know what type of requirement that you are…

Comments Off on How to Write Hive Conditional Join and Example
January 6, 2020
Snowflake

Snowflake Recover Deleted Rows – Time Travel Examples

The Snowflake cloud data warehouse works with heterogeneous data sets. You may work with huge amount of data. It is a common requirement to recover the deleted rows or records. During the clean up process, the developer may delete the data accidentally. In this article, we will check how to recover deleted rows in Snowflake using time travel settings. Snowflake Recover Deleted Rows As mentioned, you can recover the deleted records. Snowflake uses the time travel setting to store the historical data. Snowflake Time Travel The Snowflake Time Travel enables…

Comments Off on Snowflake Recover Deleted Rows – Time Travel Examples
January 3, 2020
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…

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…

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…

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…

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