Apache Spark

Apache Spark SQL Bucketing Support – Explanation

Spark SQL supports clustering column values using bucketing concept. Bucketing and partition is similar to that of Hive concept, but with syntax change. In this article, we will check Apache Spark SQL Bucketing support in different versions of Spark. In this article, we will concentrate only on the Spark SQL DDL changes. On applying bucketing on DataFrame, go through the article. Apache Spark SQL Bucketing Support Bucketing is an optimization technique in Spark SQL that uses buckets and bucketing columns to determine data partitioning. The bucketing concept is one of the optimization technique that use bucketing to…

Comments Off on Apache Spark SQL Bucketing Support – Explanation
May 18, 2020
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…

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…

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…

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…

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…

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…

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…

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…

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…

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