Como especificar um esquema
O BigQuery permite especificar o esquema de uma tabela ao carregar dados nela e ao criar uma tabela vazia. Também é possível usar a detecção automática de esquema em formatos de dados compatíveis.
Ao carregar arquivos de exportação Avro, Parquet, ORC, Firestore ou Datastore, o esquema é recuperado automaticamente dos dados de origem autodescritivos.
É possível especificar o esquema de uma tabela das seguintes maneiras:
- Use o console do Google Cloud.
- Use a instrução SQL
CREATE TABLE
. - Inline usando a ferramenta de linha de comando
bq
. - Crie um arquivo de esquema no formato JSON.
- Chame o método
jobs.insert
e configure a propriedadeschema
na configuração de jobload
. - Chame o método
tables.insert
e configure o esquema no recurso de tabela usando a propriedadeschema
.
Depois de carregar os dados ou criar uma tabela vazia, é possível modificar a definição do esquema da tabela.
Componentes do esquema
Quando você especifica um esquema de tabela, é necessário fornecer o nome e o tipo de dados de cada coluna. Também é possível fornecer a descrição, o modo e o valor padrão de uma coluna.
Nomes de coluna
O nome da coluna pode conter letras (a-z, A-Z), números (0-9) ou sublinhados (_) e precisa começar com uma letra ou sublinhado. Para ter compatibilidade com nomes de colunas mais flexíveis, consulte nomes de colunas flexíveis.
Os nomes das colunas podem ter no máximo 300 caracteres. Os nomes de colunas não podem usar nenhum dos seguintes prefixos:
_TABLE_
_FILE_
_PARTITION
_ROW_TIMESTAMP
__ROOT__
_COLIDENTIFIER
Não é permitido haver nomes de coluna duplicados, mesmo com diferença de maiúsculas e minúsculas. Por exemplo, uma coluna chamada Column1
é considerada idêntica a uma coluna chamada column1
. Para saber mais sobre regras de nomenclatura de coluna, consulte Nomes de coluna na referência do GoogleSQL.
Nomes de colunas flexíveis
Agora você tem mais flexibilidade para nomear as colunas, incluindo mais acesso a caracteres em outros idiomas além de inglês, bem como outros símbolos.
Os nomes de colunas flexíveis são compatíveis com os seguintes caracteres:
- Qualquer letra em qualquer idioma, conforme representado pela expressão regular Unicode
\p{L}
. - Qualquer caractere numérico em qualquer idioma, conforme representado pela expressão regular Unicode
\p{N}
. - Qualquer caractere de pontuação do conector, incluindo sublinhados, conforme representado pela expressão regular Unicode
\p{Pc}
. - Hífen ou traço, conforme representado pela expressão regular Unicode
\p{Pd}
. - Qualquer marca destinada a acompanhar outro caractere, conforme representado pela expressão regular Unicode
\p{M}
. Por exemplo, acentos, trema ou caixas de delimitação. - Estes caracteres especiais:
- Um "e" comercial (
&
), conforme representado pela expressão regular Unicode\u0026
. - Um sinal de porcentagem (
%
), conforme representado pela expressão regular Unicode\u0025
. - Um sinal de igual (
=
), conforme representado pela expressão regular Unicode\u003D
. - Um sinal de adição (
+
), conforme representado pela expressão regular Unicode\u002B
. - Dois-pontos (
:
), conforme representado pela expressão regular Unicode\u003A
. - Um apóstrofo (
'
), conforme representado pela expressão regular Unicode\u0027
. - Um sinal de menor que (
<
), conforme representado pela expressão regular Unicode\u003C
. - Um sinal de maior que (
>
), conforme representado pela expressão regular Unicode\u003E
. - Um sinal numérico (
#
), conforme representado pela expressão regular Unicode\u0023
. - Uma linha vertical (
|
), conforme representado pela expressão regular Unicode\u007c
. - Espaço em branco.
- Um "e" comercial (
Os nomes de colunas flexíveis não são compatíveis com os seguintes caracteres especiais:
- Um ponto de exclamação (
!
), conforme representado pela expressão regular Unicode\u0021
. - Aspas (
"
), conforme representadas pela expressão regular Unicode\u0022
. - Um cifrão (
$
), conforme representado pela expressão regular Unicode\u0024
. - Um parêntese esquerdo (
(
), conforme representado pela expressão regular Unicode\u0028
. - Um parêntese direito (
)
), conforme representado pela expressão regular Unicode\u0029
. - Um asterisco (
*
), conforme representado pela expressão regular Unicode\u002A
. - Uma vírgula (
,
), conforme representado pela expressão regular Unicode\u002C
. - Um ponto (
.
), conforme representado pela expressão regular Unicode\u002E
. - Uma barra (
/
), conforme representada pela expressão regular Unicode\u002F
. - Ponto e vírgula (
;
), conforme representado pela expressão regular Unicode\u003B
. - Um ponto de interrogação (
?
), conforme representado pela expressão regular Unicode\u003F
. - Um sinal de arroba (
@
), conforme representado pela expressão regular Unicode\u0040
. - Um colchete esquerdo (
[
), conforme representado pela expressão regular Unicode\u005B
. - Uma barra invertida (
\
), conforme representado pela expressão regular Unicode\u005C
. - Um colchete direito (
]
), conforme representado pela expressão regular Unicode\u005D
. - Um acento circunflexo (
^
), conforme representado pela expressão regular Unicode\u005E
. - Um acento grave (
`
), conforme representado pela expressão regular Unicode\u0060
. - Uma chave à esquerda (
{
), conforme representado pela expressão regular Unicode\u007B
. - Uma chave à direita (
}
), conforme representado pela expressão regular Unicode\u007D
. - Um til (
~
) conforme representado pela expressão regular Unicode\u007E
.
Para acessar outras diretrizes, consulte Nomes de colunas.
Os caracteres da coluna expandida são compatíveis com a API BigQuery Storage Read
e a API BigQuery Storage Write. Para usar a lista expandida de caracteres Unicode
com a API BigQuery Storage Read, é necessário definir uma flag. É possível usar o
atributo displayName
para recuperar o nome da coluna. O exemplo a seguir mostra
como definir uma flag com o cliente Python:
from google.cloud.bigquery_storage import types
requested_session = types.ReadSession()
#set avro serialization options for flexible column.
options = types.AvroSerializationOptions()
options.enable_display_name_attribute = True
requested_session.read_options.avro_serialization_options = options
Para usar a lista expandida de caracteres Unicode com a API BigQuery Storage Write,
forneça o esquema com a notação column_name
, a menos que você esteja usando
o objeto gravador JsonStreamWriter
. O exemplo a seguir mostra como fornecer
o esquema:
syntax = "proto2";
package mypackage;
// Source protos located in github.com/googleapis/googleapis
import "google/cloud/bigquery/storage/v1/annotations.proto";
message FlexibleSchema {
optional string item_name_column = 1
[(.google.cloud.bigquery.storage.v1.column_name) = "name-列"];
optional string item_description_column = 2
[(.google.cloud.bigquery.storage.v1.column_name) = "description-列"];
}
Neste exemplo, item_name_column
e item_description_column
são
nomes de marcadores que precisam estar em conformidade
com a convenção de nomenclatura do
buffer de protocolo. Observe que as anotações column_name
sempre têm precedência sobre
nomes de marcadores.
- Por padrão, o carregamento de dados do Parquet não é compatível com nomes de colunas flexíveis. Para se inscrever nesse pré-lançamento, preencha o formulário de inscrição.
- Por padrão, o carregamento de dados CSV usando detecção automática de esquema não é compatível com nomes de colunas flexíveis. Para se inscrever nesse pré-lançamento, preencha o formulário de inscrição.
Os seguintes nomes de coluna Unicode não são suportados pelos seguintes recursos do BigQuery:
Descrições de coluna
Cada coluna pode incluir uma descrição opcional. Ela é uma string com comprimento máximo de 1.024 caracteres.
Valores padrão
O valor padrão de uma coluna precisa ser literal ou uma das funções abaixo:
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIME
CURRENT_TIMESTAMP
GENERATE_UUID
RAND
SESSION_USER
ST_GEOGPOINT
Tipos de dados do GoogleSQL
O GoogleSQL permite especificar os seguintes tipos de dados no esquema: O tipo de dados é obrigatório.
Nome | Tipo de dado | Descrição |
---|---|---|
Número inteiro | INT64 |
Valores numéricos sem componentes fracionários. |
Ponto flutuante | FLOAT64 |
Valores numéricos aproximados com componentes fracionários. |
Numérico | NUMERIC |
Valores numéricos exatos com componentes fracionários |
BigNumeric | BIGNUMERIC |
Valores numéricos exatos com componentes fracionários |
Booleano | BOOL |
VERDADEIRO ou FALSO, não diferencia maiúsculas de minúsculas. |
String | STRING |
Dados em caracteres de comprimento variável (Unicode). |
Bytes | BYTES |
Dados binários de comprimento variável. |
Data | DATE |
Uma data de calendário lógico |
Data/hora | DATETIME |
Ano, mês, dia, hora, minuto, segundo e subsegundo |
Hora | TIME |
Um horário, sem depender de uma data específica. |
Carimbo de data/hora | TIMESTAMP |
Ponto absoluto no tempo, com precisão de microssegundo. |
Struct (Record) | STRUCT |
Contêiner de campos ordenados, cada um com um tipo (obrigatório) e um nome de campo (opcional). |
Geografia | GEOGRAPHY |
Um conjunto de pontos na superfície da Terra formado por pontos, linhas e polígonos com bordas geodésicas no esferóide de referência WGS84. |
JSON | JSON |
Representa JSON, um formato leve de troca de dados. |
Para mais informações sobre os tipos de dados no GoogleSQL, consulte Tipos de dados do GoogleSQL.
Também é possível declarar um tipo de matriz ao consultar dados. Para saber mais, consulte Como trabalhar com matrizes.
Modos
O BigQuery é compatível com os modos para colunas a seguir. O modo é opcional. Se ele não for especificado, o padrão da coluna é NULLABLE
.
Modo | Descrição |
---|---|
Anulável | A coluna permite valores NULL (padrão). |
Obrigatório | Os valores NULL não são permitidos. |
Repetida | A coluna contém uma matriz de valores do tipo especificado. |
Para mais informações sobre modos, consulte mode
no TableFieldSchema
.
Modo de arredondamento
Quando uma coluna é um tipo NUMERIC
ou BIGNUMERIC
, é possível definir a
opção de coluna rounding_mode
,
que determina como os valores nessa coluna são arredondados. ao ser gravado na
tabela. É possível definir a opção rounding_mode
em uma coluna de nível superior ou um campo
STRUCT
. Os seguintes modos de arredondamento são compatíveis:
"ROUND_HALF_AWAY_FROM_ZERO"
: esse modo (padrão) é arredondado para a metade de zero."ROUND_HALF_EVEN"
: esse modo arredonda os casos na metade para o dígito par mais próximo.
Não é possível definir a opção rounding_mode
para uma coluna que não seja um tipo NUMERIC
ou
BIGNUMERIC
. Para saber mais sobre esses tipos, consulte os tipos decimais.
O exemplo a seguir cria uma tabela e insere valores arredondados com base no modo de arredondamento da coluna:
CREATE TABLE mydataset.mytable ( x NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_EVEN'), y NUMERIC(5,2) OPTIONS (rounding_mode='ROUND_HALF_AWAY_FROM_ZERO') ); INSERT mydataset.mytable (x, y) VALUES (NUMERIC "1.025", NUMERIC "1.025"), (NUMERIC "1.0251", NUMERIC "1.0251"), (NUMERIC "1.035", NUMERIC "1.035"), (NUMERIC "-1.025", NUMERIC "-1.025");
A tabela mytable
tem esta aparência:
+-------+-------+ | x | y | +-------+-------+ | 1.02 | 1.03 | | 1.03 | 1.03 | | 1.04 | 1.04 | | -1.02 | -1.03 | +-------+-------+
Para ver mais informações, consulte roundingMode
em
TableFieldSchema
.
Como especificar esquemas
Ao carregar dados ou criar uma tabela vazia, é possível especificar o esquema da tabela usando o console do Cloud ou a ferramenta de linha de comando bq
. A especificação de esquema é compatível quando você carrega arquivos CSV e JSON (delimitado por nova linha). Ao carregar dados de exportação Avro, Parquet, ORC, Firestore ou Datastore, o esquema é recuperado automaticamente dos dados de origem autodescritivos.
Para especificar um esquema de tabela:
Console
No Console do Google Cloud, é possível especificar um esquema usando a opção Adicionar campo ou Editar como texto.
No Console do Cloud, abra a página do BigQuery.
No painel Explorer, expanda o projeto e selecione um conjunto de dados.
Expanda a opção
Ações e clique em Abrir.No painel de detalhes, clique em Criar tabela
.Na página Criar tabela, localizada na seção Origem, selecione Tabela vazia.
Siga estas etapas na página Criar tabela, localizada na seção Destino:
Em Nome do conjunto de dados, escolha o conjunto de dados apropriado.
No campo Nome da tabela, insira o nome da tabela que você está criando.
Verifique se o Tipo de tabela está definido como Tabela nativa.
Na seção Esquema, insira a definição do esquema.
- Opção 1: use Adicionar campo e especifique o nome, o tipo e o modo de cada campo.
- Opção 2: clique em Editar como texto e cole o esquema na forma de uma matriz JSON. Com ela, você gera o esquema usando um processo igual ao de criação de um arquivo de esquema JSON.
Selecione Criar tabela.
SQL
Use a
instrução CREATE TABLE
.
Especifique o esquema usando a
opção
coluna.
O exemplo a seguir cria uma nova tabela chamada newtable
com as colunas
x, y, z de tipos de número inteiro, string e booleano, respectivamente.
No Console do Google Cloud, acesse a página BigQuery.
No editor de consultas, digite a seguinte instrução:
CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRING, z BOOL) OPTIONS( description = 'My example table');
Clique em
Executar.
Para informações sobre como executar consultas, consulte Como executar consultas interativas.
bq
Forneça o esquema in-line no formato field:data_type,field:data_type
usando um dos comandos a seguir:
- Se estiver carregando dados, use o comando
bq load
. - Se você estiver criando uma tabela vazia, use o comando
bq mk
.
Quando você especifica o esquema na linha de comando, não é possível especificar o modo da coluna ou incluir um tipo RECORD
(STRUCT
) e uma descrição de coluna. Todos os modos assumem NULLABLE
como padrão. Para incluir descrições, modos e tipos RECORD
, forneça um arquivo de esquema JSON.
Para carregar dados em uma tabela usando uma definição de esquema in-line, insira o comando load
e especifique o formato de dados usando a sinalização --source_format
.
Se estiver carregando dados em uma tabela em um projeto diferente do seu projeto padrão, inclua o ID do projeto no seguinte formato: project_id:dataset.table_name
.
Opcional: forneça a sinalização --location
e defina o valor do
local.
bq --location=location load \ --source_format=format \ project_id:dataset.table_name \ path_to_source \ schema
Substitua:
location
: o nome do seu local. A sinalização--location
é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização comoasia-northeast1
. É possível definir um valor padrão para a unidade usando o arquivo .bigqueryrc.format
:NEWLINE_DELIMITED_JSON
ouCSV
.project_id
: o ID do projeto;dataset
: o conjunto de dados que contém a tabela em que você está carregando dados;table_name
: o nome da tabela em que você está carregando dados.path_to_source
: o local do arquivo de dados CSV ou JSON na sua máquina local ou no Cloud Storage;schema
: a definição do esquema in-line.
Exemplo:
Digite o comando abaixo para carregar dados de um arquivo CSV local chamado myfile.csv
em mydataset.mytable
no seu projeto padrão. O esquema é especificado in-line.
bq load \
--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING
Para mais informações sobre como carregar dados no BigQuery, consulte Introdução ao carregamento de dados.
Para especificar uma definição de esquema in-line ao criar uma tabela vazia, insira o comando bq mk
com a sinalização --table
ou -t
. Se estiver criando uma tabela em um projeto diferente do seu projeto padrão, adicione o ID do projeto ao comando no seguinte formato: project_id:dataset.table
.
bq mk --table project_id:dataset.table schema
Substitua:
project_id
: o ID do projeto;dataset
: um conjunto de dados no projeto;table
: o nome da tabela que você está criando;schema
: uma definição de esquema in-line.
O comando a seguir, por exemplo, cria uma tabela vazia chamada mytable
no seu projeto padrão. O esquema é especificado in-line.
bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING
Para mais informações sobre como criar uma tabela vazia, consulte Como criar uma tabela vazia com uma definição de esquema.
C#
Para especificar o esquema de uma tabela ao carregar dados nela:
Para especificar um esquema ao criar uma tabela vazia:
Go
Para especificar o esquema de uma tabela ao carregar dados nela:
Para especificar um esquema ao criar uma tabela vazia:
Java
Para especificar o esquema de uma tabela ao carregar dados nela:
Para especificar um esquema ao criar uma tabela vazia:
Python
Para especificar o esquema de uma tabela ao carregar dados, configure a propriedade LoadJobConfig.schema.
Para especificar um esquema ao criar uma tabela vazia, configure a propriedade Table.schema.
Como especificar um arquivo de esquema JSON
Se preferir, você pode especificar o esquema usando um arquivo de esquema JSON em vez de usar uma definição de esquema in-line. Um arquivo de esquema JSON consiste em uma matriz JSON que contém o seguinte:
- O nome da coluna
- Tipo de dado
- (Opcional) Modo (se não for especificado, o modo padrão é
NULLABLE
) - Opcional: os campos da coluna se for um
tipo
STRUCT
- (Opcional) descrição
- Opcional: as tags de política da coluna, usadas para controle de acesso no nível do campo.
- Opcional: o comprimento máximo da coluna para os valores para os tipos
STRING
ouBYTES
- Opcional: a precisão da coluna
para os tipos
NUMERIC
ouBIGNUMERIC
- Opcional: a escala da coluna para tipos de
NUMERIC
ouBIGNUMERIC
- Opcional: a compilação
da coluna para tipos
STRING
. - (Opcional) Valor padrão da coluna
- Opcional: o modo de arredondamento da coluna, se a coluna for um tipo
NUMERIC
ouBIGNUMERIC
Como criar um arquivo de esquema JSON
Para criar um arquivo de esquema JSON, insira um TableFieldSchema
para cada coluna. Os campos name
e type
são obrigatórios. Todos os outros campos são opcionais.
[ { "name": string, "type": string, "mode": string, "fields": [ { object (TableFieldSchema) } ], "description": string, "policyTags": { "names": [ string ] }, "maxLength": string, "precision": string, "scale": string, "collation": string, "defaultValueExpression": string, "roundingMode": string }, { "name": string, "type": string, ... } ]A matriz JSON é indicada pelos colchetes iniciais e finais `[]`. Cada entrada da coluna precisa ser separada por uma vírgula: `},`. É possível gravar um esquema de tabela atual em um arquivo local inserindo o seguinte comando:
bq show \ --schema \ --format=prettyjson \ project_id:dataset.table > path_to_file
É possível usar o arquivo de saída como ponto de partida do seu próprio arquivo de esquema JSON. Ao usar essa abordagem, verifique se o arquivo contém apenas a matriz JSON que representa o esquema da tabela.
Por exemplo, a matriz JSON a seguir representa um esquema de tabela básico. Este esquema tem três colunas: qtr
(REQUIRED
STRING
), rep
(NULLABLE
STRING
),
e sales
(NULLABLE
FLOAT
).
[ { "name": "qtr", "type": "STRING", "mode": "REQUIRED", "description": "quarter" }, { "name": "rep", "type": "STRING", "mode": "NULLABLE", "description": "sales representative" }, { "name": "sales", "type": "FLOAT", "mode": "NULLABLE", "defaultValueExpression": "2.55" } ]
Como usar um arquivo de esquema JSON
Depois de criar seu arquivo de esquema JSON, é possível especificá-lo usando a ferramenta de linha de comando bq
.
Não é possível usar um arquivo de esquema com o Console do Cloud ou a API.
Forneça o arquivo de esquema:
- Se estiver carregando dados, use o comando
bq load
. - Se você estiver criando uma tabela vazia, use o comando
bq mk
.
Quando você fornece um arquivo de esquema JSON, ele precisa estar armazenado em uma localização legível localmente. Não é possível especificar um que esteja armazenado no Cloud Storage ou no Google Drive.
Como especificar um arquivo de esquema ao carregar dados
Para carregar dados em uma tabela usando uma definição de esquema JSON, faça o seguinte:
bq
bq --location=location load \ --source_format=format \ project_id:dataset.table \ path_to_data_file \ path_to_schema_file
Substitua:
location
: o nome do seu local. A sinalização--location
é opcional. Por exemplo, se estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização comoasia-northeast1
. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;format
:NEWLINE_DELIMITED_JSON
ouCSV
.project_id
: o ID do projeto;dataset
: o conjunto de dados que contém a tabela em que você está carregando dados;table
: o nome da tabela em que você está carregando dados;path_to_data_file
: o local do arquivo de dados CSV ou JSON na sua máquina local ou no Cloud Storage;path_to_schema_file
: o caminho para o arquivo de esquema na máquina local.
Exemplo:
Digite o comando abaixo para carregar dados de um arquivo CSV local chamado myfile.csv
em mydataset.mytable
no seu projeto padrão. O esquema é especificado em myschema.json
no diretório atual.
bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json
Python
Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.
Para carregar um esquema de tabela de um arquivo JSON usando a biblioteca de cliente Python, chame o método schema_from_json.Como especificar um arquivo de esquema ao criar uma tabela
Para criar uma tabela vazia em um conjunto de dados atual usando um arquivo de esquema JSON, faça o seguinte:
bq
bq mk --table project_id:dataset.table path_to_schema_file
Substitua:
project_id
: o ID do projeto;dataset
: um conjunto de dados no projeto;table
: o nome da tabela que você está criando;path_to_schema_file
: o caminho para o arquivo de esquema na máquina local.
O comando a seguir, por exemplo, cria uma tabela chamada mytable
em mydataset
no seu projeto padrão. O esquema é especificado em myschema.json
no diretório atual:
bq mk --table mydataset.mytable ./myschema.json
Python
Antes de testar esta amostra, siga as instruções de configuração do Python no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Python.
Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.
Para carregar um esquema de tabela de um arquivo JSON usando a biblioteca de cliente Python, chame o método schema_from_json.Como especificar um esquema na API
Especificar um esquema de tabela usando a API, siga estas etapas:
Para especificar um esquema ao carregar dados, chame o método
jobs.insert
e configure a propriedadeschema
no recursoJobConfigurationLoad
.Para especificar um esquema ao criar uma tabela, chame o método
tables.insert
e configure a propriedadeschema
no recursoTable
.
O processo de especificação de esquema por meio da API é semelhante ao de criação de um arquivo de esquema JSON.
Segurança de tabelas
Para controlar o acesso a tabelas no BigQuery, consulte Introdução aos controles de acesso a tabelas.
Próximas etapas
- Saiba como especificar colunas aninhadas e repetidas em uma definição de esquema.
- Saiba mais sobre a detecção automática de esquema.
- Saiba mais sobre o carregamento de dados no BigQuery.
- Saiba mais sobre Como criar e usar tabelas.