Especificar valores de coluna padrão

Nesta página, descrevemos como definir um valor padrão para uma coluna em uma tabela do BigQuery. Quando você adiciona uma linha a uma tabela que não contenha dados de uma coluna com um valor padrão, esse valor é gravado na coluna.

Expressão de valor padrão

A expressão de valor padrão de uma coluna precisa ser literal ou uma das funções abaixo:

É possível compor um valor padrão de STRUCT ou ARRAY com essas funções, como [CURRENT_DATE(), DATE '2020-01-01'].

As funções são avaliadas quando os dados são gravados na tabela. O tipo de valor padrão precisa corresponder ou converter o tipo de coluna a que ele se aplica. Se nenhum valor padrão for definido, o valor padrão será NULL.

Definir valores padrão

É possível definir o valor padrão para colunas ao criar uma nova tabela. Use a instrução DDL CREATE TABLE e adicione a palavra-chave DEFAULT e a expressão de valor padrão após o nome e o tipo da coluna. O exemplo a seguir cria uma tabela chamada simple_table com duas colunas STRING, a a e a b. A coluna b tem o valor padrão 'hello'.

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

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

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

A tabela simple_table contém os valores a seguir:

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

Se uma coluna tiver o tipo STRUCT, defina o valor padrão para todo o campo STRUCT. Não é possível definir o valor padrão para um subconjunto dos campos. O valor padrão de uma matriz não pode ser NULL ou conter elementos NULL. O exemplo a seguir cria uma tabela chamada complex_table e define um valor padrão para a coluna struct_col, que contém campos aninhados, incluindo um tipo de 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 é possível definir valores padrão que violem uma restrição na coluna, como um valor padrão que não esteja em conformidade com um tipo parametrizado ou um valor NULL padrão quando o modo da coluna é REQUIRED.

Mudar os valores padrão

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

Console

No Console do Google Cloud, acesse a página BigQuery.

Ir para o BigQuery
No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.
No painel de detalhes, clique na guia Visualizar.
Clique em Editar esquema. Talvez seja necessário rolar para conferir esse botão.
Na página Esquema atual, localize o campo de nível superior que você quer mudar.
Insira o valor padrão para esse campo.
Clique em Salvar.

SQL

Use a instrução DDL ALTER COLUMN SET DEFAULT.

No Console do Google Cloud, acesse a página BigQuery.

Ir para o BigQuery

No editor de consultas, digite a seguinte instrução:

ALTER TABLE mydataset.mytable
ALTER COLUMN column_name SET DEFAULT default_expression;

Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

Definir o valor padrão para uma coluna afeta somente as inserções futuras na tabela. Isso não altera os dados das tabelas. O exemplo a seguir define o valor padrão da coluna a como SESSION_USER().

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

Se você inserir uma linha em simple_table que omita a coluna a, o usuário da sessão atual será usado.

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

A tabela simple_table contém os valores a seguir:

+------------------+---------+
| a                | b       |
+------------------+---------+
| val1             | hello   |
| val2             | hello   |
| user@example.com | goodbye |
+------------------+---------+

Remover valores padrão

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

Console

No Console do Google Cloud, acesse a página BigQuery.

Ir para o BigQuery
No painel Explorer, expanda o projeto e o conjunto de dados e selecione a tabela.
No painel de detalhes, clique na guia Visualizar.
Clique em Editar esquema. Talvez seja necessário rolar para conferir esse botão.
Na página Esquema atual, localize o campo de nível superior que você quer mudar.
Insira NULL como o valor padrão.
Clique em Salvar.

SQL

Use a instrução DDL ALTER COLUMN DROP DEFAULT.

No Console do Google Cloud, acesse a página BigQuery.

Ir para o BigQuery
No editor de consultas, digite a seguinte instrução:
```
ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;
```
Também é possível remover o valor padrão de uma coluna alterando o valor dela para NULL com a instrução DDL ALTER COLUMN SET DEFAULT.
Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

Usar instruções DML com valores padrão

É possível adicionar linhas com valores padrão a uma tabela usando a instrução DML INSERT. O valor padrão é usado quando o valor de uma coluna não é especificado ou quando a palavra-chave DEFAULT é usada no lugar da expressão de valor. O exemplo a seguir cria uma tabela e insere uma linha em que cada valor é o valor padrão:

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 esta aparência:

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

Como a coluna z não tem um valor padrão, NULL é usado como padrão. Quando o valor padrão é uma função, como CURRENT_TIME(), ele é avaliado no momento em que o valor é gravado. Chamar INSERT com o valor padrão para a coluna x novamente resulta em um valor diferente para TIME. No exemplo abaixo, apenas a coluna z tem um valor definido explicitamente, e as colunas omitidas usam os valores padrão:

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

A tabela mytable tem esta aparência:

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

É possível atualizar uma tabela com valores padrão usando a instrução DML MERGE. O exemplo a seguir cria duas tabelas e atualiza uma delas com uma instruçã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, '123@google.com' 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        | 123@google.com     |
| val2 | goodbye   | default@google.com |
| val3 | default_b | default@google.com |
+------+-----------+--------------------+

