Crie um ficheiro de definição de tabela para uma origem de dados externa

Esta página descreve como criar um ficheiro de definição de tabela para uma origem de dados externa. Uma origem de dados externa é aquela que pode consultar diretamente, mesmo que os dados não estejam armazenados no BigQuery.

Um ficheiro de definição de tabela contém a definição do esquema e os metadados de uma tabela externa, como o formato de dados da tabela e as propriedades relacionadas. Quando cria um ficheiro de definição de tabela, pode usar a deteção automática do esquema para definir o esquema de uma origem de dados externa. Pode fornecer o esquema incorporado ou um ficheiro JSON com a definição do esquema.

Os ficheiros de definição de tabelas são usados com a ferramenta de linhas de comando bq. As propriedades num ficheiro de definição de tabela também se aplicam à criação de um ExternalDataConfiguration quando usa a API REST. Não usa ficheiros de definição de tabelas quando cria uma tabela externa através da consola. Google Cloud

Pode criar ficheiros de definição de tabelas para descrever uma tabela externa permanente ou temporária para as seguintes origens de dados externas:

  • Cloud Storage

    • Valores separados por vírgulas (.csv)
    • JSON delimitado por Newline
    • Ficheiros Avro
    • Ficheiros de exportação do Datastore
    • Ficheiros ORC
    • Ficheiros Parquet
    • Ficheiros de exportação do Firestore

  • Google Drive

    • Valores separados por vírgulas (.csv)
    • JSON delimitado por Newline
    • Ficheiros Avro
    • Google Sheets

  • Bigtable

Antes de começar

Para criar um ficheiro de definição de tabela, precisa do URI da sua origem de dados:

Crie um ficheiro de definição para ficheiros CSV, JSON ou do Google Sheets

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

Use a flag autodetect

Se especificar um ficheiro CSV, JSON ou do Google Sheets sem incluir uma descrição do esquema inline ou um ficheiro de esquema, pode usar a flag --autodetect para definir a opção "autodetect" como true no ficheiro de definição da tabela. Quando a deteção automática está ativada, o BigQuery tenta inferir automaticamente o esquema. Para mais informações, consulte o artigo Deteção automática de esquemas para origens de dados externas.

Use a deteção automática com uma origem de dados do Cloud Storage

Crie um ficheiro de definição de tabela para uma origem de dados do Cloud Storage:

  1. Use o comando bq mkdef com a flag --autodetect para criar um ficheiro de definição de tabela. O comando mkdef gera um ficheiro de definição de tabela no formato JSON. O exemplo seguinte cria uma definição de tabela e escreve o resultado num ficheiro: /tmp/file_name.

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

    Substitua o seguinte:

    • SOURCE_FORMAT: o formato do ficheiro
    • FILE_NAME: o nome do ficheiro de definição da tabela

    • URI: o URI do Cloud Storage

      Por exemplo, gs://mybucket/myfile.

  2. (Opcional) Abra o ficheiro de definição da tabela num editor de texto. Por exemplo, o comando nano /tmp/file_name abre o ficheiro no nano. O ficheiro deve ter o seguinte aspeto para uma origem de dados externos CSV. O aviso "autodetect" está definido para true.

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

  3. (Opcional) Edite manualmente o ficheiro de definição da tabela para modificar, adicionar ou eliminar definições gerais, como maxBadRecords e ignoreUnknownValues. Não existem definições de configuração específicas para ficheiros de origem JSON, mas existem definições que se aplicam a ficheiros CSV e Folhas de cálculo do Google. Para mais informações, consulte ExternalDataConfiguration na referência da API.

Use a deteção automática com uma origem de dados do Drive

