kandi background

questdb | open source SQL database designed to process time series data | Time Series Database library

by questdb Java Version: 6.2.1 License: Apache-2.0

by questdb Java Version: 6.2.1 License: Apache-2.0

Download this library from

kandi X-RAY | questdb Summary

questdb is a Java library typically used in Database, Time Series Database applications. questdb has no bugs, it has no vulnerabilities, it has build file available, it has a Permissive License and it has medium support. You can install using 'pip install questdb' or download it from GitHub, PyPI.

QuestDB is a high-performance, open-source SQL database for applications in financial services, IoT, machine learning, DevOps and observability. It includes endpoints for PostgreSQL wire protocol, high-throughput schema-agnostic ingestion using InfluxDB Line Protocol, and a REST API for queries, bulk imports, and exports. QuestDB implements ANSI SQL with native extensions for time-oriented language features. These extensions make it simple to correlate data from multiple sources using relational and time series joins. QuestDB achieves high performance from a column-oriented storage model, massively-parallelized vector execution, SIMD instructions, and various low-latency techniques. The entire codebase was built from the ground up in Java and C++, with no dependencies, and is 100% free from garbage collection.

Support

Support

Quality

Quality

Security

Security

License

License

Reuse

Reuse

kandi-support Support

questdb has a medium active ecosystem.
It has 8390 star(s) with 462 fork(s). There are 108 watchers for this library.
There were 1 major release(s) in the last 6 months.
There are 200 open issues and 551 have been closed. On average issues are closed in 64 days. There are 9 open pull requests and 0 closed requests.
It has a neutral sentiment in the developer community.
The latest version of questdb is 6.2.1

questdb Support

Best in #Time Series Database

Average in #Time Series Database

questdb Support

Best in #Time Series Database

Average in #Time Series Database

quality kandi Quality

questdb has 0 bugs and 0 code smells.

questdb Quality

Best in #Time Series Database

Average in #Time Series Database

questdb Quality

Best in #Time Series Database

Average in #Time Series Database

security Security

questdb has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
questdb code analysis shows 0 unresolved vulnerabilities.
There are 0 security hotspots that need review.

questdb Security

Best in #Time Series Database

Average in #Time Series Database

questdb Security

Best in #Time Series Database

Average in #Time Series Database

license License

questdb is licensed under the Apache-2.0 License. This license is Permissive.
Permissive licenses have the least restrictions, and you can use them in most projects.

questdb License

Best in #Time Series Database

Average in #Time Series Database

questdb License

Best in #Time Series Database

Average in #Time Series Database

build Reuse

questdb releases are available to install and integrate.
Deployable package is available in PyPI.
Build file is available. You can build the component from source.
Installation instructions are not available. Examples and code snippets are available.
It has 327483 lines of code, 25136 functions and 2229 files.
It has medium code complexity. Code complexity directly impacts maintainability of the code.

questdb Reuse

Best in #Time Series Database

Average in #Time Series Database

questdb Reuse

Best in #Time Series Database

Average in #Time Series Database

Top functions reviewed by kandi - BETA

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.

Returns the initial seed value .
Assemble the parse method invocation .
Generate table query .
assembles a RecordToRowCopier
Publish multiple copy tasks .
Sorts the elements in sorted sequence .
Commit o3 .
Rewrite select clause .
Format time in milliseconds .
Create a function .

questdb Key Features

An open source SQL database designed to process time series data, faster

questdb Examples and Code Snippets

See all related Code Snippets

Try QuestDB

Copy

Download

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

Copy

Download

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

Copy

Download

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()

Copy

Download

table_columns('pos')

or

SHOW COLUMNS FROM 'mytable'

When working with QuestDB, are symbol columns good for performance for huge amounts of rows each?

Copy

Download

|----------------------------------|
| 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?

Copy

Download

# 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

Download

# 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?

Copy

Download

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?

Copy

Download

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?

Copy

Download

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?

Copy

Download

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

See all related Code Snippets

Community Discussions

Trending Discussions on questdb

