Gestisci indici di ricerca

Un indice di ricerca è una struttura di dati progettata per consentire una ricerca efficiente con la funzione SEARCH. Un indice di ricerca può anche ottimizzare alcune query che utilizzano funzioni e operatori supportati.

Proprio come l'indice che si trova sul dorso di un libro, una ricerca l'indice di una colonna di dati stringa è come una tabella ausiliaria che ha una per le parole univoche e un'altra per la posizione nei dati in cui si trovano queste parole.

Creare un indice di ricerca

Per creare un indice di ricerca, utilizza il metodo CREATE SEARCH INDEX DDL. Per specificare i tipi di dati primitivi da indicizzare, consulta Creare un indice di ricerca e specificare le colonne e i tipi di dati. Se non specifichi alcun tipo di dati, per impostazione predefinita BigQuery indicizza le colonne dei seguenti tipi che contengono dati STRING:

  • STRING
  • ARRAY<STRING>
  • STRUCT contenente almeno un campo nidificato di tipo STRING o ARRAY<STRING>
  • JSON

Quando crei un indice di ricerca, puoi specificare il tipo di strumento di analisi del testo da utilizzare. Lo strumento di analisi del testo controlla il modo in cui i dati vengono tokenizzati per l'indicizzazione e ricerca. Il valore predefinito è LOG_ANALYZER. Questo analizzatore funziona bene per generati automaticamente e ha regole speciali per i token che si trovano comunemente dati di osservabilità come indirizzi IP o email. Usa NO_OP_ANALYZER quando disponi di dati pre-elaborati che vuoi far corrispondere esattamente. PATTERN_ANALYZER estrae i token dal testo utilizzando un'espressione regolare.

Crea un indice di ricerca con lo strumento di analisi di testo predefinito

Nell'esempio seguente, viene creato un indice di ricerca nelle colonne a e c di simple_table e utilizza lo strumento di analisi LOG_ANALYZER per impostazione predefinita:

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, c);

Creare un indice di ricerca in tutte le colonne con l'analizzatore NO_OP_ANALYZER

Quando crei un indice di ricerca su ALL COLUMNS, tutti i dati di STRING o JSON in la tabella viene indicizzata. Se la tabella non contiene dati di questo tipo, ad esempio se tutte le colonne contengono numeri interi, la creazione dell'indice non va a buon fine. Se specifichi STRUCT colonna da indicizzare, tutti i campi secondari nidificati sono indicizzati.

Nell'esempio seguente viene creato un indice di ricerca su a, c.e e c.f.g e viene utilizzato l'analizzatore di 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, qualsiasi colonna aggiunta al vengono indicizzate automaticamente se contengono dati STRING.

Crea un indice di ricerca e specifica le colonne e i tipi di dati

Quando crei un indice di ricerca, puoi specificare i tipi di dati da utilizzare. Tipi di dati controlla i tipi di colonne e sottocampi di JSON e STRUCT colonne per dell'indicizzazione. Il tipo di dati predefinito per l'indicizzazione è STRING. Per creare un indice di ricerca con più tipi di dati (ad esempio, tipi numerici), utilizza Dichiarazione CREATE SEARCH INDEX con data_types inclusa l'opzione.

Nell'esempio seguente viene creato un indice di ricerca per le colonne a, b, c e d di una tabella denominata simple_table. I tipi di dati delle colonne supportati sono STRING, INT64 e TIMESTAMP.

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON, d TIMESTAMP);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, b, c, d)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

Crea un indice di ricerca in tutte le colonne e specifica i tipi di dati

Quando crei un indice di ricerca su ALL COLUMNS con l'opzione data_types specificato, viene indicizzata qualsiasi colonna che corrisponde a uno dei tipi di dati specificati. Per le colonne JSON e STRUCT, qualsiasi campo secondario nidificato che corrisponde a uno dei vengono indicizzati i tipi di dati specificati.

