LinkedIn
Twitter
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.
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.
SELECT * FROM Logs WHERE SEARCH(Logs, '`94.60.64.181`');+---------+--------------+---------------------------------+
| Level | Source | Message |
+---------+--------------+---------------------------------+
| INFO | 94.60.64.181 | Foo-Bar |
| SEVERE | 4.113.82.10 | Foo-Bar deleted by 94.60.64.181 |
+---------+--------------+---------------------------------+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
sql_always_where:
{% if log_raw_data.big_search_filter._in_query %}
SEARCH(log_raw_data,"`{% parameter netflow_log_raw_data.big_search_filter %}`")
{% else %}
1=1
{% 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.
