Combining different data sources

[rvalek]

It seems like it's not possible right now to query/evaluate/correlate datasets from multiple data sources, e.g. something from BQ and something from Postgres. Is it, and if so, at which level of abstraction would be correlation of such datasets would be described and carried out?

PS. Thanks for all your work, Evgeniy and the contributors. Logica is fascinating, elegant and powerful even as it is right now!

[EvgSkv]

@rvalek that's right, currently @Engine imperative has global impact, all predicates use the same engine.
Obviously there is always an option to have some other scripts pulling all data sources to one, but something more holistic could be desired.

AFAIK Trino supports heterogeneous data sources, e.g. a Trino query could be reading from BQ and Postgres and writing to Postgres. So this should be doable with Logica program if you have Trino cluster properly configured. But I have not tried this myself, so I can't say how convenient is it compared to your current practices.

In the future we could make @Ground imperative specify datasource, so user can express cross data source queries. This will require quite a bit of coding and design. E.g. BQ and SQLite integration is more straightforward as it's natural to assume that data can be written to local disk. But say a query across BQ and Trino could require use of distributed filesystem, which needs to be configured and requires more complex Python code.

If you have any thoughts and ideas on the topic, I'd be happy to discuss it further.

And thank you very much for your supportive feedback!

[rvalek]

Thank you for your answer, Evgeniy!

It seems combining multiple data sources at large scale would be handled better by Presto/Trino indeed; and for the simpler cases some additional scripting may be enough.

Speaking of @Ground, tutorial doesn't go over annotations (and also doesn't mention the -->). Is there reference for those?

[EvgSkv]

That's a good point! I've added a section about @Ground to Tutorial's appendix:
https://colab.research.google.com/github/EvgSkv/logica/blob/main/tutorial/Logica_tutorial.ipynb#scrollTo=ZvDxW-sSZE6Q

We will need to write a more thorough reference for annotations and --> syntax.

[rvalek]

Thanks you for taking the time to explain @Ground!

I'm now trying to use Presto as you suggested, but it doesn't like the backquoted table names:

Account(..a) :-
  `master.accounts_view`(..a);

results in

Query 20210728_153531_00034_2gaeg failed: line 7:3: backquoted identifiers are not supported; use double quotes to quote identifiers
LINE 7:   `master.accounts_view` AS master_accounts_view
          ^

In plain SQL I pass them unquoted:
SELECT master_accounts_view.account_id AS account_id

What would be the right way to address them from logica?

[EvgSkv]

I should say I didn't have much opportunity to test Presto, so it may take a few trail and error to figure out best practices.

Can you please try without quotes, i.e.:

Account(..a) :-
  master.accounts_view(..a);

[rvalek]

It works without backquotes; I understand the general syntax better now, thank you!