Resources

These functions are used to create resources from SQL definitions and deployment-specific options.

CREATE Function

Attribute	Value
Name	`create_function`
ID	`bqtools.[region].create_function`
Version	`bqtools:v1.0.0`
Description	Creates or replaces a sql user-defined function defined by an arbitrary query statement and a set of deployment options.
Type	`PROCEDURE`
Arguments	`function_id STRING, query_statement STRING, function_arguments ARRAY<STRUCT<name STRING, data_type STRING>>, function_options JSON`
Returns	`None`
Dependencies	`bqtools-qb.[region].create_function`

EXECUTION

EUUS

DECLARE function_id, query_statement STRING;
DECLARE function_arguments ARRAY<STRUCT<name STRING, data_type STRING>>;
DECLARE function_options JSON;

SET function_id = 'project_id.dataset_name.function_name';
SET function_arguments = [("string_argument", "STRING")];
SET query_statement = 'SELECT 1 AS example';

SELECT bqtools.eu.create_function(function_id, query_statement, function_arguments, function_options);

DECLARE function_id, query_statement STRING;
DECLARE function_arguments ARRAY<STRUCT<name STRING, data_type STRING>>;
DECLARE function_options JSON DEFAULT JSON '{}';

SET function_id = 'project_id.dataset_name.function_name';
SET function_arguments = [("string_argument", "STRING")];
SET query_statement = 'SELECT 1 AS example';

SELECT bqtools.us.create_function(function_id, query_statement, function_arguments, function_options);

OPTIONS

OptionsDefault Options

The following deployment options can be set using the function_options JSON argument, which is a subset of the CREATE FUNCTION DDL options.

Option	Option Data Type	Values	JSON Path	Default
REPLACE	`BOOLEAN`	`true`/`false`	`replace_function`	`true`
TEMPORARY	`BOOLEAN`	`true`/`false`	`temporary_function`	`false`
IF NOT EXISTS	`BOOLEAN`	`true`/`false`	`if_not_exists`	`false`
DESCRIPTION	`STRING`	`Function description`	`description`	`None`

If the function_options JSON argument is NULL, the default options will correspond to the following DDL statement:

CREATE OR REPLACE FUNCTION `[function_id]`([function_arguments])
AS
[query_statement]

CREATE Procedure

Attribute	Value
Name	`create_procedure`
ID	`bqtools.[region].create_procedure`
Version	`bqtools:v1.0.0`
Description	Creates or replaces a sql user-defined function defined by an arbitrary query statement and a set of deployment options.
Type	`PROCEDURE`
Arguments	`procedure_id STRING, query_statement STRING, procedure_arguments ARRAY<STRUCT<name STRING, data_type STRING>>, procedure_options JSON`
Returns	`None`
Dependencies	`bqtools-qb.[region].create_procedure`

EXECUTION

EUUS

DECLARE procedure_id, query_statement STRING;
DECLARE procedure_arguments ARRAY<STRUCT<name STRING, data_type STRING>>;
DECLARE procedure_options JSON DEFAULT JSON '{}';

SET procedure_id = 'project_id.dataset_name.procedure_name';
SET procedure_arguments = [("string_argument", "STRING")];

SELECT bqtools.eu.create_procedure(procedure_id, query_statement, procedure_arguments, procedure_options);

DECLARE procedure_id, query_statement STRING;
DECLARE procedure_arguments ARRAY<STRUCT<name STRING, data_type STRING>>;
DECLARE procedure_options JSON;

SET procedure_id = 'project_id.dataset_name.procedure_name';
SET procedure_arguments = [("string_argument", "STRING")];

SELECT bqtools.us.create_procedure(procedure_id, query_statement, procedure_arguments, procedure_options);

OPTIONS

OptionsDefault Options

The following deployment options can be set using the procedure_options JSON argument, which is a subset of the CREATE PROCEDURE DDL options.

Option	Option Data Type	Values	JSON Path	Default
REPLACE	`BOOLEAN`	`true`/`false`	`replace_procedure`	`true`
TEMPORARY	`BOOLEAN`	`true`/`false`	`temporary_procedure`	`false`
IF NOT EXISTS	`BOOLEAN`	`true`/`false`	`if_not_exists`	`false`
DESCRIPTION	`STRING`	`Function description`	`description`	`None`

If the procedure_options JSON argument is NULL, the default options will correspond to the following DDL statement:

CREATE OR REPLACE PROCEDURE `[procedure_id]`([procedure_arguments])
AS
[query_statement]

CREATE Table

