GCP BigQuery

BigQuery Control Flow Statements – IF, LOOP, WHILE

Similar to relational databases such as SQL Server, BigQuery supports scripting. BigQuery scripting allows you to send multiple statements to BigQuery in one request. You can also set variables, and use it in any supported control flow statements such as IF and WHILE. For example, you can declare a variable, assign a value to it, and then reference it in a loop statement. In this article, we will check what are BigQuery Control Flow Statements with some examples. In BigQuery, a script is a SQL statement list to be executed in sequence. A…

Continue ReadingBigQuery Control Flow Statements – IF, LOOP, WHILE
Comments Off on BigQuery Control Flow Statements – IF, LOOP, WHILE
October 15, 2020
Azure Synapse

Azure Synapse Recursive Query Alternative-Example

You can use recursive query to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy. Though Azure Synapse uses T-SQL, but it does not support all features that are supported in T-SQL. One of such features is Recursive CTE or VIEWS. Azure Synapse does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Azure Synapse Recursive Query Alternative with an working example. Azure Synapse Recursive Query Alternative In the other RDBMS such as…

Continue ReadingAzure Synapse Recursive Query Alternative-Example
Comments Off on Azure Synapse Recursive Query Alternative-Example
October 13, 2020
BigData

Hive Pivot Table-Transpose Rows to Column and Example

Many relational databases such as Oracle, Snowflake support PIVOT function which you can use to convert row to column. But, Apache Hive does not support Pivot function yet. As an alternative method, you can use CASE and DECODE statements to convert table rows to column, or columns to rows as per your requirements. In this article, we will check different methods to transpose Hive table using a Pivot function alternative method with some examples. Hive Pivot Table Creating pivot table is a relatively common task in a data warehouse. You…

Continue ReadingHive Pivot Table-Transpose Rows to Column and Example
Comments Off on Hive Pivot Table-Transpose Rows to Column and Example
September 25, 2020
Redshift

Redshift TO_NUMBER Function and Different Arguments

There are many situations where you need to pre-process the received data in order to store it in the data warehouse. For instance, you many receive currency data with currency symbol. You have pre-process currency column to remove the dollar ($) symbol and store clean value in the database. Amazon Redshift support TO_NUMBER function, you can use it to remove unwanted characters from numeric value. In this article, we will check how to use TO_NUMBER type conversion function with its different format. We will also check different examples to use…

Continue ReadingRedshift TO_NUMBER Function and Different Arguments
Comments Off on Redshift TO_NUMBER Function and Different Arguments
September 21, 2020
Redshift

Redshift Trunc and Date_Trunc Functions, Examples and Differences

Amazon Redshift supports many built-in functions that you can use to perform day to day activities such as extracting only date part from a timestamp value. Redshift provides many date and string functions that you can use to play around date type. In this article, we will check how to use an Amazon Redshift trunc and date_trunc functions, syntax, usage, examples and difference between these two functions. Redshift Trunc and Date_Trunc Functions You can use the trunc and date_trunc functions to trunc certain portions of the date type. However, you…

Continue ReadingRedshift Trunc and Date_Trunc Functions, Examples and Differences
Comments Off on Redshift Trunc and Date_Trunc Functions, Examples and Differences
September 15, 2020
Redshift

Redshift vs Snowflake – Key Differences

Data is the lifeline for many companies. Be it is an advertising company or decision making company, data plays an important role in each and every step. To understand and utilize all this data, data warehouses have become an essential part of modern business. There are many data warehouse appliance such as Teradata, Vertica, Greenplum, etc are available in the market. But, nowadays, organizations are more inclined towards cloud data warehouses such as Snowflake, Redshift, Azure SQL Synapse, Google cloud Spanner, etc. In this article, we will check key differences…

Continue ReadingRedshift vs Snowflake – Key Differences
Comments Off on Redshift vs Snowflake – Key Differences
September 14, 2020
BigData

Oracle INSERT ALL Alternative in Hive/Spark SQL

Oracle database is one of widely used relational databases. It supports many syntax that are not available in any other transaction databases. One of such command is INSERT ALL. The INSERT ALL is used to insert computed records into multiple tables based on conditions. In this article, we will check what is Oracle INSERT ALL alternative in Hive and Spark SQL. Oracle INSERT ALL alternative in Hive/Spark SQL When migrating Oracle scripts to Apache Hive and Spark SQL, you will notice Hive and Spark SQL does not support many Oracle…

Continue ReadingOracle INSERT ALL Alternative in Hive/Spark SQL
Comments Off on Oracle INSERT ALL Alternative in Hive/Spark SQL
September 11, 2020
Snowflake

How to Remove Newline Characters from String in Snowflake?

If you store a string as a variable or a column in relational databases such SQL Server , then it can contain line breaks or newline (\n) characters. But, these newline characters have to be removed in pre-processing steps. In this article, we will check how to remove newline characters from a string or text in Snowflake. Remove Newline Characters from String in Snowflake Removing newline (\n), carriage return (\r) or any special characters is the common pre-processing step before storing records in any relational databases. Many databases supports built-in…

Continue ReadingHow to Remove Newline Characters from String in Snowflake?
Comments Off on How to Remove Newline Characters from String in Snowflake?
September 10, 2020
General

What is isnull Alternative in Teradata SQL?

The relational databases such as SQL server supports isnull function. It returns the alternate value if input argument or expression is null. But, Teradata does not support isnull function. Instead, you have to use an alternate method to replace null values. In this article, we will check what is isnull alternative in Teradata SQL with some examples. isnull Function Alternative in Teradata SQL As mentioned earlier, Teradata does not support isnull function. There are many alternative methods that you can use as an alternative to isnull function. The SQL Server ISNULL() function…

Continue ReadingWhat is isnull Alternative in Teradata SQL?
Comments Off on What is isnull Alternative in Teradata SQL?
September 9, 2020
Snowflake

How to Remove Spaces in the String in snowflake?

In a data warehouse, you will receive data from multiple sources. You may have to pre-process the data before loading it to target table. The pre-process step such as removing white spaces from data is commonly used. In this article, we will check how to remove spaces in a string using Snowflake built-in functions. Remove Spaces in a String in snowflake Nowadays, data is required everywhere. Many organizations automatically capture the data using tools or machines. Machines may introduce the unwanted data such as white space when it captures the…

Continue ReadingHow to Remove Spaces in the String in snowflake?
Comments Off on How to Remove Spaces in the String in snowflake?
September 8, 2020