crea tabelle esterne di Cloud Storage

BigQuery supporta l'esecuzione di query sui dati di Cloud Storage in seguenti formati:

  • Valori separati da virgola (CSV)
  • JSON (delimitato da nuova riga)
  • Avro
  • ORC
  • Parquet
  • Esportazioni di Datastore
  • Esportazioni di Firestore

BigQuery supporta l'esecuzione di query sui dati di Cloud Storage da questi classi di archiviazione:

  • Standard
  • Nearline
  • Coldline
  • Archivia

Per eseguire query su una tabella esterna di Cloud Storage, devi disporre delle autorizzazioni sia sulla tabella esterna che sui file Cloud Storage. I nostri suggerimenti viene utilizzata una tabella BigLake se possibile. Le tabelle BigLake forniscono la delega dell'accesso, pertanto per eseguire query sui dati di Cloud Storage sono necessarie solo le autorizzazioni per la tabella BigLake.

Assicurati di considerare la posizione. del set di dati e del bucket Cloud Storage quando esegui query sui dati di archiviazione ideale in Cloud Storage.

Prima di iniziare

Concedi ruoli IAM (Identity and Access Management) che concedono agli utenti le autorizzazioni necessarie per eseguire ogni attività in questo documento. Le autorizzazioni richieste per eseguire un'attività (se presenti) sono elencate nella sezione "Autorizzazioni richieste" dell'attività.

Ruoli obbligatori

Per creare una tabella esterna, è necessario bigquery.tables.create Autorizzazione Identity and Access Management (IAM) BigQuery.

Ciascuno dei seguenti ruoli predefiniti di Identity and Access Management include questa autorizzazione:

  • Editor dati BigQuery (roles/bigquery.dataEditor)
  • BigQuery Data Owner (roles/bigquery.dataOwner)
  • Amministratore BigQuery (roles/bigquery.admin)

Devi disporre anche delle autorizzazioni seguenti per accedere all'app Cloud Storage che contiene i tuoi dati:

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (obbligatorio se utilizzi un carattere jolly per l'URI)

Il ruolo Identity and Access Management predefinito Amministratore archiviazione (roles/storage.admin) di Cloud Storage include queste autorizzazioni.

Se non sei un'entità in uno di questi ruoli, chiedi all'amministratore per concederti l'accesso o per creare la tabella esterna per te.

Per ulteriori informazioni su ruoli e autorizzazioni di Identity and Access Management in BigQuery, vedi Ruoli e ruoli predefiniti autorizzazioni.

Ambiti di accesso per le istanze Compute Engine

Se da un'istanza Compute Engine devi eseguire query su una tabella esterna collegata a un'origine Cloud Storage, l'istanza deve disporre almeno dell'ambito di accesso in sola lettura di Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only).

Gli ambiti controllano l'accesso dell'istanza Compute Engine ai prodotti Google Cloud, tra cui Cloud Storage. Le applicazioni in esecuzione sull'istanza utilizzano l'account di servizio associato all'istanza per chiamare le API Google Cloud.

Se configuri un'istanza Compute Engine per l'esecuzione come account di servizio Compute Engine predefinito, all'istanza viene concesso per impostazione predefinita una serie di ambiti predefiniti, incluso l'ambito https://www.googleapis.com/auth/devstorage.read_only.

Se invece configuri l'istanza con un account di servizio personalizzato, assicurati di grantare esplicitamente all'istanza l'ambito https://www.googleapis.com/auth/devstorage.read_only.

Per informazioni sull'applicazione degli ambiti a un'istanza Compute Engine, consulta Modificare l'account di servizio e gli ambiti di accesso per un'istanza. Per ulteriori informazioni sugli account di servizio Compute Engine, consulta Account di servizio.

Creare tabelle esterne su dati non partizionati

Per creare una tabella permanente collegata all'origine dati esterna:

Seleziona una delle seguenti opzioni:

Console

  1. Vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nel riquadro Explorer, espandi il progetto e seleziona un set di dati.

  3. Espandi l'opzione Azioni e fai clic su Crea tabella.

  4. Nella sezione Origine, specifica i seguenti dettagli:

    1. In Crea tabella da, seleziona Google Cloud Storage.

    2. In Seleziona un file dal bucket GCS o usa un pattern URI, vai a seleziona il bucket e il file da utilizzare oppure digita il percorso nel formato gs://bucket_name/[folder_name/]file_name.

      Non puoi specificare più URI nella console Google Cloud, ma puoi selezionare più file specificando un carattere jolly asterisco (*). Ad esempio, gs://mybucket/file_name*. Per ulteriori informazioni, consulta Supporto dei caratteri jolly per gli URI di Cloud Storage.

      Il bucket Cloud Storage deve trovarsi nella stessa posizione del set di dati contenente la tabella che stai creando.

    3. In Formato file, seleziona il formato corrispondente al tuo file.

  5. Nella sezione Destinazione, specifica i seguenti dettagli:

    1. In Progetto, scegli il progetto in cui creare la tabella.

    2. In Set di dati, scegli il set di dati in cui creare la tabella.

    3. In Tabella, inserisci il nome della tabella che stai creando.

    4. Per Tipo di tabella, seleziona Tabella esterna.

  6. Nella sezione Schema, puoi attivare il rilevamento automatico dello schema o specificare manualmente uno schema se disponi di un file di origine. Se non hai un file di origine, devi specificare manualmente uno schema.

    • Per attivare il rilevamento automatico dello schema, seleziona l'opzione Rilevamento automatico.

    • Per specificare manualmente uno schema, lascia l'opzione Rilevamento automatico non selezionata. Attiva Modifica come testo e inserisci lo schema della tabella come Array JSON.

  7. Per ignorare le righe con valori di colonna aggiuntivi che non corrispondono allo schema, espandi la sezione Opzioni avanzate e seleziona Valori sconosciuti.

  8. Fai clic su Crea tabella.

Dopo aver creato la tabella permanente, puoi eseguire una query su di essa come se fosse una tabella BigQuery nativa. Al termine della query, puoi esportare i risultati come file CSV o JSON, salvarli come tabella o in Fogli Google.

SQL

Puoi creare una tabella esterna permanente eseguendo l'istruzione DDL CREATE EXTERNAL TABLE. Puoi specificare lo schema in modo esplicito o utilizzare il rilevamento automatico dello schema per dedurlo dai dati esterni.

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di query, inserisci la seguente istruzione:

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]]
    );

    Sostituisci quanto segue:

    • PROJECT_ID: il nome del tuo progetto in cui vuoi creare la tabella, ad esempio myproject
    • DATASET: il nome del set di dati BigQuery in cui vuoi creare la tabella, ad esempio mydataset
    • EXTERNAL_TABLE_NAME: il nome della tabella che vuoi creare, ad esempio mytable
    • TABLE_FORMAT: il formato della tabella che che vuoi creare, ad esempio PARQUET
    • BUCKET_PATH: il percorso bucket Cloud Storage che contiene i dati per una tabella esterna, nel formato ['gs://bucket_name/[folder_name/]file_name'].

      Puoi selezionare più file dal bucket specificando un carattere jolly asterisco (*) nel percorso. Ad esempio, ['gs://mybucket/file_name*']. Per ulteriori informazioni, consulta Supporto dei caratteri jolly per gli URI di 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/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      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.

  3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

Esempi

L'esempio seguente utilizza il rilevamento automatico dello schema per creare una tabella esterna denominato sales collegato a un file CSV archiviato in Cloud Storage:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

L'esempio successivo specifica uno schema in modo esplicito e salta la prima riga in il file CSV:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

Per creare una tabella esterna, utilizza il metodo Comando bq mk con --external_table_definition flag. Questo flag contiene un percorso a un file di definizione della tabella o una definizione della tabella in linea.

Opzione 1: file di definizione della tabella

