Crea tabelle esterne Cloud Storage

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 more_vert Azioni e fai clic su Crea tabella.

4. Nella sezione Origine, specifica i seguenti dettagli:

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

   2. Per Seleziona file dal bucket GCS o utilizza un pattern URI, sfoglia per selezionare un bucket e un 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 maggiori informazioni, vedi Supporto dei caratteri jolly per gli URI Cloud Storage.

      Il bucket Cloud Storage deve trovarsi nella stessa località del set di dati che contiene la tabella che stai creando.

   3. Per 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. Per 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 hai 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 deselezionata l'opzione Rilevamento automatico. 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.

Una volta creata la tabella permanente, puoi eseguire una query sulla tabella 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 dedurre lo schema 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 vuoi creare, ad esempio PARQUET
   • BUCKET_PATH: il percorso del bucket Cloud Storage che contiene i dati per la 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 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, consulta Percorso della risorsa Cloud Storage.

3. Fai clic su play_circle Esegui.

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

Esempi

L'esempio seguente utilizza il rilevamento automatico dello schema per creare una tabella esterna denominata sales collegata 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 del 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 comando bq mk con il flag --external_table_definition. Questo flag contiene un percorso a un file di definizione della tabella o una definizione della tabella incorporata.

Opzione 1: file di definizione della tabella

Utilizza il comando bq mkdef per creare un file di definizione della tabella, quindi passa il percorso del file al 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 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.

• DEFINITION_FILE: il percorso del file di definizione della tabella sul computer 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 di schema JSON, o 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 nel comando 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 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.

• 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 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 API tables.insert e crea un ExternalDataConfiguration nella risorsa Table che trasmetti.

Specifica la proprietà schema o imposta la proprietà autodetect su true per attivare il rilevamento automatico dello schema per le origini dati supportate.

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

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

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

Console

1. Nella Google Cloud console, vai a BigQuery.

   Vai a BigQuery

2. Nel riquadro Explorer, espandi il progetto e seleziona un set di dati.
3. Fai clic su more_vert Visualizza azioni, quindi fai clic 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. Per Seleziona file dal bucket Cloud Storage, inserisci il percorso della cartella Cloud Storage utilizzando i caratteri jolly. Ad esempio: my_bucket/my_files*. Il bucket Cloud Storage deve trovarsi nella stessa posizione del set di dati che contiene la tabella che vuoi creare, aggiungere o sovrascrivere.
3. Nell'elenco Formato file, seleziona il tipo di file.
4. Seleziona la casella di controllo Partizionamento dei dati di origine e poi, in Seleziona il prefisso URI di origine, inserisci il prefisso URI Cloud Storage. Ad esempio, gs://my_bucket/my_files.
5. Nella sezione Modalità di inferenza delle partizioni, seleziona una delle seguenti opzioni:
   • Deduci automaticamente i tipi: imposta la modalità di rilevamento dello schema di partizionamento su AUTO.
   • Tutte le colonne sono stringhe: imposta la modalità di rilevamento dello schema di partizionamento su STRINGS.
   • Fornisci il mio: imposta la modalità di rilevamento dello schema di partizionamento su CUSTOM e inserisci manualmente le informazioni sullo schema per le chiavi di partizionamento. Per saperne di più, vedi Fornire uno schema di chiavi di partizione personalizzato.
6. (Facoltativo) Per richiedere un filtro di partizionamento su tutte le query per questa tabella, seleziona la casella di controllo Richiedi filtro di partizionamento. Se il filtro di partizionamento è obbligatorio, i costi possono essere ridotti e le prestazioni migliorate. Per maggiori informazioni, consulta Richiedere filtri di predicato sulle chiavi di partizione nelle query.
5. Nella sezione Destinazione, specifica i seguenti dettagli:
   1. In Progetto, seleziona il progetto in cui vuoi creare la tabella.
   2. Per Set di dati, seleziona il set di dati in cui vuoi creare la tabella.
   3. In Tabella, inserisci il nome della tabella che vuoi creare.
   4. Per Tipo di tabella, seleziona Tabella esterna.
6. Nella sezione Schema, inserisci la definizione dello schema.
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.

L'esempio seguente 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 dell'origine dati esterna, ad esempio PARQUET
• GCS_URIS: il percorso della cartella Cloud Storage, utilizzando il formato con caratteri jolly
• GCS_URI_SHARED_PREFIX: il prefisso URI di origine senza il carattere jolly
• BOOLEAN: se richiedere un filtro dei predicati al momento della query. Questo flag è facoltativo. Il valore predefinito è false.

