Como criar um arquivo de definição de tabela para uma fonte de dados externa

Nesta página, descrevemos como criar um arquivo de definição de tabela para uma fonte de dados externa. Em uma fonte de dados externa, também conhecida como federada, é possível realizar consultas diretamente, mesmo que os dados não estejam armazenados no BigQuery.

Arquivos de definição de tabela

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 tabela, use a detecção automática de esquema para defini-lo em uma fonte de dados externa. É possível fornecer o esquema in-line (na linha de comando) ou um arquivo JSON que contenha a definição do esquema.

Os arquivos de definição de tabela são usados com a CLI do BigQuery. As propriedades em um arquivo de definição de tabela também se aplicam à criação de um ExternalDataConfiguration quando a API REST é usada. Arquivos de definição de tabela não são usados na criação de tabelas externas ao utilizar o Console do Cloud ou a versão clássica da IU da Web do BigQuery.

É possível criar arquivos de definição de tabela para as seguintes fontes de dados externas:

  • Cloud Storage

    • Valores separados por vírgula (CSV, na sigla em inglês)
    • JSON delimitado por nova linha
    • Arquivos Avro
    • Arquivos de exportação do Datastore
    • Arquivos ORC (Beta)
    • Arquivos Parquet (Beta)
    • Arquivos de exportação do Cloud Firestore
  • Google Drive

    • Valores separados por vírgula (CSV, na sigla em inglês)
    • JSON delimitado por nova linha
    • Arquivos Avro
    • Planilhas Google
  • Cloud Bigtable

Antes de começar

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

Tabelas externas permanentes x temporárias

É possível consultar uma fonte de dados externa no BigQuery usando uma tabela permanente ou uma temporária. Uma tabela permanente é criada em um conjunto de dados e vinculada à sua fonte de dados externa. Como ela é permanente, é possível usar controles de acesso no nível do conjunto de dados para compartilhá-la com outras pessoas que também têm acesso à fonte de dados externa subjacente, além de consultá-la a qualquer momento.

Ao consultar uma fonte de dados externa usando uma tabela temporária, você envia um comando que inclui uma consulta e cria uma tabela não permanente vinculada a essa fonte. A tabela temporária não é criada em um dos conjuntos de dados do BigQuery. Como ela não fica armazenada permanentemente em um conjunto de dados, não é possível compartilhá-la com outros. A consulta a uma fonte de dados externa usando uma tabela temporária é útil quando você quer consultar dados externos apenas uma vez, com um propósito específico, ou executar processos de extração, transformação e carregamento (ETL, na sigla em inglês).

É possível usar um arquivo de definição de tabela para descrever uma tabela externa permanente ou temporária.

Como criar uma definição de tabela usando a detecção automática de esquema

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 no BigQuery.

É possível usar a detecção automática de esquema ao criar definições de tabela para:

  • arquivos JSON armazenados no Cloud Storage ou no Google Drive;
  • arquivos CSV armazenados no Cloud Storage ou no Google Drive;
  • arquivos do Planilhas Google armazenados no Google Drive.

Cloud Storage

