Cerca dati indicizzati

Questa pagina fornisce esempi di ricerca in BigQuery. Quando indicizzi i dati, BigQuery può ottimizzare alcune query che utilizzano la funzione SEARCH o altri funzioni e operatori, come =, IN, LIKE e STARTS_WITH.

Le query SQL restituiscono risultati corretti da tutti i dati importati, anche se alcuni non sono ancora indicizzati. Tuttavia, le prestazioni delle query possono essere notevolmente migliorate con un indice. Il risparmio in byte elaborati e millisecondi di slot vengono massimizzati quando il numero di risultati di ricerca rappresenta una frazione relativamente piccola delle righe totali nella tabella, perché viene analizzata una quantità minore di dati. Per determinare se è stato utilizzato un indice per una query, vedi Utilizzo dell'indice di ricerca.

Crea un indice di ricerca

La seguente tabella denominata Logs viene utilizzata per mostrare i diversi modi di utilizzo della funzione SEARCH. Questa tabella di esempio è piuttosto piccola, ma in pratica i guadagni in termini di prestazioni che ottieni con SEARCH migliorano con le dimensioni della tabella.

CREATE TABLE my_dataset.Logs (Level STRING, Source STRING, Message STRING)
AS (
  SELECT 'INFO' as Level, '65.177.8.234' as Source, 'Entry Foo-Bar created' as Message
  UNION ALL
  SELECT 'WARNING', '132.249.240.10', 'Entry Foo-Bar already exists, created by 65.177.8.234'
  UNION ALL
  SELECT 'INFO', '94.60.64.181', 'Entry Foo-Bar deleted'
  UNION ALL
  SELECT 'SEVERE', '4.113.82.10', 'Entry Foo-Bar does not exist, deleted by 94.60.64.181'
  UNION ALL
  SELECT 'INFO', '181.94.60.64', 'Entry Foo-Baz created'
);

La tabella è simile alla seguente:

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 65.177.8.234   | Entry Foo-Bar created                                 |
| WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 |
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
| INFO    | 181.94.60.64   | Entry Foo-Baz created                                 |
+---------+----------------+-------------------------------------------------------+

Crea un indice di ricerca nella tabella Logs utilizzando lo strumento di analisi di testo predefinito:

CREATE SEARCH INDEX my_index ON my_dataset.Logs(ALL COLUMNS);

Per saperne di più sugli indici di ricerca, consulta Gestire gli indici di ricerca.

Utilizzare la funzione SEARCH

La funzione SEARCH fornisce la ricerca tokenizzata nei dati. SEARCH è progettato per essere utilizzato con un indice al fine di ottimizzare le ricerche. Puoi utilizzare la funzione SEARCH per cercare in un'intera tabella o limitare la ricerca a colonne specifiche.

Cerca in un'intera tabella

La seguente query cerca il valore bar in tutte le colonne della tabella Logs e restituisce le righe contenenti questo valore, a prescindere dall'utilizzo delle maiuscole. Poiché l'indice di ricerca utilizza lo strumento di analisi di testo predefinito, non è necessario specificarlo nella funzione SEARCH.

SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, 'bar');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 65.177.8.234   | Entry Foo-Bar created                                 |
| WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 |
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

La seguente query cerca il valore `94.60.64.181` in tutte le colonne della tabella Logs e restituisce le righe che contengono questo valore. Gli apici inversi consentono una ricerca esatta, motivo per cui l'ultima riga della tabella Logs che contiene 181.94.60.64 viene omessa.

SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, '`94.60.64.181`');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

Cerca in un sottoinsieme di colonne

SEARCH semplifica la specifica di un sottoinsieme di colonne all'interno della quale cercare i dati. La seguente query cerca il valore 94.60.64.181 nella colonna Message della tabella Logs e restituisce le righe che contengono questo valore.

SELECT * FROM my_dataset.Logs WHERE SEARCH(Message, '`94.60.64.181`');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

La seguente query esegue la ricerca nelle colonne Source e Message della tabella Logs. Restituisce le righe che contengono il valore 94.60.64.181 di una delle colonne.

