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 che per i file condivisi. Per ulteriori informazioni su Drive, consulta Formazione e guida di 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 disporre dell'autorizzazione per creare la tabella.
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 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 ambito OAuth aggiuntivo. Per eseguire l'autenticazione in BigQuery e abilitare l'accesso a Drive, segui questi passaggi:
Console
Segui i passaggi dell'autenticazione basata sul web quando crei una tabella esterna nella console Google Cloud. Quando ti viene richiesto, fai clic su Consenti per concedere agli strumenti client di BigQuery l'accesso a Drive.
gcloud
Nella console Google Cloud, 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.
Inserisci il comando seguente per assicurarti di avere la versione più recente di Google Cloud CLI.
gcloud components update
Inserisci il seguente comando per eseguire l'autenticazione con Drive.
gcloud auth login --enable-gdrive-access
API
Richiedi l'ambito OAuth per Drive appropriato oltre all'ambito per BigQuery:
- Accedi eseguendo il comando
gcloud auth login --enable-gdrive-access
. - Per ottenere il token di accesso OAuth per l'ambito Drive
utilizzato per l'API, esegui il
comando
gcloud auth print-access-token
.
Python
Configura Credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti:
Installa Google Cloud CLI, quindi initialize eseguendo questo comando:
gcloud init
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 maggiori informazioni, consulta Credenziali utente fornite mediante gcloud CLI.
Java
Configura Credenziali predefinite dell'applicazione (ADC) nel tuo ambiente locale con gli ambiti richiesti:
Installa Google Cloud CLI, quindi initialize eseguendo questo comando:
gcloud init
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 maggiori informazioni, consulta Credenziali utente fornite mediante gcloud CLI.
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, vedi Autorizzazioni e ruoli predefiniti.
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
con il metodo APItables.insert
- Utilizzo delle librerie client
Per creare una tabella esterna:
Console
- Nella console Google Cloud, apri la pagina 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:
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. I formati validi per i dati di Drive includono:
- Valori separati da virgola (CSV)
- JSON delimitato da nuova riga
- Avro
- Fogli
(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 l'Intervallo foglio non è specificato, viene utilizzato il primo foglio del file.Nella sezione Destinazione della pagina Crea tabella:
Per 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.
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, le esportazioni di 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 sullo schema:
- Abilitando 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
. - Utilizza Aggiungi campo per inserire manualmente lo schema.
- Abilitando 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:
Fai clic su Crea tabella.
Se necessario, seleziona il tuo account e fai clic su Consenti per concedere agli strumenti del client BigQuery l'accesso a Drive.
Puoi quindi eseguire una query sulla tabella come se fosse una tabella BigQuery standard, soggetta alle limitazioni delle origini dati esterne.
Al termine della query, puoi scaricare i risultati in formato CSV o JSON, salvarli come tabella o salvarli in Fogli. Per ulteriori informazioni, vedi Scaricare, salvare ed esportare dati.
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 sul tuo computer 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 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 comando seguente.
bq mk \ --external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \ DATASET.TABLE
Dove:
SCHEMA
è la definizione dello schema nel formatoFIELD:DATA_TYPE,FIELD:DATA_TYPE
.SOURCE_FORMAT
èCSV
,NEWLINE_DELIMITED_JSON
,AVRO
oGOOGLE_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 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 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 macchina locale.SOURCE_FORMAT
èCSV
,NEWLINE_DELIMITED_JSON
,AVRO
oGOOGLE_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 denominata sales
collegata a un file CSV archiviato in 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, soggetta alle limitazioni delle origini dati esterne.
Al termine della query, puoi scaricare i risultati in formato CSV o JSON, salvarli come tabella o salvarli in Fogli. Per ulteriori informazioni, vedi Scaricare, salvare ed esportare dati.
API
Crea una 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()); } } }
Esegui query su tabelle esterne
Per maggiori informazioni, consulta Eseguire query sui dati di Drive.
La pseudo-colonna _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, il che significa che non puoi
creare una colonna con questo nome in nessuna delle tue tabelle.