Apache Spark

SQL Merge Operation Using Pyspark – UPSERT Example

In the relational databases such as Snowflake, Netezza, Oracle, etc, Merge statement is used to manipulate the data stored in the table. In this article, we will check how to SQL Merge operation simulation using Pyspark. The method is same in Scala with little modification. SQL Merge Statement The MERGE command in relational databases, allows you to update old records and insert new records simultaneously. This command is sometimes called UPSERT (UPdate and inSERT command). Following is the sample merge statement available in RDBMS. merge into merge_test using merge_test2 on…

1 Comment
January 27, 2020
General

SQL and Hive GROUP BY Alternative-Example

It is common to write the queries using GROUP BY and HAVING clause to group records or rows. Group by clause use columns in Hive or relational database tables for grouping particular column values mentioned with the group by. But, GROUP BY and DISTINCT operations are costly. It is applicable to both Hive and relational databases. But, in some cases, you can rewrite the queries to remove GROUP BY clause. In this article, we will check what are GROUP BY alternative methods available in Hive and SQL. SQL and Hive…

Comments Off on SQL and Hive GROUP BY Alternative-Example
January 24, 2020
Snowflake

How to Execute Snowflake Commands from Shell Script?- Example

Snowflake is one of the leading cloud data warehouse providers. Snowflake provides support for many leading programming languages either by providing JDBC, ODBC drivers, or language specific connectors (Python connector). In this article, we will check how to execute Snowflake commands from shell script with some examples. Execute Snowflake Commands from Shell Script The best part about Snowflake is that it provides an interactive terminal called SnowSQL. You can use it to execute queries, create database objects and perform some of the admin tasks. You can call the SnowSQL from…

Comments Off on How to Execute Snowflake Commands from Shell Script?- Example
January 22, 2020
Snowflake

Generate Snowflake Objects DDL using GET_DDL Function

Snowflake is a fully managed cloud data warehouse solution provided on AWS, Azure and GCP. You don't have to manage hardware's and your only task is to manage databases and tables that you create as part of your project development. In this article, we will check one of the administrative tasks, generate DDL for Snowflake objects such as view, tables DDL using built-in GET_DDL function. Snowflake Objects DDL using GET_DDL Function Snowflake provides many useful functions to make developers and administrators task easy. One of such function is GET_DDL function,…

Comments Off on Generate Snowflake Objects DDL using GET_DDL Function
January 22, 2020
Snowflake

Snowflake Control Structures – IF, DO, WHILE, FOR

The best part about Snowflake is it supports JavaScript as a programming language to write stored procedures and user defined functions. The Stored procedure uses JavaScript to combine SQL with control structures such as branching and looping. In this article, we will check Snowflake branching and looping control structures. Snowflake Control Structures You can use two types of control structures inside stored procedures and user defined functions. Following are the Snowflake control structures Branching Structures - Sometimes called conditional control structures Looping Structures - Sometimes called Iterative control structures. Branching…

Comments Off on Snowflake Control Structures – IF, DO, WHILE, FOR
January 19, 2020
Snowflake

Snowflake REPLACE Function, Usage and Examples

Just like translate function, replace function is also one of the widely used string functions in Snowflake. The replace function is commonly used to manipulate the strings or expression. For example, replace the part of sub-string across the input string or expression. In this article, we will check replace function, its syntax, usage with some examples. Snowflake REPLACE Function In general, SQL replace function replaces each instance of a pattern in the input with the value in the string replacement. Snowflake replace removes all occurrences of a specified substring, and…

Comments Off on Snowflake REPLACE Function, Usage and Examples
January 19, 2020
Snowflake

Snowflake TRANSLATE Function, Usage and Examples

There are many situations in data warehouse where you need to replace the one character with another. For example, replace special character such as symbol in the input expression with a space or remove it. In this article, we will check how to use the Snowflake TRANSLATE function to replace characters with some examples. Snowflake TRANSLATE Function In general, you can use translate function to translate or replace one or more characters into another set of characters. Snowflake supports the translate function, which performs the same job as translate function…

Comments Off on Snowflake TRANSLATE Function, Usage and Examples
January 19, 2020
Snowflake

Snowflake Dynamic SQL Queries and Examples

When you migrate the data from other relational databases such as Netezza, Oracle, Redshift, etc, you will find the many queries which are generated on the fly and executed. These kind of queries are called dynamic queries. In the data warehouse, there may be a situation where you want to generate dynamic SQL queries based on your requirement. In this article, we will check how to generate and execute dynamic SQL queries in Snowflake with an example. Snowflake Dynamic SQL Queries Snowflake supports generating and executing dynamic queries in stored…

Comments Off on Snowflake Dynamic SQL Queries and Examples
January 17, 2020
Snowflake

Snowflake Stored Procedures, Syntax, Limitations and Examples

To support migration from other relational databases, Snowflake supports the stored procedures. In this article, we will check Snowflake stored procedures, syntax, usage and restrictions with some examples. Stored procedure combines the complex SQL business logic with procedural statements. Snowflake Stored Procedures Overview Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's and same time handle the exceptions if any in your data or custom exception handling. For example, suppose that you want to clean up a database by deleting data older than a…

Comments Off on Snowflake Stored Procedures, Syntax, Limitations and Examples
January 17, 2020
Snowflake

Snowflake Cross Database Access, Syntax and Examples

The cross database access does allows you to access the objects such as tables, view, synonyms, functions that are available on the same cloud warehouse server. In this article, we will check Snowflake cross database access and it's restrictions. Snowflake Cross Database Access Using cross database access method, you can refer the object present in another database. For example, you can refer function present in another database and use result in the current database. select demo_db..isnumeric(to_variant('hello')) is_numeric; +------------+ | IS_NUMERIC | |------------| | False | +------------+ You can use cross…

Comments Off on Snowflake Cross Database Access, Syntax and Examples
January 16, 2020