How to call BigQuery functions from logica?

[GoodDingo]

Q: How to call BigQuery native functions?

I've discovered Logica just today, went through tutorial and already thinking about using it to replace some damn ugly unmaintainable pieces of SQL...

Sadly, I haven't found anywhere HOW can I call a BigQuery functions?

Is there some additional Logica documentation other than examples in repo and tutorial?

---

Example: I'd like to use FORMAT_TIMESTAMP() on a timestamp to get a day-of-the week or quarter, something I'm simply unable to do with Substr. (Function is listed in processed_functions.py).

%%logica MonthlyValues

MyTimestamp("2023-11-01 02:00:00+00:00", 1);
MyTimestamp("2023-11-10 09:00:00+00:00", 1);
MyTimestamp("2023-10-30 07:00:00+00:00", 2);
MyTimestamp("2023-10-20 02:00:00+00:00", 2);
MyTimestamp("2023-10-10 11:00:00+00:00", 7);
MyTimestamp("2023-10-01 05:00:00+00:00", 2);

MonthlyValues(month:, agg? Set= value) distinct :-
  MyTimestamp(date, value),
  month = Substr(date, 0, 7);
  
#QuarterValues(quarter:, agg? Set= value) distinct :-
#  MyTimestamp(date, value),
#  quarter = ??? how to call FORMAT_TIMESTAMP("%Y-%Q", date)  ???

Where MonthlyValues returns

month	agg
2023-11	[1]
2023-10	[2, 7]

and QuarterValues should return

quarter	agg
2023-4	[1, 2, 7]

---

The only way I found is abusing SqlExpr, but I believe there is a logica-native way.

TypedTimestamp(str:, timestamp:) :- 
  timestamp == SqlExpr("CAST({str_ts} AS TIMESTAMP)", {str_ts: str});

QuarterValues(quarter:, agg? Set= value) distinct :-
  MyTimestamp(date, value),
  TypedTimestamp(str: date, timestamp:),
  quarter == SqlExpr("FORMAT_TIMESTAMP('%Y-%m', {ts})", {ts: timestamp});

Continuing with this, I'd like to compute the difference between maximum and minimum in the agg array, something like diff == MAX(agg) - MIN(agg) but the best thing I'm able to do is even uglier SqlExpr:

DiffValues(time_period:, diff:) :-
  MonthlyValues(month: time_period, agg:),
  diff == SqlExpr("(SELECT MAX(ma) FROM UNNEST({agg}) as ma) -  (SELECT MIN(mi) FROM UNNEST({agg}) as mi)", {agg: agg});

And result like

time_period	diff
2023-11	0
2023-10	5

I believe that there must be a better way.

Thank you!

[EvgSkv]

Indeed, function calling was totally missing from tutorail. Updated.

Basically for functions with simple signature like FORMAT_TIMESTAMP we simply need to camel-case it, e.g. into FormatTimestamp.

So this works:

%%logica QuarterValues

MyTimestamp("2023-11-01 02:00:00+00:00", 1);
MyTimestamp("2023-11-10 09:00:00+00:00", 1);
MyTimestamp("2023-10-30 07:00:00+00:00", 2);
MyTimestamp("2023-10-20 02:00:00+00:00", 2);
MyTimestamp("2023-10-10 11:00:00+00:00", 7);
MyTimestamp("2023-10-01 05:00:00+00:00", 2);
MyTimestamp("2023-01-01 05:00:00+00:00", 11);

QuarterValues(quarter:, agg? Set= value) distinct :-
  MyTimestamp(date, value),
  quarter = FormatTimestamp("%Y-%Q", Timestamp(date));

---

As for diff, there are a couple ways to do it.

You will need an aggregating expression. And you can either define an aggregate function and apply it:

BoundingRange(x) = Max(x) - Min(x);

DiffValuesNativeV1(time_period:, diff:) :-
  MonthlyValues(month: time_period, agg:),
  diff BoundingRange= (x :- x in agg);

Or you can compute min and max, then subtract. This, I believe, results in code identical to what you've got with SQL.

DiffValuesNativeV2(time_period:, diff:) :-
  MonthlyValues(month: time_period, agg:),
  top Max= (x :- x in agg),
  bottom Min= (x :- x in agg),
  diff = top - bottom;

Let me know if you have further questions! :-)

[GoodDingo]

Thanks for updating the tutorial so promptly...

This answers both of my questions. Both CamelCasing and solution with aggregation reads pretty good!
Thanks again for your support.

---

I do have two additional questions:

(1)

Is it safe to assume that all BigQuery functions are callable via CamelCasingMethod? Do they have to be listed in processed_functions list first or if new function is introduced to BigQuery tomorrow it would work out-of-the-box as well?

(2)

When I work with partitioned table (like GCP raw billing data export), I'd like to have generated SQL contain last section for the whole query for the performance purposes...

(
  ...
)
WHERE
    TIMESTAMP_TRUNC(_PARTITIONTIME, DAY) >= TIMESTAMP("{my_starting_date}")

Am I able to do it?

(3)

When I have a SQL function that requires a literal like DAY, i.e.

TIMESTAMP_TRUNC('{my_timestamp}', DAY, 'America/Los_Angeles')

then is it possible to call it somehow from Logica in this way

...
billing_day == FormatTimestamp("%F", TimestampTrunc(date, ?????, "America/Los_Angeles")),
...                                                         _____

or do I have to resort to SqlExpr() like this...?

CorrectBillingDay(ts) =
  SqlExpr("FORMAT_TIMESTAMP('%F', TIMESTAMP_TRUNC({ts}, DAY, 'America/Los_Angeles'))", {ts: ts});

[EvgSkv]

You're very welcome!

---

For the followup:

(1) No, you were right about looking at processed_functions.py. We do need to add any newly added BQ functions there.
It's a good property for Logica to compile to SQL without making any url fetches, so we can't determined BQ functions on the fly. Also API that was initially used to created processed_functions isn't available now, we'd need to learn how to fetch all current functions in bulk (if we need it).

If you know what functions were added and you need them, feel free to create an issue, or list them here, it is easy to add them.

(2) This looks doable, but I am sure what exactly needs to happen. Will there be _PARTITIONTIME in the inner query? Should {my_starting_date} be literally that, or it should be some specific date?

For example we can simply add this constraint as conjunct in the body of the clause. Is this not what we want for some reason?

(3) We can use SqlExpr to create just the DAY token, leaving rest of syntax native.

D(x) :-
  x = TimestampTrunc(Timestamp("2023-11-10 09:00:00+00:00"), SqlExpr("DAY", {}), "America/Los_Angeles");