Esegui query su più tabelle utilizzando una tabella con funzione carattere jolly

Nota: le tabelle con funzione carattere jolly presentano molte limitazioni e sono meno performanti rispetto alle normali tabelle BigQuery che sfruttano il partizionamento e il clustering. Come best practice, utilizza tabelle partizionate o in cluster, se possibile.

Le tabelle con funzione carattere jolly in BigQuery ti consentono di eseguire query su più tabelle con nomi simili che condividono schemi compatibili. Una tabella con funzione carattere jolly rappresenta un'unione di tutte le tabelle che corrispondono all'espressione con carattere jolly. Di conseguenza, puoi analizzare set di dati frammentati senza scrivere query lunghe e complesse che contengono istruzioni UNION ALL.

Limitazioni

Le query sulle tabelle con funzione carattere jolly sono soggette alle seguenti limitazioni.

• Le tabelle con funzione carattere jolly sono disponibili solo in GoogleSQL. Per una funzionalità equivalente in SQL precedente, consulta Funzioni carattere jolly nella tabella.
• La funzionalità tabella con funzione carattere jolly non supporta le viste. Se la tabella con funzione carattere jolly corrisponde a una visualizzazione nel set di dati, la query restituisce un errore anche se contiene una clausola WHERE sulla pseudocolonna _TABLE_SUFFIX per filtrare la visualizzazione.
• I risultati memorizzati nella cache non sono supportati per le query su più tabelle che utilizzano un carattere jolly anche se è selezionata l'opzione Utilizza risultati memorizzati nella cache. Se si esegue la stessa query con caratteri jolly più volte, ti viene addebitato un importo per ogni query.
• Le tabelle con funzione carattere jolly supportano solo lo spazio di archiviazione BigQuery integrato. Non puoi utilizzare i caratteri jolly per eseguire query su una tabella esterna o una vista.
• Non puoi utilizzare query con caratteri jolly su tabelle con partizionamento incompatibile o su una combinazione di tabelle partizionate e non partizionate. Anche le tabelle su cui viene eseguita la query devono avere specifiche di clustering identiche.
• Puoi utilizzare le tabelle con funzione carattere jolly con le tabelle partizionate. Sono supportati sia il pruning delle partizioni sia il pruning dei cluster. Tuttavia, le tabelle raggruppate in cluster, ma non partizionate, non usufruiscono del vantaggio completo della riduzione dei cluster derivante dall'utilizzo dei caratteri jolly.
• Le query che contengono istruzioni Data Manipulation Language (DML) non possono utilizzare una tabella con funzione carattere jolly come destinazione della query. Ad esempio, una tabella con funzione carattere jolly può essere utilizzata nella clausola FROM di una query UPDATE, ma non può essere utilizzata come destinazione dell'operazione UPDATE.
• I filtri sulle pseudocolonne _TABLE_SUFFIX o _PARTITIONTIME che includono funzioni definite dall'utente JavaScript non limitano il numero di tabelle analizzate in una tabella con funzione carattere jolly.
• Le query con caratteri jolly non sono supportate per le tabelle protette da chiavi di crittografia gestite dal cliente (CMEK).
• Tutte le tabelle a cui viene fatto riferimento in una query con caratteri jolly devono avere esattamente lo stesso insieme di chiavi e valori di tag.

• Se una singola tabella analizzata presenta una mancata corrispondenza dello schema (ovvero una colonna con lo stesso nome è di un tipo diverso), la query non va a buon fine e viene visualizzato l'errore Impossibile leggere il campo di tipo X come campo Y: column_name. Tutte le tabelle vengono abbinate anche se utilizzi l'operatore di uguaglianza =. Ad esempio, nella seguente query viene analizzata anche la tabella my_dataset.my_table_03_backup. Pertanto, la query potrebbe non riuscire a causa di una mancata corrispondenza dello schema. Tuttavia, se non esiste una mancata corrispondenza dello schema, i risultati provengono solo dalla tabella my_dataset.my_table_03, come previsto.

  SELECT *
  FROM my_project.my_dataset.my_table_*
  WHERE _TABLE_SUFFIX = '03'
  

• Le tabelle con funzione carattere jolly non sono supportate da BI Engine.

• Le tabelle con funzione carattere jolly non sono supportate per le tabelle con nomi di colonne flessibili o colonne rinominate. Per ovviare a questo limite, escludi le colonne interessate dalla query o filtra la tabella utilizzando la pseudocolonna _TABLE_SUFFIX.

Prima di iniziare

• Assicurati di utilizzare GoogleSQL. Per saperne di più, consulta Cambio dei dialetti SQL.
• Se utilizzi l'SQL precedente, consulta Funzioni carattere jolly nella tabella.
• Molti degli esempi in questa pagina utilizzano un set di dati pubblico della National Oceanic and Atmospheric Administration (NOAA). Per saperne di più sui dati, consulta NOAA Global Surface Summary of the Day Weather Data.

