Extracting the raw SQL command without executing it

[yemaney]

Zillion nlp allows using normal text to execute sql.

result = wh.execute_text("sales for Partner A")
print(result.df) # Pandas DataFrame

The above example shows a case of using the nlp capability to execute a query.
The execute_text callable should be constructing an SQL command before it's executed.
Wondering if there if the raw SQL can be exposed before executing it.
Here is some pseudocode of what I'm thinking.

q = wh.create_nlp_q("sales Partner A")
print(q)
# SELECT sales from ...

[totalhack]

This should be doable in some form. Note that the output of the underlying NLP process is not a SQL query, rather it constructs the arguments for your report. Report results have the generated SQL, so I would have to add an option to generate but not run the SQL which shouldn't be difficult.

Also note that since a drill across query technique is used, you'll be getting the (potentially) multiple queries generated at the "datasource layer". These queries would not output the final result as zillion has an additional combined query layer as noted in the documentation.

[yemaney]

Yes, just read the docs about query-layers (https://github.com/totalhack/zillion#query-layers)

1. DataSource Layer: SQL queries against the warehouse's data sources
2. Combined Layer: A final SQL query against the combined data from the DataSource Layer

Would this mean that only the queries for the DataSource Layer can be exposed? Or is possible to expose the SQL query for the Combined Layer as well?

[totalhack]

The Combined Layer doesn't do anything fancy in SQL besides join the datasource layer data. After that there are features and transformations done in python with Pandas, such as rollups, row filters/limits, formula fields, and a few others.

[totalhack]

If you look at warehouse.execute_text it just does this under the hood:

report = Report.from_text(self, text, adhoc_datasources=adhoc_datasources, allow_partial=allow_partial)
result = report.execute()

A ReportResult comes with a query_summaries attribute that is a list of DataSourceQuerySummary objects. Take a look at report.py which has that class and you'll see _format_query which can be used to get the generated SQL at the datasource layer for each datasource query the report used.

Now that you have a better understanding of how Zillion operates with its layers, do you still think it would be useful to have some mode that stops after generating the DS layer queries before executing? What is the use case for that?

[totalhack]

Can discuss this further if there is a good use case, closing for now.