You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@horaedb.apache.org by "grenade (via GitHub)" <gi...@apache.org> on 2024/11/20 15:38:08 UTC
[I] support either nanosecond timestamp precision or composite primary keys [horaedb]
grenade opened a new issue, #1597:
URL: https://github.com/apache/horaedb/issues/1597
### Describe This Problem
my use case is trade data analytics for signal or strategy indicators. a trade has:
- timestamp: the time (in nanosecond precision) of the execution of the trade
- base: the tag/symbol of the commodity or security that was bought or sold
- quote: the tag/symbol of the currency, commodity or security that the price of `base` is given in
- exchange: the tag/symbol of the exchange where the trade was executed
- id: the (uint64) id used by the exchange to uniquely identify the trade. due to the volume of trades, most exchanges use a composite key of (id, base, quote), so there are always collisions if base, quote and exchange are not considered together
- price: the price (in `quote`) for 1 unit of `base`
- size: the quantity of `base` in the trade. a positive `size` indicates a buy. a negative `size` indicates a sell
i am struggling to document a table definition in horaedb that does not lose trades. since there are many trades which occur within the same millisecond. nanosecond precision appears to be unavailable.
i have attempted to work around the millisecond precision limitation by using a composite primary key (time, exchange, quote, id). each base gets it's own table which allows for multi-exchange series and combining quotes where their underlying values are equivalent (ie: usdt == usdc). however it appears that horaedb accepts the definition but ignores any value in the composite key that is not the timestamp. this results in all but the first trade in a given millisecond being discarded as a duplicate.
### Proposal
i would appreciate ideas about how to utilise nanosecond timestamp precision or how to correctly define a composite key that will actually work.
### Additional Context
here's the table definition i have tried:
```sql
CREATE TABLE IF NOT EXISTS {base} (
time timestamp NOT NULL,
id uint64,
exchange string,
quote string,
price double,
size double,
TIMESTAMP KEY(time),
PRIMARY KEY(time, id, exchange, quote)
) ENGINE=Analytic with (enable_ttl='false')
```
here's an example of source data where `base` is btc, `quote` is usd and `exchange` is coinbase:
https://api.exchange.coinbase.com/products/BTC-USD/trades?limit=10&after=1000
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@horaedb.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@horaedb.apache.org
For additional commands, e-mail: commits-help@horaedb.apache.org
Re: [I] support either nanosecond timestamp precision or composite primary keys [horaedb]
Posted by "jiacai2050 (via GitHub)" <gi...@apache.org>.
jiacai2050 commented on issue #1597:
URL: https://github.com/apache/horaedb/issues/1597#issuecomment-2491040997
Hi, nanoseconds is not supported in horaedb now.
For your case, I think you could add another column to bypass this limit,
``` sql
CREATE TABLE IF NOT EXISTS {base} (
time timestamp NOT NULL,
time2 uint64 NOT NULL,
id uint64,
exchange string,
quote string,
price double,
size double,
TIMESTAMP KEY(time),
PRIMARY KEY(time, time2, id, exchange, quote)
) ENGINE=Analytic with (enable_ttl='false')
```
Here we add another column `time2` to primary keys, and it's defined as `time(nanoseconds) % 1e6`, so rows with same `time` value won't be overwritten.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@horaedb.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@horaedb.apache.org
For additional commands, e-mail: commits-help@horaedb.apache.org