Como usar instruções da linguagem de definição de dados

As instruções da linguagem de definição de dados (DDL, na sigla em inglês) permitem criar e modificar recursos do BigQuery usando a sintaxe de consulta do SQL padrão. Atualmente, é possível usar comandos DDL no BigQuery para:

Instrução CREATE TABLE

Para criar uma tabela no BigQuery, use a instrução DDL CREATE TABLE.

Sintaxe

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
table_name
[(
  column_name column_schema[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

Em que:

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE} é uma das seguintes instruções:

  • CREATE TABLE: cria uma nova tabela.
  • CREATE TABLE IF NOT EXISTS: cria uma nova tabela somente se a tabela não existir atualmente no conjunto de dados especificado.
  • CREATE OR REPLACE TABLE: cria uma tabela e substitui uma tabela existente com o mesmo nome no conjunto de dados especificado.

As instruções CREATE TABLE precisam obedecer às regras a seguir:

  • Apenas uma instrução CREATE é permitida.
  • A lista de colunas, a cláusula as query_statement ou ambas precisam estar presentes.
  • Quando a lista de colunas e a cláusula as query_statement estão presentes, o BigQuery ignora os nomes na cláusula as query_statement e combina as colunas com a lista de colunas por posição.
  • Quando a cláusula as query_statement está presente e a lista de colunas não existe, o BigQuery determina os nomes e os tipos de coluna com base na cláusula as query_statement.
  • Você precisa especificar os nomes das colunas pela lista de colunas ou pela cláusula as query_statement.
  • Não é permitido duplicar nomes de colunas.

table_name

table_name é o nome da tabela que você está criando. O nome da tabela precisa ser exclusivo por conjunto de dados. O nome da tabela pode:

  • conter até 1.024 caracteres;
  • conter letras (maiúsculas e minúsculas), números e sublinhados

column_name e column_schema

(column_name column_schema[, ...]) contém as informações de esquema da tabela, em uma lista separada por vírgulas:

  • column_name é o nome da coluna. Um nome de coluna:
    • precisa conter apenas letras (a-z, A-Z), números (0-9) ou sublinhados (_);
    • precisa começar com uma letra ou um sublinhado;
    • pode ter até 128 caracteres.
  • column_schema é semelhante a um tipo de dados, mas aceita uma restrição NOT NULL opcional para tipos diferentes de ARRAY. column_schema também é compatível com opções em colunas de nível superior e campos STRUCT.
column_schema :=
   {simple_type [NOT NULL] |
    STRUCT<field_list> [NOT NULL] |
    ARRAY<array_element_schema>}
   [OPTIONS(column_option_list)]

field_list := field_name column_schema [, ...]

array_element_schema := {simple_type | STRUCT<field_list>} [NOT NULL]

simple_type é qualquer tipo de dados compatível, exceto STRUCT e ARRAY.

field_name é o nome do campo struct. Os nomes dos campos struct têm as mesmas restrições que os nomes das colunas.

Quando a restrição NOT NULL está presente em uma coluna ou um campo, a coluna ou o campo é criado com o modo REQUIRED. Por outro lado, quando a restrição NOT NULL está ausente, a coluna ou campo é criado com o modo NULLABLE.

As colunas e os campos do tipo ARRAY não são compatíveis com o modificador NOT NULL. Por exemplo, um column_schema de ARRAY<INT64> NOT NULL é inválido, uma vez que as colunas ARRAY têm o modo REPEATED e podem estar vazias, mas não podem ser NULL. Um elemento de matriz em uma tabela nunca pode ser NULL, independentemente de a restrição NOT NULL ser especificada. Por exemplo, ARRAY<INT64> é equivalente a ARRAY<INT64 NOT NULL>.

O atributo NOT NULL de column_schema de uma tabela não se propaga por consultas nela. Se a tabela T contiver uma coluna declarada como x INT64 NOT NULL, por exemplo, o CREATE TABLE dataset.newtable AS SELECT x FROM T criará uma tabela denominada dataset.newtable em que x é NULLABLE.

column_schema pode ser usado apenas na lista de definições de coluna das instruções CREATE TABLE. Não pode ser usado como um tipo em expressões. Por exemplo, CAST(1 AS INT64 NOT NULL) não é válido.

partition_expression

PARTITION BY é uma cláusula opcional que controla o particionamento de tabelas. partition_expression é uma expressão que determina como particionar a tabela. A expressão da partição pode conter os valores a seguir:

  • PARTITION BY DATE(_PARTITIONTIME): particiona a tabela usando o carimbo de data/hora baseado em data na _PARTITIONTIME pseudo column. Essa sintaxe é compatível apenas com CREATE TABLE sem a cláusula AS query_statement.
  • PARTITION BY DATE(<timestamp_column>): particiona a tabela usando a data da coluna TIMESTAMP.
  • PARTITION BY <date_column>: particiona a tabela usando a coluna DATE.

clustering_column_list

CLUSTER BY é uma cláusula opcional que controla o agrupamento de tabelas em cluster. clustering_column_list é uma lista separada por vírgula que determina como agrupar a tabela. A lista de colunas de agrupamento em cluster pode conter uma lista de até quatro agrupamentos de colunas em cluster.

table_option_list

A lista de opções permite que você defina opções de tabela, como um rótulo e um prazo de validade. Para incluir várias opções, use uma lista separada por vírgulas.

Especifique uma lista de opções de tabela no seguinte formato:

NAME=VALUE, ...

NAME e VALUE precisam ser uma das combinações a seguir:

NAME VALUE Detalhes
expiration_timestamp TIMESTAMP

Exemplo: expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC"

Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime.
partition_expiration_days

FLOAT64

Exemplo: partition_expiration_days=7

Essa propriedade é equivalente à propriedade de recurso de tabela timePartitioning.expirationMs, mas usa dias em vez de milissegundos. Um dia é equivalente a 86.400.000 milissegundos, ou 24 horas.

Só é possível definir essa propriedade se a tabela for particionada.
require_partition_filter

BOOL

Exemplo: require_partition_filter=true

Essa propriedade é equivalente à propriedade de recurso de tabela timePartitioning.requirePartitionFilter.

Só é possível definir essa propriedade se a tabela for particionada.
kms_key_name

STRING

Exemplo: kms_key_name="projects/project_id/locations/location/keyRings/keyring/cryptoKeys/key"

Essa propriedade é equivalente à propriedade de recurso de tabela encryptionConfiguration.kmsKeyName.

Consulte mais detalhes sobre Como proteger dados com chaves do Cloud KMS.
friendly_name

STRING

Exemplo: friendly_name="my_table"

Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName.
description

STRING

Exemplo: description="a table that expires in 2020"

Essa propriedade é equivalente à propriedade de recurso de tabela description.
labels

ARRAY<STRUCT<STRING, STRING>>

Exemplo: labels=[("org_unit", "development")]

Essa propriedade é equivalente à propriedade de recurso de tabela labels.

VALUE é uma expressão constante que contém apenas literais, parâmetros de consulta e funções escalares. Se a expressão constante for avaliada como null, a opção NAME correspondente será ignorada.

A expressão constante não pode conter os itens a seguir:

  • uma referência a uma tabela
  • subconsultas ou instruções SQL, como SELECT, CREATE e UPDATE
  • funções definidas pelo usuário, funções agregadas ou funções analíticas
  • as funções escalares a seguir:
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

column_option_list

column_option_list em column_schema permite que você especifique itens opcionais de coluna ou campo. As opções de coluna têm a mesma sintaxe e requisitos que as opções de tabela, mas com uma lista diferente de NAME e VALUE:

NAME VALUE Detalhes
description

STRING

Exemplo: description="a unique id"

Essa propriedade é equivalente à propriedade de recurso de tabela schema.fields[].description.

query_statement

Com a cláusula AS query_statement você especifica a consulta que servirá de ponto de base para criação da tabela. Consulte a referência de sintaxe SQL para conhecer o formato aceito de query_statement.

Limitações conhecidas:

  • Não é possível criar uma tabela particionada por tempo de ingestão a partir do resultado de uma consulta. Em vez disso, use uma instrução DDL CREATE TABLE para criar a tabela e, em seguida, uma instrução DML INSERT para inserir os dados.
  • Não é possível usar o modificador OR REPLACE para substituir uma tabela por um tipo diferente de particionamento. Em vez disso, use DROP para excluir a tabela e, em seguida, use uma instrução CREATE TABLE ... AS SELECT ... para recriá-la.

Exemplos de CREATE TABLE

Como criar uma nova tabela

A instrução DDL CREATE TABLE cria uma tabela com as opções especificadas. Se o nome da tabela existir no conjunto de dados, o erro a seguir será retornado:

Already Exists: project_id:dataset.table

No exemplo a seguir, você cria uma tabela particionada, denominada newtable, no mydataset. Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newtable`.

A tabela usa a seguinte partition_expression para particionar a tabela: PARTITION BY DATE(_PARTITIONTIME). Essa expressão particiona a tabela usando o carimbo de data/hora, baseado em data, na pseudocoluna _PARTITIONTIME.

O esquema da tabela contém duas colunas:

  • x: um inteiro, com a descrição "Um campo INTEGER opcional"

  • y: um STRUCT que contém duas colunas:

    • a: uma matriz de strings, com a descrição "Um campo STRING repetido"
    • b: um booleano

A lista de opções da tabela especifica:

  • prazo de validade da tabela: 1º de janeiro de 2020 às 00:00:00 UTC;
  • prazo de validade da partição: 1 dia;
  • descrição: uma tabela que expira em 2020;
  • rótulo: org_unit = development.

Para criar uma nova tabela usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.newtable
 (
   x INT64 OPTIONS(description="An optional INTEGER field"),
   y STRUCT<
     a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
     b BOOL
   >
 )
 PARTITION BY DATE(_PARTITIONTIME)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   partition_expiration_days=1,
   description="a table that expires in 2020, with each partition living for 24 hours",
   labels=[("org_unit", "development")]
 )

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE mydataset.newtable
 (
   x INT64 OPTIONS(description="An optional INTEGER field"),
   y STRUCT<
     a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
     b BOOL
   >
 )
 PARTITION BY DATE(_PARTITIONTIME)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   partition_expiration_days=1,
   description="a table that expires in 2020, with each partition living for 24 hours",
   labels=[("org_unit", "development")]
 )

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.newtable
(
  x INT64 OPTIONS(description="An optional INTEGER field"),
  y STRUCT<
    a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
    b BOOL
  >
)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS(
  expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
  partition_expiration_days=1,
  description="a table that expires in 2020, with each partition living for 24 hours",
  labels=[("org_unit", "development")]
)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma nova tabela a partir de uma existente

A instrução DDL CREATE TABLE ... AS SELECT cria uma tabela a partir de uma consulta. Se o nome da tabela existir no conjunto de dados, o erro a seguir será retornado:

Already Exists: project_id:dataset.table

O exemplo a seguir cria uma tabela denominada top_words no mydataset. Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.rainy_days`.

O esquema da tabela contém duas colunas:

  • corpus: nome de um corpus de Shakespeare

  • top_words: um ARRAY de STRUCTs contendo dois campos: word (uma STRING) e word_count (um INT64 com a contagem de palavras)

A lista de opções da tabela especifica:

  • descrição: 10 principais palavras por corpus de Shakespeare.

Para criar uma nova tabela usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.top_words
 OPTIONS(
   description="Top ten words per Shakespeare corpus"
 ) AS
 SELECT
   corpus,
   ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words
 FROM bigquery-public-data.samples.shakespeare
 GROUP BY corpus;

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE mydataset.top_words
 OPTIONS(
   description="Top ten words per Shakespeare corpus"
 ) AS
 SELECT
   corpus,
   ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words
 FROM bigquery-public-data.samples.shakespeare
 GROUP BY corpus;

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
     CREATE TABLE mydataset.top_words
     OPTIONS(
       description="Top ten words per Shakespeare corpus"
     ) AS
     SELECT
       corpus,
       ARRAY_AGG(STRUCT(word, word_count)
                 ORDER BY word_count DESC LIMIT 10) AS top_words
     FROM `bigquery-public-data.samples.shakespeare`
     GROUP BY corpus;'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma tabela somente se a tabela não existir

A instrução DDL CREATE TABLE IF NOT EXISTS cria uma tabela com as opções especificadas somente se o nome da tabela não existe no conjunto de dados. Se o nome da tabela existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.

No exemplo a seguir, criaremos uma tabela chamada newtable em mydataset somente se nenhuma tabela chamada newtable existir em mydataset. Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newtable`.

O esquema da tabela contém duas colunas:

  • x: um número inteiro

  • y: STRUCT que contém a (uma matriz de strings) e b (um booleano)

A lista de opções da tabela especifica:

  • prazo de validade: 1º de janeiro de 2020 às 00:00:00 UTC;
  • descrição: uma tabela que expira em 2020;
  • rótulo: org_unit = development.

Para criar uma nova tabela usando DDL somente se o nome da tabela não existir no conjunto de dados:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar ou substituir uma tabela

A instrução DDL CREATE OR REPLACE TABLE cria uma tabela com as opções especificadas. Se o nome da tabela existir no conjunto de dados, ela será substituída por uma tabela vazia.

No exemplo a seguir, será criada uma tabela chamada newtable em mydataset, e se newtable existir em mydataset, ela será substituída. Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newtable`.

O esquema da tabela contém duas colunas:

  • x: um número inteiro

  • y: STRUCT que contém a (uma matriz de strings) e b (um booleano)

A lista de opções da tabela especifica:

  • prazo de validade: 1º de janeiro de 2020 às 00:00:00 UTC;
  • descrição: uma tabela que expira em 2020;
  • rótulo: org_unit = development.

Para criar uma nova tabela usando DDL e substituir uma tabela com o mesmo nome:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )

  4. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )

  4. Clique em Executar consulta.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
 OPTIONS(
   expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
   description="a table that expires in 2020",
   labels=[("org_unit", "development")]
 )'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma tabela com colunas REQUIRED

