Google Git
Sign in
apache / superset-site / 9abab55c81fe90ab23b3a4a89f8e9fcc858e5573 / . / sqllab.html
blob: b871aa1e29df9fb26dd6d6aeb54ab21f68976f7c [file] [log] [blame]
<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>SQL Lab &mdash; Apache Superset documentation</title>
<script type="text/javascript" src="_static/js/modernizr.min.js"></script>
<script type="text/javascript" id="documentation_options" data-url_root="./" src="_static/documentation_options.js"></script>
<script src="_static/jquery.js"></script>
<script src="_static/underscore.js"></script>
<script src="_static/doctools.js"></script>
<script src="_static/language_data.js"></script>
<script type="text/javascript" src="_static/js/theme.js"></script>
<link rel="stylesheet" href="_static/css/theme.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="next" title="Visualizations Gallery" href="gallery.html" />
<link rel="prev" title="Security" href="security.html" />
</head>
<body class="wy-body-for-nav">
<div class="wy-grid-for-nav">
<nav data-toggle="wy-nav-shift" class="wy-nav-side">
<div class="wy-side-scroll">
<div class="wy-side-nav-search" >
<a href="index.html" class="icon icon-home"> Apache Superset
</a>
<div role="search">
<form id="rtd-search-form" class="wy-form" action="search.html" method="get">
<input type="text" name="q" placeholder="Search docs" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul class="current">
<li class="toctree-l1"><a class="reference internal" href="installation.html">Installation &amp; Configuration</a></li>
<li class="toctree-l1"><a class="reference internal" href="tutorials.html">Tutorials</a></li>
<li class="toctree-l1"><a class="reference internal" href="security.html">Security</a></li>
<li class="toctree-l1 current"><a class="current reference internal" href="#">SQL Lab</a><ul>
<li class="toctree-l2"><a class="reference internal" href="#feature-overview">Feature Overview</a></li>
<li class="toctree-l2"><a class="reference internal" href="#extra-features">Extra features</a></li>
<li class="toctree-l2"><a class="reference internal" href="#templating-with-jinja">Templating with Jinja</a><ul>
<li class="toctree-l3"><a class="reference internal" href="#available-macros">Available macros</a></li>
<li class="toctree-l3"><a class="reference internal" href="#extending-macros">Extending macros</a></li>
<li class="toctree-l3"><a class="reference internal" href="#customize-templating">Customize templating</a></li>
<li class="toctree-l3"><a class="reference internal" href="#query-cost-estimation">Query cost estimation</a></li>
<li class="toctree-l3"><a class="reference internal" href="#create-table-as-ctas">Create Table As (CTAS)</a></li>
</ul>
</li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="gallery.html">Visualizations Gallery</a></li>
<li class="toctree-l1"><a class="reference internal" href="druid.html">Druid</a></li>
<li class="toctree-l1"><a class="reference internal" href="misc.html">Misc</a></li>
<li class="toctree-l1"><a class="reference internal" href="issue_code_reference.html">Issue Code Reference</a></li>
<li class="toctree-l1"><a class="reference internal" href="faq.html">FAQ</a></li>
</ul>
</div>
</div>
</nav>
<section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">
<nav class="wy-nav-top" aria-label="top navigation">
<i data-toggle="wy-nav-top" class="fa fa-bars"></i>
<a href="index.html">Apache Superset</a>
</nav>
<div class="wy-nav-content">
<div class="rst-content">
<div role="navigation" aria-label="breadcrumbs navigation">
<ul class="wy-breadcrumbs">
<li><a href="index.html">Docs</a> &raquo;</li>
<li>SQL Lab</li>
<li class="wy-breadcrumbs-aside">
</li>
</ul>
<hr/>
</div>
<div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article">
<div itemprop="articleBody">
<div class="section" id="sql-lab">
<h1>SQL Lab<a class="headerlink" href="#sql-lab" title="Permalink to this headline"></a></h1>
<p>SQL Lab is a modern, feature-rich SQL IDE written in
<a class="reference external" href="https://facebook.github.io/react/">React</a>.</p>
<hr class="docutils" />
<img alt="_images/sqllab.png" src="_images/sqllab.png" />
<hr class="docutils" />
<div class="section" id="feature-overview">
<h2>Feature Overview<a class="headerlink" href="#feature-overview" title="Permalink to this headline"></a></h2>
<ul class="simple">
<li><p>Connects to just about any database backend</p></li>
<li><p>A multi-tab environment to work on multiple queries at a time</p></li>
<li><p>A smooth flow to visualize your query results using Superset’s rich
visualization capabilities</p></li>
<li><p>Browse database metadata: tables, columns, indexes, partitions</p></li>
<li><p>Support for long-running queries</p>
<ul>
<li><p>uses the <a class="reference external" href="http://www.celeryproject.org/">Celery distributed queue</a>
to dispatch query handling to workers</p></li>
<li><p>supports defining a “results backend” to persist query results</p></li>
</ul>
</li>
<li><p>A search engine to find queries executed in the past</p></li>
<li><p>Supports templating using the
<a class="reference external" href="http://jinja.pocoo.org/docs/dev/">Jinja templating language</a>
which allows for using macros in your SQL code</p></li>
</ul>
</div>
<div class="section" id="extra-features">
<h2>Extra features<a class="headerlink" href="#extra-features" title="Permalink to this headline"></a></h2>
<ul class="simple">
<li><p>Hit <code class="docutils literal notranslate"><span class="pre">alt</span> <span class="pre">+</span> <span class="pre">enter</span></code> as a keyboard shortcut to run your query</p></li>
</ul>
</div>
<div class="section" id="templating-with-jinja">
<h2>Templating with Jinja<a class="headerlink" href="#templating-with-jinja" title="Permalink to this headline"></a></h2>
<div class="highlight-sql notranslate"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">some_table</span>
<span class="k">WHERE</span> <span class="n">partition_key</span> <span class="o">=</span> <span class="s1">&#39;{{ presto.first_latest_partition(&#39;</span><span class="n">some_table</span><span class="s1">&#39;) }}&#39;</span>
</pre></div>
</div>
<p>Templating unleashes the power and capabilities of a
programming language within your SQL code.</p>
<p>Templates can also be used to write generic queries that are
parameterized so they can be re-used easily.</p>
<div class="section" id="available-macros">
<h3>Available macros<a class="headerlink" href="#available-macros" title="Permalink to this headline"></a></h3>
<p>We expose certain modules from Python’s standard library in
Superset’s Jinja context:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">time</span></code>: <code class="docutils literal notranslate"><span class="pre">time</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">datetime</span></code>: <code class="docutils literal notranslate"><span class="pre">datetime.datetime</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">uuid</span></code>: <code class="docutils literal notranslate"><span class="pre">uuid</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">random</span></code>: <code class="docutils literal notranslate"><span class="pre">random</span></code></p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">relativedelta</span></code>: <code class="docutils literal notranslate"><span class="pre">dateutil.relativedelta.relativedelta</span></code></p></li>
</ul>
<p><a class="reference external" href="http://jinja.pocoo.org/docs/dev/templates/">Jinja’s builtin filters</a> can be also be applied where needed.</p>
<dl class="py class">
<dt id="superset.jinja_context.ExtraCache">
<em class="property">class </em><code class="sig-prename descclassname">superset.jinja_context.</code><code class="sig-name descname">ExtraCache</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">extra_cache_keys</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>List<span class="p">[</span>Any<span class="p">]</span><span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em><span class="sig-paren">)</span><a class="reference internal" href="_modules/superset/jinja_context.html#ExtraCache"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.ExtraCache" title="Permalink to this definition"></a></dt>
<dd><p>Dummy class that exposes a method used to store additional values used in
calculation of query object cache keys.</p>
<dl class="py method">
<dt id="superset.jinja_context.ExtraCache.cache_key_wrapper">
<code class="sig-name descname">cache_key_wrapper</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">key</span><span class="p">:</span> <span class="n">Any</span></em><span class="sig-paren">)</span> &#x2192; Any<a class="reference internal" href="_modules/superset/jinja_context.html#ExtraCache.cache_key_wrapper"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.ExtraCache.cache_key_wrapper" title="Permalink to this definition"></a></dt>
<dd><p>Adds values to a list that is added to the query object used for calculating a
cache key.</p>
<dl class="simple">
<dt>This is needed if the following applies:</dt><dd><ul class="simple">
<li><p>Caching is enabled</p></li>
<li><p>The query is dynamically generated using a jinja template</p></li>
<li><p>A <cite>JINJA_CONTEXT_ADDONS</cite> or similar is used as a filter in the query</p></li>
</ul>
</dd>
</dl>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><strong>key</strong> – Any value that should be considered when calculating the cache key</p>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>the original value <code class="docutils literal notranslate"><span class="pre">key</span></code> passed to the function</p>
</dd>
</dl>
</dd></dl>
<dl class="py method">
<dt id="superset.jinja_context.ExtraCache.current_user_id">
<code class="sig-name descname">current_user_id</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">add_to_cache_keys</span><span class="p">:</span> <span class="n">bool</span> <span class="o">=</span> <span class="default_value">True</span></em><span class="sig-paren">)</span> &#x2192; Optional<span class="p">[</span>int<span class="p">]</span><a class="reference internal" href="_modules/superset/jinja_context.html#ExtraCache.current_user_id"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.ExtraCache.current_user_id" title="Permalink to this definition"></a></dt>
<dd><p>Return the user ID of the user who is currently logged in.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><strong>add_to_cache_keys</strong> – Whether the value should be included in the cache key</p>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>The user ID</p>
</dd>
</dl>
</dd></dl>
<dl class="py method">
<dt id="superset.jinja_context.ExtraCache.current_username">
<code class="sig-name descname">current_username</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">add_to_cache_keys</span><span class="p">:</span> <span class="n">bool</span> <span class="o">=</span> <span class="default_value">True</span></em><span class="sig-paren">)</span> &#x2192; Optional<span class="p">[</span>str<span class="p">]</span><a class="reference internal" href="_modules/superset/jinja_context.html#ExtraCache.current_username"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.ExtraCache.current_username" title="Permalink to this definition"></a></dt>
<dd><p>Return the username of the user who is currently logged in.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><strong>add_to_cache_keys</strong> – Whether the value should be included in the cache key</p>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>The username</p>
</dd>
</dl>
</dd></dl>
<dl class="py method">
<dt id="superset.jinja_context.ExtraCache.url_param">
<code class="sig-name descname">url_param</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">param</span><span class="p">:</span> <span class="n">str</span></em>, <em class="sig-param"><span class="n">default</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>str<span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="n">add_to_cache_keys</span><span class="p">:</span> <span class="n">bool</span> <span class="o">=</span> <span class="default_value">True</span></em><span class="sig-paren">)</span> &#x2192; Optional<span class="p">[</span>Any<span class="p">]</span><a class="reference internal" href="_modules/superset/jinja_context.html#ExtraCache.url_param"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.ExtraCache.url_param" title="Permalink to this definition"></a></dt>
<dd><p>Read a url or post parameter and use it in your SQL Lab query.</p>
<p>When in SQL Lab, it’s possible to add arbitrary URL “query string” parameters,
and use those in your SQL code. For instance you can alter your url and add
<cite>?foo=bar</cite>, as in <cite>{domain}/superset/sqllab?foo=bar</cite>. Then if your query is
something like SELECT * FROM foo = ‘{{ url_param(‘foo’) }}’, it will be parsed
at runtime and replaced by the value in the URL.</p>
<p>As you create a visualization form this SQL Lab query, you can pass parameters
in the explore view as well as from the dashboard, and it should carry through
to your queries.</p>
<p>Default values for URL parameters can be defined in chart metadata by adding the
key-value pair <cite>url_params: {‘foo’: ‘bar’}</cite></p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><strong>param</strong> – the parameter to lookup</p></li>
<li><p><strong>default</strong> – the value to return in the absence of the parameter</p></li>
<li><p><strong>add_to_cache_keys</strong> – Whether the value should be included in the cache key</p></li>
</ul>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>The URL parameters</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="py function">
<dt id="superset.jinja_context.filter_values">
<code class="sig-prename descclassname">superset.jinja_context.</code><code class="sig-name descname">filter_values</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">column</span><span class="p">:</span> <span class="n">str</span></em>, <em class="sig-param"><span class="n">default</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>str<span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em><span class="sig-paren">)</span> &#x2192; List<span class="p">[</span>str<span class="p">]</span><a class="reference internal" href="_modules/superset/jinja_context.html#filter_values"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.filter_values" title="Permalink to this definition"></a></dt>
<dd><p>Gets a values for a particular filter as a list</p>
<dl class="simple">
<dt>This is useful if:</dt><dd><ul class="simple">
<li><p>you want to use a filter box to filter a query where the name of filter box
column doesn’t match the one in the select statement</p></li>
<li><p>you want to have the ability for filter inside the main query for speed
purposes</p></li>
</ul>
</dd>
</dl>
<p>Usage example:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">SELECT</span> <span class="n">action</span><span class="p">,</span> <span class="n">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">as</span> <span class="n">times</span>
<span class="n">FROM</span> <span class="n">logs</span>
<span class="n">WHERE</span> <span class="n">action</span> <span class="ow">in</span> <span class="p">(</span> <span class="p">{{</span> <span class="s2">&quot;&#39;&quot;</span> <span class="o">+</span> <span class="s2">&quot;&#39;,&#39;&quot;</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">filter_values</span><span class="p">(</span><span class="s1">&#39;action_type&#39;</span><span class="p">))</span> <span class="o">+</span> <span class="s2">&quot;&#39;&quot;</span> <span class="p">}}</span> <span class="p">)</span>
<span class="n">GROUP</span> <span class="n">BY</span> <span class="n">action</span>
</pre></div>
</div>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><strong>column</strong> – column/filter name to lookup</p></li>
<li><p><strong>default</strong> – default value to return if there’s no matching columns</p></li>
</ul>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>returns a list of filter values</p>
</dd>
</dl>
</dd></dl>
<dl class="py class">
<dt id="superset.jinja_context.PrestoTemplateProcessor">
<em class="property">class </em><code class="sig-prename descclassname">superset.jinja_context.</code><code class="sig-name descname">PrestoTemplateProcessor</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">database</span><span class="p">:</span> <span class="n">Database</span></em>, <em class="sig-param"><span class="n">query</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>Query<span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="n">table</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>SqlaTable<span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="n">extra_cache_keys</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>List<span class="p">[</span>Any<span class="p">]</span><span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="o">**</span><span class="n">kwargs</span><span class="p">:</span> <span class="n">Any</span></em><span class="sig-paren">)</span><a class="reference internal" href="_modules/superset/jinja_context.html#PrestoTemplateProcessor"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.PrestoTemplateProcessor" title="Permalink to this definition"></a></dt>
<dd><p>Presto Jinja context</p>
<p>The methods described here are namespaced under <code class="docutils literal notranslate"><span class="pre">presto</span></code> in the
jinja context as in <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">'{{</span> <span class="pre">presto.some_macro_call()</span> <span class="pre">}}'</span></code></p>
<dl class="py method">
<dt id="superset.jinja_context.PrestoTemplateProcessor.first_latest_partition">
<code class="sig-name descname">first_latest_partition</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">table_name</span><span class="p">:</span> <span class="n">str</span></em><span class="sig-paren">)</span> &#x2192; Optional<span class="p">[</span>str<span class="p">]</span><a class="reference internal" href="_modules/superset/jinja_context.html#PrestoTemplateProcessor.first_latest_partition"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.PrestoTemplateProcessor.first_latest_partition" title="Permalink to this definition"></a></dt>
<dd><p>Gets the first value in the array of all latest partitions</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><strong>table_name</strong> – table name in the format <cite>schema.table</cite></p>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>the first (or only) value in the latest partition array</p>
</dd>
<dt class="field-odd">Raises</dt>
<dd class="field-odd"><p><strong>IndexError</strong> – If no partition exists</p>
</dd>
</dl>
</dd></dl>
<dl class="py method">
<dt id="superset.jinja_context.PrestoTemplateProcessor.latest_partition">
<code class="sig-name descname">latest_partition</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">table_name</span><span class="p">:</span> <span class="n">str</span></em><span class="sig-paren">)</span> &#x2192; Optional<span class="p">[</span>str<span class="p">]</span><a class="headerlink" href="#superset.jinja_context.PrestoTemplateProcessor.latest_partition" title="Permalink to this definition"></a></dt>
<dd><p>Gets the first value in the array of all latest partitions</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><strong>table_name</strong> – table name in the format <cite>schema.table</cite></p>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>the first (or only) value in the latest partition array</p>
</dd>
<dt class="field-odd">Raises</dt>
<dd class="field-odd"><p><strong>IndexError</strong> – If no partition exists</p>
</dd>
</dl>
</dd></dl>
<dl class="py method">
<dt id="superset.jinja_context.PrestoTemplateProcessor.latest_partitions">
<code class="sig-name descname">latest_partitions</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">table_name</span><span class="p">:</span> <span class="n">str</span></em><span class="sig-paren">)</span> &#x2192; Optional<span class="p">[</span>List<span class="p">[</span>str<span class="p">]</span><span class="p">]</span><a class="reference internal" href="_modules/superset/jinja_context.html#PrestoTemplateProcessor.latest_partitions"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.PrestoTemplateProcessor.latest_partitions" title="Permalink to this definition"></a></dt>
<dd><p>Gets the array of all latest partitions</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><strong>table_name</strong> – table name in the format <cite>schema.table</cite></p>
</dd>
<dt class="field-even">Returns</dt>
<dd class="field-even"><p>the latest partition array</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="py class">
<dt id="superset.jinja_context.HiveTemplateProcessor">
<em class="property">class </em><code class="sig-prename descclassname">superset.jinja_context.</code><code class="sig-name descname">HiveTemplateProcessor</code><span class="sig-paren">(</span><em class="sig-param"><span class="n">database</span><span class="p">:</span> <span class="n">Database</span></em>, <em class="sig-param"><span class="n">query</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>Query<span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="n">table</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>SqlaTable<span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="n">extra_cache_keys</span><span class="p">:</span> <span class="n">Optional<span class="p">[</span>List<span class="p">[</span>Any<span class="p">]</span><span class="p">]</span></span> <span class="o">=</span> <span class="default_value">None</span></em>, <em class="sig-param"><span class="o">**</span><span class="n">kwargs</span><span class="p">:</span> <span class="n">Any</span></em><span class="sig-paren">)</span><a class="reference internal" href="_modules/superset/jinja_context.html#HiveTemplateProcessor"><span class="viewcode-link">[source]</span></a><a class="headerlink" href="#superset.jinja_context.HiveTemplateProcessor" title="Permalink to this definition"></a></dt>
<dd></dd></dl>
</div>
<div class="section" id="extending-macros">
<h3>Extending macros<a class="headerlink" href="#extending-macros" title="Permalink to this headline"></a></h3>
<p>As mentioned in the <a class="reference external" href="https://superset.incubator.apache.org/installation.html#installation-configuration">Installation &amp; Configuration</a> documentation,
it’s possible for administrators to expose more more macros in their
environment using the configuration variable <code class="docutils literal notranslate"><span class="pre">JINJA_CONTEXT_ADDONS</span></code>.
All objects referenced in this dictionary will become available for users
to integrate in their queries in <strong>SQL Lab</strong>.</p>
</div>
<div class="section" id="customize-templating">
<h3>Customize templating<a class="headerlink" href="#customize-templating" title="Permalink to this headline"></a></h3>
<p>As mentioned in the <a class="reference external" href="https://superset.incubator.apache.org/installation.html#sql-lab">Installation &amp; Configuration</a> documentation,
it’s possible for administrators to overwrite Jinja templating with your customized
template processor using the configuration variable <code class="docutils literal notranslate"><span class="pre">CUSTOM_TEMPLATE_PROCESSORS</span></code>.
The template processors referenced in the dictionary will overwrite default Jinja template processors
of the specified database engines.</p>
</div>
<div class="section" id="query-cost-estimation">
<h3>Query cost estimation<a class="headerlink" href="#query-cost-estimation" title="Permalink to this headline"></a></h3>
<p>Some databases support <code class="docutils literal notranslate"><span class="pre">EXPLAIN</span></code> queries that allow users to estimate the cost
of queries before executing this. Currently, Presto is supported in SQL Lab. To
enable query cost estimation, add the following keys to the “Extra” field in the
database configuration:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>{
&quot;version&quot;: &quot;0.319&quot;,
&quot;cost_estimate_enabled&quot;: true
...
}
</pre></div>
</div>
<p>Here, “version” should be the version of your Presto cluster. Support for this
functionality was introduced in Presto 0.319.</p>
<p>You also need to enable the feature flag in your <cite>superset_config.py</cite>, and you
can optionally specify a custom formatter. Eg:</p>
<div class="highlight-python notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">presto_query_cost_formatter</span><span class="p">(</span><span class="n">cost_estimate</span><span class="p">:</span> <span class="n">List</span><span class="p">[</span><span class="n">Dict</span><span class="p">[</span><span class="nb">str</span><span class="p">,</span> <span class="nb">float</span><span class="p">]])</span> <span class="o">-&gt;</span> <span class="n">List</span><span class="p">[</span><span class="n">Dict</span><span class="p">[</span><span class="nb">str</span><span class="p">,</span> <span class="nb">str</span><span class="p">]]:</span>
<span class="sd">&quot;&quot;&quot;</span>
<span class="sd"> Format cost estimate returned by Presto.</span>
<span class="sd"> :param cost_estimate: JSON estimate from Presto</span>
<span class="sd"> :return: Human readable cost estimate</span>
<span class="sd"> &quot;&quot;&quot;</span>
<span class="c1"># Convert cost to dollars based on CPU and network cost. These coefficients are just</span>
<span class="c1"># examples, they need to be estimated based on your infrastructure.</span>
<span class="n">cpu_coefficient</span> <span class="o">=</span> <span class="mf">2e-12</span>
<span class="n">network_coefficient</span> <span class="o">=</span> <span class="mf">1e-12</span>
<span class="n">cost</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">cost_estimate</span><span class="p">:</span>
<span class="n">cost</span> <span class="o">+=</span> <span class="n">row</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">&quot;cpuCost&quot;</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="o">*</span> <span class="n">cpu_coefficient</span>
<span class="n">cost</span> <span class="o">+=</span> <span class="n">row</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">&quot;networkCost&quot;</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="o">*</span> <span class="n">network_coefficient</span>
<span class="k">return</span> <span class="p">[{</span><span class="s2">&quot;Cost&quot;</span><span class="p">:</span> <span class="sa">f</span><span class="s2">&quot;US$ </span><span class="si">{</span><span class="n">cost</span><span class="si">:</span><span class="s2">.2f</span><span class="si">}</span><span class="s2">&quot;</span><span class="p">}]</span>
<span class="n">DEFAULT_FEATURE_FLAGS</span> <span class="o">=</span> <span class="p">{</span>
<span class="s2">&quot;ESTIMATE_QUERY_COST&quot;</span><span class="p">:</span> <span class="kc">True</span><span class="p">,</span>
<span class="s2">&quot;QUERY_COST_FORMATTERS_BY_ENGINE&quot;</span><span class="p">:</span> <span class="p">{</span><span class="s2">&quot;presto&quot;</span><span class="p">:</span> <span class="n">presto_query_cost_formatter</span><span class="p">},</span>
<span class="p">}</span>
</pre></div>
</div>
</div>
<div class="section" id="create-table-as-ctas">
<span id="ref-ctas-engine-config"></span><h3>Create Table As (CTAS)<a class="headerlink" href="#create-table-as-ctas" title="Permalink to this headline"></a></h3>
<p>You can use <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span> <span class="pre">AS</span> <span class="pre">SELECT</span> <span class="pre">...</span></code> statements on SQLLab. This feature can be toggled on
and off at the database configuration level.</p>
<p>Note that since <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE..</span></code> belongs to a SQL DDL category. Specifically on PostgreSQL, DDL is transactional,
this means that to properly use this feature you have to set <code class="docutils literal notranslate"><span class="pre">autocommit</span></code> to true on your engine parameters:</p>
<div class="highlight-text notranslate"><div class="highlight"><pre><span></span>{
...
&quot;engine_params&quot;: {&quot;isolation_level&quot;:&quot;AUTOCOMMIT&quot;},
...
}
</pre></div>
</div>
</div>
</div>
</div>
</div>
</div>
<footer>
<div class="rst-footer-buttons" role="navigation" aria-label="footer navigation">
<a href="gallery.html" class="btn btn-neutral float-right" title="Visualizations Gallery" accesskey="n" rel="next">Next <span class="fa fa-arrow-circle-right"></span></a>
<a href="security.html" class="btn btn-neutral float-left" title="Security" accesskey="p" rel="prev"><span class="fa fa-arrow-circle-left"></span> Previous</a>
</div>
<hr/>
<div role="contentinfo">
<p>
&copy; Copyright Copyright © 2020 The Apache Software Foundation, Licensed under the Apache License, Version 2.0.
</p>
</div>
</footer>
</div>
</div>
</section>
</div>
<script type="text/javascript">
jQuery(function () {
SphinxRtdTheme.Navigation.enable(true);
});
</script>
</body>
</html>