CODEWITHSUNDEEP
google-bigquerykeyword
Gopi Kantamneni
Gopi Kantamneni

Reputation: 19

How to search a key word in all columns of big query table?

I'm using the big query to see the data in my google cloud. I want to search a keyword in all columns of a particular table.

Ex: I'm searching for Dubai. I need the result of entries where ever the Dubai word present in any column.

Upvotes: 2

Views: 7210

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL and assumes column names do not contain search word (can be adjusted to address this too) 

#standardSQL
SELECT *
FROM `yourproject.yourdataset.yourtable` t
WHERE REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), r'dubai')

You can test / play with above using dummy data as below 

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT 1 id, 'Los Angeles' col1, 'New York' col2 UNION ALL
  SELECT 2, 'Dubai', 'San Francisco' UNION ALL
  SELECT 3, 'atlanta', 'dubai' UNION ALL
  SELECT 4, 'I love Dubai', 'Me too'
)
SELECT *
FROM `yourproject.yourdataset.yourtable` t
WHERE REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), r'dubai')

Hope above can be good starting point for you to apply to your specific case

But note: cost is scan of whole table - so check cost before running against read (hopefully big) data :o)

Upvotes: 6

Related Questions