O modificador NOT NULL na lista de definição de coluna de uma instrução CREATE TABLE especifica que uma coluna ou um campo é criado no modo REQUIRED.

O exemplo a seguir cria uma tabela denominada newtable no mydataset. Se o nome da tabela existir no conjunto de dados, o erro a seguir será retornado:

Already Exists: project_id:dataset.table

Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newtable`.

O esquema da tabela contém três colunas:

  • x: um número inteiro REQUIRED;
  • y: um REQUIRED STRUCT contendo a (uma matriz de strings), b (um booleano REQUIRED) e c (um flutuante NULLABLE);

  • z: uma string NULLABLE.

Para criar uma nova tabela com colunas REQUIRED usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE my_dataset.new_table (
   x INT64 NOT NULL,
   y STRUCT<
     a ARRAY<STRING>,
     b BOOL NOT NULL,
     c FLOAT64
   > NOT NULL,
   z STRING
 )

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE my_dataset.new_table (
   x INT64 NOT NULL,
   y STRUCT<
     a ARRAY<STRING>,
     b BOOL NOT NULL,
     c FLOAT64
   > NOT NULL,
   z STRING
 )

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
 CREATE TABLE my_dataset.new_table (
   x INT64 NOT NULL,
   y STRUCT<
     a ARRAY<STRING>,
     b BOOL NOT NULL,
     c FLOAT64
   > NOT NULL,
   z STRING
 )'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma tabela particionada

No exemplo a seguir, criamos uma tabela particionada denominada newtable em mydataset usando uma coluna DATE. Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newtable`.

