Description

Great extension, I use this every day at work 👍

I find myself constantly doing the same corrections to the snippets to adhere to my workplace's style guide. However, if there is a way of customising the snippets that solve the problem.

Description

• Add supports new casting functions.
  • PARSE_BIGNUMERIC()
  • PARSE_NUMERIC()
  • Add FORMAT parameter to CAST() function

Add snippets

• PARSE_BIGNUMERIC()
  • Prefix
    parse_bignumeric
  • Body

    PARSE_BIGNUMERIC(${1:string_expression})

• PARSE_NUMERIC()
  • Prefix
    parse_numeric
  • Body

    PARSE_NUMERIC(${1:string_expression})

• CAST(... FORMAT)
  • Prefix
    cast format
  • Body

    CAST(${1:expr} AS ${2:STRING|BYTES} FORMAT "${3:format_string_expression}")

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_28_2021

Description

• Supports syntax highlighting
• Add snippets

Functions

• RANGE_BUCKET
  • Prefix
    range_bucket
  • body

    RANGE_BUCKET(point, boundaries_array)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#range_bucket

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_09_2019

Description

• Add new DDL statements snippets
• Change DDL statements snippets
• Delete DDL statements snippets

Add snippets

• CREATE SCHEMA
  • Prefix
    create schema
  • Body

    CREATE SCHEMA ${1:[IF NOT EXISTS]} `${2:project}.${3:dataset}`
    ${4:[OPTIONS (
      description = \"description\",
      default_kms_key_name = \"projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING]/cryptoKeys/[KEY]\",
      default_partition_expiration_days = 1,
      default_table_expiration_days = 1,
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]
    )]}

• CREATE MATERIALIZED VIEW
  • Prefix
    create materialized view
  • Body

    CREATE MATERIALIZED VIEW ${1:[IF NOT EXISTS] }`${2:project}.${3:dataset}.${4:materialized_view}`
    ${5:[PARTITION BY
      _PARTITIONDATE
      |DATE(_PARTITIONTIME)
      |<date_column>
      |DATE(<timestamp_column>|<datetime_column>)
      |DATETIME_TRUNC(<datetime_column>, {DAY|HOUR|MONTH|YEAR\\})
      |TIMESTAMP_TRUNC(<timestamp_column>, {DAY|HOUR|MONTH|YEAR\\})
      |TIMESTAMP_TRUNC(_PARTITIONTIME, {DAY|HOUR|MONTH|YEAR\\})
      |DATE_TRUNC(<date_column>, {MONTH|YEAR\\})
      |RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>]))]}
    ${6:[CLUSTER BY clustering_column_list]}
    ${7:[OPTIONS (
      description = \"description\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      enable_refresh = false,
      refresh_interval_minutes = 1440,
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]
    )]}

• CREATE EXTERNAL TABLE
  • Prefix
    create external table
  • Body

    CREATE ${1:[OR REPLACE] }EXTERNAL TABLE ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:external_table}`
    ${6:[(
      column type OPTIONS (description = \"comment\")
    )]}
    ${7:[WITH PARTITION COLUMNS]}
    ${8:[OPTIONS (
      description = \"description\",
      allow_jagged_rows = false,
      allow_quoted_newlines = false,
      compression = \"GZIP\",
      enable_logical_types = false,
      encoding = \"UTF8\"|\"ISO_8859_1\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      field_delimiter = \",\"|\"\\t\"|\"other\",
      format = \"AVRO\"|\"CSV\"|\"DATASTORE_BACKUP\"|\"GOOGLE_SHEETS\"|\"NEWLINE_DELIMITED_JSON\"|\"ORC\"|\"PARQUET\",
      decimal_target_types = [\"NUMERIC\", \"BIGNUMERIC\"],
      hive_partition_uri_prefix = \"gs://bucket/path\",
      ignore_unknown_values = false,
      max_bad_records = 0,
      null_marker = NULL,
      projection_fields = \"\",
      quote = \"\\\"\",
      require_hive_partition_filter = false,
      sheet_range = \"sheet1!A1:B20\",
      skip_leading_rows = 0,
      uris = [\"gs://bucket/path/*\"]
    )]}

• DROP SCHEMA
  • Prefix
    drop schema
  • Body

    DROP SCHEMA ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:dataset}` ${5:[CASCADE|RESTRICT]}

• DROP EXTERNAL TABLE
  • Prefix
    drop external table
  • Body

    DROP EXTERNAL TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:external_table}`

• DROP MATERIALIZED VIEW
  • Prefix
    drop materialized view
  • Body

    DROP MATERIALIZED VIEW ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:materialized_view}`

• DROP FUNCTION
  • Prefix
    drop function
  • Body

    DROP FUNCTION ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:functionName}`

• DROP PROCEDURE
  • Prefix
    drop procedure
  • Body

    DROP PROCEDURE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:ProcedureName}`

• ALTER SCHEMA SET OPTIONS
  • Prefix
    alter schema
  • Body

    ALTER SCHEMA ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:dataset}`
    SET OPTIONS (
    ${5:\tdescription = \"description\",
      default_kms_key_name = \"projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING]/cryptoKeys/[KEY]\",
      default_partition_expiration_days = 1,
      default_table_expiration_days = 1,
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]}
    )

• ALTER TABLE ADD COLUMN
  • Prefix
    alter table add column
  • Body

    ALTER TABLE `${1:project}.${2:dataset}.${3:table}`
      ADD COLUMN ${4:[IF NOT EXISTS]} ${5:column_name} ${6:type}${7: OPTIONS (description = \"comment\")}

• ALTER TABLE DROP COLUMN
  • Prefix
    alter table drop column
  • Body

    ALTER TABLE `${1:project}.${2:dataset}.${3:table}`
      DROP COLUMN ${4:[IF EXISTS]} ${5:column_name}

• ALTER MATERIALIZED VIEW SET OPTIONS
  • Prefix
    alter materialized view
  • Body

    ALTER MATERIALIZED VIEW ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:materialized_view}`
    SET OPTIONS (
    ${5:\tdescription = \"description\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      enable_refresh = false,
      refresh_interval_minutes = 1440,
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]}
    )

Change snippets

