Esegui una query sui dati di Amazon S3

Questo documento descrive come eseguire query sui dati archiviati in Amazon Simple Storage Service (Amazon S3) creando una tabella BigLake. Una tabella BigLake ti consente di utilizzare la delega di accesso per eseguire query sui dati in Amazon S3. La delega di accesso disaccoppia l'accesso alla tabella BigLake dall'accesso al datastore sottostante.

Prima di iniziare

Assicurati di avere una connessione per accedere ai dati di Amazon S3.

Ruolo richiesto

Per ottenere le autorizzazioni necessarie per creare una tabella esterna, chiedi all'amministratore di concederti il ruolo IAM Amministratore BigQuery (roles/bigquery.admin) nel tuo set di dati. Per ulteriori informazioni sulla concessione dei ruoli, consulta Gestire l'accesso.

Questo ruolo predefinito contiene le autorizzazioni necessarie per creare una tabella esterna. Per visualizzare le autorizzazioni esatte necessarie, espandi la sezione Autorizzazioni richieste:

Autorizzazioni obbligatorie

• bigquery.tables.create
• bigquery.tables.getData
• bigquery.jobs.create
• bigquery.connections.delegate

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

crea un set di dati

Prima di creare una tabella esterna, devi creare un set di dati nella regione aws-us-east-1. Seleziona una delle seguenti opzioni:

Console

1. Vai alla pagina BigQuery.

   Vai a BigQuery

2. Nel riquadro Explorer, seleziona il progetto in cui vuoi creare il set di dati.
3. Espandi l'opzione Visualizza azioni e fai clic su Crea set di dati.
4. Nella pagina Crea set di dati, specifica i seguenti dettagli:
1. In ID set di dati inserisci un nome univoco per il set di dati.
2. In Località dei dati scegli aws-us-east-1 .
3. (Facoltativo) Per eliminare automaticamente le tabelle, seleziona la casella di controllo Abilita scadenza della tabella e imposta l'Età massima predefinita della tabella in giorni. I dati in Amazon S3 non vengono eliminati alla scadenza della tabella.
4. Facoltativo: espandi la sezione Opzioni avanzate e seleziona le seguenti opzioni:
   1. Se vuoi utilizzare una chiave di crittografia gestita dal cliente, seleziona l'opzione Chiave di crittografia gestita dal cliente (CMEK). Per impostazione predefinita, BigQuery cripta i contenuti inattivi inattivi dei clienti utilizzando una chiave gestita da Google.
   2. Se vuoi utilizzare le regole di confronto predefinite, seleziona l'opzione Attiva regole di confronto predefinite.
5. Fai clic su Crea set di dati.

SQL

Utilizza l'istruzione DDL CREATE SCHEMA:

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

   Vai a BigQuery

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

   CREATE SCHEMA mydataset
   OPTIONS (
     location = 'aws-us-east-1');

3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire query, vedi Esecuzione di query interattive.

bq

In un ambiente a riga di comando, crea un set di dati con il comando bq mk:

bq --location=LOCATION mk \
    --dataset \
PROJECT_ID:DATASET_NAME

Il parametro --project_id sostituisce il progetto predefinito.

Sostituisci quanto segue:

• LOCATION: la località del tuo set di dati

  Il valore valido è aws-us-east-1. Dopo aver creato un set di dati, non puoi modificarne la posizione. Puoi impostare un valore predefinito per la località utilizzando il file .bigqueryrc.

• PROJECT_ID: il tuo ID progetto

• DATASET_NAME: il nome del set di dati che vuoi creare

  Per creare un set di dati in un progetto diverso da quello predefinito, aggiungi l'ID progetto al nome del set di dati nel seguente formato: PROJECT_ID:DATASET_NAME.

Java

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

Visualizza su GitHub Feedback

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import com.google.cloud.bigquery.DatasetInfo;

// Sample to create a aws dataset
public class CreateDatasetAws {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    // Note: As of now location only supports aws-us-east-1
    String location = "aws-us-east-1";
    createDatasetAws(projectId, datasetName, location);
  }

  public static void createDatasetAws(String projectId, String datasetName, String location) {
    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();

      DatasetInfo datasetInfo =
          DatasetInfo.newBuilder(projectId, datasetName).setLocation(location).build();

      Dataset dataset = bigquery.create(datasetInfo);
      System.out.println(
          "Aws dataset created successfully :" + dataset.getDatasetId().getDataset());
    } catch (BigQueryException e) {
      System.out.println("Aws dataset was not created. \n" + e.toString());
    }
  }
}

Creare tabelle BigLake

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. Nella sezione Informazioni sul set di dati, fai clic su Crea tabella.

4. Nella sezione Origine della pagina Crea tabella, procedi nel seguente modo:

   1. In Crea tabella da, seleziona Amazon S3.
   2. In Seleziona percorso S3, inserisci un URI che rimandi ai dati di Amazon S3 nel formato s3://BUCKET_NAME/PATH. Sostituisci BUCKET_NAME con il nome del bucket Amazon S3. La regione del bucket deve corrispondere a quella del set di dati. Sostituisci PATH con il percorso in cui vuoi scrivere il file esportato. Può contenere un carattere jolly *.
   3. In Formato file, seleziona il formato dei dati in Amazon S3. I formati supportati sono AVRO, PARQUET, ORC, CSV e JSONL (JSON delimitato da nuova riga).

