Crea tabelle esterne di Google Drive

Console

Segui i passaggi di autenticazione basata sul web quando crei una tabella esterna nella console Cloud de Confiance . Quando ti viene richiesto, fai clic su Consenti per concedere a BigQuery Client Tools l'accesso a Drive.

gcloud

1. Installa Google Cloud CLI, quindi accedi a gcloud CLI con la tua identità federata. Dopo aver eseguito l'accesso, inizializza Google Cloud CLI eseguendo il comando seguente:

   gcloud init

2. Inserisci il seguente comando per assicurarti di avere l'ultima versione di Google Cloud CLI.

   gcloud components update
   

3. Inserisci il seguente comando per eseguire l'autenticazione con Drive.

   gcloud auth login --enable-gdrive-access
   

API

Richiedi l'ambito OAuth appropriato per Drive oltre all'ambito per BigQuery:

1. Accedi eseguendo il comando gcloud auth login --enable-gdrive-access.
2. Ottieni il token di accesso OAuth con l'ambito di Drive utilizzato per l'API eseguendo il comando gcloud auth print-access-token.

Python

1. Crea un ID client OAuth.

2. Configura le Credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti seguendo questi passaggi:

   1. Installa Google Cloud CLI, quindi inizializzala eseguendo il seguente comando:

      gcloud init

   2. Crea credenziali di autenticazione locali per il tuo Account Google:

      gcloud auth application-default login \
          --client-id-file=CLIENT_ID_FILE \
          --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform

      Sostituisci CLIENT_ID_FILE con il file contenente l'ID client OAuth.

      Per saperne di più, consulta Credenziali utente fornite utilizzando gcloud CLI.

Java

1. Crea un ID client OAuth.

2. Configura le Credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti seguendo questi passaggi:

   1. Installa Google Cloud CLI, quindi inizializzala eseguendo il seguente comando:

      gcloud init

   2. Crea credenziali di autenticazione locali per il tuo Account Google:

      gcloud auth application-default login \
          --client-id-file=CLIENT_ID_FILE \
          --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform

      Sostituisci CLIENT_ID_FILE con il file contenente l'ID client OAuth.

      Per saperne di più, consulta Credenziali utente fornite utilizzando gcloud CLI.

Console

1. Nella console Cloud de Confiance , apri la pagina BigQuery.

   Vai a BigQuery

2. Nel riquadro a sinistra, fai clic su explore Spazio di esplorazione:

   

   Se non vedi il riquadro a sinistra, fai clic su last_page Espandi riquadro sinistro per aprirlo.

3. Nel riquadro Explorer, espandi il progetto, fai clic su Set di dati, quindi seleziona un set di dati.

4. Nel riquadro dei dettagli, fai clic su Crea tabella add_box.

5. Nella pagina Crea tabella, nella sezione Origine:

   • In Crea tabella da, seleziona Drive.

   • Nel campo Seleziona URI Drive, inserisci l'URI Drive. Tieni presente che i caratteri jolly non sono supportati per gli URI di Drive.

   • In Formato file, seleziona il formato dei dati. I formati validi per i dati di Drive includono:

     • Valori separati da virgola (CSV)
     • JSON delimitato da nuova riga
     • Avro
     • Fogli

6. (Facoltativo) Se scegli Fogli, nella casella Intervallo foglio (facoltativo), specifica il foglio e l'intervallo di celle su cui eseguire la query. Puoi specificare il nome di un foglio o sheet_name!top_left_cell_id:bottom_right_cell_id per un intervallo di celle, ad esempio "Foglio1!A1:B20". Se non viene specificato Intervallo foglio, viene utilizzato il primo foglio del file.

7. Nella pagina Crea tabella, nella sezione Destinazione:

   • In Nome set di dati, scegli il set di dati appropriato e nel campo Nome tabella inserisci il nome della tabella che stai creando in BigQuery.

     

   • Verifica che Tipo di tabella sia impostato su Tabella esterna.

