Search Indexes in BigQuery lets you easily find unique data elements without having to know the table schemas in advance.
By using this feature we can create indexes on log tables and search for specific error codes or identifying the rows of tables that contain a specific user’s PII for GDPR reporting.
How to create search index on a table.
When you run below SQL statement it will create a table called sample_table with 2 rows. we will use this table for index demo
CREATE TABLE demo.sample_table(
fruit STRING,
count INT64,
source STRUCT <source_id INT64,
city ARRAY<STRING>,
state STRUCT<country STRING, continent_id INT64>>) AS
select * from (
SELECT 'organge' AS fruit, 100 AS count,(111, ['Austin', 'Texas'], ('US', 1)) AS source
UNION ALL
SELECT 'Avacado' AS fruit, 200 AS count,(121, ['London', 'London'], ('UK', 3)) AS source) Now we will create a index called simple_index on sample_table that we created with above SQL statement. In this example we are creating index on all columns
CREATE SEARCH INDEX sample_index ON `demo.sample_table` (ALL COLUMNS);
Search with an index
Now we are going to run SEARCH function to search the index that we created by using below SQL statement. This SQL statement searches “Avacado” data element.
SELECT * FROM `demo.sample_table` AS FRUITS WHERE SEARCH(FRUITS, "`Avacado`");
Get information about search indexes
By querying INFORMATION_SCHEMA, we can get the information of the index.
the following SQL statement prints table name on which index was created, index name and SQL statement used to create the index.
SELECT table_name, index_name, ddl, coverage_percentage FROM dataset_a.INFORMATION_SCHEMA.SEARCH_INDEXES
Deleting a Search Index
Now we are going to delete the search index that we created earlier.
DROP SEARCH INDEX sample_index ON demo.sample_table;
Note: If an indexed table is deleted, its index is deleted automatically.
Conclusion
In this quick start demo we have create a search index on a BigQuery table and used standard SQL to query search index for specific data element. You can find more information if GCP official documentation.
Other BigQuery related topics: