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

Question

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.

This looks more like a narrating requirements. Where is your research / code / efforts? — Sunil, Jan 24 '18 at 03:49

score 6 · Answer 1 · answered Jan 24 '18 at 05:20

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)

Thanks a lot. It reduced my time and efforts by 10 times. – Gopi Kantamneni Jan 25 '18 at 11:37 — Gopi Kantamneni, Jan 25 '18 at 11:37
@GopiKantamneni - see my comment on your question :o) – Mikhail Berlyant Jan 25 '18 at 17:04 — Mikhail Berlyant, Jan 25 '18 at 17:04

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

1 Answers1

Linked