Snowflake

Commonly used Snowflake Date Functions and Examples

The date functions are most commonly used functions in the data warehouse. You can use date functions to manipulate the date expressions or variables containing date and time value. For example, get the current date, subtract date values, etc. In this article, we will check what are commonly used date functions in the Snowflake cloud data warehouse. Many applications use date functions to manipulate the date and time data types. Each date value contains the century, year, month, day, hour, minute, second and milliseconds. Hence, date types are highly formatted…

Continue ReadingCommonly used Snowflake Date Functions and Examples
Comments Off on Commonly used Snowflake Date Functions and Examples
January 11, 2020
Snowflake

Snowflake Interval Data Types and Conversion Examples

The INTERVAL data types in Snowflake are handy and widely used types when working with date, time variables. Snowflake supports interval types in the same way as other PostgreSQL databases such as Netezza, Redshift, Vertica, Oracle, etc. In this article, we will check interval types with some common use cases. Snowflake Interval Data Types You can express interval types as a combination of the INTERVAL keyword with a numeric quantity and a supported date part; for example: INTERVAL '1 days' or INTERVAL '10 minutes'. The Snowflake INTERVAL functions are commonly…

Continue ReadingSnowflake Interval Data Types and Conversion Examples
Comments Off on Snowflake Interval Data Types and Conversion Examples
January 11, 2020
Snowflake

Snowflake Merge Statement Syntax, Usage and Examples

In a data warehouse, it is common to manipulate the table data. Sometimes, you have to manipulate the source table using data from another table. Many relational databases such as Netezza supports Merge command that can perform update/delete, or delete simultaneously. In this example, we will check how to manipulate table using Snowflake Merge statement with some example. Snowflake Merge Statement The merge command in SQL is a command that allows you to update, delete, or insert into a source table using target table. Based on the matching condition rows…

Continue ReadingSnowflake Merge Statement Syntax, Usage and Examples
Comments Off on Snowflake Merge Statement Syntax, Usage and Examples
January 10, 2020
Snowflake

Snowflake Reuse Computed column – Derived Columns

The derived columns in Snowflake are columns that are derived from the previously calculated columns in same SELECT statement. In this article, we will check how to reuse previously computed or calculated column in Snowflake. These kind of columns are sometime called lateral column alias. What are derived columns? Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. In other word, Derived columns are columns that you derive from other previously…

Continue ReadingSnowflake Reuse Computed column – Derived Columns
Comments Off on Snowflake Reuse Computed column – Derived Columns
January 8, 2020
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…

Continue ReadingSnowflake Split String on Delimiter-Functions and Examples
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…

Continue ReadingHow to Export Snowflake Data to JSON Format? -Example
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.…

Continue ReadingApache Hive Set Operators: UNION and UNION ALL
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…

Continue ReadingSnowflake Extract Function Usage and Examples
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…

Continue ReadingHow to Write Hive Conditional Join and Example
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…

Continue ReadingSnowflake Recover Deleted Rows – Time Travel Examples
Comments Off on Snowflake Recover Deleted Rows – Time Travel Examples
January 3, 2020