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 sia per i file personali di Drive sia per i file condivisi. Per ulteriori informazioni su Drive, consulta la formazione e la guida di Drive.

Puoi creare tabelle esterne sui 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.

Recuperare 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 file di Drive.

Effettuare l'autenticazione e attivare l'accesso a Drive

L'accesso ai dati ospitati in Drive richiede un ambito OAuth aggiuntivo. Per autenticarti a BigQuery e attivare l'accesso a Drive, segui questi passaggi:

Console

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

gcloud

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

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

   gcloud components update
   

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

   gcloud auth login --enable-gdrive-access
   

di Gemini Advanced.

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'ambito OAuth per Drive appropriato oltre all'ambito per BigQuery:

1. Accedi eseguendo 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 le credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti nel seguente modo:

   1. Installa Google Cloud CLI, quindi inizializzalo eseguendo il seguente 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, consulta Credenziali utente fornite utilizzando l'interfaccia a riga di comando gcloud.

Java

1. Crea un ID client OAuth.

2. Configura le credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti nel seguente modo:

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

      gcloud init

   2. Crea le credenziali di autenticazione locale 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, consulta Credenziali utente fornite utilizzando l'interfaccia a riga di comando gcloud.

Ruoli obbligatori

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

Ciascuno dei seguenti ruoli di Identity and Access Management predefiniti 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 sui ruoli e sulle autorizzazioni di Identity and Access Management in BigQuery, consulta Ruoli e autorizzazioni predefiniti.

Creare 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 un ExternalDataConfiguration quando utilizzi il metodo API tables.insert
• 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:

   • In Crea tabella da, seleziona Drive.

   • Nel campo Seleziona URI dell'unità, inserisci l'URI dell'unità. 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
     di Gemini Advanced.
     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 nome del foglio o un valore 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 campo Nome tabella inserisci il nome della tabella che 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 di file vengono recuperati automaticamente dall'origine autodescrittiva e i dati di Google Cloud.
   • Inserisci le informazioni dello schema manualmente:
     • Attivando Modifica come testo e inserendo lo schema della tabella come array JSON. 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.
     • Utilizzando 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 una query Tabella BigQuery, soggetta alle limitazioni su origini dati esterne.

Al termine della query, puoi scaricare i risultati in formato CSV o JSON salvare i risultati in una tabella o salvarli in Fogli. Per saperne di più, consulta Scaricare, salvare ed esportare 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 su dalla tua macchina locale)
• Una definizione di schema in linea
• Un file dello schema JSON (salvato nella 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 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 comando seguente 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 contenente 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 è il tuo URI di Drive.
• DATASET è il nome del set di dati contenente la tabella.
• TABLE è il nome della tabella che stai creando.

Se il file di definizione della tabella contiene la 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 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 una tabella BigQuery standard, soggetta ai limitazioni su origini dati esterne.

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

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, consulta Eseguire query sui dati di Drive.

La pseudocolonna _FILE_NAME

Le tabelle basate su origini dati esterne forniscono una pseudocolonna 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, quindi non puoi creare una colonna con questo nome in una qualsiasi delle tabelle.