[CrateDB] Add support for data acquisition and data export

[amotl]

About

Migrating to InfluxDB version 2 would mean to leave SQL behind ^1. While using the Flux query language is intriguing, and I will not reject bringing in support for InfluxDB2 and its successor IOx, supporting an SQL-based timeseries database makes sense for me, and this time maybe even a more capable one than InfluxDB in terms of broader support for data types and SQL operations.

So, I think viable alternatives are both CrateDB and TimescaleDB [^2], which may even share parts of their corresponding adapter implementations, because both are building upon PostgreSQL standards. This patch makes a start by adding support for CrateDB, let's have a look at TimescaleDB later.

Documentation

https://kotori--148.org.readthedocs.build/en/148/database/cratedb.html

Backlog

• [x] Make Grafana instant dashboard provisioning work.
• [x] Make the data export feature work.
• [x] Update documentation across the board.
• [ ] Demonstrate LTTB downsampling on behalf of a secondary Grafana panel.
• [ ] Investigate whether some of the pandas routines on the data export subsystem could be replaced/optimized by query statements using LOCF and NOCB.
• [ ] Investigate how and where max_by and min_by could also be utilized in a sensible way.

[^2]: With the drawback that TimescaleDB also changed the license for parts of their code to non-FOSS, see https://github.com/timescale/timescaledb/blob/main/tsl/LICENSE-TIMESCALE.

[codecov[bot]]

Codecov Report

Attention: Patch coverage is 89.88764% with 18 lines in your changes missing coverage. Please review.

Project coverage is 78.86%. Comparing base (90e815a) to head (c30b6ad).

:exclamation: Current head c30b6ad differs from pull request most recent head a6d66d5

Please upload reports for the commit a6d66d5 to get more accurate results.

Files	Patch %	Lines
kotori/daq/storage/cratedb.py	88.33%	14 Missing :warning:
kotori/daq/graphing/grafana/manager.py	83.33%	2 Missing :warning:
kotori/daq/graphing/grafana/dashboard.py	92.30%	1 Missing :warning:
kotori/daq/services/mig.py	90.00%	1 Missing :warning:

Additional details and impacted files

@@            Coverage Diff             @@
##             main     crate/crate-python#148      +/-   ##
==========================================
+ Coverage   78.59%   78.86%   +0.26%     
==========================================
  Files          55       58       +3     
  Lines        3014     3180     +166     
==========================================
+ Hits         2369     2508     +139     
- Misses        645      672      +27     

Flag	Coverage Δ
unittests	78.86% <89.88%> (+0.26%)	:arrow_up:

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[amotl]

Grafana instant dashboards

About

8abe55d3e05 added baseline support for producing Grafana instant dashboards, and 9c663b2f576d now improves it by using proper time bucketing within the standard SQL statement template, based on emulating GROUP BY DATE_BIN() by using Grafana's $__timeGroupAlias macro for casting $__interval values, until CrateDB's DATE_BIN() function understands Grafana's native interval values.

• https://github.com/crate/crate/issues/14211

Reference documentation

$__timeGroup(dateColumn, $__interval) will be replaced by an expression usable in a GROUP BY clause. $__timeGroupAlias(dateColumn, $__interval) will be replaced identical to $__timeGroup but with an added column alias.

-- https://grafana.com/docs/grafana/latest/datasources/postgres/#macros

Thanks

Thank you for the guidance, @seut and @hammerhead.