Importing from ILP into CrateDB: Table vs. timeseries format?

[amotl]

@amotl
I have another query related to importing from ILP file.

Here is the content of my ILP file that I have imported from InfluxDB2 and then split into respective measurment using a python script>

position,machine=SN6H1645 gpsaltitude=862.8 1710494667494000000
position,machine=SN6H1645 gpscourse=353 1710494663917000000
position,machine=SN6H1645 gpscourse=67.76 1710494664425000000
position,machine=SN6H1645 gpscourse=359.39 1710494664688000000
position,machine=SN6H1645 gpscourse=328.3 1710494664898000000
position,machine=SN6H1645 gpscourse=45.95 1710494664998000000
position,machine=SN6H1645 gpscourse=347.4 1710494665415000000
position,machine=SN6H1645 gpscourse=110.67 1710494665437000000

The data is a timeseries in InfluxDB 2, but when I have imported it into CrateDB it seems to be a simple table only.

Is there any flag that will import data in timeseries format during ctk load table command, or do I need to run a query once it's loaded to convert the data to timeseries?

Thanks,
Zill

Originally posted by @ZillKhan in #183

[amotl]

Hi @ZillKhan. I might understand what you are referring to here, but I am not sure how I can help you, because the measurement records you are referring to do have their own unique timestamp, which will be stored in CrateDB into a dedicated column, with all the measurement values and other columns next to it, like in a regular RDBMS.

If the outcome is that the table stored in CrateDB is pretty sparse, because "other sensors" did not yield values for this particular timestamp, I think there is not much CrateDB could do about it at this point, right?

Do I need to run a query once it's loaded to convert the data to timeseries?

If you are looking into rollup or downsampling procedures, or want to fill up the gaps: CrateDB provides relevant functions for that, but data would need to be loaded first, as you are suggesting. Afterwards, the full capacity of CrateDB's SQL can be used to transform the data, if that fits your bill?

If you want to automate this specific process, or put it on rails, dbt could be the right tool to look at, favoring the ELT (first-load-then-transform) approach.

Is there any flag that will import data in timeseries format during ctk load table command.

We will also be happy to implement corresponding transformation routines into influxio itself, if you could provide relevant guidance about how to do it, or about what you would expect?

[amotl]

Do I need to run a query once it's loaded to convert the data to timeseries?

If you are looking into rollup, downsampling, or other aggregation procedures, or if you want to fill up the gaps: Yes, CrateDB provides relevant functions for that to work with timeseries data.

You can have a look at the documentation page about Advanced Querying to possibly get inspired about gap filling, interpolation, or time bucketing. Maybe this is what you have been looking for in this context?

[ZillKhan]

@amotl Thank you for your valuable input.
I will look into this further and let you know if I require any other information.

Thanks,
Zill

[amotl]

Hi Zill,

first of all, I'd like to wish you a happy new year. Do you have any further questions on this?

Maybe @hammerhead can quickly grok your question and come up with an on-the-spot answer about which functions you may want to use in your situation to get into the DWIM zone.

Otherweise, feel free to ask any more specific questions, or alternatively let's close the issue if you found the answer already?

With kind regards,
Andreas.

NB: In any way, I think it would be good to have a concise and canonical documentation section about this topic on the documentation page about InfluxDB » CrateDB, to educate how users optimally transition from InfluxDB's timeseries format-style of storage and querying into CrateDB's traditional RDBMS-style storage and querying. We could use your outcome for that, or otherwise @hammerhead could add a few cents.

[hammerhead]

Hi Zill,

Time-series data is modelled in CrateDB as standard relational tables. CrateDB's analytical SQL functions and other analytical capabilities are automatically available for all tables. This is mainly due to the fact that CrateDB automatically enables columnar indexing for all tables and columns without manual configuration.

A good starting point in the documentation that elaborates on typical table designs and query capabilities is Time series data. In addition, I recommend to think about sharding and partitioning rather early. Time-series data has the characteristics to quickly grow to a considerable size, and sharding/partitioning are key aspects to ensure stable query performance over time as data grows.

Best
Niklas

[ZillKhan]

Thanks @hammerhead and @amotl for you feedback.
I will work on this further.
BR,
Zill