Google BigQuery：TABLE_QUERY AND TABLE_DATE_RANGE [英] Google BigQuery: TABLE_QUERY AND TABLE_DATE_RANGE

问题描述

我有如下的大型查询表，并且对标记为< = 的表发出查询。

  prefix_AAAAAAA_20170320 
 prefix_AAAAAAA_20170321 
 prefix_AAAAAAA_20170322< = 
 prefix_AAAAAAA_20170323< = 
 prefix_AAAAAAA_20170324< = 
 prefix_AAAAAAA_20170325 
 prefix_BBBBBBB_20170320 
 prefix_BBBBBBB_20170321 
 prefix_BBBBBBB_20170322< = 
 prefix_BBBBBBB_20170323< = 
 prefix_BBBBBBB_20170324< = 
 prefix_BBBBBBB_20170325 
 prefix_CCCCCCC_20170320 
 prefix_CCCCCCC_20170321 
 prefix_CCCCCCC_20170322 
 prefix_CCCCCCC_20170323 
 prefix_CCCCCCC_20170324 
 prefix_CCCCCCC_20170325 
  

这个

  SELECT * FROM 
（TABLE_QUERY（mydataset，
'table_id CONTAINS前缀AND 
（table_id CONTAINSAAAAAA或table_id CONTAINSBBBBBB）''）
 AND 
 TABLE_DATE_RANGE（mydataset.prefix，TIMESTAMP（ '），TIMESTAMP（'2017-03-24'）））
  

I

 错误：在第5行第4列遇到ANDAND。期待： ）... 
  

有人有想法吗？

解决方案

您无法将TABLE_QUERY和TABLE_DATE_RANGE混合为完全相同的FROM！

尝试如下所示

  #legacySQL 
 SELECT * 
 FROM（TABLE_QUERY（mydataset，'REGEXP_MATCH（table_id，prefix_ [AB] {7} _2017032 [234]）'））
  

考虑 迁移到BigQuery标准SQL

在这种情况下，您可以 查询多个使用通配符表格

请参阅 如何从TABLE_QUERY（）迁移到_TABLE_SUFFIX

我认为，在这种情况下，您的查询可能看起来像

  #standardSQL 
 SELECT * 
 FROM`mydataset.prefix_ *`
 WHERE REGEXP_CONTAINS（_TABLE_SUFFIX，'[AB] {7} _2017032 [234] '）
  

我无法迁移到标准SQL，因为... < br>
如果我想在2017-03-29和2017-04-02之间搜索示例，您是否有任何智能SQL

尝试以下版本

  #legacySQL 
 SELECT * 
 FROM（TABLE_QUERY（mydataset，
'REGEXP_MATCH（table_id，rprefix_ [AB] {7} _（\d）{8}）AND 
 RIGHT（table_id，8）BETWEEN 20170329和20170402'））
  

当然，您可以调整以使用任何确切的逻辑哟需要申请！

I have Big Query tables like below, and like to issue a query to the tables marked <=.

prefix_AAAAAAA_20170320
prefix_AAAAAAA_20170321
prefix_AAAAAAA_20170322 <=
prefix_AAAAAAA_20170323 <=
prefix_AAAAAAA_20170324 <=
prefix_AAAAAAA_20170325
prefix_BBBBBBB_20170320
prefix_BBBBBBB_20170321
prefix_BBBBBBB_20170322 <=
prefix_BBBBBBB_20170323 <=
prefix_BBBBBBB_20170324 <=
prefix_BBBBBBB_20170325
prefix_CCCCCCC_20170320
prefix_CCCCCCC_20170321
prefix_CCCCCCC_20170322
prefix_CCCCCCC_20170323
prefix_CCCCCCC_20170324
prefix_CCCCCCC_20170325

I made a query as this

SELECT * FROM 
  (TABLE_QUERY(mydataset, 
    'table_id CONTAINS "prefix" AND 
    (table_id CONTAINS "AAAAAA" OR table_id CONTAINS "BBBBBB")' )
   AND
   TABLE_DATE_RANGE(mydataset.prefix, TIMESTAMP('2017-03-22'), TIMESTAMP('2017-03-24')))

I got this error.

Error: Encountered " "AND" "AND "" at line 5, column 4. Was expecting: ")" ...

Does anybody has ideas?

解决方案

You cannot mix TABLE_QUERY and TABLE_DATE_RANGE for exactly same FROM!

Try something like below

#legacySQL
SELECT * 
FROM (TABLE_QUERY(mydataset, 'REGEXP_MATCH(table_id, "prefix_[AB]{7}_2017032[234]")'))  

Consider Migrating to BigQuery Standard SQL

In this case you can Query Multiple Tables Using a Wildcard Table

See How to Migrate from TABLE_QUERY() to _TABLE_SUFFIX

I think, in this case your query can look like

#standardSQL
SELECT * 
FROM `mydataset.prefix_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '[AB]{7}_2017032[234]')  

I can not migrate to Standard SQL because ...
If I would like to search for example between 2017-03-29 and 2017-04-02, do you have any smart SQL

Try below version

#legacySQL
SELECT * 
FROM (TABLE_QUERY(mydataset, 
    'REGEXP_MATCH(table_id, r"prefix_[AB]{7}_(\d){8}") AND 
    RIGHT(table_id, 8) BETWEEN "20170329" AND "20170402"'))  

Of course yo can adjust above to use whatever exactly logic yo need to apply!

这篇关于Google BigQuery：TABLE_QUERY AND TABLE_DATE_RANGE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！