SELECT * FROM my_dataset.Logs WHERE SEARCH((Source, Message), '`94.60.64.181`');

+---------+----------------+-------------------------------------------------------+
| Level   | Source         | Message                                               |
+---------+----------------+-------------------------------------------------------+
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                                 |
| SEVERE  | 4.113.82.10    | Entry Foo-Bar does not exist, deleted by 94.60.64.181 |
+---------+----------------+-------------------------------------------------------+

Escludi colonne da una ricerca

Se una tabella di tabella ha molte colonne e vuoi cercarne la maggior parte, potrebbe essere più semplice specificare solo le colonne da escludere dalla ricerca. La seguente query cerca in tutte le colonne della tabella Logs tranne che nella colonna Message. Restituisce le righe di tutte le colonne diverse da Message che contengono il valore 94.60.64.181.

SELECT *
FROM my_dataset.Logs
WHERE SEARCH(
  (SELECT AS STRUCT Logs.* EXCEPT (Message)), '`94.60.64.181`');

+---------+----------------+---------------------------------------------------+
| Level   | Source         | Message                                           |
+---------+----------------+---------------------------------------------------+
| INFO    | 94.60.64.181   | Entry Foo-Bar deleted                             |
+---------+----------------+---------------------------------------------------+

Usa un altro strumento di analisi del testo

L'esempio seguente crea una tabella denominata contact_info con un indice che utilizza lo analizzatore di testo NO_OP_ANALYZER:

CREATE TABLE my_dataset.contact_info (name STRING, email STRING)
AS (
  SELECT 'Kim Lee' AS name, '[e-mail]' AS email
  UNION ALL
  SELECT 'Kim' AS name, '[e-mail]' AS email
  UNION ALL
  SELECT 'Sasha' AS name, '[e-mail]' AS email
);
CREATE SEARCH INDEX noop_index ON my_dataset.contact_info(ALL COLUMNS)
OPTIONS (analyzer = 'NO_OP_ANALYZER');

+---------+---------------------+
| name    | email               |
+---------+---------------------+
| Kim Lee | [e-mail] |
| Kim     | [e-mail]     |
| Sasha   | [e-mail]   |
+---------+---------------------+

La seguente query cerca Kim nella colonna name e kim nella colonna email. Poiché l'indice di ricerca non utilizza lo strumento di analisi di testo predefinito, devi passare il nome dell'analizzatore alla funzione SEARCH.

SELECT
  name,
  SEARCH(name, 'Kim', analyzer=>'NO_OP_ANALYZER') AS name_Kim,
  email,
  SEARCH(email, 'kim', analyzer=>'NO_OP_ANALYZER') AS email_kim
FROM
  my_dataset.contact_info;

NO_OP_ANALYZER non modifica il testo, quindi la funzione SEARCH restituisce TRUE solo per le corrispondenze esatte:

+---------+----------+---------------------+-----------+
| name    | name_Kim | email               | email_kim |
+---------+----------+---------------------+-----------+
| Kim Lee | FALSE    | [e-mail] | FALSE     |
| Kim     | TRUE     | [e-mail]     | FALSE     |
| Sasha   | FALSE    | [e-mail]   | FALSE     |
+---------+----------+---------------------+-----------+

Configura le opzioni dello strumento di analisi del testo

Gli analizzatori di testo LOG_ANALYZER e PATTERN_ANALYZER possono essere personalizzati aggiungendo una stringa in formato JSON alle opzioni di configurazione. Puoi configurare gli analizzatori di testo nella funzione SEARCH, nell'istruzione DDL CREATE SEARCH INDEX e nella funzione TEXT_ANALYZE.

L'esempio seguente crea una tabella denominata complex_table con un indice che utilizza l'analizzatore di testo LOG_ANALYZER. Usa una stringa in formato JSON per configurare le opzioni dell'analizzatore:

CREATE TABLE dataset.complex_table(
  a STRING,
  my_struct STRUCT<string_field STRING, int_field INT64>,
  b ARRAY<STRING>
);

CREATE SEARCH INDEX my_index
ON dataset.complex_table(a, my_struct, b)
OPTIONS (analyzer = 'LOG_ANALYZER', analyzer_options = '''{
  "token_filters": [
    {
      "normalization": {"mode": "NONE"}
    }
  ]
}''');

Le seguenti tabelle mostrano esempi di chiamate alla funzione SEARCH con diversi analizzatori di testo e i relativi risultati. La prima tabella chiama la funzione SEARCH utilizzando lo strumento di analisi di testo predefinito, LOG_ANALYZER:

Chiamata funzione	Restituisce	Motivo
SEARCH('foobarexample', NULL)	ERRORE	Il parametro search_terms è "NULL".
SEARCH('foobarexample', '')	ERRORE	search_terms non contiene token.
SEARCH('foobar-example', 'foobar esempio')	TRUE	'-' e ' ' sono delimitatori.
SEARCH('foobar-example', 'foobarexample')	FALSE	I termini search_terms non sono suddivisi.
SEARCH('foobar-example', 'foobar\\&example')	TRUE	La doppia barra rovesciata fa precedere la e commerciale che è un delimitatore.
SEARCH('foobar-example', R'foobar\&example')	TRUE	La singola barra rovesciata esegue l'interpretazione letterale della "e commerciale" in una stringa non elaborata.
SEARCH('foobar-example', '`foobar&example`')	FALSE	Gli apici inversi richiedono una corrispondenza esatta per foobar&example.
SEARCH('foobar&example', '`foobar&example`')	TRUE	Viene trovata una corrispondenza esatta.
SEARCH('foobar-example', 'example foobar')	TRUE	L'ordine dei termini non ha importanza.
SEARCH('foobar-example', 'foobar esempio')	TRUE	I token sono in minuscolo.
SEARCH('foobar-example', '`foobar-example`')	TRUE	Viene trovata una corrispondenza esatta.
SEARCH('foobar-example', '`foobar`')	FALSE	Gli apici inversi mantengono le lettere maiuscole.
SEARCH('`foobar-example`', '`foobar-example`')	FALSE	Gli apici inversi non hanno un significato speciale per data_to_search e
SEARCH('[e-mail]', '`example.com`')	TRUE	Viene trovata una corrispondenza esatta dopo il delimitatore in data_to_search.
SEARCH('a foobar-example b', '`foobar-example`')	TRUE	Viene trovata una corrispondenza esatta tra i delimitatori di spazio.
SEARCH(['foobar', 'esempio']; 'foobar esempio')	FALSE	Nessuna singola voce dell'array corrisponde a tutti i termini di ricerca.
SEARCH('foobar=', '`foobar\\=`')	FALSE	Il parametro search_terms equivale a foobar\=.
SEARCH('foobar=', R'`foobar\=`')	FALSE	Equivale all'esempio precedente.
SEARCH('foobar=', 'foobar\\=')	TRUE	Il segno di uguale è un delimitatore nei dati e nella query.
SEARCH('foobar=', R'foobar\=')	TRUE	Equivale all'esempio precedente.
SEARCH('foobar.example', '`foobar`')	TRUE	Viene trovata una corrispondenza esatta.
SEARCH('foobar.example', '`foobar.`')	FALSE	"foobar." non viene analizzato a causa di apici inversi; non viene
SEARCH('foobar..example', '`foobar.`')	TRUE	"foobar." non viene analizzato a causa di apici inversi; viene seguito

La tabella seguente mostra esempi di chiamate alla funzione SEARCH mediante l'analizzatore di testo NO_OP_ANALYZER e i motivi dei vari valori restituiti:

Chiamata funzione	Restituisce	Motivo
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER')	TRUE	Viene trovata una corrispondenza esatta.
SEARCH('foobar', '`foobar`', analyzer=>'NO_OP_ANALYZER')	FALSE	Gli apici inversi non sono caratteri speciali per NO_OP_ANALYZER.
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER')	FALSE	Le lettere maiuscole non corrispondono.
SEARCH('foobar example', 'foobar', analyzer=>'NO_OP_ANALYZER')	FALSE	Non sono presenti delimitatori per NO_OP_ANALYZER.
SEARCH('', '', analyzer=>'NO_OP_ANALYZER')	TRUE	Non sono presenti delimitatori per NO_OP_ANALYZER.

