Title
j
John Lewis
03/04/2023, 1:51 PMQuest DB has limited analytics functions, i'd like to perform various type of analysis (Regression, Moving average, swinging door algorithm, etc) on questdb tables and store the analysis results back into questdb. Can anyone suggest, recommend open source solutions that would support this?
h
Holger
03/04/2023, 2:34 PM@John Lewis I would do that in python with numba for speed. If you are looking for a tool for the plumbing I can highly recommend
https://www.mage.ai/
It has postgres support so the main building blocks are already there.
I can help with the swinging door compression.
n
Nicolas Hourcard
03/04/2023, 2:54 PM@Imre is working on an integration with spark that may be able to help you
i
Imre
03/04/2023, 3:42 PMhi @John Lewis, here is a quick example for calculating moving averages in spark.
// load data as dataframe
from pyspark.sql import SparkSession
query = "SELECT symbol, sum(amount) as volume, round((max(price)+min(price))/2, 2) as mid, timestamp as ts " \
"FROM trades WHERE symbol = 'BTC-USD' " \
"SAMPLE BY 1m ALIGN to CALENDAR"
spark = SparkSession.builder.appName("spark_questdb") \
.config("spark.jars", "postgresql-42.5.1.jar") \
.getOrCreate()
df = spark.read.format("jdbc").option("url", "jdbc:<postgresql://localhost:8812/questdb>") \
.option("driver", "org.postgresql.Driver").option("user", "admin").option("password", "quest") \
.option("query", query).load()// add 30-min moving average as a new column
from pyspark.sql.window import Window
window30 = Window.rowsBetween(-30, Window.currentRow)
df = df.withColumn("ma30", avg(df.mid).over(window30))df.show(3, False)
+-------+------------------+------------------+-------------------+------------------+
|symbol |volume |mid |ts |ma30 |
+-------+------------------+------------------+-------------------+------------------+
|BTC-USD|28.879045500000096|23133.29 |2023-02-01 00:00:00|23133.29 |
|BTC-USD|28.832535900000032|23130.18 |2023-02-01 00:01:00|23131.735 |
|BTC-USD|44.71088651999993 |23136.61 |2023-02-01 00:02:00|23133.36 |of course the first 29 values are not really 30-min moving averages but it is just a quick example
that is if you decide to try Spark
j
John Lewis
03/05/2023, 9:39 AMWould anyone recommend telegraf and starlink scripting for this type of processing?