Cerca dati indicizzati
Questa pagina fornisce esempi di ricerche in BigQuery.
Quando indicizzi i tuoi 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 dati non sono ancora stati indicizzati. Tuttavia, le prestazioni delle query possono essere notevolmente migliorate con un indice. Il risparmio in byte elaborati e millisecondi di slot è massimizzato quando il numero di risultati di ricerca rappresenta una frazione relativamente piccola delle righe totali della tabella perché vengono analizzati meno dati. Per determinare se un indice è stato utilizzato per una query, consulta Utilizzo dell'indice di ricerca.
Crea un indice di ricerca
La seguente tabella denominata Logs
viene utilizzata per mostrare
diversi modi di utilizzare la funzione SEARCH
. Questa tabella di esempio è piuttosto piccola, ma in
pratica i miglioramenti in termini di rendimento 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 del testo predefinito:
CREATE SEARCH INDEX my_index ON my_dataset.Logs(ALL COLUMNS);
Per maggiori informazioni sugli indici di ricerca, consulta la pagina Gestione degli indici di ricerca.
Usare la funzione SEARCH
La funzione SEARCH
fornisce la ricerca tokenizzata sui dati.
SEARCH
è progettato per essere utilizzato con un indice per ottimizzare le ricerche.
Puoi utilizzare la funzione SEARCH
per eseguire ricerche 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 che lo contengono, indipendentemente dall'uso delle maiuscole. Poiché l'indice di ricerca utilizza lo strumento di analisi del 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. L'accento grave consente di eseguire una ricerca esatta, motivo per cui l'ultima riga della tabella Logs
, che contiene 181.94.60.64
, è stata 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 un sottoinsieme di colonne
SEARCH
semplifica la specifica di un sottoinsieme di colonne all'interno delle quali cercare 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 cerca in entrambe le colonne Source
e Message
della
tabella Logs
. Restituisce le righe che contengono il valore 94.60.64.181
da
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 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
ad eccezione della colonna Message
. Restituisce le righe di qualsiasi colonna diversa da Message
che contiene 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 analizzatore di testo diverso
L'esempio seguente crea una tabella denominata contact_info
con un indice che utilizza l'analizzatore di testo di NO_OP_ANALYZER
:
CREATE TABLE my_dataset.contact_info (name STRING, email STRING) AS ( SELECT 'Kim Lee' AS name, '[email protected]' AS email UNION ALL SELECT 'Kim' AS name, '[email protected]' AS email UNION ALL SELECT 'Sasha' AS name, '[email protected]' AS email ); CREATE SEARCH INDEX noop_index ON my_dataset.contact_info(ALL COLUMNS) OPTIONS (analyzer = 'NO_OP_ANALYZER');
+---------+---------------------+ | name | email | +---------+---------------------+ | Kim Lee | [email protected] | | Kim | [email protected] | | Sasha | [email protected] | +---------+---------------------+
La seguente query cerca Kim
nella colonna name
e kim
nella colonna email
.
Poiché l'indice di ricerca non utilizza lo strumento di analisi del 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 solo TRUE
per le corrispondenze esatte:
+---------+----------+---------------------+-----------+ | name | name_Kim | email | email_kim | +---------+----------+---------------------+-----------+ | Kim Lee | FALSE | [email protected] | FALSE | | Kim | TRUE | [email protected] | FALSE | | Sasha | FALSE | [email protected] | FALSE | +---------+----------+---------------------+-----------+
Configura opzioni 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 del testo LOG_ANALYZER
. che 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 analizzatori di testo diversi e relativi risultati. La prima tabella chiama la funzione SEARCH
utilizzando l'analizzatore di testo predefinito, LOG_ANALYZER
:
Chiamata funzione | Restituisce | Motivo |
---|---|---|
SEARCH('foobarexample', NULL) | ERRORE | Il valore di search_terms è "NULL". |
SEARCH('foobarexample', '') | ERRORE | Il parametro search_terms non contiene token. |
SEARCH('foobar-example', 'foobar example') | TRUE | "-" e " " sono delimitatori. |
SEARCH('foobar-example', 'foobarexample') | FALSE | search_terms non viene suddiviso. |
SEARCH('foobar-example', 'foobar\\&example') | TRUE | La doppia barra rovesciata fa precedere da escape la e commerciale, che rappresenta un delimitatore. |
SEARCH('foobar-example', R'foobar\&example') | TRUE | La singola barra rovesciata esegue l'escape 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 | È stata trovata una corrispondenza esatta. |
SEARCH('foobar-example', 'example foobar') | TRUE | L'ordine dei termini non ha importanza. |
SEARCH('foobar-example', 'foobar example') | TRUE | I token utilizzano lettere minuscole. |
SEARCH('foobar-example', ''foobar-example`') | TRUE | È stata trovata una corrispondenza esatta. |
SEARCH('foobar-example', '`foobar`') | FALSE | Gli apici inversi conservano le lettere maiuscole. |
SEARCH(''foobar-example'', ''foobar-example'') | FALSE | Gli apici inversi non hanno un significato speciale per data_to_search e |
SEARCH('[email protected]', '`example.com`') | TRUE | Viene rilevata una corrispondenza esatta dopo il delimitatore in data_to_search. |
SEARCH('a foobar-example b', '`foobar-example`') | TRUE | Viene rilevata una corrispondenza esatta tra i delimitatori di spazio. |
SEARCH(['foobar', 'example'], 'foobar example') | FALSE | Nessuna voce dell'array singola corrisponde a tutti i termini di ricerca. |
SEARCH('foobar=', '`foobar\\=`') | FALSE | 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 | È stata trovata una corrispondenza esatta. |
SEARCH('foobar.example', '`foobar.`') | FALSE | "foobar." non viene analizzato a causa dell'accento grave; non |
SEARCH('foobar..example', '`foobar.`') | TRUE | "foobar." non viene analizzato a causa dell'accento grave; viene seguito |
La seguente tabella mostra esempi di chiamate alla funzione SEARCH
utilizzando lo strumento
di analisi del testo NO_OP_ANALYZER
e motivi per i vari valori restituiti:
Chiamata funzione | Restituisce | Motivo |
---|---|---|
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') | TRUE | È stata trovata una corrispondenza esatta. |
SEARCH('foobar', '`foobar`', analyzer=>'NO_OP_ANALYZER') | FALSE | Gli accenti 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 vari operatori, funzioni e predicati.
Ottimizza con operatori e funzioni di confronto
BigQuery può ottimizzare alcune query che utilizzano
l'operatore uguale
(=
),
l'operatore IN
,
l'operatore LIKE
o
STARTS_WITH
la funzione
per confrontare i valori letterali stringa con i dati indicizzati.
Ottimizza con predicati di stringhe
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
Per ricevere assistenza durante l'anteprima, invia un'email all'indirizzo [email protected].
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 determinate funzioni vengono applicate ai dati indicizzati.
Se l'indice di ricerca utilizza lo strumento di analisi del testo LOG_ANALYZER
predefinito, puoi applicare le funzioni UPPER
o LOWER
alla colonna, ad esempio UPPER(column_name) = 'STRING_LITERAL'
.
Per i dati della stringa scalare JSON
estratti da una colonna JSON
indicizzata, puoi
applicare la funzione
STRING
o la relativa versione sicura,
SAFE.STRING
.
Se il valore JSON
estratto non è una stringa, la funzione STRING
genera un errore e la funzione SAFE.STRING
restituisce NULL
.
Per i dati STRING
indicizzati
in formato JSON (non JSON
), puoi applicare le seguenti
funzioni:
Ad esempio, supponi di avere la seguente tabella indicizzata denominata
dataset.person_data
con una colonna JSON
e una STRING
:
+----------------------------------------------------------------+-----------------------------------------+ | json_column | string_column | +----------------------------------------------------------------+-----------------------------------------+ | { "name" : "Ariel", "email" : "[email protected]" } | { "name" : "Ariel", "job" : "doctor" } | +----------------------------------------------------------------+-----------------------------------------+
Le seguenti query sono idonee all'ottimizzazione:
SELECT * FROM dataset.person_data WHERE SAFE.STRING(json_column.email) = '[email protected]';
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 per una query è stato utilizzato un indice di ricerca, consulta le Informazioni sul job della query in Risultati delle query. I campi Modalità di utilizzo dell'indice e Motivi del mancato utilizzo dell'indice forniscono informazioni dettagliate sull'utilizzo dell'indice di ricerca.
Puoi trovare informazioni sull'utilizzo dell'indice di ricerca 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 utilizzava indici di ricerca, mentre parte no.FULLY_USED
: ogni funzioneSEARCH
nella 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 chiami la funzione SEARCH
sui dati di una tabella. Puoi visualizzare i dettagli del job della query per trovare l'ID del 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 è simile 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 indexUnusedReason
campo code
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 per l'utilizzo della funzione SEARCH
.
Cerca 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
su una tabella partitioned molto grande 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 dal fatto
che la tabella sia indicizzata o meno. Questo approccio è ideale se si cerca un termine comune.
Ad esempio, supponi che la tabella Logs
creata in precedenza
sia partizionata in una colonna aggiuntiva di tipo DATE
chiamata 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.
Usa apici inversi
Quando utilizzi la funzione SEARCH
con lo strumento di analisi del testo LOG_ANALYZER
,
includere la query di ricerca tra apici inversi
forza una corrispondenza esatta. Questo è 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 l'accento grave, la ricerca restituisce
qualsiasi riga contenente i singoli token 192
, 0
, 2
e 1
, in
qualsiasi ordine.