Using BigSearch in Looker to search across tables and columns

  • 11 May 2022
  • 1 reply

Userlevel 1

Google BigQuery recently released a new feature called BigSearch, which allows customers to rapidly find data points without having to scan all the text in their table or even know which column the data resides in. This functionality can be extremely useful when working with log data, or other unstructured/semi-structured datasets that require searching for specific strings and values (like IP addresses).

This article focuses on how to bring that capability to your Looker dashboards and explores.

BigSearch allows Looker users to search across all columns in a table for specific values

How to set it up

1. Create the search filter parameter

Inside the lookml view file for the table that you’d like to search on, create a new parameter field, which will be used to accept the user inputted search values on a dashboard or explore.

parameter: big_search_filter {
suggestable: no
type: unquoted


2. Add `sql_always_where` to your Explore 

Next find the explore that you’d like enable the searching on, and add conditional `sql_always_where` statement to it.

Google BigQuery leverages the below SQL SEARCH() function syntax, which in this example is doing an exact search.

| Level | Source | Message |
| INFO | | Foo-Bar |
| SEVERE | | Foo-Bar deleted by |

To invoke the same WHERE clause syntax in a Looker query, we can add a sql_always_where parameter to our explore. However, we only want to invoke the search filter syntax on queries that are using the previously built `big_search_filter`. So we are going to use liquid conditional logic to only add the syntax when needed.

explore: log_raw_data {

# for BigSearch filter - the below syntax applies the correct BQ syntax,
# but only if the filter is being used in the query
{% if log_raw_data.big_search_filter._in_query %}
SEARCH(log_raw_data,"`{% parameter netflow_log_raw_data.big_search_filter %}`")
{% else %}
{% endif %} ;;

That’s it! 

The above steps enable searching on one table. If you’d like to search across multiple tables, simply repeat the above steps for each table and then add a query from each table to a single dashboard. The single dashboard will be able to use one dashboard filter to apply searching to multiple tables.


This topic has been closed for comments

1 reply

Userlevel 7
Badge +1

Thanks for the news @Eric_Hutcheson seems like a great addition and could come handy in Looker too!