Eseguire query su dati Cloud Storage in tabelle esterne

Questo documento descrive come eseguire query sui dati archiviati in un Tabella esterna di Cloud Storage.

Prima di iniziare

Assicurati di avere una tabella esterna di Cloud Storage.

Ruoli obbligatori

Per eseguire query sulle tabelle esterne di Cloud Storage, assicurati disponi dei seguenti ruoli:

  • Visualizzatore dati BigQuery (roles/bigquery.dataViewer)
  • Utente BigQuery (roles/bigquery.user)
  • Visualizzatore oggetti Storage (roles/storage.objectViewer)

In base alle tue autorizzazioni, puoi concediti questi ruoli o chiedi all'amministratore per concederteli. Per ulteriori informazioni sulla concessione dei ruoli, consulta Visualizzazione dei ruoli assegnabili nelle risorse.

Per visualizzare le autorizzazioni BigQuery esatte necessarie per eseguire la query tabelle esterne, espandi la sezione Autorizzazioni obbligatorie:

Autorizzazioni obbligatorie

Potresti anche riuscire a ottenere queste autorizzazioni con i ruoli personalizzati. o altri ruoli predefiniti.

Esegui query su tabelle esterne permanenti

Dopo aver creato una tabella esterna di Cloud Storage, puoi eseguire una query utilizzando Sintassi GoogleSQL, come se era una tabella BigQuery standard. Ad esempio, SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Eseguire query su tabelle esterne temporanee

È utile eseguire query su un'origine dati esterna utilizzando una tabella temporanea per query una tantum ad hoc su dati esterni o per l'estrazione, la trasformazione e il caricamento (ETL) i processi di machine learning.

Per eseguire una query su un'origine dati esterna senza creare una tabella permanente, fornisci una tabella definizione della tabella temporanea e poi utilizzarla in un comando o in una chiamata per eseguire una query sulla tabella temporanea. Puoi fornire la definizione della tabella in uno dei seguenti modi modi:

Il file di definizione della tabella o lo schema fornito vengono utilizzati per creare la tabella esterna temporanea. e la query viene eseguita sulla tabella esterna temporanea.

Quando utilizzi una tabella esterna temporanea, non ne crei una in una delle set di dati BigQuery. Poiché la tabella non è archiviata in modo permanente in un set di dati, non possono essere condivisi con altri.

Puoi creare ed eseguire query su una tabella temporanea collegata a un'origine dati esterna mediante lo strumento a riga di comando bq, l'API o le librerie client.

bq

Per eseguire query su una tabella temporanea collegata a un'origine dati esterna, puoi utilizzare Comando bq query con Flag --external_table_definition. Quando usi lo strumento a riga di comando bq per eseguire query su una tabella temporanea collegata a un origine dati, puoi identificare lo schema della tabella utilizzando:

(Facoltativo) Fornisci il flag --location e imposta il valore su località.

Eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando una tabella di definizione, inserisci il seguente comando.

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

Sostituisci quanto segue:

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

Ad esempio, il comando seguente crea ed esegue una query su una tabella temporanea denominato sales mediante 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 query su una tabella temporanea collegata alla tua origine dati esterna utilizzando un per la definizione dello schema incorporato, inserisci il comando seguente.

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

Sostituisci quanto segue:

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

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

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

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

    I seguenti esempi mostrano valori uris validi:

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

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

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

  • QUERY: la query che stai inviando alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue una query su una tabella temporanea denominato sales collegato a un file CSV archiviato in Cloud Storage con seguente definizione di 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 JSON del file di schema, inserisci il comando seguente.

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

Sostituisci quanto segue:

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

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

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

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

    I seguenti esempi mostrano valori uris validi:

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

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

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

  • QUERY: la query che stai inviando alla tabella temporanea.

Ad esempio, il comando seguente crea ed esegue una query su una tabella temporanea denominato sales e collegato a un file CSV archiviato in Cloud Storage utilizzando /tmp/sales_schema.json file di schema.

  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, segui questi passaggi:

  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 jobs.query metodo da eseguire la query in modo sincrono, passando l'oggetto Job.

Java

Prima di provare questo esempio, segui le istruzioni per la configurazione di Java nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Java BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Prima di provare questo esempio, segui le istruzioni per la configurazione di Node.js nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Node.js BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Prima di provare questo esempio, segui le istruzioni per la configurazione di Python nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.

Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client. 

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

Esegui una query sulla pseudocolonna _FILE_NAME

Le tabelle basate su origini dati esterne forniscono una pseudocolonna denominata _FILE_NAME. Questo contiene il percorso completo del file a cui appartiene la riga. Questa colonna è disponibile solo per le tabelle che fanno riferimento a dati esterni archiviati in Cloud Storage, Google Drive, Amazon S3 e Archiviazione BLOB di Azure.

Il nome della colonna _FILE_NAME è riservato, il che significa che non puoi creare una colonna con lo stesso nome in qualsiasi tabella. Per selezionare il valore di _FILE_NAME, devi utilizzare un alias. La seguente query di esempio dimostra la selezione di _FILE_NAME assegnando l'alias fn alla pseudocolonna. 

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

Sostituisci quanto segue:

  • PROJECT_ID è un ID progetto valido (questo flag non è obbligatorio se utilizzi Cloud Shell o se imposti un progetto predefinito in Google Cloud CLI)
  • DATASET è il nome del set di dati in cui è archiviato l'elemento esterno permanente tavola
  • TABLE_NAME è il nome della tabella esterna permanente

Quando la query ha un predicato di filtro nella pseudocolonna _FILE_NAME, BigQuery tenta di saltare la lettura dei file che non soddisfano il filtro. Simile consigli per eseguire query su tabelle partizionate per data di importazione mediante pseudocolonne . si applicano quando si creano predicati di query con la pseudocolonna _FILE_NAME.

Passaggi successivi