Gestisci indici di ricerca
Un indice di ricerca è una struttura di dati progettata per consentire una ricerca molto efficiente con la funzione SEARCH
.
Proprio come l'indice che trovi sul retro di un libro, un indice di ricerca per una colonna di dati stringa si comporta come una tabella ausiliaria con una colonna per le parole univoche e un'altra per la posizione all'interno dei dati.
Crea un indice di ricerca
Per creare un indice di ricerca, utilizza l'istruzione DDL CREATE SEARCH INDEX
. Puoi creare un indice di ricerca in base a questi tipi di colonna:
STRING
ARRAY<STRING>
STRUCT
contenente almeno un campo nidificato di tipoSTRING
oARRAY<STRING>
JSON
Quando crei un indice di ricerca, puoi specificare il tipo di analizzatore di testo da utilizzare. Lo strumento di analisi del testo controlla in che modo i dati vengono tokenizzati per l'indicizzazione e la ricerca. Il valore predefinito è LOG_ANALYZER
. Questo analizzatore funziona bene per i log generati dalle macchine e ha regole speciali sui token comunemente presenti nei dati di osservabilità, come indirizzi IP o email. Utilizza l'NO_OP_ANALYZER
quando disponi di dati pre-elaborati che vuoi abbinare in modo esatto.
PATTERN_ANALYZER
estrae i token dal testo utilizzando un'espressione regolare.
L'esempio seguente crea un indice di ricerca nelle colonne a
e c
di simple_table
.
CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON);
CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, c);
Quando crei un indice di ricerca su ALL COLUMNS
, vengono indicizzati tutti i dati relativi a STRING
o JSON
nella tabella. Se la tabella non contiene questi dati, ad esempio se tutte le colonne contengono numeri interi, la creazione dell'indice non riesce. Quando specifichi una colonna STRUCT
da indicizzare, tutti i sottocampi nidificati vengono indicizzati.
Nell'esempio seguente, viene creato un indice di ricerca su a
, c.e
e c.f.g
e utilizza lo strumento di analisi del testo NO_OP_ANALYZER
.
CREATE TABLE dataset.my_table( a STRING, b INT64, c STRUCT <d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>>) AS SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c; CREATE SEARCH INDEX my_index ON dataset.my_table(ALL COLUMNS) OPTIONS (analyzer = 'NO_OP_ANALYZER');
Poiché l'indice di ricerca è stato creato il giorno ALL COLUMNS
, tutte le colonne aggiunte alla tabella vengono indicizzate automaticamente se contengono dati STRING
.
Informazioni sull'aggiornamento dell'indice
Gli indici di ricerca sono completamente gestiti da BigQuery e vengono aggiornati automaticamente quando la tabella viene modificata. Le seguenti modifiche allo schema possono attivare un aggiornamento completo:
- Una nuova colonna Indicizzabile viene aggiunta a una tabella con un indice di ricerca su
ALL COLUMNS
. - Una colonna indicizzata viene aggiornata a causa di una modifica allo schema della tabella.
Se elimini l'unica colonna indicizzata in una tabella o rinomini la tabella stessa, l'indice di ricerca viene eliminato automaticamente.
Gli indici di ricerca sono progettati per tabelle di grandi dimensioni. Se crei un indice di ricerca in una tabella di dimensioni inferiori a 10 GB, l'indice non viene completato. Allo stesso modo, se elimini dati da una tabella indicizzata e la dimensione della tabella è inferiore a 10 GB, l'indice viene temporaneamente disattivato. In questo caso, le query di ricerca non utilizzano l'indice e il codice IndexUnusedReason
è BASE_TABLE_TOO_SMALL
. Questo accade indipendentemente dal fatto che utilizzi o meno la tua prenotazione per i job di gestione dell'indice. Quando le dimensioni di una tabella indicizzata superano
10 GB, il suo indice viene completato automaticamente. Lo spazio di archiviazione non viene addebitato finché l'indice di ricerca non è compilato e attivo. Le query che utilizzano la funzione SEARCH
restituiscono sempre risultati corretti anche se alcuni dati non sono ancora stati indicizzati.
Ricevere informazioni sugli indici di ricerca
Puoi verificare l'esistenza e l'idoneità di un indice di ricerca eseguendo una query su INFORMATION_SCHEMA
. Esistono due viste contenenti
metadati sugli indici di ricerca. La vista INFORMATION_SCHEMA.SEARCH_INDEXES
contiene informazioni su ogni indice di ricerca creato in un set di dati. La vista INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS
contiene informazioni su quali colonne di ogni tabella del set di dati sono indicizzate.
L'esempio seguente mostra tutti gli indici di ricerca attiva nelle tabelle nel set di dati my_dataset
, che si trova nel progetto my_project
. Sono inclusi i nomi, le istruzioni DDL utilizzate per crearle, la percentuale di copertura e l'analizzatore di testo. Se una tabella di base indicizzata è
inferiore a 10 GB, il suo indice non viene compilato, nel qual caso
coverage_percentage
è 0.
SELECT table_name, index_name, ddl, coverage_percentage, analyzer
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE index_status = 'ACTIVE';
I risultati dovrebbero essere simili ai seguenti:
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+ | table_name | index_name | ddl | coverage_percentage | analyzer | +-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+ | small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names) | 0 | NO_OP_ANALYZER | | large_table | logs_index | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100 | LOG_ANALYZER | +-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
L'esempio seguente crea un indice di ricerca su tutte le colonne di my_table
.
CREATE TABLE dataset.my_table( a STRING, b INT64, c STRUCT <d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>>) AS SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c; CREATE SEARCH INDEX my_index ON dataset.my_table(ALL COLUMNS);
La seguente query estrae informazioni sui campi indicizzati.
index_field_path
indica quale campo di una colonna viene indicizzato. Si differenzia da index_column_name
solo nel caso di un STRUCT
, in cui viene fornito il percorso completo del campo indicizzato. In questo esempio, la colonna c
contiene un campo ARRAY<STRING>
e
e un altro STRUCT
denominato f
che contiene un campo STRING
g
, ognuno dei quali indicizzato.
SELECT table_name, index_name, index_column_name, index_field_path
FROM my_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS
Il risultato è simile al seguente:
+------------+------------+-------------------+------------------+ | table_name | index_name | index_column_name | index_field_path | +------------+------------+-------------------+------------------+ | my_table | my_index | a | a | | my_table | my_index | c | c.e | | my_table | my_index | c | c.f.g | +------------+------------+-------------------+------------------+
La seguente query unisce la vista INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS
alle viste INFORMATION_SCHEMA.SEARCH_INDEXES
e INFORMATION_SCHEMA.COLUMNS
per includere lo stato dell'indice di ricerca e il tipo di dati di ogni colonna:
SELECT index_columns_view.index_catalog AS project_name, index_columns_view.index_SCHEMA AS dataset_name, indexes_view.TABLE_NAME AS table_name, indexes_view.INDEX_NAME AS index_name, indexes_view.INDEX_STATUS AS status, index_columns_view.INDEX_COLUMN_NAME AS column_name, index_columns_view.INDEX_FIELD_PATH AS field_path, columns_view.DATA_TYPE AS data_type FROM mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES indexes_view INNER JOIN mydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS index_columns_view ON indexes_view.TABLE_NAME = index_columns_view.TABLE_NAME AND indexes_view.INDEX_NAME = index_columns_view.INDEX_NAME LEFT OUTER JOIN mydataset.INFORMATION_SCHEMA.COLUMNS columns_view ON indexes_view.INDEX_CATALOG = columns_view.TABLE_CATALOG AND indexes_view.INDEX_SCHEMA = columns_view.TABLE_SCHEMA AND index_columns_view.TABLE_NAME = columns_view.TABLE_NAME AND index_columns_view.INDEX_COLUMN_NAME = columns_view.COLUMN_NAME ORDER BY project_name, dataset_name, table_name, column_name;
Il risultato è simile al seguente:
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+ | project | dataset | table | index_name | status | column_name | field_path | data_type | +------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+ | my_project | my_dataset | my_table | my_index | ACTIVE | a | a | STRING | | my_project | my_dataset | my_table | my_index | ACTIVE | c | c.e | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> | | my_project | my_dataset | my_table | my_index | ACTIVE | c | c.f.g | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> | +------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
Opzioni di gestione degli indici
Per creare gli indici e fare in modo che BigQuery li gestisca, hai due opzioni:
- Utilizza il pool di slot condiviso predefinito: quando i dati che prevedi di indicizzare sono al di sotto del limite per organizzazione, puoi utilizzare il pool di slot condivisi gratuito per la gestione dell'indice.
- Utilizza la tua prenotazione: per ottenere avanzamento dell'indicizzazione più prevedibile e coerente, sui tuoi carichi di lavoro di produzione più grandi, puoi utilizzare le tue prenotazioni per la gestione degli indici.
Usa gli spazi condivisi
Se non hai configurato il tuo progetto per l'utilizzo di una prenotazione dedicata per l'indicizzazione, la gestione dell'indice viene gestita nel pool di slot condiviso gratuito, in base ai vincoli seguenti.
Se aggiungi dati a una tabella e questo fa sì che la dimensione totale delle tabelle indicizzate superi il limite della tua organizzazione, BigQuery mette in pausa la gestione dell'indice per tutte le tabelle indicizzate. In questo caso, il campo index_status
nella
vista INFORMATION_SCHEMA.SEARCH_INDEXES
mostra PENDING DISABLEMENT
e l'indice è in coda per l'eliminazione. Mentre l'indice è in attesa di disattivazione, viene comunque utilizzato nelle query e ti viene addebitato il costo dell'archiviazione dell'indice.
Dopo aver eliminato un indice, il campo index_status
mostra l'indice come TEMPORARILY DISABLED
. In questo stato, le query non utilizzano l'indice e non ti viene addebitato alcun costo per l'archiviazione dell'indice. In questo caso, il codice IndexUnusedReason
è BASE_TABLE_TOO_LARGE
.
Se elimini dati dalla tabella e la dimensione totale delle tabelle indicizzate scende al di sotto del limite per organizzazione, la gestione dell'indice viene ripresa per tutte le tabelle indicizzate. Il campo index_status
nella visualizzazione
INFORMATION_SCHEMA.SEARCH_INDEXES
è ACTIVE
; le query possono utilizzare l'indice e ti viene addebitato il costo
di archiviazione dell'indice.
BigQuery non fornisce garanzie sulla capacità disponibile del pool condiviso o sulla velocità effettiva di indicizzazione visualizzata. Per le applicazioni di produzione, potresti voler usare slot dedicati per l'elaborazione dell'indice.
Usa la tua prenotazione
Anziché utilizzare il pool di slot condiviso predefinito, puoi facoltativamente designare la tua prenotazione per indicizzare le tabelle. L'utilizzo di una prenotazione personalizzata garantisce prestazioni prevedibili e coerenti dei job di gestione dell'indice, come creazione, aggiornamento e ottimizzazioni in background.
- Non sono previsti limiti di dimensione della tabella quando viene eseguito un job di indicizzazione nella tua prenotazione.
- L'utilizzo della tua prenotazione ti offre flessibilità nella gestione degli indici. Se devi creare un indice di grandi dimensioni o eseguire un aggiornamento principale di una tabella indicizzata, puoi aggiungere temporaneamente altri slot all'assegnazione.
Per indicizzare le tabelle in un progetto con una prenotazione designata, crea una prenotazione nella regione in cui si trovano le tabelle. Quindi, assegna il progetto alla prenotazione con job_type
impostato su BACKGROUND
:
SQL
Utilizza l'istruzione DDL CREATE ASSIGNMENT
.
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'Editor query, inserisci la seguente istruzione:
CREATE ASSIGNMENT `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID` OPTIONS ( assignee = 'projects/PROJECT_ID', job_type = 'BACKGROUND');
Sostituisci quanto segue:
ADMIN_PROJECT_ID
: l'ID del progetto di amministrazione proprietario della risorsa di prenotazioneLOCATION
: la località della prenotazioneRESERVATION_NAME
: il nome della prenotazioneASSIGNMENT_ID
: l'ID del compitoL'ID deve essere univoco per il progetto e la località, iniziare e terminare con una lettera minuscola o un numero e contenere solo lettere minuscole, numeri e trattini.
PROJECT_ID
: l'ID del progetto contenente le tabelle da indicizzare. Questo progetto è assegnato alla prenotazione.
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
bq
Usa il comando bq mk
:
bq mk \ --project_id=ADMIN_PROJECT_ID \ --location=LOCATION \ --reservation_assignment \ --reservation_id=RESERVATION_NAME \ --assignee_id=PROJECT_ID \ --job_type=BACKGROUND \ --assignee_type=PROJECT
Sostituisci quanto segue:
ADMIN_PROJECT_ID
: l'ID del progetto di amministrazione proprietario della risorsa di prenotazioneLOCATION
: la località della prenotazioneRESERVATION_NAME
: il nome della prenotazionePROJECT_ID
: l'ID del progetto da assegnare a questa prenotazione
Visualizzare i job di indicizzazione
Ogni volta che viene creato o aggiornato un indice in una singola tabella, viene creato un nuovo job di indicizzazione. Per visualizzare le informazioni sul job, esegui una query sulle viste INFORMATION_SCHEMA.JOBS*
. Puoi
filtrare i job di indicizzazione
impostando job_type IS NULL AND SEARCH(job_id, '`search_index`')
nella
clausola WHERE
della tua query. Nell'esempio seguente sono elencati i cinque job di indicizzazione più recenti nel progetto my_project
:
SELECT * FROM region-us.INFORMATION_SCHEMA.JOBS WHERE project_id = 'my_project' AND job_type IS NULL AND SEARCH(job_id, '`search_index`') ORDER BY creation_time DESC LIMIT 5;
Scegli le dimensioni della prenotazione
Per scegliere il numero giusto di slot per la prenotazione, devi considerare quando vengono eseguiti i job di gestione dell'indice, il numero di slot che utilizzano e l'aspetto del tuo utilizzo nel tempo. BigQuery attiva un job di gestione dell'indice nelle seguenti situazioni:
- Crei un indice su una tabella.
- I dati vengono modificati in una tabella indicizzata.
- Lo schema di una tabella cambia e ciò influisce sulle colonne indicizzate.
- I dati dell'indice e i metadati vengono ottimizzati o aggiornati periodicamente.
Il numero di slot necessari per un job di gestione dell'indice in una tabella dipende dai seguenti fattori:
- Le dimensioni della tabella
- La velocità di importazione dati nella tabella
- La percentuale di istruzioni DML applicate alla tabella
- Il ritardo accettabile per la creazione e la manutenzione dell'indice
- La complessità dell'indice, solitamente determinata dagli attributi dei dati, come il numero di termini duplicati
Stima iniziale
Le stime seguenti possono aiutarti a calcolare approssimativamente il numero di slot richiesti dalla prenotazione. A causa della natura altamente variabile dell'indicizzazione dei carichi di lavoro, dovresti rivalutare i tuoi requisiti dopo aver iniziato a indicizzare i dati.
- Dati esistenti: con una prenotazione di 1000 slot, una tabella esistente in BigQuery può essere indicizzata a una velocità media di massimo 4 GiB al secondo, ovvero circa 336 TiB al giorno.
- Dati appena importati: l'indicizzazione richiede in genere un maggior utilizzo di risorse per i dati appena importati, in quanto la tabella e il relativo indice vengono sottoposti a diversi cicli di ottimizzazioni trasformative. In media, l'indicizzazione dei dati importati di recente consuma il triplo delle risorse rispetto all'indicizzazione iniziale del backfill degli stessi dati.
- Dati modificati raramente: le tabelle indicizzate con modifiche dei dati minime o nulle richiedono molte meno risorse per la manutenzione continua dell'indice. Un punto di partenza consigliato è mantenere 1/5 degli slot necessari per l'indicizzazione del backfill iniziale degli stessi dati e non meno di 250 slot.
- L'avanzamento dell'indicizzazione viene scalato in modo lineare in base alle dimensioni della prenotazione. Tuttavia, sconsigliamo di utilizzare prenotazioni di dimensioni inferiori a 250 slot per l'indicizzazione perché potrebbero portare a inefficienze che potrebbero rallentare l'avanzamento dell'indicizzazione.
- Queste stime possono variare in base alle funzionalità, alle ottimizzazioni e all'utilizzo effettivo.
- Se le dimensioni totali della tabella della tua organizzazione superano il limite di indicizzazione della tua regione, devi mantenere una prenotazione diversa da zero assegnata per l'indicizzazione. In caso contrario, l'indicizzazione potrebbe tornare al livello predefinito, con l'eliminazione involontaria di tutti gli indici.
Monitoraggio di utilizzo e avanzamento
Il modo migliore per valutare il numero di slot di cui hai bisogno per eseguire in modo efficiente i job di gestione dell'indice è monitorare l'utilizzo degli slot e regolare di conseguenza la dimensione della prenotazione. La seguente query produce l'utilizzo giornaliero degli slot per i job di gestione degli indici. Sono inclusi solo gli ultimi 30 giorni nella
regione us-west1
:
SELECT TIMESTAMP_TRUNC(job.creation_time, DAY) AS usage_date, -- Aggregate total_slots_ms used for index-management jobs in a day and divide -- by the number of milliseconds in a day. This value is most accurate for -- days with consistent slot usage. SAFE_DIVIDE(SUM(job.total_slot_ms), (1000 * 60 * 60 * 24)) AS average_daily_slot_usage FROM `region-us-west1`.INFORMATION_SCHEMA.JOBS job WHERE project_id = 'my_project' AND job_type IS NULL AND SEARCH(job_id, '`search_index`') GROUP BY usage_date ORDER BY usage_date DESC limit 30;
Se non ci sono slot sufficienti per eseguire job di gestione dell'indice, un indice può diventare non sincronizzato con la relativa tabella e i job di indicizzazione potrebbero non riuscire. In questo caso, BigQuery ricrea l'indice da zero. Per evitare di avere un indice non sincronizzato, assicurati di avere slot sufficienti per supportare gli aggiornamenti dell'indice a seguito dell'importazione e dell'ottimizzazione dei dati. Per ulteriori informazioni sul monitoraggio dell'utilizzo degli slot, consulta i grafici delle risorse di amministrazione.
best practice
- Gli indici di ricerca sono progettati per tabelle di grandi dimensioni. I miglioramenti delle prestazioni di un indice di ricerca aumentano con le dimensioni della tabella.
- Non indicizzare le colonne che contengono solo un numero molto ridotto di valori univoci.
- Non indicizzare le colonne su cui non intendi chiamare la funzione
SEARCH
. - Fai attenzione quando crei un indice di ricerca su
ALL COLUMNS
. Ogni volta che aggiungi una colonna contenente datiSTRING
oJSON
, questa viene indicizzata. - Ti consigliamo di utilizzare la tua prenotazione per la gestione dell'indice nelle applicazioni di produzione. Se scegli di utilizzare il pool di slot condiviso predefinito per i job di gestione dell'indice, si applicano i limiti di dimensione per organizzazione.
Elimina un indice di ricerca
Quando non hai più bisogno di un indice di ricerca o vuoi modificare le colonne indicizzate in una tabella, puoi eliminare l'indice attualmente in quella tabella. A questo scopo, utilizza l'istruzione DDL DROP SEARCH INDEX
.
Se viene eliminata una tabella indicizzata, il relativo indice viene eliminato automaticamente.
Esempio:
DROP SEARCH INDEX my_index ON dataset.simple_table;
Passaggi successivi
- Per una panoramica dei casi d'uso dell'indice di ricerca, dei prezzi, delle autorizzazioni richieste e delle limitazioni, consulta l'introduzione alla ricerca in BigQuery.
- Per informazioni sulla ricerca efficiente delle colonne indicizzate, consulta Eseguire ricerche con un indice.