Eseguire query sui dati di Cloud Storage nelle tabelle esterne

bq

Esegui una query su una tabella temporanea collegata a un'origine dati esterna utilizzando il comando bq query con il flag --external_table_definition. Quando utilizzi lo strumento a riga di comando bq per eseguire query su una tabella temporanea collegata a un'origine dati esterna, puoi identificare lo schema della tabella utilizzando:

• Un file di definizione della tabella (memorizzato sul computer locale)
• Una definizione dello schema in linea
• Un file di schema JSON (memorizzato sulla macchina locale)

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua posizione.

Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando un file di definizione della tabella, inserisci il seguente comando.

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

Sostituisci quanto segue:

• LOCATION: il nome della tua posizione. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.
• TABLE: il nome della tabella temporanea che stai creando.
• DEFINITION_FILE: il percorso del file di definizione della tabella sul computer locale.
• QUERY: la query che invii alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue query su una tabella temporanea denominata sales utilizzando un file di definizione della tabella denominato sales_def.

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

Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando una definizione dello schema incorporata, inserisci il seguente comando.

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

Sostituisci quanto segue:

• LOCATION: il nome della tua posizione. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.
• TABLE: il nome della tabella temporanea che stai creando.
• SCHEMA: la definizione dello schema in linea nel formato field:data_type,field:data_type.
• SOURCE_FORMAT: il formato dell'origine dati esterna, ad esempio CSV.

• BUCKET_PATH: il percorso del bucket Cloud Storage che contiene i dati per la tabella, nel formato gs://bucket_name/[folder_name/]file_pattern.

  Puoi selezionare più file dal bucket specificando un carattere jolly asterisco (*) in file_pattern. Ad esempio: gs://mybucket/file00*.parquet. Per maggiori informazioni, vedi Supporto dei caratteri jolly per gli URI Cloud Storage.

  Puoi specificare più bucket per l'opzione uris fornendo più percorsi.

  I seguenti esempi mostrano valori uris validi:

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

  Quando specifichi valori uris che hanno come target più file, tutti questi file devono condividere uno schema compatibile.

  Per ulteriori informazioni sull'utilizzo degli URI Cloud Storage in BigQuery, consulta Percorso della risorsa Cloud Storage.

• QUERY: la query che invii alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue una query su una tabella temporanea denominata sales collegata a un file CSV archiviato in Cloud Storage con la seguente definizione dello schema: 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'

Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando un file di schema JSON, inserisci il seguente comando.

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

Sostituisci quanto segue:

• LOCATION: il nome della tua posizione. Il flag --location è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag su asia-northeast1. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.
• SCHEMA_FILE: il percorso del file dello schema JSON sul computer locale.
• SOURCE_FORMAT: il formato dell'origine dati esterna, ad esempio CSV.

• BUCKET_PATH: il percorso del bucket Cloud Storage che contiene i dati per la tabella, nel formato gs://bucket_name/[folder_name/]file_pattern.

  Puoi selezionare più file dal bucket specificando un carattere jolly asterisco (*) in file_pattern. Ad esempio: gs://mybucket/file00*.parquet. Per maggiori informazioni, vedi Supporto dei caratteri jolly per gli URI Cloud Storage.

  Puoi specificare più bucket per l'opzione uris fornendo più percorsi.

  I seguenti esempi mostrano valori uris validi:

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

  Quando specifichi valori uris che hanno come target più file, tutti questi file devono condividere uno schema compatibile.

  Per ulteriori informazioni sull'utilizzo degli URI Cloud Storage in BigQuery, consulta Percorso della risorsa Cloud Storage.

• QUERY: la query che invii alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue query su una tabella temporanea denominata sales collegata a un file CSV archiviato in Cloud Storage utilizzando il file di schema /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

Per eseguire una query utilizzando l'API:

1. Crea un oggetto Job.
2. Compila la sezione configuration dell'oggetto Job con un oggetto JobConfiguration.
3. Compila la sezione query dell'oggetto JobConfiguration con un oggetto JobConfigurationQuery.
4. Compila la sezione tableDefinitions dell'oggetto JobConfigurationQuery con un oggetto ExternalDataConfiguration.
5. Chiama il metodo jobs.insert per eseguire la query in modo asincrono o il metodo jobs.query per eseguire la query in modo sincrono, passando l'oggetto 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)))