search

tableau / connector-plugin-sdk

SDK for Developing Tableau Connector Plugins
https://tableau.github.io/connector-plugin-sdk/
MIT License
108 stars 107 forks source link

Developing a connector that rewrites SQL queries #639

Closed buremba closed 3 years ago

buremba commented 4 years ago

About You: Company: Rakam Inc.

Your question: We have a huge table that has billions of rows in our Postgresql cluster and we're looking for a way to speed up queries into this table. We actually have incremental summary tables for some common dimensions and measures and the users make use of these tables in case they use these common dimension & measure pairs.

Since these summary tables are just typical tables, The users need to know which table has the dimension & measure pairs that they're going to analyze and it's a huge bottleneck for them. Ideally, we're looking for a way to hide this complexity from the user and develop a custom connector that leverages these summary tables when the user analyzes the fact table.

In order to get it done, we need to rewrite the measures generated in the SQL. For example, an example total_rows measure should be written as sum(total_rows) on the summary table while Tableau just runs count(*) on the fact table. We also need to rewrite the FROM clause pointing to the summary table instead of the fact table.

Looking at the docs, Tableau Dialect Definition files are not suitable for this use-case as we need to generate the SQL expressions in the connector. Is there any other way to write a low-level connector that maps the Tableau queries to SQL queries or is it possible to develop such a connector for Tableau using another SDK?

sidwray commented 3 years ago

tfs 1197772

buremba commented 3 years ago

@sidwray One way I can think of is writing a JDBC connector that has a custom executeQuery implementation which parses the SQL queries and rewrites them but it's really hard to get it done as I need to create an AST from the query string and rewrite them which is not trivial work and the implementation depends on the dialect.

Also, it won't be convenient for the end-users as we support multiple databases, and integrating custom JDBC connectors is not that easy for the end-users. Ideally, we would like to build a middleware plugin for rewriting the SQL queries, not a connector built from scratch.

Do you have any API that I can look for? I know that such API does not exist but any suggestion is welcomed as I don't know much about Tableau APIs other than connector-plugin-sdk.

rosswbrown commented 3 years ago

Rewriting the contents of the SQL queries in this way is outside of the scope this SDK. As you've mentioned the dialect influences the SQL statements used within a query, but not its structure or contents. The customizations that are available are listed here, but based on your question I believe they are likely too high level for your needs.

Something available as a general purpose extension point to users is Connect to a Custom SQL Query. This is a common way optimizations and non-standard queries are used within the existing Tableau scenarios.

buremba commented 3 years ago

@rosswbrown thanks for the answer. I'm looking for a way to convert ad-hoc Tableau reports to SQL but the Custom SQL query feature seems to work the other way around. I also looked into the Web Connector feature but that requires us to send raw data into Tableau.