Crie um arquivo de definição de tabelas em uma fonte de dados externa.

Esta página descreve como criar um arquivo de definição de tabela para uma fonte de dados externa. Uma fonte de dados externa pode ser consultada diretamente, mesmo que os dados não estejam armazenados no BigQuery.

Um arquivo de definição de tabela contém a definição de esquema de uma tabela externa e metadados, como o formato de dados da tabela e propriedades relacionadas. Ao criar um arquivo de definição de tabelas, use a detecção automática de esquema para definir o esquema de uma fonte de dados externa. Você pode fornecer o esquema in-line ou um arquivo JSON contendo a definição do esquema.

Os arquivos de definição de tabela são usados com a ferramenta de linha de comando bq. As propriedades em um arquivo de definição de tabela também se aplicam à criação de um ExternalDataConfiguration quando a API REST é usada. Não use arquivos de definição de tabela ao criar uma tabela externa com o Console do Google Cloud.

É possível criar arquivos de definição de tabela para descrever uma tabela externa permanente ou temporária para as seguintes fontes de dados externas:

  • Cloud Storage

    • Valores separados por vírgula (CSV)
    • JSON delimitado por nova linha
    • Arquivos Avro
    • Arquivos de exportação do Datastore
    • Arquivos ORC
    • Arquivos Parquet
    • Arquivos de exportação do Firestore

  • Google Drive

    • Valores separados por vírgula (CSV)
    • JSON delimitado por nova linha
    • Arquivos Avro
    • Planilhas Google

  • Bigtable

Antes de começar

Para criar um arquivo de definição de tabela, você precisa do URI da fonte de dados:

Criar um arquivo de definição para arquivos CSV, JSON ou do Planilhas Google

Use um dos seguintes métodos para criar um arquivo de definição de tabela para arquivos CSV, JSON ou do Planilhas Google no Cloud Storage ou no Drive:

Use a sinalização autodetect.

Ao especificar um arquivo CSV, JSON ou de planilhas do Google sem incluir uma descrição de esquema in-line ou um arquivo de esquema, é possível usar a sinalização --autodetect para definir a opção "autodetect" como true no arquivo de definição de tabela. Quando a detecção automática está ativada, o BigQuery tenta inferir automaticamente o esquema. Para mais informações, consulte Detecção automática de esquema para fontes de dados externas.

Usar a detecção automática com uma fonte de dados do Cloud Storage

