database - Materialize view error: deduplicate key list must include dedicated timestamp - Stack Overflow

admin管理员组

文章数量:1399955

I have a table with trading data, and I want to create a materialized view on top of it. My table has this schema:

CREATE TABLE 'btc_trades3' ( 
    symbol INT,
    side SYMBOL CAPACITY 256 CACHE,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL
WITH maxUncommittedRows=500000, o3MaxLag=5000000us
DEDUP UPSERT KEYS(symbol,timestamp);

And stores a few hundreds record per second, for a few dozens of different symbols. I am trying to build a view like this

create materialized view price_lag AS (
WITH price_and_prev AS (
  select 
    timestamp, symbol, price, 
    lag(price) over (partition by symbol order by timestamp) as pprice
  from btc_trades3
) 
SELECT timestamp, symbol, avg(price) as price, avg(price-pprice) as pprice
FROM price_and_prev
SAMPLE BY 5m
ORDER by symbol, timestamp
 ) partition by day;

But I get deduplicate key list must include dedicated timestamp column. I have some other views without any issues and with no window functions, so I tried removing the window function to test if this was the issue, but same error. I cannot figure out what I am doing wrong.

I have a table with trading data, and I want to create a materialized view on top of it. My table has this schema:

CREATE TABLE 'btc_trades3' ( 
    symbol INT,
    side SYMBOL CAPACITY 256 CACHE,
    price DOUBLE,
    amount DOUBLE,
    timestamp TIMESTAMP
) timestamp(timestamp) PARTITION BY DAY WAL
WITH maxUncommittedRows=500000, o3MaxLag=5000000us
DEDUP UPSERT KEYS(symbol,timestamp);

And stores a few hundreds record per second, for a few dozens of different symbols. I am trying to build a view like this

create materialized view price_lag AS (
WITH price_and_prev AS (
  select 
    timestamp, symbol, price, 
    lag(price) over (partition by symbol order by timestamp) as pprice
  from btc_trades3
) 
SELECT timestamp, symbol, avg(price) as price, avg(price-pprice) as pprice
FROM price_and_prev
SAMPLE BY 5m
ORDER by symbol, timestamp
 ) partition by day;

But I get deduplicate key list must include dedicated timestamp column. I have some other views without any issues and with no window functions, so I tried removing the window function to test if this was the issue, but same error. I cannot figure out what I am doing wrong.

• database
• time-series
• questdb

Share Improve this question Follow edited Mar 26 at 8:49 Javier Ramirez asked Mar 25 at 18:02 Javier RamirezJavier Ramirez 4,08511 gold badge2727 silver badges3636 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

[Edited on March 26th with extra context]

Materialized views in QuestDB are auto-incremental. To achieve this, QuestDB relays on the view outputting data via SAMPLE BY and as with regular SAMPLE BY expects the output of the query to be sorted by incremental designated timestamp.

The query above is forcing an ORDER BY symbol first, and timestamp later, which causes the data to be unsorted by designated timestamp, so the view complains. Moreover, since this is a materialized view, sorting the data in the query does not make sense, as data will be stored by incremental timestamp, so just avoid any sorting after the SAMPLE BY and order, if needed, whenever running a SELECT against this view

create materialized view price_lag AS (
WITH price_and_prev AS (
select timestamp, symbol, price, 
lag(price) over (partition by symbol order by timestamp )  as pprice
from btc_trades3
) SELECT timestamp, symbol, avg(price) as price, avg(price-pprice) as pprice
FROM price_and_prev
SAMPLE BY 5m
 ) partition by day;

However, an incremental refresh is non deterministic here, as incremental refreshes in QuestDB materialized views work with small slices of the data around the inserted data, which means it is possible to have a previous row in the table, but it might be skipped by the lag function in some cases.

This means the output of (price-pprice) could return null (In QuestDB, numeric minus null is null) for some rows even if there are matching rows in the table, as those rows might be outside the scope of the data being in consideration for the incremental refresh. If you are using the view for statistic purposes, it might still be good enough, but if you are expecting exact calculations, this would not be a good strategy.

本文标签： databaseMaterialize view error deduplicate key list must include dedicated timestampStack Overflow