| <!doctype html> |
| <html class="docs-version-current" lang="en" dir="ltr"> |
| <head> |
| <meta charset="UTF-8"> |
| <meta name="viewport" content="width=device-width,initial-scale=1"> |
| <meta name="generator" content="Docusaurus v2.0.0-beta.17"> |
| <link rel="preconnect" href="https://www.google-analytics.com"> |
| <script>window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments)},ga.l=+new Date,ga("create","G-133LHD3B3N","auto"),ga("set","anonymizeIp",!0),ga("send","pageview")</script> |
| <script async src="https://www.google-analytics.com/analytics.js"></script> |
| <link rel="preconnect" href="https://www.google-analytics.com"> |
| <link rel="preconnect" href="https://www.googletagmanager.com"> |
| <script async src="https://www.googletagmanager.com/gtag/js?id=G-133LHD3B3N"></script> |
| <script>function gtag(){dataLayer.push(arguments)}window.dataLayer=window.dataLayer||[],gtag("js",new Date),gtag("config","G-133LHD3B3N",{anonymize_ip:!0})</script> |
| <link rel="search" type="application/opensearchdescription+xml" title="Superset" href="/opensearch.xml"> |
| <script src="/script/matomo.js"></script><title data-rh="true">SQL Templating | Superset</title><meta data-rh="true" name="twitter:card" content="summary_large_image"><meta data-rh="true" property="og:url" content="https://superset.apache.org/docs/installation/sql-templating"><meta data-rh="true" name="docusaurus_locale" content="en"><meta data-rh="true" name="docusaurus_version" content="current"><meta data-rh="true" name="docusaurus_tag" content="docs-default-current"><meta data-rh="true" name="docsearch:language" content="en"><meta data-rh="true" name="docsearch:version" content="current"><meta data-rh="true" name="docsearch:docusaurus_tag" content="docs-default-current"><meta data-rh="true" property="og:title" content="SQL Templating | Superset"><meta data-rh="true" name="description" content="SQL Templating"><meta data-rh="true" property="og:description" content="SQL Templating"><link data-rh="true" rel="icon" href="/img/favicon.ico"><link data-rh="true" rel="canonical" href="https://superset.apache.org/docs/installation/sql-templating"><link data-rh="true" rel="alternate" href="https://superset.apache.org/docs/installation/sql-templating" hreflang="en"><link data-rh="true" rel="alternate" href="https://superset.apache.org/docs/installation/sql-templating" hreflang="x-default"><link data-rh="true" rel="preconnect" href="https://WR5FASX5ED-dsn.algolia.net" crossorigin="anonymous"><link rel="stylesheet" href="/assets/css/styles.ba1779ef.css"> |
| <link rel="preload" href="/assets/js/runtime~main.9b07dba6.js" as="script"> |
| <link rel="preload" href="/assets/js/main.f67bdcb9.js" as="script"> |
| </head> |
| <body class="navigation-with-keyboard"> |
| <script>!function(){function t(t){document.documentElement.setAttribute("data-theme",t)}var e=function(){var t=null;try{t=localStorage.getItem("theme")}catch(t){}return t}();t(null!==e?e:"light")}()</script><div id="__docusaurus"> |
| <div role="region"><a href="#" class="skipToContent_ZgBM">Skip to main content</a></div><nav class="navbar navbar--fixed-top"><div class="navbar__inner"><div class="navbar__items"><button aria-label="Navigation bar toggle" class="navbar__toggle clean-btn" type="button" tabindex="0"><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="themedImage_W2Cr themedImage--light_TfLj"><img src="/img/superset-logo-horiz-dark.svg" alt="Superset Logo" class="themedImage_W2Cr themedImage--dark_oUvU"></div></a><div class="navbar__item dropdown dropdown--hoverable"><a href="#" class="navbar__link">Documentation</a><ul class="dropdown__menu"><li><a class="dropdown__link" href="/docs/intro">Getting Started</a></li><li><a class="dropdown__link" href="/docs/intro">Tutorial</a></li><li><a class="dropdown__link" href="/docs/frequently-asked-questions">FAQ</a></li></ul></div><div class="navbar__item dropdown dropdown--hoverable"><a href="#" class="navbar__link">Community</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"><span>GitHub<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_I5OW"><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"></path></svg></span></a></li><li><a href="https://join.slack.com/t/apache-superset/shared_invite/zt-16jvzmoi8-sI7jKWp~xc2zYRe~NqiY9Q" target="_blank" rel="noopener noreferrer" class="dropdown__link"><span>Slack<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_I5OW"><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"></path></svg></span></a></li><li><a href="https://lists.apache.org/list.html?dev@superset.apache.org" target="_blank" rel="noopener noreferrer" class="dropdown__link"><span>Mailing List<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_I5OW"><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"></path></svg></span></a></li><li><a href="https://stackoverflow.com/questions/tagged/superset+apache-superset" target="_blank" rel="noopener noreferrer" class="dropdown__link"><span>Stack Overflow<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_I5OW"><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"></path></svg></span></a></li></ul></div></div><div class="navbar__items navbar__items--right"><a href="https://github.com/apache/superset" target="_blank" rel="noopener noreferrer" class="navbar__item navbar__link" class="github-logo-container"></a><div class="searchBox_qEbK"><button type="button" class="DocSearch DocSearch-Button" aria-label="Search"><span class="DocSearch-Button-Container"><svg width="20" height="20" class="DocSearch-Search-Icon" viewBox="0 0 20 20"><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 class="main-wrapper docs-wrapper docs-doc-page"><div class="docPage_P2Lg"><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_RiI4" type="button"></button><aside class="theme-doc-sidebar-container docSidebarContainer_rKC_"><div class="sidebar_CW9Y"><nav class="menu thin-scrollbar menu_SkdO"><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/intro">Introduction</a></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--active" href="/docs/installation/installing-superset-using-docker-compose">Installation and Configuration</a></div><ul style="display:block;overflow:visible;height:auto" 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/installation/installing-superset-using-docker-compose">Installing Locally Using Docker Compose</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/installation/installing-superset-from-scratch">Installing From Scratch</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/installation/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/installation/networking-settings">Additional Networking 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/installation/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/installation/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/installation/upgrading-superset">Upgrading 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/installation/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" tabindex="0" href="/docs/installation/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 menu__link--active" aria-current="page" tabindex="0" href="/docs/installation/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/installation/running-on-kubernetes">Running on Kubernetes</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" href="/docs/databases/installing-database-drivers">Connecting to Databases</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" href="/docs/creating-charts-dashboards/creating-your-first-dashboard">Creating Charts and Dashboards</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" href="/docs/miscellaneous/country-map-tools">Miscellaneous</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" href="/docs/contributing/contributing-page">Contributing</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/frequently-asked-questions">Frequently Asked Questions</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/api">API</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/security">Security</a></li></ul></nav></div></aside><main class="docMainContainer_TCnq"><div class="container padding-top--md padding-bottom--lg"><div class="row"><div class="col docItemCol_DM6M"><div class="docItemContainer_vinB"><article><nav class="theme-doc-breadcrumbs breadcrumbsContainer_Xlws" aria-label="breadcrumbs"><ul class="breadcrumbs"><li class="breadcrumbs__item"><a class="breadcrumbs__link breadcrumbsItemLink_e5ie" href="/">🏠</a></li><li class="breadcrumbs__item"><span class="breadcrumbs__link breadcrumbsItemLink_e5ie">Installation and Configuration</span></li><li class="breadcrumbs__item breadcrumbs__item--active"><a class="breadcrumbs__link breadcrumbsItemLink_e5ie" href="/docs/installation/sql-templating">SQL Templating</a></li></ul></nav><div class="tocCollapsible_jdIR theme-doc-toc-mobile tocMobile_TmEX"><button type="button" class="clean-btn tocCollapsibleButton_Fzxq">On this page</button></div><div class="theme-doc-markdown markdown"><h2 class="anchor anchorWithStickyNavbar_mojV" id="sql-templating">SQL Templating<a class="hash-link" href="#sql-templating" title="Direct link to heading"></a></h2><h3 class="anchor anchorWithStickyNavbar_mojV" id="jinja-templates">Jinja Templates<a class="hash-link" href="#jinja-templates" title="Direct link to heading"></a></h3><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="https://superset.apache.org/docs/installation/configuring-superset#feature-flags" target="_blank" rel="noopener noreferrer">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)</li><li><code>to_dttm</code>: end <code>datetime</code> value from the selected time range (<code>None</code> if undefined)</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="codeBlockContainer_I0IT language-sql theme-code-block"><div class="codeBlockContent_wNvx sql"><pre tabindex="0" class="prism-code language-sql codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token keyword" style="color:#00009f">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">*</span><span class="token plain"> </span><span class="token keyword" style="color:#00009f">from</span><span class="token plain"> tbl </span><span class="token keyword" style="color:#00009f">where</span><span class="token plain"> dttm_col </span><span class="token operator" style="color:#393A34">></span><span class="token plain"> </span><span class="token string" style="color:#e3116c">'{{ from_dttm }}'</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">and</span><span class="token plain"> dttm_col </span><span class="token operator" style="color:#393A34"><</span><span class="token plain"> </span><span class="token string" style="color:#e3116c">'{{ to_dttm }}'</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT language-sql theme-code-block"><div class="codeBlockContent_wNvx sql"><pre tabindex="0" class="prism-code language-sql codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token keyword" style="color:#00009f">SELECT</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">*</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token keyword" style="color:#00009f">FROM</span><span class="token plain"> tbl</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token keyword" style="color:#00009f">WHERE</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {</span><span class="token operator" style="color:#393A34">%</span><span class="token plain"> </span><span class="token keyword" style="color:#00009f">if</span><span class="token plain"> from_dttm </span><span class="token operator" style="color:#393A34">is</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">not</span><span class="token plain"> none </span><span class="token operator" style="color:#393A34">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> dttm_col </span><span class="token operator" style="color:#393A34">></span><span class="token plain"> </span><span class="token string" style="color:#e3116c">'{{ from_dttm }}'</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">AND</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {</span><span class="token operator" style="color:#393A34">%</span><span class="token plain"> endif </span><span class="token operator" style="color:#393A34">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {</span><span class="token operator" style="color:#393A34">%</span><span class="token plain"> </span><span class="token keyword" style="color:#00009f">if</span><span class="token plain"> to_dttm </span><span class="token operator" style="color:#393A34">is</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">not</span><span class="token plain"> none </span><span class="token operator" style="color:#393A34">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> dttm_col </span><span class="token operator" style="color:#393A34"><</span><span class="token plain"> </span><span class="token string" style="color:#e3116c">'{{ to_dttm }}'</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">AND</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {</span><span class="token operator" style="color:#393A34">%</span><span class="token plain"> endif </span><span class="token operator" style="color:#393A34">%</span><span class="token plain">}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token boolean" style="color:#36acaa">true</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token punctuation" style="color:#393A34">)</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></div></div><p>Note how the Jinja parameters are called within double brackets in the query, and without 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="codeBlockContainer_I0IT language-python theme-code-block"><div class="codeBlockContent_wNvx python"><pre tabindex="0" class="prism-code language-python codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">JINJA_CONTEXT_ADDONS </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token string" style="color:#e3116c">'my_crazy_macro'</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> </span><span class="token keyword" style="color:#00009f">lambda</span><span class="token plain"> x</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> x</span><span class="token operator" style="color:#393A34">*</span><span class="token number" style="color:#36acaa">2</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token punctuation" style="color:#393A34">}</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></div></div><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="codeBlockContainer_I0IT language-python theme-code-block"><div class="codeBlockContent_wNvx python"><pre tabindex="0" class="prism-code language-python codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token keyword" style="color:#00009f">def</span><span class="token plain"> </span><span class="token function" style="color:#d73a49">DATE</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> ts</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> datetime</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> day_offset</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> SupportsInt </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token number" style="color:#36acaa">0</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> hour_offset</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> SupportsInt </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token number" style="color:#36acaa">0</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">-</span><span class="token operator" style="color:#393A34">></span><span class="token plain"> </span><span class="token builtin">str</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:#e3116c">"""Current day as a string."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> day_offset</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> hour_offset </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token builtin">int</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">day_offset</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> </span><span class="token builtin">int</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">hour_offset</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> offset_day </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">ts </span><span class="token operator" style="color:#393A34">+</span><span class="token plain"> timedelta</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">days</span><span class="token operator" style="color:#393A34">=</span><span class="token plain">day_offset</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> hours</span><span class="token operator" style="color:#393A34">=</span><span class="token plain">hour_offset</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">date</span><span class="token punctuation" style="color:#393A34">(</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token keyword" style="color:#00009f">return</span><span class="token plain"> </span><span class="token builtin">str</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">offset_day</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token keyword" style="color:#00009f">class</span><span class="token plain"> </span><span class="token class-name">CustomPrestoTemplateProcessor</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">PrestoTemplateProcessor</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:#e3116c">"""A custom presto template processor."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> engine </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token string" style="color:#e3116c">"presto"</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token keyword" style="color:#00009f">def</span><span class="token plain"> </span><span class="token function" style="color:#d73a49">process_template</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">self</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> sql</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> </span><span class="token builtin">str</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">**</span><span class="token plain">kwargs</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">-</span><span class="token operator" style="color:#393A34">></span><span class="token plain"> </span><span class="token builtin">str</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:#e3116c">"""Processes a sql template with $ style macro using regex."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token comment" style="color:#999988;font-style:italic"># Add custom macros functions.</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> macros </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token string" style="color:#e3116c">"DATE"</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> partial</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">DATE</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> datetime</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">utcnow</span><span class="token punctuation" style="color:#393A34">(</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">}</span><span class="token plain"> </span><span class="token comment" style="color:#999988;font-style:italic"># type: Dict[str, Any]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token comment" style="color:#999988;font-style:italic"># Update with macros defined in context and kwargs.</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> macros</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">update</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">self</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">context</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> macros</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">update</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">kwargs</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token keyword" style="color:#00009f">def</span><span class="token plain"> </span><span class="token function" style="color:#d73a49">replacer</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">match</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token triple-quoted-string string" style="color:#e3116c">"""Expand $ style macros with corresponding function calls."""</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> macro_name</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> args_str </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> match</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">groups</span><span class="token punctuation" style="color:#393A34">(</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> args </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">[</span><span class="token plain">a</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">strip</span><span class="token punctuation" style="color:#393A34">(</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"> </span><span class="token keyword" style="color:#00009f">for</span><span class="token plain"> a </span><span class="token keyword" style="color:#00009f">in</span><span class="token plain"> args_str</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">split</span><span class="token punctuation" style="color:#393A34">(</span><span class="token string" style="color:#e3116c">","</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token keyword" style="color:#00009f">if</span><span class="token plain"> args </span><span class="token operator" style="color:#393A34">==</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">[</span><span class="token string" style="color:#e3116c">""</span><span class="token punctuation" style="color:#393A34">]</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> args </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">[</span><span class="token punctuation" style="color:#393A34">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> f </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> macros</span><span class="token punctuation" style="color:#393A34">[</span><span class="token plain">macro_name</span><span class="token punctuation" style="color:#393A34">[</span><span class="token number" style="color:#36acaa">1</span><span class="token punctuation" style="color:#393A34">:</span><span class="token punctuation" style="color:#393A34">]</span><span class="token punctuation" style="color:#393A34">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token keyword" style="color:#00009f">return</span><span class="token plain"> f</span><span class="token punctuation" style="color:#393A34">(</span><span class="token operator" style="color:#393A34">*</span><span class="token plain">args</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> macro_names </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">[</span><span class="token string" style="color:#e3116c">"$"</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">+</span><span class="token plain"> name </span><span class="token keyword" style="color:#00009f">for</span><span class="token plain"> name </span><span class="token keyword" style="color:#00009f">in</span><span class="token plain"> macros</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">keys</span><span class="token punctuation" style="color:#393A34">(</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">]</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> pattern </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token string" style="color:#e3116c">r"(%s)\s*\(([^()]*)\)"</span><span class="token plain"> </span><span class="token operator" style="color:#393A34">%</span><span class="token plain"> </span><span class="token string" style="color:#e3116c">"|"</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">join</span><span class="token punctuation" style="color:#393A34">(</span><span class="token builtin">map</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">re</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">escape</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> macro_names</span><span class="token punctuation" style="color:#393A34">)</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token keyword" style="color:#00009f">return</span><span class="token plain"> re</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">sub</span><span class="token punctuation" style="color:#393A34">(</span><span class="token plain">pattern</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> replacer</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"> sql</span><span class="token punctuation" style="color:#393A34">)</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">CUSTOM_TEMPLATE_PROCESSORS </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> CustomPrestoTemplateProcessor</span><span class="token punctuation" style="color:#393A34">.</span><span class="token plain">engine</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> CustomPrestoTemplateProcessor</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token punctuation" style="color:#393A34">}</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT language-python theme-code-block"><div class="codeBlockContent_wNvx python"><pre tabindex="0" class="prism-code language-python codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">FEATURE_FLAGS </span><span class="token operator" style="color:#393A34">=</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token string" style="color:#e3116c">'SQL_VALIDATORS_BY_ENGINE'</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">{</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token string" style="color:#e3116c">'presto'</span><span class="token punctuation" style="color:#393A34">:</span><span class="token plain"> </span><span class="token string" style="color:#e3116c">'PrestoDBSQLValidator'</span><span class="token punctuation" style="color:#393A34">,</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> </span><span class="token punctuation" style="color:#393A34">}</span><span class="token plain"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"></span><span class="token punctuation" style="color:#393A34">}</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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><h3 class="anchor anchorWithStickyNavbar_mojV" id="available-macros">Available Macros<a class="hash-link" href="#available-macros" title="Direct link to heading"></a></h3><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 username 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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">{{ current_username(add_to_cache_keys=False) }}</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></div></div><p><strong>Current User ID</strong></p><p>The <code>{{ current_user_id() }}</code> macro returns the user_id of the currently logged in user.</p><p>If you have caching enabled in your Superset configuration, then by default the <code>user_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 <code>user_id</code> value in the calculation of the |
| cache key by adding the following parameter to your Jinja code:</p><div class="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">{{ current_user_id(add_to_cache_keys=False) }}</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></div></div><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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT count(*)</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">FROM ORDERS</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">WHERE country_code = '{{ url_param('countrycode') }}'</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT count(*)</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">FROM ORDERS</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">WHERE country_code = 'ES'</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></div></div></li><li><p>For your coworker in the USA, the SQL Lab query will be rendered as:</p><div class="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT count(*)</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">FROM ORDERS</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">WHERE country_code = 'US'</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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 for filter inside the main query for performance purposes</li></ul><p>Here's a concrete example:</p><div class="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT action, count(*) as times</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">FROM logs</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">WHERE</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> action in {{ filter_values('action_type')|where_in }}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain">GROUP BY action</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain"> WITH RECURSIVE</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> superiors(employee_id, manager_id, full_name, level, lineage) AS (</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> SELECT</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> employee_id,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> manager_id,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> full_name,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> 1 as level,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> employee_id as lineage</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> FROM</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> employees</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> WHERE</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> 1=1</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {# Render a blank line #}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {%- for filter in get_filters('full_name', remove_filter=True) -%}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {%- if filter.get('op') == 'IN' -%}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> AND</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> full_name IN {{ filter.get('val')|where_in }}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {%- endif -%}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {%- if filter.get('op') == 'LIKE' -%}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> AND</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> full_name LIKE {{ "'" + filter.get('val') + "'" }}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {%- endif -%}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> {%- endfor -%}</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> UNION ALL</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> SELECT</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> e.employee_id,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> e.manager_id,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> e.full_name,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> s.level + 1 as level,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> s.lineage</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> FROM</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> employees e,</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> superiors s</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> WHERE s.manager_id = e.employee_id</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> )</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> SELECT</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> employee_id, manager_id, full_name, level, lineage</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> FROM</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> superiors</span><br></span><span class="token-line" style="color:#393A34"><span class="token plain"> order by lineage, level</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT * FROM {{ dataset(42) }} LIMIT 10</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT * FROM {{ dataset(42, include_metrics=True) }} LIMIT 10</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></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="codeBlockContainer_I0IT theme-code-block"><div class="codeBlockContent_wNvx"><pre tabindex="0" class="prism-code language-text codeBlock_jd64 thin-scrollbar" style="color:#393A34;background-color:#f6f8fa"><code class="codeBlockLines_mRuA"><span class="token-line" style="color:#393A34"><span class="token plain">SELECT * FROM {{ dataset(42, include_metrics=True, columns=["ds", "category"]) }} LIMIT 10</span><br></span></code></pre><button type="button" aria-label="Copy code to clipboard" class="copyButton_wuS7 clean-btn">Copy</button></div></div></div><footer class="theme-doc-footer docusaurus-mt-lg"><div class="theme-doc-footer-edit-meta-row row"><div class="col"><a href="https://github.com/apache/superset/tree/master/docs/docs/installation/sql-templating.mdx" target="_blank" rel="noreferrer noopener" class="theme-edit-this-page"><svg fill="currentColor" height="20" width="20" viewBox="0 0 40 40" class="iconEdit_dcUD" 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_foO9"></div></div></footer></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages navigation"><div class="pagination-nav__item"><a class="pagination-nav__link" href="/docs/installation/alerts-reports"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Alerts and Reports</div></a></div><div class="pagination-nav__item pagination-nav__item--next"><a class="pagination-nav__link" href="/docs/installation/running-on-kubernetes"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Running on Kubernetes</div></a></div></nav></div></div><div class="col col--3"><div class="tableOfContents_cNA8 thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#sql-templating" class="table-of-contents__link toc-highlight">SQL Templating</a><ul><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></ul></li></ul></div></div></div></div></main></div></div><footer class="footer footer--dark"><div class="container container-fluid"><div class="footer__bottom text--center"><div class="footer__copyright">Copyright © 2022, |
| 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>. <br> |
| <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><br> |
| <small> |
| <a href="https://www.apache.org/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> |
| </small></div></div></div></footer></div> |
| <script src="/assets/js/runtime~main.9b07dba6.js"></script> |
| <script src="/assets/js/main.f67bdcb9.js"></script> |
| </body> |
| </html> |