• CREATE TABLE
  • Prefix
    create table
  • Body

    CREATE ${1:[OR REPLACE] }TABLE ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:table}`
    (
      ${6:column} ${7:type}${8: OPTIONS (description = \"comment\")}
    )
    ${9:[PARTITION BY
      _PARTITIONDATE
      |DATE(_PARTITIONTIME)
      |<date_column>
      |DATE(<timestamp_column>|<datetime_column>)
      |DATETIME_TRUNC(<datetime_column>, {DAY|HOUR|MONTH|YEAR\\})
      |TIMESTAMP_TRUNC(<timestamp_column>, {DAY|HOUR|MONTH|YEAR\\})
      |TIMESTAMP_TRUNC(_PARTITIONTIME, {DAY|HOUR|MONTH|YEAR\\})
      |DATE_TRUNC(<date_column>, {MONTH|YEAR\\})
      |RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>]))]}
    ${10:[CLUSTER BY clustering_column_list]}
    ${11:[OPTIONS (
      description = \"description\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      partition_expiration_days = 1,
      require_partition_filter = false,
      kms_key_name = \"projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING]/cryptoKeys/[KEY]\",
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]
    )]}

• CREATE VIEW
  • Prefix
    create view
  • Body

    CREATE ${1:[OR REPLACE] }VIEW ${2:[IF NOT EXISTS] }`${4:project}.${5:dataset}.${6:view}`
    ${7:[OPTIONS (
      description = \"description\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]
    )]}
    AS
    SELECT
      ${8:column}
    FROM `${9:project}.${10:dataset}.${11:table}`

• PARTITION BY
  • Prefix
    partitionby
  • Body

    PARTITION BY
      ${1:_PARTITIONDATE}
      ${2:|DATE(_PARTITIONTIME)}
      ${3:|<date_column>}
      ${4:|DATE(<timestamp_column>|<datetime_column>)}
      ${5:|DATETIME_TRUNC(<datetime_column>, {DAY|HOUR|MONTH|YEAR\\})}
      ${6:|TIMESTAMP_TRUNC(<timestamp_column>, {DAY|HOUR|MONTH|YEAR\\})}
      ${7:|TIMESTAMP_TRUNC(_PARTITIONTIME, {DAY|HOUR|MONTH|YEAR\\})}
      ${8:|DATE_TRUNC(<date_column>, {MONTH|YEAR\\})}
      ${9:|RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>]))}
    ${10:[CLUSTER BY clustering_column_list]}

• CREATE TEMPORARY JavaScript FUNCTION
  • Prefix
    create temp function javascript
  • Body

    CREATE TEMP FUNCTION ${1:functionName}(${2:param_name param_type[, ...]})
    RETURNS ${3:data_type}
    LANGUAGE js
    ${4:[OPTIONS (library = [\"gs://bucket/path/file.js\"])]}
    AS \"\"\
      ${5:return \"expression\";}
    \"\"\";

• CREATE TEMPORARY SQL FUNCTION
  • Prefix
    create temp function sql
  • Body

    CREATE TEMP FUNCTION ${1:functionName}(${2:param_name param_type[, ...]})
    ${3:[RETURNS data_type]}
    AS (
      ${4:sql_expression}
    );

• CREATE JavaScript FUNCTION
  • Prefix
    create function javascript
  • Body

    CREATE ${1:[OR REPLACE] }FUNCTION ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:functionName}`(${6:param_name param_type[, ...]})
    RETURNS ${7:data_type}
    LANGUAGE js
    ${8:[OPTIONS (library = [\"gs://bucket/path/file.js\"])]}
    AS \"\"\
      ${9:return \"expression\";}
    \"\"\";

• CREATE SQL FUNCTION
  • Prefix
    create function sql
  • Body

    CREATE ${1:[OR REPLACE] }FUNCTION ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:functionName}`(${6:param_name param_type[, ...]})
    ${7:[RETURNS data_type]}
    AS (
      ${8:sql_expression}
    );

• CREATE PROCEDURE
  • Prefix
    create procedure
  • Body

    CREATE ${1:[OR REPLACE] }PROCEDURE ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:ProcedureName}`(${6:[IN|OUT|INOUT] arg_name arg_type[, ...]})
    ${7:[OPTIONS (strict_mode = TRUE|FALSE)]}
    BEGIN
      ${8:statements}
    END;

• CREATE MODEL
  • Prefix
    create model
  • Body

    CREATE ${1:[OR REPLACE] }MODEL ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:model}`
    ${6:[OPTIONS (model_option_list)]}
    AS
    ${7:query_statement}

• DROP TABLE
  • Prefix
    drop table
  • Body

    DROP TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:table}`

• DROP VIEW
  • Prefix
    drop view
  • Body

    DROP VIEW ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:view}`

• ALTER TABLE SET OPTIONS
  • Prefix
    alter table options
  • Body

    ALTER TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:table}`
    SET OPTIONS (
    ${5:\tdescription = \"description\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      partition_expiration_days = 1,
      require_partition_filter = false,
      kms_key_name = \"projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING]/cryptoKeys/[KEY]\",
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]}
    )

