daq-tools / kotori

A flexible data historian based on InfluxDB, Grafana, MQTT, and more. Free, open, simple.
https://getkotori.org/
GNU Affero General Public License v3.0
107 stars 17 forks source link

[CrateDB] Add support for data acquisition and data export #148

Open amotl opened 1 year ago

amotl commented 1 year ago

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

[^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] commented 1 year ago

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 ```diff @@ 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](https://app.codecov.io/gh/daq-tools/kotori/pull/148/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=daq-tools) | Coverage Δ | | |---|---|---| | [unittests](https://app.codecov.io/gh/daq-tools/kotori/pull/148/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=daq-tools) | `78.86% <89.88%> (+0.26%)` | :arrow_up: | Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=daq-tools#carryforward-flags-in-the-pull-request-comment) to find out more.

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

amotl commented 1 year ago

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.

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.