5. Facoltativo: se i dati in Amazon S3 utilizzano il partizionamento hive, procedi nel seguente modo:

   1. Seleziona la casella di controllo Partizione dei dati di origine.
   2. Nel campo Seleziona prefisso URI di origine, inserisci il prefisso. Il prefisso URI è la parte dell'URI visualizzata prima della codifica della partizione. Ad esempio, s3://s3-bucket/path/.
   3. (Facoltativo) Per richiedere un filtro di partizione per tutte le query per questa tabella, seleziona la casella di controllo Richiedi filtro di partizione. Un filtro di partizionamento può ridurre i costi e migliorare le prestazioni. Per ulteriori informazioni, consulta la pagina Impostare i requisiti di filtro per le partizioni.
   4. Seleziona una modalità di inferenza della partizione. Se selezioni Fornisci il mio, inserisci le informazioni sullo schema per le chiavi di partizione.

6. Nella sezione Destinazione della pagina Crea tabella, specifica i seguenti dettagli:

   1. In Set di dati, scegli il set di dati appropriato.
   2. Nel campo Tabella, inserisci il nome della tabella.
   3. Verifica che Tipo di tabella sia impostato su Tabella esterna.
   4. In ID connessione, scegli l'ID connessione appropriato dal menu a discesa. Per informazioni sulle connessioni, vedi Connettersi ad Amazon S3.

7. Fai clic su Crea tabella.

Nota: non è necessario specificare lo schema della tabella perché viene rilevato automaticamente dal file di origine.

SQL

Per creare una tabella esterna, utilizza l'istruzione CREATE EXTERNAL TABLE:

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

   Vai a BigQuery

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

   CREATE EXTERNAL TABLE DATASET_NAME.TABLE_NAME
     WITH CONNECTION `AWS_LOCATION.CONNECTION_NAME`
     OPTIONS (
       format = "DATA_FORMAT",
       uris = ["S3_URI"]);

   Sostituisci quanto segue:

   • DATASET_NAME: il nome del set di dati che hai creato
   • TABLE_NAME: il nome che vuoi assegnare a questa tabella
   • AWS_LOCATION: una località AWS in Google Cloud (ad esempio "aws-us-east-1")
   • CONNECTION_NAME: il nome della connessione che hai creato
   • DATA_FORMAT: uno qualsiasi dei formati federati BigQuery (come AVRO o CSV).
   • S3_URI: un URI che rimanda ai dati di Amazon S3 (ad esempio, s3://bucket/path)

3. Fai clic su Esegui.

Per ulteriori informazioni su come eseguire query, vedi Esecuzione di query interattive.

Esempio:

CREATE EXTERNAL TABLE awsdataset.awstable
  WITH CONNECTION `aws-us-east-1.s3-read-connection`
  OPTIONS (format="CSV", uris=["s3://s3-bucket/path/file.csv"]);

bq

Crea un file di definizione della tabella:

bq mkdef  \
--source_format=DATA_FORMAT \
--connection_id=AWS_LOCATION.CONNECTION_NAME \
S3_URI > table_def

Sostituisci quanto segue:

• DATA_FORMAT: uno qualsiasi dei formati federati BigQuery (ad esempio AVRO o CSV).
• S3_URI: un URI che rimanda ai dati di Amazon S3 (ad esempio, s3://bucket/path).
• AWS_LOCATION: una località AWS in Google Cloud (ad esempio aws-us-east-1).
• CONNECTION_NAME: il nome della connessione che hai creato.

Nota: per eseguire l'override del progetto predefinito, utilizza il parametro --project_id=PROJECT_ID. Sostituisci PROJECT_ID con l'ID del tuo progetto Cloud.

Successivamente, crea la tabella esterna:

bq mk --external_table_definition=table_def DATASET_NAME.TABLE_NAME

Sostituisci quanto segue:

• DATASET_NAME: il nome del set di dati che hai creato.
• TABLE_NAME: il nome che vuoi assegnare a questa tabella.

Ad esempio, il seguente comando crea una nuova tabella esterna, awsdataset.awstable, che può eseguire query sui tuoi dati Amazon S3 archiviati nel percorso s3://s3-bucket/path/file.csv e che hanno una connessione di lettura nella posizione aws-us-east-1:

bq mkdef  \
--autodetect \
--source_format=CSV \
--connection_id=aws-us-east-1.s3-read-connection \
s3://s3-bucket/path/file.csv > table_def

bq mk --external_table_definition=table_def awsdataset.awstable

Partizionamento Hive

Se i dati in Amazon S3 utilizzano il partizionamento hive, crea il file di definizione della tabella nel seguente modo:

bq mkdef  \
--source_format=DATA_FORMAT \
--connection_id=AWS_LOCATION.CONNECTION_NAME \
--hive_partitioning_mode=HIVE_PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=HIVE_PARTITIONING_URI_PRFEFIX \
"S3_URI" > table_def

Sostituisci quanto segue:

• HIVE_PARTITIONING_MODE: la modalità di rilevamento dello schema di partizione.
• HIVE_PARTITIONING_URI_PRFEFIX: il prefisso URI, la parte dell'URI visualizzata prima della codifica della partizione. Ad esempio: s3://s3-bucket/path/.

API

Chiama il metodo API tables.inserttables.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.

Specifica la proprietà connectionId per identificare la connessione da utilizzare per la connessione ad Amazon S3.

Java

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

Visualizza su GitHub Feedback

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.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create an external aws table
public class CreateExternalTableAws {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String connectionId = "MY_CONNECTION_ID";
    String sourceUri = "s3://your-bucket-name/";
    CsvOptions options = CsvOptions.newBuilder().setSkipLeadingRows(1).build();
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    ExternalTableDefinition externalTableDefinition =
        ExternalTableDefinition.newBuilder(sourceUri, options)
            .setConnectionId(connectionId)
            .setSchema(schema)
            .build();
    createExternalTableAws(projectId, datasetName, tableName, externalTableDefinition);
  }

  public static void createExternalTableAws(
      String projectId,
      String datasetName,
      String tableName,
      ExternalTableDefinition externalTableDefinition) {
    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();

      TableId tableId = TableId.of(projectId, datasetName, tableName);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, externalTableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Aws external table created successfully");

      // Clean up
      bigquery.delete(TableId.of(projectId, datasetName, tableName));
    } catch (BigQueryException e) {
      System.out.println("Aws external was not created." + e.toString());
    }
  }
}

