Especifique valores de colunas predefinidos

Esta página descreve como definir um valor predefinido para uma coluna numa tabela do BigQuery. Quando adiciona uma linha a uma tabela que não contém dados para uma coluna com um valor predefinido, o valor predefinido é escrito na coluna.

Expressão de valor predefinido

A expressão de valor predefinido de uma coluna tem de ser um literal ou uma das seguintes funções:

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

Pode compor um valor predefinido STRUCT ou ARRAY com estas funções, como [CURRENT_DATE(), DATE '2020-01-01'].

As funções são avaliadas imediatamente antes de os dados serem escritos na tabela durante o processamento do trabalho. O tipo do valor predefinido tem de corresponder ou ser coagido ao tipo da coluna à qual se aplica. Se não for definido nenhum valor predefinido, o valor predefinido é NULL.

Predefina valores

Pode definir o valor predefinido das colunas quando cria uma nova tabela. Use a CREATE TABLE declaração DDL e adicione a palavra-chave DEFAULT e a expressão de valor predefinido após o nome da coluna e o tipo. O exemplo seguinte cria uma tabela denominada simple_table com duas colunas STRING, a e b. A coluna b tem o valor predefinido 'hello'.

CREATE TABLE mydataset.simple_table (
  a STRING,
  b STRING DEFAULT 'hello');

Quando insere dados em simple_table que omitem a coluna b, é usado o valor predefinido 'hello'. Por exemplo:

INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');

A tabela simple_table contém os seguintes valores:

+------+-------+
| a    | b     |
+------+-------+
| val1 | hello |
| val2 | hello |
+------+-------+

Se uma coluna tiver o tipo STRUCT, tem de definir o valor predefinido para todo o campo STRUCT. Não pode definir o valor predefinido para um subconjunto dos campos. O valor predefinido de uma matriz não pode ser NULL nem conter elementos NULL. O exemplo seguinte cria uma tabela denominada complex_table e define um valor predefinido para a coluna struct_col, que contém campos aninhados, incluindo um tipo ARRAY:

CREATE TABLE mydataset.complex_table (
  struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>
    DEFAULT ((CURRENT_TIMESTAMP(), NULL),
             [DATE '2022-01-01', CURRENT_DATE()])
);

Não pode definir valores predefinidos que violem uma restrição na coluna, como um valor predefinido que não esteja em conformidade com um tipo parametrizado ou um valor predefinido NULL quando o modo da coluna é REQUIRED.

Altere os valores predefinidos

Para alterar o valor predefinido de uma coluna, selecione uma das seguintes opções:

A definição do valor predefinido de uma coluna só afeta as inserções futuras na tabela. Não altera os dados de tabelas existentes. O exemplo seguinte define o valor predefinido da coluna a como SESSION_USER();

ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();

Se inserir uma linha em simple_table que omite a coluna a, é usado o utilizador da sessão atual.

INSERT mydataset.simple_table (b) VALUES ('goodbye');

A tabela simple_table contém os seguintes valores:

+------------------+---------+
| a                | b       |
+------------------+---------+
| val1             | hello   |
| val2             | hello   |
| [email protected] | goodbye |
+------------------+---------+

Remova valores predefinidos

Para remover o valor predefinido de uma coluna, selecione uma das seguintes opções:

Use declarações DML com valores predefinidos

Pode adicionar linhas com valores predefinidos a uma tabela através da INSERTdeclaração DML. O valor predefinido é usado quando o valor de uma coluna não é especificado ou quando a palavra-chave DEFAULT é usada em vez da expressão de valor. O exemplo seguinte cria uma tabela e insere uma linha em que todos os valores são os valores predefinidos:

CREATE TABLE mydataset.mytable (
  x TIME DEFAULT CURRENT_TIME(),
  y INT64 DEFAULT 5,
  z BOOL);

INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);

A tabela mytable tem o seguinte aspeto:

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
+-----------------+---+------+

A coluna z não tem um valor predefinido, pelo que NULL é usado como predefinição. Quando o valor predefinido é uma função, como CURRENT_TIME(), é avaliado no momento em que o valor é escrito. Chamar INSERT com o valor predefinido para a coluna x novamente resulta num valor diferente para TIME. No exemplo seguinte, apenas a coluna z tem um valor definido explicitamente, e as colunas omitidas usam os respetivos valores predefinidos:

INSERT mydataset.mytable (z) VALUES (TRUE);

