Consultar dados do Cloud Storage em tabelas externas

Neste documento, descrevemos como consultar dados armazenados em uma tabela externa do Cloud Storage.

Antes de começar

Verifique se você tem uma tabela externa do Cloud Storage.

Funções exigidas

Para consultar tabelas externas do Cloud Storage, verifique se você tem os seguintes papéis:

  • Leitor de dados do BigQuery (roles/bigquery.dataViewer)
  • Usuário do BigQuery (roles/bigquery.user)
  • Leitor de objetos do Storage (roles/storage.objectViewer)

Dependendo das suas permissões, é possível conceder esses papéis a você mesmo ou pedir ao administrador para concedê-los. Para mais informações sobre como conceder papéis, consulte Como visualizar os papéis atribuíveis em recursos.

Para conferir as permissões exatas do BigQuery necessárias para consultar tabelas externas, expanda a seção Permissões necessárias:

Permissões necessárias

Essas permissões também podem ser concedidas com papéis personalizados ou outros papéis predefinidos.

Consultar tabelas externas permanentes

Após criar uma tabela externa do Cloud Storage, consulte-a usando a sintaxe do Google SQL, como se ela fosse uma tabela padrão do BigQuery. Por exemplo, SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Consultar tabelas externas temporárias

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).

Para consultar uma fonte de dados externa sem criar uma tabela permanente, forneça uma definição de tabela para a tabela temporária e use-a em um comando ou uma chamada para consultar a tabela temporária. É possível fornecer a definição da tabela de uma destas maneiras:

O arquivo de definição de tabela ou esquema fornecido é usado para criar a tabela externa temporária, e a consulta será executada nela.

A tabela externa 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.

É possível criar e consultar uma tabela temporária vinculada a uma fonte de dados externa usando a ferramenta de linha de comando bq, a API ou as bibliotecas de cliente.

bq

É possível consultar uma tabela temporária vinculada a uma fonte de dados externa usando o comando bq query com a flag --external_table_definition. Ao usar a ferramenta de linha de comando bq para consultar uma tabela temporária vinculada a uma fonte de dados externa, é possível identificar o esquema dessa tabela usando:

Opcional: forneça a sinalização --location e defina o valor do local.

Para consultar uma tabela temporária vinculada à sua fonte de dados externa usando um arquivo de definição de tabela, digite o seguinte comando:

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

Substitua:

  • LOCATION: o nome do seu local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;
  • TABLE: o nome da tabela temporária que você está criando
  • DEFINITION_FILE: o caminho para o arquivo de definição de tabelas na máquina local.
  • QUERY: a consulta que você está enviando para a tabela temporária.

Por exemplo, o comando a seguir cria e consulta uma tabela temporária chamada sales usando um arquivo de definição de tabela chamado sales_def.

bq query \
--external_table_definition=sales::sales_def \
'SELECT
  Region,
  Total_sales
FROM
  sales'

Para consultar uma tabela temporária vinculada à sua fonte de dados externa usando uma definição de esquema in-line, digite o seguinte comando.

bq --location=LOCATION query \
--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=BUCKET_PATH \
'QUERY'