Altri operatori e funzioni

Puoi ottimizzare l'indice di ricerca con diversi operatori, funzioni e predicati.

Ottimizzare con operatori e funzioni di confronto

BigQuery può ottimizzare alcune query che utilizzano l'operatore uguale (=), IN, l'operatore LIKE o la funzione STARTS_WITH per confrontare i valori letterali stringa con i dati indicizzati.

Ottimizza con predicati di stringa

I seguenti predicati sono idonei per l'ottimizzazione dell'indice di ricerca:

• column_name = 'string_literal'
• 'string_literal' = column_name
• struct_column.nested_field = 'string_literal'
• string_array_column[OFFSET(0)] = 'string_literal'
• string_array_column[ORDINAL(1)] = 'string_literal'
• column_name IN ('string_literal1', 'string_literal2', ...)
• STARTS_WITH(column_name, 'prefix')
• column_name LIKE 'prefix%'

Ottimizza con predicati numerici

Anteprima

Questo prodotto o funzionalità è soggetto ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici del servizio. I prodotti e le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

Per ricevere assistenza durante l'anteprima, invia un'email a [e-mail].

Se l'indice di ricerca è stato creato con tipi di dati numerici, BigQuery può ottimizzare alcune query che utilizzano l'operatore uguale (=) o IN con i dati indicizzati. I seguenti predicati sono idonei per l'ottimizzazione dell'indice di ricerca:

• INT64(json_column.int64_field) = 1
• int64_column = 1
• int64_array_column[OFFSET(0)] = 1
• int64_column IN (1, 2)
• struct_column.nested_int64_field = 1
• struct_column.nested_timestamp_field = TIMESTAMP "2024-02-15 21:31:40"
• timestamp_column = "2024-02-15 21:31:40"
• timestamp_column IN ("2024-02-15 21:31:40", "2024-02-16 21:31:40")

Ottimizza le funzioni che producono dati indicizzati

BigQuery supporta l'ottimizzazione dell'indice di ricerca quando ai dati indicizzati vengono applicate determinate funzioni. Se l'indice di ricerca utilizza lo strumento di analisi di testo LOG_ANALYZER predefinito, puoi applicare le funzioni UPPER o LOWER alla colonna, ad esempio UPPER(column_name) = 'STRING_LITERAL'.

Per i dati di stringa scalare JSON estratti da una colonna JSON indicizzata, puoi applicare la funzione STRING o la sua versione sicura SAFE.STRING. Se il valore di JSON estratto non è una stringa, la funzione STRING produce un errore e la funzione SAFE.STRING restituisce NULL.

Per i dati STRING indicizzati in formato JSON (non JSON), puoi applicare le seguenti funzioni:

• JSON_EXTRACT
• JSON_EXTRACT_SCALAR
• JSON_QUERY
• JSON_VALUE

Ad esempio, supponi di avere la seguente tabella indicizzata denominata dataset.person_data con una colonna JSON e una colonna STRING:

+----------------------------------------------------------------+-----------------------------------------+
| json_column                                                    | string_column                           |
+----------------------------------------------------------------+-----------------------------------------+
| { "name" : "Ariel", "email" : "[e-mail]" } | { "name" : "Ariel", "job" : "doctor" }  |
+----------------------------------------------------------------+-----------------------------------------+

Le seguenti query sono idonee per l'ottimizzazione:

SELECT * FROM dataset.person_data
WHERE SAFE.STRING(json_column.email) = '[e-mail]';

SELECT * FROM dataset.person_data
WHERE JSON_VALUE(string_column, '$.job') IN ('doctor', 'lawyer', 'teacher');

Anche le combinazioni di queste funzioni sono ottimizzate, ad esempio UPPER(JSON_VALUE(json_string_expression)) = 'FOO'.

Utilizzo dell'indice di ricerca

Per determinare se è stato utilizzato un indice di ricerca per una query, guarda le Informazioni job della query in Risultati query. I campi Modalità di utilizzo dell'indice e Motivi non utilizzati dell'indice forniscono informazioni dettagliate sull'utilizzo dell'indice di ricerca.

