Com as instruções de linguagem de definição de dados (DDL, na sigla em inglês), é possível criar e modificar recursos do BigQuery usando a sintaxe de consulta do SQL padrão. Atualmente, você pode usar comandos DDL no BigQuery para:
- criar tabelas, visualizações e funções definidas pelo usuário (UDF, na sigla em inglês);
- alterar tabelas;
- excluir tabelas e visualizações.
Como executar instruções DDL
É possível executar instruções DDL usando o Console do Cloud, com a ferramenta de linha de comando bq
, chamando a API REST jobs.query
ou de maneira programática, usando as bibliotecas de cliente da API BigQuery.
Console
Acesse a página do BigQuery no Console do Cloud.
Clique em Escrever nova consulta.
Insira a instrução DDL na área de texto do Editor de consultas. Por exemplo:
CREATE TABLE mydataset.newtable ( x INT64 )
Clique em Executar.
bq
Digite o comando bq query
e forneça a instrução DDL como o parâmetro de consulta. Defina a sinalização use_legacy_sql
como false
.
bq query --use_legacy_sql=false \ 'CREATE TABLE mydataset.newtable ( x INT64 )'
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 dois campos extras:
ddlOperationPerformed
: a operação DDL realizada, possivelmente dependente da existência do destino DDL. Os valores atuais incluem:CREATE
: a consulta criou o destino DDL.SKIP
: exemplos de ambiente autônomo:CREATE TABLE IF NOT EXISTS
foi enviado e a tabela existe. OuDROP 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çãoCREATE TABLE/VIEW
ouDROP 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
Job.waitFor()
para aguardar a conclusão da consulta DDL.
Node.js
Python
Chame o método Client.query()
para iniciar um job de consulta. Chame o
QueryJob.result()
para aguardar a conclusão da consulta DDL.
Instrução CREATE TABLE
Para criar uma tabela no BigQuery, use a instrução DDL CREATE TABLE
.
{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE} [[project_name.]dataset_name.]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 instruções a seguir:
CREATE TABLE
: cria uma nova tabela.CREATE TABLE IF NOT EXISTS
: cria uma nova tabela somente se a tabela não existir no conjunto de dados especificado.CREATE OR REPLACE TABLE
: cria uma tabela e substitui uma tabela atual de mesmo nome no conjunto de dados especificado.
Nas instruções CREATE TABLE
, siga estas regras:
- 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áusulaas 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áusulaas 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.
Caminho da tabela
project_name
é o nome do projeto onde você está criando a tabela.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto tiver
caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados onde você está criando a tabela.
O padrão na solicitação é defaultDataset
.
table_name
é o nome da tabela que você está criando.
Ao criar uma tabela no BigQuery, o nome dela precisa ser exclusivo para cada conjunto de dados. Esse nome pode:
- conter até 1.024 caracteres;
- conter caracteres Unicode na categoria L (letra), M (marca), N (número), Pc (conector, inclusive sublinhado), Pd (travessão), Zs (espaço). Para mais informações, consulte Categoria geral.
Por exemplo, os nomes de tabela a seguir são válidos: table-01
, ग्राहक
,
00_お客様
, étudiant
.
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çãoNOT NULL
opcional para tipos diferentes deARRAY
.column_schema
também é compatível com opções em colunas de nível superior e camposSTRUCT
.
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 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 seguintes valores:
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 comCREATE TABLE
sem a cláusulaAS query_statement
.PARTITION BY _PARTITIONDATE
: particiona a tabela usando_PARTITIONDATE pseudo column
. Essa sintaxe é compatível apenas comCREATE TABLE
sem a cláusulaAS query_statement
e é equivalente a usarPARTITION BY DATE(_PARTITIONTIME)
.PARTITION BY DATE(<timestamp_column>)
: particiona a tabela usando a data da colunaTIMESTAMP
PARTITION BY RANGE_BUCKET(<integer_column>, GENERATE_ARRAY(start, end[, interval]))
: particiona a tabela usando o intervalo da colunaINTEGER
especificado.start
é o início do particionamento de intervalo (inclusivo),end
é o final do particionamento de intervalo (exclusivo) einterval
é a largura de cada intervalo dentro da partição. O padrão é 1, caso não esteja definido.PARTITION BY TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR })
: particiona a tabela usando a data/hora/mês/ano da colunaTIMESTAMP
PARTITION BY DATETIME_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR })
: particiona a tabela usando a data/hora/mês/ano da colunaDATETIME
PARTITION BY <date_column>
: particiona a tabela usando a colunaDATE
clustering_column_list
CLUSTER BY
é uma cláusula opcional que controla o clustering de tabelas.
clustering_column_list
é uma lista separada por vírgulas que termina como agrupar a tabela em cluster. 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 formato a seguir:
NAME=VALUE, ...
NAME
e VALUE
precisam ser uma das combinações a seguir:
NAME |
VALUE |
Detalhes |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime. |
partition_expiration_days |
|
Exemplo: 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 |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela timePartitioning.requirePartitionFilter. Só é possível definir essa propriedade se a tabela for particionada. |
kms_key_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela encryptionConfiguration.kmsKeyName. Consulte mais detalhes sobre Como proteger dados com chaves do Cloud KMS. |
friendly_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName. |
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela description. |
labels |
|
Exemplo: 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 correspondente NAME
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
eUPDATE
- 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
A column_option_list
em column_schema
permite especificar opções de
coluna ou campo opcionais. 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
s e VALUE
s:
NAME |
VALUE |
Detalhes |
---|---|---|
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela schema.fields[].description. |
query_statement
A cláusula AS query_statement
especifica a consulta que serve de base para a criação da tabela. Consulte a referência de sintaxe do SQL para saber a forma de query_statement
compatível.
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 DMLINSERT
para inserir os dados. - Não é possível usar o modificador
OR REPLACE
para substituir uma tabela por outro tipo de particionamento. Em vez disso,DROP
a tabela e use uma instruçãoCREATE TABLE ... AS SELECT ...
para recriá-la.
Tabelas temporárias
Para criar uma tabela temporária, use a palavra-chave TEMP
ou TEMPORARY
quando usar a instrução CREATE TABLE
.
Sintaxe
{ CREATE {TEMP|TEMPORARY} TABLE | CREATE {TEMP|TEMPORARY} TABLE IF NOT EXISTS | CREATE OR REPLACE {TEMP|TEMPORARY} TABLE } ...
Além do uso de TEMP
ou TEMPORARY
, a sintaxe é idêntica à sintaxe CREATE TABLE
.
Não qualifique nomes de tabelas temporários. Isto é, não use um qualificador de projeto ou de conjunto de dados. Tabelas temporárias são criadas automaticamente em um conjunto de dados especial.
É possível consultar uma tabela temporária pelo nome dependendo da duração do script atual. Para mais informações, consulte Scripting em um SQL padrão. Isso inclui tabelas criadas por um procedimento sem o script. Não é possível consultar uma tabela depois que o script for completamente criado.
Após a finalização de um script, a tabela temporária existirá por até 24 horas. Em vez de ser salva com o nome que você deu, ela receberá um nome aleatório. Para ver os dados e a estrutura da tabela, acesse o console do BigQuery, clique em Histórico de consulta e escolha a consulta que criou a tabela temporária. Depois, na linha Tabela de destino, clique em Tabela temporária.
Não é possível compartilhar tabelas temporárias, e elas não são visíveis usando listas padrões ou outros métodos de manipulação de tabela. Não há cobrança pelo armazenamento de tabelas temporárias.
Para criar uma tabela temporária:
CREATE TEMP TABLE Example
(
x INT64,
y STRING
);
INSERT INTO Example
VALUES (5, 'foo');
INSERT INTO Example
VALUES (6, 'bar');
SELECT *
FROM Example;
Esse script retorna a saída a seguir:
+-----+---+-----+
| Row | x | y |
+-----+---|-----+
| 1 | 5 | foo |
| 2 | 6 | bar |
+-----+---|-----+
Para excluir uma tabela temporária explicitamente antes da conclusão do script, execute uma instrução DROP TABLE
:
CREATE TEMP TABLE foo(x INT64);
SELECT * FROM foo; -- Succeeds
DROP TABLE foo;
SELECT * FROM foo; -- Results in an error
Quando tabelas temporárias são usadas com um conjunto de dados padrão, os nomes de tabela não qualificados se referem a:
- uma tabela temporária, se houver;
- caso contrário, uma tabela no conjunto de dados padrão.
A exceção é para instruções CREATE TABLE
, em que a tabela de destino é considerada uma tabela temporária somente se a palavra-chave TEMP
ou TEMPORARY
estiver presente.
Por exemplo, considere o script a seguir:
-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);
-- Create temporary table t1
CREATE TEMP TABLE t1 (x INT64);
-- This statement will select from the temporary table
SELECT * FROM t1;
-- Drop the temporary table
DROP TABLE t1;
-- Now that the temporary table is dropped, this statement will select from
-- the table in the default dataset
SELECT * FROM t1;
Para indicar explicitamente que está se referindo a uma tabela temporária, qualifique o nome da tabela com _SESSION
:
-- Create a temp table CREATE TEMP TABLE t1 (x INT64); -- Create a temp table using the `_SESSION` qualifier CREATE TEMP TABLE _SESSION.t2 (x INT64); -- Select from a temporary table using the `_SESSION` qualifier SELECT * FROM _SESSION.t1;
Se você usar o qualificador _SESSION
para uma consulta de uma tabela temporária que não existe, você receberá um erro indicando que a tabela não existe. Por exemplo, se não houver uma tabela temporária chamada t3
, você receberá o erro mesmo que uma tabela chamada t3
exista no conjunto de dados padrão.
Não é possível usar _SESSION
para criar uma tabela não temporária:
CREATE TABLE _SESSION.t4 (x INT64); -- Fails
Exemplos
Como criar uma nova tabela
No exemplo a seguir, criamos uma tabela particionada denominada newtable
em mydataset
.
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 _PARTITIONDATE OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", partition_expiration_days=1, description="a table that expires in 2025, with each partition living for 24 hours", labels=[("org_unit", "development")] )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.newtable
, o qualificador de tabela pode ser `myproject.mydataset.newtable`
.
Se o nome da tabela existir no conjunto de dados, o erro a seguir será retornado:
Already Exists: project_id:dataset.table
A tabela usa partition_expression
a seguir para particionar a tabela: PARTITION BY _PARTITIONDATE
. Essa expressão particiona a tabela usando a data na pseudocoluna _PARTITIONDATE
.
O esquema da tabela tem 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 2025 às 00:00:00 UTC;
- Prazo de validade da partição: 1 dia;
- Descrição: uma tabela que expira em 2025;
- Rótulo: org_unit = development.
Como criar uma nova tabela de uma atual
No exemplo a seguir, criamos uma tabela chamada top_words
em mydataset
de uma consulta:
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;
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.top_words
, o qualificador de tabela pode ser `myproject.mydataset.top_words`
.
Se o nome da tabela existir no conjunto de dados, o erro a seguir será retornado:
Already Exists: project_id:dataset.table
O esquema da tabela contém duas colunas:
- corpus: nome de um corpus de Shakespeare
top_words: um
ARRAY
deSTRUCT
s contendo dois campos:word
(umaSTRING
) eword_count
(umINT64
com a contagem de palavras)
A lista de opções da tabela especifica:
- Descrição: 10 principais palavras por corpus de Shakespeare.
Como criar uma tabela somente se a tabela não existir
O exemplo a seguir cria uma tabela denominada newtable
em mydataset
somente se não houver uma tabela denominada newtable
em mydataset
. Se o nome da tabela existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será realizada.
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.newtable
, o qualificador de tabela pode ser `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 2025 às 00:00:00 UTC;
- Descrição: uma tabela que expira em 2025;
- Rótulo: org_unit = development.
Como criar ou substituir uma tabela
No exemplo a seguir, será criada uma tabela chamada newtable
em mydataset
, e se newtable
existir em mydataset
, ela será substituída por uma tabela vazia.
CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>) OPTIONS( expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC", description="a table that expires in 2025", labels=[("org_unit", "development")] )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.newtable
, o qualificador de tabela pode ser `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 2025 às 00:00:00 UTC;
- Descrição: uma tabela que expira em 2025;
- Rótulo: org_unit = development.
Como criar uma tabela com colunas REQUIRED
No exemplo a seguir, criamos uma tabela denominada newtable
em mydataset
. O modificador NOT
NULL
na lista de definição de coluna de uma instrução CREATE TABLE
especifica que uma coluna ou campo seja criado no modo REQUIRED
.
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 )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de my_dataset.new_table
, o qualificador de tabela pode ser `myproject.my_dataset.new_table`
.
Se o nome da tabela existir no conjunto de dados, o erro a seguir será retornado:
Already Exists: project_id:dataset.table
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 booleanoREQUIRED
) e c (um flutuanteNULLABLE
); z: uma string
NULLABLE
.
Como criar uma tabela particionada
No exemplo a seguir, criamos uma tabela particionada denominada newtable
em mydataset
usando uma coluna DATE
.
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" )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.newtable
, o qualificador de tabela pode ser `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
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
.
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 can 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
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.days_with_rain
, o qualificador de tabela pode ser `myproject.mydataset.days_with_rain`
.
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.
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
.
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" )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.myclusteredtable
, o qualificador de tabela pode ser `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".
Exemplo 2
No exemplo a seguir, é criada uma tabela em cluster denominada myclusteredtable
em mydataset
. É uma tabela particionada por tempo de processamento.
CREATE TABLE mydataset.myclusteredtable ( 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" )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.myclusteredtable
, o qualificador de tabela pode ser `myproject.mydataset.myclusteredtable`
.
O esquema da tabela contém duas colunas:
- 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".
Exemplo 3
No exemplo a seguir, é criada uma tabela em cluster denominada myclusteredtable
em mydataset
. A tabela não é particionada.
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" )
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.myclusteredtable
, o qualificador de tabela pode ser `myproject.mydataset.myclusteredtable`
.
O esquema da tabela contém duas colunas:
- 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:
- Descrição: "uma tabela agrupada por customer_id".
Como criar uma tabela em cluster com base no resultado de uma consulta
Exemplo 1
No exemplo a seguir, é criada uma tabela em cluster denominada myclusteredtable
em mydataset
usando o resultado de uma consulta. É uma tabela particionada por uma coluna TIMESTAMP
.
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
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.myclusteredtable
, o qualificador de tabela pode ser `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".
Exemplo 2
No exemplo a seguir, é criada uma tabela em cluster denominada myclusteredtable
em mydataset
usando o resultado de uma consulta. A tabela não é particionada.
CREATE TABLE mydataset.myclusteredtable ( customer_id STRING, transaction_amount NUMERIC ) CLUSTER BY customer_id OPTIONS ( description="a table clustered by customer_id" ) AS SELECT * FROM mydataset.myothertable
Se você não tiver configurado um projeto padrão, anexe um ID do projeto ao nome do conjunto de dados no SQL de exemplo e coloque o nome entre crases caso project_id
contenha caracteres especiais: `project_id.dataset.table`
. Assim, em vez de mydataset.myclusteredtable
, o qualificador de tabela pode ser `myproject.mydataset.myclusteredtable`
.
O esquema da tabela contém duas colunas:
- 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:
- Descrição: "uma tabela agrupada por customer_id".
Instrução CREATE VIEW
Para criar uma visualização no BigQuery, use a instrução DDL CREATE VIEW
.
{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} [[project_name.]dataset_name.]view_name [OPTIONS(view_option_list)] AS query_expression
Em que:
{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
é uma das instruções a seguir:
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 que substitui uma visualização de mesmo nome no conjunto de dados especificado.
project_name
é o nome do projeto onde você está criando a visualização.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto tiver
caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados onde você está criando a visualização.
O padrão na solicitação é defaultDataset
.
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 especificar
outras opções de criação de visualizações, como um rótulo e
um prazo de validade.
Nas instruções CREATE VIEW
, siga estas regras:
- 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 formato a seguir:
NAME=VALUE, ...
NAME
e VALUE
precisam ser uma das combinações a seguir:
NAME |
VALUE |
Detalhes |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime. |
friendly_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName. |
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela description. |
labels |
|
Exemplo: 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 correspondente NAME
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
eUPDATE
- 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
Projeto padrão no corpo da visualização
Se a visualização for criada no mesmo projeto usado para executar a instrução CREATE VIEW
,
o corpo da visualização query_expression
poderá referir-se a entidades sem
especificar o projeto. O projeto padrão é o proprietário
da visualização. Considere o exemplo de consulta abaixo.
CREATE VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
Depois de executar a consulta CREATE VIEW
acima no projeto myProject
,
execute SELECT * FROM myProject.myDataset.myView
. Independentemente do projeto
escolhido para executar essa consulta SELECT
, a tabela referenciada anotherDataset.myTable
é sempre resolvida em relação ao projeto myProject
.
Se a visualização não for criada no mesmo projeto usado para executar a instrução CREATE VIEW
,
todas as referências no corpo da visualização query_expression
precisarão ser
qualificadas com os IDs do projeto. Por exemplo, a consulta de amostra CREATE VIEW
anterior
é inválida se for executada em um projeto diferente de myProject
.
Exemplos
Como criar uma nova visualização
No exemplo a seguir, criamos uma visualização denominada newview
em mydataset
:
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`
Se o nome da visualização existir no conjunto de dados, o erro a seguir será retornado:
Already Exists: project_id:dataset.table
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.
Como criar uma visualização somente se ela não existir
No exemplo a seguir, criaremos uma visualização chamada newview
em mydataset
somente se nenhuma visualização chamada newview
existir em mydataset
. Se o nome da visualização existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.
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`
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.
Como criar ou substituir uma visualização
No exemplo a seguir, criamos uma visualização chamada newview
em mydataset
, e se newview
existir em mydataset
, ela será substituída usando a expressão de consulta especificada.
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`
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.
Instrução CREATE MATERIALIZED VIEW
Para criar uma visualização materializada no BigQuery, use a instrução DDL CREATE
MATERIALIZED VIEW
.
{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS } [[project_name.]dataset_name.]materialized_view_name [OPTIONS(materialized_view_option_list)] AS query_expression
Em que:
{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS }
é uma das instruções a seguir:
CREATE MATERIALIZED VIEW
: cria uma nova visualização materializada.CREATE MATERIALIZED VIEW IF NOT EXISTS
: cria uma nova visualização materializada somente se ela ainda não existir no conjunto de dados especificado.
project_name
é o nome do projeto onde você está criando a visualização materializada.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto tiver
caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
Se o project_name
for omitido ou for igual ao projeto que executa
essa consulta DDL, o último também será usado como o projeto padrão das
referências a tabelas, funções etc., em query_expression
(observação: o projeto
padrão das referências é corrigido e não depende das consultas futuras
que invocam a nova visualização materializada). Caso contrário, todas as referências em query_expression
precisam ser
qualificadas com projetos.
dataset_name
é o nome do conjunto de dados onde você está criando a visualização materializada.
O padrão na solicitação é defaultDataset
.
materialized_view_name
é o nome da visualização materializada que está sendo criada.
Esse nome precisa ser exclusivo por conjunto de dados. Ele
pode:
- Conter até 1.024 caracteres
- Conter letras (maiúsculas e minúsculas), números e sublinhados
materialized_view_option_list
permite que você
especifique outras opções de visualização materializadas, como a ativação
e o intervalo da atualização, um rótulo e um
prazo de validade.
Nas instruções CREATE MATERIALIZED VIEW
, siga estas regras:
- Apenas uma instrução
CREATE
é permitida.
query_expression
é a expressão de consulta SQL padrão usada para definir a visualização
materializada.
materialized_view_option_list
A lista de opções permite que você defina opções de visualização materializadas, como a ativação e o intervalo de atualização, 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 materializada no formato a seguir:
NAME=VALUE, ...
NAME
e VALUE
precisam ser uma das combinações a seguir:
NAME |
VALUE |
Detalhes |
---|---|---|
enable_refresh |
BOOLEAN |
Exemplo: |
refresh_interval_minutes |
FLOAT64 |
Exemplo: |
expiration_timestamp |
TIMESTAMP |
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime. |
friendly_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName. |
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela description. |
labels |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela labels. |
Projeto padrão no corpo da visualização materializada
Se a visualização materializada for criada no mesmo projeto usado para executar a instrução CREATE MATERIALIZED VIEW
,
o corpo da visualização materializada query_expression
poderá referir-se a entidades sem
especificar o projeto. O projeto padrão é o proprietário
da visualização materializada. Considere o exemplo de consulta abaixo.
CREATE MATERIALIZED VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
Depois de executar a consulta CREATE MATERIALIZED VIEW
acima no projeto myProject
,
execute SELECT * FROM myProject.myDataset.myView
. Independentemente do projeto
escolhido para executar essa consulta SELECT
, a tabela referenciada anotherDataset.myTable
é sempre resolvida em relação ao projeto myProject
.
Se a visualização materializada não for criada no mesmo projeto usado para executar a instrução CREATE VIEW
,
todas as referências no corpo da visualização materializada query_expression
precisarão ser
qualificadas com IDs de projeto. Por exemplo, a consulta de amostra CREATE MATERIALIZED VIEW
anterior
é inválida se for executada em um projeto diferente de myProject
.
Exemplos
Como criar uma nova visualização materializada
O exemplo a seguir cria uma visualização materializada denominada new_mv
em mydataset
:
CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_mv",
description="a materialized view that expires in 2 days",
labels=[("org_unit", "development")].
enable_refresh=true,
refresh_interval_minutes=20
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
Se o nome da visualização materializada existir no conjunto de dados, este erro será retornado:
Already Exists: project_id:dataset.materialized_view
Ao usar uma instrução DDL para criar uma visualização materializada, especifique o projeto, o conjunto de dados e a visualização materializada no seguinte formato: `project_id.dataset.materialized_view`
(incluindo os acentos graves, se project_id
tiver caracteres especiais); por exemplo, `myproject.mydataset.new_mv`
.
A visualização materializada é 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 materializada especifica:
- Prazo de validade: 48 horas a partir da criação da visualização materializada;
- Nome amigável: new_mv;
- Descrição: uma visualização materializada que expira em dois dias;
- Rótulo: org_unit = development.
- Atualização ativada: true;
- Intervalo de atualização: 20 minutos.
Como criar uma visualização materializada apenas se ela não existir
O exemplo a seguir cria uma visualização materializada chamada new_mv
no mydataset
apenas se não houver nenhuma com o nome new_mv
no mydataset
. Se esse nome existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será realizada.
CREATE MATERIALIZED VIEW IF NOT EXISTS `myproject.mydataset.new_mv`
OPTIONS(
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
friendly_name="new_mv",
description="a view that expires in 2 days",
labels=[("org_unit", "development")],
enable_refresh=false
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`
A visualização materializada é 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 materializada especifica:
- Prazo de validade: 48 horas a partir do momento em que a visualização é criada;
- Nome amigável: new_mv;
- Descrição: uma visualização que expira em dois dias;
- Rótulo: org_unit = development.
- Atualização ativada: falso.
Instrução CREATE EXTERNAL TABLE
A instrução CREATE EXTERNAL TABLE
cria uma tabela externa. Com as tabelas externas,
os dados de consulta do BigQuery são armazenados fora do
armazenamento do BigQuery. Para mais informações sobre tabelas externas, consulte
Introdução às fontes de dados externas.
CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] [[project_name.]dataset_name.]table_name [( column_name column_schema, ... )] [WITH PARTITION COLUMNS [( partition_column_name partition_column_type, ... )] ] OPTIONS ( external_table_option_list, ... );
Em que:
project_name
é o nome do projeto onde você está criando a tabela. O padrão é o projeto que executa essa consulta DDL.dataset_name
é o nome do conjunto de dados em que você está criando a tabela.table_name
é o nome da tabela externa.column_name
é o nome de uma coluna na tabela.column_schema
especifica o esquema da coluna. Ele usa a mesma sintaxe da definiçãocolumn_schema
na instruçãoCREATE TABLE
. Se você não incluir essa cláusula, o esquema será detectado automaticamente pelo BigQuery.partition_column_name
é o nome de uma coluna de partição. Inclua este campo se os dados externos usarem um layout particionado do Hive. Para mais informações, consulte Layouts de dados compatíveis.partition_column_type
é o tipo da coluna de partição.external_table_option_list
especifica uma lista de opções para criar a tabela externa.
external_table_option_list
A lista especifica opções para a criação da tabela externa. As opções format
e uris
são obrigatórias. Especifique a lista de opções no seguinte
formato: NAME=VALUE, ...
Opções | |
---|---|
allow_jagged_rows |
Se Aplica-se a dados CSV. |
allow_quoted_newlines |
Se Aplica-se a dados CSV. |
compression |
O tipo de compactação da fonte de dados. Os valores aceitos são:
Aplica-se a dados CSV e JSON. |
description |
Uma descrição da tabela. |
enable_logical_types |
Se Aplica-se a dados Avro. |
encoding |
A codificação de caracteres dos dados. Os valores aceitos incluem:
Aplica-se a dados CSV. |
expiration_timestamp |
A hora em que esta tabela expira. Se não for especificado, a tabela não expirará. Exemplo: |
field_delimiter |
O separador de campos em um arquivo CSV. Aplica-se a dados CSV. |
format |
O formato dos dados externos.
Os valores aceitos são: O valor |
hive_partition_uri_prefix |
Um prefixo comum para todos os URIs de origem antes do início da codificação da chave de partição. Aplica-se apenas a tabelas externas particionadas pelo Hive. Aplica-se aos dados Avro, CSV, JSON, Parquet e ORC. Exemplo: |
ignore_unknown_values |
Se for Aplica-se a dados CSV e JSON. |
max_bad_records |
O número máximo de registros corrompidos a serem ignorados durante a leitura dos dados. Aplica-se a: dados CSV, JSON e Planilhas. |
null_marker |
A string que representa os valores Aplica-se a dados CSV. |
projection_fields |
Uma lista de propriedades da entidade a serem carregadas. Aplica-se aos dados do Datastore. |
quote |
A string usada para citar seções de dados em um arquivo CSV. Se os dados
contiverem caracteres de nova linha entre aspas, defina também a
propriedade Aplica-se a dados CSV. |
require_hive_partition_filter |
Se Aplica-se aos dados Avro, CSV, JSON, Parquet e ORC. |
sheet_range |
Intervalo de uma planilha do Planilhas a ser consultada. Aplicável aos dados do Planilhas. Exemplo: |
skip_leading_rows |
O número de linhas na parte superior de um arquivo a ser ignorado na leitura dos dados. Aplicável aos dados CSV e Planilhas. |
uris |
Uma matriz de URIs totalmente qualificados para os locais de dados externos. Exemplo: |
A instrução CREATE EXTERNAL TABLE
não é compatível com a criação de tabelas
externas temporárias.
Para criar uma tabela particionada externamente, use a cláusula WITH PARTITION COLUMNS
para especificar os detalhes do esquema de partição. O BigQuery
valida as definições de coluna em relação ao local externo de dados. A declaração
do esquema precisa seguir estritamente a ordem dos campos do caminho
externo. Para mais informações sobre o particionamento externo, consulte
Como consultar dados particionados externamente.
Exemplos
No exemplo a seguir, criamos uma tabela externa a partir de vários URIs. O formato dos dados é CSV. Este exemplo usa detecção automática de esquema.
CREATE EXTERNAL TABLE dataset.CsvTable OPTIONS (
format = 'CSV',
uris = ['gs://bucket/path1.csv', 'gs://bucket/path2.csv']
);
O exemplo a seguir cria uma tabela externa a partir de um arquivo CSV e especifica
explicitamente o esquema. Ele também especifica o delimitador do campo ('|'
) e define o
número máximo de registros inválidos permitidos.
CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable
(
x INT64,
y STRING
)
OPTIONS (
format = 'CSV',
uris = ['gs://bucket/path1.csv'],
field_delimiter = '|',
max_bad_records = 5
);
No exemplo a seguir, criamos uma tabela particionada externamente. Ele usa a detecção automática de esquema para detectar o esquema de arquivo e o layout de particionamento do Hive.
Por exemplo, se o caminho externo for
gs://bucket/path/field_1=first/field_2=1/data.csv
, as colunas da partição
serão field_1
(STRING
) e field_2
(INT64
).
CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris=['gs://bucket/path/*'],
format=csv,
hive_partition_uri_prefix='gs://bucket/path'
);
O exemplo a seguir cria uma tabela particionada externamente especificando
explicitamente as colunas de partição. Neste exemplo, consideramos que o caminho do arquivo
externo tem o padrão gs://bucket/path/field_1=first/field_2=1/data.csv
.
CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64
)
OPTIONS (
uris=['gs://bucket/path/*'],
format=csv,
hive_partition_uri_prefix='gs://bucket/path'
);
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 mais informações sobre o uso de UDFs, consulte Funções definidas pelo usuário.
Sintaxe da UDF
Para criar uma UDF permanente, use a seguinte sintaxe:
CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] [[project_name.]dataset_name.]function_name ([named_parameter[, ...]]) [RETURNS data_type] { sql_function_definition | javascript_function_definition }
Para criar uma UDF temporária, use a seguinte sintaxe:
CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS] 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: [determinism_specifier] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code determinism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }
Essa sintaxe é composta pelos componentes a seguir:
CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }. Cria ou atualiza uma função. 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áusulaIF NOT EXISTS
.project_name é o nome do projeto onde você está criando a função. O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo:`google.com:my_project`
).dataset_name é o nome do conjunto de dados onde você está criando a função. O padrão na solicitação é
defaultDataset
.named_parameter. Consiste em um par de
param_name
eparam_type
separado por vírgula. O valor deparam_type
é um tipo de dados do BigQuery. Para uma UDF SQL, o valor deparam_type
também pode serANY TYPE
.determinism_specifier. Aplica-se somente a funções JavaScript definidas pelo usuário. Indica ao BigQuery se o resultado da consulta pode ser armazenado em cache. Pode ser um dos seguintes valores:
DETERMINISTIC
: a função sempre retorna o mesmo resultado quando os mesmos argumentos são transmitidos. O resultado da consulta é potencialmente armazenável em cache. Por exemplo, se a funçãoadd_one(i)
sempre retornari + 1
, a função será determinista.NOT DETERMINISTIC
: a função nem sempre retorna o mesmo resultado quando os mesmos argumentos são transmitidos e, portanto, não é armazenável em cache. Por exemplo, seadd_random(i)
retornari + rand()
, a função não é determinista e o BigQuery não usará resultados armazenados em cache.Se todas as funções invocadas forem DETERMINISTIC, o BigQuery tentará armazenar o resultado em cache, a menos que os resultados não possam ser armazenados em cache por outros motivos. Para saber mais, veja Como usar resultados de consulta armazenados em cache.
[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áusulaRETURNS
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. - Se a função estiver definida em JavaScript, a cláusula
RETURNS
será obrigatória. Para mais informações sobre valores permitidos paradata_type
, consulte Tipos de dados de UDF JavaScript compatíveis.
- Se a função estiver definida em SQL, a cláusula
AS (sql_expression). Especifica o código SQL que define a função.
[OPTIONS (library = library_array)]. Em uma UDF JavaScript, especifica uma matriz de bibliotecas JavaScript que serão incluídas na definição de função.
AS javascript_code. Especifica a definição de uma função JavaScript.
javascript_code
é um literal de string.
Estrutura de UDF de SQL
Crie UDFs de SQL usando a sintaxe a seguir:
CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]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 param_type
= ANY TYPE
pode corresponder a mais de um tipo de argumento quando a função é chamada.
- Se mais de um parâmetro tiver o tipo
ANY TYPE
, o BigQuery não aplicará qualquer relação entre esses argumentos. - O tipo de retorno da função não pode ser
ANY TYPE
. Ele precisa ser omitido, ou seja, determinado automaticamente com base emsql_expression
, ou um tipo explícito. - Transmitir os argumentos de tipos da função que não são compatíveis com a definição da função resultará em um erro no tempo de chamada.
Projeto padrão no corpo da UDF do SQL
Se a UDF do SQL for criada no mesmo projeto usado para executar a instrução CREATE FUNCTION
,
o corpo da UDF sql_expression
poderá referenciar entidades sem especificar
o projeto. O projeto padrão é o
proprietário da UDF. Considere o exemplo de consulta abaixo.
CREATE FUNCTION myProject.myDataset.myFunction() AS (anotherDataset.anotherFunction());
Depois de executar a consulta CREATE FUNCTION
acima no projeto myProject
, execute SELECT myProject.myDataset.myFunction()
. Independentemente do projeto escolhido
para executar essa consulta SELECT
, a função referenciada anotherDataset.anotherFunction
é sempre resolvida em relação ao projeto myProject
.
Se a UDF não for criada no mesmo projeto usado para executar a instrução CREATE FUNCTION
,
todas as referências no corpo da UDF sql_expression
precisarão ser
qualificadas com os IDs do projeto. Por exemplo, a consulta de amostra CREATE FUNCTION
anterior
é inválida se for executada em um projeto diferente de myProject
.
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
, a nova função permanente definida pelo usuário pode ser usada 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
, a nova função permanente definida pelo usuário pode ser usada 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
, a nova função permanente definida pelo usuário pode ser usada 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 [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS] [[`project_name`.]dataset_name.]function_name ([named_parameter[, ...]]) RETURNS data_type [DETERMINISTIC | NOT DETERMINISTIC] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code
Consulte os Tipos de dados de UDF do JavaScript compatíveis para mais informações sobre os valores permitidos para data_type
e tipos de parâmetros.
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
, a nova UDF JavaScript permanente pode ser usada 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 resultado a seguir:
+-----+-----+--------------+
| 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 resultado a seguir:
+-----+-----+--------------+
| 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) && 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
, a nova função permanente definida pelo usuário pode ser usada 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 resultado a seguir:
+---------------------------------------------------------------------+---------+
| 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 |
+---------------------------------------------------------------------+---------+
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() < 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
. Esta
seção permite especificar bibliotecas de código 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 o Console do Cloud
É possível usar o Console do Cloud para criar funções definidas pelo usuário permanentes.
Como executar uma consulta para criar uma UDF permanente
Acesse a página do BigQuery no Console do Cloud.
Clique em Escrever nova consulta.
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; """;
Clique em Executar.
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;
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 sintaxe a seguir para executar uma consulta e criar uma UDF permanente:
bq query --use_legacy_sql=false '
CREATE FUNCTION mydataset.AddTwo(x INT64) AS (x + 2);
'
Instrução CREATE PROCEDURE
Cria um procedimento, que é um bloco de instruções que podem ser chamadas de outras consultas.
CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] [[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] ) [OPTIONS(procedure_option_list)] BEGIN statement_list END; procedure_argument: [procedure_argument_mode] argument_name argument_type
procedure_argument_mode: IN | OUT | INOUT
Descrição
project_name
é o nome do projeto onde você está criando o procedimento.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto tiver
caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados onde você está criando o procedimento.
O padrão na solicitação é defaultDataset
.
statement_list
é uma lista de instruções
do BigQuery. Uma lista de instruções é uma série de instruções que terminam com um ponto e vírgula.
argument_type
é qualquer tipo
válido do BigQuery.
procedure_argument_mode
especifica se um argumento é uma entrada, uma saída
ou ambas.
Os procedimentos podem ser chamados recursivamente.
procedure_option_list
O procedure_option_list
permite especificar opções de procedimento. As opções
de procedimento têm a mesma sintaxe e requisitos que as opções de tabela, mas com uma
lista diferente de NAME
s e VALUE
s:
NAME |
VALUE |
Detalhes |
---|---|---|
strict_mode |
|
Exemplo: Se Embora Se O valor padrão é |
Modo de argumento
IN
indica que o argumento é somente uma entrada do procedimento. Você pode
especificar uma variável ou uma expressão de valor para argumentos IN
.
OUT
indica que o argumento é uma saída do procedimento. Um argumento OUT
é inicializado como NULL
junto do procedimento. Para argumentos OUT
, é necessário especificar uma variável.
INOUT
indica que o argumento é uma entrada e uma saída do procedimento. Para argumentos INOUT
, é necessário especificar uma variável. Um argumento
INOUT
pode ser referenciado no corpo de um procedimento como uma variável e novos valores
atribuídos.
Se não forem especificados IN
, OUT
nem INOUT
, o argumento é tratado
como IN
.
Escopo de variáveis
Se uma variável for declarada fora de um procedimento, passar o argumento INOUT ou OUT para um procedimento e esse procedimento atribuir um novo valor à variável, esse novo valor fica visível fora do procedimento.
Os valores declarados em um procedimento não são visíveis fora do procedimento e vice-versa.
Um argumento OUT
ou INOUT
pode ser atribuído a um valor usando SET
. Nesse caso,
o valor modificado é visível fora do procedimento. Se o procedimento sair
com sucesso, o valor do argumento OUT
ou INOUT
será o valor final
atribuído à variável INOUT
.
Tabelas temporárias existem enquanto o script durar. Por isso, se um procedimento cria uma tabela temporária, o autor da chamada do procedimento também poderá referenciar a tabela temporária.
Projeto padrão no corpo do procedimento
Corpos de procedimentos podem referenciar entidades sem especificar o projeto. O
projeto padrão é o proprietário do procedimento, não necessariamente o
projeto usado para executar a instrução CREATE PROCEDURE
. Considere o exemplo de consulta
abaixo.
CREATE PROCEDURE myProject.myDataset.QueryTable()
BEGIN
SELECT * FROM anotherDataset.myTable;
END;
Depois de criar o procedimento acima, execute a consulta
CALL myProject.myDataset.QueryTable()
. Independentemente do projeto
escolhido para executar essa consulta CALL
, a tabela referenciada anotherDataset.myTable
é sempre resolvida em relação ao projeto myProject
.
Exemplos
No exemplo a seguir, criaremos um procedimento que usa x
como argumento de entrada e retorna x
como saída. Como não há um modo para o argumento delta
, ele é um argumento de entrada. O procedimento é composto por um bloco com uma única instrução, que atribui a soma dos dois argumentos de entrada a x
.
CREATE PROCEDURE mydataset.AddDelta(INOUT x INT64, delta INT64)
BEGIN
SET x = x + delta;
END;
No exemplo a seguir, é chamado o procedimento AddDelta
do exemplo acima, passando a variável accumulator
nas duas vezes. Como as alterações em x
dentro de AddDelta
são visíveis fora de AddDelta
, essas chamadas de procedimento têm um incremento accumulator
de 8.
DECLARE accumulator INT64 DEFAULT 0;
CALL mydataset.AddDelta(accumulator, 5);
CALL mydataset.AddDelta(accumulator, 3);
SELECT accumulator;
Isso retorna o resultado a seguir:
+-------------+
| accumulator |
+-------------+
| 8 |
+-------------+
No exemplo a seguir, criaremos o procedimento SelectFromTablesAndAppend
, que leva target_date
como um argumento de entrada e retorna rows_added
como saída.
O procedimento cria uma tabela temporária DataForTargetDate
a partir de uma consulta. Em seguida, ele calcula o número de linhas em DataForTargetDate
e atribui o resultado a rows_added
. Em seguida, ele insere uma nova linha em TargetTable
, passando o valor de target_date
como um dos nomes de coluna. Por fim, ele descarta a tabela DataForTargetDate
e retorna rows_added
.
CREATE PROCEDURE mydataset.SelectFromTablesAndAppend(
target_date DATE, OUT rows_added INT64)
BEGIN
CREATE TEMP TABLE DataForTargetDate AS
SELECT t1.id, t1.x, t2.y
FROM dataset.partitioned_table1 AS t1
JOIN dataset.partitioned_table2 AS t2
ON t1.id = t2.id
WHERE t1.date = target_date
AND t2.date = target_date;
SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);
SELECT id, x, y, target_date -- note that target_date is a parameter
FROM DataForTargetDate;
DROP TABLE DataForTargetDate;
END;
No exemplo a seguir, declaramos uma variável rows_added
e a transmitimos como um argumento ao procedimento SelectFromTablesAndAppend
do exemplo anterior com o valor de CURRENT_DATE
. Em seguida, é retornada uma mensagem informando quantas linhas foram adicionadas.
DECLARE rows_added INT64;
CALL mydataset.SelectFromTablesAndAppend(CURRENT_DATE(), rows_added);
SELECT FORMAT('Added %d rows', rows_added);
Instrução ALTER TABLE SET OPTIONS
Para definir as opções de uma tabela no BigQuery, use a instrução DDL ALTER TABLE SET OPTIONS
.
ALTER TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name SET OPTIONS(table_set_options_list)
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a tabela especificada
não existe. Se ausente, a consulta falhará quando a tabela especificada não existir.
project_name
é o nome do projeto que contém a tabela que você está
alterando. O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a tabela que você está
alterando. O padrão na solicitação é defaultDataset
.
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 formato a seguir:
NAME=VALUE, ...
NAME
e VALUE
precisam ser uma das combinações a seguir:
NAME |
VALUE |
Detalhes |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime. |
partition_expiration_days |
|
Exemplo: 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 |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela timePartitioning.requirePartitionFilter. Só é possível definir essa propriedade se a tabela for particionada. |
kms_key_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela encryptionConfiguration.kmsKeyName. Consulte mais detalhes sobre Como proteger dados com chaves do Cloud KMS. |
friendly_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName. |
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela description. |
labels |
|
Exemplo: 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 correspondente NAME
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
eUPDATE
- 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 atual 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 validade 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:
ALTER TABLE mydataset.mytable SET OPTIONS ( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), description="Table that expires seven days from now" )
Como configurar o atributo "require partition filter" em uma tabela particionada
No exemplo a seguir, definimos o atributo timePartitioning.requirePartitionFilter
em uma tabela particionada .
ALTER TABLE mydataset.mypartitionedtable SET OPTIONS (require_partition_filter=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.
Como apagar 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:
ALTER TABLE mydataset.mytable SET OPTIONS (expiration_timestamp=NULL)
Instrução ALTER TABLE ADD COLUMN
A instrução ALTER TABLE ADD COLUMN
adiciona uma ou mais colunas novas a um
esquema de tabela existente. Para mais informações sobre modificações de esquema no
BigQuery, consulte
Como modificar esquemas de tabelas.
ALTER TABLE [[project_name.]dataset_name.]table_name
ADD COLUMN [IF NOT EXISTS] column_name column_schema [, ...]
Em que:
project_name
é o nome do projeto que contém a tabela. O padrão é o projeto que executa essa consulta DDL.dataset_name
é o nome do conjunto de dados que contém a tabela.table_name
é o nome da tabela a ser alterada. A tabela já precisa existir e ter um esquema.column_name
é o nome da coluna a ser adicionada.column_schema
é o esquema da coluna. Esse esquema usa a mesma sintaxe que o esquema de coluna da instruçãoCREATE TABLE
.
Não é possível usar esta instrução para criar:
- Colunas particionadas.
- Colunas em cluster.
- Colunas aninhadas dentro de campos
RECORD
existentes.
Não é possível adicionar uma coluna REQUIRED
a um esquema de tabela existente. No entanto, é possível
criar uma coluna REQUIRED
aninhada como parte de um novo campo RECORD
.
Sem a cláusula IF NOT EXISTS
, se a tabela já contiver uma coluna com
esse nome, a instrução retornará um erro. Se a cláusula IF NOT EXISTS
estiver
incluída e o nome da coluna já existir, nenhum erro será retornado e
nenhuma medida será tomada.
O valor da nova coluna para linhas existentes é definido como um dos seguintes itens:
NULL
se a nova coluna foi adicionada com o modoNULLABLE
. Esse é o modo padrão.- Um
ARRAY
vazio se a nova coluna foi adicionada com o modoREPEATED
.
Exemplos
Como adicionar colunas
O exemplo a seguir adiciona as seguintes colunas a uma tabela existente chamada
mytable
:
- Coluna
A
do tipoSTRING
. - Coluna
B
do tipoGEOGRAPHY
. - Coluna
C
do tipoNUMERIC
com o modoREPEATED
. - Coluna
D
do tipoDATE
com uma descrição.
ALTER TABLE mydataset.mytable
ADD COLUMN A STRING,
ADD COLUMN IF NOT EXISTS B GEOGRAPHY,
ADD COLUMN C ARRAY<NUMERIC>,
ADD COLUMN D DATE OPTIONS(description="my description")
Se qualquer uma das colunas chamadas A
, C
ou D
já existir, a instrução falhará.
Se a coluna B
já existir, a instrução será bem-sucedida devido à cláusula IF NOT
EXISTS
.
Como adicionar uma coluna RECORD
O exemplo a seguir adiciona uma coluna chamada A
do tipo STRUCT
que contém as
seguintes colunas aninhadas:
- Coluna
B
do tipoGEOGRAPHY
. - Coluna
C
do tipoINT64
com o modoREPEATED
. - Coluna
D
do tipoINT64
com o modoREQUIRED
. - Coluna
E
do tipoTIMESTAMP
com uma descrição.
ALTER TABLE mydataset.mytable
ADD COLUMN A STRUCT<
B GEOGRAPHY,
C ARRAY<INT64>,
D INT64 NOT NULL,
E TIMESTAMP OPTIONS(description="creation time")
>
A consulta falhará se a tabela já tiver uma coluna chamada A
, mesmo que essa
coluna não contenha nenhuma das colunas aninhadas especificadas.
O novo STRUCT
denominado A
é anulável, mas a coluna aninhada D
em A
é
obrigatória para qualquer valor STRUCT
de A
.
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
.
ALTER VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name SET OPTIONS(view_set_options_list)
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a visualização especificada não
existe. Se ausente, a consulta falhará quando a visualização especificada não existir.
project_name
é o nome do projeto que contém a visualização que você está
alterando. O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a visualização que você está
alterando. O padrão na solicitação é defaultDataset
.
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 formato a seguir:
NAME=VALUE, ...
NAME
e VALUE
precisam ser uma das combinações a seguir:
NAME |
VALUE |
Detalhes |
---|---|---|
expiration_timestamp |
TIMESTAMP |
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime. |
friendly_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName. |
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela description. |
labels |
|
Exemplo: 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 correspondente NAME
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
eUPDATE
- 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 atual 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:
ALTER VIEW mydataset.myview SET OPTIONS ( expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY), description="View that expires seven days from now" )
Instrução ALTER MATERIALIZED VIEW SET OPTIONS
Para definir as opções em uma visualização materializada no BigQuery, use a instrução DDL ALTER MATERIALIZED VIEW SET OPTIONS
.
ALTER MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]materialized_view_name SET OPTIONS(materialized_view_set_options_list)
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a visualização especificada não
existe. Se ausente, a consulta falhará quando a visualização especificada não existir.
project_name
é o nome do projeto que contém a visualização materializada que você está
alterando. O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a visualização materializada que você está
alterando. O padrão na solicitação é defaultDataset
.
materialized_view_name
é o nome da visualização materializada que você está alterando.
materialized_view_set_options_list
A lista de opções permite que você defina opções de visualização materializadas, como a ativação e o intervalo de atualização, 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 materializada no formato a seguir:
NAME=VALUE, ...
NAME
e VALUE
precisam ser uma das combinações a seguir:
NAME |
VALUE |
Detalhes |
---|---|---|
enable_refresh |
BOOLEAN |
Exemplo: |
refresh_interval_minutes |
FLOAT64 |
Exemplo: |
expiration_timestamp |
TIMESTAMP |
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela expirationTime. |
friendly_name |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela friendlyName. |
description |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela description. |
labels |
|
Exemplo: Essa propriedade é equivalente à propriedade de recurso de tabela labels. |
A configuração de VALUE
substitui o valor existente dessa opção para a
visualização materializada, se houver. Definir o VALUE
como NULL
limpa o
valor da visualização materializada para essa opção.
Exemplos
Como definir o estado e o intervalo de atualização em uma visualização materializada
O exemplo a seguir ativa a atualização e define o intervalo dela para 20 minutos em uma visualização materializada:
ALTER MATERIALIZED VIEW mydataset.my_mv
SET OPTIONS (
enable_refresh=true,
refresh_interval_minutes=20
)
Instrução DROP TABLE
Para excluir uma tabela no BigQuery, use a instrução DDL DROP TABLE
.
DROP TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a tabela especificada
não existe. Se ausente, a consulta falhará quando a tabela especificada não existir.
project_name
é o nome do projeto que contém a tabela a ser excluída.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a tabela a ser excluída.
O padrão na solicitação é defaultDataset
.
table_name
: o nome da tabela a ser excluída.
Exemplos
Como excluir tabelas
No exemplo a seguir, uma tabela chamada mytable
no mydataset
é excluída:
DROP TABLE mydataset.mytable
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
Como excluir uma tabela somente se ela existir
O exemplo a seguir exclui uma tabela chamada mytable
em mydataset
somente se a tabela existir. Se o nome da tabela não existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.
DROP TABLE IF EXISTS mydataset.mytable
Instrução DROP EXTERNAL TABLE
A instrução DROP EXTERNAL TABLE
exclui uma tabela externa.
DROP EXTERNAL TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
Em que:
project_name
é o nome do projeto que contém a tabela. O padrão é o projeto que executa essa consulta DDL.dataset_name
é o nome do conjunto de dados que contém a tabela.table_name
é o nome da tabela a ser excluída.
Sem a cláusula IF EXISTS
, se a tabela externa não existir, a
instrução retornará um erro. Se a cláusula IF EXISTS
estiver incluída e a tabela
não existir, nenhum erro será retornado e nenhuma medida será tomada.
Se table_name
existir, mas não for uma tabela externa, a instrução retornará o seguinte
erro:
Cannot drop table_name which has type TYPE. An
external table was expected.
A instrução DROP EXTERNAL
só remove a definição de tabela externa do
BigQuery. Os dados armazenados no local externo não são
afetados.
Exemplos
O exemplo a seguir descarta a tabela externa denominada external_table
do
conjunto de dados mydataset
. Ela retornará um erro se a tabela externa não existir.
DROP EXTERNAL TABLE mydataset.external_table
O exemplo a seguir descarta a tabela externa denominada external_table
do
conjunto de dados mydataset
. Se a tabela externa não existir, nenhum erro será retornado.
DROP EXTERNAL TABLE IF EXISTS mydataset.external_table
Instrução DROP VIEW
Para excluir uma visualização no BigQuery, use a instrução DDL DROP VIEW
.
DROP VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a visualização especificada não
existe. Se ausente, a consulta falhará quando a visualização especificada não existir.
project_name
é o nome do projeto que contém a visualização a ser excluída.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a visualização a ser excluída.
O padrão na solicitação é defaultDataset
.
view_name
é o nome da visualização que você está excluindo.
Exemplos
Como excluir uma visualização
No exemplo a seguir, uma visualização chamada myview
em mydataset
é excluída:
DROP VIEW mydataset.myview
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
Como excluir uma visualização somente se ela existir
O exemplo a seguir exclui uma visualização chamada myview
em mydataset
somente se a visualização existir. Se o nome da visualização não existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será tomada.
DROP VIEW IF EXISTS mydataset.myview
Instrução DROP MATERIALIZED VIEW
Para excluir uma visualização materializada no BigQuery, use a instrução DDL DROP
MATERIALIZED VIEW
.
DROP MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]mv_name
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a visualização materializada especificada
não existe. Se ausente, a consulta falhará quando a visualização materializada especificada não existir.
project_name
é o nome do projeto que contém a visualização materializada a ser excluída.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a visualização materializada a ser excluída.
O padrão na solicitação é defaultDataset
.
mv_name
é o nome da visualização materializada que você está excluindo.
Exemplos
Como excluir uma visualização materializada
O exemplo a seguir exclui uma visualização materializada chamada my_mv
em mydataset
:
DROP MATERIALIZED VIEW mydataset.my_mv
Se o nome da visualização materializada não existir no conjunto de dados, o seguinte erro será retornado:
Error: Not found: Table myproject:mydataset.my_mv
Se você estiver excluindo uma visualização materializada em outro projeto, especifique o projeto, o conjunto de dados e a visualização materializada no seguinte formato: `project_id.dataset.materialized_view`
(incluindo crases, se project_id
tiver caracteres especiais); por exemplo, `myproject.mydataset.my_mv`
.
Como excluir uma visualização materializada apenas se ela existir
O exemplo a seguir exclui uma visualização materializada chamada my_mv
no mydataset
somente se a visualização materializada existir. Se o nome dela não existir no conjunto de dados, nenhum erro será retornado e nenhuma ação será realizada.
DROP MATERIALIZED VIEW IF EXISTS mydataset.my_mv
Se você estiver excluindo uma visualização materializada em outro projeto, especifique o projeto, o conjunto de dados e a visualização materializada no seguinte formato: `project_id.dataset.materialized_view`,
(incluindo crases, se project_id
tiver caracteres especiais); por exemplo, `myproject.mydataset.my_mv`
.
Instrução DROP FUNCTION
DROP FUNCTION [IF EXISTS] [[project_name.]dataset_name.]function_name
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando a função especificada
não existe. Se ausente, a consulta falhará quando a função especificada não existir.
project_name
é o nome do projeto que contém a função a ser excluída.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém a função a ser excluída.
O padrão na solicitação é defaultDataset
.
function_name
é o nome da função que você está excluindo.
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;
Instrução DROP PROCEDURE
DROP PROCEDURE [IF EXISTS] [[project_name.]dataset_name.]procedure_name
Em que:
IF EXISTS
: se presente, a consulta é bem-sucedida quando o procedimento especificado
não existe. Se ausente, a consulta falhará quando o procedimento especificado não existir.
project_name
é o nome do projeto que contém o procedimento a ser excluído.
O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto
tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`
(exemplo: `google.com:my_project`
).
dataset_name
é o nome do conjunto de dados que contém o procedimento a ser excluído.
O padrão na solicitação é defaultDataset
.
procedure_name
é o nome do procedimento que você está excluindo.
Exemplos
A instrução de exemplo a seguir exclui o procedimento myprocedure
contido no conjunto de dados mydataset
.
DROP PROCEDURE mydataset.myProcedure;
A instrução de exemplo a seguir exclui o procedimento myProcedure
do conjunto de dados sample_dataset
no projeto other_project
.
DROP PROCEDURE `other-project`.sample_dataset.myprocedure;