O esquema da tabela contém duas colunas:

  • transaction_id: um número inteiro
  • transaction_date: uma data

A lista de opções da tabela especifica:

  • validade da partição: três dias;
  • descrição: uma tabela particionada por transaction_date.

Para criar uma nova tabela usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE)
 PARTITION BY transaction_date
 OPTIONS(
   partition_expiration_days=3,
   description="a table partitioned by transaction_date"
 )

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE)
 PARTITION BY transaction_date
 OPTIONS(
   partition_expiration_days=3,
   description="a table partitioned by transaction_date"
 )

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY transaction_date
OPTIONS(
  partition_expiration_days=3,
  description="a table partitioned by transaction_date"
)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma tabela particionada a partir do resultado de uma consulta

No exemplo a seguir, criamos uma tabela particionada denominada days_with_rain em mydataset usando uma coluna DATE. Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newtable`.

O esquema da tabela contém duas colunas:

  • data: a DATE da coleta de dados;
  • station_name: o nome da estação meteorológica como uma STRING;
  • prcp: a quantidade de precipitação em polegadas como um FLOAT64.

A lista de opções da tabela especifica:

  • validade da partição: um ano;
  • Descrição: estações meteorológicas com precipitação, divididas por dia.

Para criar uma nova tabela usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.days_with_rain
 PARTITION BY date
 OPTIONS (
   partition_expiration_days=365,
   description="weather stations with precipitation, partitioned by day"
 ) AS
 SELECT
   DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
   (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
    WHERE stations.usaf = stn) AS station_name,  -- Stations may have multiple names
   prcp
 FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather
 WHERE prcp != 99.9  -- Filter unknown values
   AND prcp > 0      -- Filter stations/days with no precipitation

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE mydataset.days_with_rain
 PARTITION BY date
 OPTIONS (
   partition_expiration_days=365,
   description="weather stations with precipitation, partitioned by day"
 ) AS
 SELECT
   DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
   (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
    WHERE stations.usaf = stn) AS station_name,  -- Stations may have multiple names
   prcp
 FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather
 WHERE prcp != 99.9  -- Filter unknown values
   AND prcp > 0      -- Filter stations/days with no precipitation

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.days_with_rain
PARTITION BY date
OPTIONS (
  partition_expiration_days=365,
  description="weather stations with precipitation, partitioned by day"
) AS
SELECT
  DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
  (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
   WHERE stations.usaf = stn) AS station_name,  -- Stations may have multiple names
  prcp
FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather
WHERE prcp != 99.9  -- Filter unknown values
  AND prcp > 0      -- Filter stations/days with no precipitation
'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma tabela em cluster

Exemplo 1

No exemplo a seguir, é criada uma tabela em cluster denominada myclusteredtable em mydataset. É uma tabela particionada por uma coluna TIMESTAMP e agrupada em cluster por uma coluna STRING denominada customer_id.

Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.myclusteredtable`.

O esquema da tabela contém três colunas:

  • timestamp: o momento da coleta de dados como um TIMESTAMP;
  • customer_id: o ID do cliente como STRING;
  • transaction_amount: o valor da transação como NUMERIC.

A lista de opções da tabela especifica:

  • validade da partição: 3 dias;
  • descrição: "uma tabela agrupada por customer_id".

Para criar uma tabela em cluster usando uma instrução DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(timestamp)
 CLUSTER BY customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL CREATE TABLE na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(timestamp)
 CLUSTER BY customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.myclusteredtable
(
  timestamp TIMESTAMP,
  customer_id STRING,
  transaction_amount NUMERIC
)
PARTITION BY DATE(timestamp)
CLUSTER BY customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by customer_id"
)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Exemplo 2

No exemplo a seguir, é criada uma tabela em cluster denominada myclusteredtable em mydataset. É uma tabela particionada por tempo de ingestão.

Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.myclusteredtable`.

O esquema da tabela contém três colunas:

  • timestamp: o momento da coleta de dados como um TIMESTAMP;
  • customer_id: o ID do cliente como STRING;
  • transaction_amount: o valor da transação como NUMERIC.

A lista de opções da tabela especifica:

  • validade da partição: 3 dias;
  • descrição: "uma tabela agrupada por customer_id".

Para criar uma tabela em cluster usando uma instrução DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(_PARTITIONTIME)
 CLUSTER BY
   customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL CREATE TABLE na área de texto Nova consulta.

     CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(_PARTITIONTIME)
 CLUSTER BY
   customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.myclusteredtable
(
  timestamp TIMESTAMP,
  customer_id STRING,
  transaction_amount NUMERIC
)
PARTITION BY DATE(_PARTITIONTIME)
CLUSTER BY
  customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by customer_id"
)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar uma tabela em cluster a partir do resultado de uma consulta

No exemplo a seguir, uma tabela em cluster chamada myclusteredtable é criada em mydataset usando o resultado de uma consulta. É uma tabela particionada por uma coluna TIMESTAMP.

Se você não tiver um projeto padrão configurado, anexe-o ao nome do conjunto de dados no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.myclusteredtable`.

O esquema da tabela contém três colunas:

  • timestamp: o momento da coleta de dados como um TIMESTAMP;
  • customer_id: o ID do cliente como STRING;
  • transaction_amount: o valor da transação como NUMERIC.

A lista de opções da tabela especifica:

  • validade da partição: 3 dias;
  • descrição: "uma tabela agrupada por customer_id".

Para criar uma tabela em cluster usando uma instrução DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(timestamp)
 CLUSTER BY
   customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )
 AS SELECT * FROM mydataset.myothertable

  4. Clique em Executar. Quando a consulta é concluída, a tabela aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL CREATE TABLE na área de texto Nova consulta.

     #standardSQL
 CREATE TABLE mydataset.myclusteredtable
 (
   timestamp TIMESTAMP,
   customer_id STRING,
   transaction_amount NUMERIC
 )
 PARTITION BY DATE(timestamp)
 CLUSTER BY
   customer_id
 OPTIONS (
   partition_expiration_days=3,
   description="a table clustered by customer_id"
 )
 AS SELECT * FROM mydataset.myothertable

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE mydataset.myclusteredtable
  (
    timestamp TIMESTAMP,
    customer_id STRING,
    transaction_amount NUMERIC
  )
PARTITION BY DATE(timestamp)
CLUSTER BY
  customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by customer_id"
)
AS SELECT * FROM mydataset.myothertable'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Instrução CREATE VIEW

Para criar uma visualização no BigQuery, use a instrução DDL CREATE VIEW.

Sintaxe

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
view_name
[OPTIONS(view_option_list)]
AS query_expression

