Table functions (built in)

GoogleSQL for BigQuery supports built-in table functions.

This topic includes functions that produce columns of a table. You can only use these functions in the FROM clause.

Function list

Name Summary
APPENDS Gets all rows that are appended to a table for a given time range.
EXTERNAL_OBJECT_TRANSFORM Produces an object table with the original columns plus one or more additional columns.
GAP_FILL Finds and fills gaps in a time series.
RANGE_SESSIONIZE Produces a table of session ranges.

APPENDS

Gets all rows that are appended to a table for a given time range. For more information, see APPENDS TVF.

EXTERNAL_OBJECT_TRANSFORM

EXTERNAL_OBJECT_TRANSFORM(TABLE object_table_name, transform_types_array)

Description

This function returns a transformed object table with the original columns plus one or more additional columns, depending on the transform_types values specified.

This function only supports object tables as inputs. Subqueries or any other types of tables are not supported.

object_table_name is the name of the object table to be transformed, in the format dataset_name.object_table_name.

transform_types_array is an array of STRING literals. Currently, the only supported transform_types_array value is SIGNED_URL. Specifying SIGNED_URL creates read-only signed URLs for the objects in the identified object table, which are returned in a signed_url column. Generated signed URLs are valid for 6 hours.

Return Type

TABLE

Example

Run the following query to return URIs and signed URLs for the objects in the mydataset.myobjecttable object table.

SELECT uri, signed_url
FROM EXTERNAL_OBJECT_TRANSFORM(TABLE mydataset.myobjecttable, ['SIGNED_URL']);

--The preceding statement returns results similar to the following:
/*-----------------------------------------------------------------------------------------------------------------------------*
 |  uri                                 | signed_url                                                                           |
 +-----------------------------------------------------------------------------------------------------------------------------+
 | gs://myobjecttable/1234_Main_St.jpeg | https://storage.googleapis.com/mybucket/1234_Main_St.jpeg?X-Goog-Algorithm=1234abcd… |
 +-----------------------------------------------------------------------------------------------------------------------------+
 | gs://myobjecttable/345_River_Rd.jpeg | https://storage.googleapis.com/mybucket/345_River_Rd.jpeg?X-Goog-Algorithm=2345bcde… |
 *-----------------------------------------------------------------------------------------------------------------------------*/

GAP_FILL

Finds and fills gaps in a time series. For more information, see GAP_FILL in Time series functions.

RANGE_SESSIONIZE

For more information, see RANGE_SESSIONIZE in Range functions.