0
395
ViewsConnecting data tables (data model), not joining them, in R, Python, Julia or
Is there any equivalent way to connecting data like what we do in BI software (i.e., Power Bi or Tableau) and then Query from them?
To clarify:
We use explicit join in programming languages for example new_table = inner_join(a,b, by = c) so the new table is calculated and final, but in BI tools we introduce data model, you can see a model in the picture, it is not calculated now, then we perform multi tables query without any explicit join. The software itself decide how to retrieve data from the data model just in time.
HR_tables %>% group_by(DEPARTMENTS$department_name) %>% (sum(EMPLOYEES$salary))
1 answers
Answer question0
The idea of a query optimizer being used with non-materialized views in a relational system such as a data warehouse generally doesn't have a direct corollary in any of these languages. You do see this sort of optimizer in action in systems like Mahout Samsara or Tensorflow.
Another analog to a traditional relational query optimizer can be found in Julia in the way that the optimizer can transform broadcast expressions. In many cases, unnecessary data allocation in such an expression can be cut to zero by transforming what appears to be a pipeline to an in-place progressive mutation.
You also see some analogous lazy evaluation in the LazyArrays system in Julia but, again, this is not oriented towards the relational join optimization that you described earlier.
Part of the lack of these systems in Julia and in Spark is the difference in focus. Non-materialized views and complex join optimization show up in systems where normalized forms are prevalent (aka relational systems). Normalized forms are very nice when you want to always see the latest updates so, say, a person's address. To a lesser extent, joins also become important when you have a central fact table which references dimensions as in a snowflake architecture (again, reference to up-to-date information like addresses and phone numbers is considered a benefit.
In many more modern systems, however, there is a much larger focus on dataflow designs and on preserving the data as it originally was presented. Most joins consist of very large datasets or even realtime data streams against relatively small dimension tables, or they are very large datasets against very large datasets. Joins are typically minimized in these systems because of an emphasis on denormalized data as a partial consequence of geographically distributed systems and partially a consequence of a focus on preserving data as it originally appeared rather than keeping it entirely up to date. The optimization of the trivial joins that show up in these systems is pretty simple and you don't normally need a cost-based optimizer to do this.
In this kind of context, the assumptions behind your question really don't apply.