A tabela mytable tem o seguinte aspeto:

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
| 22:18:29.890547 | 5 | true |
+-----------------+---+------+

Pode atualizar uma tabela com valores predefinidos através da MERGE declaração DML. O exemplo seguinte cria duas tabelas e atualiza uma delas com uma declaração MERGE:

CREATE TABLE mydataset.target_table (
  a STRING,
  b STRING DEFAULT 'default_b',
  c STRING DEFAULT SESSION_USER())
AS (
  SELECT
    'val1' AS a, 'hi' AS b, '[email protected]' AS c
  UNION ALL
  SELECT
    'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c
);

CREATE TABLE mydataset.source_table (
  a STRING DEFAULT 'default_val',
  b STRING DEFAULT 'Happy day!')
AS (
  SELECT
    'val1' AS a, 'Good evening！' AS b
  UNION ALL
  SELECT
    'val3' AS a, 'Good morning!' AS b
);

MERGE mydataset.target_table T
USING mydataset.source_table S
ON T.a = S.a
WHEN NOT MATCHED THEN
  INSERT(a, b) VALUES (a, DEFAULT);

O resultado é o seguinte:

+------+-----------+--------------------+
| a    | b         | c                  |
+------+-----------+--------------------+
| val1 | hi        | [email protected]     |
| val2 | goodbye   | [email protected] |
| val3 | default_b | [email protected] |
+------+-----------+--------------------+

Pode atualizar uma tabela com valores predefinidos através da UPDATE declaração DML. O exemplo seguinte atualiza a tabela source_table para que cada linha da coluna b seja igual ao respetivo valor predefinido:

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

O resultado é o seguinte:

+------+------------+
| a    | b          |
+------+------------+
| val1 | Happy day! |
| val3 | Happy day! |
+------+------------+

Anexe uma tabela

Pode usar o comando bq query com a flag --append_table para anexar os resultados de uma consulta a uma tabela de destino com valores predefinidos. Se a consulta omitir uma coluna com um valor predefinido, o valor predefinido é atribuído. O exemplo seguinte anexa dados que especificam valores apenas para a coluna z:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'

A tabela mytable usa valores predefinidos para as colunas x e y:

+-----------------+---+-------+
|        x        | y |   z   |
+-----------------+---+-------+
| 22:13:24.799555 | 5 |  NULL |
| 22:18:29.890547 | 5 |  true |
| 23:05:18.841683 | 5 | false |
| 23:05:18.841683 | 5 | false |
+-----------------+---+-------+

Carregue dados

Pode carregar dados para uma tabela com valores predefinidos através do comando bq load ou da declaração LOAD DATA. Os valores predefinidos são aplicados quando os dados carregados têm menos colunas do que a tabela de destino. Os valores NULL nos dados carregados não são convertidos em valores predefinidos.

Os formatos binários, como AVRO, Parquet ou ORC, têm esquemas de ficheiros codificados. Quando o esquema do ficheiro omite algumas colunas, são aplicados valores predefinidos.

Os formatos de texto, como JSON e CSV, não têm um esquema de ficheiro codificado. Para especificar o esquema dos campos com a ferramenta de linhas de comando bq, pode usar a flag --autodetect ou fornecer um esquema JSON. Para especificar o esquema através da declaração LOAD DATA, tem de fornecer uma lista de colunas. Segue-se um exemplo que carrega apenas a coluna a de um ficheiro CSV:

LOAD DATA INTO mydataset.insert_table (a)
FROM FILES(
  uris = ['gs://test-bucket/sample.csv'],
  format = 'CSV');

API Write

A API Storage Write só preenche os valores predefinidos quando o esquema de fluxo de escrita não tem um campo que esteja contido no esquema da tabela de destino. Neste caso, o campo em falta é preenchido com o valor predefinido na coluna para cada gravação. Se o campo existir no esquema da stream de gravação, mas estiver em falta nos próprios dados, o campo em falta é preenchido com NULL. Por exemplo, suponhamos que está a escrever dados numa tabela do BigQuery com o seguinte esquema:

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

O seguinte esquema de fluxo de escrita não tem o campo c que está presente na tabela de destino:

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
]

Suponhamos que transmite os seguintes valores para a tabela:

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

O resultado é o seguinte:

+-------+-------+-----------+
| a     | b     | c         |
+-------+-------+-----------+
| val_a | val_b | default_c |
| val_a | NULL  | default_c |
+-------+-------+-----------+