Quando utilizzare le tabelle con funzione carattere jolly

Le tabelle con funzione carattere jolly sono utili quando un set di dati contiene più tabelle con nomi simili e schemi compatibili. In genere, questi set di dati contengono tabelle che rappresentano ciascuna i dati di un singolo giorno, mese o anno. Ad esempio, un set di dati pubblico ospitato da BigQuery, NOAA Global Surface Summary of the Day Weather Data, contiene una tabella per ogni anno dal 1929 a oggi.

Una query che analizza tutti gli ID tabella dal 1929 al 1940 sarebbe molto lunga se dovessi nominare tutte e 12 le tabelle nella clausola FROM (la maggior parte delle tabelle è omessa in questo esempio):

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

La stessa query che utilizza una tabella con funzione carattere jolly è molto più concisa:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC

Le tabelle con funzione carattere jolly supportano solo lo spazio di archiviazione BigQuery integrato. Non puoi utilizzare i caratteri jolly quando esegui query su una tabella esterna o una vista.

Sintassi delle tabelle con funzione carattere jolly

Sintassi delle tabelle con funzione carattere jolly:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>

ID progetto della piattaforma Google Cloud. Facoltativo se utilizzi l'ID progetto predefinito.

<dataset-id>

ID set di dati BigQuery.

<table-prefix>

Una stringa comune a tutte le tabelle corrispondenti al carattere jolly. Il prefisso della tabella è facoltativo. Se ometti il prefisso della tabella, verranno trovate tutte le tabelle nel set di dati.

* (carattere jolly)

Il carattere jolly "*" rappresenta uno o più caratteri del nome di una tabella. Il carattere jolly può essere visualizzato solo come carattere finale del nome di una tabella con funzione carattere jolly.

Le query con tabelle con caratteri jolly supportano la pseudocolonna _TABLE_SUFFIX nella clausola WHERE. Questa colonna contiene i valori corrispondenti al carattere jolly, in modo che le query possano filtrare le tabelle a cui viene eseguito l'accesso. Ad esempio, le seguenti clausole WHERE utilizzano operatori di confronto per filtrare le tabelle corrispondenti:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

Per ulteriori informazioni sulla pseudocolonna _TABLE_SUFFIX, consulta Filtro delle tabelle selezionate utilizzando _TABLE_SUFFIX.

Racchiudi i nomi delle tabelle con caratteri jolly tra apici inversi