8. Nella sezione Schema, inserisci la definizione dello schema.

   • Per i file JSON o CSV, puoi selezionare l'opzione Rilevamento automatico per attivare il rilevamento automatico dello schema. Il rilevamento automatico non è disponibile per le esportazioni di Datastore, Firestore e i file Avro. Le informazioni sullo schema per questi tipi di file vengono recuperate automaticamente dai dati di origine autodescrittivi.
   • Inserisci manualmente le informazioni dello schema:
     • Attivando Modifica come testo e inserendo lo schema della tabella come array JSON. Nota: puoi visualizzare lo schema di una tabella esistente in formato JSON inserendo il seguente comando nello strumento a riga di comando bq: bq show --format=prettyjson DATASET.TABLE.
     • Utilizzando Aggiungi campo per inserire manualmente lo schema.

9. Fai clic su Crea tabella.

10. Se necessario, seleziona il tuo account e poi fai clic su Consenti per concedere agli strumenti client di BigQuery l'accesso a Drive.

Puoi quindi eseguire una query sulla tabella come se fosse una tabella BigQuery standard, nel rispetto delle limitazioni sulle origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON, salvarli come tabella o salvarli in Fogli. Per saperne di più, consulta Scarica, salva ed esporta i dati.

bq

Puoi creare una tabella nello strumento a riga di comando bq utilizzando il comando bq mk. Quando utilizzi lo strumento a riga di comando bq per creare una tabella collegata a un'origine dati esterna, puoi identificare lo schema della tabella utilizzando:

• Un file di definizione della tabella (archiviato sulla tua macchina locale)
• Una definizione dello schema in linea
• Un file di schema JSON (archiviato sul tuo computer locale)

Per creare una tabella permanente collegata all'origine dati di Drive utilizzando un file di definizione della tabella, inserisci il comando seguente.

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

Dove:

• DEFINITION_FILE è il percorso del file di definizione della tabella sulla tua macchina locale.
• DATASET è il nome del set di dati che contiene la tabella.
• TABLE è il nome della tabella che stai creando.

Ad esempio, il seguente comando crea una tabella permanente denominata mytable utilizzando un file di definizione della tabella denominato mytable_def.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

Per creare una tabella permanente collegata all'origine dati esterna utilizzando una definizione di schema incorporato, inserisci il seguente comando.

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Dove:

• SCHEMA è la definizione dello schema nel formato FIELD:DATA_TYPE,FIELD:DATA_TYPE.
• SOURCE_FORMAT è CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
• DRIVE_URI è l'URI di Drive.
• DATASET è il nome del set di dati che contiene la tabella.
• TABLE è il nome della tabella che stai creando.

Ad esempio, il seguente comando crea una tabella permanente denominata sales collegata a un file di Fogli archiviato su Drive con la seguente definizione di schema: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

Per creare una tabella permanente collegata all'origine dati esterna utilizzando un file di schema JSON, inserisci il seguente comando.

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

Dove:

• SCHEMA_FILE è il percorso del file di schema JSON sulla tua macchina locale.
• SOURCE_FORMAT è CSV, NEWLINE_DELIMITED_JSON, AVRO o GOOGLE_SHEETS.
• DRIVE_URI è l'URI di Drive.
• DATASET è il nome del set di dati che contiene la tabella.
• TABLE è il nome della tabella che stai creando.

Se il file di definizione della tabella contiene una configurazione specifica per Fogli, puoi saltare le righe iniziali e specificare un intervallo di fogli definito.

L'esempio seguente crea una tabella denominata sales collegata a un file CSV archiviato su Drive utilizzando il file dello schema /tmp/sales_schema.json.

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

Dopo aver creato la tabella permanente, puoi eseguire una query sulla tabella come se fosse una tabella BigQuery standard, nel rispetto delle limitazioni sulle origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON, salvarli come tabella o salvarli in Fogli. Per saperne di più, consulta Scarica, salva ed esporta i dati.

API

Crea un ExternalDataConfiguration quando utilizzi il metodo API tables.insert. Specifica la proprietà schema o imposta la proprietà autodetect su true per attivare il rilevamento automatico dello schema per le origini dati supportate.

Python

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an API request.

# Example query to find states starting with "W".
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)

results = client.query_and_wait(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(results)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

Java

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
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.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
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;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  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 =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    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);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}