Crie um ficheiro de definição de tabela para uma origem de dados do Drive:

  1. Use o comando bq mkdef com a flag --autodetect para criar uma definição de tabela. O comando mkdef gera um ficheiro de definição de tabela no formato JSON. O exemplo seguinte cria uma definição de tabela e escreve o resultado num ficheiro: /tmp/file_name.

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

    Substitua o seguinte:

    • SOURCE_FORMAT: o formato do ficheiro
    • FILE_NAME: o nome do ficheiro de definição da tabela

    • URI: o URI do Drive

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

  2. Abra o ficheiro de definição da tabela num editor de texto. Por exemplo, o comando nano /tmp/file_name abre o ficheiro no nano. O ficheiro deve ter o seguinte aspeto para uma origem de dados externa do Google Sheets. O aviso "autodetect" está definido para true.

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

  3. (Opcional) Edite manualmente o ficheiro de definição da tabela para modificar, adicionar ou eliminar definições gerais, como maxBadRecords e ignoreUnknownValues. Não existem definições de configuração específicas para ficheiros de origem JSON, mas existem definições que se aplicam a ficheiros CSV e Folhas de cálculo do Google. Para mais informações, consulte ExternalDataConfiguration na referência da API.

  4. Para especificar uma folha ou um intervalo de células específico num ficheiro do Google Sheets, adicione a propriedade range ao objeto GoogleSheetsOptions no ficheiro de definição da tabela. Para consultar uma página específica, especifique o nome da página. 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 for especificado, é usada a primeira folha no ficheiro.

Use um esquema inline

Se não quiser usar a deteção automática de esquemas, pode criar um ficheiro de definição de tabela fornecendo uma definição de esquema inline. Para fornecer uma definição de esquema inline, liste os campos e os tipos de dados na linha de comandos no seguinte formato: FIELD:DATA_TYPE,FIELD:DATA_TYPE.

Use um esquema inline com uma origem de dados do Cloud Storage ou do Drive

Crie uma definição de tabela para uma origem de dados do Cloud Storage ou do Drive usando uma definição de esquema inline:

  1. Use o comando bq mkdef com a flag --noautodetect para criar uma definição de tabela. O comando mkdef gera um ficheiro de definição de tabela no formato JSON. O exemplo seguinte cria uma definição de tabela e escreve o resultado num ficheiro: /tmp/file_name.

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

    Substitua o seguinte

    • SOURCE_FORMAT: o formato do ficheiro 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 ficheiro de definição da tabela

  2. (Opcional) Abra o ficheiro de definição da tabela num editor de texto. Por exemplo, o comando nano /tmp/file_name abre o ficheiro no nano. O ficheiro deve ter o seguinte aspeto. Repare que a opção "autodetect" não está ativada e as informações do esquema são escritas no ficheiro 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 ficheiro de definição da tabela para modificar, adicionar ou eliminar definições gerais, como maxBadRecords e ignoreUnknownValues. Não existem definições de configuração específicas para ficheiros de origem JSON, mas existem definições que se aplicam a ficheiros CSV e Folhas de cálculo do Google. Para mais informações, consulte ExternalDataConfiguration na referência da API.

Use um ficheiro de esquema JSON

Se não quiser usar a deteção automática nem fornecer uma definição de esquema inline, pode criar um ficheiro de esquema JSON e referenciá-lo quando criar o ficheiro de definição da tabela. Crie o ficheiro de esquema JSON manualmente na sua máquina local. A referência a um ficheiro de esquema JSON armazenado no Cloud Storage ou no Drive não é suportada.

Use um ficheiro de esquema com uma origem de dados do Cloud Storage

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

  1. Use o comando bq mkdef com a flag --noautodetect para criar uma definição de tabela. O comando mkdef gera um ficheiro de definição de tabela no formato JSON. O exemplo seguinte cria uma definição de tabela e escreve o resultado num ficheiro: /tmp/file_name.

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

    Substitua o seguinte:

    • SOURCE_FORMAT: o formato do ficheiro
    • FILE_NAME: o nome do ficheiro de definição da tabela

    • URI: o URI do Cloud Storage

      Por exemplo, gs://mybucket/myfile.

    • PATH_TO_SCHEMA_FILE: a localização do ficheiro de esquema JSON no seu computador local

  2. (Opcional) Abra o ficheiro de definição da tabela num editor de texto. Por exemplo, o comando nano /tmp/file_name abre o ficheiro no
    nano. O ficheiro deve ter o seguinte aspeto. Repare que a opção "autodetect" não está ativada e as informações do esquema são escritas no ficheiro 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 ficheiro de definição da tabela para modificar, adicionar ou eliminar definições gerais, como maxBadRecords e ignoreUnknownValues. Não existem definições de configuração específicas para ficheiros de origem JSON, mas existem definições que se aplicam a ficheiros CSV e Folhas de cálculo do Google. Para mais informações, consulte ExternalDataConfiguration na referência da API.

