Consultar dados do Cloud Storage em tabelas externas

bq

Consulta uma tabela temporária associada a uma origem de dados externa através do comando bq query com a flag --external_table_definition. Quando usa a ferramenta de linhas de comando bq para consultar uma tabela temporária associada a uma origem de dados externa, pode identificar o esquema da tabela através do seguinte:

• Um ficheiro de definição de tabela (armazenado no seu computador local)
• Uma definição de esquema inline
• Um ficheiro de esquema JSON (armazenado no seu computador local)

(Opcional) Forneça a flag --location e defina o valor para a sua localização.

Para consultar uma tabela temporária associada à sua origem de dados externa através de um ficheiro de definição de tabela, introduza o seguinte comando.

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

Substitua o seguinte:

• LOCATION: o nome da sua localização. A flag --location é opcional. Por exemplo, se estiver a usar o BigQuery na região de Tóquio, pode definir o valor da flag como asia-northeast1. Pode predefinir um valor para a localização através do ficheiro.bigqueryrc.
• TABLE: o nome da tabela temporária que está a criar.
• DEFINITION_FILE: o caminho para o ficheiro de definição da tabela no seu computador local.
• QUERY: a consulta que está a enviar para a tabela temporária.

Por exemplo, o seguinte comando cria e consulta uma tabela temporária denominada sales através de um ficheiro de definição de tabela denominado sales_def.

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

Para consultar uma tabela temporária associada à sua origem de dados externa através de uma definição de esquema inline, introduza o seguinte comando.

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

Substitua o seguinte:

• LOCATION: o nome da sua localização. A flag --location é opcional. Por exemplo, se estiver a usar o BigQuery na região de Tóquio, pode definir o valor da flag como asia-northeast1. Pode predefinir um valor para a localização através do ficheiro.bigqueryrc.
• TABLE: o nome da tabela temporária que está a criar.
• SCHEMA: a definição do esquema inline no formato field:data_type,field:data_type.
• SOURCE_FORMAT: o formato da origem de dados externa, por exemplo, CSV.

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

  Pode selecionar vários ficheiros do contentor especificando um caráter universal (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

  Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

  Os exemplos seguintes mostram valores uris válidos:

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

  Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

  Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o caminho de recurso do Cloud Storage.

• QUERY: a consulta que está a enviar para a tabela temporária.

Por exemplo, o comando seguinte cria e consulta uma tabela temporária denominada sales associada a um ficheiro 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 associada à sua origem de dados externa através de um ficheiro de esquema JSON, introduza o seguinte comando.

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

Substitua o seguinte:

• LOCATION: o nome da sua localização. A flag --location é opcional. Por exemplo, se estiver a usar o BigQuery na região de Tóquio, pode definir o valor da flag como asia-northeast1. Pode predefinir um valor para a localização através do ficheiro.bigqueryrc.
• SCHEMA_FILE: o caminho para o ficheiro de esquema JSON no seu computador local.
• SOURCE_FORMAT: o formato da origem de dados externa, por exemplo, CSV.

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

  Pode selecionar vários ficheiros do contentor especificando um caráter universal (*) no file_pattern. Por exemplo, gs://mybucket/file00*.parquet. Para mais informações, consulte o artigo Suporte de carateres universais para URIs do Cloud Storage.

  Pode especificar vários contentores para a opção uris fornecendo vários caminhos.

  Os exemplos seguintes mostram valores uris válidos:

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

  Quando especifica valores uris que segmentam vários ficheiros, todos esses ficheiros têm de partilhar um esquema compatível.

  Para mais informações sobre a utilização de URIs do Cloud Storage no BigQuery, consulte o caminho de recurso do Cloud Storage.

• QUERY: a consulta que está a enviar para a tabela temporária.

Por exemplo, o comando seguinte cria e consulta uma tabela temporária denominada sales associada a um ficheiro CSV armazenado no Cloud Storage através do ficheiro 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 através da API, siga estes passos:

1. Crie um objeto Job.
2. Preencha a secção configuration do objeto Job com um objeto JobConfiguration.
3. Preencha a secção query do objeto JobConfiguration com um objeto JobConfigurationQuery.
4. Preencha a secçã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 o objeto Job.

Java

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

// 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

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