What is a equivalent of a merge query(insert/update/delete) is present in the QuestDB?
How do I aggregate open and close values in QuestDB using SAMPLE BY
How to generate a monthly report filling missing days of the month with zero
Quest DB failed to run tutorial data insertion using Java with line protocol
QuestDB : difference between table_columns() and tables()
Did the Quesdb performance change with the implementation of the new geohash type?
QuestDB invalid metadata version
Is there an NPM package for QuestDB?
When working with QuestDB, are symbol columns good for performance for huge amounts of rows each?
How can I store MQTT data into QuestDB?

Trending Discussions on questdb

QUESTION

What is a equivalent of a merge query(insert/update/delete) is present in the QuestDB?

Asked 2022-Feb-11 at 09:52

What 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:52

QuestDB (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.

Source https://stackoverflow.com/questions/71077841

QUESTION

How do I aggregate open and close values in QuestDB using SAMPLE BY

Asked 2022-Jan-05 at 00:11

I'm working on a price data project, trying to store OHLC 1 minute data and then sampling it over various intervals. I decided to try QuestDB for its neat time-series features such as SAMPLE BY but am running into a wall.

I want to use SAMPLE BY to query my data, which looks like this:

timestamp	open	high	low	close
"2021-09-10T19:43:21.657672Z"	2.0	4.0	1.0	3.0
"2021-09-15T06:12:42.267416Z"	3.0	6.0	3.0	6.0

For example, for the two rows above, which are in the same month, I would like to run SAMPLE BY 1M and aggregate the values for the month.

For high and low values the aggregate functions are min(low) and max(high), but I can't figure out what to do for open and close values.

I tried retrofitting this approach but got as far as the following without errors:

with ohlc as ( 
    select row_number() over (
        partition by cast(cast(timestamp as float) / 1000000 / 60 / 60 / 24 / 7 / 4.34524 as int)
        order by timestamp
    ), open, high, low, close, timestamp
    from eurusd
) 
select max(high) high,
    min(low) low,
    min(case when rn_asc = 1 then [open] end) as open
from ohlc

Some errors I encountered:

Adding the second row_number() over [...] order by timestamp desc fails with a syntax error. If I remove the desc it works, so I just removed that column altogether since then it's the same as the first.
Neither SAMPLE BY nor GROUP BY at the end works with "base query does not provide dedicated TIMESTAMP column" and "group by expression does not match anything select in statement" errors, respectively.
QuestDB recognizes the CASE WHEN THEN but if I yield a column name in the THEN portion it doesn't work with no error message.

I'm currently stuck at this point having looked across the QuestDB tutorials, SO questions, and GitHub issues for a mention of this because it seems like a common operation, especially since they have written many price time-series data tutorials, but none address this.

I'm wondering if anyone has encountered this and has a solution? Thank you!

ANSWER

Answered 2022-Jan-05 at 00:11

It will be first() and last() to get open and close.

select max(high) high,
    min(low) low,
    first(open) as open,
    last(close) as close
from ohlc
SAMPLE BY 1M

Source https://stackoverflow.com/questions/70586302

QUESTION

When working with QuestDB, are symbol columns good for performance for huge amounts of rows each?

Asked 2021-Sep-22 at 15:55

When working with regular SQL databases, indexes are useful for fetching a few rows, but not so useful when you are fetching a large amount of data from a table. For example, imagine you have a table with stock valuations of 10 stocks over time:

|------+--------+-------+
| time |  stock | value |
|------+--------+-------+
|  ... | stock1 | ...   |
|  ... | stock2 | ...   |
|  ... | ...    | ...   |
|------+--------+-------+

As far as I can tell, indexing it by stock (even with an enum/int/foreign key) is usually not very useful in a database like Postgres if you want to get data over a large period of time. You end up with an index spanning a large part of the table, and it ends up being faster for the database to do a sequential scan, for example, to get the average value over the whole dataset for each stock:

SELECT stock, avg(value) FROM stock_values GROUP BY stock

Given that QuestDB is row oriented, I would guess that it would result in better performance to have a separated column for each stock.

So, what schema is recommended in QuestDB for a situation like this? One column for each stock, or would a symbol column for each stock symbol be as good (or good enough) even if there are millions of results for each row?

ANSWER

Answered 2021-Sep-22 at 15:55

A column per stock is not easy to achieve in QuestDB. If you create table like this

|----------------------------------|
| time |  stock1 | stock1 | stock3 |
|----------------------------------|

Then you'll have to insert all values together in one row or you end up with gaps

|----------------------------------|
| time |  stock1 | stock1 | stock3 |
|----------------------------------|
| t1   |     1.1 |        |        |
| t2   |         |   3.45 |        |
| t3   |         |        | 103.45 |
|----------------------------------|

Even for t1 == t2 == t3 when you do the insert as 3 operation it will still result in 3 rows.

So symbols are a better choice here.

Symbol can be indexed and not indexed and you may have benefits of non-indexed symbols when distinct number of them is low. Reading full table vs reading by index is the matter of index selectivity, not data range. If the selectivity is high (e.g. distinct symbol count is say 10k) fetching by index is faster than range scans.

Source https://stackoverflow.com/questions/69261459

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install questdb

You can install using 'pip install questdb' or download it from GitHub, PyPI.
You can use questdb like any standard Java library. Please include the the jar files in your classpath. You can also use any IDE and you can run and debug the questdb component as you would do with any other Java program. Best practice is to use a build tool that supports dependency management such as Maven or Gradle. For Maven installation, please refer maven.apache.org. For Gradle installation, please refer gradle.org .

Support

Community Slack: join technical discussions, ask questions, and meet other users!GitHub issues: report bugs or issues with QuestDB.GitHub discussions: propose new features or show what you've built.Stack Overflow: look for common troubleshooting solutions.

DOWNLOAD this Library from

Reuse Trending Solutions

14 best Python Telegram Bot

21 best C# Game Development

Build AI Fake News Detector

Build Text Summarizer in Python

Getting Started with Web3

24 best Python Blockchain

26 best Python Face Recognition

Build Ludo Game in Python

Build Location Based AR App

Build AI Study Assistant Bot

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items

Find more libraries

Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Explore Related Topics

Time Series Database

Share this Page

Reuse Pre-built Kits with questdb

12 best Java Database

13 best Java SQL Database

7 best Java Time Series Database

See all related Kits

Reuse Time Series Database Kits

7 best Java Time Series Database

8 best JavaScript Time Series Database

9 best Python Time Series Database

5 best Ruby Time Series Database

6 best C# Time Series Database

See all related Kits

Reuse Database Kits

Read-Easy BuildWithAi

Read-Easy BuildWithAI

21 best PHP DB Operations

Cancer Prediction Using Data Mining

Hangman game

See all related Kits

Consider Popular Time Series Database Libraries

prometheus

by prometheus

prophet

by facebook

timescaledb

by timescale

graphite-web

by graphite-project

questdb

by questdb

See all Time Series Database Libraries

Try Top Libraries by questdb

questdb.io

by questdb TypeScript

c-questdb-client

by questdb C++

questdb-packer-ami

by questdb Shell

questdb-slack-grafana-alerts

by questdb Python

grafana-datasource

by questdb TypeScript

See all Libraries by this author

Compare Time Series Database Libraries with Highest Support

prometheus

by prometheus

prophet

by facebook

timescaledb

by timescale

kairosdb

by kairosdb

stumpy

by TDAmeritrade

See all Time Series Database Libraries

Compare Time Series Database Libraries with Highest Quality

scikits.timeseries-sandbox

by pierregm

Rhombus

by pardot

FaultMap

by SimonStreicher

VMTKSlicerExtension

by haehn

constraint-layout-demos

by AndroidStudyGroup

See all Time Series Database Libraries

Compare Time Series Database Libraries with Highest Security

hawkular-metrics

by hawkular

Rhombus

by pardot

spectator

by Netflix

krati

by jingwei

series-reducer

by LukaszWiktor

See all Time Series Database Libraries

Compare Time Series Database Libraries with Permissive License

Rhombus

by pardot

series-reducer

by LukaszWiktor

krati

by jingwei

hawkular-metrics

by hawkular

kairosdb

by kairosdb

See all Time Series Database Libraries

Compare Time Series Database Libraries with Highest Reuse

redis-traffic-stats

by c1982

demography

by robjhyndman

EMMAgeo

by coffeemuggler

tscompdata

by robjhyndman

series_joiner

by cerebris

See all Time Series Database Libraries

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items

Find more libraries

Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

© 2022 Open Weaver Inc.

© 2022 Open Weaver Inc.