To user-friendly SQL with love from BigQuery

Thirty five years ago, SQL-86, the first SQL standard, came into our world, published as an ANSI standard in 1986 and adopted by the International Standards Organization (ISO) in 1987. On this Valentine’s Day, we, in BigQuery, reaffirm our love and commitment to user-friendly SQL through a whole slew of new SQL features that we’re pleased to share with you, our beloved BigQuery users.

Expanded Datatypes

INTERVAL datatype

They say time and tide wait for no man. Now, thanks to the INTERVAL data type, you can measure the duration of time within BigQuery. This datatype allows you to save the difference between a start and an end timestamp in a native datatype in units ranging from years to fractions of a second with sign.

Change column datatype

In a prior BigQuery user-friendly SQL update, we announced support for parameterized datatypes in BigQuery. Building on this, BigQuery now support the ability to change the datatype of an existing column to make it less restrictive. Using the SET DATA TYPE clause, a NUMERIC data type can be changed to a BIGNUMERIC type or the length or precision & scale of a parameterized datatype column  can be increased. For a table of valid data type coercions, compare the "From Type" column to the "Coercion To" column in the Conversion rules in Standard SQL page.

To learn about the new JSON data type, read Announcing preview of BigQuery’s native support for semi-structured data.

Expanded SQL Expressions and Scripting Control Statements

WITH RECURSIVE common table expression

A common table expression (CTE) referenced using a WITH clause in a query allow the user to break up a complex query by allowing a temporary table containing the results of the CTE subquery which can then be referenced in other parts of the same query as a table. A recursive CTE referenced using a WITH RECURSIVE clause containting a UNION ALL operation has the following parts:

• base_term: Runs the initial iteration of the recursive operation.
• recursive_term: Runs the remaining iterations until the recursion terminates.
• union_operator: The UNION operator returns the rows that are from the union of the base term and recursive term.

Control statements in Scripting

As business logic to analyze data becomes more complex, control statements in scripting allow data analysts to apply conditional logic to execute different workflows based on specific conditions encountered during script execution. BigQuery is pleased to support the following additional control statements in scripting:

• FOR…IN: loops over every row in a table expression. This offers a succinct way to iterate through query results that other loops do not.
• REPEAT: repeatedly executes a list of SQL statements until the boolean condition at the end of the list is TRUE
• CASE: Provides a more efficient SQL expression to execute conditional logic that previously supported IF…ELSE IF statements. It executes the first list of SQL statements where a boolean expression is TRUE.
• CASE <search expression>: The CASE statement with the search expression executes the first list of SQL statements where the search expression matches a WHEN expression.
• Labels:  provides an unconditional jump to the end of the block or loop associated with a label. With labeled BREAK or CONTINUE, users now have more control over nested loops or statement bodies by skipping to specific (named) locations in the script instead of continuing with sequential execution.

Table copy DDL

CREATE TABLE LIKE and COPY

Analysts and data engineers often need to make a copy of a table schema (without data) or a full table copy (with data) from a production into a test or development environment. The CREATE TABLE LIKE statement copies only the metadata of the source table while the CREATE TABLE COPY statement copies both the metadata and data from the source table into the new table. The new table for both CREATE TABLE operations has no relationship to the source table after creation; thus modifications to the source table will not propagate to the new table.

To learn about DDL support for table snapshots, read Quickly, easily and affordably back up your data with BigQuery table snapshots.

Expanded INFORMATION_SCHEMA views

INFORMATION SCHEMA for streaming data

If you stream data into BigQuery, you can now monitor your data streams using INFORMATION_SCHEMA streaming views to retrieve historical and real-time information about data streaming into BigQuery. These views contain per minute aggregated statistics for each table that have data streamed into them.

Expanded DDL column support in INFORMATION_SCHEMA views

Last year, we announced DDL column support in INFORMATION SCHEMA views - an innovative approach which allows data administrators to generate object creation DDL for one, multiple or all tables and views directly from the TABLES INFORMATION_SCHEMA view. BigQuery now supports the ability to generate object creation DDL for other object types such as schemata (datasets) and routines (functions, table functions and procedures).

We hope you love these new user-friendly SQL features from BigQuery. To learn more, visit the BigQuery page and try BigQuery for free using the BigQuery Sandbox.