src/partials/query.editor.html
<query-editor-row query-ctrl="ctrl" has-text-edit-mode="true">
<div ng-if="ctrl.target.rawQuery">
<div class="gf-form-inline">
<div class="gf-form gf-form--grow">
<code-editor content="ctrl.target.rawSql" datasource="ctrl.datasource"
on-change="ctrl.panelCtrl.refresh()" data-mode="sql">
</code-editor>
</div>
</div>
</div>
<div ng-if="!ctrl.target.rawQuery">
<div class="gf-form-inline">
<div class="gf-form">
<label class="gf-form-label query-keyword width-8">FROM</label>
<metric-segment segment="ctrl.projectSegment"
get-options="ctrl.getProjectSegments()"
on-change="ctrl.projectChanged()"></metric-segment>
<metric-segment segment="ctrl.datasetSegment"
get-options="ctrl.getDatasetSegments()"
on-change="ctrl.datasetChanged()"></metric-segment>
<metric-segment segment="ctrl.tableSegment"
get-options="ctrl.getTableSegments()"
on-change="ctrl.tableChanged()"></metric-segment>
<label class="gf-form-label query-keyword width-7">Time column</label>
<metric-segment segment="ctrl.timeColumnSegment"
get-options="ctrl.getTimeColumnSegments()"
on-change="ctrl.timeColumnChanged()"></metric-segment>
<label class="gf-form-label query-keyword width-10">
Metric column
<info-popover mode="right-normal">Column to be used as metric name for
the value column.
</info-popover>
</label>
<metric-segment segment="ctrl.metricColumnSegment"
get-options="ctrl.getMetricColumnSegments()"
on-change="ctrl.metricColumnChanged()"></metric-segment>
</div>
<div class="gf-form gf-form--grow">
<div class="gf-form-label gf-form-label--grow"></div>
</div>
</div>
<div class="gf-form-inline" ng-repeat="selectParts in ctrl.selectParts">
<div class="gf-form">
<label class="gf-form-label query-keyword width-8">
<span ng-show="$index === 0">SELECT</span>
</label>
</div>
<div class="gf-form" ng-repeat="part in selectParts">
<sql-part-editor class="gf-form-label sql-part" part="part"
handle-event="ctrl.handleSelectPartEvent(selectParts, part, $event)">
</sql-part-editor>
</div>
<div class="gf-form">
<label class="dropdown"
dropdown-typeahead2="ctrl.selectMenu"
dropdown-typeahead-on-select="ctrl.addSelectPart(selectParts, $item, $subItem)"
button-template-class="gf-form-label query-part"
>
</label>
</div>
<div class="gf-form gf-form--grow">
<div class="gf-form-label gf-form-label--grow"></div>
</div>
</div>
<div class="gf-form-inline">
<div class="gf-form">
<label class="gf-form-label query-keyword width-8">WHERE</label>
</div>
<div class="gf-form" ng-repeat="part in ctrl.whereParts">
<sql-part-editor class="gf-form-label sql-part" part="part"
handle-event="ctrl.handleWherePartEvent(ctrl.whereParts, part, $event, $index)">
</sql-part-editor>
</div>
<div class="gf-form">
<metric-segment segment="ctrl.whereAdd"
get-options="ctrl.getWhereOptions()"
on-change="ctrl.addWhereAction(part, $index)"></metric-segment>
</div>
<div class="gf-form gf-form--grow">
<div class="gf-form-label gf-form-label--grow"></div>
</div>
</div>
<div class="gf-form-inline">
<div class="gf-form">
<label class="gf-form-label query-keyword width-8">
<span>GROUP BY</span>
</label>
<sql-part-editor ng-repeat="part in ctrl.groupParts"
part="part" class="gf-form-label sql-part"
handle-event="ctrl.handleGroupPartEvent(part, $index, $event)">
</sql-part-editor>
</div>
<div class="gf-form">
<metric-segment segment="ctrl.groupAdd"
get-options="ctrl.getGroupOptions()"
on-change="ctrl.addGroupAction(part, $index)"></metric-segment>
</div>
<div class="gf-form gf-form--grow">
<div class="gf-form-label gf-form-label--grow"></div>
</div>
</div>
</div>
<!-- Start Order BY -->
<div class="gf-form">
<label class="gf-form-label query-keyword width-8">ORDER BY</label>
<div class="gf-form-select-wrapper">
<select class="gf-form-input gf-size-auto"
ng-model="ctrl.target.orderByCol"
ng-options="f.value as f.text for f in ctrl.orderByCols"
ng-change="ctrl.refresh()"></select>
</div>
<div class="gf-form-select-wrapper">
<select class="gf-form-input gf-size-auto"
ng-model="ctrl.target.orderBySort"
ng-options="f.value as f.text for f in ctrl.orderBySorts"
ng-change="ctrl.refresh()"></select>
</div>
<div class="gf-form gf-form--grow">
<div class="gf-form-label gf-form-label--grow"></div>
</div>
</div>
<!-- End Order BY. -->
<div class="gf-form-inline">
<div class="gf-form">
<label class="gf-form-label query-keyword">Format as</label>
<div class="gf-form-select-wrapper">
<select class="gf-form-input gf-size-auto" ng-model="ctrl.target.format"
ng-options="f.value as f.text for f in ctrl.formats"
ng-change="ctrl.refresh()"></select>
</div>
</div>
<div>
<gf-form-switch class="gf-form"
label="Convert time filters to UTC"
label-class="query-keyword"
checked="ctrl.target.convertToUTC"
</gf-form-switch>
</div>
<div class="gf-form">
<label class="gf-form-label query-keyword">Processing Location</label>
<div class="">
<!-- <select class="gf-form-input gf-size-auto" ng-model="ctrl.target.location"
ng-options="f.value as f.text for f in ctrl.locations"
ng-change="ctrl.refresh()"></select> -->
<input class="gf-form-input gf-size-auto" list="locationList" ng-model="ctrl.target.location"/>
<datalist id="locationList">
<option value=US>United States (US)</option>
<option value=EU>European Union (EU)</option>
<option value=us-west1>Oregon (us-west1)</option>
<option value=us-west2>Los Angeles (us-west2)</option>
<option value=us-west3>Salt Lake City (us-west3)</option>
<option value=us-west4>Las Vegas (us-west4)</option>
<option value=us-central1>Iowa (us-central1)</option>
<option value=us-east1>South Carolina (us-east1)</option>
<option value=us-east4>Northern Virginia (us-east4)</option>
<option value=northamerica-northeast1>Montréal (northamerica-northeast1)</option>
<option value=southamerica-east1>São Paulo (southamerica-east1)</option>
<option value=europe-north1>Finland (europe-north1)</option>
<option value=europe-west1>Belgium (europe-west1)</option>
<option value=europe-west2>London (europe-west2)</option>
<option value=europe-west3>Frankfurt (europe-west3)</option>
<option value=europe-west4>Netherlands (europe-west4)</option>
<option value=europe-west6>Zürich (europe-west6)</option>
<option value=asia-east2>Hong Kong (asia-east2)</option>
<option value=asia-south1>Mumbai (asia-south1)</option>
<option value=asia-northeast2>Osaka (asia-northeast2)</option>
<option value=asia-east1>Taiwan (asia-east1)</option>
<option value=asia-northeast1>Tokyo (asia-northeast1)</option>
<option value=asia-southeast1>Singapore (asia-southeast1)</option>
<option value=asia-southeast2>Jakarta (asia-southeast2)</option>
<option value=australia-southeast1>Sydney (australia-southeast1)</option>
<option value=asia-northeast3>Seoul (asia-northeast3)</option>
</datalist>
</div>
</div>
<div class="gf-form">
<label class="gf-form-label query-keyword pointer"
ng-click="ctrl.toggleEditorMode()"
ng-show="ctrl.panelCtrl.panel.type !== 'table'">
<span ng-show="ctrl.target.rawQuery">Query Builder</span>
<span ng-hide="ctrl.target.rawQuery">Edit SQL</span>
</label>
</div>
<div class="gf-form">
<label class="gf-form-label query-keyword pointer"
ng-click="ctrl.showHelp = !ctrl.showHelp">
Show Help
<i class="fa fa-caret-down" ng-show="ctrl.showHelp"></i>
<i class="fa fa-caret-right" ng-hide="ctrl.showHelp"></i>
</label>
</div>
<div class="gf-form" ng-show="ctrl.lastQueryMeta">
<label class="gf-form-label query-keyword pointer"
ng-click="ctrl.showLastQuerySQL = !ctrl.showLastQuerySQL">
Generated SQL
<i class="fa fa-caret-down" ng-show="ctrl.showLastQuerySQL"></i>
<i class="fa fa-caret-right" ng-hide="ctrl.showLastQuerySQL"></i>
</label>
</div>
<div class="gf-form gf-form--grow">
<div class="gf-form-label gf-form-label--grow"></div>
</div>
</div>
<div class="gf-form" ng-show="ctrl.showLastQuerySQL">
<pre class="gf-form-pre">{{ctrl.lastQueryMeta.sql}}</pre>
</div>
<div class="gf-form" ng-show="ctrl.showHelp">
<pre class="gf-form-pre alert alert-info">Time series:
- return column named <i>time</i> (UTC in seconds or timestamp)
- return column(s) with numeric datatype as values
Optional:
- return column named <i>metric</i> to represent the series name.
- If multiple value columns are returned the metric column is used as prefix.
- If no column named metric is found the column name of the value column is used as series name
Resultsets of time series queries need to be sorted by time.
Table:
- return any set of columns
Macros:
- $__timeFilter(column) -> column BETWEEN '2017-04-21T05:01:17Z' AND '2017-04-21T05:01:17Z'
- $__timeFrom(column) -> column > '2017-04-21T05:01:17Z'
- $__timeTo(column) -> column < '2017-04-21T05:01:17Z'
- $__timeGroup(column,'5m') -> (extract(epoch from column)/300)::bigint*300 AS "time"
- $__timeShifting(1m) -> compare data from the current range to the previous range
- $__millisTimeFrom(time) -> Translate time ito millis
- __millisTimeTo(time) -> Translate time ito millis
Example of group by and order by with $__timeGroup:
SELECT
$__timeGroup(date_time_col, '1h'),
sum(value) as value
FROM yourtable
GROUP BY time
ORDER BY time
</pre>
</div>
</div>
<div class="gf-form" ng-show="ctrl.lastQueryError">
<pre class="gf-form-pre alert alert-error">{{ctrl.lastQueryError}}</pre>
</div>
</query-editor-row>