How to reference the latest table from a manually partitioned BigQuery table

1
votes

We have a manually partitioned "video metadata" table being fed fresh data each day. In our system, old data is only kept for historical reasons since the latest data is the most up to date.

What we cant figure out is how to reference only the latest partition in this table using LookML.

So far we have attempted to store views in BigQuery. We have tried and failed to store a simple "fetch the newest partition" query as a view, in both standard and legacy SQL, and upon some searching, this seems to be by design, even though the error message states "Dataset not found" instead of something more relevant.

We've also tried to build the filtering into Looker, but we're having trouble with getting things to actually work and only having the latest data returned to us through it.

Any help would be appreciated.

1
sqlgoogle-bigquerylooker

1 Answers

5
votes

We've managed to find a solution, derived tables

We figured that since we couldn't define a view on BigQuery's side, we could do it on Looker's side instead, so we defined the table in a derived table block inside a view.

derived_table: {
    sql: SELECT * FROM dataset.table_*
         WHERE _TABLE_SUFFIX = (
             SELECT max(_TABLE_SUFFIX) FROM dataset.table_*
         );;
    sql_trigger_value: SELECT max(_TABLE_SUFFIX) FROM dataset.table_*;;
}

This gave us a view with just the newest data in it.