Utilizza la bq mkdef per creare un file di definizione della tabella, quindi passa il percorso del file il comando bq mk come segue:

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Sostituisci quanto segue:

  • SOURCE_FORMAT: il formato dell'origine dati esterna. Ad esempio, CSV.

  • BUCKET_PATH: il percorso del bucket Cloud Storage contenente i dati della tabella, 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 maggiori informazioni le 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.

  • DEFINITION_FILE: il percorso del file di definizione della tabella sulla tua macchina locale.

  • DATASET_NAME: il nome del set di dati che contiene la tabella.

  • TABLE_NAME: il nome della tabella che stai creando.

  • SCHEMA: specifica un percorso a un file dello schema JSON oppure specifica lo schema nel formato field:data_type,field:data_type,....

Esempio:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Per utilizzare il rilevamento automatico dello schema, imposta il flag --autodetect=true nella mkdef e ometti lo schema:

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

Opzione 2: definizione della tabella in linea

Anziché creare un file di definizione della tabella, puoi passare la definizione della tabella direttamente al comando bq mk:

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Sostituisci quanto segue:

  • SOURCE_FORMAT: il formato dell'origine dati esterna

    Ad esempio, CSV.

  • BUCKET_PATH: il percorso del bucket Cloud Storage contenente 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 maggiori 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/*

    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.

  • DATASET_NAME: il nome del set di dati che contiene la tabella.

  • TABLE_NAME: il nome della tabella che stai creando.

  • SCHEMA: specifica un percorso a un file dello schema JSON oppure specifica lo schema nel formato field:data_type,field:data_type,.... Per utilizzare il rilevamento automatico dello schema, ometti questo argomento.

Esempio:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Chiama il metodo tables.insert API e crea un'istanza ExternalDataConfiguration nella risorsa Table che trasmetti.

Specifica la proprietà schema o imposta la proprietà Proprietà autodetect in true per attivare il rilevamento automatico dello schema per e origini dati supportate.

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 la documentazione di riferimento dell'API BigQuery Java.

Per autenticarti a BigQuery, configura le 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.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    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.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, 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();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

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

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

      System.out.println("Query on external permanent 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 riportate nella guida rapida all'utilizzo di BigQuery con le 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 queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

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

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

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

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  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 Python riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Python.

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

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

Creare tabelle esterne su dati partizionati

Puoi creare una tabella esterna per i dati partizionati di Hive che risiedono in di archiviazione ideale in Cloud Storage. Dopo aver creato una tabella partizionata esternamente, non possono cambiare la chiave di partizione. Devi ricreare la tabella per modificare di partizione.

Per creare una tabella esterna per i dati partizionati di Hive, scegli una delle le seguenti opzioni:

Console

  1. Nella console Google Cloud, vai a BigQuery.

    Vai a BigQuery

  2. Nel riquadro Explorer, espandi il progetto e seleziona un set di dati.
  3. Fai clic su Visualizza azioni e poi su Crea tabella. Si aprirà il riquadro Crea tabella.
  4. Nella sezione Origine, specifica i seguenti dettagli:
    1. Per Crea tabella da, seleziona Google Cloud Storage.
    2. In Seleziona file dal bucket Cloud Storage, inserisci il percorso della cartella Cloud Storage utilizzando i carattere jolly. Ad esempio: my_bucket/my_files*. Il bucket Cloud Storage deve trovarsi stessa posizione del set di dati che contiene la tabella da creare, aggiungere o sovrascrivere.
    3. Seleziona il tipo di file dall'elenco Formato file.
    4. Seleziona la casella di controllo Partizionamento dei dati di origine e poi, in Seleziona il prefisso dell'URI di origine, inserisci il prefisso dell'URI di Cloud Storage. Ad esempio, gs://my_bucket/my_files.
    5. Nella sezione Modalità di inferenza della partizione, seleziona una delle seguenti opzioni opzioni:
      • Induce automaticamente i tipi: imposta la modalità di rilevamento dello schema di partizione a AUTO.
      • Tutte le colonne sono stringhe: imposta la modalità di rilevamento dello schema della partizione su STRINGS.
      • Fornisci il mio: imposta la modalità di rilevamento dello schema della partizione su CUSTOM e inserisci manualmente le informazioni sullo schema per le chiavi di partizione. Per ulteriori informazioni, consulta Fornire uno schema personalizzato per le chiavi di partizione.
    6. (Facoltativo) Per richiedere un filtro di partizione su tutte le query per questa tabella, Seleziona la casella di controllo Richiedi filtro di partizione. Richiesta di una partizione può ridurre i costi e migliorare il rendimento. Per ulteriori informazioni, consulta Requisiti per i filtri predefiniti sulle chiavi di partizione nelle query.
  5. Nella sezione Destinazione, specifica i seguenti dettagli:
    1. In Progetto, seleziona il progetto in cui creare il tabella.
    2. In Set di dati, seleziona il set di dati in cui vuoi creare la tabella.
    3. In Tabella, inserisci il nome della tabella da creare.
    4. In Tipo di tabella, seleziona Tabella esterna.
  6. Nella sezione Schema, inserisci lo schema. definizione di Kubernetes.
  7. Per attivare il rilevamento automatico dello schema: Seleziona Rilevamento automatico.
  8. Per ignorare le righe con valori di colonna aggiuntivi che non corrispondono allo schema, espandi la sezione Opzioni avanzate e seleziona Valori sconosciuti.
  9. Fai clic su Crea tabella.

SQL

Utilizza l'istruzione DDL CREATE EXTERNAL TABLE.

Il seguente esempio utilizza il rilevamento automatico delle chiavi di partizione Hive:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Sostituisci quanto segue:

  • SOURCE_FORMAT: il formato dei dati esterni origine dati, ad esempio PARQUET
  • GCS_URIS: il percorso della cartella Cloud Storage, utilizzando il formato jolly
  • GCS_URI_SHARED_PREFIX: il prefisso dell'URI di origine senza il carattere jolly
  • BOOLEAN: se richiedere un filtro dei predicati in l'ora della query. Questo flag è facoltativo. Il valore predefinito è false.

L'esempio seguente utilizza chiavi e tipi di partizione Hive personalizzati elencando nella clausola WITH PARTITION COLUMNS:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Sostituisci quanto segue:

  • PARTITION_COLUMN_LIST: un elenco di colonne che segue lo stesso ordine nel percorso della cartella Cloud Storage, nel formato:
KEY1 TYPE1, KEY2 TYPE2

L'esempio seguente crea una tabella partizionata esternamente. Utilizza il rilevamento automatico dello schema per rilevare sia lo schema del file sia il layout della suddivisione in celle. Se il percorso esterno è gs://bucket/path/field_1=first/field_2=1/data.parquet, le colonne di partizione vengono rilevati come field_1 (STRING) e field_2 (INT64).

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

L'esempio seguente crea una tabella partizionata esternamente specificando esplicitamente le colonne di partizione. In questo esempio si presuppone che il percorso del file esterno abbia il pattern gs://bucket/path/field_1=first/field_2=1/data.parquet.

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

Per prima cosa, utilizza Comando bq mkdef per crea un file di definizione della tabella:

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

Sostituisci quanto segue:

  • SOURCE_FORMAT: il formato dell'origine dati esterna. Ad esempio, CSV.
  • PARTITIONING_MODE: la modalità di partizionamento di Hive. Utilizza uno dei seguenti i seguenti valori:
    • AUTO: rileva automaticamente i nomi e i tipi di chiavi.
    • STRINGS: converti automaticamente i nomi delle chiavi in stringhe.
    • CUSTOM: codifica lo schema della chiave nel prefisso dell'URI di origine.
  • GCS_URI_SHARED_PREFIX: il prefisso dell'URI di origine.
  • BOOLEAN: specifica se è necessario un filtro predicato al momento della query. Questo flag è facoltativo. Il valore predefinito è false.
  • GCS_URIS: il percorso della cartella Cloud Storage, utilizzando il formato carattere jolly.
  • DEFINITION_FILE: il percorso file di definizione della tabella sul tuo in una macchina virtuale.

Se PARTITIONING_MODE è CUSTOM, includi lo schema della chiave di partizione nel prefisso dell'URI di origine, utilizzando il seguente formato:

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Dopo aver creato il file di definizione della tabella, utilizza il metodo Comando bq mk per crea la tabella esterna:

bq mk --external_table_definition=DEFINITION_FILE \
DATASET_NAME.TABLE_NAME \
SCHEMA

Sostituisci quanto segue:

  • DEFINITION_FILE: il percorso del file di definizione della tabella.
  • DATASET_NAME: il nome del set di dati che contiene la tabella.
  • TABLE_NAME: il nome della tabella che stai creando.
  • SCHEMA: specifica un percorso a un File di schema JSON, o specifica lo schema nella forma field:data_type,field:data_type,.... Per utilizzare il rilevamento automatico dello schema, ometti questo argomento.

Esempi

L'esempio seguente utilizza la AUTOmodalità di partizionamento Hive:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

L'esempio seguente utilizza la modalità di partizionamento Hive STRING:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

L'esempio seguente utilizza la modalità di partizionamento Hive CUSTOM:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Per impostare la partizione Hive utilizzando l'API BigQuery, includi un oggetto hivePartitioningOptions nell'oggetto ExternalDataConfiguration quando crei il file di definizione della tabella.

Se imposti il campo hivePartitioningOptions.mode su CUSTOM, devi codificare lo schema della chiave di partizione nel campo hivePartitioningOptions.sourceUriPrefix come segue: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Per applicare l'utilizzo di un filtro per i predicati in fase di query, imposta il valore hivePartitioningOptions.requirePartitionFilter su true.

Java

Prima di provare questo esempio, segui le istruzioni di configurazione Java riportate nella guida rapida all'utilizzo di BigQuery con le librerie client. Per ulteriori informazioni, consulta la documentazione di riferimento dell'API BigQuery Java.

Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per saperne di più, consulta Configurare 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.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    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();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

Esegui query sulle tabelle esterne

Per ulteriori informazioni, vedi Eseguire query sui dati di Cloud Storage in tabelle esterne.

Esegui l'upgrade delle tabelle esterne a BigLake

Puoi eseguire l'upgrade delle tabelle basate su Cloud Storage alle tabelle BigLake associando la tabella esterna a una connessione. Se vuoi utilizzare memorizzazione nella cache dei metadati con la tabella BigLake, puoi specificare le impostazioni per questo contemporaneamente. Per visualizzare i dettagli della tabella, come il formato e l'URI di origine, consulta Ottenere informazioni sulle tabelle.

Per aggiornare una tabella esterna in una tabella BigLake, seleziona una delle seguenti opzioni:

SQL

Utilizza la CREATE OR REPLACE EXTERNAL TABLE Istruzione DDL per aggiornare una tabella:

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor query, inserisci la seguente istruzione:

    CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION `REGION.CONNECTION_ID`
  OPTIONS(
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

    Sostituisci quanto segue:

    • PROJECT_ID: il nome del progetto che contiene la tabella
    • DATASET: il nome del set di dati che contiene la tabella
    • EXTERNAL_TABLE_NAME: il nome della tabella
    • REGION: la regione che contiene la connessione
    • CONNECTION_ID: il nome della connessione per utilizzare
    • TABLE_FORMAT: il formato utilizzato dalla tabella

      Non puoi modificarlo quando aggiorni la tabella.

    • BUCKET_PATH: il percorso del bucket Cloud Storage contenente i dati della tabella esterna, nel formato ['gs://bucket_name/[folder_name/]file_name'].

      Puoi selezionare più file dal bucket specificando un carattere jolly asterisco (*) nel percorso. Ad esempio, ['gs://mybucket/file_name*']. Per ulteriori informazioni, 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/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      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 BigQuery, consulta Percorso della risorsa Cloud Storage.

    • STALENESS_INTERVAL: specifica se i metadati memorizzati nella cache vengono utilizzati dalle operazioni sulla tabella e quanto devono essere aggiornati i metadati memorizzati nella cache affinché l'operazione possa utilizzarli

      Per ulteriori informazioni sulle considerazioni relative alla memorizzazione nella cache dei metadati, consulta Memorizzazione nella cache dei metadati per il rendimento.

      Per disattivare la memorizzazione nella cache dei metadati, specifica 0. Questa è l'impostazione predefinita.

      Per abilitare la memorizzazione nella cache dei metadati, specifica valore letterale intervallo compreso tra 30 minuti e 7 giorni. Ad esempio, specifica INTERVAL 4 HOUR per un intervallo di inattività di 4 ore. Con questo valore, le operazioni sulla tabella utilizzano i metadati memorizzati nella cache se è stato aggiornato nelle ultime 4 ore. Se i metadati memorizzati nella cache sono precedenti a questa data, l'operazione recupera i metadati da Cloud Storage.

    • CACHE_MODE: specifica se i metadati La cache viene aggiornata automaticamente o manualmente

      Per ulteriori informazioni su considerazioni relative alla memorizzazione nella cache dei metadati, consulta Memorizzazione nella cache dei metadati per il rendimento.

      Imposta il valore AUTOMATIC affinché la cache dei metadati venga vengono aggiornati a un intervallo definito dal sistema, solitamente compreso tra 30 e 60 minuti.

      Imposta su MANUAL se vuoi aggiornare dei metadati in base a una pianificazione determinata da te. In questo caso, puoi chiamare la procedura di sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE per aggiornare la cache.

      Devi impostare CACHE_MODE se STALENESS_INTERVAL è impostato su un valore maggiore maggiore di 0.

  3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire query, consulta Eseguire una query interattiva.

bq

Utilizza i bq mkdef e Comandi bq update per aggiornare una tabella:

  1. Genera una definizione della tabella esterna, che descrive gli aspetti della tabella da modificare:

    bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \
--source_format=TABLE_FORMAT \
--metadata_cache_mode=CACHE_MODE \
"BUCKET_PATH" > /tmp/DEFINITION_FILE

    Sostituisci quanto segue:

    • PROJECT_ID: il nome del progetto che contiene la connessione
    • REGION: la regione che contiene connessione
    • CONNECTION_ID: il nome della connessione per utilizzare
    • TABLE_FORMAT: il formato utilizzato dalla tabella. Non puoi modificarlo quando aggiorni la tabella.

    • CACHE_MODE: specifica se la cache dei metadati viene aggiornata automaticamente o manualmente. Per ulteriori informazioni considerazioni sulla memorizzazione nella cache dei metadati, Memorizzazione nella cache dei metadati per migliorare le prestazioni.

      Imposta AUTOMATIC per aggiornare la cache dei metadati in un definito dal sistema, di solito tra 30 e 60 minuti.

      Imposta su MANUAL se desideri aggiornare la cache dei metadati su una il piano di controllo da te determinato. In questo caso, puoi chiamare la procedura di sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE per aggiornare la cache.

      Devi impostare CACHE_MODE se STALENESS_INTERVAL è impostato su un valore maggiore di 0.

    • BUCKET_PATH: il percorso del bucket Cloud Storage contenente i dati della tabella externa, nel formato gs://bucket_name/[folder_name/]file_name.

      Puoi limitare i file selezionati dal bucket specificando un carattere jolly asterisco (*) nel percorso. Ad esempio, gs://mybucket/file_name*. Per maggiori informazioni le 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/*.csv
      • gs://bucket/path1/*,gs://bucket/path2/file00*

      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, vedi Percorso della risorsa di Cloud Storage.

    • DEFINITION_FILE: il nome del file di definizione della tabella che stai creando.

  2. Aggiorna la tabella utilizzando la nuova definizione della tabella esterna:

    bq update --max_staleness=STALENESS_INTERVAL \
--external_table_definition=/tmp/DEFINITION_FILE \
PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME

    Sostituisci quanto segue:

    • STALENESS_INTERVAL: specifica se I metadati memorizzati nella cache vengono utilizzati dalle operazioni e il livello di attualità dei metadati memorizzati nella cache per l'operazione per utilizzarlo. Per ulteriori informazioni sulle considerazioni relative alla memorizzazione nella cache dei metadati, consulta Memorizzazione nella cache dei metadati per il rendimento.

      Per disabilitare la memorizzazione nella cache dei metadati, specifica 0. Questa è l'impostazione predefinita.

      Per abilitare la memorizzazione nella cache dei metadati, specifica un intervallo di valori compreso tra 30 minuti e 7 giorni, utilizzando Y-M D H:M:S descritto in Tipo di dati INTERVAL documentazione. Ad esempio, specifica 0-0 0 4:0:0 per un intervallo di inattività di 4 ore. Con questo valore, le operazioni sulla tabella utilizzano i metadati memorizzati nella cache se sono stati aggiornati nelle ultime 4 ore. Se i metadati memorizzati nella cache sono precedenti a questa data, l'operazione recupera i metadati da Cloud Storage.

    • DEFINITION_FILE: il nome della tabella di definizione che hai creato o aggiornato.

    • PROJECT_ID: il nome del progetto che contiene la tabella

    • DATASET: il nome del set di dati che contiene la tabella

    • EXTERNAL_TABLE_NAME: il nome della tabella

Percorso della risorsa di Cloud Storage

Quando crei una tabella esterna basata su un'origine dati Cloud Storage, devi fornire il percorso dei dati.

Il percorso della risorsa Cloud Storage contiene il nome del bucket e (nome file). Ad esempio, se il bucket Cloud Storage è denominato mybucket e il file di dati è denominato myfile.csv, il percorso della risorsa sarà gs://mybucket/myfile.csv.

BigQuery non supporta i percorsi delle risorse Cloud Storage che includono più barre consecutive dopo la barra doppia iniziale. I nomi degli oggetti Cloud Storage possono contenere più barre consecutive ("/") caratteri. Tuttavia, BigQuery converte più istanze barre in una singola barra. Ad esempio, il seguente percorso della risorsa, sebbene valido in Cloud Storage, non funziona in BigQuery: gs://bucket/my//object//name.

Per recuperare il percorso della risorsa di Cloud Storage:

  1. Apri la console Cloud Storage.

    Console Cloud Storage

  2. Accedi alla posizione dell'oggetto (file) che contiene i dati di origine.

  3. Fai clic sul nome dell'oggetto.

    Si apre la pagina Dettagli oggetto.

  4. Copia il valore fornito nel campo URI gsutil, che inizia con gs://.

Supporto dei caratteri jolly per gli URI Cloud Storage

Se i dati sono separati in più file, puoi utilizzare un asterisco (*) carattere jolly per selezionare più file. Il carattere jolly asterisco deve seguire queste regole:

  • L'asterisco può essere visualizzato all'interno del nome dell'oggetto o alla fine dell'oggetto nome.
  • L'utilizzo di più asterischi non è supportato. Ad esempio, il percorsogs://mybucket/fed-*/temp/*.csv non è valido.
  • L'utilizzo di un asterisco con il nome del bucket non è supportato.

Esempi:

  • L'esempio seguente mostra come selezionare tutti i file di tutte le cartelle che iniziano con il prefisso gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • L'esempio seguente mostra come selezionare solo i file con estensione .csv nella cartella denominata fed-samples e in eventuali sottocartelle di fed-samples:

    gs://mybucket/fed-samples/*.csv

  • L'esempio seguente mostra come selezionare file con un modello di denominazione fed-sample*.csv nella cartella denominata fed-samples. Questo esempio non seleziona i file nelle sottocartelle di fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Quando usi lo strumento a riga di comando bq, potresti dover inserire il carattere di escape in alcune piattaforme di terze parti.

Non puoi utilizzare un asterisco jolly quando crei tabelle esterne collegate alle esportazioni di Datastore o Firestore.

Limitazioni

Per informazioni sulle limitazioni che si applicano alle tabelle esterne, consulta Limitazioni per le tabelle esterne.

Passaggi successivi