Eseguire query sui dati di Cloud Storage nelle tabelle esterne

Questo documento descrive come eseguire query sui dati archiviati in una 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 di avere i seguenti ruoli:

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

A seconda delle tue autorizzazioni, puoi concedere questi ruoli a te stesso o chiedere all'amministratore di concederteli. Per saperne di più sulla concessione dei ruoli, consulta Visualizzazione dei ruoli assegnabili sulle risorse.

Per vedere quali sono esattamente le autorizzazioni BigQuery richieste per eseguire query sulle tabelle esterne, espandi la sezione Autorizzazioni obbligatorie:

Autorizzazioni obbligatorie

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

Eseguire query sulle tabelle esterne permanenti

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

Eseguire query sulle tabelle esterne temporanee

L'esecuzione di query su un'origine dati esterna utilizzando una tabella temporanea è utile per query ad hoc una tantum sui dati esterni o per processi di estrazione, trasformazione e caricamento (ETL) processi.

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

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

Quando utilizzi una tabella esterna temporanea, non crei una tabella in uno dei tuoi set di dati BigQuery. Poiché la tabella non è archiviata in modo permanente in un set di dati, non può essere condivisa con altri.

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

bq

Esegui query su una tabella temporanea collegata a un'origine dati esterna utilizzando il bq query comando con il --external_table_definition flag. 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:

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

Per eseguire 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 località. 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 sulla macchina locale.
  • QUERY: la query che stai inviando alla tabella temporanea.

Ad esempio, il seguente comando 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 query su una tabella temporanea collegata all'origine dati esterna utilizzando una definizione dello schema in linea, 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 località. 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 della 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 saperne di più, consulta Supporto dei caratteri jolly per gli URI di 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 fanno riferimento a più file, tutti questi file devono condividere uno schema compatibile.

    Per saperne di più sull'utilizzo degli URI di Cloud Storage in BigQuery, consulta Percorso delle risorse di Cloud Storage.

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

Ad esempio, il seguente comando crea ed esegue 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 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 località. 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 di schema JSON sulla macchina locale.
  • SOURCE_FORMAT: il formato dell'origine dati esterna, ad esempio CSV.

  • BUCKET_PATH: il percorso del bucket Cloud Storage che contiene i dati della 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 saperne di più, consulta Supporto dei caratteri jolly per gli URI di 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 fanno riferimento a più file, tutti questi file devono condividere uno schema compatibile.

    Per saperne di più sull'utilizzo degli URI di Cloud Storage in BigQuery, consulta Percorso delle risorse di Cloud Storage.

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

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

  1. Crea un oggetto Job.
  2. Compila la sezione configuration dell'oggetto Job con un JobConfiguration oggetto.
  3. Compila la sezione query dell'oggetto JobConfiguration con un JobConfigurationQuery oggetto.
  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

Prima di provare questo esempio, segui le istruzioni di configurazione Java nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell' API Java di BigQuery.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, 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 di configurazione Node.js nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery.Node.js

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, 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 di configurazione di Python nella guida rapida di BigQuery per l'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'APIPython di BigQuery.

Per eseguire l'autenticazione in BigQuery, configura le Credenziali predefinite dell'applicazione. Per saperne di più, 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)))

Eseguire query sulla pseudo-colonna _FILE_NAME

Le tabelle basate su origini dati esterne forniscono una pseudo-colonna denominata _FILE_NAME. Questa colonna 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 questo nome in nessuna delle tue tabelle. Per selezionare il valore di _FILE_NAME, devi utilizzare un alias. La seguente query di esempio mostra la selezione di _FILE_NAME assegnando l'alias fn alla pseudo-colonna. 

  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 nella Google Cloud CLI)
  • DATASET è il nome del set di dati che memorizza la tabella esterna permanente tabella
  • TABLE_NAME è il nome della tabella esterna permanente

Quando la query ha un predicato di filtro sulla pseudo-colonna _FILE_NAME, BigQuery tenta di saltare la lettura dei file che non soddisfano il filtro. Quando crei predicati di query con la _FILE_NAME pseudo-colonna, si applicano consigli simili a quelli per l'esecuzione di query sulle tabelle partizionate per data di importazione utilizzando le pseudo-colonne .

Ottimizzare le query sulle tabelle esterne

Valuta la possibilità di abilitare Rapid Cache quando esegui query sui dati di Cloud Storage con tabelle esterne. La cache rapida fornisce una cache di lettura zonale basata su SSD per i bucket Cloud Storage, che può potenzialmente migliorare le prestazioni delle query e ridurre i costi delle query quando esegui query sulle tabelle esterne. Per saperne di più, consulta Ottimizzare le query sulle tabelle esterne di Cloud Storage.

Passaggi successivi