Expressions: Sql expressions with Duckdb

[scottlepp]

What is this feature?

This is a subset of the Optimus Duck hackathon. Which won the bronze in the Dec 2023 Hackathon.

This adds a SQL type to Expressions allowing the execution of SQL referencing queries on the panel. This provides powerful transformations, chaining SQL (think SQL Notebooks), analytics and aggregate functions from DuckDB, and provides the ability to transform data in Alerting, something that isn't possible with front end Transformations but is highly sought after.

Why do we need this feature?

• More powerful transformations.
• Backend transformations that can process more data with better performance.
• A plethora of Analytical/Aggregate functions from DuckDB that we don't have to write/maintain.
• Provides a way to transform data with Alerting.
  • Provides a solution for some long standing issues:
  • Alerting support for queries using transformations #42881
  • Alerting support for queries using transformations #26516
  • https://github.com/grafana/observability-metrics-squad/issues/50
  • https://github.com/grafana/observability-metrics-squad/issues/51
  • Unable to apply alerts on graphs using transformations #37751

Who is this feature for?

• Anyone interested in using Transformations.
• Users who prefer a standard query language (SQL) over using Front End Transformations.
• Anyone who wants more powerful Alerting.

Population by State (gdev CSV file example)

Summarized Population for 2020 using SQL Expression

Please check that:

• It works as expected from a user's perspective.
• If this is a pre-GA feature, it is behind a feature toggle.
• The docs are updated, and if this is a notable improvement, it's added to our What's New doc.

Fixes https://github.com/orgs/grafana/projects/55/views/19?pane=issue&itemId=51638046

[scottlepp]

@kylebrandt added the changes we discussed ( and fixed the issue you noticed ).

Can you check/review this again please? NOTE: you will need to have the duckdb cli installed now and in your path.

[ryantxu]

this looks great -- very nice compromise that lets us make progress without exploding the build size for everyone. Given the feature toggle and limited dependency issues, i think it is safe to merge and improve some of the ergonomics in follup PRs -- most important is error handling and explanations

[kylebrandt]

I think we can make it more interoperable by laying over dataplane in another pass:

• Add QueryType Dropdown (Table/TimeSeries/Numeric) in UI
• Introduce two SSE types that mirror dataplane types, TimeSeriesLong and NumericLong
• Use those types respectively when TimeSeries or Numeric or selected
• (At this point, the output should work with Record queries for example)
• Update SSE operators to be able to read the Long Types (could be shim grafana/dataplane/sdata should make this relatively easy)