L'esempio seguente utilizza tipi e chiavi di partizione Hive personalizzati elencandoli 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 seguono lo stesso ordine nel percorso della cartella Cloud Storage, nel formato di:

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 di partizionamento Hive. Se il percorso esterno è gs://bucket/path/field_1=first/field_2=1/data.parquet, le colonne di partizione vengono rilevate 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 partizionamento. Questo esempio 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

Innanzitutto, utilizza il comando bq mkdef per creare 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 Hive. Utilizza uno dei seguenti valori:
  • AUTO: rileva automaticamente i nomi e i tipi delle chiavi.
  • STRINGS: converte 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 URI di origine.
• BOOLEAN: specifica se richiedere 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 con caratteri jolly.
• DEFINITION_FILE: il percorso del file di definizione della tabella sul computer locale.

Se PARTITIONING_MODE è CUSTOM, includi lo schema della chiave di partizionamento nel prefisso 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 comando bq mk per creare 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 nel formato field:data_type,field:data_type,.... Per utilizzare il rilevamento automatico dello schema, ometti questo argomento.

Esempi

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

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 il partizionamento 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 nel seguente modo: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Per forzare l'utilizzo di un filtro predicato al momento della query, imposta il campo hivePartitioningOptions.requirePartitionFilter su true.

Java

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

SQL

Utilizza l'istruzione DDL CREATE OR REPLACE EXTERNAL TABLE per aggiornare una tabella:

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

   Vai a BigQuery

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

   CREATE OR REPLACE EXTERNAL TABLE
     `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
     WITH CONNECTION {`REGION.CONNECTION_ID` | DEFAULT}
     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 da utilizzare

     Per utilizzare una connessione predefinita, specifica DEFAULT anziché la stringa di connessione contenente REGION.CONNECTION_ID.

   • TABLE_FORMAT: il formato utilizzato dalla tabella

     Non puoi modificare questa impostazione durante l'aggiornamento della tabella.

   • BUCKET_PATH: il percorso del bucket Cloud Storage che contiene i dati per la 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 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, 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 li utilizzi

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

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

     Per attivare la memorizzazione nella cache dei metadati, specifica un valore letterale di intervallo compreso tra 30 minuti e 7 giorni. Ad esempio, specifica INTERVAL 4 HOUR per un intervallo di obsolescenza 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 più vecchi, l'operazione recupera i metadati da Cloud Storage.

   • CACHE_MODE: specifica se la cache dei metadati viene aggiornata automaticamente o manualmente

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

     Imposta su AUTOMATIC per aggiornare la cache dei metadati a un intervallo definito dal sistema, in genere tra 30 e 60 minuti.

     Imposta MANUAL se vuoi aggiornare la cache dei metadati in base a una pianificazione che determini. 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.

3. Fai clic su play_circle Esegui.

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

bq

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

1. Genera una definizione di 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 la connessione
   • CONNECTION_ID: il nome della connessione da utilizzare
   • TABLE_FORMAT: il formato utilizzato dalla tabella. Non puoi modificare questa impostazione durante l'aggiornamento della tabella.

   • CACHE_MODE: specifica se la cache dei metadati viene aggiornata automaticamente o manualmente. Per ulteriori informazioni sulle considerazioni relative alla memorizzazione nella cache dei metadati, consulta Memorizzazione nella cache dei metadati per migliorare il rendimento.

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

     Imposta MANUAL se vuoi aggiornare la cache dei metadati in base a una pianificazione che determini. 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 che contiene i dati per la tabella esterna, 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, 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, consulta Percorso della risorsa 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 sulla tabella e quanto devono essere aggiornati i metadati memorizzati nella cache affinché l'operazione li utilizzi. Per ulteriori informazioni sulla memorizzazione nella cache dei metadati, consulta Memorizzazione nella cache dei metadati per le prestazioni.

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

     Per attivare la memorizzazione nella cache dei metadati, specifica un valore di intervallo compreso tra 30 minuti e 7 giorni utilizzando il formato Y-M D H:M:S descritto nella documentazione del tipo di dati INTERVAL. Ad esempio, specifica 0-0 0 4:0:0 per un intervallo di obsolescenza 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 più vecchi, l'operazione recupera i metadati da Cloud Storage.

   • DEFINITION_FILE: il nome del file di definizione della tabella 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