É possível usar a interface de linha de comando para criar uma definição de tabela para uma fonte de dados do Cloud Storage. Para isso, siga as instruções abaixo:

  1. Use o comando mkdef da ferramenta de linha de comando 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.

    No comando, substitua:

    • source_format pelo formato de arquivo: NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS;
    • file_name pelo nome do seu arquivo de definição de tabela;
    • bucket_uri pelo URI do Cloud Storage. Por exemplo: gs://mybucket/myfile.
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "bucket_uri" > /tmp/file_name
    
  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. Observe que "autodetect" está definido como true.

    {
    "autodetect": true,
    "csvOptions": {
      "allowJaggedRows": false,
      "allowQuotedNewlines": false,
      "encoding": "UTF-8",
      "fieldDelimiter": ",",
      "quote": "\"",
      "skipLeadingRows": 0
    },
    "sourceFormat": "CSV",
    "sourceUris": [
      "bucket_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 Planilha Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Dados particionados externamente

Para configurar um arquivo de definição de tabela para dados particionados externamente no Cloud Storage, consulte Como consultar dados particionados externamente.

Google Drive

É possível usar a interface de linha de comando para criar uma definição de tabela para uma fonte de dados do Google Drive. Para isso, siga as instruções abaixo:

  1. Use o comando mkdef da ferramenta de linha de comando 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.

    No comando, substitua:

    • source_format pelo formato de arquivo: NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS;
    • file_name pelo nome do seu arquivo de definição de tabela.
    • drive_uri pelo URI do Google Drive. Por exemplo: https://drive.google.com/open?id=123ABCD123AbcD123Abcd;
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "drive_uri" > /tmp/file_name
    
  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": [
      "drive_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 Planilha 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, informe 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.

Como criar uma definição de tabela usando um esquema in-line

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.

É possível usar uma definição de esquema in-line ao criar arquivos de definição de tabela para:

  • arquivos JSON armazenados no Cloud Storage ou no Google Drive;
  • arquivos CSV armazenados no Cloud Storage ou no Google Drive;
  • arquivos do Planilhas Google armazenados no Google Drive.

É possível usar a interface de linha de comando para criar uma definição de tabela para uma fonte de dados do Cloud Storage usando uma definição de esquema in-line. Para isso, siga as instruções abaixo:

  1. Use o comando mkdef da ferramenta de linha de comando 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.

    No comando, substitua:

    • source_format pelo formato de arquivo: NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS;
    • uri pelo URI de Cloud Storage ou pelo URI do Google Drive. Por exemplo: https://drive.google.com/open?id=123ABCD123AbcD123Abcd para o Google Drive ou gs://mybucket/myfile para o Cloud Storage;
    • field:data_type,field:data_type pela sua definição de esquema. Por exemplo: Name:STRING,Address:STRING, ....
    • file_name pelo nome do seu arquivo de definição de tabela.
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "uri" \
    field:data_type,field:data_type > /tmp/file_name
    
  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 Planilha Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Como criar uma definição de tabela usando 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. É necessário criar o arquivo de esquema JSON manualmente, e ele precisa estar na sua máquina local. Não é possível fazer referência a um arquivo de esquema JSON no Cloud Storage ou no Google Drive.

É possível usar um arquivo de esquema JSON ao criar definições de tabela para:

  • arquivos JSON armazenados no Cloud Storage ou no Google Drive;
  • arquivos CSV armazenados no Cloud Storage ou no Google Drive;
  • arquivos do Planilhas Google armazenados no Google Drive.

Cloud Storage

É possível usar a interface de linha de comando para criar uma definição de tabela para uma fonte de dados do Cloud Storage usando um arquivo de esquema JSON. Para isso, siga as instruções abaixo:

  1. Use o comando mkdef da ferramenta de linha de comando 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.

    No comando, substitua:

    • source_format pelo formato de arquivo: NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS;
    • file_name pelo nome do seu arquivo de definição de tabela;
    • bucket_uri pelo URI do Cloud Storage. Por exemplo: gs://mybucket/myfile;
    • path_to_schema_file pelo local do arquivo de esquema JSON na sua máquina local;
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "bucket_uri" \
    path_to_schema_file > /tmp/file_name
    
  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": [
      "bucket_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 Planilha Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Google Drive

É possível usar a interface de linha de comando para criar uma definição de tabela para uma fonte de dados do Google Drive usando um arquivo de esquema JSON. Para isso, siga as instruções abaixo:

  1. Use o comando mkdef da ferramenta de linha de comando 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.

    No comando, substitua:

    • source_format pelo formato de arquivo: NEWLINE_DELIMITED_JSON, CSV ou GOOGLE_SHEETS;
    • drive_uri pelo URI do Google Drive. Por exemplo: https://drive.google.com/open?id=123ABCD123AbcD123Abcd;
    • path_to_schema_file pelo local do arquivo de esquema JSON na sua máquina local;
    • file_name pelo nome do seu arquivo de definição de tabela.
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "drive_uri" \
    path_to_schema_file > /tmp/file_name
    
  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": [
      "drive_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 Planilha 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, informe 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.

Como criar definições de tabela Avro

Se você usa um arquivo Avro como fonte de dados externa, o BigQuery recupera automaticamente o esquema usando os dados de origem. Ao criar uma definição de tabela para arquivos Avro, você não precisa usar a detecção automática de esquema e não precisa fornecer uma definição ou um arquivo de esquema in-line.

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

É possível usar a interface de linha de comando para criar um arquivo de definição de tabela para dados Avro. Para isso, siga as instruções abaixo:

  1. Use o comando mkdef da ferramenta de linha de comando para criar uma definição de tabela. Não é preciso usar a sinalização --noautodetect com arquivos Avro. A detecção automática de esquema é desativada para arquivos Avro. 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.

    No comando, substitua:

    • uri pelo URI de Cloud Storage ou pelo URI do Google Drive. Por exemplo: https://drive.google.com/open?id=123ABCD123AbcD123Abcd para o Google Drive ou gs://mybucket/myfile para o Cloud Storage;
    • file_name pelo nome do seu arquivo de definição de tabela.
    bq mkdef \
    --source_format=AVRO \
    "uri" > /tmp/file_name
    
  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": "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. Não há ajustes de configuração específicos para os arquivos de origem Avro, mas existem configurações aplicáveis aos arquivos do Planilha Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Como criar definições de tabela de exportação do Datastore e do Cloud Firestore

Se você usa uma exportação do Datastore ou do Cloud Firestore como fonte de dados externa, o BigQuery consegue recuperar automaticamente o esquema usando os dados de origem autoexplicativos. Ao criar uma definição de tabela para arquivos de backup do Datastore e do Cloud Firestore, não é preciso usar a detecção automática de esquema e nem fornecer uma definição de esquema in-line ou arquivo de esquema.

É possível criar um arquivo de definição de tabela para os dados de exportação do Datastore e do Cloud Firestore armazenados no Cloud Storage.

Se você quer usar a CLI para criar um arquivo de definição de tabela para os dados de exportação do Datastore ou do Cloud Firestore, faça o seguinte:

  1. Use o comando mkdef da ferramenta de linha de comando para criar uma definição de tabela. Não é preciso usar a sinalização --noautodetect com arquivos de backup do Datastore ou do Cloud 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.

    No comando, substitua:

    Observe que o formato de origem DATASTORE_BACKUP é usado para o Datastore e o Cloud Firestore.

    bq mkdef \
    --source_format=DATASTORE_BACKUP \
    "uri" > /tmp/file_name
    
  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://bucket_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 exportação do Datastore e do Cloud Firestore, mas existem configurações aplicáveis aos arquivos do Planilha Google e CSV. Para mais informações, consulte a ExternalDataConfiguration na Referência da API.

Como criar definições de tabela do Cloud 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 Cloud Bigtable. Para conferir uma lista de opções de definição de tabela do Cloud Bigtable, consulte BigtableOptions na Referência da API REST.

O arquivo JSON de definição de tabela do Cloud 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_int",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ],
    }
}

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

Se os dados do Cloud Storage estiverem separados em vários arquivos que compartilham um nome de base comum, é possível usar um caractere curinga no URI no arquivo de definição de tabela. Anexe um asterisco (*) ao nome de base e coloque o intervalo e o nome de arquivo entre aspas. Se você tiver dois arquivos chamados fed- sample000001.csv e fed-sample000002.csv, por exemplo, o URI do intervalo será "gs://mybucket/fed-sample*".

Você só pode usar um caractere curinga para objetos (nomes de arquivos) no intervalo. O caractere curinga pode ser exibido dentro ou no final do nome do objeto. Não é possível incluir um caractere curinga ao nome do intervalo.

Para dados do Cloud Bigtable, apenas um URI pode ser especificado. Além disso, ele precisa ser um URL HTTPS totalmente definido e válido para uma tabela do Cloud Bigtable. Para backups do Datastore, apenas um URI pode ser especificado e ele precisa terminar com .backup_info.

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

  • fontes de dados Cloud Bigtable;
  • exportações do Datastore armazenadas no Cloud Storage;
  • exportações do Cloud Firestore armazenadas no Cloud Storage;
  • dados armazenados no Google Drive.