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,
ad esempio =
, IN
, LIKE
e STARTS_WITH
.
Le query SQL restituiscono risultati corretti da tutti i dati importati, anche se dei dati non è ancora stato indicizzato. Tuttavia, le prestazioni delle query possono essere migliorato con un indice. Risparmi in byte elaborati e millisecondi slot vengono massimizzate quando il numero di ricerche i risultati costituiscono una frazione relativamente piccola delle righe totali della tabella perché vengono analizzati meno dati. Per determinare se è stato utilizzato un indice per una query, consulta Utilizzo dell'indice di ricerca.
Crea un indice di ricerca
La seguente tabella denominata Logs
viene utilizzata per mostrare
modi di utilizzare la funzione SEARCH
. Questa tabella di esempio è piuttosto piccola, ma
i miglioramenti in termini di prestazioni che ottieni con SEARCH
migliorano con la dimensione
nella 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 per
ottimizzare le ricerche.
Puoi utilizzare la funzione SEARCH
per eseguire ricerche in un'intera tabella o limitare
eseguire ricerche in colonne specifiche.
Cerca in un'intera tabella
La seguente query cerca in tutte le colonne della tabella Logs
i valori
bar
e restituisce le righe che contengono questo valore, indipendentemente da
maiuscole/minuscole. Poiché l'indice di ricerca utilizza lo strumento di analisi di testo predefinito,
devi 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 in tutte le colonne della tabella Logs
i valori
`94.60.64.181`
e restituisce le righe che contengono questo valore. La
gli apici inversi consentono una ricerca esatta, motivo per cui l'ultima riga dei 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 in cui cercare
e i dati di Google Cloud. La seguente query cerca nella colonna Message
della tabella Logs
il valore 94.60.64.181
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
da
entrambe le 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
specificare solo le colonne da escludere dalla ricerca. Le seguenti
la query effettua ricerche in tutte le colonne della tabella Logs
tranne che per
la colonna Message
. Restituisce le righe di tutte le colonne diverse 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 altro strumento di analisi del testo
Nell'esempio seguente viene creata una tabella denominata contact_info
con un indice che
utilizza NO_OP_ANALYZER
analizzatore di testo:
CREATE TABLE my_dataset.contact_info (name STRING, email STRING) AS ( SELECT 'Kim Lee' AS name, 'kim.lee@example.com' AS email UNION ALL SELECT 'Kim' AS name, 'kim@example.com' AS email UNION ALL SELECT 'Sasha' AS name, 'sasha@example.com' AS email ); CREATE SEARCH INDEX noop_index ON my_dataset.contact_info(ALL COLUMNS) OPTIONS (analyzer = 'NO_OP_ANALYZER');
+---------+---------------------+ | name | email | +---------+---------------------+ | Kim Lee | kim.lee@example.com | | Kim | kim@example.com | | Sasha | sasha@example.com | +---------+---------------------+
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
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 solo la funzione SEARCH
restituisce TRUE
per le corrispondenze esatte:
+---------+----------+---------------------+-----------+ | name | name_Kim | email | email_kim | +---------+----------+---------------------+-----------+ | Kim Lee | FALSE | kim.lee@example.com | FALSE | | Kim | TRUE | kim@example.com | FALSE | | Sasha | FALSE | sasha@example.com | FALSE | +---------+----------+---------------------+-----------+
Configura le opzioni dello strumento di analisi del testo
Il testo di LOG_ANALYZER
e PATTERN_ANALYZER
analizzatori
personalizzati aggiungendo una stringa in formato JSON alle opzioni di configurazione. Tu
può configurare analizzatori di testo in SEARCH
, il DDL CREATE
SEARCH INDEX
l'Informativa,
e TEXT_ANALYZE
.
Nell'esempio seguente viene creata una tabella denominata complex_table
con un indice che
utilizza lo strumento di analisi di testo LOG_ANALYZER
. Utilizza una stringa in formato JSON per
configurare le opzioni dello strumento di analisi:
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"} } ] }''');
La tabella seguente mostra esempi di chiamate alla funzione SEARCH
con
da diversi analizzatori di testo e i relativi risultati. La prima tabella chiama 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') | VERO | "-" e ' sono delimitatori. |
SEARCH('foobar-example', 'foobarexample') | FALSE | I termini search_terms non sono suddivisi. |
SEARCH('foobar-example', 'foobar\\&example') | VERO | La doppia barra rovesciata fa precedere la e commerciale che è un delimitatore. |
SEARCH('foobar-example', R'foobar\&example') | VERO | 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`') | VERO | Viene trovata una corrispondenza esatta. |
SEARCH('foobar-example', 'example foobar') | VERO | L'ordine dei termini non ha importanza. |
SEARCH('foobar-example', 'foobar esempio') | VERO | I token sono in minuscolo. |
SEARCH('foobar-example', '`foobar-example`') | VERO | 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('foobar@example.com', '`example.com`') | VERO | Viene trovata una corrispondenza esatta dopo il delimitatore in data_to_search. |
SEARCH('a foobar-example b', '`foobar-example`') | VERO | 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\\=') | VERO | Il segno di uguale è un delimitatore nei dati e nella query. |
SEARCH('foobar=', R'foobar\=') | VERO | Equivale all'esempio precedente. |
SEARCH('foobar.example', '`foobar`') | VERO | Viene trovata una corrispondenza esatta. |
SEARCH('foobar.example', '`foobar.`') | FALSE | "foobar." non viene analizzato a causa di apici inversi; non è |
SEARCH('foobar..example', '`foobar.`') | VERO | "foobar." non viene analizzato a causa di apici inversi; viene seguito |
La tabella seguente mostra esempi di chiamate alla funzione SEARCH
utilizzando il metodo
Analizzatore di testo NO_OP_ANALYZER
e motivi per i vari valori restituiti:
Chiamata funzione | Restituisce | Motivo |
---|---|---|
SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') | VERO | 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') | VERO | Non sono presenti delimitatori per NO_OP_ANALYZER. |
Altri operatori e funzioni
Puoi eseguire ottimizzazioni dell'indice di ricerca con diversi operatori, funzioni e predicati.
Ottimizzare con operatori e funzioni di confronto
BigQuery può ottimizzare alcune query che utilizzano
operatore uguale
(=
),
operatore IN
,
operatore LIKE
,
o
Funzione STARTS_WITH
per confrontare i valori letterali delle stringhe 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
Per ricevere assistenza durante l'anteprima, invia un'email bq-search-team@google.com.
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 dati indicizzati. I seguenti predicati sono idonei per l'indice di ricerca
ottimizzazione:
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
vengono applicate ai dati indicizzati.
Se l'indice di ricerca utilizza lo strumento di analisi di testo LOG_ANALYZER
predefinito, puoi:
applica il
UPPER
o LOWER
funzioni nella colonna, ad esempio UPPER(column_name) = 'STRING_LITERAL'
.
Per i dati di stringa scalare JSON
estratti da una colonna JSON
indicizzata, puoi
applica il
STRING
o la sua 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 indicizzati
Dati STRING
(non JSON
) con formattazione JSON, puoi applicare quanto segue
:
Ad esempio, supponiamo che tu abbia la seguente tabella indicizzata chiamata
dataset.person_data
con una colonna JSON
e una STRING
:
+----------------------------------------------------------------+-----------------------------------------+ | json_column | string_column | +----------------------------------------------------------------+-----------------------------------------+ | { "name" : "Ariel", "email" : "cloudysanfrancisco@gmail.com" } | { "name" : "Ariel", "job" : "doctor" } | +----------------------------------------------------------------+-----------------------------------------+
Le seguenti query sono idonee per l'ottimizzazione:
SELECT * FROM dataset.person_data WHERE SAFE.STRING(json_column.email) = 'cloudysanfrancisco@gmail.com';
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, controlla lo stato della query Informazioni job in Risultati query. La Modalità di utilizzo dell'indice e Motivi del mancato utilizzo dell'indice forniscono informazioni dettagliate sull'indice di ricerca all'utilizzo delle risorse.
Le informazioni sull'uso dell'indice di ricerca sono disponibili anche tramite la
Campo searchStatistics
nel metodo API Jobs.Get. La
Il campo indexUsageMode
in searchStatistics
indica se è presente un indice di ricerca
è stato utilizzato 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 funzioneSEARCH
della query ha utilizzato un indice di ricerca.
Quando indexUsageMode
è UNUSED
o PARTIALLY_USED
, 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. Tu
possono visualizzare
dettagli job della query
individua l'ID job, quindi esegui 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 cerca
in modo simile al seguente. In questo esempio, indexUsageMode
indica che
indice non utilizzato. Il motivo è che la tabella non ha un indice di ricerca. A
risolvere questo problema, crea un indice di ricerca nella
. Consulta le
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 relative all'utilizzo della funzione SEARCH
.
Effettua ricerche in modo selettivo
La ricerca funziona meglio quando la ricerca ha pochi risultati. Imposta le tue ricerche come più specifici possibile.
Ottimizzazione ORDER BY LIMIT
Query che utilizzano SEARCH
, =
, IN
, LIKE
o STARTS_WITH
su una dimensione
La tabella partizionata può essere ottimizzata
quando utilizzi una clausola ORDER BY
nel campo partizionato e una clausola LIMIT
.
Per le query che non contengono la funzione SEARCH
, puoi utilizzare il metodo
altri operatori e funzioni per
sfruttare l'ottimizzazione. L'ottimizzazione viene applicata indipendentemente dal fatto che
viene indicizzata. Funziona bene se stai cercando un termine comune.
Ad esempio, supponiamo che la tabella Logs
creata in precedenza
è partizionata in base a una colonna aggiuntiva di tipo DATE
chiamato 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
che prevedi di contenere i termini di ricerca. Ciò migliora le prestazioni
riduce il numero di byte da analizzare.
Utilizzare l'accento grave
Quando usi la funzione SEARCH
con l'analizzatore di testo LOG_ANALYZER
,
Racchiudi la query di ricerca tra apici inversi
forza una corrispondenza esatta. È 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
, usa `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.