| <!doctype html> |
| <html lang="en" dir="ltr" class="docs-wrapper plugin-docs plugin-id-default docs-version-6.0.0 docs-doc-page docs-doc-id-configuration/sql-templating" data-has-hydrated="false"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="generator" content="Docusaurus v3.8.1"> |
| <title data-rh="true">SQL Templating | Superset</title><meta data-rh="true" name="viewport" content="width=device-width,initial-scale=1"><meta data-rh="true" name="twitter:card" content="summary_large_image"><meta data-rh="true" property="og:url" content="https://superset.apache.org/docs/6.0.0/configuration/sql-templating"><meta data-rh="true" property="og:locale" content="en"><meta data-rh="true" name="docusaurus_locale" content="en"><meta data-rh="true" name="docsearch:language" content="en"><meta data-rh="true" name="docusaurus_version" content="6.0.0"><meta data-rh="true" name="docusaurus_tag" content="docs-default-6.0.0"><meta data-rh="true" name="docsearch:version" content="6.0.0"><meta data-rh="true" name="docsearch:docusaurus_tag" content="docs-default-6.0.0"><meta data-rh="true" property="og:title" content="SQL Templating | Superset"><meta data-rh="true" name="description" content="Jinja Templates"><meta data-rh="true" property="og:description" content="Jinja Templates"><link data-rh="true" rel="icon" href="/img/favicon.ico"><link data-rh="true" rel="canonical" href="https://superset.apache.org/docs/6.0.0/configuration/sql-templating"><link data-rh="true" rel="alternate" href="https://superset.apache.org/docs/6.0.0/configuration/sql-templating" hreflang="en"><link data-rh="true" rel="alternate" href="https://superset.apache.org/docs/6.0.0/configuration/sql-templating" hreflang="x-default"><link data-rh="true" rel="preconnect" href="https://WR5FASX5ED-dsn.algolia.net" crossorigin="anonymous"><script data-rh="true" type="application/ld+json">{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"SQL Templating","item":"https://superset.apache.org/docs/6.0.0/configuration/sql-templating"}]}</script><link rel="search" type="application/opensearchdescription+xml" title="Superset" href="/opensearch.xml"> |
| |
| |
| |
| |
| |
| <script src="https://widget.kapa.ai/kapa-widget.bundle.js" async data-website-id="c6a8a8b8-3127-48f9-97a7-51e9e10d20d0" data-project-name="Apache Superset" data-project-color="#FFFFFF" data-project-logo="https://images.seeklogo.com/logo-png/50/2/superset-icon-logo-png_seeklogo-500354.png" data-modal-override-open-id="ask-ai-input" data-modal-override-open-class="search-input" data-modal-disclaimer="This is a custom LLM for Apache Superset with access to all [documentation](superset.apache.org/docs/intro/), [GitHub Open Issues, PRs and READMEs](github.com/apache/superset).&#10;&#10;Companies deploy assistants like this ([built by kapa.ai](https://kapa.ai)) on docs via [website widget](https://docs.kapa.ai/integrations/website-widget) (Docker, Reddit), in [support forms](https://docs.kapa.ai/integrations/support-form-deflector) for ticket deflection (Monday.com, Mapbox), or as [Slack bots](https://docs.kapa.ai/integrations/slack-bot) with private sources." data-modal-example-questions="How do I install Superset?,How can I contribute to Superset?" data-button-text-color="rgb(81,166,197)" data-modal-header-bg-color="#ffffff" data-modal-title-color="rgb(81,166,197)" data-modal-title="Apache Superset AI" data-modal-disclaimer-text-color="#000000" data-consent-required="true" data-consent-screen-disclaimer="By clicking "I agree, let's chat", you consent to the use of the AI assistant in accordance with kapa.ai's [Privacy Policy](https://www.kapa.ai/content/privacy-policy). This service uses reCAPTCHA, which requires your consent to Google's [Privacy Policy](https://policies.google.com/privacy) and [Terms of Service](https://policies.google.com/terms). By proceeding, you explicitly agree to both kapa.ai's and Google's privacy policies."></script><link rel="stylesheet" href="/assets/css/styles.f7e4fcdd.css"> |
| <script src="/assets/js/runtime~main.6c7cf69f.js" defer="defer"></script> |
| <script src="/assets/js/main.a3befe85.js" defer="defer"></script> |
| </head> |
| <body class="navigation-with-keyboard"> |
| <svg xmlns="http://www.w3.org/2000/svg" style="display: none;"><defs> |
| <symbol id="theme-svg-external-link" viewBox="0 0 24 24"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"/></symbol> |
| </defs></svg> |
| <script>!function(){var t=function(){try{return new URLSearchParams(window.location.search).get("docusaurus-theme")}catch(t){}}()||function(){try{return window.localStorage.getItem("theme")}catch(t){}}();document.documentElement.setAttribute("data-theme",t||(window.matchMedia("(prefers-color-scheme: dark)").matches?"dark":"light")),document.documentElement.setAttribute("data-theme-choice",t||"system")}(),function(){try{const c=new URLSearchParams(window.location.search).entries();for(var[t,e]of c)if(t.startsWith("docusaurus-data-")){var a=t.replace("docusaurus-data-","data-");document.documentElement.setAttribute(a,e)}}catch(t){}}()</script><div id="__docusaurus"><div role="region" aria-label="Skip to main content"><a class="skipToContent_fXgn" href="#__docusaurus_skipToContent_fallback">Skip to main content</a></div><nav aria-label="Main" class="theme-layout-navbar navbar navbar--fixed-top"><div class="navbar__inner"><div class="theme-layout-navbar-left navbar__items"><button aria-label="Toggle navigation bar" aria-expanded="false" class="navbar__toggle clean-btn" type="button"><svg width="30" height="30" viewBox="0 0 30 30" aria-hidden="true"><path stroke="currentColor" stroke-linecap="round" stroke-miterlimit="10" stroke-width="2" d="M4 7h22M4 15h22M4 23h22"></path></svg></button><a class="navbar__brand" href="/"><div class="navbar__logo"><img src="/img/superset-logo-horiz.svg" alt="Superset Logo" class="themedComponent_mlkZ themedComponent--light_NVdE"><img src="/img/superset-logo-horiz-dark.svg" alt="Superset Logo" class="themedComponent_mlkZ themedComponent--dark_xIcU"></div></a><div class="navbar__item dropdown dropdown--hoverable"><a href="#" aria-haspopup="true" aria-expanded="false" role="button" class="navbar__link">Documentation</a><ul class="dropdown__menu"><li><a aria-current="page" class="dropdown__link dropdown__link--active" href="/docs/6.0.0/intro">Getting Started</a></li><li><a aria-current="page" class="dropdown__link dropdown__link--active" href="/docs/6.0.0/faq">FAQ</a></li></ul></div><div class="navbar__item dropdown dropdown--hoverable"><a class="navbar__link" aria-haspopup="true" aria-expanded="false" role="button" href="/community">Community Resources</a><ul class="dropdown__menu"><li><a class="dropdown__link" href="/community">Resources</a></li><li><a href="https://github.com/apache/superset" target="_blank" rel="noopener noreferrer" class="dropdown__link">GitHub<svg width="12" height="12" aria-hidden="true" class="iconExternalLink_nPIU"><use href="#theme-svg-external-link"></use></svg></a></li><li><a href="http://bit.ly/join-superset-slack" target="_blank" rel="noopener noreferrer" class="dropdown__link">Slack<svg width="12" height="12" aria-hidden="true" class="iconExternalLink_nPIU"><use href="#theme-svg-external-link"></use></svg></a></li><li><a href="https://lists.apache.org/list.html?dev@superset.apache.org" target="_blank" rel="noopener noreferrer" class="dropdown__link">Mailing List<svg width="12" height="12" aria-hidden="true" class="iconExternalLink_nPIU"><use href="#theme-svg-external-link"></use></svg></a></li><li><a href="https://stackoverflow.com/questions/tagged/apache-superset" target="_blank" rel="noopener noreferrer" class="dropdown__link">Stack Overflow<svg width="12" height="12" aria-hidden="true" class="iconExternalLink_nPIU"><use href="#theme-svg-external-link"></use></svg></a></li></ul></div></div><div class="theme-layout-navbar-right navbar__items navbar__items--right"><a class="navbar__item navbar__link default-button-theme get-started-button" href="/docs/intro">Get Started</a><a href="https://github.com/apache/superset" target="_blank" rel="noopener noreferrer" class="navbar__item navbar__link github-button"></a><div class="toggle_vylO colorModeToggle_DEke"><button class="clean-btn toggleButton_gllP toggleButtonDisabled_aARS" type="button" disabled="" title="system mode" aria-label="Switch between dark and light mode (currently system mode)"><svg viewBox="0 0 24 24" width="24" height="24" aria-hidden="true" class="toggleIcon_g3eP lightToggleIcon_pyhR"><path fill="currentColor" d="M12,9c1.65,0,3,1.35,3,3s-1.35,3-3,3s-3-1.35-3-3S10.35,9,12,9 M12,7c-2.76,0-5,2.24-5,5s2.24,5,5,5s5-2.24,5-5 S14.76,7,12,7L12,7z M2,13l2,0c0.55,0,1-0.45,1-1s-0.45-1-1-1l-2,0c-0.55,0-1,0.45-1,1S1.45,13,2,13z M20,13l2,0c0.55,0,1-0.45,1-1 s-0.45-1-1-1l-2,0c-0.55,0-1,0.45-1,1S19.45,13,20,13z M11,2v2c0,0.55,0.45,1,1,1s1-0.45,1-1V2c0-0.55-0.45-1-1-1S11,1.45,11,2z M11,20v2c0,0.55,0.45,1,1,1s1-0.45,1-1v-2c0-0.55-0.45-1-1-1C11.45,19,11,19.45,11,20z M5.99,4.58c-0.39-0.39-1.03-0.39-1.41,0 c-0.39,0.39-0.39,1.03,0,1.41l1.06,1.06c0.39,0.39,1.03,0.39,1.41,0s0.39-1.03,0-1.41L5.99,4.58z M18.36,16.95 c-0.39-0.39-1.03-0.39-1.41,0c-0.39,0.39-0.39,1.03,0,1.41l1.06,1.06c0.39,0.39,1.03,0.39,1.41,0c0.39-0.39,0.39-1.03,0-1.41 L18.36,16.95z M19.42,5.99c0.39-0.39,0.39-1.03,0-1.41c-0.39-0.39-1.03-0.39-1.41,0l-1.06,1.06c-0.39,0.39-0.39,1.03,0,1.41 s1.03,0.39,1.41,0L19.42,5.99z M7.05,18.36c0.39-0.39,0.39-1.03,0-1.41c-0.39-0.39-1.03-0.39-1.41,0l-1.06,1.06 c-0.39,0.39-0.39,1.03,0,1.41s1.03,0.39,1.41,0L7.05,18.36z"></path></svg><svg viewBox="0 0 24 24" width="24" height="24" aria-hidden="true" class="toggleIcon_g3eP darkToggleIcon_wfgR"><path fill="currentColor" d="M9.37,5.51C9.19,6.15,9.1,6.82,9.1,7.5c0,4.08,3.32,7.4,7.4,7.4c0.68,0,1.35-0.09,1.99-0.27C17.45,17.19,14.93,19,12,19 c-3.86,0-7-3.14-7-7C5,9.07,6.81,6.55,9.37,5.51z M12,3c-4.97,0-9,4.03-9,9s4.03,9,9,9s9-4.03,9-9c0-0.46-0.04-0.92-0.1-1.36 c-0.98,1.37-2.58,2.26-4.4,2.26c-2.98,0-5.4-2.42-5.4-5.4c0-1.81,0.89-3.42,2.26-4.4C12.92,3.04,12.46,3,12,3L12,3z"></path></svg><svg viewBox="0 0 24 24" width="24" height="24" aria-hidden="true" class="toggleIcon_g3eP systemToggleIcon_QzmC"><path fill="currentColor" d="m12 21c4.971 0 9-4.029 9-9s-4.029-9-9-9-9 4.029-9 9 4.029 9 9 9zm4.95-13.95c1.313 1.313 2.05 3.093 2.05 4.95s-0.738 3.637-2.05 4.95c-1.313 1.313-3.093 2.05-4.95 2.05v-14c1.857 0 3.637 0.737 4.95 2.05z"></path></svg></button></div><div class="navbarSearchContainer_Bca1"><button type="button" class="DocSearch DocSearch-Button" aria-label="Search (Command+K)"><span class="DocSearch-Button-Container"><svg width="20" height="20" class="DocSearch-Search-Icon" viewBox="0 0 20 20" aria-hidden="true"><path d="M14.386 14.386l4.0877 4.0877-4.0877-4.0877c-2.9418 2.9419-7.7115 2.9419-10.6533 0-2.9419-2.9418-2.9419-7.7115 0-10.6533 2.9418-2.9419 7.7115-2.9419 10.6533 0 2.9419 2.9418 2.9419 7.7115 0 10.6533z" stroke="currentColor" fill="none" fill-rule="evenodd" stroke-linecap="round" stroke-linejoin="round"></path></svg><span class="DocSearch-Button-Placeholder">Search</span></span><span class="DocSearch-Button-Keys"></span></button></div></div></div><div role="presentation" class="navbar-sidebar__backdrop"></div></nav><div id="__docusaurus_skipToContent_fallback" class="theme-layout-main main-wrapper mainWrapper_z2l0"><div class="docsWrapper_hBAB"><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_sjWU" type="button"></button><div class="docRoot_UBD9"><aside class="theme-doc-sidebar-container docSidebarContainer_YfHR"><div class="sidebarViewport_aRkj"><div class="sidebar_njMd"><nav aria-label="Docs sidebar" class="menu thin-scrollbar menu_SIkG"><ul class="theme-doc-sidebar-menu menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/6.0.0/intro">Introduction</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/6.0.0/quickstart">Quickstart</a></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/6.0.0/installation/architecture">Installation</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" role="button" aria-expanded="true" href="/docs/6.0.0/configuration/configuring-superset">Configuration</a></div><ul class="menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/configuring-superset">Configuring Superset</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/databases">Connecting to Databases</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/alerts-reports">Alerts and Reports</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/cache">Caching</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/async-queries-celery">Async Queries via Celery</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link menu__link--active" aria-current="page" tabindex="0" href="/docs/6.0.0/configuration/sql-templating">SQL Templating</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/timezones">Timezones</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/networking-settings">Network and Security Settings</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/event-logging">Event Logging</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/country-map-tools">Country Map Tools</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/importing-exporting-datasources">Importing and Exporting Datasources</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/map-tiles">Map Tiles</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/6.0.0/configuration/theming">Theming</a></li></ul></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/6.0.0/using-superset/creating-your-first-dashboard">Using Superset</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/6.0.0/contributing/">Contributing</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" role="button" aria-expanded="false" href="/docs/6.0.0/security/">Security</a></div></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/6.0.0/faq">FAQ</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-1 menu__list-item"><a class="menu__link" href="/docs/6.0.0/api">API</a></li></ul></nav><button type="button" title="Collapse sidebar" aria-label="Collapse sidebar" class="button button--secondary button--outline collapseSidebarButton_PEFL"><svg width="20" height="20" aria-hidden="true" class="collapseSidebarButtonIcon_kv0_"><g fill="#7a7a7a"><path d="M9.992 10.023c0 .2-.062.399-.172.547l-4.996 7.492a.982.982 0 01-.828.454H1c-.55 0-1-.453-1-1 0-.2.059-.403.168-.551l4.629-6.942L.168 3.078A.939.939 0 010 2.528c0-.548.45-.997 1-.997h2.996c.352 0 .649.18.828.45L9.82 9.472c.11.148.172.347.172.55zm0 0"></path><path d="M19.98 10.023c0 .2-.058.399-.168.547l-4.996 7.492a.987.987 0 01-.828.454h-3c-.547 0-.996-.453-.996-1 0-.2.059-.403.168-.551l4.625-6.942-4.625-6.945a.939.939 0 01-.168-.55 1 1 0 01.996-.997h3c.348 0 .649.18.828.45l4.996 7.492c.11.148.168.347.168.55zm0 0"></path></g></svg></button></div></div></aside><main class="docMainContainer_TBSr"><div class="container padding-top--md padding-bottom--lg"><div class="row"><div class="col docItemCol_VOVn"><div class="docItemContainer_Djhp"><article><nav class="theme-doc-breadcrumbs breadcrumbsContainer_Z_bl" aria-label="Breadcrumbs"><ul class="breadcrumbs"><li class="breadcrumbs__item"><a aria-label="Home page" class="breadcrumbs__link" href="/"><svg viewBox="0 0 24 24" class="breadcrumbHomeIcon_YNFT"><path d="M10 19v-5h4v5c0 .55.45 1 1 1h3c.55 0 1-.45 1-1v-7h1.7c.46 0 .68-.57.33-.87L12.67 3.6c-.38-.34-.96-.34-1.34 0l-8.36 7.53c-.34.3-.13.87.33.87H5v7c0 .55.45 1 1 1h3c.55 0 1-.45 1-1z" fill="currentColor"></path></svg></a></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Configuration</span></li><li class="breadcrumbs__item breadcrumbs__item--active"><span class="breadcrumbs__link">SQL Templating</span></li></ul></nav><span class="versionBadge_QOso">Version:<!-- --> <a class="ant-dropdown-trigger versionSelector_oOvN">6.0.0<!-- --> <span role="img" aria-label="down" class="anticon anticon-down"><svg viewBox="64 64 896 896" focusable="false" data-icon="down" width="1em" height="1em" fill="currentColor" aria-hidden="true"><path d="M884 256h-75c-5.1 0-9.9 2.5-12.9 6.6L512 654.2 227.9 262.6c-3-4.1-7.8-6.6-12.9-6.6h-75c-6.5 0-10.3 7.4-6.5 12.7l352.6 486.1c12.8 17.6 39 17.6 51.7 0l352.6-486.1c3.9-5.3.1-12.7-6.4-12.7z"></path></svg></span></a></span><div class="tocCollapsible_ETCw theme-doc-toc-mobile tocMobile_ITEo"><button type="button" class="clean-btn tocCollapsibleButton_TO0P">On this page</button></div><div class="theme-doc-markdown markdown"><header><h1>SQL Templating</h1></header> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="jinja-templates">Jinja Templates<a href="#jinja-templates" class="hash-link" aria-label="Direct link to Jinja Templates" title="Direct link to Jinja Templates"></a></h2> |
| <p>SQL Lab and Explore supports <a href="https://jinja.palletsprojects.com/en/2.11.x/" target="_blank" rel="noopener noreferrer">Jinja templating</a> in queries. |
| To enable templating, the <code>ENABLE_TEMPLATE_PROCESSING</code> <a href="/docs/configuration/configuring-superset#feature-flags">feature flag</a> needs to be enabled in |
| <code>superset_config.py</code>. When templating is enabled, python code can be embedded in virtual datasets and |
| in Custom SQL in the filter and metric controls in Explore. By default, the following variables are |
| made available in the Jinja context:</p> |
| <ul> |
| <li><code>columns</code>: columns which to group by in the query</li> |
| <li><code>filter</code>: filters applied in the query</li> |
| <li><code>from_dttm</code>: start <code>datetime</code> value from the selected time range (<code>None</code> if undefined) (deprecated beginning in version 5.0, use <code>get_time_filter</code> instead)</li> |
| <li><code>to_dttm</code>: end <code>datetime</code> value from the selected time range (<code>None</code> if undefined). (deprecated beginning in version 5.0, use <code>get_time_filter</code> instead)</li> |
| <li><code>groupby</code>: columns which to group by in the query (deprecated)</li> |
| <li><code>metrics</code>: aggregate expressions in the query</li> |
| <li><code>row_limit</code>: row limit of the query</li> |
| <li><code>row_offset</code>: row offset of the query</li> |
| <li><code>table_columns</code>: columns available in the dataset</li> |
| <li><code>time_column</code>: temporal column of the query (<code>None</code> if undefined)</li> |
| <li><code>time_grain</code>: selected time grain (<code>None</code> if undefined)</li> |
| </ul> |
| <p>For example, to add a time range to a virtual dataset, you can write the following:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> tbl</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> dttm_col </span><span class="token operator" style="color:rgb(212, 212, 212)">></span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'{{ from_dttm }}'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">and</span><span class="token plain"> dttm_col </span><span class="token operator" style="color:rgb(212, 212, 212)"><</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'{{ to_dttm }}'</span><br></span></code></pre></div></div> |
| <p>You can also use <a href="https://jinja.palletsprojects.com/en/2.11.x/templates/#tests" target="_blank" rel="noopener noreferrer">Jinja's logic</a> |
| to make your query robust to clearing the timerange filter:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> tbl</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">if</span><span class="token plain"> from_dttm </span><span class="token operator" style="color:rgb(212, 212, 212)">is</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">not</span><span class="token plain"> none </span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> dttm_col </span><span class="token operator" style="color:rgb(212, 212, 212)">></span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'{{ from_dttm }}'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">AND</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain"> endif </span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">if</span><span class="token plain"> to_dttm </span><span class="token operator" style="color:rgb(212, 212, 212)">is</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">not</span><span class="token plain"> none </span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> dttm_col </span><span class="token operator" style="color:rgb(212, 212, 212)"><</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'{{ to_dttm }}'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">AND</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain"> endif </span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><br></span></code></pre></div></div> |
| <p>The <code>1 = 1</code> at the end ensures a value is present for the <code>WHERE</code> clause even when |
| the time filter is not set. For many database engines, this could be replaced with <code>true</code>.</p> |
| <p>Note that the Jinja parameters are called within <em>double</em> brackets in the query and with |
| <em>single</em> brackets in the logic blocks.</p> |
| <p>To add custom functionality to the Jinja context, you need to overload the default Jinja |
| context in your environment by defining the <code>JINJA_CONTEXT_ADDONS</code> in your superset configuration |
| (<code>superset_config.py</code>). Objects referenced in this dictionary are made available for users to use |
| where the Jinja context is made available.</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">JINJA_CONTEXT_ADDONS </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'my_crazy_macro'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">lambda</span><span class="token plain"> x</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> x</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token number" style="color:rgb(181, 206, 168)">2</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>Default values for jinja templates can be specified via <code>Parameters</code> menu in the SQL Lab user interface. |
| In the UI you can assign a set of parameters as JSON</p> |
| <div class="language-json codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-json codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token property">"my_table"</span><span class="token operator" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"foo"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>The parameters become available in your SQL (example: <code>SELECT * FROM {{ my_table }}</code> ) by using Jinja templating syntax. |
| SQL Lab template parameters are stored with the dataset as <code>TEMPLATE PARAMETERS</code>.</p> |
| <p>There is a special <code>_filters</code> parameter which can be used to test filters used in the jinja template.</p> |
| <div class="language-json codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-json codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token property">"_filters"</span><span class="token operator" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token property">"col"</span><span class="token operator" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"action_type"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token property">"op"</span><span class="token operator" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"IN"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token property">"val"</span><span class="token operator" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token string" style="color:rgb(206, 145, 120)">"sell"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"buy"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">action</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">count</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">as</span><span class="token plain"> times</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> logs</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">action</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">in</span><span class="token plain"> {{ filter_values</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'action_type'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token operator" style="color:rgb(212, 212, 212)">|</span><span class="token plain">where_in }}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">BY</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">action</span><br></span></code></pre></div></div> |
| <p>Note <code>_filters</code> is not stored with the dataset. It's only used within the SQL Lab UI.</p> |
| <p>Besides default Jinja templating, SQL lab also supports self-defined template processor by setting |
| the <code>CUSTOM_TEMPLATE_PROCESSORS</code> in your superset configuration. The values in this dictionary |
| overwrite the default Jinja template processors of the specified database engine. The example below |
| configures a custom presto template processor which implements its own logic of processing macro |
| template with regex parsing. It uses the <code>$</code> style macro instead of <code>{{ }}</code> style in Jinja |
| templating.</p> |
| <p>By configuring it with <code>CUSTOM_TEMPLATE_PROCESSORS</code>, a SQL template on a presto database is |
| processed by the custom one rather than the default one.</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">def</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">DATE</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> ts</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> datetime</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> day_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> SupportsInt </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">0</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> hour_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> SupportsInt </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">0</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">></span><span class="token plain"> </span><span class="token builtin" style="color:rgb(86, 156, 214)">str</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:rgb(206, 145, 120)">"""Current day as a string."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> day_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> hour_offset </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token builtin" style="color:rgb(86, 156, 214)">int</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">day_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token builtin" style="color:rgb(86, 156, 214)">int</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">hour_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> offset_day </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">ts </span><span class="token operator" style="color:rgb(212, 212, 212)">+</span><span class="token plain"> timedelta</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">days</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain">day_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> hours</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain">hour_offset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">date</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">return</span><span class="token plain"> </span><span class="token builtin" style="color:rgb(86, 156, 214)">str</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">offset_day</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">class</span><span class="token plain"> </span><span class="token class-name" style="color:rgb(78, 201, 176)">CustomPrestoTemplateProcessor</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">PrestoTemplateProcessor</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:rgb(206, 145, 120)">"""A custom presto template processor."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> engine </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"presto"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">def</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">process_template</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">self</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> sql</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token builtin" style="color:rgb(86, 156, 214)">str</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">**</span><span class="token plain">kwargs</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">></span><span class="token plain"> </span><span class="token builtin" style="color:rgb(86, 156, 214)">str</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:rgb(206, 145, 120)">"""Processes a sql template with $ style macro using regex."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token comment" style="color:rgb(106, 153, 85)"># Add custom macros functions.</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> macros </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"DATE"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> partial</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">DATE</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> datetime</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">utcnow</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token plain"> </span><span class="token comment" style="color:rgb(106, 153, 85)"># type: Dict[str, Any]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token comment" style="color:rgb(106, 153, 85)"># Update with macros defined in context and kwargs.</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> macros</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">update</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">self</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">context</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> macros</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">update</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">kwargs</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">def</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">replacer</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token keyword" style="color:rgb(86, 156, 214)">match</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:rgb(206, 145, 120)">"""Expand $ style macros with corresponding function calls."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> macro_name</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> args_str </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">match</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">groups</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> args </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token plain">a</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">strip</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">for</span><span class="token plain"> a </span><span class="token keyword" style="color:rgb(86, 156, 214)">in</span><span class="token plain"> args_str</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">split</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">","</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">if</span><span class="token plain"> args </span><span class="token operator" style="color:rgb(212, 212, 212)">==</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token string" style="color:rgb(206, 145, 120)">""</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> args </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> f </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> macros</span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token plain">macro_name</span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">return</span><span class="token plain"> f</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain">args</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> macro_names </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token string" style="color:rgb(206, 145, 120)">"$"</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">+</span><span class="token plain"> name </span><span class="token keyword" style="color:rgb(86, 156, 214)">for</span><span class="token plain"> name </span><span class="token keyword" style="color:rgb(86, 156, 214)">in</span><span class="token plain"> macros</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">keys</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> pattern </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">r"(%s)\s*\(([^()]*)\)"</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"|"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">join</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token builtin" style="color:rgb(86, 156, 214)">map</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">re</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">escape</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> macro_names</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">return</span><span class="token plain"> re</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">sub</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">pattern</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> replacer</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> sql</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">CUSTOM_TEMPLATE_PROCESSORS </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> CustomPrestoTemplateProcessor</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">engine</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> CustomPrestoTemplateProcessor</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>SQL Lab also includes a live query validation feature with pluggable backends. You can configure |
| which validation implementation is used with which database engine by adding a block like the |
| following to your configuration file:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">FEATURE_FLAGS </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'SQL_VALIDATORS_BY_ENGINE'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'presto'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">:</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'PrestoDBSQLValidator'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>The available validators and names can be found in |
| <a href="https://github.com/apache/superset/tree/master/superset/sql_validators" target="_blank" rel="noopener noreferrer">sql_validators</a>.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="available-macros">Available Macros<a href="#available-macros" class="hash-link" aria-label="Direct link to Available Macros" title="Direct link to Available Macros"></a></h2> |
| <p>In this section, we'll walkthrough the pre-defined Jinja macros in Superset.</p> |
| <p><strong>Current Username</strong></p> |
| <p>The <code>{{ current_username() }}</code> macro returns the <code>username</code> of the currently logged in user.</p> |
| <p>If you have caching enabled in your Superset configuration, then by default the <code>username</code> value will be used |
| by Superset when calculating the cache key. A cache key is a unique identifier that determines if there's a |
| cache hit in the future and Superset can retrieve cached data.</p> |
| <p>You can disable the inclusion of the <code>username</code> value in the calculation of the |
| cache key by adding the following parameter to your Jinja code:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"> current_username</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">add_to_cache_keys</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">False</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p><strong>Current User ID</strong></p> |
| <p>The <code>{{ current_user_id() }}</code> macro returns the account ID of the currently logged in user.</p> |
| <p>If you have caching enabled in your Superset configuration, then by default the account <code>id</code> value will be used |
| by Superset when calculating the cache key. A cache key is a unique identifier that determines if there's a |
| cache hit in the future and Superset can retrieve cached data.</p> |
| <p>You can disable the inclusion of the account <code>id</code> value in the calculation of the |
| cache key by adding the following parameter to your Jinja code:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"> current_user_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">add_to_cache_keys</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">False</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p><strong>Current User Email</strong></p> |
| <p>The <code>{{ current_user_email() }}</code> macro returns the email address of the currently logged in user.</p> |
| <p>If you have caching enabled in your Superset configuration, then by default the email address value will be used |
| by Superset when calculating the cache key. A cache key is a unique identifier that determines if there's a |
| cache hit in the future and Superset can retrieve cached data.</p> |
| <p>You can disable the inclusion of the email value in the calculation of the |
| cache key by adding the following parameter to your Jinja code:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"> current_user_email</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">add_to_cache_keys</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">False</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p><strong>Current User Roles</strong></p> |
| <p>The <code>{{ current_user_roles() }}</code> macro returns an array of roles for the logged in user.</p> |
| <p>If you have caching enabled in your Superset configuration, then by default the roles value will be used |
| by Superset when calculating the cache key. A cache key is a unique identifier that determines if there's a |
| cache hit in the future and Superset can retrieve cached data.</p> |
| <p>You can disable the inclusion of the roles value in the calculation of the |
| cache key by adding the following parameter to your Jinja code:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"> current_user_roles</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">add_to_cache_keys</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">False</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>You can json-stringify the array by adding <code>|tojson</code> to your Jinja code:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"> current_user_roles</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token operator" style="color:rgb(212, 212, 212)">|</span><span class="token plain">tojson </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>You can use the <code>|where_in</code> filter to use your roles in a SQL statement. For example, if <code>current_user_roles()</code> returns <code>['admin', 'viewer']</code>, the following template:</p> |
| <div class="language-python codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-python codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">SELECT </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"> FROM users WHERE role IN </span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token punctuation" style="color:rgb(212, 212, 212)">{</span><span class="token plain"> current_user_roles</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token operator" style="color:rgb(212, 212, 212)">|</span><span class="token plain">where_in </span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><span class="token punctuation" style="color:rgb(212, 212, 212)">}</span><br></span></code></pre></div></div> |
| <p>Will be rendered as:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> users </span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> role </span><span class="token operator" style="color:rgb(212, 212, 212)">IN</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'admin'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'viewer'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><br></span></code></pre></div></div> |
| <p><strong>Current User RLS Rules</strong></p> |
| <p>The <code>{{ current_user_rls_rules() }}</code> macro returns an array of RLS rules applied to the current dataset for the logged in user.</p> |
| <p>If you have caching enabled in your Superset configuration, then the list of RLS Rules will be used |
| by Superset when calculating the cache key. A cache key is a unique identifier that determines if there's a |
| cache hit in the future and Superset can retrieve cached data.</p> |
| <p><strong>Custom URL Parameters</strong></p> |
| <p>The <code>{{ url_param('custom_variable') }}</code> macro lets you define arbitrary URL |
| parameters and reference them in your SQL code.</p> |
| <p>Here's a concrete example:</p> |
| <ul> |
| <li> |
| <p>You write the following query in SQL Lab:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">count</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> ORDERS</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> country_code </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'{{ url_param('</span><span class="token plain">countrycode</span><span class="token string" style="color:rgb(206, 145, 120)">') }}'</span><br></span></code></pre></div></div> |
| </li> |
| <li> |
| <p>You're hosting Superset at the domain <a href="http://www.example.com" target="_blank" rel="noopener noreferrer">www.example.com</a> and you send your |
| coworker in Spain the following SQL Lab URL <code>www.example.com/superset/sqllab?countrycode=ES</code> |
| and your coworker in the USA the following SQL Lab URL <code>www.example.com/superset/sqllab?countrycode=US</code></p> |
| </li> |
| <li> |
| <p>For your coworker in Spain, the SQL Lab query will be rendered as:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">count</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> ORDERS</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> country_code </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'ES'</span><br></span></code></pre></div></div> |
| </li> |
| <li> |
| <p>For your coworker in the USA, the SQL Lab query will be rendered as:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">count</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> ORDERS</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> country_code </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'US'</span><br></span></code></pre></div></div> |
| </li> |
| </ul> |
| <p><strong>Explicitly Including Values in Cache Key</strong></p> |
| <p>The <code>{{ cache_key_wrapper() }}</code> function explicitly instructs Superset to add a value to the |
| accumulated list of values used in the calculation of the cache key.</p> |
| <p>This function is only needed when you want to wrap your own custom function return values |
| in the cache key. You can gain more context |
| <a href="https://github.com/apache/superset/blob/efd70077014cbed62e493372d33a2af5237eaadf/superset/jinja_context.py#L133-L148" target="_blank" rel="noopener noreferrer">here</a>.</p> |
| <p>Note that this function powers the caching of the <code>user_id</code> and <code>username</code> values |
| in the <code>current_user_id()</code> and <code>current_username()</code> function calls (if you have caching enabled).</p> |
| <p><strong>Filter Values</strong></p> |
| <p>You can retrieve the value for a specific filter as a list using <code>{{ filter_values() }}</code>.</p> |
| <p>This is useful if:</p> |
| <ul> |
| <li>You want to use a filter component to filter a query where the name of filter component column doesn't match the one in the select statement</li> |
| <li>You want to have the ability to filter inside the main query for performance purposes</li> |
| </ul> |
| <p>Here's a concrete example:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">action</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token function" style="color:rgb(220, 220, 170)">count</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">as</span><span class="token plain"> times</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> logs</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">action</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">in</span><span class="token plain"> {{ filter_values</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'action_type'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token operator" style="color:rgb(212, 212, 212)">|</span><span class="token plain">where_in }}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"></span><span class="token keyword" style="color:rgb(86, 156, 214)">GROUP</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">BY</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">action</span><br></span></code></pre></div></div> |
| <p>There <code>where_in</code> filter converts the list of values from <code>filter_values('action_type')</code> into a string suitable for an <code>IN</code> expression.</p> |
| <p><strong>Filters for a Specific Column</strong></p> |
| <p>The <code>{{ get_filters() }}</code> macro returns the filters applied to a given column. In addition to |
| returning the values (similar to how <code>filter_values()</code> does), the <code>get_filters()</code> macro |
| returns the operator specified in the Explore UI.</p> |
| <p>This is useful if:</p> |
| <ul> |
| <li>You want to handle more than the IN operator in your SQL clause</li> |
| <li>You want to handle generating custom SQL conditions for a filter</li> |
| <li>You want to have the ability to filter inside the main query for speed purposes</li> |
| </ul> |
| <p>Here's a concrete example:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">WITH</span><span class="token plain"> RECURSIVE</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> superiors</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain">employee_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> manager_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> full_name</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">level</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> lineage</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">AS</span><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> employee_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> manager_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> full_name</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">as</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">level</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> employee_id </span><span class="token keyword" style="color:rgb(86, 156, 214)">as</span><span class="token plain"> lineage</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> employees</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token comment" style="color:rgb(106, 153, 85)"># Render a blank line #}</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">for</span><span class="token plain"> filter </span><span class="token operator" style="color:rgb(212, 212, 212)">in</span><span class="token plain"> get_filters</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'full_name'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> remove_filter</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">True</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">if</span><span class="token plain"> filter</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">get</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'op'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'IN'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">AND</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> full_name </span><span class="token operator" style="color:rgb(212, 212, 212)">IN</span><span class="token plain"> {{ filter</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">get</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'val'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token operator" style="color:rgb(212, 212, 212)">|</span><span class="token plain">where_in }}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token plain"> endif </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">if</span><span class="token plain"> filter</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">get</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'op'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">'LIKE'</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">AND</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> full_name </span><span class="token operator" style="color:rgb(212, 212, 212)">LIKE</span><span class="token plain"> {{ </span><span class="token string" style="color:rgb(206, 145, 120)">"'"</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">+</span><span class="token plain"> filter</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">get</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token string" style="color:rgb(206, 145, 120)">'val'</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">+</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"'"</span><span class="token plain"> }}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token plain"> endif </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> {</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token plain"> endfor </span><span class="token operator" style="color:rgb(212, 212, 212)">-</span><span class="token operator" style="color:rgb(212, 212, 212)">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">UNION</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">ALL</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> e</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">employee_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> e</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">manager_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> e</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">full_name</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> s</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token keyword" style="color:rgb(86, 156, 214)">level</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">+</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">1</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">as</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">level</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> s</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">lineage</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> employees e</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> superiors s</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">WHERE</span><span class="token plain"> s</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">manager_id </span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token plain"> e</span><span class="token punctuation" style="color:rgb(212, 212, 212)">.</span><span class="token plain">employee_id</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> employee_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> manager_id</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> full_name</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">level</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> lineage</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"></span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> superiors</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">order</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">by</span><span class="token plain"> lineage</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">level</span><br></span></code></pre></div></div> |
| <p><strong>Time Filter</strong></p> |
| <p>The <code>{{ get_time_filter() }}</code> macro returns the time filter applied to a specific column. This is useful if you want |
| to handle time filters inside the virtual dataset, as by default the time filter is placed on the outer query. This can |
| considerably improve performance, as many databases and query engines are able to optimize the query better |
| if the temporal filter is placed on the inner query, as opposed to the outer query.</p> |
| <p>The macro takes the following parameters:</p> |
| <ul> |
| <li><code>column</code>: Name of the temporal column. Leave undefined to reference the time range from a Dashboard Native Time Range |
| filter (when present).</li> |
| <li><code>default</code>: The default value to fall back to if the time filter is not present, or has the value <code>No filter</code></li> |
| <li><code>target_type</code>: The target temporal type as recognized by the target database (e.g. <code>TIMESTAMP</code>, <code>DATE</code> or |
| <code>DATETIME</code>). If <code>column</code> is defined, the format will default to the type of the column. This is used to produce |
| the format of the <code>from_expr</code> and <code>to_expr</code> properties of the returned <code>TimeFilter</code> object.</li> |
| <li><code>strftime</code>: format using the <code>strftime</code> method of <code>datetime</code> for custom time formatting. |
| (<a href="https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes" target="_blank" rel="noopener noreferrer">see docs for valid format codes</a>). |
| When defined <code>target_type</code> will be ignored.</li> |
| <li><code>remove_filter</code>: When set to true, mark the filter as processed, removing it from the outer query. Useful when a |
| filter should only apply to the inner query.</li> |
| </ul> |
| <p>The return type has the following properties:</p> |
| <ul> |
| <li><code>from_expr</code>: the start of the time filter (if any)</li> |
| <li><code>to_expr</code>: the end of the time filter (if any)</li> |
| <li><code>time_range</code>: The applied time range</li> |
| </ul> |
| <p>Here's a concrete example using the <code>logs</code> table from the Superset metastore:</p> |
| <div class="language-text codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set time_filter = get_time_filter("dttm", remove_filter=True) %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set from_expr = time_filter.from_expr %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set to_expr = time_filter.to_expr %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set time_range = time_filter.time_range %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">SELECT</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> *,</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> '{{ time_range }}' as time_range</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">FROM logs</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% if from_expr or to_expr %}WHERE 1 = 1</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% if from_expr %}AND dttm >= {{ from_expr }}{% endif %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% if to_expr %}AND dttm < {{ to_expr }}{% endif %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% endif %}</span><br></span></code></pre></div></div> |
| <p>Assuming we are creating a table chart with a simple <code>COUNT(*)</code> as the metric with a time filter <code>Last week</code> on the |
| <code>dttm</code> column, this would render the following query on Postgres (note the formatting of the temporal filters, and |
| the absence of time filters on the outer query):</p> |
| <div class="language-text codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">SELECT COUNT(*) AS count</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">FROM</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> (SELECT *,</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> 'Last week' AS time_range</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> FROM public.logs</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> WHERE 1 = 1</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> AND dttm >= TO_TIMESTAMP('2024-08-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> AND dttm < TO_TIMESTAMP('2024-09-03 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')) AS virtual_table</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">ORDER BY count DESC</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">LIMIT 1000;</span><br></span></code></pre></div></div> |
| <p>When using the <code>default</code> parameter, the templated query can be simplified, as the endpoints will always be defined |
| (to use a fixed time range, you can also use something like <code>default="2024-08-27 : 2024-09-03"</code>)</p> |
| <div class="language-text codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set time_filter = get_time_filter("dttm", default="Last week", remove_filter=True) %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">SELECT</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> *,</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> '{{ time_filter.time_range }}' as time_range</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">FROM logs</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">WHERE</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> dttm >= {{ time_filter.from_expr }}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> AND dttm < {{ time_filter.to_expr }}</span><br></span></code></pre></div></div> |
| <p><strong>Datasets</strong></p> |
| <p>It's possible to query physical and virtual datasets using the <code>dataset</code> macro. This is useful if you've defined computed columns and metrics on your datasets, and want to reuse the definition in adhoc SQL Lab queries.</p> |
| <p>To use the macro, first you need to find the ID of the dataset. This can be done by going to the view showing all the datasets, hovering over the dataset you're interested in, and looking at its URL. For example, if the URL for a dataset is <a href="https://superset.example.org/explore/?dataset_type=table&dataset_id=42" target="_blank" rel="noopener noreferrer">https://superset.example.org/explore/?dataset_type=table&dataset_id=42</a> its ID is 42.</p> |
| <p>Once you have the ID you can query it as if it were a table:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> {{ dataset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token number" style="color:rgb(181, 206, 168)">42</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> }} </span><span class="token keyword" style="color:rgb(86, 156, 214)">LIMIT</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">10</span><br></span></code></pre></div></div> |
| <p>If you want to select the metric definitions as well, in addition to the columns, you need to pass an additional keyword argument:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> {{ dataset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token number" style="color:rgb(181, 206, 168)">42</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> include_metrics</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">True</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> }} </span><span class="token keyword" style="color:rgb(86, 156, 214)">LIMIT</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">10</span><br></span></code></pre></div></div> |
| <p>Since metrics are aggregations, the resulting SQL expression will be grouped by all non-metric columns. You can specify a subset of columns to group by instead:</p> |
| <div class="language-sql codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-sql codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token keyword" style="color:rgb(86, 156, 214)">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:rgb(212, 212, 212)">*</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">FROM</span><span class="token plain"> {{ dataset</span><span class="token punctuation" style="color:rgb(212, 212, 212)">(</span><span class="token number" style="color:rgb(181, 206, 168)">42</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> include_metrics</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token boolean">True</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token keyword" style="color:rgb(86, 156, 214)">columns</span><span class="token operator" style="color:rgb(212, 212, 212)">=</span><span class="token punctuation" style="color:rgb(212, 212, 212)">[</span><span class="token string" style="color:rgb(206, 145, 120)">"ds"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">,</span><span class="token plain"> </span><span class="token string" style="color:rgb(206, 145, 120)">"category"</span><span class="token punctuation" style="color:rgb(212, 212, 212)">]</span><span class="token punctuation" style="color:rgb(212, 212, 212)">)</span><span class="token plain"> }} </span><span class="token keyword" style="color:rgb(86, 156, 214)">LIMIT</span><span class="token plain"> </span><span class="token number" style="color:rgb(181, 206, 168)">10</span><br></span></code></pre></div></div> |
| <p><strong>Metrics</strong></p> |
| <p>The <code>{{ metric('metric_key', dataset_id) }}</code> macro can be used to retrieve the metric SQL syntax from a dataset. This can be useful for different purposes:</p> |
| <ul> |
| <li>Override the metric label in the chart level</li> |
| <li>Combine multiple metrics in a calculation</li> |
| <li>Retrieve a metric syntax in SQL lab</li> |
| <li>Re-use metrics across datasets</li> |
| </ul> |
| <p>This macro avoids copy/paste, allowing users to centralize the metric definition in the dataset layer.</p> |
| <p>The <code>dataset_id</code> parameter is optional, and if not provided Superset will use the current dataset from context (for example, when using this macro in the Chart Builder, by default the <code>macro_key</code> will be searched in the dataset powering the chart). |
| The parameter can be used in SQL Lab, or when fetching a metric from another dataset.</p> |
| <h2 class="anchor anchorWithStickyNavbar_LWe7" id="available-filters">Available Filters<a href="#available-filters" class="hash-link" aria-label="Direct link to Available Filters" title="Direct link to Available Filters"></a></h2> |
| <p>Superset supports <a href="https://jinja.palletsprojects.com/en/stable/templates/#builtin-filters" target="_blank" rel="noopener noreferrer">builtin filters from the Jinja2 templating package</a>. Custom filters have also been implemented:</p> |
| <p><strong>Where In</strong> |
| Parses a list into a SQL-compatible statement. This is useful with macros that return an array (for example the <code>filter_values</code> macro):</p> |
| <div class="language-text codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">Dashboard filter with "First", "Second" and "Third" options selected</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{{ filter_values('column') }} => ["First", "Second", "Third"]</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{{ filter_values('column')|where_in }} => ('First', 'Second', 'Third')</span><br></span></code></pre></div></div> |
| <p>By default, this filter returns <code>()</code> (as a string) in case the value is null. The <code>default_to_none</code> parameter can be se to <code>True</code> to return null in this case:</p> |
| <div class="language-text codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">Dashboard filter without any value applied</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{{ filter_values('column') }} => ()</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{{ filter_values('column')|where_in(default_to_none=True) }} => None</span><br></span></code></pre></div></div> |
| <p><strong>To Datetime</strong></p> |
| <p>Loads a string as a <code>datetime</code> object. This is useful when performing date operations. For example:</p> |
| <div class="language-text codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#9CDCFE;--prism-background-color:#1E1E1E"><div class="codeBlockContent_QJqH"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar" style="color:#9CDCFE;background-color:#1E1E1E"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set from_expr = get_time_filter("dttm", strftime="%Y-%m-%d").from_expr %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% set to_expr = get_time_filter("dttm", strftime="%Y-%m-%d").to_expr %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% if (to_expr|to_datetime(format="%Y-%m-%d") - from_expr|to_datetime(format="%Y-%m-%d")).days > 100 %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> do something</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% else %}</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain"> do something else</span><br></span><span class="token-line" style="color:#9CDCFE"><span class="token plain">{% endif %}</span><br></span></code></pre></div></div></div><footer class="theme-doc-footer docusaurus-mt-lg"><div class="row margin-top--sm theme-doc-footer-edit-meta-row"><div class="col"><a href="https://github.com/apache/superset/edit/master/docs/versioned_docs/version-6.0.0/configuration/sql-templating.mdx" target="_blank" rel="noopener noreferrer" class="theme-edit-this-page"><svg fill="currentColor" height="20" width="20" viewBox="0 0 40 40" class="iconEdit_Z9Sw" aria-hidden="true"><g><path d="m34.5 11.7l-3 3.1-6.3-6.3 3.1-3q0.5-0.5 1.2-0.5t1.1 0.5l3.9 3.9q0.5 0.4 0.5 1.1t-0.5 1.2z m-29.5 17.1l18.4-18.5 6.3 6.3-18.4 18.4h-6.3v-6.2z"></path></g></svg>Edit this page</a></div><div class="col lastUpdated_JAkA"><span class="theme-last-updated">Last updated<!-- --> on <b><time datetime="2025-09-02T13:38:03.000Z" itemprop="dateModified">Sep 2, 2025</time></b> by <b>dependabot[bot]</b></span></div></div></footer></article><nav class="docusaurus-mt-lg pagination-nav" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/6.0.0/configuration/async-queries-celery"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Async Queries via Celery</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/6.0.0/configuration/timezones"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Timezones</div></a></nav></div></div><div class="col col--3"><div class="tableOfContents_bqdL thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#jinja-templates" class="table-of-contents__link toc-highlight">Jinja Templates</a></li><li><a href="#available-macros" class="table-of-contents__link toc-highlight">Available Macros</a></li><li><a href="#available-filters" class="table-of-contents__link toc-highlight">Available Filters</a></li></ul></div></div></div></div></main></div></div></div><footer class="theme-layout-footer footer"><div class="container container-fluid"><div class="footer__bottom text--center"><div class="footer__copyright"> |
| <div class="footer__applitools"> |
| We use <a href="https://applitools.com/" target="_blank" rel="nofollow"><img src="/img/applitools.png" title="Applitools"></a> |
| </div> |
| <p>Copyright © 2024, |
| The <a href="https://www.apache.org/" target="_blank" rel="noreferrer">Apache Software Foundation</a>, |
| Licensed under the Apache <a href="https://apache.org/licenses/LICENSE-2.0" target="_blank" rel="noreferrer">License</a>.</p> |
| <p><small>Apache Superset, Apache, Superset, the Superset logo, and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation. All other products or name brands are trademarks of their respective holders, including The Apache Software Foundation. |
| <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a> resources</small></p> |
| <img class="footer__divider" src="/img/community/line.png" alt="Divider"> |
| <p> |
| <small> |
| <a href="/docs/security/" target="_blank" rel="noreferrer">Security</a> | |
| <a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noreferrer">Donate</a> | |
| <a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noreferrer">Thanks</a> | |
| <a href="https://apache.org/events/current-event" target="_blank" rel="noreferrer">Events</a> | |
| <a href="https://apache.org/licenses/" target="_blank" rel="noreferrer">License</a> | |
| <a href="https://privacy.apache.org/policies/privacy-policy-public.html" target="_blank" rel="noreferrer">Privacy</a> |
| </small> |
| </p> |
| <!-- telemetry/analytics pixel: --> |
| <img referrerpolicy="no-referrer-when-downgrade" src="https://static.scarf.sh/a.png?x-pxid=39ae6855-95fc-4566-86e5-360d542b0a68"> |
| </div></div></div></footer></div> |
| </body> |
| </html> |
