CODEWITHSUNDEEP
google-bigquery
Shahin Ghannadian
Shahin Ghannadian

Reputation: 295

select latest Table in a Big Query Dataset - Standard SQL syntax

I have dataset containing multiple tables with similar names:

e.g.

affilinet_4221_first_20180911_204956
affilinet_4221_first_20180911_160004
affilinet_4221_first_20180911_085559
affilinet_4221_first_20180910_201323
affilinet_4221_first_20180910_201042
affilinet_4221_first_20180910_080006
affilinet_4221_first_20180909_160707

This query identifies the latest dataset (according to yyyymmdd_hhmmss naming convention) with __TABLES_SUMMARY__ method

SELECT max(table_id) as table_id FROM `modemutti-8d8a6.feed_first.__TABLES_SUMMARY__`
where table_id LIKE "affilinet_4221_first_%"

query result

this query extracts all values from a specific table with _TABLE_SUFFIX method

SELECT * FROM `modemutti-8d8a6.feed_first.*`
WHERE _TABLE_SUFFIX = "affilinet_4221_first_20180911_204956"

query result

This query combines __TABLES_SUMMARY__ (which returns affilinet_4221_first_20180911_204956) and _TABLE_SUFFIX

SELECT * FROM `modemutti-8d8a6.feed_first.*`
WHERE _TABLE_SUFFIX = (
            SELECT max(table_id) FROM `modemutti-8d8a6.feed_first.__TABLES_SUMMARY__`
            where table_id LIKE "affilinet_4221_first_%")

this query fails:

Error: Cannot read field 'modemio_cat_level' of type INT64 as STRING

error screenshot

any idea why is this happening or how I could solve the issue?

------------EDIT------------

@Mikhail solution works correctly but processes a huge amount of data. See explicit call Vs the suggested Method. Another solution would have been 

SELECT * FROM  `modemutti-8d8a6.feed_first.affilinet_4221_first_*` WHERE _TABLE_SUFFIX = 
    ( 
    SELECT MAX(_TABLE_SUFFIX)  FROM`modemutti-8d8a6.feed_first.affilinet_4221_first_*`
    )

but this processes also a much bigger amount of data compared to the explicit query. Is there are way to achieve through a view in the UI or should I rather use the Python / Java SDK via API?

Upvotes: 0

Views: 1256

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Try below 

#standardSQL
SELECT * FROM `modemutti-8d8a6.feed_first.affilinet_4221_first_*`
WHERE _TABLE_SUFFIX = (
    SELECT REPLACE(MAX(table_id), 'affilinet_4221_first_', '') 
    FROM `modemutti-8d8a6.feed_first.__TABLES_SUMMARY__`
    WHERE table_id LIKE "affilinet_4221_first_%"
)

Upvotes: 3

Related Questions