Get table id from lookup query

Question

Get table id from lookup query

I would like to check if the table range only covers one day (i.e. we don't accidentally put records in the wrong table).

We now have tables that look like this:

Table: sometable_20140506

pst_date   | pst_hour | ...
2014-05-06 | 10       | ...
2014-05-06 | 10       | ...
2014-05-06 | 11       | ...
2014-05-06 | 11       | ...
2014-05-06 | 12       | ...

And I would like to find any tables where there is more than one unique value for pst_date, eg. something like:

SELECT table_id, COUNT(*) AS num_dates

FROM
    (SELECT
    table_id,
    pst_date
    FROM
    (TABLE_DATE_RANGE(somedataset.sometable_, 
                      TIMESTAMP('2014-05-06'),
                      TIMESTAMP('2014-06-06')))
    GROUP BY table_id, pst_date)
HAVING COUNT(*) > 1
GROUP BY table_id

You can get table_id

in TABLE_QUERY

by simply wondering if you can get it when choosing out TABLE_QUERY

.

+3

google-bigquery

Jeff tratner 13 Aug 14 at 23:13

source to share

No one has answered this question yet

See similar questions:

8

Is there a way to select table_id in Bigquery substring table?

or similar:

8

Is there a way to select table_id in Bigquery substring table?

6

Using TABLE_DATE_RANGE with tables over 1 year old

2

Is there a way to group timestamp data into 30 day intervals starting with minimum (date) and add it as columns

2

How to extract table name of records when using table lookup functions

1

BigQuery how to group after aligning a collection of tables by timer

0

BigQuery GA Open Funnel Obsolete SQL: Exclude Sessions Viewing Specific Pages

0

Calculating the size of proxy bits in a BigQuery table

0

Is there a way to make a SELECT query in BigQuery conditionally only if the table exists?

0

column with number of bigquery with condition

0

Using table_id from __TABLES_SUMMARY__ in SQL query to select tables

Get table id from lookup query

More articles: