BigQuery Rolls Out New Set Of SQL Features

These new capabilities will give BigQuery users a more user-friendly SQL.

Recently, Google announced its newest set of SQL features in BigQuery that provides new ways of storing and analyzing all your data.

The announcement brings the GA of BIGNUMERIC data type which supports 76 digits of precision and 38 digits of scale. Just like NUMERIC, BIGNUMERIC data type is available in all aspects of BigQuery from clustering to BI Engine. The new data type is also supported in the JDBC/ODBC drivers and client libraries.

Source: Google

BigQuery is also introducing the TABLESAMPLE clause in queries which allows you to sample a subset of the data, specified as a percentage of a table. TABLESAMPLE  clause can sample data from native BigQuery tables or external tables, stored in storage buckets in Google Cloud Storage. It randomly selects a percentage of data blocks from the table and reads all of the rows in the selected blocks, lowering query costs when trying ad hoc queries.

BigQuery now offers full SQL support (CREATE, ALTER and DROP) for dataset operations using SCHEMA. These operations significantly simplify data administrators’ ability to provision and manage schema across their BigQuery projects. 

The TABLES view in INFORMATION_SCHEMA in BigQuery now has a new column called DDL. This column contains the exact object creation DDL for every table, view and materialized view within the dataset. Now, in combination with dynamic SQL, you can quickly generate and execute the creation DDL commands for a specific object or all objects of a particular type.

BigQuery platform now also supports the DROP COLUMN clause (currently in Preview) as a part of the ALTER TABLE command to enable you to remove one or more of these columns.  You can now have longer column names up to 300 characters within tables, views and materialized views. Earlier the limit was of 128 characters.

Another new capability is 'PARTITIONS view' - as a part of BigQuery INFORMATION_SCHEMA. It gives you up-to-date information on tables or partitions of a table.

For additional details, you can visit the official announcement here.