Bigquery wildcard query with ruby gem

Question

I have using the google-cloud-bigquery gem (version 0.20.2, can't upgrade at the moment).

I have an audit dataset which contains many tables of the following format:

audit.some_table20170101
audit.some_table20170102 
audit.some_table20170103

etc..

I am trying to run a query which will scan all of these tables, and give me the last value of field some_field.

What I was going for is using the tables wildcard:

FROM audit.some_table*

and to hopefully

SELECT LAST(some_field) AS last_some_field

While using Bigquery web console, I was able to do so by using backticks (FROM `audit.some_table*`), but doing the same programmatically with the gem causes a Google::Cloud::InvalidArgumentError: invalid: Invalid table name: `audit.some_table*`
Even in the web console, when I try to use the LAST command it requires using legacy SQL, which then gives an error due to the backticks of the previous section. If I disable legacy sql, LAST is no available anymore (unfamiliar command) and then I have to order by a timestamp column descending and limit 1.

Any ideas how to solve these problems and to be able to query using the above mention gem and version?

Mosha Pasumansky Mosha Pasumansky · Accepted Answer · 2017-02-09T15:07:41

LAST is only meaningful when there is an order. Tables in BigQuery do not have inherit ordering, and if you run SELECT * FROM table you may get results in different order every time. Therefore the right thing to do it is to use ORDER BY some_value DESC LIMIT 1 construct.

The wildcard tables are indeed only available in Standard SQL, to get similar functionality with Legacy SQL you can use TABLE_DATE_RANGE function in FROM clause.

Bigquery wildcard query with ruby gem

1 Answers