Crie um arquivo de definição de tabela para uma fonte de dados do Cloud Storage:

  1. Use o comando bq mkdef com a sinalização --autodetect para criar um arquivo de definição de tabela. O comando mkdef gera um arquivo de definição de tabela no formato JSON. O exemplo a seguir cria uma definição de tabela e grava a saída em um arquivo: /tmp/file_name.

    bq mkdef \
  --autodetect \
  --source_format=SOURCE_FORMAT \
  "URI" > /tmp/FILE_NAME

    Substitua:

    • SOURCE_FORMAT: o formato do arquivo
    • FILE_NAME: o nome do arquivo de definição de tabela.

    • URI: o URI do Cloud Storage.

      Por exemplo, gs://mybucket/myfile.

  2. Opcional: abra o arquivo de definição de tabela em um editor de texto. Por exemplo, o comando nano /tmp/file_name abre o arquivo em formato nano. O arquivo deve ter a aparência a seguir para uma fonte de dados externa CSV. "autodetect" está definido como true.

    {
"autodetect": true,
"csvOptions": {
  "allowJaggedRows": false,
  "allowQuotedNewlines": false,
  "encoding": "UTF-8",
  "fieldDelimiter": ",",
  "quote": "\"",
  "skipLeadingRows": 0
},
"sourceFormat": "CSV",
"sourceUris": [
  "URI"
]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Não há ajustes de configuração específicos para os arquivos de origem JSON, mas existem configurações aplicáveis aos arquivos do Planilhas Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Usar a detecção automática com uma fonte de dados do Drive

Crie um arquivo de definição de tabela para uma fonte de dados do Drive:

  1. Use o comando bq mkdef com a sinalização --autodetect para criar uma definição de tabela. O comando mkdef gera um arquivo de definição de tabela no formato JSON. O exemplo a seguir cria uma definição de tabela e grava a saída em um arquivo: /tmp/file_name.

    bq mkdef \
   --autodetect \
   --source_format=SOURCE_FORMAT \
   "URI" > /tmp/FILE_NAME

    Substitua:

    • SOURCE_FORMAT: o formato do arquivo
    • FILE_NAME: o nome do arquivo de definição de tabela.

    • URI: o URI do Drive

      Por exemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.

  2. Abra o arquivo de definição de tabela em um editor de texto. Por exemplo, o comando nano /tmp/file_name abre o arquivo em formato nano. O arquivo deve ter a aparência a seguir para uma fonte de dados externa do Planilhas Google. Observe que "autodetect" está definido como true.

    {
"autodetect": true,
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Não há ajustes de configuração específicos para os arquivos de origem JSON, mas existem configurações aplicáveis aos arquivos do Planilhas Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

  4. Para especificar uma determinada página ou um intervalo de células em um arquivo do Planilhas Google, adicione a property range ao arquivo de definição de tabela. Para consultar uma planilha específica, especifique o nome dela. Para consultar um intervalo de células, especifique o intervalo no formato sheet_name!top_left_cell_id:bottom_right_cell_id. Por exemplo: "Sheet1!A1:B20". Se o parâmetro range não estiver especificado, a primeira página do arquivo será usada.

Usar um esquema inline

Se você não quer usar a detecção automática de esquema, é possível criar um arquivo de definição de tabela fornecendo uma definição de esquema in-line. Para fornecer uma definição de esquema in-line, liste os campos e tipos de dados na linha de comando no seguinte formato: FIELD:DATA_TYPE,FIELD:DATA_TYPE.

Usar um esquema inline com uma fonte de dados do Cloud Storage ou do Drive

Crie uma definição de tabela para uma fonte de dados do Cloud Storage ou do Drive usando uma definição de esquema in-line:

  1. Use o comando bq mkdef com a sinalização --noautodetect para criar uma definição de tabela. O comando mkdef gera um arquivo de definição de tabela no formato JSON. O exemplo a seguir cria uma definição de tabela e grava a saída em um arquivo: /tmp/file_name.

    bq mkdef \
  --noautodetect \
  --source_format=SOURCE_FORMAT \
  "URI" \
  FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME

    Substitua:

    • SOURCE_FORMAT: o formato do arquivo de origem

    • URI: o URI do Cloud Storage ou o URI do Drive;

      Por exemplo, gs://mybucket/myfile para o Cloud Storage ou https://drive.google.com/open?id=123ABCD123AbcD123Abcd para o Drive.

    • FIELD:DATA_TYPE,FIELD:DATA_TYPE: a definição do esquema

      Por exemplo, Name:STRING,Address:STRING, ....

    • FILE_NAME: o nome do arquivo de definição de tabela.

  2. Opcional: abra o arquivo de definição de tabela em um editor de texto. Por exemplo, o comando nano /tmp/file_name abre o arquivo em formato nano. O arquivo deve se assemelhar ao exemplo seguinte. Observe que "autodetect" não está ativado e as informações do esquema são gravadas no arquivo de definição da tabela.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Não há ajustes de configuração específicos para os arquivos de origem JSON, mas existem configurações aplicáveis aos arquivos do Planilhas Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Usar um arquivo de esquema JSON

Se você não quer usar a detecção automática ou fornecer uma definição de esquema in-line, é possível criar um arquivo de esquema JSON e fazer referência a ele ao criar o arquivo de definição de tabela. Crie o arquivo de esquema JSON manualmente na máquina local. Não é possível fazer referência a um arquivo de esquema JSON no Cloud Storage ou no Google Drive.

Usar um arquivo de esquema com uma fonte de dados do Cloud Storage

Crie uma definição de tabela para uma fonte de dados do Cloud Storage usando um arquivo de esquema JSON:

  1. Utilize o comando bq mkdef com a sinalização --noautodetect para criar uma definição de tabela. O comando mkdef gera um arquivo de definição de tabela no formato JSON. O exemplo a seguir cria uma definição de tabela e grava a saída em um arquivo: /tmp/file_name.

    bq mkdef \
   --noautodetect \
   --source_format=SOURCE_FORMAT \
   "URI" \
  PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    Substitua:

    • SOURCE_FORMAT: o formato do arquivo
    • FILE_NAME: o nome do arquivo de definição de tabela.

    • URI: o URI do Cloud Storage.

      Por exemplo, gs://mybucket/myfile.

    • PATH_TO_SCHEMA_FILE: o local do arquivo de esquema JSON na máquina local.

  2. Opcional: abra o arquivo de definição de tabela em um editor de texto. Por exemplo, o comando nano /tmp/file_name abre o arquivo em formato
    nano. O arquivo deve se assemelhar ao exemplo seguinte. Observe que "autodetect" não está ativado e as informações do esquema são gravadas no arquivo de definição da tabela.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Não há ajustes de configuração específicos para os arquivos de origem JSON, mas existem configurações aplicáveis aos arquivos do Planilhas Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Usar um arquivo de esquema com uma fonte de dados do Drive

Crie uma definição de tabela para uma fonte de dados do Drive usando um arquivo de esquema JSON:

  1. Utilize o comando bq mkdef com a sinalização --noautodetect para criar uma definição de tabela. O comando mkdef gera um arquivo de definição de tabela no formato JSON. O exemplo a seguir cria uma definição de tabela e grava a saída em um arquivo: /tmp/file_name.

    bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "URI" \
   PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    Substitua:

    • SOURCE_FORMAT: o formato do arquivo de origem

    • URI: o URI do Drive

      Por exemplo, https://drive.google.com/open?id=123ABCD123AbcD123Abcd.

    • PATH_TO_SCHEMA_FILE: o local do arquivo de esquema JSON na máquina local.

    • FILE_NAME: o nome do arquivo de definição de tabela.

  2. Abra o arquivo de definição de tabela em um editor de texto. Por exemplo, o comando nano /tmp/file_name abre o arquivo em formato nano. O arquivo deve se assemelhar ao exemplo seguinte. Observe que "autodetect" não está ativado e as informações do esquema são gravadas no arquivo de definição da tabela.

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Não há ajustes de configuração específicos para os arquivos de origem JSON, mas existem configurações aplicáveis aos arquivos do Planilhas Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

  4. Para especificar uma determinada página ou um intervalo de células em um arquivo do Planilhas Google, adicione a property range ao arquivo de definição de tabela. Para consultar uma planilha específica, especifique o nome dela. Para consultar um intervalo de células, especifique o intervalo no formato sheet_name!top_left_cell_id:bottom_right_cell_id. Por exemplo: "Sheet1!A1:B20". Se o parâmetro range não estiver especificado, a primeira página do arquivo será usada.

Criar um arquivo de definição para formatos autodescritivos

Avro, Parquet e ORC são formatos autodescritivos. Os arquivos de dados nesses formatos contêm as próprias informações de esquema. Se você usa um desses formatos como uma fonte de dados externa, o BigQuery recupera automaticamente o esquema usando os dados de origem. Ao criar uma definição de tabela, você não precisa usar a detecção automática de esquema e nem fornecer uma definição ou um arquivo de esquema in-line.

É possível criar um arquivo de definição de tabela para dados Avro, Parquet ou ORC armazenados no Cloud Storage ou no Google Drive:

  1. Use o comando bq mkdef para criar uma definição de tabela.

    bq mkdef \
    --source_format=FORMAT \
    "URI" > FILE_NAME

    Substitua:

    • FORMAT: o formato de origem

    • URI: o URI do Cloud Storage ou o URI do Drive;

      Por exemplo, gs://mybucket/myfile para o Cloud Storage ou https://drive.google.com/open?id=123ABCD123AbcD123Abcd para o Drive.

    • FILE_NAME: o nome do arquivo de definição de tabela.

  2. Opcional: abra o arquivo de definição de tabela em um editor de texto. O arquivo se parecerá com isto:

    {
   "sourceFormat": "AVRO",
   "sourceUris": [
   "URI"
    ]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Para mais informações, consulte ExternalDataConfiguration na Referência da API.

Criar um arquivo de definição para dados particionados no Hive

Use obq mkdef comando com as sinalizações hive_partitioning_mode e hive_partitioning_source_uri_prefix para criar um arquivo de definição para dados particionados do hive armazenado no Cloud Storage, no Amazon Simple Storage Service (Amazon S3) ou no Armazenamento de Blob do Azure.

Criar um arquivo de definição para o Datastore e o Firestore

Se você usa uma exportação de Datastore ou Firestore como fonte de dados externa, o BigQuery recupera automaticamente o esquema usando os dados de origem com descrição automática. Ao criar uma definição de tabela, não é necessário fornecer uma definição de esquema in-line ou um arquivo de esquema.

Crie um arquivo de definição de tabela para os dados de exportação do Datastore e do Firestore armazenados no Cloud Storage:

  1. Use o comando bq mkdef para criar uma definição de tabela. Você não precisa usar a sinalização --noautodetect com arquivos de backup do Datastore ou do Firestore. A detecção automática de esquema está desativada para esses tipos de arquivo. O comando mkdef gera um arquivo de definição de tabela no formato JSON. O exemplo a seguir cria uma definição de tabela e grava a saída em um arquivo: /tmp/file_name.

    bq mkdef \
--source_format=DATASTORE_BACKUP \
"URI" > /tmp/FILE_NAME

    Substitua:

    O formato de origem DATASTORE_BACKUP é usado para o Datastore e o Firestore.

  2. Opcional: abra o arquivo de definição de tabela em um editor de texto. Por exemplo, o comando nano /tmp/file_name abre o arquivo em formato nano. O arquivo deve se assemelhar ao exemplo seguinte. Observe que não há necessidade da configuração "autodetect".

    {
"sourceFormat": "DATASTORE_BACKUP",
"sourceUris": [
  "gs://URI"
]
}

  3. Opcional: edite manualmente o arquivo de definição de tabela para modificar, adicionar ou excluir configurações gerais, como maxBadRecords e ignoreUnknownValues. Não há definições de configuração específicas para os arquivos de exportação do Datastore e do Firestore. Para mais informações, consulte ExternalDataConfiguration na Referência da API.

Criar um arquivo de definição para o Bigtable

Ao criar um arquivo de definição de tabela no Cloud Bigtable, você gera o arquivo manualmente no formato JSON. No momento, o uso do comando mkdef para criar uma definição de tabela não é compatível com as fontes de dados do Cloud Bigtable. A detecção automática de esquema também não é compatível com o Bigtable. Para conferir uma lista de opções de definição de tabela do Bigtable, consulte BigtableOptions na Referência da API REST.

O arquivo JSON de definição de tabela do Bigtable é semelhante ao mostrado a seguir. Com esse arquivo, o BigQuery lê os dados de um único grupo de colunas, interpretando os valores como números inteiros binários codificados.

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

Substitua:

  • PROJECT_ID: o projeto que contém o cluster do Bigtable.
  • INSTANCE_ID: o ID da instância do Bigtable.
  • TABLE_NAME: o nome da tabela que você está consultando.
  • FAMILY_ID: o identificador do grupo de colunas.

Para mais informações, consulte Como recuperar o URI do Bigtable.

Compatibilidade com caracteres curinga para arquivos de definição de tabela

Se os dados estiverem separados em vários arquivos, use um caractere curinga (*) para selecionar vários arquivos. O uso do caractere curinga de asterisco precisa seguir estas regras:

  • O caractere curinga pode ser exibido dentro ou no final do nome do objeto.
  • Não é possível usar vários asteriscos. Por exemplo, o caminho gs://mybucket/fed-*/temp/*.csv é inválido.
  • Não é possível usar um asterisco com o nome do bucket.

Exemplos:

  • O exemplo a seguir mostra como selecionar todos os arquivos em todas as pastas que começam com o prefixo gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • O exemplo a seguir mostra como selecionar apenas arquivos com uma extensão .csv na pasta chamada fed-samples e em qualquer subpasta de fed-samples:

    gs://mybucket/fed-samples/*.csv

  • O exemplo a seguir mostra como selecionar arquivos com um padrão de nomenclatura de fed-sample*.csv na pasta chamada fed-samples. Este exemplo não seleciona arquivos em subpastas de fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Ao usar a ferramenta de linha de comando bq, talvez seja necessário evitar o asterisco em algumas plataformas.

Se você usar um caractere curinga de asterisco, coloque o bucket e o nome do arquivo entre aspas. Por exemplo, se você tiver dois arquivos chamados fed-sample000001.csv e fed-sample000002.csv e quiser usar um asterisco para selecionar ambos, o URI do bucket será "gs://mybucket/fed-sample*".

O caractere curinga * não é permitido ao criar arquivos de definição de tabela para as seguintes fontes de dados:

  • Bigtable. Para dados do Bigtable, apenas uma fonte de dados pode ser especificada. O valor do URI precisa ser um URL HTTPS válido para uma tabela do Bigtable.
  • Datastore ou Firestore. Exportações do Datastore ou do Firestore armazenadas no Cloud Storage. Para backups do Datastore, apenas uma fonte de dados pode ser especificada. O valor do URI precisa terminar com .backup_info ou .export_metadata.
  • Drive. Dados armazenados no Drive.

A seguir