[Question] Does shillelagh support path variables in URL?

[luozhouyang]

Does shillelagh support path variables in URL?

for exmaples:

https://mydomain.com/users/{user_id} https://mydomain.com/orders/{order_id}

[betodealmeida]

@luozhouyang can you give more context here? How could queries work, do you have a concrete example?

[luozhouyang]

I have a superset chart to show each online shop's page view today. The backend API is https://mydomain.com/shops/{shop_id}/pv, I want to set the path variable {shop_id} dynamiclly, so the chart will show different shop's pv. So how can I achieve this goal?

[betodealmeida]

@luozhouyang I think you can do something like this:

1. Create a virtual dataset in Superset with the following SQL:

{% set default_shop_id = XXX %}
SELECT * FROM "https://mydomain.com/shops/{{ filter_values('shop_id')[0] if filter_values('shop_id') else default_shop_id }}/pv";

This assumes that your API returns JSON. You might need to specify a JSONPath in order to get the data you want from the API, see https://shillelagh.readthedocs.io/en/latest/adapters.html#generic-json-apis.

2. Create a dataset with a column called shop_id that has all the IDs of the shops.

3. Create a dashboard and add a filter on the dataset from step 2, on the shop_id column.

I think that if you do this, you should be able to select the shop ID from the filter dropdown, and the chart will show the corresponding results. Pinging @Vitor-Avila since he might have other ideas.

[Vitor-Avila]

@luozhouyang I think you can do something like this:

1. Create a virtual dataset in Superset with the following SQL:

{% set default_shop_id = XXX %}
SELECT * FROM "https://mydomain.com/shops/{{ filter_values('shop_id')[0] or default_shop_id }}/pv";

This assumes that your API returns JSON. You might need to specify a JSONPath in order to get the data you want from the API, see https://shillelagh.readthedocs.io/en/latest/adapters.html#generic-json-apis.

2. Create a dataset with a column called shop_id that has all the IDs of the shops.
3. Create a dashboard and add a filter on the dataset from step 2, on the shop_id column.

I think that if you do this, you should be able to select the shop ID from the filter dropdown, and the chart will show the corresponding results. Pinging @Vitor-Avila since he might have other ideas.

I believe that's indeed the best implementation approach.

[luozhouyang]

Thank you very much for your enthusiastic help!

I enabled the ENABLE_TEMPLATE_PROCESSING feature in superset first, and then I wrote this SQL in SQL Lab:

{% set default_shop_id = 2301072346546930 %}

SELECT * 
FROM "https://xxx.com/metrics/shops/{{ filter_values('shop_id')[0] if filter_values('shop_id') else 2301072346546930 }}/pageviewByRegion/today#$.resultObject[*]"

But an error occurs

I ignored this error and try to save this SQL as a virtual database, another exception throwed

I checked superset's log, it seems the same as the first one:

Did I misconfigured superset or something else?

[Vitor-Avila]

@luozhouyang I just performed a similar test on Superset local using below SQL query and it worked:

{% set default_table = 'cleaned_sales_data' %}

select * from {{ filter_values('table_options')[0] if filter_values('table_options') else default_table }}

Could you perform this test and validate if it works on your end? If so we can confirm the JInja enablement/configuration is working properly.

[luozhouyang]

Thanks very much! I did not config template processing correctly. I deploy superset with helm, the correct config in values.yaml is:

# -- A dictionary of overrides to append at the end of superset_config.py - the name does not matter
# WARNING: the order is not guaranteed
# Files can be passed as helm --set-file configOverrides.my-override=my-file.py
configOverrides: 
  role_configs: |
    PUBLIC_ROLE_LIKE = "Gamma"
  feature_flags: |
    FEATURE_FLAGS = {
      "ENABLE_TEMPLATE_PROCESSING": True,
      "EMBEDDABLE_CHARTS": True,
      "EMBEDDED_SUPERSET": True,
      "DASHBOARD_RBAC": True
    }

just for some need it!