Nell'esempio seguente, viene creato un indice di ricerca su ALL COLUMNS con tipi di dati specificati. Le colonne a, b, c, d.e, d.f, d.g.h, d.g.i di una tabella denominata my_table sono indicizzate:

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c TIMESTAMP,
  d STRUCT <e INT64,
            f ARRAY<STRING>,
            g STRUCT<h STRING, i INT64>>)
AS (
  SELECT
    'hello' AS a,
    10 AS b,
    TIMESTAMP('2008-12-25 15:30:00 UTC') AS c,
    (20, ['x', 'y'], ('z', 30)) AS d;
)

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

Poiché l'indice di ricerca è stato creato il giorno ALL COLUMNS, qualsiasi colonna aggiunta al vengono indicizzate automaticamente se corrispondono a uno qualsiasi dei dati specificati di testo.

Informazioni sull'aggiornamento dell'indice

Gli indici di ricerca sono completamente gestiti da BigQuery e automaticamente vengono aggiornate quando la tabella viene modificata. Le seguenti modifiche allo schema della tabella possono attivare un aggiornamento completo:

  • Una nuova colonna indicizzabile viene aggiunta a una tabella con un indice di ricerca attivato ALL COLUMNS.
  • Una colonna indicizzata viene aggiornata a causa di una modifica dello schema della tabella.

Se elimini l'unica colonna indicizzata in una tabella o rinomini la tabella stessa, significa che l'indice di ricerca viene eliminato automaticamente.

Gli indici di ricerca sono progettati per tabelle di grandi dimensioni. Se crei un indice di ricerca una tabella inferiore a 10 GB, l'indice non viene compilato. Analogamente, se elimini dati da una tabella indicizzata e le sue dimensioni scendono al di sotto di 10 GB, significa che l'indice è temporaneamente disattivato. In questo caso, le query di ricerca usano l'indice e Codice IndexUnusedReason è BASE_TABLE_TOO_SMALL. Questo accade indipendentemente dal fatto che utilizzi o meno i tuoi per i tuoi job di gestione degli indici. Quando le dimensioni di una tabella indicizzata superano 10 GB, il relativo indice verrà compilato automaticamente. Non ti viene addebitato alcun costo per lo spazio di archiviazione fino al che l'indice di ricerca sia compilato e attivo. Le query che utilizzano la funzioneSEARCH restituiscono sempre risultati corretti anche se alcuni dati non sono ancora indicizzati.

Ottieni 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 visualizzazioni che contengono metadati negli indici di ricerca. La INFORMATION_SCHEMA.SEARCH_INDEXES visualizzazione contiene informazioni su ogni indice di ricerca creato in un set di dati. La INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS visualizzazione contiene informazioni su quali colonne di ogni tabella nel set di dati vengono indicizzate.

L'esempio seguente mostra tutti gli indici di ricerca attivi nelle tabelle del set di datimy_dataset, nel progetto my_project. Sono inclusi i nomi, le istruzioni DDL utilizzate per crearli, la percentuale di copertura e l'analizza del testo. Se una tabella di base indicizzata è inferiore a 10 GB, l'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 in 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 indicizzati. Questo differisce dal valore index_column_name solo nel caso di un STRUCT, dove viene fornito il percorso completo del campo indicizzato. In questo esempio, la colonna c contiene un campo ARRAY<STRING> e e un altro STRUCT chiamato f che contiene un campo STRING g, ciascuno 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 con le 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 indici e gestirli con BigQuery, hai due opzioni:

  • Utilizza il pool di slot condiviso predefinito: se i dati che prevedi di indicizzare sono al di sotto del limite per organizzazione, puoi utilizzare il pool di slot condiviso gratuito per la gestione degli indici.
  • Utilizza la tua prenotazione: Per ottenere un'indicizzazione più prevedibile e coerente progressi sui carichi di lavoro di produzione più grandi, puoi utilizzare per la gestione degli indici.