Le informazioni sull'utilizzo dell'indice di ricerca sono disponibili anche tramite il campo searchStatistics nel metodo API Jobs.Get. Il campo indexUsageMode in searchStatistics indica se è stato utilizzato un indice di ricerca con i seguenti valori:

• UNUSED: non è stato utilizzato alcun indice di ricerca.
• PARTIALLY_USED: parte della query ha utilizzato indici di ricerca e parte no.
• FULLY_USED: ogni funzione SEARCH della query ha utilizzato un indice di ricerca.

Quando indexUsageMode è UNUSED o PARTIALLY_USED, il campo indexUnusuedReasons contiene informazioni sul motivo per cui gli indici di ricerca non sono stati utilizzati nella query.

Per visualizzare searchStatistics per una query, esegui il comando bq show.

bq show --format=prettyjson -j JOB_ID

Esempio

Supponi di eseguire una query che chiama la funzione SEARCH sui dati di una tabella. Puoi visualizzare i dettagli job della query per trovare l'ID job, quindi eseguire il comando bq show per visualizzare ulteriori informazioni:

bq show --format=prettyjson --j my_project:US.bquijob_123x456_789y123z456c

L'output contiene molti campi, tra cui searchStatistics, che sono simili al seguente. In questo esempio, indexUsageMode indica che l'indice non è stato utilizzato. Il motivo è che la tabella non ha un indice di ricerca. Per risolvere il problema, crea un indice di ricerca nella tabella. Consulta il campo code indexUnusedReason per un elenco di tutti i motivi per cui un indice di ricerca potrebbe non essere utilizzato in una query.

"searchStatistics": {
  "indexUnusedReasons": [
    {
      "baseTable": {
        "datasetId": "my_dataset",
        "projectId": "my_project",
        "tableId": "my_table"
      },
      "code": "INDEX_CONFIG_NOT_AVAILABLE",
      "message": "There is no search index configuration for the base table `my_project:my_dataset.my_table`."
    }
  ],
  "indexUsageMode": "UNUSED"
},

Best practice

Le seguenti sezioni descrivono le best practice relative all'utilizzo della funzione SEARCH.

Effettua ricerche in modo selettivo

La ricerca funziona meglio quando la ricerca ha pochi risultati. Rendi le tue ricerche il più specifiche possibile.

Ottimizzazione ORDER BY LIMIT

Le query che utilizzano SEARCH, =, IN, LIKE o STARTS_WITH in una tabella partitioned di grandi dimensioni possono essere ottimizzate quando utilizzi una clausola ORDER BY nel campo partizionato e una clausola LIMIT. Per le query che non contengono la funzione SEARCH, puoi utilizzare gli altri operatori e funzioni per sfruttare l'ottimizzazione. L'ottimizzazione viene applicata indipendentemente dall'indicizzazione della tabella. Funziona bene se stai cercando un termine comune. Ad esempio, supponiamo che la tabella Logs creata in precedenza sia partizionata in una colonna aggiuntiva di tipo DATE denominata day. La seguente query è ottimizzata:

SELECT
  Level, Source, Message
FROM
  my_dataset.Logs
WHERE
  SEARCH(Message, "foo")
ORDER BY
  day
LIMIT 10;

Definisci l'ambito della ricerca

Quando utilizzi la funzione SEARCH, cerca solo le colonne della tabella che prevedi contengano i termini di ricerca. Questo migliora le prestazioni e riduce il numero di byte da analizzare.

Utilizzare l'accento grave

Quando utilizzi la funzione SEARCH con l'analizzatore di testo LOG_ANALYZER, includendo la query di ricerca tra apici inversi viene forzata una corrispondenza esatta. Ciò è utile se la ricerca è sensibile alle maiuscole o contiene caratteri che non devono essere interpretati come delimitatori. Ad esempio, per cercare l'indirizzo IP 192.0.2.1, utilizza `192.0.2.1`. Senza gli accenti inversi, la ricerca restituisce qualsiasi riga contenente i singoli token 192, 0, 2 e 1, in qualsiasi ordine.