What's the best way to upload an ILP file into QuestDB?

Community
15

Hi @bob3150 ,

Certainly seems to be an issue with sparse data. There are two routes to solve this.

  1. Reingest using Telegraf to transform the data into a dense format. @javier could help with this another time.

  2. Transform and insert it into a new table in QuestDB.

For this second route, it looks like your data is grouped into 1s intervals. It also looks like you have only one valid entry for each column per timestamp.

Therefore, we should be able to resolve this with a sample by. Simply create a new table with the same schema, then try a query such as:

INSERT INTO new_table_name
SELECT first(Endlager), timestamp, first(Gassack), first(Kellerwasser), first(Mischerwaage), first(Nachfermenter), first("Brunnen Endlager"), first(Zisterne), first(Dieseltank), first(Fermenter), first(Sickergrube), first("Ölgrube lang"), first("Ölgrube rund")
FROM Füllstände
SAMPLE BY 1s

Essentially, select the first value for each of your columns for each 1s period, and then insert that as a single row into the new table.

For reference, the reason QuestDB is taking extra space is because QuestDB stores null values as sentinels. So a null value takes the same space as a normal value. Since you have sparse rows, you are using many times more storage than normal because you all the nulls are being stored.