• ALTER VIEW SET OPTIONS
  • Prefix
    alter view
  • Body

    ALTER VIEW ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:view}`
    SET OPTIONS (
    ${5:\tdescription = \"description\",
      expiration_timestamp = TIMESTAMP \"YYYY-MM-DD HH:MI:SS UTC\",
      friendly_name = \"friendly_name\",
      labels = [(\"key\", \"value\")]}
    )

Delete snippets

• CREATE TABLE IF NOT EXISTS
• CREATE OR REPLACE TABLE
• CREATE TABLE ... PARTITION BY
• CREATE JavaScript FUNCTION IF NOT EXISTS
• CREATE SQL FUNCTION IF NOT EXISTS
• DROP TABLE IF EXISTS
• DROP VIEW IF EXISTS
• ALTER TABLE IF EXISTS SET OPTIONS
• ALTER VIEW IF EXISTS SET OPTIONS

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#April_30_2021
• https://cloud.google.com/bigquery/docs/release-notes#April_02_2021
• https://cloud.google.com/bigquery/docs/release-notes#March_31_2021
• https://cloud.google.com/bigquery/docs/release-notes#October_14_2020
• https://cloud.google.com/bigquery/docs/release-notes#September_21_2020
• https://cloud.google.com/bigquery/docs/release-notes#February_18_2020
• https://cloud.google.com/bigquery/docs/release-notes#December_20_2019
• https://cloud.google.com/bigquery/docs/release-notes#December_11_2019

Description

BigQuery UI switched from -- to # default line commenting option
It will be nice if the BigQuery visual code follows the same.

Additional Information

Example copy and paste code BigQuery

SELECT
[1,
# 2,
3] AS numbers

I try to uncomment row 3 in visual code, will create a new commenting using another symbol.

SELECT
[1,
-- # 2, 
3] AS numbers

No icon in sidebar after install

• I click install. Seems to complete. Files in .vscode and "application support" directories but i get no icon in sidebar.

Shouldn't it require me to configure BQ connection?

• Thanks for help.

Description

• Add BigQuery ML supports the DROP MODEL DDL statement for deleting models.

Additional Information

BigQuery Release notes

• https://cloud.google.com/bigquery-ml/docs/release-notes?hl=ja#may_14_2019

BigQuery Documents

• BigQuery ML > Documentation > The DROP MODEL statement

Description

• Change the use single line comment symbol from # to --
  • BigQuery UI reswitched comment symbol from # to --

Description

• Supports syntax highlighting
• Add snippets
• Change Temporary UDF snippets

UDFs

• CREATE TEMPORARY JavaScript FUNCTION
  • Prefix
    create function javascript -> create temp function javascript
  • body

    CREATE OR REPLACE TEMP FUNCTION functionName(param_name param_type[, ...])
    RETURNS data_type
    LANGUAGE js AS """
      return "expression";
    """;

• CREATE TEMPORARY SQL FUNCTION
  • Prefix
    create function sql -> create temp function sql
  • body

    CREATE OR REPLACE TEMP FUNCTION functionName(param_name param_type[, ...])
    [RETURNS data_type]
    AS (
      sql_expression
    );

• CREATE JavaScript FUNCTION
  • Prefix
    create function javascript
  • body

    CREATE OR REPLACE FUNCTION `project.dataset.functionName`(param_name param_type[, ...])
    RETURNS data_type
    LANGUAGE js AS """
      return "expression";
    """;

• CREATE SQL FUNCTION
  • Prefix
    create function sql
  • body

    CREATE OR REPLACE FUNCTION `project.dataset.functionName`(param_name param_type[, ...])
    [RETURNS data_type]
    AS (
      sql_expression
    );

• CREATE JavaScript FUNCTION IF NOT EXISTS
  • Prefix
    create function javascript if not exists
  • body

    CREATE FUNCTION IF NOT EXISTS `project.dataset.functionName`(param_name param_type[, ...])
    RETURNS data_type
    LANGUAGE js AS """
      return "expression";
    """;

• CREATE SQL FUNCTION IF NOT EXISTS
  • Prefix
    create function sql if not exists
  • body

    CREATE FUNCTION IF NOT EXISTS `project.dataset.functionName`(param_name param_type[, ...])
    [RETURNS data_type]
    AS (
      sql_expression
    );

Document

• https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions
• https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#July_01_2019

Description

• Add supports new Geography functions.
  • ST_GEOGFROMWKB
  • ST_GEOGPOINTFROMGEOHASH
  • ST_ASBINARY
  • ST_GEOHASH
  • ST_CENTROID_AGG
  • ST_CONVEXHULL
  • ST_DUMP
  • ST_SIMPLIFY
  • ST_CLUSTERDBSCAN
  • ST_NPOINTS
  • ST_X
  • ST_Y
  • ST_GEOGFROMTEXT

Add snippets

• ST_GEOGFROMWKB
  • Prefix
    st_geogfromwkb
  • Body

    ST_GEOGFROMWKB(${1:wkb_bytes_expression|wkb_hex_string_expression})

• ST_GEOGPOINTFROMGEOHASH
  • Prefix
    st_geogpointfromgeohash
  • Body

    ST_GEOGPOINTFROMGEOHASH(${1:geohash})

• ST_ASBINARY
  • Prefix
    st_asbinary
  • Body

    ST_ASBINARY(${1:geography_expression})

• ST_GEOHASH
  • Prefix
    st_geohash
  • Body

    ST_GEOHASH(${1:geography_expression}, ${2:maxchars})

• ST_CENTROID_AGG
  • Prefix
    st_centroid_agg
  • Body

    ST_CENTROID_AGG(${1:geography})

• ST_CONVEXHULL
  • Prefix
    st_convexhull
  • Body

    ST_CONVEXHULL(${1:geography_expression})

• ST_DUMP
  • Prefix
    st_dump
  • Body

    ST_DUMP(${1:geography}${2:[, dimension]})

• ST_SIMPLIFY
  • Prefix
    ST_SIMPLIFY
  • Body

    ST_SIMPLIFY(${1:geography}, ${2:tolerance_meters})

• ST_CLUSTERDBSCAN
  • Prefix
    st_clusterdbscan
  • Body

    ST_CLUSTERDBSCAN(${1:geography_column}, ${2:epsilon}, ${3:minimum_geographies}) OVER (${4:...})

• ST_NPOINTS
  • Prefix
    st_npoints
  • Body

    ST_NPOINTS(${1:geography_expression})

• ST_X
  • Prefix
    st_x
  • Body

    ST_X(${1:geography_expression})

• ST_Y
  • Prefix
    st_y
  • Body

    ST_Y(${1:geography_expression})

• ST_GEOGFROMTEXT
  • Prefix
    st_geogfromtext
  • Body

    ST_GEOGFROMTEXT(${1:wkt_string}${2:[, oriented => boolean_constant_1]}${3:[, planar => boolean_constant_2]}${4:[, make_valid => boolean_constant_3]})

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_02_2020
• https://cloud.google.com/bigquery/docs/release-notes#July_16_2020
• https://cloud.google.com/bigquery/docs/release-notes#October_22_2020

Description

• Add supports ALTER TABLE RENAME TO statement.

Add snippets

• ALTER TABLE RENAME TO
  • Prefix
    alter table rename
  • Body

    ALTER TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:table}`
      RENAME TO ${5:new_table_name}

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#May_19_2021

Description

• Add supports new DDL syntax.
  • ALTER COLUMN DROP NOT NULL
  • CREATE VIEW with column name list

Add snippets

• ALTER COLUMN DROP NOT NULL
  • Prefix
    alter column drop not null
  • Body

    ALTER TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:table}`
      ALTER COLUMN ${5:[IF EXISTS]} ${6:column_name} DROP NOT NULL

Change snippets

• CREATE VIEW
  • Prefix
    create view
  • Body

    CREATE ${1:[OR REPLACE] }VIEW ${2:[IF NOT EXISTS] }`${4:project}.${5:dataset}.${6:view}`
    ${7:[(
      column_name_list
    )]}
    ${8:[OPTIONS (
      description = "description",
      expiration_timestamp = TIMESTAMP "YYYY-MM-DD HH:MI:SS UTC",
      friendly_name = "friendly_name",
      labels = [("key", "value")]
    )]}
    AS
    SELECT
      ${9:column}
    FROM `${10:project}.${11:dataset}.${12:table}`

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#April_30_2021

Description

• Add supports Table functions (TVF) DDL statements.
  • CREATE TABLE FUNCTION
  • DROP TABLE FUNCTION

Add snippets

• CREATE TABLE FUNCTION
  • Prefix
    create table function
  • Body

    CREATE ${1:[OR REPLACE] }TABLE FUNCTION ${2:[IF NOT EXISTS] }`${3:project}.${4:dataset}.${5:functionName}`(${6:param_name data_type|ANY TYPE[, ...]})
    ${7:[RETURNS TABLE<column_name data_type[, ...]>]}
    AS (
      ${8:sql_query}
    );

• DROP TABLE FUNCTION
  • Prefix
    drop table function
  • Body

    DROP TABLE FUNCTION ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:functionName}`

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_28_2021

Description

• Add supports new DML syntax.
  • TRUNCATE TABLE

Add snippets

• TRUNCATE TABLE
  • Prefix
    truncate table
  • Body

    TRUNCATE TABLE `${1:project}.${2:dataset}.${3:table}`

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_14_2020

Description

• Add supports new ALTER COLUMN DDL statements.
  • ALTER COLUMN SET OPTIONS
  • ALTER COLUMN SET DATA TYPE

Add snippets

