It must be a backend data source since SQL expressions is server side.
SQL Expressions supports two general categories of responses:
- Tabular data (A single Frame, with no labels on any of the columns)
- Dataplane Metric Data (For labaled time series or numeric data) https://grafana.com/developers/dataplane
Therefore, support is really per query type or response type in DSes, and is often not all or nothing.
For tabular data it should just work, so the only thing to do is test out a basic select query.
For metric data, SQL Expressions detects metrics based on the Frame.Meta.Type dataplane type only (not the version.) That can be timeseries-multi, timeseries-wide, numeric-multi, or numeric-wide. (Long types should fall into the tabular category). So if your DS has metric data that matches one of those data types, it should work as long as Frame.Meta.Type is set to that data type.
In a dashboard, and for each type for response type your query offers:
- Add your DS query
- Add Expression SQL
- The default should be
select * from A LIMIT 10(assuming your query is RefIDA). If this works on a few variations of that query type, it should be compatible with SQL expressions. - If it doesn't work, see section above, if you are sending metadata and the datatypes mentioned above (or tabular data, and it isn't working, there may be an issue with SQL expressions)
Two new data types have been introduces with SQL expressions. timeseries-full-long and numeric-full-long. This is a row-oriented tabular format similar to long (labels are not used), but structured in a way that allows more lossless conversion of information between the labeled (wide and multi) formats. So the use case for this format is when you need to be able to convert between labeled and tabular formats with more preservation of the information when converting back and forth. Whereas Long can create artifacts when converting.
There are three reserved fields (columns):
__metric_name__: The name of the metric__value__: A nullable float64 numeric column, where this is the value that that represents combination of the metric name and dimensions for the row.__display_name__: (Optional) When converting from other formats, if theDisplayNameFromDSproperty is set, it is flatted into this column- Note: Additional reserved names may be added in the same naming style. This would be to flatten other column metadata such as a data links or units.
Like long any dimensions (that would be labels in in wide or multi) become their own field (column) for . They become nullable string fields where the name of column correspond to a label/dimension key, and values are in the rows of that field. The the case of full_long they are nullable string fields. When the value on a row is null, the label is considered absent.
Single Metric Example:
| __metric_name__ | __value__ | host | region |
| cpu_load | 0.82 | a | us-east-1 |
| cpu_load | 0.61 | b | us-east-1 |
Multi-Metric Example:
| __metric_name__ | __value__ | host | region |
| cpu_load | 0.82 | a | us-east-1 |
| cpu_load | 0.61 | b | null |
| disk_free_bytes | 1.23e+12 | a | us-east-1 |
| disk_free_bytes | 9.80e+11 | b | us-east-1 |
In the long format each dimension gets a field (column) and each unique metric name gets a field as well. The Field's Name property is the metric name. Therefore the dimensions that would be labels in the wide and multi formats get flatted, but the metric name is not treated as a dimension and does not get flatted.
In the full-long formats, the metric name is treated as another dimension. This creates two reserved fields for this purpose in full long __value__ and __metric_name__.
This format also uses nullable strings for dimensions. This means that if the value is null for the row, where converting to labels, that label should be considered not present.
In long, the numeric type for the value is persevered. For example a metric that is a unit64 will stay that way. Because full-long flattens to a a single column (__value__), all numeric values become nullable float64 values. In most cases, upstream we effectively convert to something like a float64 (or JS float), so this likely doesn't matter much in current practice.
When SQL expressions receives the data in the kinds of timeseries or numeric in the wide or multi formats, it converts it to the corresponding full long format. This is because there is no notion of labels in SQL, so the data must be flatted into tabular format.