如何在大查询表的所有列中搜索关键字? [英] How to search a key word in all columns of big query table?

问题描述

我正在使用大型查询来查看我的Google云中的数据.我想在特定表格的所有列中搜索关键字.

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.

推荐答案

下面是BigQuery标准SQL的假设，并且假定列名不包含搜索词(也可以对其进行调整以解决此问题)

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

但是请注意:成本是对整个表的扫描-因此，请在运行读取的(希望很大的)数据之前检查成本:o)

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

这篇关于如何在大查询表的所有列中搜索关键字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！