Attribute	Value
Name	`create_table`
ID	`bqtools.[region].create_table`
Version	`bqtools:v1.0.0`
Description	Creates or replaces a single base table as the results of an arbitrary sql statement and a set of deployment options.
Type	`PROCEDURE`
Arguments	`table_id STRING, query_statement STRING, table_options JSON`
Returns	`None`
Dependencies	`bqtools-qb.[region].create_table`

EXECUTION

EUUS

DECLARE table_id, query_statement STRING;
DECLARE table_options JSON;

SET table_id = 'project_id.dataset_name.table_name';

SELECT bqtools.eu.create_table(table_id, query_statement, table_options);

DECLARE table_id, query_statement STRING;
DECLARE table_options JSON DEFAULT JSON '{}';

SET table_id = 'project_id.dataset_name.table_name';

SELECT bqtools.us.create_table(table_id, query_statement, table_options);

OPTIONS

OptionsDefault Options

The following deployment options can be set using the table_options JSON argument, which is a subset of the CREATE TABLE DDL options.

Option	Option Data Type	Values	JSON Path	Default
REPLACE	`BOOLEAN`	`true`/`false`	`replace_procedure`	`true`
TEMPORARY	`BOOLEAN`	`true`/`false`	`temporary_procedure`	`false`
IF NOT EXISTS	`BOOLEAN`	`true`/`false`	`if_not_exists`	`false`
DESCRIPTION	`STRING`	`Function description`	`description`	`None`
PARTITION EXPRESSION	`STRING`	`Partition expression`	`partition_expression`	`None`
CLUSTERING COLUMN LIST	`ARRAY<STRING>`	`Clustering columns`	`clustering_column_list`	`None`
EXPIRATION TIMESTAMP	`STRING`	`Timestamp`	`expiration_timestamp`	`None`
PARTITION EXPIRATION DAYS	`INTEGER`	`Integer`	`partition_expiration_days`	`None`
REQUIRE PARTITION FILTER	`BOOLEAN`	`true`/`false`	`require_partition_filter`	`false`
KMS KEY NAME	`STRING`	`KMS key name`	`kms_key_name`	`None`
FRIENDLY NAME	`STRING`	`Friendly name`	`friendly_name`	`None`
DEFAULT ROUNDING MODE	`STRING`	`Default rounding mode`	`default_rounding_mode`	`None`

If the table_options JSON argument is NULL, the default options will correspond to the following DDL statement:

CREATE OR REPLACE TABLE `[procedure_id]`
AS
[query_statement]

CREATE Table Function

Attribute	Value
Name	`create_table_function`
ID	`bqtools.[region].create_table_function`
Version	`bqtools:v1.0.0`
Description	Creates or replaces a table function defined by an arbitrary sql statement and a set of deployment options.
Type	`PROCEDURE`
Arguments	`table_function_id STRING, query_statement STRING, table_function_arguments ARRAY<STRUCT<name STRING, data_type STRING>>, table_function_options JSON`
Returns	`None`
Dependencies	`bqtools-qb.[region].create_table_function`

EXECUTION

EUUS

DECLARE table_function_id, query_statement STRING;
DECLARE table_function_arguments ARRAY<STRUCT<name STRING, data_type STRING>>;
DECLARE table_function_options JSON;

SET table_function_id = 'project_id.dataset_name.table_function_name';
SET table_function_arguments = [("string_argument", "STRING")];
SET query_statement = 'SELECT 1 AS example';

SELECT bqtools.eu.create_table_function(table_function_id, query_statement, table_function_arguments, table_function_options);

DECLARE table_function_id, query_statement STRING;
DECLARE table_function_arguments ARRAY<STRUCT<name STRING, data_type STRING>>;
DECLARE table_function_options JSON;

SET table_function_id = 'project_id.dataset_name.table_function_name';
SET table_function_arguments = [("string_argument", "STRING")];
SET query_statement = 'SELECT 1 AS example';

SELECT bqtools.us.create_table_function(table_function_id, query_statement, table_function_arguments, table_function_options);

OPTIONS

OptionsDefault Options

The following deployment options can be set using the table_function_options JSON argument, which is a subset of the CREATE TABLE FUNCTION DDL options.

Option	Option Data Type	Values	JSON Path	Default
REPLACE	`BOOLEAN`	`true`/`false`	`replace_procedure`	`true`
IF NOT EXISTS	`BOOLEAN`	`true`/`false`	`if_not_exists`	`false`
DESCRIPTION	`STRING`	`Function description`	`description`	`None`

If the function_options JSON argument is NULL, the default options will correspond to the following DDL statement:

CREATE OR REPLACE TABLE FUNCTION `[procedure_id]`([procedure_arguments])
AS
[query_statement]

CREATE Table from DBTF