É possível atualizar uma tabela com valores padrão usando a instrução DML UPDATE. O exemplo a seguir atualiza a tabela source_table para que cada linha da coluna b seja igual ao valor padrão:

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

O resultado é o seguinte:

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

Anexar uma tabela

É possível usar o comando bq query com a sinalização --append_table para anexar os resultados de uma consulta a uma tabela de destino que tenha valores padrão. Se a consulta omitir uma coluna com um valor padrão, esse valor será atribuído. O exemplo a seguir 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 padrão 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 |
+-----------------+---+-------+

Carregar dados

É possível carregar dados em uma tabela com valores padrão usando o comando bq load ou o LOAD DATA completa. Os valores padrão são aplicados quando os dados carregados têm menos colunas que a tabela de destino. Os valores NULL nos dados carregados não são convertidos em valores padrão.

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

Formatos de texto, como JSON e CSV, não têm um esquema de arquivo codificado. Para especificar o esquema usando a ferramenta de linha de comando bq, , use a sinalização --autodetect ou forneça um esquema JSON. Para especificar o esquema usando a instrução LOAD DATA, forneça uma lista de colunas. Confira a seguir um exemplo que carrega apenas a coluna a de um arquivo 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 padrão quando o esquema de stream de gravação não tem um campo contido no esquema da tabela de destino. Nesse caso, o campo ausente é preenchido com o valor padrão na coluna para cada gravação. Se o campo existir no esquema do stream de gravação, mas estiver ausente dos dados, o campo ausente será preenchido com NULL. Por exemplo, suponha que você esteja gravando dados em uma 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 esquema de stream de gravação a seguir não tem o campo c que está presente na tabela de destino:

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

Suponha que você faça streaming dos valores a seguir 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 do stream de gravação contém o campo b. Portanto, o valor padrão default_b não é usado, mesmo quando nenhum valor é especificado para o campo. Como o esquema de stream de gravação não contém o campo c, cada linha na coluna c é preenchida com o valor padrão da tabela de destino default_c.

O esquema de stream de gravação a seguir corresponde ao esquema da tabela em que você está gravando:

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

Suponha que você faça streaming dos valores a seguir para a tabela:

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

O esquema de stream de gravação não tem nenhum campo contido na tabela de destino. Portanto, nenhum dos valores padrão das colunas é aplicado, independente de os campos estarem preenchidos nos dados transmitidos:

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

É possível especificar as configurações de valores padrão no nível da conexão em default_missing_value_interpretation, na mensagem AppendRowsRequest. Se o valor estiver definido como DEFAULT_VALUE, o valor ausente usará o padrão mesmo quando a coluna estiver presente no esquema do usuário.

Também é possível especificar valores padrão no nível da solicitação no mapa missing_value_interpretations na mensagem AppendRowsRequest. Cada chave é o nome de uma coluna, e o valor indica como interpretar valores ausentes.

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

Se um campo não estiver nesse mapa e tiver valores ausentes, os valores ausentes serã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 da API tabledata.insertAll preenche valores padrão no nível da linha quando os dados são gravados em uma tabela. Se uma linha não tiver colunas com valores padrão, eles serão aplicados.

Por exemplo, suponha que você tenha o esquema de tabela a seguir:

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

Suponha que você faça streaming dos valores a seguir 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. Portanto, o valor padrão default_c é gravado na coluna c. A segunda linha inserida não contém valores para os campos b ou c, então os valores padrão são gravados nas colunas b e c. A terceira linha inserida não contém valores. O valor gravado na coluna a é NULL, já que nenhum outro valor padrão é definido. Os valores padrão default_b e default_c são gravados nas colunas b e c.

Acessar valores padrão

Para acessar o valor padrão de uma coluna, consulte a visualização INFORMATION_SCHEMA.COLUMNS. O campo da coluna column_default contém o valor padrão da coluna. Se nenhum valor padrão for definido, ele será NULL. O exemplo a seguir mostra os nomes das colunas e os valores padrão da tabela mytable:

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

O resultado será semelhante ao seguinte:

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

Limitações

É possível ler tabelas com valores padrão usando o SQL legado, mas não é possível gravar em tabelas com valores padrão usando o SQL legado.
Não é possível adicionar uma nova coluna com um valor padrão a uma tabela. Mas é possível adicionar a coluna sem um valor padrão e, em seguida, alterar o valor padrão usando a instrução DDL ALTER COLUMN SET DEFAULT.
Não é possível copiar e anexar uma tabela de origem a uma tabela de destino com mais colunas do que a tabela de origem, e as colunas adicionais têm valores padrão. Mas é possível executar INSERT destination_table SELECT * FROM source_table para copiar os dados.

A seguir

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

Especificar valores de coluna padrão

Expressão de valor padrão

Definir valores padrão

Mudar os valores padrão

Console

SQL

Remover valores padrão

Console

SQL

Usar instruções DML com valores padrão

Anexar uma tabela

Carregar dados

API Write

Use o método da API insertAll

Acessar valores padrão

Limitações

A seguir

Use o método da API `insertAll`