Utilizzare gli slot condivisi

Se non hai configurato il tuo progetto per l'utilizzo di una prenotazione dedicata per l'indicizzazione la gestione degli indici viene gestita nel pool di slot condiviso gratuito, in base alle rispetto ai vincoli.

Se aggiungi dati a una tabella che determina la dimensione totale di superare il limite della tua organizzazione, BigQuery mette in pausa la gestione degli indici per tutte le tabelle indicizzate. In questo caso, il campo index_status nella INFORMATION_SCHEMA.SEARCH_INDEXES visualizzazione visualizza PENDING DISABLEMENT e l'indice è in coda per l'eliminazione. Mentre l'indice è in attesa di disattivazione, viene ancora utilizzato nelle query e ti viene addebitato lo spazio di archiviazione dell'indice. Dopo l'eliminazione di un indice, il campo index_status lo mostra come TEMPORARILY DISABLED. In questo stato, le query non utilizzano l'indice e non ti viene addebitato alcun costo per lo spazio di archiviazione dell'indice. In questo caso, il codice IndexUnusedReason è BASE_TABLE_TOO_LARGE.

Se elimini i dati dalla tabella e le dimensioni totali delle tabelle indicizzate rientrano nel limite per organizzazione, la gestione dell'indice viene ripresa per tutte le tabelle indicizzate. Il campo index_status nella INFORMATION_SCHEMA.SEARCH_INDEXES vista è ACTIVE, le query possono utilizzare l'indice e ti viene addebitato lo spazio di archiviazione dell'indice.

BigQuery non garantisce la disponibilità del pool condiviso o la velocità effettiva di indicizzazione visualizzata. Per le applicazioni di produzione, ti consigliamo di utilizzare slot dedicati per l'elaborazione dell'indice.

Utilizza la tua prenotazione

Anziché utilizzare il pool di slot condiviso predefinito, puoi scegliere di designare la tua prenotazione per indicizzare le tabelle. L'utilizzo della tua prenotazione garantisce le prestazioni prevedibili e coerenti dei job di gestione degli indici, come creazione, aggiornamento e ottimizzazioni dello sfondo.

  • Non ci sono limiti di dimensioni delle tabelle quando un job di indicizzazione viene eseguito nel tuo prenotazione.
  • L'utilizzo della tua prenotazione ti offre flessibilità nella gestione dell'indice. Se devi creare un indice di grandi dimensioni o eseguire un aggiornamento importante 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 la CREATE ASSIGNMENT Istruzione DDL.

  1. Nella console Google Cloud, vai alla pagina BigQuery.

    Vai a BigQuery

  2. Nell'editor di 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 prenotazione
    • LOCATION: il località della prenotazione
    • RESERVATION_NAME: il nome della prenotazione

    • ASSIGNMENT_ID: l'ID del compito

      L'ID deve essere univoco per il progetto e la località, iniziano e terminano 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.

  3. 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 che possiede la risorsa di prenotazione
  • LOCATION: il località della prenotazione
  • RESERVATION_NAME: il nome della prenotazione
  • PROJECT_ID: l'ID del progetto da assegnare a questa prenotazione

Visualizzare i job di indicizzazione

Ogni volta che viene creato o aggiornato un indice su una singola tabella, viene creato un nuovo job di indicizzazione. Per visualizzare le informazioni sul job, esegui una query INFORMATION_SCHEMA.JOBS* visualizzazioni. Puoi filtrare i job di indicizzazione impostando job_type IS NULL AND SEARCH(job_id, '`search_index`') nella clausola WHERE della query. L'esempio seguente elenca 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 tua prenotazione, devi considerare quando vengono eseguiti i job di gestione degli indici, quanti slot utilizzano e quale nel tempo. BigQuery attiva un job di gestione degli indici nelle seguenti situazioni:

  • Crei un indice in 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 degli indici in una tabella dipende dai seguenti fattori:

  • Le dimensioni della tabella
  • La velocità di importazione dati nella tabella
  • La frequenza delle istruzioni DML applicate alla tabella
  • Il ritardo accettabile per la creazione e la gestione dell'indice
  • La complessità dell'indice, solitamente determinata dagli attributi dei dati, ad esempio il numero di termini duplicati
