You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2021/11/01 08:54:37 UTC
[GitHub] [superset] bvy007 opened a new issue #17302: Dynamic SQL generation at Schema Level and Database Level
bvy007 opened a new issue #17302:
URL: https://github.com/apache/superset/issues/17302
I would like to know a way to work with superset in my scenario of creating a dashboard at work. I have different schemas in a database as shown in following directory structure:
Database
├── public
│ ├── schema_meta_Info
│ └── Statistics
│
├── DBClient1
│ ├── financial_data
│ ├── customer_data
│ └── Sales_data
│
├── DBClient2
│ ├── financial_data
│ ├── customer_data
│ └── Sales_data
│
├── DBClient3
│ ├── financial_data
│ ├── customer_data
│ └── Sales_data
│
├── DBClient4
│ ├── financial_data
│ ├── customer_data
│ └── Sales_data
**_DBClient_** : Schemas
_**financial_data**_ : financial information of customers in Client
_**customer_data**_ : Customers of Client
_**Sales_data**_ : Sales Information of Client's Customers
And I am trying to create a dynamic SQL query and add it to the dashboard in superset. For this I am trying to have a chart with drop down(like filter in superset) option and wanted to select the DBClient (_DBClient1_ (or) _DBClient2_ (or) _DBClient3_ (or) _DBClient4_). Based on the my selection, I would like to show my plots with an updated query in the background.
For example _plot1_ in my dashboard wants to have a query like:
`select * from {{SelectedDBClient}}.financial_data;`
and if I had selected DBclient3 as the schema in the drop down plot of the dashboard, it should update the query of the _plot1_ as following:
`select * from DBclient3 .financial_data;`
Options that I tried:
- Tried using Jinja Templating : I found that this technique tend to work with values inside the table.
- Tried an option of passing parameter values : This technique is working in the SQL editor of superset by passing a dictionary of values when starting superset. But, it should be dependent on drop down selection schema value.
How can I achieve this ??. In my scenario, I had 10000 schemas with same tables inside. Also assume that I am an admin (Assume no RBAC for the datasets are needed).
Question 2:
Also I am curious to know how this can be done in multi database scenario (same conditions applied from Question 1 ) ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] rumbin commented on issue #17302: Dynamic SQL generation at Schema Level and Database Level
Posted by GitBox <gi...@apache.org>.
rumbin commented on issue #17302:
URL: https://github.com/apache/superset/issues/17302#issuecomment-958690445
You can use Jinja, referring to the filter values.
Just ensure you add a db_client column to the query which just contains the filter value and also refer to the filter value in the schema of the `FROM` clause.
The dashboard filter needs to be populated from another dataset that returns all possible db_clients then.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] rumbin commented on issue #17302: Dynamic SQL generation at Schema Level and Database Level
Posted by GitBox <gi...@apache.org>.
rumbin commented on issue #17302:
URL: https://github.com/apache/superset/issues/17302#issuecomment-958690445
You can use Jinja, referring to the filter values.
Just ensure you add a db_client column to the query which just contains the filter value and also refer to the filter value in the schema of the `FROM` clause.
The dashboard filter needs to be populated from another dataset that returns all possible db_clients then.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] spdustin commented on issue #17302: Dynamic SQL generation at Schema Level and Database Level
Posted by GitBox <gi...@apache.org>.
spdustin commented on issue #17302:
URL: https://github.com/apache/superset/issues/17302#issuecomment-972305064
I have a similar requirement, and I solved it by creating a "rollup view" for each table that looks something like this:
```sql
SELECT
'Acme' as client_name,
*
FROM
schema_for_acme.my_table
UNION ALL
SELECT
'Widgets, Inc.' as client_name,
*
FROM
schema_for_widgets_inc.my_table
UNION ALL
-- etc.
```
As a result, we can use a "master" dashboard with a filter control that staff can use, and use row-level security and RBAC to allow clients to see that same dashboard, but limit the data being returned. Client accounts are also prevented from accessing SQL Lab, etc.
The view is a temporary construct—we'll have a stage in our `dbt` pipeline that outputs fully materialized "master" tables.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] rumbin commented on issue #17302: Dynamic SQL generation at Schema Level and Database Level
Posted by GitBox <gi...@apache.org>.
rumbin commented on issue #17302:
URL: https://github.com/apache/superset/issues/17302#issuecomment-958690445
You can use Jinja, referring to the filter values.
Just ensure you add a db_client column to the query which just contains the filter value and also refer to the filter value in the schema of the `FROM` clause.
The dashboard filter needs to be populated from another dataset that returns all possible db_clients then.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] rumbin commented on issue #17302: Dynamic SQL generation at Schema Level and Database Level
Posted by GitBox <gi...@apache.org>.
rumbin commented on issue #17302:
URL: https://github.com/apache/superset/issues/17302#issuecomment-958690445
You can use Jinja, referring to the filter values.
Just ensure you add a db_client column to the query which just contains the filter value and also refer to the filter value in the schema of the `FROM` clause.
The dashboard filter needs to be populated from another dataset that returns all possible db_clients then.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org