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:
- Especifique manualmente:
- Usando o Console do Google Cloud.
- Inline usando a ferramenta de linha de comando
bq
. - Como usar a instrução SQL
CREATE TABLE
.
- 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 precisa conter apenas letras (a-z, A-Z), números (0-9) ou sublinhados (_) e começar com uma letra ou sublinhado. O comprimento máximo é de 300 caracteres. Não é possível usar estes prefixos nos nomes da coluna:
_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
.
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
parametrizado, é 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 o comportamento de arredondamento para esses tipos, consulte
Tipos decimais parametrizados.
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 os esquemas manualmente
Ao carregar dados ou criar uma tabela vazia, é possível especificar manualmente o esquema da tabela usando o Console do Cloud ou a ferramenta de linha de comando bq
. A especificação manual 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 manualmente 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 manualmente 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 manualmente e 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 manualmente e 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
parametrizado
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 manualmente 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 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 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.