Esegui query sui dati di Cloud Storage in tabelle esterne
Questo documento descrive come eseguire query sui dati archiviati in una tabella esterna di Cloud Storage.
Prima di iniziare
Assicurati di avere una tabella esterna di Cloud Storage.
Ruoli obbligatori
Per eseguire query sulle tabelle esterne di Cloud Storage, assicurati di disporre dei seguenti ruoli:
- Visualizzatore dati BigQuery (
roles/bigquery.dataViewer
) - Utente BigQuery (
roles/bigquery.user
) - Visualizzatore oggetti Storage (
roles/storage.objectViewer
)
A seconda delle tue autorizzazioni, puoi concedere questi ruoli a te stesso o chiedere all'amministratore di concederli. Per ulteriori informazioni sulla concessione dei ruoli, consulta Visualizzazione dei ruoli assegnabili sulle risorse.
Per visualizzare le autorizzazioni BigQuery esatte necessarie per eseguire query sulle tabelle esterne, espandi la sezione Autorizzazioni richieste:
Autorizzazioni obbligatorie
bigquery.jobs.create
bigquery.readsessions.create
(Obbligatorio solo se stai leggendo i dati con l'API BigQuery Storage Read)bigquery.tables.get
bigquery.tables.getData
Potresti essere in grado di ottenere queste autorizzazioni anche con i ruoli personalizzati o altri ruoli predefiniti.
Esegui query su tabelle esterne permanenti
Dopo aver creato una tabella esterna di Cloud Storage, puoi eseguire query utilizzando la sintassi GoogleSQL, come se fosse una tabella BigQuery standard. Ad esempio, SELECT field1, field2
FROM mydataset.my_cloud_storage_table;
.
Esegui query su tabelle esterne temporanee
L'esecuzione di query su un'origine dati esterna utilizzando una tabella temporanea è utile per le query una tantum ad hoc su dati esterni o per i processi di estrazione, trasformazione e caricamento (ETL).
Per eseguire query su un'origine dati esterna senza creare una tabella permanente, fornisci una definizione per la tabella temporanea e poi utilizzala in un comando o in una chiamata per eseguire query sulla tabella temporanea. Puoi fornire la definizione della tabella in uno dei seguenti modi:
- Un file di definizione della tabella
- Una definizione di schema in linea
- Un file di schema JSON
Il file di definizione della tabella o lo schema fornito viene utilizzato per creare la tabella esterna temporanea, dopodiché la query viene eseguita nella tabella esterna temporanea.
Quando utilizzi una tabella esterna temporanea, non ne crei una in uno dei tuoi set di dati BigQuery. Poiché la tabella non è archiviata in modo permanente in un set di dati, non può essere condivisa con altri.
Puoi creare ed eseguire query su una tabella temporanea collegata a un'origine dati esterna utilizzando lo strumento a riga di comando bq, l'API o le librerie client.
bq
Esegui una query su una tabella temporanea collegata a un'origine dati esterna utilizzando il comando bq query
con il flag --external_table_definition
.
Quando utilizzi lo strumento a riga di comando bq per eseguire una query su una tabella temporanea 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)
(Facoltativo) Fornisci il flag --location
e imposta il valore sulla tua
località.
Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando un file di definizione della tabella, inserisci il seguente comando.
bq --location=LOCATION query \ --external_table_definition=TABLE::DEFINITION_FILE \ 'QUERY'
Sostituisci quanto segue:
LOCATION
: il nome della tua località. Il flag--location
è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag suasia-northeast1
. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.TABLE
: il nome della tabella temporanea che stai creando.DEFINITION_FILE
: il percorso del file di definizione della tabella sulla tua macchina locale.QUERY
: la query che stai inviando alla tabella temporanea.
Ad esempio, il seguente comando crea ed esegue query su una tabella temporanea denominata sales
utilizzando un file di definizione della tabella denominato sales_def
.
bq query \
--external_table_definition=sales::sales_def \
'SELECT
Region,
Total_sales
FROM
sales'
Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando una definizione di schema in linea, inserisci il seguente comando.
bq --location=LOCATION query \ --external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=BUCKET_PATH \ 'QUERY'
Sostituisci quanto segue:
LOCATION
: il nome della tua località. Il flag--location
è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag suasia-northeast1
. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.TABLE
: il nome della tabella temporanea che stai creando.SCHEMA
: la definizione dello schema incorporato nel formatofield:data_type,field:data_type
.SOURCE_FORMAT
: il formato dell'origine dati esterna, ad esempioCSV
.BUCKET_PATH
: il percorso del bucket Cloud Storage che contiene i dati per la tabella, nel formatogs://bucket_name/[folder_name/]file_pattern
.Puoi selezionare più file dal bucket specificando un carattere jolly asterisco (
*
) infile_pattern
. Ad esempio:gs://mybucket/file00*.parquet
. Per ulteriori informazioni, consulta Supporto dei caratteri jolly per gli URI di Cloud Storage.Puoi specificare più bucket per l'opzione
uris
fornendo più percorsi.I seguenti esempi mostrano valori
uris
validi:gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
,gs://bucket1/path1/*
Quando specifichi valori
uris
che hanno come target più file, tutti questi file devono condividere uno schema compatibile.Per ulteriori informazioni sull'utilizzo degli URI Cloud Storage in BigQuery, consulta Percorso risorsa di Cloud Storage.
QUERY
: la query che stai inviando alla tabella temporanea.
Ad esempio, il comando seguente crea ed esegue query su una tabella temporanea denominata sales
collegata a un file CSV archiviato in Cloud Storage con la seguente definizione di schema: Region:STRING,Quarter:STRING,Total_sales:INTEGER
.
bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
Region,
Total_sales
FROM
sales'
Per eseguire una query su una tabella temporanea collegata all'origine dati esterna utilizzando un file di schema JSON, inserisci il comando seguente.
bq --location=LOCATION query \ --external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \ 'QUERY'
Sostituisci quanto segue:
LOCATION
: il nome della tua località. Il flag--location
è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag suasia-northeast1
. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.SCHEMA_FILE
: il percorso del file di schema JSON sulla tua macchina locale.SOURCE_FORMAT
: il formato dell'origine dati esterna, ad esempioCSV
.BUCKET_PATH
: il percorso del bucket Cloud Storage che contiene i dati per la tabella, nel formatogs://bucket_name/[folder_name/]file_pattern
.Puoi selezionare più file dal bucket specificando un carattere jolly asterisco (
*
) infile_pattern
. Ad esempio:gs://mybucket/file00*.parquet
. Per ulteriori informazioni, consulta Supporto dei caratteri jolly per gli URI di Cloud Storage.Puoi specificare più bucket per l'opzione
uris
fornendo più percorsi.I seguenti esempi mostrano valori
uris
validi:gs://bucket/path1/myfile.csv
gs://bucket/path1/*.parquet
gs://bucket/path1/file1*
,gs://bucket1/path1/*
Quando specifichi valori
uris
che hanno come target più file, tutti questi file devono condividere uno schema compatibile.Per ulteriori informazioni sull'utilizzo degli URI Cloud Storage in BigQuery, consulta Percorso risorsa di Cloud Storage.
QUERY
: la query che stai inviando alla tabella temporanea.
Ad esempio, il seguente comando crea ed esegue query su una tabella temporanea denominata sales
collegata a un file CSV archiviato in Cloud Storage utilizzando il file di schema /tmp/sales_schema.json
.
bq query \ --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \ 'SELECT Region, Total_sales FROM sales'
API
Per eseguire una query utilizzando l'API:
- Crea un oggetto
Job
. - Completa la sezione
configuration
dell'oggettoJob
con un oggettoJobConfiguration
. - Completa la sezione
query
dell'oggettoJobConfiguration
con un oggettoJobConfigurationQuery
. - Completa la sezione
tableDefinitions
dell'oggettoJobConfigurationQuery
con un oggettoExternalDataConfiguration
. - Chiama il metodo
jobs.insert
per eseguire la query in modo asincrono oppure il metodojobs.query
per eseguirla in modo sincrono, passando l'oggettoJob
.
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.
Node.js
Prima di provare questo esempio, segui le istruzioni di configurazione di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
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.
Esegui una query sulla 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, Google Drive, Amazon S3 e Azure Blob Storage.
Il nome della colonna _FILE_NAME
è riservato, il che significa che non puoi creare una colonna con questo nome in nessuna delle tue tabelle. Per selezionare il valore di _FILE_NAME
, devi utilizzare
un alias. La seguente query di esempio mostra la selezione di _FILE_NAME
assegnando l'alias fn
alla pseudo colonna.
bq query \
--project_id=PROJECT_ID \
--use_legacy_sql=false \
'SELECT
name,
_FILE_NAME AS fn
FROM
`DATASET.TABLE_NAME`
WHERE
name contains "Alex"'
Sostituisci quanto segue:
-
PROJECT_ID
è un ID progetto valido (questo flag non è obbligatorio se utilizzi Cloud Shell o se imposti un progetto predefinito in Google Cloud CLI) -
DATASET
è il nome del set di dati in cui è archiviata la tabella esterna permanente -
TABLE_NAME
è il nome della tabella esterna permanente
Quando la query ha un predicato di filtro nella pseudo-colonna _FILE_NAME
, BigQuery cerca di saltare la lettura dei file che non soddisfano il filtro. Suggerimenti
simili per
eseguire query su tabelle partizionate per data di importazione
utilizzando pseudo-colonne
si applicano quando si creano predicati di query con la pseudo-colonna _FILE_NAME
.
Passaggi successivi
- Scopri di più sull'utilizzo di SQL in BigQuery.
- Scopri di più sulle tabelle esterne.
- Scopri di più sulle quote di BigQuery.