Shine on with user-friendly SQL capabilities in BigQuery

June is the month which holds the summer solstice, and (at least in the northern hemisphere) we enjoy the longest days of sunshine out of the entire year. Just as the sun is making its longest trips across the sky, the BigQuery team is delighted to announce our next set of user-friendly SQL features.

These new capabilities come in three themes: Powerful Analytics Features, Flexible Columns, and Secure with SQL. 

Powerful Analytics Features (Continued)

Last month, we announced Powerful Analytics Features for advanced filtering of analytic functions with QUALIFY, and pivoting columns with aggregation using PIVOT & UNPIVOT. This month, we continue to bring valuable analytics capabilities by enabling BigQuery users to encode business logic in user-defined functions using table functions. 

Table function (Preview)

BigQuery users often persist and share business logic using user-defined functions (UDFs), extending BigQuery’s SQL functionality. UDFs accept scalar arguments as input and calculate a scalar value as output. But many customers require the ability to support functions that can output a table. 

We are excited to share that BigQuery now supports table functions, also known as table-valued functions (TVFs). These functions output an entire table instead of a single scalar value, making them extremely versatile tools for an analyst’s SQL toolkit. 

We use the bigquery-public-data.usa_names.usa_1910_current to walk you through potential use cases here.

Flexible Columns

For your ever-changing business needs, BigQuery offers greater granularity and flexibility for managing your column data types, descriptions, and aliases. 

Parameterized Data Types (Preview)

Business data often requires additional precision on data types to enforce business rules. In response to this, BigQuery’s standard data types support the specification of additional parameters on these data types to allow for more targeted enforcement of business data rules. 

For instance, you may want to use BigQuery to store ISO country codes as 2-digit characters. For this use case, there is a business requirement to ensure that records in this column have a maximum of two characters. To support business cases like this, BigQuery now supports parameterized data types, which allows you to set a max length parameter on this string column, as well as the following data types: 

• STRING(L)

• BYTES(L)

• NUMERIC(P) / NUMERIC(P, S)

• BIGNUMERIC(P) / BIGNUMERIC(P, S)

Below, we illustrate a customer creating a table with a field for credit_card_number stored as STRING. With parameterized data types, the customer can limit the field to 16 characters of length to make sure credit card number inputs do not contain extra characters.

Flexible Data Type Casting and Formatting (GA)

Temporal data are ubiquitous in data analytics workflows, and wrangling these temporal data to the required types and formats is a vital use case for BigQuery customers. 

For example, an analyst may need to convert a temporal data type (i.e. DATE) to a STRING data type while applying a specific formatting choice (i.e. YYYY/MM/DD). In this use case, a data type conversion and a formatting change are both required. Existing BigQuery capabilities such as CAST, along with Date functions and Datetime functions, are used by customers today for this use case. In addition to these, the BigQuery team is announcing new capabilities aligned with the ANSI standard to support flexible data type casting and formatting. 

First, we are adding the ability to include a FORMAT within a CAST function and STRING to NUMERIC/BIGNUMERIC conversion.

• FORMAT clause for CAST available for the following data types:

• String type

• Date type

• Datetime type

• Time type

• Timestamp type

• Numeric types

• Bytes type

• PARSE_NUMERIC to convert from STRING to NUMERIC

• PARSE_BIGNUMERIC to convert from STRING to BIGNUMERIC

Second, for the numeric data type INT64, we will support different new INT64 alias types. Now SQL analysts can use BYTEINT, TINYINT, SMALLINT, BIGINT, INT, and INTEGER as aliases for BigQuery’s INT64 numeric data type.

Third, we are adding a new function to convert STRING/BYTES data types to a GEOGRAPHY data type using ST_GEOGFROM.  Unlike existing functions such as ST_GEOGFROMWKB or ST_GEOGFROMTEXT which require the user to specify the input format, ST_GEOGFROM can automatically detect the input format.

Change a column’s description (GA)

While BigQuery has historically provided many tools available in the UI, CLI and APIs, we know that many administrators prefer interfacing with SQL. Last month, we released ALTER COLUMN DROP NOT NULL constraints, which allows the administrator to remove the NOT NULL constraint from a column BigQuery. This month, we provide an additional ALTER COLUMN DDL feature: 

• ALTER COLUMN SET OPTIONS allows the administrator to add a description to a specified column. 

The example below combines the two ALTER COLUMN features together for one use case: 

Secure through SQL

Rather than using the UI, CLI, or API to provision access to tables, datasets, and views, many administrators prefer the familiar SQL interface. Today, we’re excited to support provisioning and reviewing access privileges on tables, datasets, and views using SQL in BigQuery.  

GRANT and REVOKE access to tables, datasets and views (GA)

Here, we are adding two SQL features to BigQuery Standard SQL: GRANT and REVOKE. These will allow administrators to add and remove access privileges for users on tables, datasets, and views. 

Review object privileges with INFORMATION_SCHEMA (Preview)

INFORMATION_SCHEMA.OBJECT_PRIVILEGES shows explicit object privileges set on tables, datasets, and views (these are the same set of permissions returned with the BQ CLI). This familiar SQL interface enables administrators to ensure the right users have access to the right objects. 

In the example below, we see that these three SQL features can be used together to provision access to a dataset, and then ensure that the privileges on a given dataset are correct.

The summer solstice is celebrated around the world with many cultures and traditions. The BigQuery team hopes to celebrate with you as we continue to help your data analytics workflows shine. To learn more about BigQuery, visit our website, and get started immediately with the free BigQuery Sandbox.