GCP BigQuery

NVL and NVL2 Functions in BigQuery and Alternatives

Like many relational databases, GCP BigQuery supports many useful built-in functions. Not all the functions those are available in other relational databases are supported in BigQuery. But, BigQuery provides alternative built-in functions that you can use. In this article, we will check NVL and NVL2 functions in BigQuery database. NVL and NVL2 Functions in BigQuery The databases such as Redshift, Teradata, Vertica, Netezza, Oracle, etc. supports standard NULL handling functions such as NVL and NVL2. But as of now, BigQuery supports alternative functions in place of NVL and NVL2. NVL…

Continue ReadingNVL and NVL2 Functions in BigQuery and Alternatives
Comments Off on NVL and NVL2 Functions in BigQuery and Alternatives
January 13, 2022
Redshift

Redshift SELECT INTO Variable – Example

The relational database that supports procedural language allows you to assign a value to local variable using SELECT statement within stored procedures. The databases such as Teradata, Oracle supports SELECT INTO clause to assign a value to a local variable. In this article, we will check how to assign a subquery value to a local variable using Redshift SELECT INTO clause within Stored Procedures. Redshift SELECT INTO Variable The SELECT INTO statement in Redshift retrieves data from one or more database tables, and assigns the selected values to variables. You can use SELECT…

Continue ReadingRedshift SELECT INTO Variable – Example
Comments Off on Redshift SELECT INTO Variable – Example
December 13, 2021
Redshift

What is ENDS_WITH Function Alternative in Redshift?

The relational databases such as Oracle and cloud databases such as BigQuery, Snowflake supports ends with function. The function name may be different, but the functionality is same. For example, Oracle and BigQuery provides ENDS_WITH function and Snowflake contains ENDSWITH function. The Amazon Redshift does not provide ENDS_WITH function. In my other article, What is STARTS_WITH Function Alternative in Redshift, we have seen starts_with alternative in Redshift. In this article, we will check what is ENDS_WITH function alternative in AWS Redshift? ENDS_WITH Function In Redshift Not all the string functions…

Continue ReadingWhat is ENDS_WITH Function Alternative in Redshift?
Comments Off on What is ENDS_WITH Function Alternative in Redshift?
November 30, 2021
Redshift

What is STARTS_WITH Function Alternative in Redshift?

The relational databases such as Oracle and cloud databases such as BigQuery, Snowflake supports starts with function. The function name may be different, but the functionality is same. For example, Oracle and BigQuery provides STARTS_WITH function and Snowflake contains STARTSWITH function. The Amazon Redshift does not provide STARTS_WITH function. In my other article, What is ENDS_WITH Function Alternative in Redshift, we have seen ends_with alternative in Redshift. In this article, we will check what is STARTS_WITH function alternative in AWS Redshift? STARTS_WITH Function In Redshift Not all the string functions…

Continue ReadingWhat is STARTS_WITH Function Alternative in Redshift?
Comments Off on What is STARTS_WITH Function Alternative in Redshift?
November 30, 2021
General

How to Generate Teradata Object DDL? – Tables,Views,UDF,SP,Macros

Teradata database is one of the widely used relational databases. The Teradata system is available on premises and cloud version. Teradata supports many useful tools such as Fast Export, Fast Load, BTEQ, etc. But, there are no tools to pull object DDL's from the Teradata database. In this article, we will check one of the administrative tasks, How to Generate Teradata Object DDL using System Tables and show command. We will generate DDL's for Teradata objects such as tables, views, stored procedures, macros and user defined functions. How to Generate…

Continue ReadingHow to Generate Teradata Object DDL? – Tables,Views,UDF,SP,Macros
Comments Off on How to Generate Teradata Object DDL? – Tables,Views,UDF,SP,Macros
October 8, 2021
Snowflake

How to Get Most Queried Table in Snowflake? Query

Snowflake is a fully managed cloud data warehouse solution. It is fast, reliable and comes with near zero management. Similar to many relational databases such as AWS Redshift, Azure Synapse, Snowflake support many system tables and views. Snowflake system tables store more rich and important information such as table usage, records count, constraint details, etc. In this article, we will check How to get most queried table in Snowflake using system tables present in the information schema. How to Get Most Queried Table in Snowflake? Snowflake maintains the important information…

Continue ReadingHow to Get Most Queried Table in Snowflake? Query
Comments Off on How to Get Most Queried Table in Snowflake? Query
August 12, 2021
Snowflake

Snowflake NULL Handling Functions, Usage and Examples

A NULL value in a relational database is a special marker used in SQL to indicate that a data value is UNKNOWN or does not exist in the database. In other words, a NULL value is just a placeholder to denote values that are missing or it is unknown. Snowflake supports NULL handling functions that are available in other cloud data warehouse such as Redshift, Azure Synapse, etc. Along with those, Snowflake also supports many extended functions to handle null values. In this article, we will check Snowflake NULL handling functions, usage and some examples. Snowflake NULL…

Continue ReadingSnowflake NULL Handling Functions, Usage and Examples
Comments Off on Snowflake NULL Handling Functions, Usage and Examples
August 3, 2021
Snowflake

How to Create View Dynamically in Snowflake?

The best part about Snowflake is that it supports almost all features that are available in any modern data warehouse. One of such a feature is dynamic SQL. You can execute SQL statements dynamically using Snowflake stored procedures. Snowflake supports JavaScript API to write stored procedures and user-defined functions. Note that, there is a difference between Snowflake stored procedure and UDFs. We will use stored procedures to create a view dynamically in Snowflake. Create View Dynamically in Snowflake In a data warehouse application, you will always get requirement to write…

Continue ReadingHow to Create View Dynamically in Snowflake?
Comments Off on How to Create View Dynamically in Snowflake?
July 30, 2021
Snowflake

How to use Conditional Insert into Snowflake Tables?

Snowflake cloud data warehouse supports many useful features that are not yet available in many other similar databases. One of such features is multi-table INSERT. You can insert one or more rows from query into one or more table. Syntax support both conditional and unconditional inserts. This feature is useful when you are inserting data using some conditions such as insert only positive rows to the particular table. In this article, we will check how to use conditional and unconditional insert into Snowflake tables, its syntax and examples. Conditional and…

Continue ReadingHow to use Conditional Insert into Snowflake Tables?
Comments Off on How to use Conditional Insert into Snowflake Tables?
July 28, 2021
Snowflake

How to Drop All SQL Variables in Snowflake Session?

Snowflake supports SQL session variable declaration by the user. SQL variable serves many purposes such as storing application specific environmental variables. You can also use SQL variables to create parameterized views or parameterized query. Once the variables are defined, you can explicitly use UNSET command to reset the SQL variables. In this article, we will check how to drop or reset all SQL variables in Snowflake session. Snowflake SQL Variables Before going into methods to reset all SQL variables, let us check how to define a SQL variable and reset…

Continue ReadingHow to Drop All SQL Variables in Snowflake Session?
Comments Off on How to Drop All SQL Variables in Snowflake Session?
July 27, 2021