Support
Quality
Security
License
Reuse
kandi has reviewed questdb and discovered the below as its top functions. This is intended to give you an instant insight into questdb implemented functionality, and help decide if they suit your requirements.
An open source SQL database designed to process time series data, faster
Try QuestDB
docker run -p 9000:9000 -p 9009:9009 -p 8812:8812 questdb/questdb
How do I aggregate open and close values in QuestDB using SAMPLE BY
select max(high) high,
min(low) low,
first(open) as open,
last(close) as close
from ohlc
SAMPLE BY 1M
How to generate a monthly report filling missing days of the month with zero
SELECT x as day, COALESCE(sb.count_items, 0) AS count_items
from long_sequence(31) ls
LEFT JOIN (
SELECT DAY(ts) as day, COUNT_DISTINCT(item) AS count_items
FROM my_table
WHERE ts IN '2020-01'
SAMPLE BY d
) AS sb ON sb.day = ls.x
-----------------------
SELECT x as day, COALESCE(sb.count_items, 0) AS count_items
from long_sequence(DAYS_IN_MONTH('2020-01') ls
LEFT JOIN (
SELECT CAST(DAY(ts) AS LONG) as day, COUNT_DISTINCT(item) AS count_items
FROM my_table
WHERE ts IN '2020-01'
SAMPLE BY d
) AS sb ON sb.day = ls.x
QuestDB : difference between table_columns() and tables()
table_columns('pos')
or
SHOW COLUMNS FROM 'mytable'
When working with QuestDB, are symbol columns good for performance for huge amounts of rows each?
|----------------------------------|
| time | stock1 | stock1 | stock3 |
|----------------------------------|
|----------------------------------|
| time | stock1 | stock1 | stock3 |
|----------------------------------|
| t1 | 1.1 | | |
| t2 | | 3.45 | |
| t3 | | | 103.45 |
|----------------------------------|
-----------------------
|----------------------------------|
| time | stock1 | stock1 | stock3 |
|----------------------------------|
|----------------------------------|
| time | stock1 | stock1 | stock3 |
|----------------------------------|
| t1 | 1.1 | | |
| t2 | | 3.45 | |
| t3 | | | 103.45 |
|----------------------------------|
How can I store MQTT data into QuestDB?
# Configuration for Telegraf agent
[agent]
## Default data collection interval for all inputs
interval = "5s"
hostname = "qdb"
[[outputs.socket_writer]]
# Write metrics to a local QuestDB instance over TCP
address = "tcp://127.0.0.1:9009"
[[inputs.mqtt_consumer]]
## The MQTT broker
servers = ["tcp://127.0.0.1:1883"]
## Topics that will be subscribed to.
topics = [
"telegraf/host01/cpu",
"telegraf/+/mem",
"sensors/#",
]
How can I update a QuestDB docker version without losing the container's data?
# copy the contents of the old_questdb container to the current dir
docker cp old_questdb:/root/.questdb $(pwd)
# run 6.0.4 and mount to the current dir
docker run --name new_questdb \
-v "$(pwd)/.questdb:/root/.questdb/" \
-p 9000:9000 -p 9009:9009 questdb/questdb:6.0.4
How can I plot a moving average from Pandas to a chart?
import psycopg2
import pandas as pd
import plotly.graph_objects as go
df_trades = pd.DataFrame()
try:
connection = psycopg2.connect(user="admin",
password="quest",
host="127.0.0.1",
port="8812",
database="qdb")
cursor = connection.cursor()
df_trades = pd.read_sql_query("select * from my_table",connection)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to QuestDB", error)
finally:
if (connection):
cursor.close()
connection.close()
print("QuestDB connection closed")
print(df_trades.head())
fig = go.Figure()
fig.update_layout(title_text="Table candlestick")
df_trades['10point_ma'] = df_trades['close'].rolling(window=10).mean()
fig.add_trace(go.Scatter(x=df_trades['ts'], y=df_trades['10point_ma'],
name='10 point moving average',
mode='lines',
opacity=1,
marker=dict(color='MediumPurple',
size=1)))
# original table as candlestick chart
fig.add_trace(go.Candlestick(x=df_trades['ts'],
open=df_trades['open'],
high=df_trades['high'],
low=df_trades['low'],
close=df_trades['close'],
name='My Awesome Chart'))
fig.update(layout_xaxis_rangeslider_visible=False)
fig.show()
How do I get query results from QuestDB into a Pandas dataframe?
import psycopg2
import pandas as pd
dataframe = pd.DataFrame()
try:
connection = psycopg2.connect(user="admin",
password="quest",
host="127.0.0.1",
port="8812",
database="qdb")
cursor = connection.cursor()
dataframe = pd.read_sql_query("select * from my_table",connection)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to QuestDB", error)
finally:
if (connection):
cursor.close()
connection.close()
print("QuestDB connection closed")
print(dataframe)
timestamp event origin
0 2021-08-23 12:15:43.582771 100 1
1 2021-08-23 12:15:46.529379 1 2
2 2021-08-23 12:15:46.656823 1 2
3 2021-08-23 12:15:46.662040 1 2
4 2021-08-23 12:15:46.805505 1 2
5 2021-08-23 12:15:46.807359 1 2
6 2021-08-23 12:15:48.631560 1 2
7 2021-08-23 12:16:08.120285 6 3
8 2021-08-23 12:16:58.080873 6 3
9 2021-08-23 12:16:58.081986 6 3
10 2021-08-23 12:16:58.084083 1 3
-----------------------
import psycopg2
import pandas as pd
dataframe = pd.DataFrame()
try:
connection = psycopg2.connect(user="admin",
password="quest",
host="127.0.0.1",
port="8812",
database="qdb")
cursor = connection.cursor()
dataframe = pd.read_sql_query("select * from my_table",connection)
except (Exception, psycopg2.Error) as error:
print("Error while connecting to QuestDB", error)
finally:
if (connection):
cursor.close()
connection.close()
print("QuestDB connection closed")
print(dataframe)
timestamp event origin
0 2021-08-23 12:15:43.582771 100 1
1 2021-08-23 12:15:46.529379 1 2
2 2021-08-23 12:15:46.656823 1 2
3 2021-08-23 12:15:46.662040 1 2
4 2021-08-23 12:15:46.805505 1 2
5 2021-08-23 12:15:46.807359 1 2
6 2021-08-23 12:15:48.631560 1 2
7 2021-08-23 12:16:08.120285 6 3
8 2021-08-23 12:16:58.080873 6 3
9 2021-08-23 12:16:58.081986 6 3
10 2021-08-23 12:16:58.084083 1 3
What does '[24] could not open read-only' error mean while benchmarking QuestDB in docker?
docker run --ulimit nofile=5000:5000 \
-p 9000:9000 -p 8812:8812 -p 9009:9009 \
questdb/questdb
How to avoid error "Cannot insert rows out of order" in QuestDB?
create table records2(
type INT,
interval INT,
timestamp TIMESTAMP,
name STRING
)
timestamp(timestamp) partition by DAY
insert into records2
select * from records
drop table records
rename table records2 to records
QUESTION
What is a equivalent of a merge query(insert/update/delete) is present in the QuestDB?
Asked 2022-Feb-11 at 09:52What is a equivalent of a merge query(insert/update/delete) is present in the QuestDB?
Below is an example for tsql. I would like to understand how to implement the same logic in QuestDB - insert rows for new data and update rows for existing data (if they changed)
https://www.sqlshack.com/understanding-the-sql-merge-statement/ USE SqlShackMergeDemo GO
MERGE TargetProducts AS Target
USING SourceProducts AS Source
ON Source.ProductID = Target.ProductID
-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (ProductID,ProductName, Price)
VALUES (Source.ProductID,Source.ProductName, Source.Price)
-- For Updates
WHEN MATCHED THEN UPDATE SET
Target.ProductName = Source.ProductName,
Target.Price = Source.Price;
ANSWER
Answered 2022-Feb-11 at 09:52QuestDB (current v6.2) does not support any form UPDATE or DELETE statements at the moment. The only way to delete data is to drop a partition or truncate table. There is no equivalent for MERGE either.
This is going to change soon.
Community Discussions, Code Snippets contain sources that include Stack Exchange Network
No vulnerabilities reported
Save this library and start creating your kit
Explore Related Topics
Save this library and start creating your kit