Creare tabelle esterne di Google Drive

Questo documento descrive come creare una tabella esterna sui dati archiviati su Google Drive.

BigQuery supporta le tabelle esterne su Drive personali file e file condivisi. Per ulteriori informazioni su Drive, vedi Formazione e guida per Drive.

Puoi creare tabelle esterne su file di Drive che hanno i seguenti formati:

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

Prima di iniziare

Prima di creare una tabella esterna, raccogli alcune informazioni e assicurati di disponi dell'autorizzazione per creare la tabella.

Recupera gli URI di Drive

Per creare una tabella esterna per un'origine dati Google Drive, devi fornire il parametro URI Drive. Puoi recuperare l'URI di Drive direttamente dall'URL dei tuoi dati di Drive:

Formato URI

• https://docs.google.com/spreadsheets/d/FILE_ID

  o

• https://drive.google.com/open?id=FILE_ID

dove FILE_ID è l'ID alfanumerico del tuo File di Drive.

Autenticare e abilitare l'accesso a Drive

L'accesso ai dati ospitati su Drive richiede un token OAuth aggiuntivo l'ambito di attività. Per eseguire l'autenticazione in BigQuery e abilitare l'accesso a Drive, procedi nel seguente modo: le seguenti:

Console

Segui i passaggi dell'autenticazione basata sul web quando crei un tabella esterna nella console Google Cloud. Quando Fai clic su Consenti per concedere l'accesso agli strumenti client di BigQuery. su Drive.

gcloud

1. Nella console Google Cloud, attiva Cloud Shell.

   Attiva Cloud Shell

   Nella parte inferiore della console Google Cloud viene avviata una sessione di Cloud Shell che mostra un prompt della riga di comando. Cloud Shell è un ambiente shell con Google Cloud CLI già installato e con valori già impostati per il progetto attuale. L'inizializzazione della sessione può richiedere alcuni secondi.

2. Inserisci il seguente comando per assicurarti di avere la versione più recente di con Google Cloud CLI.

   gcloud components update
   

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

   gcloud auth login --enable-gdrive-access
   

Nota: se utilizzi Cloud Shell per accedere ai tuoi dati di Drive, Non è necessario aggiornare Google Cloud CLI o eseguire l'autenticazione con Drive.

API

Richiedi l'appropriata Ambito OAuth per Drive oltre all'ambito per BigQuery:

1. Per accedere, esegui il Comando gcloud auth login --enable-gdrive-access.
2. Ottenere il token di accesso OAuth con l'ambito Drive che viene utilizzato per l'API eseguendo Comando gcloud auth print-access-token.

Python

1. Crea un ID client OAuth.

2. Configura Credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti eseguendo la seguenti:

   1. Installa Google Cloud CLI, quindi inizializzarlo eseguendo questo comando :

      gcloud init
      

   2. Crea le 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 il tuo ID client OAuth.

      Per ulteriori informazioni, vedi Credenziali utente fornite mediante gcloud CLI.

Java

1. Crea un ID client OAuth.

2. Configura Credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti eseguendo la seguenti:

   1. Installa Google Cloud CLI, quindi inizializzarlo eseguendo questo comando :

      gcloud init
      

   2. Crea le 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 il tuo ID client OAuth.

      Per ulteriori informazioni, vedi Credenziali utente fornite mediante gcloud CLI.

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)
• Proprietario dati BigQuery (roles/bigquery.dataOwner)
• Amministratore BigQuery (roles/bigquery.admin)

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.

Crea tabelle esterne

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

• Utilizzo della console Google Cloud
• Utilizzo del comando mk dello strumento a riga di comando bq
• Creazione di una ExternalDataConfiguration quando utilizzi tables.insert Metodo API
• Utilizzo delle librerie client

Per creare una tabella esterna:

Console

1. Nella console Google Cloud, apri la pagina BigQuery.

Vai a BigQuery

1. Nel riquadro Spazio di esplorazione, espandi il progetto e seleziona un set di dati.

2. Espandi Azioni e fai clic su Apri.

3. Nel riquadro dei dettagli, fai clic su Crea tabella. .

4. Nella sezione Origine della pagina Crea tabella:

   • Per 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 tuoi dati. Formati validi per i dati di Drive includono:

     • Valori separati da virgola (CSV)
     • JSON delimitato da nuova riga
     • Avro
     • Fogli
     Nota: nella console Google Cloud, scegli Avro o Datastore Backup nasconde l'opzione Rileva automaticamente perché questi file sono autodescrittive.

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

6. Nella sezione Destinazione della pagina Crea tabella:

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

     

   • Verifica che l'opzione Tipo di tabella sia impostata su Tabella esterna.

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

   • Per i file JSON o CSV, puoi selezionare l'opzione Rilevamento automatico per abilita il rilevamento automatico dello schema. Il rilevamento automatico non è disponibile per le esportazioni di Datastore. Esportazioni Firestore e file Avro. Informazioni sullo schema per questi tipi di file vengono recuperati automaticamente dall'origine autodescrittiva e i dati di Google Cloud.
   • Inserisci manualmente le informazioni sullo schema:
     • Attivazione dell'opzione Modifica come testo e inserimento dello schema della tabella come JSON un array di dati. Nota: puoi visualizzare lo schema di una tabella esistente in JSON inserendo il seguente comando nello strumento a riga di comando bq: bq show --format=prettyjson DATASET.TABLE.
     • Utilizza Aggiungi campo per inserire manualmente lo schema.

8. Fai clic su Crea tabella.

9. Se necessario, seleziona il tuo account e fai clic su Consenti per concedere Accesso agli strumenti client di BigQuery a Drive.

Puoi quindi eseguire una query sulla tabella come se fosse un modello Tabella BigQuery, soggetta alle limitazioni su origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON salva i risultati in una tabella o salvali in Fogli. Consulta Scaricare, salvare ed esportare i dati per ulteriori informazioni.

bq

Per creare una tabella nello strumento a riga di comando bq, utilizza 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 su dalla macchina locale)
• Definizione di uno schema in linea
• Un file di schema JSON (archiviato sulla macchina locale)

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

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

Dove:

• DEFINITION_FILE è il percorso del file di definizione della tabella sul tuo computer 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 un per la definizione dello schema incorporato, inserisci il comando seguente.

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 collegato a un file di Fogli archiviato su Drive con le seguenti definizione 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 JSON del file di schema, inserisci il comando seguente.

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

Dove:

• SCHEMA_FILE è il percorso del file di schema JSON sul tuo server in una macchina virtuale.
• 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 collegata sales in un file CSV archiviato su Drive utilizzando /tmp/sales_schema.json del file di schema.

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 una tabella BigQuery standard, soggetta ai limitazioni su origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON salva i risultati in una tabella o salvali in Fogli. Consulta Scaricare, salvare ed esportare i dati per ulteriori informazioni.

API

Crea una ExternalDataConfiguration quando utilizzi tables.insert API. 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());
    }
  }
}

Esegui query su tabelle esterne

Per ulteriori informazioni, vedi Eseguire query sui dati di Drive.

La pseudocolonna _FILE_NAME

Le tabelle basate su origini dati esterne forniscono una pseudocolonna denominata _FILE_NAME. Questo contiene il percorso completo del file a cui appartiene la riga. Questa colonna è disponibile solo per le tabelle che fanno riferimento a dati esterni archiviati in Cloud Storage e Google Drive.

Il nome della colonna _FILE_NAME è riservato, quindi non puoi creare una colonna con questo nome in qualsiasi tabella.