Use um ficheiro de esquema com uma origem de dados do Drive

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

  1. Use o comando bq mkdef com a flag --noautodetect para criar uma definição de tabela. O comando mkdef gera um ficheiro de definição de tabela no formato JSON. O exemplo seguinte cria uma definição de tabela e escreve o resultado num ficheiro: /tmp/file_name.

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

    Substitua o seguinte:

    • SOURCE_FORMAT: o formato do ficheiro de origem

    • URI: o URI do Drive

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

    • PATH_TO_SCHEMA_FILE: a localização do ficheiro de esquema JSON no seu computador local

    • FILE_NAME: o nome do ficheiro de definição da tabela

  2. Abra o ficheiro de definição da tabela num editor de texto. Por exemplo, o comando nano /tmp/file_name abre o ficheiro no nano. O ficheiro deve ter o seguinte aspeto. Repare que "autodetect" não está ativado e as informações do esquema são escritas no ficheiro 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 ficheiro de definição da tabela para modificar, adicionar ou eliminar definições gerais, como maxBadRecords e ignoreUnknownValues. Não existem definições de configuração específicas para ficheiros de origem JSON, mas existem definições que se aplicam a ficheiros CSV e Folhas de cálculo do Google. Para mais informações, consulte ExternalDataConfiguration na referência da API.

  4. Para especificar uma folha ou um intervalo de células específico num ficheiro do Google Sheets, adicione a propriedade range ao objeto GoogleSheetsOptions no ficheiro de definição da tabela. Para consultar uma página específica, especifique o nome da página. 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 for especificado, é usada a primeira folha no ficheiro.

Crie um ficheiro de definição para formatos autodescritivos

O Avro, o Parquet e o ORC são formatos autodescritivos. Os ficheiros de dados nestes formatos contêm as suas próprias informações de esquema. Se usar um destes formatos como uma origem de dados externa, o BigQuery obtém automaticamente o esquema através dos dados de origem. Quando cria uma definição de tabela, não precisa de usar a deteção automática de esquemas nem fornecer uma definição de esquema inline ou um ficheiro de esquema.

Pode criar um ficheiro de definição de tabela para dados Avro, Parquet ou ORC armazenados no Cloud Storage ou no Drive:

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

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

    Substitua o seguinte:

    • 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 ficheiro de definição da tabela

  2. Opcional: abra o ficheiro de definição da tabela num editor de texto. O ficheiro tem um aspeto semelhante ao seguinte:

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

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

Crie um ficheiro de definição para dados particionados por Hive

Use o comando bq mkdef com os indicadores hive_partitioning_mode e hive_partitioning_source_uri_prefix para criar um ficheiro de definição para dados particionados por Hive armazenados no Cloud Storage, no Amazon Simple Storage Service (Amazon S3) ou no Azure Blob Storage.

Crie um ficheiro de definição para o Datastore e o Firestore

Se usar uma exportação do Datastore ou do Firestore como uma origem de dados externa, o BigQuery obtém automaticamente o esquema através dos dados de origem autodescritivos. Quando cria uma definição de tabela, não tem de fornecer uma definição de esquema inline nem um ficheiro de esquema.

