大查询 从任何列包含'FINISHED'的表中选择

Question

我的BigQuery中有100多列，行超过7000万行。我想找出是否可以编写查询以提取任何列中包含一个"完成"的值的行。

Answer 1

以下是BigQuery Standard SQL对您来说应该是一个好的开始:)

#standardSQL
SELECT <columns to output> 
FROM yourTable AS t
WHERE REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), 'finished')

您可以使用以下虚拟数据进行测试/播放

#standardSQL
WITH yourTable AS (
  SELECT 'a' AS x, 'b' AS y, 'c' AS z UNION ALL
  SELECT 'finished', '', '' UNION ALL
  SELECT '', 'Bigquery Select from table where any column contains "FINISHED"','' UNION ALL
  SELECT '', '', 'aaa' UNION ALL
  SELECT 'finished', 'bbb', 'finished' 
)
SELECT * 
FROM yourTable AS t
WHERE REGEXP_CONTAINS(LOWER(TO_JSON_STRING(t)), 'finished')   

更新

注意：如果您将搜索字作为至少一个列名称的一部分，上面将返回所有行！要解决这个问题 - 您需要投资更多的编码

例如，对于简单的架构（没有记录或重复），这将是

#standardSQL
SELECT <columns to output>  
FROM yourTable AS t
WHERE (SELECT COUNTIF(SPLIT(zzz, ':')[SAFE_OFFSET(1)] LIKE '%finished%') 
  FROM UNNEST(SPLIT(SUBSTR(LOWER(TO_JSON_STRING(t)),2,LENGTH(TO_JSON_STRING(t))-2))) AS zzz
  ) > 0   

您可以用以下

进行测试

#standardSQL
WITH yourTable AS (
  SELECT 'a' AS x, 'b' AS y, 'c' AS col_finished UNION ALL
  SELECT 'finished', '', '' UNION ALL
  SELECT '', 'Bigquery Select from table where any column contains "FINISHED"','' UNION ALL
  SELECT '', '', 'aaa' UNION ALL
  SELECT 'finished', 'bbb', 'finished' 
)
SELECT * 
FROM yourTable AS t
WHERE (SELECT COUNTIF(SPLIT(zzz, ':')[SAFE_OFFSET(1)] LIKE '%finished%') 
  FROM UNNEST(SPLIT(SUBSTR(LOWER(TO_JSON_STRING(t)),2,LENGTH(TO_JSON_STRING(t))-2))) AS zzz
  ) > 0