GCP BigQuery

Reuse Column Aliases in BigQuery – Lateral Column alias

BigQuery lateral Column alias are columns that are derived from the previously computed columns in same SELECT statement. Derived columns or lateral Column alias are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many PostgreSQL relational databases such as Netezza supports reuse of the column aliases within the same SELECT statement but GCP BigQuery does not support reuse of calculated derived columns. In this article, we will identify the alternate methods reuse column aliases in Google…

Continue ReadingReuse Column Aliases in BigQuery – Lateral Column alias
Comments Off on Reuse Column Aliases in BigQuery – Lateral Column alias
April 18, 2023
GCP BigQuery

Google BigQuery GROUP BY CUBE Alternative and Example

In my other BigQuery related articles, we have seen BigQuery grouping sets alternative, BigQuery control flow statements, NVL and NVL2 alternatives in BigQuery, cursors in BigQuery, etc. In this article, we will check one of the important GROUP BY extensions GROUP BY CUBE alternative in Google BigQuery. Google BigQuery GROUP BY CUBE SQL GROUP BY CUBE in Google BigQuery GROUP BY CUBE is an extension of the GROUP BY clause similar to GROUP BY ROLLUP and GROUPING SETS which is used to analyzes data by grouping it into multiple dimensions. In addition to…

Continue ReadingGoogle BigQuery GROUP BY CUBE Alternative and Example
Comments Off on Google BigQuery GROUP BY CUBE Alternative and Example
September 15, 2022
GCP BigQuery

Google BigQuery Grouping Sets Alternative and Example

In my other BigQuery related articles, we have seen BigQuery control flow statements, NVL and NVL2 alternatives in BigQuery, cursors in BigQuery, etc. In this article, we will check one of the important GROUP BY extensions GROUPING SETS alternative in Google BigQuery. Google BigQuery Grouping Sets SQL GROUPING SETS in Google BigQuery Many modern day analytics databases support GROUPING SETS. The GROUPING SETS option in SQL gives you an ability to combine multiple GROUP BY clauses into one GROUP BY clause. By definition, a grouping set is a group of…

Continue ReadingGoogle BigQuery Grouping Sets Alternative and Example
Comments Off on Google BigQuery Grouping Sets Alternative and Example
September 15, 2022
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
GCP BigQuery

What is SQL Cursor Alternative in BigQuery?

SQL Cursor is a database object to retrieve data from a result set one row at a time. The cursor are useful in many scenarios such as updating records using loops, inserting only required records, etc. You can think cursor as a temporary workspace provisioned in database system memory when a SQL query is executed. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is SQL cursor alternative in Google BigQuery with an example. Cursors…

Continue ReadingWhat is SQL Cursor Alternative in BigQuery?
Comments Off on What is SQL Cursor Alternative in BigQuery?
October 28, 2020
GCP BigQuery

BigQuery Recursive Query Alternative – Example

You can use recursive query to identify the hierarchies of data, such as an organizational structure, employee-manager, bill-of-materials, and document hierarchy. Many relational databases such as Teradata, Oracle, etc. supports recursive queries, but Google BigQuery does not support all features. One of such features is Recursive CTE or VIEWS. Google BigQuery does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Google BigQuery Recursive Query Alternative with an working example. BigQuery Recursive Query Alternative As mentioned earlier,…

Continue ReadingBigQuery Recursive Query Alternative – Example
Comments Off on BigQuery Recursive Query Alternative – Example
October 16, 2020
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