Pode criar um ficheiro de definição de tabela para 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. Não precisa de usar a flag --noautodetect com ficheiros de cópia de segurança do Datastore ou do Firestore. A deteção automática do esquema está desativada para estes tipos de ficheiros. O comando mkdef gera um ficheiro de definição de tabela no formato JSON. O exemplo seguinte cria uma definição de tabela e escreve o resultado num ficheiro: /tmp/file_name.

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

    Substitua o seguinte:

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

  2. (Opcional) Abra o ficheiro de definição da tabela num editor de texto. Por exemplo, o comando nano /tmp/file_name abre o ficheiro no nano. O ficheiro deve ter o seguinte aspeto. Repare que não é necessária a definição "autodetect".

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

  3. (Opcional) Edite manualmente o ficheiro de definição da tabela para modificar, adicionar ou eliminar definições, como maxBadRecords e ignoreUnknownValues. Não existem definições de configuração específicas dos ficheiros de exportação do Datastore e do Firestore. Para mais informações, consulte ExternalDataConfiguration na referência da API.

Crie um ficheiro de definição para o Bigtable

Quando cria um ficheiro de definição de tabela para o Bigtable, gera manualmente o ficheiro no formato JSON. Atualmente, a utilização do comando mkdef para criar uma definição de tabela não é suportada para origens de dados do Bigtable. A deteção automática de esquemas também não é suportada para o Bigtable. Para ver uma lista das opções de definição de tabelas do Bigtable, consulte BigtableOptions na referência da API REST.

Um ficheiro de definição de tabela JSON para o Bigtable tem o seguinte aspeto. Com este ficheiro de definição de tabela, o BigQuery lê dados de uma única família de colunas, interpretando os valores como números inteiros codificados binários.

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

Substitua o seguinte:

  • PROJECT_ID: o projeto que contém o seu cluster do Bigtable
  • INSTANCE_ID: o ID da instância do Bigtable
  • APP_PROFILE_ID (opcional): o ID do perfil da app que quer usar para ler os seus dados do Bigtable. As definições do perfil da app indicam se a tabela externa usa o Data Boost ou nós aprovisionados para computação.
  • TABLE_NAME: o nome da tabela que está a consultar
  • FAMILY_ID: o identificador da família de colunas

Para mais informações, consulte o artigo Obtenha o URI do Bigtable.

Suporte de carateres universais para ficheiros de definição de tabelas

Se os seus dados estiverem separados em vários ficheiros, pode usar um caráter universal asterisco (*) para selecionar vários ficheiros. A utilização do caráter universal asterisco tem de seguir estas regras:

  • O asterisco pode aparecer no nome do objeto ou no final do nome do objeto.
  • A utilização de vários asteriscos não é suportada. Por exemplo, o caminho gs://mybucket/fed-*/temp/*.csv é inválido.
  • A utilização de um asterisco com o nome do contentor não é suportada.

Exemplos:

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

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

  • O exemplo seguinte mostra como selecionar apenas ficheiros com a extensão .csv na pasta com o nome fed-samples e quaisquer subpastas de fed-samples:

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

  • O exemplo seguinte mostra como selecionar ficheiros com um padrão de nomenclatura de fed-sample*.csv na pasta denominada fed-samples. Este exemplo não seleciona ficheiros em subpastas de fed-samples.

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

Quando usar a ferramenta de linhas de comando bq, pode ter de usar o caráter de escape no asterisco em algumas plataformas.

Se usar um carater universal de asterisco, coloque o nome do contentor e do ficheiro entre aspas. Por exemplo, se tiver dois ficheiros com os nomes fed-sample000001.csv e fed-sample000002.csv e quiser usar um asterisco para selecionar ambos, o URI do contentor seria "gs://mybucket/fed-sample*".

O caráter universal * não é permitido quando cria ficheiros de definição de tabelas para as seguintes origens de dados:

  • Bigtable. Para dados do Bigtable, só é possível especificar uma origem de dados. O valor do URI tem de ser um URL HTTPS válido para uma tabela do Bigtable.
  • Datastore ou Firestore. Datastore ou exportações do Firestore armazenadas no Cloud Storage. Para as cópias de segurança do Datastore, só é possível especificar uma origem de dados. O valor do URI tem de terminar com .backup_info ou .export_metadata.
  • Drive. Dados armazenados no Drive.

O que se segue?