Snowflake

Replace Function in Snowflake – Usage and Examples

The Replace function in Snowflake is one of the commonly used functions in string manipulations. For example, you can use the replace function to remove the unwanted or junk characters from your string. This function is similar to the translate function available in Snowflake. We will discuss about translate function in another post. For now, let us continue with the replace function, its syntax and some examples. Replace Function in Snowflake Replace Function in Snowflake In general, replace function in SQL replaces each instance of a pattern in the input…

Comments Off on Replace Function in Snowflake – Usage and Examples
May 31, 2022
Redshift

Set Operators in Redshift: UNION, EXCEPT/MINUS and INTERSECT

Like many other cloud data warehouses, Amazon Redshift supports set operators such as UNION, EXCEPT/MINUS and INTERSECT to combine two or more similar data sets from two or more SELECT statements. Here the similar data set literally mean, the data type of the result set should also match, otherwise you have to explicitly type cast data when using Redshift set operators. Set Operators in Redshift Types of Set Operators in Redshift Amazon Redshift supports the following types of set operators: UNION [DISTINCT] and UNION ALLINTERSECT [DISTINCT]EXCEPT [DISTINCT] or MINUS [DISTINCT]…

Comments Off on Set Operators in Redshift: UNION, EXCEPT/MINUS and INTERSECT
April 6, 2022
Redshift

Working with Redshift Regular Expression Functions

The regular expression functions in Amazon Redshift offer many advantages such as identifying precise patterns of characters in the given string. You can use these functions to validate the input data. For e.g. validate if the input value is an integer. The regular expression can also help you to extract specific string or characters from input. Redshift Regular Expression Functions In the previous post, we have discussed Redshift NVL and NVL2 functions to deal with NULL values. In this article, we will check how to use Redshift RegEx functions in…

Comments Off on Working with Redshift Regular Expression Functions
April 4, 2022
Redshift

Redshift NVL and NVL2 Functions – Syntax and Examples

In my other post, we have discussed how to handle NULL values in Redshift using NULL handling functions. In this article, we will check couple of NULL handling functions such as Redshift NVL and NVL2 functions in details with syntax, usage and few examples. Redshift NVL and NVL2 Functions Redshift NVL and NVL2 Functions Similar to many other relational database or data warehouse appliances, Redshift supports NVL and NVL2. These functions are mainly used to handle the null values in Redshift tables. For example, replace NULL values with any readable…

Comments Off on Redshift NVL and NVL2 Functions – Syntax and Examples
March 9, 2022
Redshift

How to Handle NULL in Redshift? – Functions

A NULL value in a relational database is a special marker in SQL that indicates a data value does not exist in the database table. In other words, it is just a placeholder to denote values that are missing or that we do not know. Almost all relational databases and bigdata frameworks support functions to handle null values. In this article, we will check how to handle NULL in Amazon AWS Redshift, we shall also check Redshift NULL handling functions, usage and some examples. Handle NULL in Redshift Handle NULL in Amazon Redshift Similar…

Comments Off on How to Handle NULL in Redshift? – Functions
March 9, 2022
Redshift

Working with External Tables in Amazon Redshift

Until recently Amazon Redshift was not supporting external tables. But, you can create external tables in Amazon Redshift using Redshift Spectrum. External tables allow you to access S3 flat files as a regular Redshift table. You can join the Redshift external table with a database tables such as permanent or temporary table to get required information. You can also perform a complex transformation involving various tables including external tables. The External tables are usually used to build the data lake where you access the raw data file which is stored…

Comments Off on Working with External Tables in Amazon Redshift
March 2, 2022
Snowflake

How to Handle Error in Snowflake Procedures and Functions?

Snowflake Cloud data warehouse supports stored procedures and user defined functions to help migration from other relational databases such as Oracle, Teradata. You can write stored procedures using JavaScript APIs. You can also write user defined functions using JavaScript APIs. JavaScript support error handling using try/catch block. However, Snowflake also supports built-in functions such as try_cast to handle error during type conversion. In this article, we will check how to handle error in Snowflake procedures and functions. Snowflake Stored Procedure How to Handle Error in Snowflake? The Snowflake cloud database…

Comments Off on How to Handle Error in Snowflake Procedures and Functions?
February 18, 2022
Apache Spark

How to Replace Spark DataFrame Column Value? – Scala and PySpark

Similar to relational database tables, a DataFrame in Spark is a dataset organized into named columns. Spark DataFrame consists of columns and rows. When you are working on a multiple data sources, you may receive a data with unwanted values such as junk characters in your Spark DataFrames. In this article, we will check how to replace such a junk value in Spark DataFrame column. We will also check methods to replace values in Spark DataFrames. Replace Spark DataFrame Column Value It is very common requirement to cleanse the source…

Comments Off on How to Replace Spark DataFrame Column Value? – Scala and PySpark
February 17, 2022
Snowflake

How to Handle NULL in Snowflake? Functions

Snowflake is one of the leading cloud databases that you can use to create cloud data warehouse. It is built for a cloud and supports almost all features that are available in the current on-premises or cloud databases. In my other article, we have seen why you should learn Snowflake database. In this article, we will check how to handle NULL values in a Snowflake cloud database. We will also check null handling functions available in Snowflake. How to Handle NULL in Snowflake? A NULL value in any relational database is a…

Comments Off on How to Handle NULL in Snowflake? Functions
February 10, 2022
Snowflake

Why You Should Learn Snowflake? Complete Features

Snowflake is an analytic data warehouse on cloud provided as Software-as-a-Service (SaaS). The Snowflake database support ANSI SQL with added functionalities. It is faster and easier to use cloud data warehouse compared other cloud databases such as Redshift, Azure Synapse or GCP BigQuery. In this article, we will explain why you should learn Snowflake cloud database. We will also check the features that makes Snowflake a unique cloud database for data warehouse design. Why You Should Learn Snowflake? Snowflake is a cloud database specifically designed for a cloud. As per…

Comments Off on Why You Should Learn Snowflake? Complete Features
January 24, 2022