You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "ampil (via GitHub)" <gi...@apache.org> on 2025/07/19 13:14:09 UTC
[D] Jinja macro fails to extract data from column-contained SQL string [superset]
GitHub user ampil created a discussion: Jinja macro fails to extract data from column-contained SQL string
I'm trying to create a Jinja macro in Superset that extracts source tables from SQL queries stored in a database column. While the function works locally, it fails when called from within a Superset SQL/Jinja query.
Here's what I tried:
Added into `superset_config.py`:
```
def extract_source_tables_from_sql(sql_text: str) -> str:
import re
tables = set(re.findall(r'\b(?:FROM|JOIN)\s+([\w]+\.[\w]+)\b', sql_text, re.I))
return ', '.join(sorted(tables))
JINJA_CONTEXT_ADDONS = {
...
'extract_source_tables_from_sql': extract_source_tables_from_sql,
}
```
For simplicity, connected to the Superset's internal DB on PostgreSQL.
Sample example that works with a hardcoded string:
```
SELECT
, '{{ extract_source_tables_from_sql("select 1 as t from dm.abc") }}' AS sources
, '{{ extract_source_tables_from_sql("select 1 as t from dm.abc"| tojson |safe) }}' AS sources2
FROM superset.tables t
where t.id=1
```
That means the macro works.
But when trying to query the actual database:
```
SELECT
t.sql
, {{ extract_source_tables_from_sql('t.sql') }} AS sources
FROM superset.tables t
WHERE t.id = 216
```
I get a malformed template error message.
How can I pass a column value to a jinja macro?
Superset 4.1.1
Python 3.9
PostgreSQL 16.8
GitHub link: https://github.com/apache/superset/discussions/34234
----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [D] Jinja macro fails to extract data from column-contained SQL string [superset]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
GitHub user dosubot[bot] added a comment to the discussion: Jinja macro fails to extract data from column-contained SQL string
<!-- Answer -->
You can't pass a column value to a Jinja macro in Superset because Jinja macros are evaluated before the SQL is sent to the database, during the template rendering phase. At that point, only static values or template parameters are available—actual column data is not accessible yet. That's why your macro works with hardcoded strings but fails with column references like `t.sql`—the macro receives the literal string `'t.sql'`, not the value from each row.
If you need to extract information from SQL strings stored in a column, you'll need to do it using SQL functions or database-side logic (like a PostgreSQL function or UDF), or by exporting the data and processing it externally. This limitation is fundamental to how Superset and Jinja templating work and is consistent across SQL Lab and dataset queries [[source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py)].
For more details, see the related discussion and code references:
- [Jinja macros are evaluated before SQL execution, so they can't access column values](https://github.com/apache/superset/issues/33591)
- [Superset Jinja template rendering lifecycle](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/sqllab/query_render.py)
<!-- Dosu Comment Footer -->
*To reply, just mention [@dosu](https://go.dosu.dev/dosubot).*
---
<sup>How did I do? [Good](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/c96b567f-64df-4c4a-958b-ed0bc76e8b1d?feedback_type=other)</sup> [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/34234)
GitHub link: https://github.com/apache/superset/discussions/34234#discussioncomment-13816735
----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [D] Jinja macro fails to extract data from column-contained SQL string [superset]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
GitHub user dosubot[bot] edited a comment on the discussion: Jinja macro fails to extract data from column-contained SQL string
<!-- Answer -->
You can't pass a column value to a Jinja macro in Superset because Jinja macros are evaluated before the SQL is sent to the database, during the template rendering phase. At that point, only static values or template parameters are available—actual column data is not accessible yet. That's why your macro works with hardcoded strings but fails with column references like `t.sql`—the macro receives the literal string `'t.sql'`, not the value from each row.
If you need to extract information from SQL strings stored in a column, you'll need to do it using SQL functions or database-side logic (like a PostgreSQL function or UDF), or by exporting the data and processing it externally. This limitation is fundamental to how Superset and Jinja templating work and is consistent across SQL Lab and dataset queries [[source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py)].
For more details, see the related discussion and code references:
- [Jinja macros are evaluated before SQL execution, so they can't access column values](https://github.com/apache/superset/issues/33591)
- [Superset Jinja template rendering lifecycle](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/sqllab/query_render.py)
<!-- Dosu Comment Footer -->
*To reply, just mention [@dosu](https://go.dosu.dev/dosubot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=reply-with-mention&utm_term=apache%2Fsuperset).*
---
Docs are dead. Just use [Dosu](https://dosu.dev?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=static-docs-tagline&utm_term=apache%2Fsuperset).
[](https://app.dosu.dev/response-feedback?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=static-docs-feedback&utm_term=apache%2Fsuperset&message_id=c96b567f-64df-4c4a-958b-ed0bc76e8b1d) [](https://github.dosu.com/apache/superset?utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=static-docs-ask-repo&utm_term=apache%2Fsuperset) [](https://app.dosu.dev/signup?referrer=openSource&source=github-footer&utm_source=github&utm_medium=bot-comment&utm_campaign=github-comment-footer-20260415&utm_content=static-docs-share-team&utm_term=apache%2Fsuperset)
GitHub link: https://github.com/apache/superset/discussions/34234#discussioncomment-13816735
----
This is an automatically sent email for notifications@superset.apache.org.
To unsubscribe, please send an email to: notifications-unsubscribe@superset.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org