Attribute	Value
Name	`create_table_from_date_bounded_table_function`
ID	`bqtools.[region].create_table_from_date_bounded_table_function`
Version	`bqtools:v1.0.0`
Description	Creates or replaces a single base table as a date partition range from a date-bounded table function, with a set of deployment options.
Type	`PROCEDURE`
Arguments	`destination_table_id STRING, source_table_function_id STRING, start_date DATE, end_date DATE, table_options JSON`
Returns	`None`
Dependencies	`bqtools-qb.[region].create_table`

EXECUTION

EUUS

DECLARE destination_table_id, source_table_function_id STRING;
DECLARE start_date, end_date DATE;
DECLARE table_options JSON;

SET destination_table_id = 'project_id.dataset_name.destination_table_name';
SET source_table_function_id = 'project_id.dataset_name.source_table_function_name';

SELECT bqtools.eu.create_table_function(table_function_id, query_statement, table_function_arguments, table_function_options);

DECLARE destination_table_id, source_table_function_id STRING;
DECLARE start_date, end_date DATE;
DECLARE table_options JSON;

SET destination_table_id = 'project_id.dataset_name.destination_table_name';
SET source_table_function_id = 'project_id.dataset_name.source_table_function_name';

SELECT bqtools.us.create_table_function(table_function_id, query_statement, table_function_arguments, table_function_options);

OPTIONS

OptionsDefault Options

The following deployment options can be set using the table_options JSON argument, which is a subset of the CREATE TABLE DDL options.

Option	Option Data Type	Values	JSON Path	Default
REPLACE	`BOOLEAN`	`true`/`false`	`replace_procedure`	`true`
TEMPORARY	`BOOLEAN`	`true`/`false`	`temporary_procedure`	`false`
IF NOT EXISTS	`BOOLEAN`	`true`/`false`	`if_not_exists`	`false`
DESCRIPTION	`STRING`	`Function description`	`description`	`None`
PARTITION EXPRESSION	`STRING`	`Partition expression`	`partition_expression`	`None`
CLUSTERING COLUMN LIST	`ARRAY<STRING>`	`Clustering columns`	`clustering_column_list`	`None`
EXPIRATION TIMESTAMP	`STRING`	`Timestamp`	`expiration_timestamp`	`None`
PARTITION EXPIRATION DAYS	`INTEGER`	`Integer`	`partition_expiration_days`	`None`
REQUIRE PARTITION FILTER	`BOOLEAN`	`true`/`false`	`require_partition_filter`	`false`
KMS KEY NAME	`STRING`	`KMS key name`	`kms_key_name`	`None`
FRIENDLY NAME	`STRING`	`Friendly name`	`friendly_name`	`None`
DEFAULT ROUNDING MODE	`STRING`	`Default rounding mode`	`default_rounding_mode`	`None`

If the table_options JSON argument is NULL, the default options will correspond to the following DDL statement:

CREATE OR REPLACE TABLE `[destination_table_id]`
AS
SELECT * FROM `[source_table_function_id]`([start_date], [end_date])

CREATE View

Attribute	Value
Name	`create_view`
ID	`bqtools.[region].create_view`
Version	`bqtools:v1.0.0`
Description	Creates or replaces a single view defined by an arbitrary sql statement and a set of deployment options.
Type	`PROCEDURE`
Arguments	`view_id STRING, query_statement STRING, view_options JSON`
Returns	`None`
Dependencies	`bqtools-qb.[region].create_view`

EXECUTION

EUUS

DECLARE view_id, query_statement STRING;
DECLARE view_options JSON;

SET view_id = 'project_id.dataset_name.view_name';
SET query_statement = 'SELECT 1 AS example';

SELECT bqtools.eu.create_view(view_id, query_statement, view_options);

DECLARE view_id, query_statement STRING;
DECLARE view_options JSON;

SET view_id = 'project_id.dataset_name.view_name';
SET query_statement = 'SELECT 1 AS example';

SELECT bqtools.us.create_view(view_id, query_statement, view_options);

OPTIONS

OptionsDefault Options

The following deployment options can be set using the view_options JSON argument, which is a subset of the CREATE VIEW DDL options.

Option	Option Data Type	Values	JSON Path	Default
REPLACE	`BOOLEAN`	`true`/`false`	`replace_view`	`true`
IF NOT EXISTS	`BOOLEAN`	`true`/`false`	`if_not_exists`	`false`
DESCRIPTION	`STRING`	`View description`	`description`	`None`
EXPIRATION TIMESTAMP	`STRING`	`Timestamp`	`expiration_timestamp`	`None`
FRIENDLY NAME	`STRING`	`Friendly name`	`friendly_name`	`None`

If the view_options JSON argument is NULL, the default options will correspond to the following DDL statement:

CREATE OR REPLACE VIEW `[view_id]`
AS
[query_statement]