O esquema de fluxo de gravação contém o campo b, pelo que o valor predefinido default_b não é usado mesmo quando não é especificado nenhum valor para o campo. Uma vez que o esquema do fluxo de gravação não contém o campo c, todas as linhas na coluna c são preenchidas com o valor predefinido da tabela de destino default_c.

O seguinte esquema de fluxo de escrita corresponde ao esquema da tabela na qual está a escrever:

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
  {
    "name": "c",
    "type": "STRING",
  }
]

Suponhamos que transmite os seguintes valores para a tabela:

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

O esquema de fluxo de gravação não tem campos em falta contidos na tabela de destino, pelo que não são aplicados valores predefinidos das colunas, independentemente de os campos estarem preenchidos nos dados transmitidos:

+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
| val_a | val_b | NULL |
| val_a | NULL  | NULL |
+-------+-------+------+

Pode especificar definições de valores predefinidos ao nível da associação em default_missing_value_interpretation na mensagem AppendRowsRequest. Se o valor estiver definido como DEFAULT_VALUE, o valor em falta vai usar o valor predefinido, mesmo quando a coluna é apresentada no esquema do utilizador.

Também pode especificar valores predefinidos ao nível do pedido no mapa missing_value_interpretations na mensagem AppendRowsRequest. Cada chave é o nome de uma coluna e o respetivo valor indica como interpretar os valores em falta.

Por exemplo, o mapa {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} significa que todos os valores em falta em col1 são interpretados como NULL e todos os valores em falta em col2 são interpretados como o valor predefinido definido para col2 no esquema da tabela.

Se um campo não estiver neste mapa e tiver valores em falta, os valores em falta são interpretados como NULL.

As chaves só podem ser nomes de colunas de nível superior. As chaves não podem ser subcampos de estrutura, como col1.subfield1.

Use o método da API insertAll

O método API tabledata.insertAll preenche os valores predefinidos ao nível da linha quando os dados são escritos numa tabela. Se uma linha não tiver colunas com valores predefinidos, os valores predefinidos são aplicados a essas colunas.

Por exemplo, suponha que tem o seguinte esquema de tabela:

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

Suponhamos que transmite os seguintes valores para a tabela:

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
{}

O resultado é o seguinte:

+-------+------------+-----------+
| a     | b          | c         |
+-------+------------+-----------+
| val_a | val_b      | default_c |
| val_a | default_b  | default_c |
| NULL  | default_b  | default_c |
+-------+------------+-----------+

A primeira linha inserida não contém um valor para o campo c, pelo que o valor predefinido default_c é escrito na coluna c. A segunda linha inserida não contém valores para os campos b nem c, pelo que os respetivos valores predefinidos são escritos nas colunas b e c. A terceira linha inserida não contém valores. O valor escrito na coluna a é NULL, uma vez que não está definido nenhum outro valor predefinido. Os valores predefinidos default_b e default_c são escritos nas colunas b e c.

Veja os valores predefinidos

Para ver o valor predefinido de uma coluna, consulte a vista INFORMATION_SCHEMA.COLUMNS. O campo da coluna column_default contém o valor predefinido para a coluna. Se não for definido nenhum valor predefinido, é NULL. O exemplo seguinte mostra os nomes das colunas e os valores predefinidos para a tabela mytable:

SELECT
  column_name,
  column_default
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

O resultado é semelhante ao seguinte:

+-------------+----------------+
| column_name | column_default |
+-------------+----------------+
| x           | CURRENT_TIME() |
| y           | 5              |
| z           | NULL           |
+-------------+----------------+

Limitações

• Pode ler a partir de tabelas com valores predefinidos através do SQL antigo, mas não pode escrever em tabelas com valores predefinidos através do SQL antigo.
• Não pode adicionar uma nova coluna com um valor predefinido a uma tabela existente. No entanto, pode adicionar a coluna sem um valor predefinido e, em seguida, alterar o respetivo valor predefinido através da declaração DDL ALTER COLUMN SET DEFAULT.
• Não pode copiar e acrescentar uma tabela de origem a uma tabela de destino que tenha mais colunas do que a tabela de origem e as colunas adicionais tenham valores predefinidos. Em alternativa, pode executar o comando INSERT destination_table SELECT * FROM source_table para copiar os dados.

O que se segue?

• Para mais informações sobre o carregamento de dados no BigQuery, consulte o artigo Introdução ao carregamento de dados.