<h1 dir="auto">Motivation</h1>
<p dir="auto">Druid SQL should be able to emit SQL metrics and write SQL request logs. It should also help users correlate SQL request with underlying native queries so that users can troubleshoot SQL performance issue using native query metrics.</p>
<p dir="auto">This is a follow-up work of <a href="https://github.com/apache/incubator-druid/issues/4047" data-hovercard-type="issue" data-hovercard-url="/apache/druid/issues/4047/hovercard">#4047</a>. I'm trying to implement all the missing functionalities except SQL cancellation.</p>
<h1 dir="auto">Proposed implementation</h1>
<p dir="auto">There are two entries for SQL processing: <code class="notranslate">SqlResource</code> for HTTP request and <code class="notranslate">DruidStatement</code> for JDBC request. In order to remove duplicated logic, <code class="notranslate">SqlLifecycle</code> is added as a central place to manage sql execution. It follows the same design as <code class="notranslate">QueryLifecycle</code> except it's thread-safe, since <code class="notranslate">SqlLifecycle</code> can be invoked in multi-threads in JDBC use cases.</p>
<p dir="auto">The public interface of SqlLifecycle would be</p>
<div class="highlight highlight-source-java notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="class SqlLifecyle {
  // state transition: NEW -&gt; INITIALIZED
  void initialize(SqlQuery);
  void initialize(String sql, Map&lt;String, Object&gt; queryContext);
  // state transition: INITIALIZED -&gt; PLANNED
  void plan(AuthenticationResult);
  void plan(HttpServletRequest);
  // state transition: PLANNED -&gt; AUTHORIZED or UNAUTHORIZED
  Access authorize();
  // state transition: AUTHORIZED -&gt; EXECUTING
  Sequence&lt;Object[]&gt; execute();
  // state transition: -&gt; DONE
  void emitLogsAndMetrics(Throwable, String remoteAddress, long bytesWritten);
}"><pre class="notranslate"><span class="pl-k">class</span> <span class="pl-smi">SqlLifecyle</span> {
  <span class="pl-c">// state transition: NEW -&gt; INITIALIZED</span>
  <span class="pl-smi">void</span> <span class="pl-en">initialize</span>(<span class="pl-smi">SqlQuery</span>);
  <span class="pl-smi">void</span> <span class="pl-en">initialize</span>(<span class="pl-smi">String</span> <span class="pl-s1">sql</span>, <span class="pl-smi">Map</span>&lt;<span class="pl-smi">String</span>, <span class="pl-smi">Object</span>&gt; <span class="pl-s1">queryContext</span>);
  <span class="pl-c">// state transition: INITIALIZED -&gt; PLANNED</span>
  <span class="pl-smi">void</span> <span class="pl-en">plan</span>(<span class="pl-smi">AuthenticationResult</span><span class="pl-s1"></span>);
  <span class="pl-smi">void</span> <span class="pl-en">plan</span>(<span class="pl-smi">HttpServletRequest</span><span class="pl-s1"></span>);
  <span class="pl-c">// state transition: PLANNED -&gt; AUTHORIZED or UNAUTHORIZED</span>
  <span class="pl-smi">Access</span> <span class="pl-en">authorize</span>();
  <span class="pl-c">// state transition: AUTHORIZED -&gt; EXECUTING</span>
  <span class="pl-smi">Sequence</span>&lt;<span class="pl-smi">Object</span>[]&gt; <span class="pl-en">execute</span>();
  <span class="pl-c">// state transition: -&gt; DONE</span>
  <span class="pl-smi">void</span> <span class="pl-en">emitLogsAndMetrics</span>(<span class="pl-smi">Throwable</span><span class="pl-s1"></span>, <span class="pl-smi">String</span> <span class="pl-s1">remoteAddress</span>, <span class="pl-smi">long</span> <span class="pl-s1">bytesWritten</span>);
}</pre></div>
<p dir="auto">In <code class="notranslate">initialize</code> method, a <code class="notranslate">sqlId</code> is assigned to each SQL query. User can specify <code class="notranslate">sqlId</code> in query context, or Druid will generate a UUID for it, just like <code class="notranslate">queryId</code> for native query. We call it <code class="notranslate">sqlId</code> to make it clear that <code class="notranslate">sqlId</code> is for SQL request while <code class="notranslate">queryId</code> is for native query, and one SQL can map to several native queries.</p>
<p dir="auto">To help users correlate SQL with native queries, <code class="notranslate">sqlId</code> is added to query context of native queries, so that from native query's request log, it's clear whether the native query is from end user or SQL, and if it's from SQL, which SQL is it comes from. Similarly, <code class="notranslate">queryId</code> of all native queries underpinning a SQL are collected and stored in SQL's query context key <code class="notranslate">nativeQueryIds</code>, so that from SQL's request log or metrics, it's clear which native queries it contains. Finally, <code class="notranslate">sqlId</code> and its corresponding <code class="notranslate">queryId</code>s are returned in <code class="notranslate">X-Druid-SQL-Id</code> and <code class="notranslate">X-Druid-Native-Query-Ids</code> HTTP headers.</p>
<p dir="auto">Two SQL metrics are emitted in <code class="notranslate">emitLogsAndMetrics</code> method, namely <code class="notranslate">sql/time</code> and <code class="notranslate">sql/bytes</code>. I perfer <code class="notranslate">sql/time</code> to <code class="notranslate">query/sql/time</code> proposed in <a href="https://github.com/apache/incubator-druid/issues/4047" data-hovercard-type="issue" data-hovercard-url="/apache/druid/issues/4047/hovercard">#4047</a> because I think in the past, all metrics starting with "query/" are for native queries, and we should stick to that convention. Also <code class="notranslate">sql/time</code> makes SQL a first citizen and is more intuitive for me to understand. SQL metrics will have the following dimensions: id, nativeQueryIds, dataSource, remoteAddress, and success.</p>
<p dir="auto">SQL requests are logged in <code class="notranslate">emitLogsAndMetrics</code> via <code class="notranslate">SqlRequestLogger</code>. Several built-in impls such as <code class="notranslate">NoopSqlRequestLogger</code> and <code class="notranslate">FileSqlRequestLogger</code> are provided, of cause we can add more later. <code class="notranslate">SqlRequestLogger</code> is configured via <code class="notranslate">druid.sql.request.logging</code>, and can be extended in similar way as RequestLogger.</p>
<div class="highlight highlight-source-java notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="public interface SqlRequestLogger
{
  void log(SqlRequestLogLine sqlRequestLogLine) throws IOException;
  default void start() throws IOException {}
  default void stop() {}
}"><pre class="notranslate"><span class="pl-k">public</span> <span class="pl-k">interface</span> <span class="pl-smi">SqlRequestLogger</span>
{
  <span class="pl-smi">void</span> <span class="pl-en">log</span>(<span class="pl-smi">SqlRequestLogLine</span> <span class="pl-s1">sqlRequestLogLine</span>) <span class="pl-k">throws</span> <span class="pl-smi">IOException</span>;
  <span class="pl-k">default</span> <span class="pl-smi">void</span> <span class="pl-en">start</span>() <span class="pl-k">throws</span> <span class="pl-smi">IOException</span> {}
  <span class="pl-k">default</span> <span class="pl-smi">void</span> <span class="pl-en">stop</span>() {}
}</pre></div>
<p dir="auto">The 'cluster' panel has:</p>
<ol dir="auto">
<li>Duplicate links to 'old console'</li>
<li>A perpetual 'loading' spinner</li>
</ol>
<p dir="auto">Screenshot:<br>
<a href="http://screencast.com/t/Cxh3rltYf" rel="nofollow">http://screencast.com/t/Cxh3rltYf</a></p>