• ALTER COLUMN SET OPTIONS
  • Prefix
    alter column set options
  • Body

    ALTER TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:table}`
      ALTER COLUMN ${5:[IF EXISTS]} ${6:column_name} SET OPTIONS (${7:description = "comment"})

• ALTER COLUMN SET DATA TYPE
  • Prefix
    alter column set data type
  • Body

    ALTER TABLE ${1:[IF EXISTS]} `${2:project}.${3:dataset}.${4:table}`
      ALTER COLUMN ${5:[IF EXISTS]} ${6:column_name} SET DATA TYPE (${7:data_type})

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_28_2021
• https://cloud.google.com/bigquery/docs/release-notes#August_03_2021

Description

Hello, thanks for the plugin.
I'm using Terraform templatefile function to parameterize some queries, therefore I can have SQL like:

SELECT _entity.*,
FROM `${project}.other_table`
WHERE field IS TRUE
;

And it renders like this:

I'm guessing the syntax regex might need to be tweaked for it to ignore the $ in the identifier.

Description

• Add supports table snapshot
  • CREATE SNAPSHOT TABLE DDL statements.
  • INFORMATION_SCHEMA.TABLE_SNAPSHOTS table.

Add snippets

• CREATE SNAPSHOT TABLE
  • Prefix
    create snapshot table
  • Body

    CREATE SNAPSHOT TABLE ${1:[IF NOT EXISTS] }`${2:project}.${3:dataset}.${4:snapshot_table}`
      CLONE `${5:project}.${6:dataset}.${7:source_table}`
    ${8:  [FOR SYSTEM_TIME AS OF ${9:time_expression}]}
    ${10:[OPTIONS (
      description = "description",
      expiration_timestamp = TIMESTAMP "YYYY-MM-DD HH:MI:SS UTC",
      friendly_name = "friendly_name",
      labels = [("key", "value")]
    )]}

• INFORMATION_SCHEMA.TABLE_SNAPSHOTS
  • Prefix
    create snapshot table
  • Body

    CREATE SNAPSHOT TABLE ${1:[IF NOT EXISTS] }`${2:project}.${3:dataset}.${4:snapshot_table}`
      CLONE `${5:project}.${6:dataset}.${7:source_table}`
    ${8:  [FOR SYSTEM_TIME AS OF ${9:time_expression}]}
    ${10:[OPTIONS (
      description = "description",
      expiration_timestamp = TIMESTAMP "YYYY-MM-DD HH:MI:SS UTC",
      friendly_name = "friendly_name",
      labels = [("key", "value")]
    )]}

• SELECT ... FROM INFORMATION_SCHEMA.TABLE_SNAPSHOTS
  • Prefix
    select info table snapshots
  • Body

    SELECT
      table_catalog
      table_schema
      table_name
      base_table_catalog
      base_table_schema
      base_table_name
      snapshot_time
    FROM
      `${1:project}.${2:dataset}`|`region-${3:(us|eu|region_name)}`.INFORMATION_SCHEMA.TABLE_SNAPSHOTS
    ORDER BY
      table_name

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_25_2021

Description

• Supports syntax highlighting
• Add snippets

DDLs

• CREATE PROCEDURE
  • Prefix
    create procedure
  • body

    CREATE OR REPLACE PROCEDURE `project.dataset.ProcedureName`([IN|OUT|INOUT] arg_name arg_type[, ...])
    BEGIN
      statements
    END;

• CALL PROCEDURE
  • Prefix
    call procedure
  • body

    CALL `project.dataset.ProcedureName`(arg[, ...]);

• DECLARE
  • Prefix
    declare
  • body

    DECLARE variable_name[, ...] variable_type [DEFAULT expression];

• SET
  • Prefix
    set
  • body

    SET variable_name = expression;

• SET multiple
  • Prefix
    set multiple
  • body

    SET (variable_name_1, ...) = (expression_1, ...);

• SET from query
  • Prefix
    set query
  • body

    SET variable_name = (SELECT query);

• BEGIN ... END
  • Prefix
    begin/end
  • body

    BEGIN
      statements
    END;

• IF ... THEN
  • Prefix
    if
  • body

    IF condition THEN
      statements
    END IF;

• IF ... THEN ELSE
  • Prefix
    if/else
  • body

    IF condition THEN
      statements
    ELSE
      statements
    END IF;

• ELSE
  • Prefix
    else
  • body

    ELSE
      statements

• IF EXISTS query THEN
  • Prefix
    if exists query
  • body

    IF EXISTS (SELECT expression FROM `project.dataset.table` WHERE condition) THEN
      statements
    END IF;

• LOOP
  • Prefix
    loop
  • body

    LOOP
      statements
    END LOOP;

• WHILE
  • Prefix
    while
  • body

    WHILE expression DO
      statements
    END WHILE;

• BREAK
  • Prefix
    break
  • body

    BREAK;

• LEAVE
  • Prefix
    leave
  • body

    LEAVE;

• CONTINUE
  • Prefix
    continue
  • body

    CONTINUE;

• ITERATE
  • Prefix
    iterate
  • body

    ITERATE;

• RETURN
  • Prefix
    return
  • body

    RETURN;

Document

• https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_procedure
• https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_03_2019

Description

• Supports syntax highlighting
• Add snippets

Functions

• KEYS.NEW_KEYSET

  • Prefix
    keysnew_keyset
  • body

    KEYS.NEW_KEYSET(key_type)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#keysnew_keyset

• KEYS.ADD_KEY_FROM_RAW_BYTES

  • Prefix
    keysadd_key_from_raw_bytes
  • body

    KEYS.ADD_KEY_FROM_RAW_BYTES(keyset, key_type, raw_key_bytes)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#keysadd_key_from_raw_bytes

• AEAD.DECRYPT_BYTES

  • Prefix
    aeaddecrypt_bytes
  • body

    AEAD.DECRYPT_BYTES(keyset, ciphertext, additional_data)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#aeaddecrypt_bytes

• AEAD.DECRYPT_STRING

  • Prefix
    aeaddecrypt_string
  • body

    AEAD.DECRYPT_STRING(keyset, ciphertext, additional_data)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#aeaddecrypt_string

• AEAD.ENCRYPT

  • Prefix
    aeadencrypt
  • body

    AEAD.ENCRYPT(keyset, plaintext, additional_data)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#aeadencrypt

• KEYS.KEYSET_FROM_JSON

  • Prefix
    keyskeyset_from_json
  • body

    KEYS.KEYSET_FROM_JSON(json_keyset)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#keyskeyset_from_json

• KEYS.KEYSET_TO_JSON

  • Prefix
    keyskeyset_to_json
  • body

    KEYS.KEYSET_TO_JSON(keyset)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#keyskeyset_to_json

• KEYS.ROTATE_KEYSET

  • Prefix
    keysrotate_keyset
  • body

    KEYS.ROTATE_KEYSET(keyset, key_type)

  • Document
    https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#keysrotate_keyset

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#april_10_2019

Description

• Add supports new Scripting.
  • EXECUTE IMMEDIATE
  • BEGIN ... EXCEPTION
  • ELSEIF ... THEN
  • RAISE

Add snippets

• EXECUTE IMMEDIATE
  • Prefix
    execute immediate
  • Body

    EXECUTE IMMEDIATE
      ${1:"sql_expression @parameter_name"}
      ${2:[INTO variable]}
      ${3:[USING value AS parameter_name]};

  • Description

    Executes a dynamic SQL statement on the fly.
    

• IF ... ELSEIF ... ELSE
  • Prefix
    if elseif else
  • Body

    IF ${1:condition} THEN
      ${2:statements}
    ELSEIF ${3:condition} THEN
      ${4:statements}
    ELSE
      ${5:statements}
    END IF;

• RAISE
  • Prefix
    raise
  • Body

    RAISE ${1:[USING MESSAGE = "message"]};

  • Description

    Raises an error.
    

Change snippets

• BEGIN ... END
  • Prefix
    begin/end
  • Body

    BEGIN
      ${1:statements}
    EXCEPTION WHEN ERROR THEN
      ${2:statements}
      ${3:[# Get error informations
      SELECT
        @@error.message,
        @@error.stack_trace,
        @@error.statement_text,
        @@error.formatted_stack_trace;]}
    END;

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#May_20_2020

Description

• Supports syntax highlighting
• Add snippets

DDLs

• CREATE TEMPORARY TABLE
  • Prefix
    create temp table
  • body

    CREATE OR REPLACE TEMP TABLE table_name
    (
      column type OPTIONS (description = "comment")
    )

Document

• https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#temporary_tables

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_03_2019

Description

• Add EXPORT DATA snippets.

Add snippets

• EXPORT DATA
  • Prefix
    export data
  • Body

    EXPORT DATA
    OPTIONS (
      ${1:format = "AVRO"|"CSV"|"JSON"|"PARQUET",}
      ${2:compression = "GZIP"|"DEFLATE"|"SNAPPY",}
      ${3:field_delimiter = ","|"\t"|"other",}
      ${4:header = false|true,}
      ${5:overwrite = false|true,}
      ${6:uri = "gs://bucket/path/file_*.csv.gz"}
    ) AS
    query_statement

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_14_2020

Description

• Add supports new String functions.
  • ASCII
  • CHR
  • INITCAP
  • INSTR
  • LEFT
  • OCTET_LENGTH
  • REGEXP_EXTRACT with 2 additional parameters (position and occurrence)
  • REGEXP_INSTR
  • REGEXP_SUBSTR
  • RIGHT
  • SOUNDEX
  • SUBSTRING
  • TRANSLATE
  • UNICODE

Add snippets

• ASCII()
  • Prefix
    ASCII
  • Body

    ASCII(${1:value})

  • Description

    Returns the ASCII code for the first character or byte in value.
    

• CHR()
  • Prefix
    CHR
  • Body

    CHR(${1:value})

  • Description

    Takes a Unicode code point and returns the character that matches the code point.
    

• INITCAP()
  • Prefix
    INITCAP
  • Body

    INITCAP(${1:value}${2:[, delimiters]})

  • Description

    Returns it with the first character in each word in uppercase and all other characters in lowercase.
    

• INSTR()
  • Prefix
    INSTR
  • Body

    INSTR(${1:source_value}, ${2:search_value}${3:[, position[, occurrence]]})

  • Description

    Returns the lowest 1-based index of search_value in source_value.
    

• LEFT()
  • Prefix
    LEFT
  • Body

    LEFT(${1:value}, ${2:length})

  • Description

    Return the specified leftmost number of characters.
    

• OCTET_LENGTH()
  • Prefix
    OCTET_LENGTH
  • Body

    OCTET_LENGTH(${1:value})

  • Description

    Alias for BYTE_LENGTH.
    

• REGEXP_INSTR()
  • Prefix
    REGEXP_INSTR
  • Body

    REGEXP_INSTR(${1:source_value}, r\"${2:regex}\"${3:[, position[, occurrence, [occurrence_position]]]})

  • Description

    Returns the lowest 1-based index of a regular expression, regexp, in source_value.
    

• REGEXP_SUBSTR()
  • Prefix
    REGEXP_SUBSTR
  • Body

    REGEXP_SUBSTR(${1:value}, r\"${2:regex}\"${3:[, position[, occurrence]]})

  • Description

    Synonym for REGEXP_EXTRACT.
    

• RIGHT()
  • Prefix
    RIGHT
  • Body

    RIGHT(${1:value}, ${2:length})

  • Description

    Return the specified rightmost number of characters.
    

• SOUNDEX()
  • Prefix
    SOUNDEX
  • Body

    SOUNDEX(${1:value})

  • Description

    Returns a STRING that represents the Soundex code for value.
    

• SUBSTRING()
  • Prefix
    substring
  • Body

    SUBSTRING(${1:value}, ${2:position}${3:[, length]})",

  • Description

    Returns a substring of the supplied `value`. (Alias for SUBSTR)
    

• TRANSLATE()
  • Prefix
    TRANSLATE
  • Body

    TRANSLATE(${1:expression}, ${2:source_characters}, ${3:target_characters})

  • Description

    In `expression`, replaces each character in `source_characters` with the corresponding character in `target_characters`.
    

• UNICODE()
  • Prefix
    UNICODE
  • Body

    UNICODE(${1:value})

  • Description

    Returns the Unicode code point for the first character in value.
    

Change snippets

• REGEXP_EXTRACT()
  • Prefix
    regexp_extract
  • Body

    REGEXP_EXTRACT(${1:value}, r\"${2:regex}\"${3:[, position[, occurrence]]})",

  • Description

    Returns the first substring in `value` that matches the regular expression, `regex`. Returns NULL if there is no match.
    

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_14_2020
• https://cloud.google.com/bigquery/docs/release-notes#October_26_2020

Description

• Add supports BQ.JOBS.CANCEL snippets.

Add snippets

• BQ.JOBS.CANCEL
  • Prefix
    call bq jobs cancel
  • Body

    CALL BQ.JOBS.CANCEL(${1:"project-id.job_id"});

  • Description

    Canceling a job.
    

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#September_11_2020

Description

Backticks do not parse correctly when a colon (:) is placed after a period (.) within backticks (`) .

