GitHub user pandax08 created a discussion: warm_up_cache API doesn't apply
filters when the dataset uses the filter_values Jinja macro
Hi everyone,
I'm trying to pre-warm the cache for a dashboard connected to AWS Athena using
a Python script that calls the `/api/v1/dataset/warm_up_cache` endpoint. My
datasets are virtual and use Jinja templates in the SQL to apply filters
dynamically.
The issue I'm facing is that the filters I send via the `extra_filters`
parameter in the API call are not being applied to the final query that runs on
Athena. After several tests, I have confirmed that the
`filter_values('my_column')` macro always returns an empty list (`[]`) when the
query is triggered from the context of the `warm_up_cache` API. This causes the
`{% if filter_values(...) %}` conditions to always evaluate to false, and
therefore, the `WHERE` clauses are never added to the query.
#### Environment Details:
* **Database**: AWS Athena
* **Datasets**: Virtual, with SQL parameterized using Jinja.
* **API Endpoint**: `/api/v1/dataset/warm_up_cache`
#### Example SQL Query in the Dataset
The structure of my filters in the dataset's SQL is as follows:
```sql
SELECT
-- my columns
FROM
my_table
WHERE
1 = 1
{% if filter_values('my_filter_column_1') %}
AND column_1 IN {{ filter_values('my_filter_column_1', remove_filter=true)
| where_in }}
{% endif %}
{% if filter_values('my_filter_column_2') %}
AND column_2 IN {{ filter_values('my_filter_column_2', remove_filter=true)
| where_in }}
{% endif %}
```
#### API Request Body Example
Here is an example of the JSON body I'm sending in the `PUT` request to the
`/api/v1/dataset/warm_up_cache` endpoint. The `extra_filters` parameter is a
JSON string as required.
```json
{
"dashboard_id": 15,
"db_name": "AWS Athena",
"table_name": "my_virtual_dataset",
"extra_filters": "[{\\"col\\": \\"my_filter_column_1\\", \\"op\\":
\\"in\\", \\"val\\": [\\"value_a\\"]}, {\\"col\\": \\"my_filter_column_2\\",
\\"op\\": \\"in\\", \\"val\\": [\\"value_b\\"]}]"
}
```
#### Observed Behavior
When my script calls the API with the body above, the query that reaches Athena
does not have the filters applied:
```sql
-- Query that actually runs on Athena
SELECT
-- my columns
FROM
my_table
WHERE
1 = 1
-- The AND clause for the filters is missing
```
#### Expected Behavior
I would expect the query executed on Athena to include the filters sent from
the API, like this:
```sql
-- Query that should run on Athena
SELECT
-- my columns
FROM
my_table
WHERE
1 = 1
AND column_1 IN ('value_a')
AND column_2 IN ('value_b')
```
#### Diagnosis
To confirm the problem, I ran a test by replacing the dataset's query with
`SELECT '{{ filter_values("my_filter_column_1") }}'`. The result executed on
Athena was `SELECT '[]'`, which proves that the macro is not receiving the
values from the API's context.
Has anyone else experienced this behavior? Is there a recommended configuration
or method for `filter_values` to work correctly with the `warm_up_cache` API?
Thank you very much in advance!
GitHub link: https://github.com/apache/superset/discussions/34979
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