Substitua:

  • LOCATION: o nome do seu local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;
  • TABLE: o nome da tabela temporária que você está criando
  • SCHEMA: a definição de esquema in-line no formato field:data_type,field:data_type
  • SOURCE_FORMAT: o formato da fonte de dados externa, por exemplo, CSV.

  • BUCKET_PATH: o caminho para o bucket do Cloud Storage que contém os dados da tabela, no formato gs://bucket_name/[folder_name/]file_pattern.

    É possível selecionar vários arquivos do bucket especificando um caractere curinga de asterisco (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte Compatibilidade de caracteres curinga com URIs do Cloud Storage.

    É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

    Os exemplos a seguir mostram valores uris válidos:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

    Para mais informações sobre o uso de URIs do Cloud Storage no BigQuery, consulte Caminho do recurso do Cloud Storage.

  • QUERY: a consulta que você está enviando para a tabela temporária.

Por exemplo, o comando a seguir cria e consulta uma tabela temporária chamada sales vinculada a um arquivo CSV armazenado no Cloud Storage com a seguinte definição de esquema: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

Para consultar uma tabela temporária vinculada à sua fonte de dados externa usando um arquivo de esquema JSON, digite o seguinte comando:

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \
'QUERY'

Substitua:

  • LOCATION: o nome do seu local. A sinalização --location é opcional. Por exemplo, se você estiver usando o BigQuery na região de Tóquio, defina o valor da sinalização como asia-northeast1. É possível definir um valor padrão para o local usando o arquivo .bigqueryrc;
  • SCHEMA_FILE: o caminho para o arquivo de esquema JSON na máquina local
  • SOURCE_FORMAT: o formato da fonte de dados externa, por exemplo, CSV.

  • BUCKET_PATH: o caminho para o bucket do Cloud Storage que contém os dados da tabela, no formato gs://bucket_name/[folder_name/]file_pattern.

    É possível selecionar vários arquivos do bucket especificando um caractere curinga de asterisco (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte Compatibilidade de caracteres curinga com URIs do Cloud Storage.

    É possível especificar vários buckets para a opção uris fornecendo múltiplos caminhos.

    Os exemplos a seguir mostram valores uris válidos:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Quando você especifica valores uris voltados para vários arquivos, todos eles precisam compartilhar um esquema compatível.

    Para mais informações sobre o uso de URIs do Cloud Storage no BigQuery, consulte Caminho do recurso do Cloud Storage.

  • QUERY: a consulta que você está enviando para a tabela temporária.

Por exemplo, o comando a seguir cria e consulta uma tabela temporária chamada sales vinculada a um arquivo CSV armazenado no Cloud Storage usando o arquivo de esquema /tmp/sales_schema.json.

  bq query \
  --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
  'SELECT
      Region,
      Total_sales
    FROM
      sales'

API

Para executar uma consulta usando a API, siga estas etapas:

  1. Criar um objeto Job.
  2. Preencha a seção configuration do objeto Job com um objeto JobConfiguration.
  3. Preencha a seção query do objeto JobConfiguration com um objeto JobConfigurationQuery.
  4. Preencha a seção tableDefinitions do objeto JobConfigurationQuery com um objeto ExternalDataConfiguration.
  5. Chame o método jobs.insert para executar a consulta de forma assíncrona ou o método jobs.query para executar a consulta de forma síncrona, transmitindo no objeto Job.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java 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 Java.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a temporary table
public class QueryExternalGCSTemp {

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Antes de testar esta amostra, siga as instruções de configuração do Node.js 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 Node.js.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de cliente. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

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, acesse Configurar a autenticação para bibliotecas de cliente. 

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with 'W'.
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

w_states = list(query_job)  # Wait for the job to complete.
print("There are {} states with names starting with W.".format(len(w_states)))

Consulte a pseudocoluna _FILE_NAME:

Tabelas baseadas em fontes de dados externas fornecem uma pseudocoluna denominada _FILE_NAME. Ela contém o caminho totalmente qualificado para o arquivo ao qual a linha pertence. Essa coluna está disponível apenas para tabelas com referência a dados externos armazenados no Cloud Storage, Google Drive, Amazon S3 e Armazenamento de Blobs do Azure.

O nome de coluna _FILE_NAME é reservado. Por isso, não crie nenhuma coluna com esse nome nas tabelas. Para selecionar o valor de _FILE_NAME, use um alias. O exemplo de consulta a seguir mostra a seleção de _FILE_NAME ao atribuir o alias fn na pseudocoluna. 

  bq query \
  --project_id=PROJECT_ID \
  --use_legacy_sql=false \
  'SELECT
     name,
     _FILE_NAME AS fn
   FROM
     `DATASET.TABLE_NAME`
   WHERE
     name contains "Alex"'

Substitua:

  • PROJECT_ID é um ID de projeto válido (essa flag não é necessária se você usa o Cloud Shell ou configurou um projeto padrão na CLI do Google Cloud)
  • DATASET é o nome do conjunto de dados que armazena a tabela externa permanente
  • TABLE_NAME é o nome da tabela externa permanente.

Quando a consulta tem um predicado de filtro na pseudocoluna _FILE_NAME, o BigQuery tenta ignorar a leitura de arquivos que não satisfazem esse filtro. Recomendações semelhantes às consultas de tabelas particionadas por tempo de processamento usando pseudocolunas são aplicadas ao criar predicados de consulta com a pseudocoluna _FILE_NAME.

A seguir