Em que:

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} é uma das seguintes instruções:

  • CREATE VIEW: cria uma nova visualização.
  • CREATE VIEW IF NOT EXISTS: cria uma nova visualização somente se ela não existir no conjunto de dados especificado.
  • CREATE OR REPLACE VIEW: cria uma visualização e substitui uma visualização existente com o mesmo nome no conjunto de dados especificado.

view_name é o nome da visualização que você está criando. O nome da visualização precisa ser exclusivo por conjunto de dados. O nome da visualização pode:

  • conter até 1.024 caracteres;
  • conter letras (maiúsculas e minúsculas), números e sublinhados

view_option_list permite que você especifique outras opções de criação de visualizações, como um rótulo e um prazo de validade.

As instruções CREATE VIEW precisam obedecer às regras a seguir:

  • Apenas uma instrução CREATE é permitida.

query_expression é a expressão da consulta SQL padrão usada para definir a visualização.

view_option_list

A lista de opções permite que você defina opções de visualização, como um rótulo e um prazo de validade. Para incluir várias opções, use uma lista separada por vírgulas.

Especifique uma lista de opções de visualização no seguinte formato:

NAME=VALUE, ...

NAME e VALUE precisam ser uma das combinações a seguir:

NAME VALUE Detalhes
expiration_timestamp TIMESTAMP

Exemplo: expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC"

Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime.
friendly_name

STRING

Exemplo: friendly_name="my_view"

Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName.
description

STRING

Exemplo: description="a view that expires in 2020"

Essa propriedade é equivalente à propriedade de recurso de tabela description.
labels

ARRAY<STRUCT<STRING, STRING>>

Exemplo: labels=[("org_unit", "development")]

Essa propriedade é equivalente à propriedade de recurso de tabela labels.

VALUE é uma expressão constante que contém apenas literais, parâmetros de consulta e funções escalares. Se a expressão constante for avaliada como null, a opção NAME correspondente será ignorada.

A expressão constante não pode conter os itens a seguir:

  • uma referência a uma tabela
  • subconsultas ou instruções SQL, como SELECT, CREATE e UPDATE
  • funções definidas pelo usuário, funções agregadas ou funções analíticas
  • as funções escalares a seguir:
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

Exemplos

Como criar uma nova visualização

A instrução DDL CREATE VIEW cria uma visualização com as opções especificadas. Se o nome da visualização existir no conjunto de dados, o erro a seguir será retornado:

Already Exists: project_id:dataset.table

No exemplo a seguir, criaremos uma visualização chamada newview em mydataset. Ao usar uma instrução DDL para criar uma visualização, é preciso especificar o projeto, o conjunto de dados e a visualização no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newview`.

A visualização é definida usando a consulta SQL padrão a seguir:

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

A lista de opções de visualização especifica:

  • prazo de validade: 48 horas a partir do momento em que a visualização é criada;
  • nome amigável: newview;
  • descrição: uma visualização que expira em dois dias;
  • rótulo: org_unit = development.

Para criar uma nova visualização usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE VIEW `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

  4. Clique em Executar. Quando a consulta é concluída, a visualização aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE VIEW `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

  4. Clique em Executar consulta. Quando a consulta é concluída, a visualização aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE TABLE `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

JAVA

Chame o método BigQuery.create () para iniciar um job de consulta. Chame o método Job.waitFor () para aguardar a conclusão da consulta DDL.

google-cloud-examples/src/test/java/com/google/cloud/examples/bigquery/snippets/ITCloudSnippets.java 
// import com.google.cloud.bigquery.*;
// String projectId = "my-project";
// String datasetId = "my_dataset";
// String tableId = "new_view";
// BigQuery bigquery = BigQueryOptions.getDefaultInstance().toBuilder()
//     .setProjectId(projectId)
//     .build().getService();

String sql =
    String.format(
        "CREATE VIEW `%s.%s.%s`\n"
            + "OPTIONS(\n"
            + "  expiration_timestamp=TIMESTAMP_ADD(\n"
            + "    CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),\n"
            + "  friendly_name=\"new_view\",\n"
            + "  description=\"a view that expires in 2 days\",\n"
            + "  labels=[(\"org_unit\", \"development\")]\n"
            + ")\n"
            + "AS SELECT name, state, year, number\n"
            + "  FROM `bigquery-public-data.usa_names.usa_1910_current`\n"
            + "  WHERE state LIKE 'W%%';\n",
        projectId, datasetId, tableId);

// Make an API request to run the query job.
Job job = bigquery.create(JobInfo.of(QueryJobConfiguration.newBuilder(sql).build()));

// Wait for the query to finish.
job = job.waitFor();

QueryJobConfiguration jobConfig = (QueryJobConfiguration) job.getConfiguration();
System.out.printf(
    "Created new view \"%s.%s.%s\".\n",
    jobConfig.getDestinationTable().getProject(),
    jobConfig.getDestinationTable().getDataset(),
    jobConfig.getDestinationTable().getTable());

PYTHON

Chame o método Client.query () para iniciar um job de consulta. Chame o método QueryJob.result () para aguardar a conclusão da consulta DDL.

bigquery/docs/snippets.py 
# from google.cloud import bigquery
# project = 'my-project'
# dataset_id = 'my_dataset'
# table_id = 'new_view'
# client = bigquery.Client(project=project)

sql = """
CREATE VIEW `{}.{}.{}`
OPTIONS(
    expiration_timestamp=TIMESTAMP_ADD(
        CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
    friendly_name="new_view",
    description="a view that expires in 2 days",
    labels=[("org_unit", "development")]
)
AS SELECT name, state, year, number
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state LIKE 'W%'
""".format(
    project, dataset_id, table_id
)

job = client.query(sql)  # API request.
job.result()  # Waits for the query to finish.

print(
    'Created new view "{}.{}.{}".'.format(
        job.destination.project,
        job.destination.dataset_id,
        job.destination.table_id,
    )
)

Como criar uma visualização somente se ela não existir

A instrução DDL CREATE VIEW IF NOT EXISTS cria uma visualização com as opções especificadas somente se o nome da visualização não existir no conjunto de dados. Se o nome da visualização existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.

No exemplo a seguir, criaremos uma visualização chamada newview em mydataset somente se nenhuma visualização chamada newview existir em mydataset. Ao usar uma instrução DDL para criar uma visualização, é preciso especificar o projeto, o conjunto de dados e a visualização no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newview`.

A visualização é definida usando a consulta SQL padrão a seguir:

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

A lista de opções de visualização especifica:

  • prazo de validade: 48 horas a partir do momento em que a visualização é criada;
  • nome amigável: newview;
  • descrição: uma visualização que expira em dois dias;
  • rótulo: org_unit = development.

Para criar uma nova visualização usando DDL somente se o nome dela não existir no conjunto de dados:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

  4. Clique em Executar. Quando a consulta é concluída, a visualização aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

  4. Clique em Executar consulta. Quando a consulta é concluída, a visualização aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como criar ou substituir uma visualização

A instrução DDL CREATE OR REPLACE VIEW cria uma visualização com as opções especificadas. Se o nome da visualização existir no conjunto de dados, ela será substituída usando a expressão de consulta especificada.

No exemplo a seguir, criaremos uma visualização chamada newview em mydataset e, se newview existir em mydataset, ela será substituída. Ao usar uma instrução DDL para criar uma visualização, é preciso especificar o projeto, o conjunto de dados e a visualização no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.newview`.