For example:

• Description
  • View name snippets
    • Table metadata
    • Job metadata
    • Job metadata by timeslice
    • Reservations metadata
    • Routine metadata
    • Streaming metadata
  • SELECT query snippets
    • Table metadata
    • Job metadata
    • Job metadata by timeslice
    • Reservations metadata
    • Routine metadata
    • Streaming metadata
• BigQuery Release notes

Description

• Add new INFORMATION_SCHEMA views snippets

View name snippets

Table metadata

• INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
  • Prefix
    infocolumnfieldpaths
  • Body

    INFORMATION_SCHEMA.COLUMN_FIELD_PATHS

• INFORMATION_SCHEMA.PARTITIONS
  • Prefix
    infopartitions
  • Body

    INFORMATION_SCHEMA.PARTITIONS

Job metadata

• INFORMATION_SCHEMA.JOBS_BY_USER
  • Prefix
    infojobsbyuser
  • Body

    INFORMATION_SCHEMA.JOBS_BY_USER

• INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • Prefix
    infojobsbyproject
  • Body

    INFORMATION_SCHEMA.JOBS_BY_PROJECT

• INFORMATION_SCHEMA.JOBS_BY_FOLDER
  • Prefix
    infojobsbyfolder
  • Body

    INFORMATION_SCHEMA.JOBS_BY_FOLDER

• INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
  • Prefix
    infojobsbyorganization
  • Body

    INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION

Job metadata by timeslice

• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER
  • Prefix
    infojobstimelinebyuser
  • Body

    INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER

• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
  • Prefix
    infojobstimelinebyproject
  • Body

    INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT

• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER
  • Prefix
    infojobstimelinebyfolder
  • Body

    INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER

• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  • Prefix
    infojobstimelinebyorganization
  • Body

    INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION

Reservations metadata

• INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  • Prefix
    inforeservationchangesbyproject
  • Body

    INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT

• INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT
  • Prefix
    inforeservationsbyproject
  • Body

    INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT

• INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
  • Prefix
    infocapacitycommitmentchangesby_project
  • Body

    INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT

• INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT
  • Prefix
    infocapacitycommitmentsbyproject
  • Body

    INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT

• INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT
  • Prefix
    infoassignmentchangesbyproject
  • Body

    INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT

• INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
  • Prefix
    infoassignmentsbyproject
  • Body

    INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT

