Apache Spark

Spark SQL COALESCE on DataFrame – Examples

You will know the importance of coalesce function if you are from SQL or Data Warehouse background. Coalesce function is one of the widely used function in SQL. You can use the coalesce function to return non-null values. In this article, we will check how to use Spark SQL coalesce on an Apache Spark DataFrame with an example. Spark SQL COALESCE on DataFrame The coalesce is a non-aggregate regular function in Spark SQL. The coalesce gives the first non-null value among the given columns or null if all columns are null. Coalesce requires at…

Continue ReadingSpark SQL COALESCE on DataFrame – Examples
Comments Off on Spark SQL COALESCE on DataFrame – Examples
May 7, 2020
Redshift

Redshift Temporary Tables, Usage and Examples

Similar to many other relational databases such as Netezza, Snowflake, Oracle, etc. Amazon Redshift support creating temp or temporary tables to hold non-permanent data. i.e. Data which you will use only in the current session and Redshift will drop the temp table soon after the session ends. In this article, we will check how to create Redshift temp or temporary tables, syntax, usage and restrictions with some examples. Redshift Temporary Tables The temporary table in Redshift is visible only within the current session. The table is automatically dropped at the…

Continue ReadingRedshift Temporary Tables, Usage and Examples
Comments Off on Redshift Temporary Tables, Usage and Examples
May 5, 2020
Snowflake

Handle Cursor in Snowflake Stored Procedures – Examples

Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's. Stored procedure also handles an exceptions if any in your data or custom exception handling. The relational databases such as Oracle, Redshift, Netezza, etc. supports cursor variables. In this article, we will check how to handle cursor variable in Snowflake stored procedures with an example. Handle Cursor in Snowflake Stored Procedures In a relational database, cursors are extensively used in stored procedures to loop through the records from SELECT statements. Stored procedures encapsulate the business logic. For…

Continue ReadingHandle Cursor in Snowflake Stored Procedures – Examples
Comments Off on Handle Cursor in Snowflake Stored Procedures – Examples
May 2, 2020
Snowflake

Convert Permanent table to Transient Table in Snowflake

Snowflake Transient tables are similar to that of permanent tables only difference is that they don not support fail-safe period. Therefore, cost associated with fail-safe is not applicable to transient tables. You can use transient tables in an ETL design to hold temporary data. In this article, we will check how to convert permanent table to transient table in Snowflake. Transient Table in Snowflake As mentioned earlier, transition tables are similar to managed tables with key difference such as fail-safe is not available. The transient tables are designed for transitory…

Continue ReadingConvert Permanent table to Transient Table in Snowflake
Comments Off on Convert Permanent table to Transient Table in Snowflake
May 2, 2020
Redshift

TRY_CAST Function Alternative in Redshift – Examples

There are many situations where in CAST conversion fails. For example, let us say you are trying to convert varchar to integer. The cast function will fail if the content is not valid integer values. Data bases such as Snowflake, Azure SQL data warehouse supports try_cast function to safely convert data types. In this article, we will check TRY_CAST function alternative in Redshift and how to use it to safely convert data types of the input values. TRY_CAST Function Alternative in Reshift Before going into details about try_cast alternative in…

Continue ReadingTRY_CAST Function Alternative in Redshift – Examples
Comments Off on TRY_CAST Function Alternative in Redshift – Examples
April 25, 2020
Snowflake

Working with Snowflake External Tables and S3 Examples

Snowflake External tables allow you to access files stored in external stage as a regular table. You can join the Snowflake external table with permanent or managed table to get required information or perform the complex transformations involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables. Snowflake External Tables As mentioned earlier, external tables access the files stored in external stage area such as Amazon…

Continue ReadingWorking with Snowflake External Tables and S3 Examples
Comments Off on Working with Snowflake External Tables and S3 Examples
April 7, 2020
Snowflake

How to Create Snowflake Clustered Tables? Examples

Snowflake cloud data warehouse produces create clustered tables by default. However, as the table size grows and DML occurs on the table, the data in some table rows may no longer cluster optimally on desired dimensions. In this article, we will check how to create Snowflake clustered tables to improve the DML query performance. Snowflake Clustered Tables When you create a table and insert records into the Snowflake tables, inserted rows are grouped into continuous storage such as micro-partitions. But, as the table size grows, data in some column may…

Continue ReadingHow to Create Snowflake Clustered Tables? Examples
Comments Off on How to Create Snowflake Clustered Tables? Examples
April 5, 2020
Snowflake

Snowflake Cloud Data Warehouse Best Practices

Snowflake cloud data warehouse is optimized to work with cloud environments such as AWS, GCP, and Azure. Snowflake data warehouse is fast, reliable and designed for cloud. In this article, we will check what are Snowflake cloud data warehouse best practices. Snowflake Cloud Data Warehouse Best Practices If you follow the Snowflake official documentation. You will find many optimization methods. We have also discussed how to optimize the table structure in my other articles. Following are some of the best practices that you can use when working with Snowflake cloud…

Continue ReadingSnowflake Cloud Data Warehouse Best Practices
Comments Off on Snowflake Cloud Data Warehouse Best Practices
April 4, 2020
Snowflake

Type of Snowflake Stages – How to Create and Use them?

Snowflake cloud data warehouse architecture is specifically designed to work with the cloud such as Amazon AWS, Google cloud platform and Microsoft Azure. Loading and unloading tables, Snowflake use different methods compared to other relational database. Snowflake uses staging area to store the file that you can in your COPY command. In this article, we will check the types of Snowflake stages, how to create and use them. Types of Snowflake Stages A stage in Snowflake is an intermediate space where you can upload the files so that you can…

Continue ReadingType of Snowflake Stages – How to Create and Use them?
Comments Off on Type of Snowflake Stages – How to Create and Use them?
March 25, 2020
Snowflake

Snowflake Snowsql Windows System Exit Codes

In my other article, Snowflake Snowsql Exit Codes for Unix/Linux Systems, we have discussed on SnowSQL exit codes for Linux systems. But, many developers prefer to use a Windows system. In this article, we will check Snowflake Snowsql windows system exit codes and how to capture them on a Windows command prompt. Snowflake Snowsql Windows System Exit Codes SnowSQL command line interface for Snowflake is written in pure Python. You can download the msi installer and install same on your Windows machine. The configuration is pretty much similar to that…

Continue ReadingSnowflake Snowsql Windows System Exit Codes
Comments Off on Snowflake Snowsql Windows System Exit Codes
March 13, 2020