A visualização é definida usando a consulta SQL padrão a seguir:

    SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

A lista de opções de visualização especifica:

  • prazo de validade: 48 horas a partir do momento em que a visualização é criada;
  • nome amigável: newview;
  • descrição: uma visualização que expira em dois dias;
  • rótulo: org_unit = development.

Para criar uma nova visualização usando DDL e substituir uma visualização com o mesmo nome:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     CREATE OR REPLACE VIEW `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

  4. Clique em Executar. Quando a consulta é concluída, a visualização aparece no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 CREATE OR REPLACE VIEW `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

  4. Clique em Executar consulta. Quando a consulta é concluída, a visualização aparece no painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
CREATE OR REPLACE VIEW `myproject.mydataset.newview`
 OPTIONS(
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
   friendly_name="newview",
   description="a view that expires in 2 days",
   labels=[("org_unit", "development")]
 )
 AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Instrução CREATE FUNCTION

O BigQuery é compatível com funções definidas pelo usuário (UDF, na sigla em inglês). Uma UDF permite criar uma função usando uma expressão SQL ou JavaScript. Essas funções aceitam colunas de entrada e executam ações, retornando o resultado dessas ações como um valor.

As UDFs podem ser permanentes ou temporárias. É possível reutilizar UDFs permanentes em várias consultas, mas as UDFs temporárias só podem ser usadas em uma única consulta. Para informações sobre UDFs temporárias, consulte a documentação das funções definidas pelo usuário.

Sintaxe da UDF

As funções permanentes definidas pelo usuário no BigQuery usam a seguinte sintaxe:

CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }
    dataset_name.function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

named_parameter:
  param_name param_type

sql_function_definition:
  AS (sql_expression)

javascript_function_definition:
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Essa sintaxe consiste nos seguintes componentes:

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }. Cria uma nova função. Uma função pode conter zero ou mais named_parameters. Para substituir qualquer função atual pelo mesmo nome, use a palavra-chave OR REPLACE. Para tratar a consulta como bem-sucedida e não tomar nenhuma ação caso uma função com o mesmo nome já exista, use a cláusula IF NOT EXISTS.
  • named_parameter. Consiste em um par de param_type e param_name separado por vírgula. O valor de param_type é um tipo de dados do BigQuery. Para uma UDF do SQL, o valor de param_type também pode ser ANY TYPE.
  • [RETURNS data_type]. Especifica o tipo de dados retornado pela função.
    • Se a função estiver definida em SQL, a cláusula RETURNS será opcional. Se a cláusula RETURNS for omitida, o BigQuery deduzirá o tipo de resultado da função a partir do corpo da função SQL quando uma consulta chamar a função. O tipo de retorno inferido não permanecerá com a função. Se a definição da função referenciar uma outra, o tipo de retorno inferido poderá depender do tipo de retorno da função referenciada e será atualizado automaticamente quando a função referenciada for atualizada.
    • Se a função estiver definida em JavaScript, a cláusula RETURNS será obrigatória. Consulte Tipos de dados de UDF do JavaScript compatíveis para mais informações sobre os valores permitidos de data_type.
  • AS (sql_expression). Especifica o código SQL que define a função.
  • [OPTIONS (library = library_array)]. Para uma UDF JavaScript, especifica uma matriz de bibliotecas JavaScript a incluir na definição de função.
  • AS javascript_code. Especifica a definição de uma função JavaScript. javascript_code é uma literal de string.

Estrutura de UDF de SQL

Crie UDFs de SQL usando a sintaxe a seguir:

CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }
  dataset_name.function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Parâmetros de UDF do SQL com modelo

Um parâmetro com modelo pode corresponder a mais de um tipo de argumento no tempo da chamada de função. Se uma assinatura de função incluir um parâmetro com modelo, o BigQuery permitirá que as chamadas de função transmitam um dos vários tipos de argumento para a função.

As assinaturas SQL de função definidas pelo usuário podem conter o seguinte valor com modelo param_type:

  • ANY TYPE. A função aceita uma entrada de qualquer tipo para este argumento. Se mais de um parâmetro tiver o tipo ANY TYPE, o BigQuery não aplicará qualquer relação entre esses argumentos no momento da criação da função. No entanto, transmitir os argumentos da função de tipos não compatíveis com a definição da função resultará em um erro no tempo de chamada.

Exemplos de UDF de SQL

No exemplo a seguir, é criada uma UDF de SQL permanente. Ele pressupõe que já existe um conjunto de dados chamado mydataset no projeto ativo. Caso não haja um conjunto de dados com esse nome, consulte a documentação sobre como criar bancos de dados.

CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
AS (x * y);

Depois de executar a instrução CREATE FUNCTION, é possível usar a nova função permanente definida pelo usuário em uma consulta separada. Substitua o editor de consultas pelo conteúdo a seguir e execute a consulta:

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, mydataset.multiplyInputs(x, y) as product
FROM numbers;

Com o exemplo acima, temos a seguinte saída:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

No exemplo a seguir, mostramos uma UDF de SQL que usa um parâmetro com modelo. A função resultante aceita argumentos de vários tipos.

CREATE FUNCTION mydataset.addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
  (x + 4) / y
);

Depois de executar a instrução CREATE FUNCTION, é possível usar a nova função permanente definida pelo usuário em uma consulta separada:

SELECT addFourAndDivideAny(3, 4) AS integer_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

Essa consulta retorna o resultado a seguir:

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

No exemplo a seguir, mostramos uma UDF de SQL que usa um parâmetro com modelo para retornar o último elemento de uma matriz de qualquer tipo.

CREATE FUNCTION mydataset.lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

Depois de executar a instrução CREATE FUNCTION, é possível usar a nova função permanente definida pelo usuário em uma consulta separada:

SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

A consulta acima retorna o resultado a seguir:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

Estrutura de UDF de JavaScript

Crie UDFs JavaScript permanentes usando a sintaxe a seguir.

CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }
  dataset_name.function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Exemplos de UDF de JavaScript

CREATE TEMP FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x*y;
""";

Depois de executar a instrução CREATE FUNCTION, é possível usar a nova UDF JavaScript permanente em uma consulta separada:

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

Com o exemplo acima, temos o seguinte resultado:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

É possível passar o resultado de um UDF como entrada para outro UDF. Por exemplo, crie um UDF permanente com a seguinte consulta:

CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x*y;
""";

Em seguida, execute outra consulta para criar uma segunda UDF permanente:

CREATE FUNCTION mydataset.divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS """
  return x/2;
""";

Agora, execute a seguinte consulta para usar as duas UDFs permanentes na mesma consulta:

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  mydataset.multiplyInputs(
    mydataset.divideByTwo(x), mydataset.divideByTwo(y)) as half_product
FROM numbers;

Com o exemplo acima, temos o seguinte resultado:

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

No exemplo a seguir, os valores de todos os campos denominados "foo" são somados na string JSON fornecida.

CREATE FUNCTION mydataset.SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js
  AS """
  function SumFoo(obj) {
    var sum = 0;
    for (var field in obj) {
      if (obj.hasOwnProperty(field) &amp;&amp; obj[field] != null) {
        if (typeof obj[field] == "object") {
          sum += SumFoo(obj[field]);
        } else if (field == "foo") {
          sum += obj[field];
        }
      }
    }
    return sum;
  }
  var row = JSON.parse(json_row);
  return SumFoo(row);
  """;