Esegui una query sulla tabella

BigQuery Omni consente di eseguire query sulla tabella esterna come qualsiasi tabella BigQuery. La dimensione massima dei risultati per le query interattive è 20 GiB di byte logici (anteprima). Per ulteriori informazioni, consulta le limitazioni di BigQuery Omni. Se il risultato della query è superiore a 20 GiB, ti consigliamo di esportarlo in Amazon S3. I risultati delle query memorizzati nella cache vengono archiviati in una tabella temporanea di BigQuery.

Per eseguire una query, utilizza SQL o Java.

SQL

Per eseguire una query sulla tabella:

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

   Vai a BigQuery

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

   SELECT * FROM DATASET_NAME.TABLE_NAME;

   Sostituisci quanto segue:

   • DATASET_NAME: il nome del set di dati che hai creato
   • TABLE_NAME: il nome della tabella che hai creato.

   • Fai clic su Esegui.

Per ulteriori informazioni su come eseguire query, vedi Esecuzione di query interattive.

Java

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

Visualizza su GitHub Feedback

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.DatasetId;
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 aws s3 using a permanent table
public class QueryExternalTableAws {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String externalTableName = "MY_EXTERNAL_TABLE_NAME";
    // Query to find states starting with 'W'
    String query =
        String.format(
            "SELECT * FROM s%.%s.%s WHERE name LIKE 'W%%'",
            projectId, datasetName, externalTableName);
    queryExternalTableAws(query);
  }

  public static void queryExternalTableAws(String query) throws InterruptedException {
    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();

      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 aws external permanent table performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Visualizza i metadati della risorsa con INFORMATION_SCHEMA

Anteprima

Questa funzionalità è soggetta ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici dei servizi. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

Quando esegui query sulle viste JOBS_BY_*, JOBS_TIMELINE_BY_* e RESERVATION* BigQuery INFORMATION_SCHEMA, devi specificare che la località di elaborazione della query è us-east4 (Nord Virginia) che si trova nella regione della tabella. Il job non riesce se la località non è specificata. Per tutte le altre tabelle di sistema, specificare la località del job di query è facoltativo.

Per informazioni sulle tabelle di sistema supportate da BigQuery Omni, consulta Limitazioni.

Per eseguire una query sulle tabelle di sistema JOBS_* e RESERVATION*, specifica la località di elaborazione come segue:

Console

1. Vai alla pagina BigQuery.

   Vai a BigQuery

2. Se la scheda Editor non è visibile, fai clic su Crea nuova query.

3. Fai clic su Altro > Impostazioni query. Si apre la finestra di dialogo Impostazioni query.

   

4. Nella finestra di dialogo Impostazioni query, per Impostazioni aggiuntive > Località dei dati, seleziona us-east4 (Nord Virginia).

5. Seleziona i campi rimanenti e fai clic su Salva.

bq

Utilizza il flag --location per impostare la località di elaborazione del job su us-east4.

Esempio

bq query --use_legacy_sql=false --location=us-east4 \
"SELECT * FROM region-aws-us-east-1.INFORMATION_SCHEMA.JOBS limit 10;"

API

Se stai eseguendo job in modo programmatico, assicurati di aver impostato l'argomento location su us-east4.

Passaggi successivi

• Scopri di più su BigQuery Omni.
• Utilizza il BigQuery Omni con lab AWS.
• Scopri di più sulle tabelle BigLake.
• Scopri come esportare i risultati delle query in Amazon S3.
• Scopri come configurare i Controlli di servizio VPC per BigQuery Omni.