Routine metadata

• INFORMATION_SCHEMA.ROUTINES
  • Prefix
    inforoutines
  • Body

    INFORMATION_SCHEMA.ROUTINES

• INFORMATION_SCHEMA.ROUTINE_OPTIONS
  • Prefix
    inforoutineoptions
  • Body

    INFORMATION_SCHEMA.ROUTINE_OPTIONS

• INFORMATION_SCHEMA.PARAMETERS
  • Prefix
    infoparameters
  • Body

    INFORMATION_SCHEMA.PARAMETERS

Streaming metadata

• INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT
  • Prefix
    infostreamingtimelinebyproject
  • Body

    INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT

• INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_FOLDER
  • Prefix
    infostreamingtimelinebyfolder
  • Body

    INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_FOLDER

• INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_ORGANIZATION
  • Prefix
    infostreamingtimelinebyorganization
  • Body

    INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_ORGANIZATION

SELECT query snippets

Table metadata

• SELECT ... FROM INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
  • Prefix
    select info columnfieldpaths
  • Body

    SELECT
      table_catalog,
      table_schema,
      table_name,
      column_name,
      field_path,
      data_type,
      description
    FROM
      `${1:project}.${2:dataset}`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    ORDER BY
      table_name, column_name

• SELECT ... FROM INFORMATION_SCHEMA.PARTITIONS
  • Prefix
    select info partitions
  • Body

    SELECT
      table_catalog,
      table_schema,
      table_name,
      partition_id,
      total_rows,
      total_logical_bytes,
      total_billable_bytes,
      last_modified_time,
      storage_tier
    FROM
      `${1:project}.${2:dataset}`.INFORMATION_SCHEMA.PARTITIONS
    ORDER BY
      table_name, partition_id DESC

Job metadata

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_BY_USER
  • Prefix
    select info jobsbyuser
  • Body

    SELECT
      creation_time,
      project_id,
      project_number,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      start_time,
      end_time,
      query,
      state,
      reservation_id,
      total_bytes_processed,
      total_slot_ms,
      error_result,
      cache_hit,
      destination_table,
      referenced_tables,
      labels,
      timeline,
      job_stages,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_BY_USER
    ORDER BY
      creation_time DESC

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_BY_PROJECT
  • Prefix
    select info jobsbyproject
  • Body

    SELECT
      creation_time,
      project_id,
      project_number,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      start_time,
      end_time,
      query,
      state,
      reservation_id,
      total_bytes_processed,
      total_slot_ms,
      error_result,
      cache_hit,
      destination_table,
      referenced_tables,
      labels,
      timeline,
      job_stages,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
    ORDER BY
      creation_time DESC

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_BY_FOLDER
  • Prefix
    select info jobsbyfolder
  • Body

    SELECT
      creation_time,
      project_id,
      project_number,
      folder_numbers,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      start_time,
      end_time,
      state,
      reservation_id,
      total_bytes_processed,
      total_slot_ms,
      error_result,
      cache_hit,
      destination_table,
      referenced_tables,
      labels,
      timeline,
      job_stages,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_BY_FOLDER
    ORDER BY
      creation_time DESC

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
  • Prefix
    select info jobsbyorganization
  • Body

    SELECT
      creation_time,
      project_id,
      project_number,
      folder_numbers,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      start_time,
      end_time,
      state,
      reservation_id,
      total_bytes_processed,
      total_slot_ms,
      error_result,
      cache_hit,
      destination_table,
      referenced_tables,
      labels,
      timeline,
      job_stages,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
    ORDER BY
      creation_time DESC

Job metadata by timeslice

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER
  • Prefix
    select info jobstimelinebyuser
  • Body

    SELECT
      period_start,
      period_slot_ms,
      project_id,
      project_number,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      job_creation_time,
      job_start_time,
      job_end_time,
      state,
      reservation_id,
      total_bytes_processed,
      error_result,
      cache_hit,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_USER
    ORDER BY
      period_start DESC

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
  • Prefix
    select info jobstimelinebyproject
  • Body

    SELECT
      period_start,
      period_slot_ms,
      project_id,
      project_number,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      job_creation_time,
      job_start_time,
      job_end_time,
      state,
      reservation_id,
      total_bytes_processed,
      error_result,
      cache_hit,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
    ORDER BY
      period_start DESC

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER
  • Prefix
    select info jobstimelinebyfolder
  • Body

    SELECT
      period_start,
      period_slot_ms,
      project_id,
      project_number,
      folder_numbers,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      job_creation_time,
      job_start_time,
      job_end_time,
      state,
      reservation_id,
      total_bytes_processed,
      error_result,
      cache_hit,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_FOLDER
    ORDER BY
      period_start DESC

• SELECT ... FROM INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
  • Prefix
    select info jobstimelinebyorganization
  • Body

    SELECT
      period_start,
      period_slot_ms,
      project_id,
      project_number,
      folder_numbers,
      user_email,
      job_id,
      job_type,
      statement_type,
      priority,
      job_creation_time,
      job_start_time,
      job_end_time,
      state,
      reservation_id,
      total_bytes_processed,
      error_result,
      cache_hit,
      total_bytes_billed,
      parent_job_id
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_ORGANIZATION
    ORDER BY
      period_start DESC

Reservations metadata

• SELECT ... FROM INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
  • Prefix
    select info reservationchangesbyproject
  • Body

    SELECT
      change_timestamp,
      project_id,
      project_number,
      reservation_name,
      ignore_idle_slots,
      action,
      slot_capacity,
      user_email
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.RESERVATION_CHANGES_BY_PROJECT
    ORDER BY
      change_timestamp DESC

• SELECT ... FROM INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT
  • Prefix
    select info reservationsbyproject
  • Body

    SELECT
      project_id,
      project_number,
      reservation_name,
      ignore_idle_slots,
      slot_capacity
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.RESERVATIONS_BY_PROJECT
    ORDER BY
      project_id

• SELECT ... FROM INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
  • Prefix
    select info capacitycommitmentchangesbyproject
  • Body

    SELECT
      change_timestamp,
      project_id,
      project_number,
      capacity_commitment_id,
      commitment_plan,
      state,
      slot_count,
      action,
      user_email,
      commitment_start_time,
      commitment_end_time,
      failure_status,
      renewal_plan
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT
    ORDER BY
      change_timestamp DESC

• SELECT ... FROM INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT
  • Prefix
    select info capacitycommitmentsbyproject
  • Body

    SELECT
      project_id,
      project_number,
      capacity_commitment_id,
      commitment_plan,
      state,
      slot_count
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.CAPACITY_COMMITMENTS_BY_PROJECT
    ORDER BY
      project_id

• SELECT ... FROM INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT
  • Prefix
    select info assignmentchangesbyproject
  • Body

    SELECT
      change_timestamp,
      project_id,
      project_number,
      assignment_id,
      reservation_name,
      job_type,
      assignee_id,
      assignee_number,
      assignee_type,
      action,
      user_email,
      state
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.ASSIGNMENT_CHANGES_BY_PROJECT
    ORDER BY
      change_timestamp DESC

• SELECT ... FROM INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
  • Prefix
    select info assignmentsbyproject
  • Body

    SELECT
      project_id,
      project_number,
      assignment_id,
      reservation_name,
      job_type,
      assignee_id,
      assignee_number,
      assignee_type
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.ASSIGNMENTS_BY_PROJECT
    ORDER BY
      project_id

