Creare tabelle esterne di Google Drive

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

BigQuery supporta tabelle esterne sia per i file di Drive personali sia per i file condivisi. Per maggiori informazioni su Drive, consulta 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 avere l'autorizzazione per crearla.

Recupera gli URI di Drive

Per creare una tabella esterna per un'origine dati Google Drive, devi fornire l'URI di Drive. Puoi recuperare l'URI di Drive direttamente dall'URL dei 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 file di Drive.

Abilita l'accesso a Drive

L'accesso ai dati ospitati all'interno di Drive richiede un ambito OAuth aggiuntivo. Anche se non è abilitato per impostazione predefinita, può essere incluso nella console Google Cloud, nello strumento a riga di comando bq o tramite l'API tramite i seguenti meccanismi:

Console

Segui i passaggi per l'autenticazione basata sul web quando crei una tabella permanente nella console Google Cloud. Quando ti viene richiesto, fai clic su Consenti per concedere a Strumenti client BigQuery l'accesso a Drive.

gcloud

Per abilitare l'accesso a Drive:

Inserisci il comando seguente per assicurarti di avere la versione più recente di Google Cloud CLI.
```
gcloud components update
```
Inserisci il seguente comando per autenticarti con Drive.
```
gcloud auth login --enable-gdrive-access
```

API

Se utilizzi l'API BigQuery, richiedi l'ambito OAuth per Drive oltre all'ambito per BigQuery.

Python

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

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/cloud-platform",
    ]
)

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

Java

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

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

GoogleCredentials credentials =
    ServiceAccountCredentials.getApplicationDefault()
        .createScoped(
            ImmutableSet.of(
                "https://www.googleapis.com/auth/bigquery",
                "https://www.googleapis.com/auth/drive"));

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

Ruoli obbligatori

Per creare una tabella esterna, devi disporre dell'autorizzazione bigquery.tables.create BigQuery Identity and Access Management (IAM).

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 di concederti l'accesso o di creare la tabella esterna per te.

Per ulteriori informazioni sui ruoli e sulle autorizzazioni di Identity and Access Management in BigQuery, consulta Ruoli e autorizzazioni predefiniti.

Crea tabelle esterne

Puoi 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 un elemento ExternalDataConfiguration quando utilizzi il metodo API tables.insert
Utilizzo delle librerie client

Per creare una tabella esterna:

Console

Nella console Google Cloud, apri la pagina BigQuery.

Vai a BigQuery

Nel riquadro Spazio di esplorazione, espandi il progetto e seleziona un set di dati.
Espandi l'opzione Azioni e fai clic su Apri.
Nel riquadro dei dettagli, fai clic su Crea tabella .
Nella sezione Origine della pagina Crea tabella:
- In Crea tabella da, seleziona Drive.
- Nel campo Seleziona URI di Drive, inserisci l'URI di Drive. Tieni presente che i caratteri jolly non sono supportati per gli URI di Drive.
- Per 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
  Nota: nella console Google Cloud, scegliendo Avro o Datastore backup, l'opzione Rileva automaticamente nasconde l'opzione Rileva automaticamente perché questi tipi di file sono autodescritti.
(Facoltativo) Se scegli Fogli, nella casella Intervallo di fogli (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 l'intervallo di fogli non è specificato, viene utilizzato il primo foglio del file.
Nella sezione Destinazione della pagina Crea tabella:
- 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.
Nella sezione Schema, inserisci la definizione di 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, le esportazioni di Firestore e i file Avro. Le informazioni sullo schema per questi tipi di file vengono recuperate automaticamente dai dati di origine autodescritti.
- Inserisci manualmente le informazioni dello schema:
  - Abilitare Modifica come testo e inserire 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.
  - Utilizza Aggiungi campo per inserire manualmente lo schema.
Fai clic su Crea tabella.
Se necessario, seleziona il tuo account e 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, in conformità alle limitazioni sulle origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON, salvarli come tabella o in Fogli. Per ulteriori informazioni, vedi Scaricare, salvare ed esportare 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 di tabella (memorizzato sulla macchina locale)
Una definizione di schema in linea
Un file di schema JSON (archiviato sulla tua macchina locale)

Per creare una tabella permanente collegata all'origine dati di Drive utilizzando un 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 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 incorporata, 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 comando seguente crea una tabella permanente denominata sales collegata a un file di Fogli archiviato in 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 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 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.

Ad esempio, il seguente comando crea una tabella denominata sales collegata a un file CSV archiviato su Drive utilizzando il file di 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, in conformità alle limitazioni sulle origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON, salvarli come tabella o in Fogli. Per ulteriori informazioni, vedi Scaricare, salvare ed esportare dati.

API

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

Python

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# 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

Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.

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 {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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 maggiori informazioni, vedi Eseguire query sui dati di Drive.

La pseudo colonna _FILE_NAME

Le tabelle basate su origini dati esterne forniscono una pseudo colonna denominata _FILE_NAME. Questa colonna 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, il che significa che non puoi creare una colonna con questo nome in nessuna delle tue tabelle.