如何在使用TABLE_DATE_RANGE时获取表名 [英] How to get table name when using TABLE_DATE_RANGE

问题描述

我希望像这样使用TABLE_DATE_RANGE获得每日统计数据：

 选择计数（*），表名
 FROM 
（TABLE_DATE_RANGE（appengine_logs.appengine_googleapis_com_request_log_，
 DATE_ADD（CURRENT_TIMESTAMP（），-7，'DAY'），CURRENT_TIMESTAMP（）））
 group by tableName 
  
 $ b 
有什么办法在使用TABLE_DATE_RANGE时得到一个表名？
解决方案
 
您需要使用元数据查询来查询您的数据集。
  SELECT * FROM publicdata：samples .__ TABLES__ 
 WHERE MSEC_TO_TIMESTAMP（creation_time）< DATE_ADD（CURRENT_TIMESTAMP（），-7，'DAY'）
  
返回
  + ----- + ------------ + ------------ + ----------------- + --------------- + --------------- ----- + ----------- + -------------- + ------ + --- + 
 |行| project_id | dataset_id | table_id | creation_time | last_modified_time | row_count | size_bytes |键入| | 
 + ----- + ------------ + ------------ + ------------- ---- + --------------- + -------------------- + -------- --- + -------------- + ------ + --- + 
 | 1 | publicdata |样本| github_nested | 1348782587310 | 1348782587310 | 2541639 | 1694950811 | 1 | | 
 | 2 | publicdata |样本| github_timeline | 1335915950690 | 1335915950690 | 6219749 | 3801936185 | 1 | | 
 | 3 | publicdata |样本| gsod | 1335916040125 | 1413937987846 | 114420316 | 17290009238 | 1 | | 
 | 4 | publicdata |样本| natality | 1335916045005 | 1413925598038 | 137826763 | 23562717384 | 1 | | 
 | 5 | publicdata |样本|莎士比亚| 1335916045099 | 1413926827257 | 164656 | 6432064 | 1 | | 
 | 6 | publicdata |样本| trigrams | 1335916127449 | 1335916127449 | 68051509 | 277168458677 | 1 | | 
 | 7 | publicdata |样本|维基百科| 1335916132870 | 1423520879902 | 313797035 | 38324173849 | 1 | | 
 + ----- + ------------ + ------------ + ------------- ---- + --------------- + -------------------- + -------- --- + -------------- + ------ + --- + 
  
您可以在WHERE子句中添加以限制到类似于 
 
 
 
 
  WHERE table_id包含wiki 
 
 
或正则表达式像 WHERE REGEXP_MATCH（table_id，r^ foo [\d] {3， 5}） 
 
 
 
I would like to get daily statistics using TABLE_DATE_RANGE like this:
Select count(*), tableName
FROM
(TABLE_DATE_RANGE(appengine_logs.appengine_googleapis_com_request_log_,
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP()))
group by tableName
Is there any way to get a table name when using TABLE_DATE_RANGE?
解决方案
You need to query your dataset with a metadata query.
SELECT * FROM publicdata:samples.__TABLES__ 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
this returns
+-----+------------+------------+-----------------+---------------+--------------------+-----------+--------------+------+---+
| Row | project_id | dataset_id |    table_id     | creation_time | last_modified_time | row_count |  size_bytes  | type |   |
+-----+------------+------------+-----------------+---------------+--------------------+-----------+--------------+------+---+
|   1 | publicdata | samples    | github_nested   | 1348782587310 |      1348782587310 |   2541639 |   1694950811 |    1 |   |
|   2 | publicdata | samples    | github_timeline | 1335915950690 |      1335915950690 |   6219749 |   3801936185 |    1 |   |
|   3 | publicdata | samples    | gsod            | 1335916040125 |      1413937987846 | 114420316 |  17290009238 |    1 |   |
|   4 | publicdata | samples    | natality        | 1335916045005 |      1413925598038 | 137826763 |  23562717384 |    1 |   |
|   5 | publicdata | samples    | shakespeare     | 1335916045099 |      1413926827257 |    164656 |      6432064 |    1 |   |
|   6 | publicdata | samples    | trigrams        | 1335916127449 |      1335916127449 |  68051509 | 277168458677 |    1 |   |
|   7 | publicdata | samples    | wikipedia       | 1335916132870 |      1423520879902 | 313797035 |  38324173849 |    1 |   |
+-----+------------+------------+-----------------+---------------+--------------------+-----------+--------------+------+---+
You can add in the WHERE clauses to restrict to tables similar to


WHERE table_id contains "wiki"
or regexp like WHERE REGEXP_MATCH(table_id, r"^foo[\d]{3,5}")


                        
这篇关于如何在使用TABLE_DATE_RANGE时获取表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！