Spaces:

Leon4gr45
/

powerbi

Paused

powerbi / docs /user_docs_versioned_docs /version-6.1.0 /using-superset /sql-templating.mdx

Upload folder using huggingface_hub (part 9)

e535652 verified 2 months ago

7.45 kB

---
title: SQL Templating
sidebar_position: 4
description: Use Jinja templates in SQL Lab and virtual datasets to create dynamic queries
keywords: [sql templating, jinja, sql lab, virtual datasets, dynamic queries]
---

{/*
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
*/}

# SQL Templating

Superset supports [Jinja templating](https://jinja.palletsprojects.com/) in SQL Lab queries and virtual datasets. This allows you to write dynamic SQL that responds to filters, user context, and URL parameters.

:::note
SQL templating must be enabled by your administrator via the `ENABLE_TEMPLATE_PROCESSING` feature flag.
For advanced configuration options, see the [SQL Templating Configuration Guide](/admin-docs/configuration/sql-templating).
:::

## Using Jinja in Calculated Columns

Jinja template macros are available in calculated column expressions in the dataset editor — not just in SQL Lab queries and virtual datasets. This allows column expressions to reference the current user or dynamic context.

**Example: User-scoped calculated column**

```sql
CASE WHEN sales_rep = '{{ current_username() }}' THEN amount ELSE 0 END
```

**Example: Conditional display based on role**

Because `current_user_roles()` returns a Python list, test role membership with a Jinja
conditional at template time rather than matching against the list's string representation:
```sql
{% if 'Finance' in current_user_roles() %}revenue{% else %}NULL{% endif %} AS finance_revenue
```
:::note
The `ENABLE_TEMPLATE_PROCESSING` feature flag must be enabled by your administrator for Jinja in calculated columns to work.
:::
## Basic Usage
Jinja templates use double curly braces `{{ }}` for expressions and `{% %}` for logic blocks.
### Using Parameters
You can define parameters in SQL Lab via the **Parameters** menu as JSON:
```json
{
  "my_table": "sales",
  "start_date": "2024-01-01"
}
```
Then reference them in your query:
```sql
SELECT *
FROM {{ my_table }}
WHERE order_date >= '{{ start_date }}'
```
### Conditional Logic
Use Jinja's logic blocks for conditional SQL:

```sql
SELECT *
FROM orders
WHERE 1 = 1
{% if start_date %}
  AND order_date >= '{{ start_date }}'
{% endif %}
{% if end_date %}
  AND order_date < '{{ end_date }}'
{% endif %}
```

## Available Macros

Superset provides built-in macros for common use cases.

### User Context

| Macro                        | Description                             |
| ---------------------------- | --------------------------------------- |
| `{{ current_username() }}`   | Returns the logged-in user's username   |
| `{{ current_user_id() }}`    | Returns the logged-in user's account ID |
| `{{ current_user_email() }}` | Returns the logged-in user's email      |
| `{{ current_user_roles() }}` | Returns an array of the user's roles    |

**Example: Row-level filtering by user**

```sql
SELECT *
FROM sales_data
WHERE sales_rep = '{{ current_username() }}'
```

**Example: Role-based access**

```sql
SELECT *
FROM users
WHERE role IN {{ current_user_roles()|where_in }}
```

### Filter Values

Access dashboard and chart filter values in your queries:

| Macro                           | Description                     |
| ------------------------------- | ------------------------------- |
| `{{ filter_values('column') }}` | Returns filter values as a list |
| `{{ get_filters('column') }}`   | Returns filters with operators  |

**Example: Using filter values**

```sql
SELECT product, SUM(revenue) as total
FROM sales
WHERE region IN {{ filter_values('region')|where_in }}
GROUP BY product
```

The `where_in` filter converts the list to SQL format: `('value1', 'value2', 'value3')`

### Time Filters

For charts with time range filters:

| Macro                             | Description                                        |
| --------------------------------- | -------------------------------------------------- |
| `{{ get_time_filter('column') }}` | Returns time filter with `from_expr` and `to_expr` |

**Example: Time-filtered virtual dataset**

```sql
{% set time_filter = get_time_filter("order_date", default="Last 7 days") %}
SELECT *
FROM orders
WHERE order_date >= {{ time_filter.from_expr }}
  AND order_date < {{ time_filter.to_expr }}
```

### URL Parameters

Pass custom values via URL query strings:

```sql
SELECT *
FROM orders
WHERE country = '{{ url_param('country') }}'
```

Access via: `superset.example.com/sqllab?country=US`

### Reusing Dataset Definitions

Query existing datasets by ID:

```sql
-- Query a dataset (ID 42) as a table
SELECT * FROM {{ dataset(42) }} LIMIT 100

-- Include computed metrics
SELECT * FROM {{ dataset(42, include_metrics=True) }}
```

Reuse metric definitions across queries:

```sql
SELECT
  category,
  {{ metric('total_revenue') }} as revenue
FROM sales
GROUP BY category
```

## Testing Templates in SQL Lab

Some variables like `from_dttm` and `filter_values()` only work when filters are applied from dashboards or charts. To test in SQL Lab:

**Option 1: Use defaults**

```sql
SELECT *
FROM orders
WHERE date >= '{{ from_dttm | default("2024-01-01", true) }}'
```

**Option 2: Set test parameters**

Add to the Parameters menu:

```json
{
  "_filters": [{ "col": "region", "op": "IN", "val": ["US", "EU"] }]
}
```

**Option 3: Use `{% set %}`**

```sql
{% set start_date = "2024-01-01" %}
SELECT * FROM orders WHERE date >= '{{ start_date }}'
```

## Common Patterns

### Dynamic Table Selection

```sql
{% set table_name = url_param('table') or 'default_table' %}
SELECT * FROM {{ table_name }}
```

### User-Specific Data Access

```sql
SELECT *
FROM sensitive_data
WHERE department IN (
  SELECT department
  FROM user_permissions
  WHERE username = '{{ current_username() }}'
)
```

### Time-Based Partitioning

```sql
{% set time_filter = get_time_filter("event_date", remove_filter=True) %}
SELECT *
FROM events
WHERE event_date >= {{ time_filter.from_expr }}
  AND event_date < {{ time_filter.to_expr }}
```

Using `remove_filter=True` applies the filter in the inner query for better performance.

## Tips

- Use `|where_in` filter to convert lists to SQL `IN` clauses
- Use `|tojson` to serialize arrays as JSON strings
- Test queries with explicit parameter values before relying on filter context
- For complex templating needs, ask your administrator about custom Jinja macros
- **Format SQL is Jinja-aware**: The "Format SQL" button in SQL Lab correctly preserves `{{ }}` and `{% %}` template syntax and applies your selected database's SQL dialect when formatting.
:::resources
- [Admin Guide: SQL Templating Configuration](/admin-docs/configuration/sql-templating)
- [Blog: Intro to Jinja Templating in Apache Superset](https://preset.io/blog/intro-jinja-templating-apache-superset/)
  :::

	---
	title: SQL Templating
	sidebar_position: 4
	description: Use Jinja templates in SQL Lab and virtual datasets to create dynamic queries
	keywords: [sql templating, jinja, sql lab, virtual datasets, dynamic queries]
	---

	{/*
	Licensed to the Apache Software Foundation (ASF) under one
	or more contributor license agreements. See the NOTICE file
	distributed with this work for additional information
	regarding copyright ownership. The ASF licenses this file
	to you under the Apache License, Version 2.0 (the
	"License"); you may not use this file except in compliance
	with the License. You may obtain a copy of the License at

	http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing,
	software distributed under the License is distributed on an
	"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	KIND, either express or implied. See the License for the
	specific language governing permissions and limitations
	under the License.
	*/}

	# SQL Templating

	Superset supports [Jinja templating](https://jinja.palletsprojects.com/) in SQL Lab queries and virtual datasets. This allows you to write dynamic SQL that responds to filters, user context, and URL parameters.

	:::note
	SQL templating must be enabled by your administrator via the `ENABLE_TEMPLATE_PROCESSING` feature flag.
	For advanced configuration options, see the [SQL Templating Configuration Guide](/admin-docs/configuration/sql-templating).
	:::

	## Using Jinja in Calculated Columns

	Jinja template macros are available in calculated column expressions in the dataset editor — not just in SQL Lab queries and virtual datasets. This allows column expressions to reference the current user or dynamic context.

	Example: User-scoped calculated column

	```sql
	CASE WHEN sales_rep = '{{ current_username() }}' THEN amount ELSE 0 END
	```

	Example: Conditional display based on role

	Because `current_user_roles()` returns a Python list, test role membership with a Jinja
	conditional at template time rather than matching against the list's string representation:

	```sql
	{% if 'Finance' in current_user_roles() %}revenue{% else %}NULL{% endif %} AS finance_revenue
	```

	:::note
	The `ENABLE_TEMPLATE_PROCESSING` feature flag must be enabled by your administrator for Jinja in calculated columns to work.
	:::

	## Basic Usage

	Jinja templates use double curly braces `{{ }}` for expressions and `{% %}` for logic blocks.

	### Using Parameters

	You can define parameters in SQL Lab via the Parameters menu as JSON:

	```json
	{
	"my_table": "sales",
	"start_date": "2024-01-01"
	}
	```

	Then reference them in your query:

	```sql
	SELECT *
	FROM {{ my_table }}
	WHERE order_date >= '{{ start_date }}'
	```

	### Conditional Logic

	Use Jinja's logic blocks for conditional SQL:

	```sql
	SELECT *
	FROM orders
	WHERE 1 = 1
	{% if start_date %}
	AND order_date >= '{{ start_date }}'
	{% endif %}
	{% if end_date %}
	AND order_date < '{{ end_date }}'
	{% endif %}
	```

	## Available Macros

	Superset provides built-in macros for common use cases.

	### User Context

	\| Macro \| Description \|
	\| ---------------------------- \| --------------------------------------- \|
	\| `{{ current_username() }}` \| Returns the logged-in user's username \|
	\| `{{ current_user_id() }}` \| Returns the logged-in user's account ID \|
	\| `{{ current_user_email() }}` \| Returns the logged-in user's email \|
	\| `{{ current_user_roles() }}` \| Returns an array of the user's roles \|

	Example: Row-level filtering by user

	```sql
	SELECT *
	FROM sales_data
	WHERE sales_rep = '{{ current_username() }}'
	```

	Example: Role-based access

	```sql
	SELECT *
	FROM users
	WHERE role IN {{ current_user_roles()\|where_in }}
	```

	### Filter Values

	Access dashboard and chart filter values in your queries:

	\| Macro \| Description \|
	\| ------------------------------- \| ------------------------------- \|
	\| `{{ filter_values('column') }}` \| Returns filter values as a list \|
	\| `{{ get_filters('column') }}` \| Returns filters with operators \|

	Example: Using filter values

	```sql
	SELECT product, SUM(revenue) as total
	FROM sales
	WHERE region IN {{ filter_values('region')\|where_in }}
	GROUP BY product
	```

	The `where_in` filter converts the list to SQL format: `('value1', 'value2', 'value3')`

	### Time Filters

	For charts with time range filters:

	\| Macro \| Description \|
	\| --------------------------------- \| -------------------------------------------------- \|
	\| `{{ get_time_filter('column') }}` \| Returns time filter with `from_expr` and `to_expr` \|

	Example: Time-filtered virtual dataset

	```sql
	{% set time_filter = get_time_filter("order_date", default="Last 7 days") %}
	SELECT *
	FROM orders
	WHERE order_date >= {{ time_filter.from_expr }}
	AND order_date < {{ time_filter.to_expr }}
	```

	### URL Parameters

	Pass custom values via URL query strings:

	```sql
	SELECT *
	FROM orders
	WHERE country = '{{ url_param('country') }}'
	```

	Access via: `superset.example.com/sqllab?country=US`

	### Reusing Dataset Definitions

	Query existing datasets by ID:

	```sql
	-- Query a dataset (ID 42) as a table
	SELECT * FROM {{ dataset(42) }} LIMIT 100

	-- Include computed metrics
	SELECT * FROM {{ dataset(42, include_metrics=True) }}
	```

	Reuse metric definitions across queries:

	```sql
	SELECT
	category,
	{{ metric('total_revenue') }} as revenue
	FROM sales
	GROUP BY category
	```

	## Testing Templates in SQL Lab

	Some variables like `from_dttm` and `filter_values()` only work when filters are applied from dashboards or charts. To test in SQL Lab:

	Option 1: Use defaults

	```sql
	SELECT *
	FROM orders
	WHERE date >= '{{ from_dttm \| default("2024-01-01", true) }}'
	```

	Option 2: Set test parameters

	Add to the Parameters menu:

	```json
	{
	"_filters": [{ "col": "region", "op": "IN", "val": ["US", "EU"] }]
	}
	```

	Option 3: Use `{% set %}`

	```sql
	{% set start_date = "2024-01-01" %}
	SELECT * FROM orders WHERE date >= '{{ start_date }}'
	```

	## Common Patterns

	### Dynamic Table Selection

	```sql
	{% set table_name = url_param('table') or 'default_table' %}
	SELECT * FROM {{ table_name }}
	```

	### User-Specific Data Access

	```sql
	SELECT *
	FROM sensitive_data
	WHERE department IN (
	SELECT department
	FROM user_permissions
	WHERE username = '{{ current_username() }}'
	)
	```

	### Time-Based Partitioning

	```sql
	{% set time_filter = get_time_filter("event_date", remove_filter=True) %}
	SELECT *
	FROM events
	WHERE event_date >= {{ time_filter.from_expr }}
	AND event_date < {{ time_filter.to_expr }}
	```

	Using `remove_filter=True` applies the filter in the inner query for better performance.

	## Tips

	- Use `\|where_in` filter to convert lists to SQL `IN` clauses
	- Use `\|tojson` to serialize arrays as JSON strings
	- Test queries with explicit parameter values before relying on filter context
	- For complex templating needs, ask your administrator about custom Jinja macros
	- Format SQL is Jinja-aware: The "Format SQL" button in SQL Lab correctly preserves `{{ }}` and `{% %}` template syntax and applies your selected database's SQL dialect when formatting.

	:::resources

	- [Admin Guide: SQL Templating Configuration](/admin-docs/configuration/sql-templating)
	- [Blog: Intro to Jinja Templating in Apache Superset](https://preset.io/blog/intro-jinja-templating-apache-superset/)
	:::