I’m new to using Crate and currently POC testing to use with IoT data . I come from a relational DB background so I know I need to change my thinking a bit.
I’m having issues with table creation, in that I’m not sure what the best approach might be to the schema.
There are 6700(<>) devices each being 1 of 11 device types (between 24 and 146 parameters for each type).
The parameters are 90% double precision with a deviceid and timestamp field.
The input data is in a custom binary format and comes in once every minute, so all the parameters for the deviceid arrive at once, which I am then translating to insert into CrateDB.
At the moment I am creating 7 tables for the device types with fields for all the parameters . I did try creating a table for each deviceid but it seemed to hit a limit that CrateDB did not like.
1: Is it OK to create these “Tall” tables or would it be better to split out the individual parameters so you have deviceid, timestamp, field, value type schema and have far more data.
2: Could Cratedb be configured to allow for having 6700+ deviceid tables, or is it a stability/reliability issue ?
3: Regarding primary keys, does the order of the fields matter ?
I hope this makes some sense and any help would be appreciated, even if pointing to any videos/articles on the subject.
I’m using a local CrateDB 3-node Docker setup and its running quite slow for only querying 1440 rows (with interpolation for missing minutes) from 141,000 records - 63 seconds !!