Stima iniziale

Le seguenti stime possono aiutarti a capire approssimativamente quanti slot richiede la tua prenotazione. A causa della natura altamente variabile dei carichi di lavoro di indicizzazione, devi rivalutare i requisiti dopo aver iniziato a indicizzare i dati.

  • Dati esistenti: con una prenotazione da 1000 slot, una tabella esistente in BigQuery possono essere indicizzati a una tariffa media massima di 4 GiB al secondo, ovvero circa 336 TiB al giorno.
  • Dati appena importati: l'indicizzazione in genere richiede più risorse importati, poiché la tabella e il relativo indice vengono sottoposti a diversi cicli di ottimizzazioni rivoluzionarie. In media, l'indicizzazione di nuovi i dati importati consumano il triplo delle risorse rispetto alla quantità dell'indicizzazione di backfill degli stessi dati.
  • Dati modificati raramente: tabelle indicizzate con pochi o nessun dato modifiche richiedono molte meno risorse per un indice continuo manutenzione. 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 varia approssimativamente in modo lineare in base alle dimensioni della prenotazione. Tuttavia, sconsigliamo di utilizzare prenotazioni di dimensioni inferiori di oltre 250 slot per l'indicizzazione, in quanto ciò potrebbe causare inefficienze l'avanzamento dell'indicizzazione è lento.
  • Queste stime possono variare in base alle funzionalità, alle ottimizzazioni e al tuo utilizzo effettivo.
  • Se le dimensioni totali della tabella della tua organizzazione superano il limite di indicizzazione della regione, devi mantenere una prenotazione non pari a zero assegnata per l'indicizzazione. In caso contrario, l'indicizzazione potrebbe al livello predefinito, con conseguente eliminazione involontaria di tutti gli indici.
Monitorare l'utilizzo e i progressi

Il modo migliore per valutare il numero di slot necessari per eseguire in modo efficiente i job di gestione degli indici è monitorare l'utilizzo degli slot e modificare di conseguenza le dimensioni della prenotazione. La seguente query restituisce l'utilizzo giornaliero degli slot per i job di gestione degli indici. Nella regione us-west1 sono inclusi solo gli ultimi 30 giorni:

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 gli slot non sono sufficienti per eseguire job di gestione dell'indice, un indice può non saranno sincronizzati con la relativa tabella e i job di indicizzazione potrebbero non riuscire. In questo caso, BigQuery ricostruisce l'indice da zero. Per evitare di avere un indice non sincronizzato, assicurati di disporre di slot sufficienti per supportare gli aggiornamenti dell'indice dall'importazione e dall'ottimizzazione dei dati. Per ulteriori informazioni monitorare l'utilizzo degli slot, 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 che non intendi mai utilizzare con la funzione SEARCH o con altre funzioni e operatori supportati.
  • Fai attenzione quando crei un indice di ricerca su ALL COLUMNS. Ogni volta che aggiungi una colonna contenente dati STRING o JSON, questa viene indicizzata.
  • Dovresti 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 degli indici, quindi si applicano i limiti di ridimensionamento per organizzazione.

Eliminare un indice di ricerca

Quando non hai più bisogno di un indice di ricerca o vuoi modificare le colonne indicizzati in una tabella, puoi eliminare l'indice attualmente presente nella tabella. Utilizza la DROP SEARCH INDEX Istruzione DDL.

Se una tabella indicizzata viene eliminata, il relativo indice viene eliminato automaticamente.

Esempio:

DROP SEARCH INDEX my_index ON dataset.simple_table;

Passaggi successivi