Utilizzare le query con più dichiarazioni
Questo documento descrive come utilizzare query con più istruzioni in BigQuery, ad esempio su come scrivere query con più istruzioni, utilizza tabelle temporanee nelle query con più istruzioni, variabili di riferimento in query con più istruzioni ed eseguire il debug di query con più istruzioni.
Una query con più istruzioni è una raccolta di istruzioni SQL che puoi eseguire in un'unica richiesta. Con le query con più istruzioni puoi eseguire più istruzioni una sequenza con stato condiviso. Le query multi-istruzione possono avere effetti collaterali come l'aggiunta o la modifica dei dati della tabella.
Le query multi-istruzione sono spesso utilizzate procedure archiviate e supporto dichiarazioni nel linguaggio procedurale, che ti consentono di definire variabili e implementare il flusso di controllo.
Scrivere, eseguire e salvare query con più istruzioni
Una query con più istruzioni è composta da una o più istruzioni SQL separate da un punto e virgola. Qualsiasi istruzione SQL valida può essere utilizzata in un una query con più istruzioni. Le query con più istruzioni possono includere anche istruzioni del linguaggio procedurale, che ti consentono di utilizzare variabili o implementare il flusso di controllo con le istruzioni SQL.
Scrivi una query con più dichiarazioni
Puoi scrivere una query con più dichiarazioni in BigQuery. Le seguenti
query su più istruzioni con più query dichiara una variabile e utilizza
all'interno di un elemento IF
:
DECLARE day INT64;
SET day = (SELECT EXTRACT(DAYOFWEEK from CURRENT_DATE));
if day = 1 or day = 7 THEN
SELECT 'Weekend';
ELSE
SELECT 'Weekday';
END IF
BigQuery interpreta qualsiasi richiesta con più istruzioni come un
query con più istruzioni, a meno che le istruzioni siano costituite interamente da
CREATE TEMP FUNCTION
istruzioni seguite da una singola istruzione SELECT
.
Ad esempio, quanto segue non è considerato una query con più istruzioni:
CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);
SELECT Add(3, 4);
Esegui una query con più istruzioni
Puoi eseguire una query con più istruzioni come faresti con qualsiasi altra query nella console Google Cloud o usando lo strumento a riga di comando bq.
Eseguire una prova di una query con più istruzioni
Per stimare il numero di byte letti da una query con pi�� istruzioni, considera un
prova. Prova di
query a più istruzioni è più precisa per le query che contengono solo SELECT
istruzioni.
Le prove secche hanno una gestione speciale per i seguenti tipi di query e istruzioni:
- Istruzioni
CALL
: la prova convalida l'esistenza della procedura chiamata e ha una firma corrispondente agli argomenti forniti. I contenuti della procedura chiamata e tutte le istruzioni successive all'istruzioneCALL
non vengono convalidati. - Istruzioni DDL: la prova dry run convalida la prima istruzione DDL e poi si arresta. Tutte le istruzioni successive vengono ignorate.
- Istruzioni DML: la modalità dry run convalida l'istruzione DML, quindi continua con la convalida dichiarazioni successive. In questo caso, le stime dei byte sono basate dimensioni delle tabelle e non prendono in considerazione il risultato dell'istruzione DML.
- Istruzioni
EXECUTE IMMEDIATE
: la prova convalida l'espressione di query, ma non valuta la query dinamica stessa. Tutte le dichiarazioni che seguono L'istruzioneEXECUTE IMMEDIATE
è stata ignorata. - Query che utilizzano variabili in un filtro di partizione: la prova convalida la query iniziale e le istruzioni successive. Tuttavia, la modalità dry run non è in grado il valore di runtime delle variabili in un filtro di partizione. Questo influisce la stima dei byte letti.
- Query che utilizzano variabili nell'espressione del timestamp di una clausola
FOR SYSTEM TIME AS OF
: la simulazione utilizza i contenuti attuali della tabella e ignora la clausolaFOR SYSTEM TIME AS OF
. Questo influisce sui byte letti stimare se ci sono differenze di dimensioni tra la tabella corrente e la precedente dell'iterazione della tabella. - Istruzioni di controllo
FOR
,IF
eWHILE
: la prova si interrompe immediatamente. Le espressioni di condizione, i corpi dell'istruzione di controllo e tutte le istruzioni successive non vengono convalidate.
Le prove vengono eseguite secondo il criterio del "best effort" e il processo sottostante è soggetto per cambiare. Le prove sono soggette alle seguenti condizioni:
- Una query che completa correttamente una prova potrebbe non essere eseguita correttamente. Ad esempio, le query potrebbero non riuscire in fase di runtime per motivi non rilevate dalle prove.
- Una query che viene eseguita correttamente potrebbe non completare correttamente una prova. Ad esempio, le query potrebbero non riuscire nelle prove a causa di motivi rilevati durante l'esecuzione.
- Non è garantito che le prove di prova eseguite oggi correttamente vengano eseguite sempre per il futuro. Ad esempio, le modifiche all'implementazione dry run potrebbero rilevare errori in una query che in precedenza non erano state rilevate.
Salvare una query con più istruzioni
Per salvare una query con più dichiarazioni, consulta Utilizzare le query salvate.
Utilizzare le variabili in una query con più istruzioni
Una query con più dichiarazioni può contenere variabili create dall'utente e le variabili di sistema.
Puoi dichiarare le variabili create dall'utente, assegnare valori a e farvi riferimento in tutta la query.
Puoi fare riferimento alle variabili di sistema in una query e assegnare valori ad alcune di esse, ma, a differenza delle variabili definite dall'utente, non devi dichiararle. Le variabili di sistema sono integrate in BigQuery.
Dichiarare una variabile creata dall'utente
Devi dichiarare le variabili create dall'utente all'inizio della query con più istruzioni o all'inizio di un blocco BEGIN
. Le variabili dichiarate all'inizio della query con più istruzioni rientrano nell'ambito
per l'intera query. Le variabili dichiarate all'interno di un blocco BEGIN
hanno ambito per
bloccare il blocco. Non rientrano nell'ambito dopo l'istruzione END
corrispondente. La
la dimensione massima di una variabile è 1 MB e la dimensione massima di tutte le variabili utilizzate
in una query con più istruzioni è di 10 MB.
Puoi dichiarare una variabile
DECLARE
dichiarazione procedurale come la seguente:
DECLARE x INT64;
BEGIN
DECLARE y INT64;
-- Here you can reference x and y
END;
-- Here you can reference x, but not y
Imposta una variabile creata dall'utente
Dopo aver dichiarato una variabile creata dall'utente, puoi assegnarle un valore con
SET
dichiarazione procedurale come la seguente:
DECLARE x INT64 DEFAULT 0;
SET x = 10;
Imposta una variabile di sistema
Non sei tu a creare variabili di sistema, ma puoi eseguire l'override il valore predefinito per alcuni, come questo:
SET @@dataset_project_id = 'MyProject';
Puoi anche impostare e utilizzare in modo implicito una variabile di sistema in una query. Ad esempio, nella query seguente devi includere il progetto volta che vuoi creare una nuova tabella:
BEGIN
CREATE TABLE MyProject.MyDataset.MyTempTableA (id STRING);
CREATE TABLE MyProject.MyDataset.MyTempTableB (id STRING);
END;
Se non vuoi aggiungere più volte il progetto ai percorsi delle tabelle, puoi:
Assegna l'ID progetto del set di dati MyProject
al sistema @@dataset_project_id
nella query con più istruzioni. Questo compito rende MyProject
il progetto predefinito per il resto della query.
SET @@dataset_project_id = 'MyProject';
BEGIN
CREATE TABLE MyDataset.MyTempTableA (id STRING);
CREATE TABLE MyDataset.MyTempTableB (id STRING);
END;
Analogamente, puoi impostare la variabile di sistema @@dataset_id
in modo che assegni una
per la query. Ad esempio:
SET @@dataset_project_id = 'MyProject';
SET @@dataset_id = 'MyDataset';
BEGIN
CREATE TABLE MyTempTableA (id STRING);
CREATE TABLE MyTempTableB (id STRING);
END;
Puoi anche fare riferimento esplicitamente a variabili di sistema come @@dataset_id
in
molte parti di una query con più istruzioni. Per saperne di più, vedi
Fai riferimento a una variabile di sistema.
Fare riferimento a una variabile creata dall'utente
Dopo aver dichiarato e impostato una variabile creata dall'utente, puoi farvi riferimento in una query con più istruzioni. Se una variabile e una colonna hanno lo stesso nome, colonna ha la precedenza.
Questa operazione restituisce column x
+ column x
:
DECLARE x INT64 DEFAULT 0;
SET x = 10;
WITH Numbers AS (SELECT 50 AS x)
SELECT (x+x) AS result FROM Numbers;
+--------+
| result |
+--------+
| 100 |
+--------+
Questa operazione restituisce column y
+ variable x
:
DECLARE x INT64 DEFAULT 0;
SET x = 10;
WITH Numbers AS (SELECT 50 AS y)
SELECT (y+x) AS result FROM Numbers;
+--------+
| result |
+--------+
| 60 |
+--------+
Fare riferimento a una variabile di sistema
Puoi fare riferimento a una variabile di sistema in una query con più istruzioni.
La seguente query restituisce il fuso orario predefinito:
BEGIN
SELECT @@time_zone AS default_time_zone;
END;
+-------------------+
| default_time_zone |
+-------------------+
| UTC |
+-------------------+
Puoi utilizzare le variabili di sistema con le query DDL e DML.
Ad esempio, ecco alcuni modi per utilizzare la variabile di sistema @@time_zone
quando crei e aggiorni una tabella:
BEGIN
CREATE TEMP TABLE MyTempTable
AS SELECT @@time_zone AS default_time_zone;
END;
BEGIN
CREATE OR REPLACE TABLE MyDataset.MyTable(default_time_zone STRING)
OPTIONS (description = @@time_zone);
END;
BEGIN
UPDATE MyDataset.MyTable
SET default_time_zone = @@time_zone
WHERE TRUE;
END;
Esistono alcune posizioni in cui le variabili di sistema non possono essere utilizzate
delle query DDL e DML. Ad esempio, non puoi utilizzare una variabile di sistema come
nome del progetto, del set di dati o della tabella. Viene generato un errore quando provi
a includere la variabile di sistema @@dataset_id
in un percorso tabella:
BEGIN
CREATE TEMP TABLE @@dataset_id.MyTempTable (id STRING);
END;
Utilizzare tabelle temporanee in una query con più istruzioni
Le tabelle temporanee consentono di salvare i risultati intermedi in una tabella. Le tabelle temporanee sono gestite da BigQuery, quindi non è necessario salvarle o gestirle in un set di dati. Ti viene addebitato il costo per l'archiviazione di tabelle temporanee.
Puoi creare e fare riferimento a una tabella temporanea in una query con più istruzioni. Quando hai finito di utilizzare la tabella temporanea, puoi eliminarla manualmente per ridurre al minimo i costi di archiviazione o attendere che BigQuery lo elimini dopo 24 ore su 24.
Crea una tabella temporanea
Puoi creare una tabella temporanea per una query con più istruzioni con il
CREATE TABLE
.
Il seguente esempio crea una tabella temporanea per archiviare i risultati di una query e poi utilizza la tabella temporanea in una sottoquery:
-- Find the top 100 names from the year 2017.
CREATE TEMP TABLE top_names(name STRING)
AS
SELECT name
FROM `bigquery-public-data`.usa_names.usa_1910_current
WHERE year = 2017
ORDER BY number DESC LIMIT 100
;
-- Which names appear as words in Shakespeare's plays?
SELECT
name AS shakespeare_name
FROM top_names
WHERE name IN (
SELECT word
FROM `bigquery-public-data`.samples.shakespeare
);
A parte l'utilizzo di TEMP
o TEMPORARY
, la sintassi è identica alla
CREATE TABLE
.
Quando crei una tabella temporanea, non utilizzare un qualificatore di progetto o set di dati in il nome della tabella. La tabella viene creata automaticamente in un set di dati speciale.
Fare riferimento a una tabella temporanea
Puoi fare riferimento a una tabella temporanea per nome per la durata della tabella attuale
una query con più istruzioni. Sono incluse le tabelle temporanee create da una procedura
all'interno di una query con più dichiarazioni. Non puoi condividere tabelle temporanee. Temporaneo
risiedono in set di dati _script%
nascosti con nomi generati in modo casuale.
L'articolo Elenco dei set di dati descrive come elencare i set di dati nascosti.
Elimina tabelle temporanee
Puoi eliminare una tabella temporanea in modo esplicito prima della query con più istruzioni
viene completata utilizzando l'istruzione DROP TABLE
:
CREATE TEMP TABLE table1(x INT64); SELECT * FROM table1; -- Succeeds DROP TABLE table1; SELECT * FROM table1; -- Results in an error
Al termine di una query con più istruzioni, la tabella temporanea esiste per un massimo di 24 ore su 24.
Visualizzare i dati temporanei delle tabelle
Dopo aver creato una tabella temporanea, puoi visualizzare la struttura del tabella e i dati al suo interno. Per visualizzare la struttura e i dati della tabella, segui questi passaggi:
Nella console Google Cloud, vai alla pagina Explorer di BigQuery.
Fai clic su Cronologia query.
Scegli la query che ha creato la tabella temporanea.
Nella riga Tabella di destinazione, fai clic su Tabella temporanea.
Qualifica le tabelle temporanee con _SESSION
Quando le tabelle temporanee vengono utilizzate insieme a un set di dati predefinito, non è valido
i nomi delle tabelle si riferiscono a una tabella temporanea, se esistente, o a una tabella
del set di dati. L'eccezione è per le istruzioni CREATE TABLE
, in cui la tabella target
è considerata una tabella temporanea solo se la parola chiave TEMP
o TEMPORARY
è presente.
Considera ad esempio la seguente query con più istruzioni:
-- Create table t1 in the default dataset CREATE TABLE t1 (x INT64); -- Create temporary table t1. CREATE TEMP TABLE t1 (x INT64); -- This statement selects from the temporary table. SELECT * FROM t1; -- Drop the temporary table DROP TABLE t1; -- Now that the temporary table is dropped, this statement selects from the -- table in the default dataset. SELECT * FROM t1;
Puoi indicare esplicitamente che ti riferisci a una tabella temporanea eseguendo
che qualifica il nome della tabella con _SESSION
:
-- Create a temp table CREATE TEMP TABLE t1 (x INT64); -- Create a temp table using the `_SESSION` qualifier CREATE TEMP TABLE _SESSION.t2 (x INT64); -- Select from a temporary table using the `_SESSION` qualifier SELECT * FROM _SESSION.t1;
Se utilizzi il qualificatore _SESSION
per una query di una tabella temporanea che
non esiste, la query con più istruzioni genera un errore che indica che la tabella
inesistente. Ad esempio, se non esiste una tabella temporanea denominata t3
, la query con più istruzioni genera un errore anche se nel set di dati predefinito esiste una tabella denominata t3
.
Non puoi utilizzare _SESSION
per creare una tabella non temporanea:
CREATE TABLE _SESSION.t4 (x INT64); -- Fails
Raccogli informazioni su un job di query con più istruzioni
Un job di query con più istruzioni contiene informazioni su una query con più istruzioni eseguito. Alcune attività comuni che puoi eseguire con i dati dei job includere la restituzione dell'ultima istruzione eseguita con la query con più istruzioni che restituisce tutte le istruzioni eseguite con la query con più istruzioni.
Restituisce l'ultima istruzione eseguita
La jobs.getQueryResults
restituisce i risultati della query per l'ultima istruzione da eseguire nella
una query con più istruzioni. Se non viene eseguita alcuna istruzione, non viene eseguito alcun risultato
restituito.
Restituisce tutte le istruzioni eseguite
Per ottenere i risultati di tutte le istruzioni in un
query con più istruzioni, enumera i job figlio
e chiama jobs.getQueryResults
su ciascuno di essi.
Enumera job figlio
Le query multi-istruzione vengono eseguite in BigQuery utilizzando
jobs.insert
,
simili a qualsiasi altra query, con le query con più istruzioni specificate come
del testo della query. Quando viene eseguita una query multi-istruzione, job aggiuntivi, noti come
job figlio, vengono creati per ciascuna istruzione nella query con più istruzioni. Tu
può enumerare i job figlio di una query con più istruzioni chiamando
jobs.list
, che supera
ID job di query con più istruzioni come parametro parentJobId
.
Eseguire il debug di una query con più istruzioni
Ecco alcuni suggerimenti per il debug di query con più dichiarazioni:
Utilizza la
ASSERT
per affermare che una condizione booleana è vera.Utilizza
BEGIN...EXCEPTION...END
per individuare gli errori e visualizzare il messaggio di errore e l'analisi dello stack.Usa
SELECT FORMAT("....")
per mostrare i risultati intermedi.Quando esegui una query con più istruzioni nella console Google Cloud, puoi visualizzare l'output di ogni istruzione nella query con più istruzioni. Lo strumento a riga di comando bq Il comando "bq query" mostra anche i risultati di ogni passaggio quando esegui un una query con più istruzioni.
Nella console Google Cloud, puoi selezionare una singola istruzione all'interno del editor di query ed eseguirlo.
Autorizzazioni
L'autorizzazione per accedere a una tabella, un modello o un'altra risorsa è verificata in fase di dell'esecuzione. Se un'istruzione non viene eseguita o un'espressione non viene valutata, BigQuery non controlla se l'utente che esegue una query con più istruzioni ha accesso a tutte le risorse a cui fa riferimento.
All'interno di una query con più istruzioni, le autorizzazioni per ogni espressione oistruzione vengono convalidate separatamente. Ad esempio:
SELECT * FROM dataset_with_access.table1; SELECT * FROM dataset_without_access.table2;
Se l'utente che esegue la query ha accesso a table1
ma non ha accesso a table2
, la prima query va a buon fine e
la seconda query ha esito negativo. Il job di query multi-istruzione stesso
non riesce.
Vincoli di sicurezza
Nelle query con più istruzioni, puoi utilizzare l'SQL dinamico per creare istruzioni SQL in fase di runtime. È conveniente, ma può offrire nuovi possibili usi impropri. Ad esempio, l'esecuzione della seguente query comporta potenziale sicurezza dell'iniezione SQL perché il parametro table potrebbe essere filtrato in modo errato, consentire l'accesso a, e l'esecuzione su tabelle indesiderate.
-- Risky query vulnerable to SQL injection attack.
EXECUTE IMMEDIATE CONCAT('SELECT * FROM SensitiveTable WHERE id = ', @id);
Per evitare di esporre o divulgare dati sensibili in una tabella o di eseguire comandi come DROP TABLE
per eliminare i dati in una tabella, le istruzioni procedurali dinamiche di BigQuery supportano più misure di sicurezza per ridurre l'esposizione agli attacchi di SQL injection, tra cui:
- Un'istruzione
EXECUTE IMMEDIATE
non consente la relativa query, espansa con parametri di ricerca e variabili, per incorporare più istruzioni SQL. - L'esecuzione dinamica dei seguenti comandi è limitata:
BEGIN
/END
,CALL
,CASE
,IF
,LOOP
,WHILE
eEXECUTE IMMEDIATE
.
Limitazioni dei campi di configurazione
I seguenti campi di query di configurazione dei job non può essere impostato per una query con più istruzioni:
clustering
create_disposition
destination_table
destination_encryption_configuration
range_partitioning
schema_update_options
time_partitioning
user_defined_function_resources
write_disposition
Prezzi
I prezzi delle query con più istruzioni includono gli addebiti per le query (se utilizzi il modello di fatturazione on demand) e lo spazio di archiviazione per le tabelle temporanee. Quando utilizzi prenotazioni, l'utilizzo delle query è coperto i costi della prenotazione.
Calcolo delle dimensioni delle query on demand
Se utilizzi la fatturazione on demand, BigQuery addebita un costo per query a più istruzioni basate sul numero di byte elaborati durante l'esecuzione delle query con più dichiarazioni.
Per avere una stima del numero di byte che potrebbe elaborare una query con più istruzioni, puoi eseguire un simulacro.
Per queste query con più istruzioni si applicano i prezzi seguenti:
DECLARE
: la somma dei byte analizzati per le tabelle cui viene fatto riferimento nell'espressioneDEFAULT
. Le istruzioniDECLARE
senza riferimenti a tabella non sono addebitate.SET
: la somma dei byte analizzati per le tabelle cui viene fatto riferimento nell'espressione.SET
le istruzioni senza riferimenti a tabelle non sono addebitate.IF
: la somma dei byte analizzati per le tabelle cui viene fatto riferimento nell'espressione della condizione. Le espressioni della condizioneIF
senza riferimenti a tabelle non sono soggette a un ad accesso meno frequente per ridurre i costi di archiviazione. Eventuali istruzioni non eseguite all'interno del bloccoIF
non sono addebitate.WHILE
: la somma dei byte analizzati per le tabelle cui viene fatto riferimento nell'espressione della condizione. Le istruzioniWHILE
senza riferimenti a tabelle nell'espressione della condizione non sono addebitate. Tutte le istruzioni all'interno del bloccoWHILE
che sono non eseguite non comportano alcun costo.CONTINUE
oITERATE
: nessun costo associato.BREAK
oLEAVE
: nessun costo associato.BEGIN
oEND
: nessun costo associato.
Se una query con più istruzioni ha esito negativo, il costo di eventuali istruzioni fino l'errore è ancora valido. L'istruzione con esito negativo non sarà addebitata.
Ad esempio, il seguente codice campione contiene commenti che precedono ogni affermazione che spiega gli eventuali costi sostenuti da ciascuna affermazione:
-- No cost, since no tables are referenced. DECLARE x DATE DEFAULT CURRENT_DATE(); -- Incurs the cost of scanning string_col from dataset.table. DECLARE y STRING DEFAULT (SELECT MAX(string_col) FROM dataset.table); -- Incurs the cost of copying the data from dataset.big_table. Once the -- table is created, you are not charged for storage while the rest of the -- multi-statement query runs. CREATE TEMP TABLE t AS SELECT * FROM dataset.big_table; -- Incurs the cost of scanning column1 from temporary table t. SELECT column1 FROM t; -- No cost, since y = 'foo' doesn't reference a table. IF y = 'foo' THEN -- Incurs the cost of scanning all columns from dataset.other_table, if -- y was equal to 'foo', or otherwise no cost since it is not executed. SELECT * FROM dataset.other_table; ELSE -- Incurs the cost of scanning all columns from dataset.different_table, if -- y was not equal to 'foo', or otherwise no cost since it is not executed. UPDATE dataset.different_table SET col = 10 WHERE true; END IF; -- Incurs the cost of scanning date_col from dataset.table for each -- iteration of the loop. WHILE x < (SELECT MIN(date_col) FROM dataset.table) DO -- No cost, since the expression does not reference any tables. SET x = DATE_ADD(x, INTERVAL 1 DAY); -- No cost, since the expression does not reference any tables. IF true THEN -- LEAVE has no associated cost. LEAVE; END IF; -- Never executed, since the IF branch is always taken, so does not incur -- a cost. SELECT * FROM dataset.big_table; END WHILE;
Per ulteriori informazioni, vedi Calcolo delle dimensioni delle query:
Prezzi di archiviazione
Ti viene addebitato il costo delle tabelle temporanee create da query con più istruzioni. Puoi utilizzare lo
TABLE_STORAGE
oppure
TABLE_STORAGE_USAGE_TIMELINE
per vedere lo spazio di archiviazione utilizzato da queste tabelle temporanee. Temporaneo
risiedono in set di dati _script%
nascosti con nomi generati in modo casuale.
Quote
Per informazioni sulle quote per le query con più istruzioni, consulta Quote e limiti.
Visualizza il numero di query con più dichiarazioni
Puoi visualizzare il numero di query con più istruzioni attive utilizzando
INFORMATION_SCHEMA.JOBS_BY_PROJECT
visualizzazione.
L'esempio seguente utilizza la vista INFORMATION_SCHEMA.JOBS_BY_PROJECT
per
mostra il numero di query con più istruzioni del giorno precedente:
SELECT
COUNT(*)
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
AND job_type = "QUERY"
AND state = 'RUNNING'
AND statement_type = 'SCRIPT'
Per ulteriori informazioni su come eseguire query su INFORMATION_SCHEMA.JOBS
per query con più istruzioni, consulta Job di query con più istruzioni.