Il nome della tabella con funzione carattere jolly contiene il carattere speciale (*), il che significa che devi racchiudere il nome della tabella con funzione carattere jolly tra apici inversi (`). Ad esempio, la seguente query è valida perché utilizza i backtick:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

La seguente query NON è valida perché non è racchiusa correttamente tra apici inversi:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Le virgolette non funzionano:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Esegui query sulle tabelle utilizzando tabelle con funzione carattere jolly

Le tabelle con funzione carattere jolly consentono di eseguire query su più tabelle in modo conciso. Ad esempio, un set di dati pubblico ospitato da BigQuery, il NOAA Global Surface Summary of the Day Weather Data, contiene una tabella per ogni anno dal 1929 a oggi che condivide il prefisso comune gsod seguito dall'anno di quattro cifre. Le tabelle sono denominate gsod1929, gsod1930, gsod1931 ecc.

Per eseguire query su un gruppo di tabelle che condividono un prefisso comune, utilizza la funzione carattere jolly nella tabella (*) dopo il prefisso della tabella nell'istruzione FROM. Ad esempio, la seguente query trova la temperatura massima registrata negli anni '40:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

Filtra le tabelle selezionate utilizzando _TABLE_SUFFIX

Per limitare una query in modo che analizzi solo un insieme specificato di tabelle, utilizza la pseudocolonna _TABLE_SUFFIX in una clausola WHERE con una condizione che sia un'espressione costante.

La pseudocolonna _TABLE_SUFFIX contiene i valori corrispondenti al carattere jolly nella tabella. Ad esempio, la query di esempio precedente, che analizza tutte le tabelle degli anni '40, utilizza un carattere jolly nella tabella per rappresentare l'ultima cifra dell'anno:

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

La pseudocolonna _TABLE_SUFFIX corrispondente contiene valori compresi tra 0 e 9, che rappresentano le tabelle da gsod1940 a gsod1949. Questi valori _TABLE_SUFFIX possono essere utilizzati in una clausola WHERE per filtrare tabelle specifiche.

Ad esempio, per filtrare in base alla temperatura massima negli anni 1940 e 1944, utilizza i valori 0 e 4 per _TABLE_SUFFIX:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

L'utilizzo di _TABLE_SUFFIX può ridurre notevolmente il numero di byte analizzati, il che contribuisce a ridurre il costo di esecuzione delle query.

Tuttavia, i filtri su _TABLE_SUFFIX che includono condizioni senza espressioni costanti non limitano il numero di tabelle scansionate in una tabella con funzione carattere jolly. Ad esempio, la seguente query non limita le tabelle analizzate per la tabella con funzione carattere jolly bigquery-public-data.noaa_gsod.gsod19* perché il filtro utilizza il valore dinamico della colonna table_id:

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Come altro esempio, la seguente query limita l'analisi in base alla prima condizione di filtro, _TABLE_SUFFIX BETWEEN '40' and '60', perché si tratta di un'espressione costante. Tuttavia, la seguente query non limita l'analisi in base alla seconda condizione di filtro, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), perché si tratta di un'espressione dinamica:

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Per risolvere il problema, puoi eseguire due query separate, ad esempio:

Prima query:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%'

Seconda query:

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

Queste query di esempio utilizzano la vista INFORMATION_SCHEMA.TABLES. Per saperne di più sulla tabella INFORMATION_SCHEMA, consulta Recupero dei metadati della tabella utilizzando INFORMATION_SCHEMA.

Scansione di un intervallo di tabelle utilizzando _TABLE_SUFFIX

Per eseguire la scansione di un intervallo di tabelle, utilizza la pseudocolonna _TABLE_SUFFIX insieme alla clausola BETWEEN. Ad esempio, per trovare la temperatura massima registrata negli anni compresi tra il 1929 e il 1935 inclusi, utilizza la funzione carattere jolly nella tabella per rappresentare le ultime due cifre dell'anno:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

Scansione di un intervallo di tabelle partizionate per data di acquisizione utilizzando _PARTITIONTIME

Per analizzare un intervallo di tabelle partizionate per data di acquisizione, utilizza la pseudocolonna _PARTITIONTIME con la pseudocolonna _TABLE_SUFFIX. Ad esempio, la seguente query analizza la partizione del 1° gennaio 2017 nella tabella my_dataset.mytable_id1.

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

Esecuzione di query su tutte le tabelle di un set di dati

Per eseguire la scansione di tutte le tabelle di un set di dati, puoi utilizzare un prefisso vuoto e il carattere jolly della tabella, il che significa che la pseudocolonna _TABLE_SUFFIX contiene i nomi completi delle tabelle. Ad esempio, la seguente clausola FROM analizza tutte le tabelle nel set di dati GSOD:

FROM
  `bigquery-public-data.noaa_gsod.*`

Con un prefisso vuoto, la pseudocolonna _TABLE_SUFFIX contiene i nomi completi delle tabelle. Ad esempio, la seguente query equivale all'esempio precedente che trova la temperatura massima tra gli anni 1929 e 1935, ma utilizza i nomi completi delle tabelle nella clausola WHERE:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

Tuttavia, tieni presente che i prefissi più lunghi in genere hanno un rendimento migliore. Per saperne di più, consulta le best practice.

Dettagli di esecuzione della query

Schema utilizzato per la valutazione delle query

Per eseguire una query GoogleSQL che utilizza una tabella con funzione carattere jolly, BigQuery deduce automaticamente lo schema per quella tabella. BigQuery utilizza lo schema della tabella creata più di recente che corrisponde al carattere jolly come schema per la tabella con funzione carattere jolly. Anche se limiti il numero di tabelle che vuoi utilizzare dalla tabella con funzione carattere jolly utilizzando la pseudocolonna _TABLE_SUFFIX in una clausola WHERE, BigQuery utilizza lo schema per la tabella creata più di recente che corrisponde al carattere jolly.

Se una colonna dello schema inferito non esiste in una tabella corrispondente, BigQuery restituisce valori NULL per quella colonna nelle righe della tabella in cui manca la colonna.

Se lo schema non è coerente tra le tabelle corrispondenti alla query con caratteri jolly, BigQuery restituisce un errore. Questo accade quando le colonne delle tabelle corrispondenti hanno tipi di dati diversi o quando non è possibile presumere che le colonne non presenti in tutte le tabelle corrispondenti abbiano un valore null.

Best practice

• I prefissi più lunghi generalmente hanno un rendimento migliore rispetto a quelli più brevi. Ad esempio, la seguente query utilizza un prefisso lungo (gsod200):

  #standardSQL
  SELECT
  max
  FROM
  `bigquery-public-data.noaa_gsod.gsod200*`
  WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '0' AND '1'
  ORDER BY
  max DESC

  La seguente query in genere ha prestazioni peggiori perché utilizza un prefisso vuoto:

  #standardSQL
  SELECT
  max
  FROM
  `bigquery-public-data.noaa_gsod.*`
  WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
  ORDER BY
  max DESC

• Il partizionamento è consigliato rispetto allo sharding, perché le tabelle partizionate offrono prestazioni migliori. Lo sharding riduce le prestazioni e crea più tabelle da gestire. Per saperne di più, consulta Partizionamento e sharding.

Per le best practice per il controllo dei costi in BigQuery, consulta Controllo dei costi in BigQuery

Passaggi successivi

• Per ulteriori informazioni su GoogleSQL, consulta il riferimento per le query GoogleSQL.