Routine metadata

• SELECT ... FROM INFORMATION_SCHEMA.ROUTINES
  • Prefix
    select info routines
  • Body

    SELECT
      specific_catalog,
      specific_schema,
      specific_name,
      routine_catalog,
      routine_schema,
      routine_name,
      routine_type,
      data_type,
      routine_body,
      routine_definition,
      external_language,
      is_deterministic,
      security_type,
      created,
      last_modified
    FROM
      `${1:project}.${2:dataset}`.INFORMATION_SCHEMA.ROUTINES
    ORDER BY
      specific_catalog, specific_schema, specific_name

• SELECT ... FROM INFORMATION_SCHEMA.ROUTINE_OPTIONS
  • Prefix
    select info routineoptions
  • Body

    SELECT
      specific_catalog,
      specific_schema,
      specific_name,
      option_name,
      option_type,
      option_value
    FROM
      `${1:project}.${2:dataset}`.INFORMATION_SCHEMA.ROUTINE_OPTIONS
    ORDER BY
      specific_catalog, specific_schema, specific_name, option_name

• SELECT ... FROM INFORMATION_SCHEMA.PARAMETERS
  • Prefix
    select info parameters
  • Body

    SELECT
      specific_catalog,
      specific_schema,
      specific_name,
      ordinal_position,
      parameter_mode,
      is_result,
      parameter_name,
      data_type,
      parameter_default,
      is_aggregate
    FROM
      `${1:project}.${2:dataset}`.INFORMATION_SCHEMA.PARAMETERS
    ORDER BY
      specific_catalog, specific_schema, specific_name, ordinal_position

Streaming metadata

• SELECT ... FROM INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT
  • Prefix
    select info streamingtimelinebyproject
  • Body

    SELECT
      start_timestamp,
      project_id,
      project_number,
      dataset_id,
      table_id,
      error_code,
      total_requests,
      total_rows,
      total_input_bytes
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT
    ORDER BY
      start_timestamp DESC

• SELECT ... FROM INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_FOLDER
  • Prefix
    select info streamingtimelinebyfolder
  • Body

    SELECT
      start_timestamp,
      project_id,
      project_number,
      dataset_id,
      table_id,
      error_code,
      total_requests,
      total_rows,
      total_input_bytes
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_FOLDER
    ORDER BY
      start_timestamp DESC

• SELECT ... FROM INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_ORGANIZATION
  • Prefix
    select info streamingtimelinebyorganization
  • Body

    SELECT
      start_timestamp,
      project_id,
      project_number,
      dataset_id,
      table_id,
      error_code,
      total_requests,
      total_rows,
      total_input_bytes
    FROM
      `region-${1:(us|eu|region_name)}`.INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_ORGANIZATION
    ORDER BY
      start_timestamp DESC

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#March_31_2021
• https://cloud.google.com/bigquery/docs/release-notes#August_25_2020
• https://cloud.google.com/bigquery/docs/release-notes#July_27_2020
• https://cloud.google.com/bigquery/docs/release-notes#June_26_2020
• https://cloud.google.com/bigquery/docs/release-notes#April_16_2020
• https://cloud.google.com/bigquery/docs/release-notes#March_31_2020
• https://cloud.google.com/bigquery/docs/release-notes#February_14_2020
• https://cloud.google.com/bigquery/docs/release-notes#November_22_2019

Description

• Add supports new Query syntax.
  • The ORDER BY clause options NULLS FIRST and NULLS LAST clauses.
  • TABLESAMPLE
  • FOR SYSTEM_TIME AS OF
  • QUALIFY
  • PIVOT
  • UNPIVOT

Add snippets

• TABLESAMPLE
  • Prefix
    "tablesample", "sampling"
  • Body

    SELECT
      ${1:column}
    FROM `${2:project}.${3:dataset}.${4:table}`
      TABLESAMPLE SYSTEM (${5:value} PERCENT)

  • Description

    Table sampling lets you query random subsets of data from large BigQuery tables.
    

• FOR SYSTEM_TIME AS OF
  • Prefix
    "system_time", "time travel"
  • Body

    SELECT
      ${1:column}
    FROM `${2:project}.${3:dataset}.${4:table}`
      FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL ${5:1} HOUR)

  • Description

    Time travel to access data at any point within the last 7 days.
    

• PIVOT
  • Prefix
    pivot
  • Body

    SELECT
      *
    FROM `${1:project}.${2:dataset}.${3:table}`
      PIVOT(
        ${4:aggregate_function(aggregate_column)}
        FOR ${5:input_column}
        IN (${6:pivot_column, ...})
      )

• UNPIVOT
  • Prefix
    unpivot
  • Body

    SELECT
      *
    FROM `${1:project}.${2:dataset}.${3:table}`
      UNPIVOT${4:[ INCLUDE NULLS|EXCLUDE NULLS ]}(
        ${5:value_column_name}
        FOR ${6:dimension_column_name}
        IN (${7:unpivot_column, ...})
      )

Change snippets

• ORDER BY
  • Prefix
    order by
  • Body

    ORDER BY
      ${1:expression} ${2:[ASC|DESC]} ${3:[NULLS FIRST|NULLS LAST]}

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#March_31_2021
• https://cloud.google.com/bigquery/docs/release-notes#May_10_2021

Description

• Add description option to CREATE TABLE statements

Example

• Prefix
  create table
• Body
  • old

    CREATE TABLE `project.dataset.table`
    (
      column type
    )

  • new

    CREATE TABLE `project.dataset.table`
    (
      column type OPTIONS (description = "comment")
    )

Description

• Add supports new Date functions.
  • LAST_DAY

Add snippets

• LAST_DAY()
  • Prefix
    LAST_DAY
  • Body

    LAST_DAY(${1:date_expression}${2:[, date_part]})

  • Description

    description": "Returns the last day from a date expression.
    

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#October_14_2020

Description

• Fix BEGIN ... END snippets prefix

Fix snippets

• BEGIN ... END
  • Prefix
    begin/end -> begin end

Description

• Add supports parameterized types.
  • STRING(L)
  • BYTES(L)
  • NUMERIC(P) / NUMERIC(P, S)
  • BIGNUMERIC(P) / BIGNUMERIC(P, S)
• Add supports new data types.
  • INTERVAL
• Add supports Numeric type INT64 aliases.
  • SMALLINT
  • INTEGER
  • BIGINT
  • TINYINT
  • BYTEINT
• Add all data types snippets.

Add snippets

• ARRAY type
  • Prefix
    ARRAY type
  • Body

    ARRAY<${1:T}>

• BOOL type
  • Prefix
    BOOL type
  • Body

    BOOL

• BYTES type
  • Prefix
    BYTES type
  • Body

    BYTES${1:[(L)]}

• DATE type
  • Prefix
    DATE type
  • Body

    DATE

• DATETIME type
  • Prefix
    DATETIME type
  • Body

    DATETIME

• GEOGRAPHY type
  • Prefix
    GEOGRAPHY type
  • Body

    GEOGRAPHY

• INTERVAL type
  • Prefix
    INTERVAL type
  • Body

    INTERVAL