Depois de executar a instrução CREATE FUNCTION, é possível usar a nova função permanente definida pelo usuário em uma consulta separada:

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  mydataset.SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

Com o exemplo acima, temos o seguinte resultado:

+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Tipos de dados de UDF em JavaScript compatíveis

Para UDFs de JavaScript, os seguintes tipos de dados são compatíveis no BigQuery:

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • NUMERIC
  • STRING
  • STRUCT
  • TIMESTAMP

Codificações de tipo do SQL no JavaScript

Alguns tipos do SQL têm mapeamento direto para tipos do JavaScript, mas outros não.

Como o tipo inteiro de 64 bits não é compatível com o JavaScript, o INT64 não é aceito como um tipo de entrada para UDFs JavaScript. Em vez disso, use FLOAT64 para representar valores inteiros como um número ou STRING para representar valores inteiros como uma string.

O BigQuery é compatível com INT64 como um tipo de retorno em UDFs JavaScript. Nesse caso, o corpo da função JavaScript pode retornar um número JavaScript ou uma string. Em seguida, o BigQuery converte um desses tipos para INT64.

No BigQuery, os tipos são representados da seguinte maneira:

Tipo de dados do BigQuery Tipo de dados do JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING codificada em base64
FLOAT64 NUMBER
NUMERIC Se um valor NUMERIC puder ser representado exatamente como um valor de ponto flutuante IEEE 754 e não tiver uma parte fracionária, ele será codificado como um número. Esses valores estão no intervalo [-253, 253]. Caso contrário, ele será codificado como uma string.
STRING STRING
STRUCT OBJECT, onde cada campo STRUCT é um campo nomeado
TIMESTAMP DATE com um campo de microssegundo contendo a fração de microsecond do timestamp
DATE DATE

Regras de uso de aspas

Coloque o código JavaScript entre aspas. Para snippets simples com uma linha de código, use uma string entre aspas padrão:

CREATE FUNCTION mydataset.plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";

Quando o snippet contém aspas ou é composto por várias linhas, use blocos com aspas triplas:

CREATE FUNCTION mydataset.customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  var d = new Date();
  if (d.getHours() &lt; 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
  """;

Como incluir bibliotecas JavaScript

Estenda a funcionalidades das UDFs de JavaScript usando a seção OPTIONS. Essa seção permite especificar as bibliotecas de códigos JavaScript para a UDF.

CREATE FUNCTION mydataset.myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js
  OPTIONS (
    library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
  )
  AS
  """
      // Assumes 'doInterestingStuff' is defined in one of the library files.
      return doInterestingStuff(a, b);
  """;

SELECT mydataset.myFunc(3.14, 'foo');

No exemplo anterior, o código em lib1.js e lib2.js está disponível para qualquer código na seção javascript_code da UDF. Observe que é possível especificar arquivos de biblioteca usando um único elemento ou sintaxe de matriz.

UDFs e a IU da Web

É possível usar a IU da Web do BigQuery para criar funções definidas pelo usuário permanentes.

Como executar uma consulta para criar uma UDF permanente

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução da UDF na área de texto do Editor de consultas. Por exemplo:

      CREATE FUNCTION mydataset.timesTwo(x FLOAT64)
  RETURNS FLOAT64
    LANGUAGE js AS """
    return x*2;
  """;

  4. Clique em Executar.

  5. Depois de criar a função permanente definida pelo usuário, substitua o conteúdo do editor por uma nova consulta que o utilize:

      SELECT mydataset.timesTwo(numbers) as doubles
  FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;

  6. Clique em Executar.

UDFs e a ferramenta de linha de comando bq

É possível usar a ferramenta de linha de comando bq no SDK do Cloud para criar UDFs permanentes.

Use a seguinte sintaxe para executar uma consulta e criar uma UDF permanente:

bq query --use_legacy_sql=false '
  CREATE FUNCTION mydataset.AddTwo(x INT64) AS (x + 2);
'

Práticas recomendadas para UDFs em JavaScript

Pré-filtrar a entrada

Se for possível filtrar a entrada com facilidade antes de passá-la para uma UDF em JavaScript, a consulta provavelmente será mais rápida e mais barata.

Evitar o estado mutável permanente

Não armazene nem acesse o estado mutável nas chamadas à UDF em JavaScript.

Usar a memória de maneira eficiente

O ambiente de processamento do JavaScript tem uma memória disponível por consulta limitada. As consultas da UDF em JavaScript que acumulam muitos estados locais podem ter falhas devido ao esgotamento da memória.

Limitações

Os limites a seguir se aplicam às funções definidas pelo usuário permanentes e temporárias.

  • Os objetos DOM Window, Document e Node, bem como as funções que usam esses objetos, não são compatíveis.
  • Funções JavaScript que dependem de código nativo não são compatíveis.
  • Por causa da natureza não determinista, as consultas que invocam funções JavaScript definidas pelo usuário não podem usar resultados armazenados em cache.
  • Não é possível referenciar uma tabela em uma UDF.
  • Volume de dados de saída da UDF de JavaScript durante o processamento de uma única linha: aproximadamente 5 MB ou menos.
  • Limite de taxa simultânea para consultas de SQL legado com UDFs: seis consultas simultâneas.

    • O limite de taxa simultânea para consultas de SQL legado que contêm UDFs inclui consultas interativas e em lote. Consultas interativas que contêm UDFs também são consideradas em relação ao limite de taxa simultânea para consultas interativas. Esse limite não se aplica a consultas SQL padrão.

  • Número máximo de recursos de UDF em JavaScript, como blobs de código inline ou arquivos externos em um job de consulta: 50
  • Tamanho máximo de cada blob de código in-line: 32 KB
  • Tamanho máximo de cada recurso de código externo: 1 MB

Os limites a seguir se aplicam às funções definidas pelo usuário permanentes.
  • Comprimento máximo de um nome de função: 256 caracteres
  • Número máximo de argumentos: 256
  • Comprimento máximo de um nome de argumento: 128 caracteres
  • Profundidade máxima de uma cadeia de referência de uma função definida pelo usuário: 16
  • Profundidade máxima de um argumento ou de uma resposta do tipo STRUCT: 15
  • Número máximo de campos em um argumento ou resposta do tipo STRUCT por UDF: 1.024
  • Número máximo de UDFs únicas e referências de tabela por consulta: 1.000 Após a expansão completa, cada UDF poderá consultar até 1.000 UDFs e tabelas únicas. Esse número é uma combinação desses dois elementos.
  • Número máximo de bibliotecas JavaScript na instrução CREATE FUNCTION: 50
  • Comprimento máximo de caminhos de bibliotecas JavaScript incluídos: 5.000 caracteres
  • Taxa máxima de atualização por UDF: cinco a cada 10 segundos Após a criação da função, é possível atualizar cada função até cinco vezes a cada 10 segundos.
  • Cada blob de código in-line está limitado a um tamanho máximo de 32 KB.
  • Cada recurso de código JavaScript está limitado a um tamanho máximo de 1 MB.
  • As operações Bitwise em JavaScript lidam apenas com os 32 bits mais significativos.
  • Cada conjunto de dados pode conter apenas uma UDF permanente com o mesmo nome. No entanto, é possível criar uma UDF com o nome igual ao de uma tabela no mesmo banco de dados.
  • Ao fazer referência a uma UDF permanente de outra UDF permanente, é preciso qualificar o nome com o conjunto de dados. Por exemplo:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
  • Ao fazer referência a uma UDF permanente de uma visualização lógica, é preciso qualificar o nome com o projeto e conjunto de dados. Por exemplo:
    CREATE VIEW mydataset.sample_view AS SELECT my-project.mydataset.referencedFunction();

Os limites a seguir se aplicam às funções definidas pelo usuário temporárias.

  • Na criação de uma UDF temporária, function_name não pode conter pontos.
  • As visualizações lógicas não podem fazer referência a UDFs temporárias.

Instrução ALTER TABLE SET OPTIONS

Para definir as opções em uma tabela no BigQuery, use a instrução DDL ALTER TABLE SET OPTIONS.

Sintaxe

{ALTER TABLE | ALTER TABLE IF EXISTS}
table_name
SET OPTIONS(table_set_options_list)

Em que:

{ALTER TABLE | ALTER TABLE IF EXISTS} é uma das seguintes instruções:

  • ALTER TABLE: altera as opções em uma tabela existente.
  • ALTER TABLE IF EXISTS: altera as opções em uma tabela somente se ela existir.

table_name: é o nome da tabela que você está alterando.

table_set_options_list

A lista de opções permite que você defina opções de tabela, como um rótulo e um prazo de validade. Para incluir várias opções, use uma lista separada por vírgulas.

Especifique uma lista de opções de tabela no seguinte formato:

NAME=VALUE, ...

NAME e VALUE precisam ser uma das combinações a seguir:

NAME VALUE Detalhes
expiration_timestamp TIMESTAMP

Exemplo: expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC"

Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime.
partition_expiration_days

FLOAT64

Exemplo: partition_expiration_days=7

Essa propriedade é equivalente à propriedade de recurso de tabela timePartitioning.expirationMs, mas usa dias em vez de milissegundos. Um dia é equivalente a 86.400.000 milissegundos, ou 24 horas.

Só é possível definir essa propriedade se a tabela for particionada.
require_partition_filter

BOOL

Exemplo: require_partition_filter=true

Essa propriedade é equivalente à propriedade de recurso de tabela timePartitioning.requirePartitionFilter.

Só é possível definir essa propriedade se a tabela for particionada.
kms_key_name

STRING

Exemplo: kms_key_name="projects/project_id/locations/location/keyRings/keyring/cryptoKeys/key"

Essa propriedade é equivalente à propriedade de recurso de tabela encryptionConfiguration.kmsKeyName.

Consulte mais detalhes sobre Como proteger dados com chaves do Cloud KMS.
friendly_name

STRING

Exemplo: friendly_name="my_table"

Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName.
description

STRING

Exemplo: description="a table that expires in 2020"

Essa propriedade é equivalente à propriedade de recurso de tabela description.
labels

ARRAY<STRUCT<STRING, STRING>>

Exemplo: labels=[("org_unit", "development")]

Essa propriedade é equivalente à propriedade de recurso de tabela labels.

VALUE é uma expressão constante que contém apenas literais, parâmetros de consulta e funções escalares. Se a expressão constante for avaliada como null, a opção NAME correspondente será ignorada.

A expressão constante não pode conter os itens a seguir:

  • uma referência a uma tabela
  • subconsultas ou instruções SQL, como SELECT, CREATE e UPDATE
  • funções definidas pelo usuário, funções agregadas ou funções analíticas
  • as funções escalares a seguir:
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

A definição de VALUE substitui o valor existente dessa opção para a tabela (se havia um). Definir o VALUE como NULL limpa o valor da tabela para essa opção.

Exemplos

Como configurar o carimbo de data e hora de expiração e descrição em uma tabela

No exemplo a seguir, o carimbo de data/hora de validade em uma tabela é definido como sete dias a partir do horário da execução da instrução ALTER TABLE, e a descrição também é definida:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     ALTER TABLE mydataset.mytable
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="Table that expires seven days from now"
 )

  4. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 ALTER TABLE mydataset.mytable
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="Table that expires seven days from now"
 )

  4. Clique em Executar consulta.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
ALTER TABLE mydataset.mytable
SET OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
  description="Table that expires seven days from now"
)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como configurar o atributo "require partition filter" em uma tabela particionada

No exemplo a seguir, definimos o atributo timePartitioning.requirePartitionFilter em uma tabela particionada. Quando definido como "true", as consultas que fazem referência a essa tabela precisam usar um filtro na coluna de particionamento. Caso contrário, o BigQuery retorna um erro. Definir essa opção como "true" pode ajudar a evitar erros na consulta de mais dados do que o pretendido:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     ALTER TABLE mydataset.mypartitionedtable
 SET OPTIONS (require_partition_filter=true)

  4. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 ALTER TABLE mydataset.mypartitionedtable
 SET OPTIONS (require_partition_filter=true)

  4. Clique em Executar consulta.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
ALTER TABLE mydataset.mypartitionedtable
SET OPTIONS (require_partition_filter=true)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como limpar o carimbo de data e hora de expiração em uma tabela

No exemplo a seguir, limpamos o carimbo de data/hora de validade em uma tabela para que ela não expire:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     ALTER TABLE mydataset.mytable
 SET OPTIONS (expiration_timestamp=NULL)

  4. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 ALTER TABLE mydataset.mytable
 SET OPTIONS (expiration_timestamp=NULL)

  4. Clique em Executar consulta.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
ALTER TABLE mydataset.mytable
SET OPTIONS (expiration_timestamp=NULL)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Instrução ALTER VIEW SET OPTIONS

Para definir as opções de uma visualização no BigQuery, use a instrução DDL ALTER VIEW SET OPTIONS.

Sintaxe

{ALTER VIEW | ALTER VIEW IF EXISTS}
view_name
SET OPTIONS(view_set_options_list)

Em que:

{ALTER VIEW | ALTER VIEW IF EXISTS} é uma das seguintes instruções:

  • ALTER VIEW: altera as opções em uma visualização existente.
  • ALTER VIEW IF EXISTS: altera as opções em uma exibição somente visualização, se ela existir.

view_name: é o nome da visualização que você está alterando.

view_set_options_list

A lista de opções permite que você defina opções de visualização, como um rótulo e um prazo de validade. Para incluir várias opções, use uma lista separada por vírgulas.

Especifique uma lista de opções de visualização no seguinte formato:

NAME=VALUE, ...

NAME e VALUE precisam ser uma das combinações a seguir:

NAME VALUE Detalhes
expiration_timestamp TIMESTAMP

Exemplo: expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC"

Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime.
friendly_name

STRING

Exemplo: friendly_name="my_view"

Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName.
description

STRING

Exemplo: description="a view that expires in 2020"

Essa propriedade é equivalente à propriedade de recurso de tabela description.
labels

ARRAY<STRUCT<STRING, STRING>>

Exemplo: labels=[("org_unit", "development")]

Essa propriedade é equivalente à propriedade de recurso de tabela labels.

VALUE é uma expressão constante que contém apenas literais, parâmetros de consulta e funções escalares. Se a expressão constante for avaliada como null, a opção NAME correspondente será ignorada.

A expressão constante não pode conter os itens a seguir:

  • uma referência a uma tabela
  • subconsultas ou instruções SQL, como SELECT, CREATE e UPDATE
  • funções definidas pelo usuário, funções agregadas ou funções analíticas
  • as funções escalares a seguir:
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

A definição de VALUE substitui o valor existente dessa opção para a visualização (se havia um). Definir o VALUE como NULL limpa o valor da visualização para essa opção.

Exemplos

Como configurar o carimbo de data e hora de expiração e descrição em uma visualização

No exemplo a seguir, definimos o carimbo de data/hora de validade em uma visualização como sete dias a partir do horário da execução da instrução ALTER VIEW e também definimos a descrição:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     ALTER VIEW mydataset.myview
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="View that expires seven days from now"
 )

  4. Clique em Executar.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 ALTER VIEW mydataset.myview
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
   description="View that expires seven days from now"
 )

  4. Clique em Executar consulta.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
ALTER VIEW mydataset.myview
SET OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
  description="View that expires seven days from now"
)'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Instrução DROP TABLE

Para excluir uma tabela no BigQuery, use a instrução DDL DROP TABLE.

Sintaxe

{DROP TABLE | DROP TABLE IF EXISTS}
table_name

Em que:

{DROP TABLE | DROP TABLE IF EXISTS} é uma das seguintes instruções:

  • DROP TABLE: exclui uma tabela no conjunto de dados especificado.
  • DROP TABLE IF EXISTS: exclui uma tabela somente se ela existir no conjunto de dados especificado.

table_name é o nome da tabela que você está excluindo.

Exemplos

Como excluir tabelas

A instrução DDL DROP TABLE exclui uma tabela no conjunto de dados especificado. Se o nome da tabela não existir no conjunto de dados, o erro a seguir será retornado:

Error: Not found: Table myproject:mydataset.mytable

Se você estiver excluindo uma tabela em outro projeto, será preciso especificar o projeto, o conjunto de dados e a tabela no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.mytable`.

Para excluir uma tabela usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     DROP TABLE mydataset.mytable

  4. Clique em Executar. Quando a consulta é concluída, a tabela é removida do painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 DROP TABLE mydataset.mytable

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela é removida do painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
DROP TABLE mydataset.mytable'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como excluir uma tabela somente se ela existir

A instrução DDL DROP TABLE IF EXISTS excluirá uma tabela no conjunto de dados especificado somente se ela existir. Se o nome da tabela não existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.

Se você estiver excluindo uma tabela em outro projeto, será preciso especificar o projeto, o conjunto de dados e a tabela no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.mytable`.

Para excluir uma tabela usando DDL somente se a tabela existir:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     DROP TABLE IF EXISTS mydataset.mytable

  4. Clique em Executar. Quando a consulta é concluída, a tabela é removida do painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 DROP TABLE IF EXISTS mydataset.mytable

  4. Clique em Executar consulta. Quando a consulta é concluída, a tabela é removida do painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
DROP TABLE IF EXISTS mydataset.mytable'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Instrução DROP VIEW

Para excluir uma visualização no BigQuery, use a instrução DDL DROP VIEW.

Sintaxe

{DROP VIEW | DROP VIEW IF EXISTS}
view_name

Em que:

{DROP VIEW | DROP VIEW IF EXISTS} é uma das seguintes instruções:

  • DROP VIEW: exclui uma visualização no conjunto de dados especificado.
  • DROP VIEW IF EXISTS: exclui uma visualização somente se ela existe no conjunto de dados especificado.

view_name é o nome da visualização que você está excluindo.

Exemplos

Como excluir uma visualização

A instrução DDL DROP VIEW exclui uma visualização no conjunto de dados especificado. Se o nome da visualização não existir no conjunto de dados, o erro a seguir será retornado:

Error: Not found: Table myproject:mydataset.myview

Se você estiver excluindo uma visualização em outro projeto, será preciso especificar o projeto, o conjunto de dados e a visualização no seguinte formato: `project_id.dataset.table` (incluindo os acentos graves). Por exemplo, `myproject.mydataset.myview`.

Para excluir uma visualização usando DDL:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     DROP VIEW mydataset.myview

  4. Clique em Executar. Quando a consulta é concluída, a visualização é removida no painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 DROP VIEW mydataset.myview

  4. Clique em Executar consulta. Quando a consulta é concluída, a visualização é removida do painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
DROP VIEW mydataset.myview'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Como excluir uma visualização somente se ela existir

A instrução DDL DROP VIEW IF EXISTS excluirá uma visualização no conjunto de dados especificado somente se ela existir. Se o nome da visualização não existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.

Se você estiver excluindo uma visualização em outro projeto, será preciso especificar o projeto, o conjunto de dados e a visualização no seguinte formato: `project_id.dataset.table`, (incluindo os acentos graves). Por exemplo, `myproject.mydataset.myview`.

Para excluir uma visualização usando DDL somente se ela existir:

Console

  1. Abra a IU da Web do BigQuery no Console do GCP.
    Acessar o Console do GCP

  2. Clique em Escrever nova consulta.

    Escrever nova consulta

  3. Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:

     DROP VIEW IF EXISTS mydataset.myview

  4. Clique em Executar. Quando a consulta é concluída, a visualização é removida do painel de recursos.

IU clássica

  1. Acesse a IU da Web do BigQuery.

    Acessar a IU da Web do BigQuery

  2. Clique em Escrever consulta.

  3. Digite a instrução DDL na área de texto Nova consulta.

     #standardSQL
 DROP VIEW IF EXISTS mydataset.myview

  4. Clique em Executar consulta. Quando a consulta é concluída, a visualização é removida do painel de navegação.

CLI

Digite o comando bq query e forneça a instrução DDL como o parâmetro de consulta.

bq query --use_legacy_sql=false '
DROP VIEW IF EXISTS mydataset.myview'

API

Chame o método jobs.query e forneça a instrução DDL na propriedade query do corpo da solicitação.

A funcionalidade DDL amplia as informações retornadas por um recurso de jobs. statistics.query.statementType inclui estes outros valores para compatibilidade com DDL:

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query tem mais dois campos:

  • ddlOperationPerformed: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem os itens a seguir:
    • CREATE: a consulta criou o destino DDL.
    • SKIP: ambiente autônomo. Exemplos: CREATE TABLE IF NOT EXISTS foi enviado e a tabela existe. Ou DROP TABLE IF EXISTS foi enviado e a tabela não existe.
    • REPLACE: a consulta substituiu o destino DDL. Exemplo: CREATE OR REPLACE TABLE foi enviado e a tabela já existe.
    • DROP: a consulta excluiu o destino DDL.
  • ddlTargetTable: quando você envia uma instrução CREATE TABLE/VIEW ou DROP TABLE/VIEW, a tabela de destino é retornada como um objeto com três campos:
    • "projectId": string
    • "datasetId": string
    • "tableId": string

Instrução DROP FUNCTION

Sintaxe

DROP FUNCTION [ IF EXISTS ] [`project_name`.]dataset_name.function_name

Descrição

Exclui a função function_name do conjunto de dados dataset_name.

Cláusulas opcionais

IF EXISTS: só exclui a função se ela existir no conjunto de dados especificado.

project_name: especifica o projeto que contém a função. Se a função não estiver localizada no projeto atual, project_name precisará estar presente.

Exemplos

A instrução de exemplo a seguir exclui a função parseJsonAsStruct contida no conjunto de dados mydataset.

DROP FUNCTION mydataset.parseJsonAsStruct;

A instrução de exemplo a seguir exclui a função parseJsonAsStruct do conjunto de dados sample_dataset no projeto other_project.

DROP FUNCTION `other_project`.sample_dataset.parseJsonAsStruct;
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Esta página
Comentários sobre a documentação
BigQuery
Comentários sobre o produto
Precisa de ajuda? Acesse nossa página de suporte.