0

I'm looking at the user-defined function docs for BigQuery, but I need to define a function to simplify the process of querying multiple tables. I have people who have to do stuff like this: 

SELECT * FROM (TABLE_DATE_RANGE([SomeDataSet.SomeTableName_],DATE_ADD(CURRENT_TIMESTAMP(),-2,'DAY'),DATE_ADD(CURRENT_TIMESTAMP(),-1,'DAY'))) where magical_rainbows = 7;

Nobody is going to remember all of that, and they'll end up copy-pasting it in from some source, over and over. No big deal, except people are lazy and will inevitably develop the habit of setting that date range to be all-inclusive and run my bill through the roof.

I'd rather use a UDF to get all of that down to something they can remember - and type quickly:

SELECT * FROM da_tables([SomeDataSet.SomeTableName_], -2, -1) where .....

Everything I see in UDF, however, is about table schema production, and there doesn't appear to be a way for me to solve this headache.

Sniggerfardimungus
  • 11,583
  • 10
  • 52
  • 97
  • If you ask "how can you make it easy to query over the last 3 days of tables to avoid all the cut-and-paste", I would suggest creating a view that embedded all your TABLE_DATE_RANGE() computations. – Michael Sheldon May 19 '16 at 04:21
  • Regarding the concern of "run my bill through the roof", take a look at "Controlling query costs using BigQuery custom quotas" at https://cloud.google.com/bigquery/cost-controls – Michael Sheldon May 19 '16 at 04:23
  • it could have been done easily using table_query() and reference table containing the "business dates" metadata... as it was removed, please vote up for getting the functionality back... http://stackoverflow.com/questions/36673468/error-table-query-expressions-cannot-query-bigquery-tables – N.N. May 19 '16 at 07:17
  • Thank you for the suggestion--it seems like a useful feature to support functions that operate on tables in this way. I filed https://code.google.com/p/google-bigquery/issues/detail?id=535 in the BigQuery issue tracker. – Elliott Brossard May 19 '16 at 16:36
  • To close the loop, we now support SQL UDFs that operate on structs (e.g. row types) so it's possible to express this sort of transformation with a function. I marked the corresponding feature request as fixed: https://code.google.com/p/google-bigquery/issues/detail?id=535. – Elliott Brossard Aug 10 '16 at 17:09

1 Answers1

1

Current implementation of BigQuery UDF does not provide this type of feature

Meantime - on optimistic note:
Check Big Data and Analytics section in this blog Google takes Cloud Machine Learning service mainstream. Specifically check out Automatic Table Partitions bullet!

In addition - you can check timePartitioning experimental property in Table's Resource, which tells me that there is a progress in this direction :o)

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230