• INT64 type
  • Prefix
    INT64 type
  • Body

    INT64

• NUMERIC type
  • Prefix
    NUMERIC type
  • Body

    NUMERIC${1:[(P[, S])]}

• BIGNUMERIC type
  • Prefix
    BIGNUMERIC type
  • Body

    BIGNUMERIC${1:[(P[, S])]}

• FLOAT64 type
  • Prefix
    FLOAT64 type
  • Body

    FLOAT64

• STRING type
  • Prefix
    STRING type
  • Body

    STRING${1:[(L)]}

• STRUCT type
  • Prefix
    STRUCT type
  • Body

    STRUCT<${1:T}>

• TIME type
  • Prefix
    TIME type
  • Body

    TIME

• TIMESTAMP type
  • Prefix
    TIMESTAMP type
  • Body

    TIMESTAMP

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_07_2021
• https://cloud.google.com/bigquery/docs/release-notes#June_28_2021
• https://cloud.google.com/bigquery/docs/release-notes#July_27_2021

Description

• Add supports Federated query syntax.
  • EXTERNAL_QUERY

Add snippets

• EXTERNAL_QUERY
  • Prefix
    external_query
  • Body

    EXTERNAL_QUERY(${1:connection_id}, ${2:external_database_query}${3:[, options]})

  • Description

    Executes the query in Cloud SQL and returns results as a temporary table.
    

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#May_20_2020

Description

• Add supports DCL statements.
  • GRANT
  • REVOKE
  • INFORMATION_SCHEMA.OBJECT_PRIVILEGES

Add snippets

• GRANT
  • Prefix
    grant
  • Body

    GRANT `${1:role_name[, ...]}`
      ON ${2:SCHEMA `project.dataset`}|${3:TABLE `project.dataset.table`}|${4:VIEW `project.dataset.view`}
      TO ${5:"user:user@domain|group:group@domain|serviceAccount:[email protected]|domain:domain|specialGroup:(allAuthenticatedUsers|allUsers)"[, ...]}

• REVOKE
  • Prefix
    revoke
  • Body

    REVOKE `${1:role_name[, ...]}`
      ON ${2:SCHEMA `project.dataset`}|${3:TABLE `project.dataset.table`}|${4:VIEW `project.dataset.view`}
      FROM ${5:"user:user@domain|group:group@domain|serviceAccount:[email protected]|domain:domain|specialGroup:(allAuthenticatedUsers|allUsers)"[, ...]}

• INFORMATION_SCHEMA.OBJECT_PRIVILEGES
  • Prefix
    info object privileges
  • Body

    INFORMATION_SCHEMA.OBJECT_PRIVILEGES

• SELECT ... FROM INFORMATION_SCHEMA.OBJECT_PRIVILEGES
  • Prefix
    select info object privileges
  • Body

    SELECT
      object_catalog,
      object_schema,
      object_name,
      object_type,
      privilege_type,
      grantee
    FROM
      ${1:`project`}.`region-${2:(us|eu|region_name)}`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
    WHERE
      -- Dataset required condition
      ${3:object_name = "dataset"}
      -- Table/View required conditions
      ${4:object_schema = "dataset"}
      AND ${5:object_name = "table_or_view"}
    ORDER BY
      privilege_type,
      grantee

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_28_2021
• https://cloud.google.com/bigquery/docs/release-notes#July_19_2021

Description

• Add supports Debugging statements syntax.
  • ASSERT

Add snippets

• ASSERT
  • Prefix
    assert
  • Body

    ASSERT ${1:expression}${2: [AS "description"]}

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#July_13_2020

Description

• Add supports new JSON functions.
  • JSON_QUERY
  • JSON_VALUE
  • JSON_EXTRACT_ARRAY
  • JSON_QUERY_ARRAY
  • JSON_EXTRACT_STRING_ARRAY
  • JSON_VALUE_ARRAY
• Chang to legacy function.
  • JSON_EXTRACT
  • JSON_EXTRACT_SCALAR
  • JSON_EXTRACT_ARRAY
  • JSON_EXTRACT_STRING_ARRAY

Add snippets

• JSON_QUERY
  • Prefix
    json_query
  • Body

    JSON_QUERY(${1:"json_string_expr"}, ${2:"json_path"})

  • Description

    Extracts a JSON values.
    

• JSON_VALUE
  • Prefix
    json_value
  • Body

    JSON_VALUE(${1:"json_string_expr"}, ${2:"json_path"})

  • Description

    Extracts a scalar value.
    

• JSON_EXTRACT_ARRAY
  • Prefix
    json_extract_array
  • Body

    JSON_EXTRACT_ARRAY(${1:'json_string_expr'}, ${2:'json_path'})

  • Description

    Extracts an array of JSON values. (Legacy JSON function, recommend using `JSON_QUERY_ARRAY`)
    

• JSON_QUERY_ARRAY
  • Prefix
    json_query_array
  • Body

    JSON_QUERY_ARRAY(${1:"json_string_expr"}, ${2:"json_path"})

  • Description

    Extracts an array of JSON values.
    

• JSON_EXTRACT_STRING_ARRAY
  • Prefix
    json_extract_string_array
  • Body

    JSON_EXTRACT_STRING_ARRAY(${1:'json_string_expr'}, ${2:'json_path'})

  • Description

    Extracts an array of scalar values. (Legacy JSON function, recommend using `JSON_VALUE_ARRAY`)
    

• JSON_VALUE_ARRAY
  • Prefix
    json_value_array
  • Body

    JSON_VALUE_ARRAY(${1:"json_string_expr"}, ${2:"json_path"})

  • Description

    Extracts an array of scalar values.
    

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#May_01_2020
• https://cloud.google.com/bigquery/docs/release-notes#March_31_2021

Description

• Add supports new Data types syntax.
  • GEOGRAPHY
  • DECIMAL
  • BIGNUMERIC
  • BIGDECIMAL

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#December_21_2020

Description

• Add supports new casting functions.
  • ST_GEOGFROM()

Add snippets

• ST_GEOGFROM()
  • Prefix
    st_geogfrom
  • Body

    ST_GEOGFROM(${1:expression})

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#June_28_2021

Description

• Add supports new Geography functions.
  • ST_STARTPOINT
  • ST_ENDPOINT
  • ST_POINTN

Add snippets

• ST_STARTPOINT
  • Prefix
    st_startpoint
  • Body

    ST_STARTPOINT(${1:linestring_geography})

• ST_ENDPOINT
  • Prefix
    st_endpoint
  • Body

    ST_ENDPOINT(${1:linestring_geography})

• ST_POINTN
  • Prefix
    st_pointn
  • Body

    ST_POINTN(${1:linestring_geography}, ${2:index})

BigQuery Release notes

• https://cloud.google.com/bigquery/docs/release-notes#May_20_2021

Description

r'\d+" & \d+"|x'
A regular expression with double-quote inside single quote expression 'reg_exp_with_double_quote'

Everything, after is the first double quote, it is string considered.

Description

Change default saved file extension to .sql - currently it's .dml, is there a way to change this please?

Hi! I love this extension - thanks!

One way to make it better is to also add formatting - currently when I try to format it says that no formatting is configured for this language. Would you consider adding formatting - perhaps sqlfmt? I would have looked into it myself, but have not written vscode extensions before, and am swamped with work. Thanks!

-- Ran