Elasticsearch as a primary DB and other questions

[brolly759 (Steve)]

Fairly new to ES and Kibana. I started migrating some of our IoT Sensor data to ES. Currently 2.5 weeks of data is 17mil records. Currently we use SQL and I am looking at using ES as a primary DB. Here are my questions:

-Can / should I use ES as a primary DB? I found mixed answers online.
-If I shouldn't use it as a primary DB, what would be a good backup / alternative repo.
-Can I run a job and store aggregate values or do you recommend I just query aggregate values in real time?
-Fairly static data like device id's, hardware /firmware versions, lists of assignments to where IoT devices are located, etc, should that be stored in ES or should I use a relationship DB like SQL? If its possible, making ES a "one stop shop" would simplify what I need to manage.
-What kind of security is on ES / Kibana

Thanks

[warkolm (Mark Walkom)]

You might look at this as nit picking, but words matter. Elasticsearch is not a database, it's a datastore. It doesn't have things like roll back

Should you use Elasticsearch as a primary datastore? That's really down to the use case and your aversion to risk. We have a huge number of users that do this for logs and metrics without concerns.

We usually see users inserting the raw data into Elasticsearch, and then running aggregations when they want answers. Then, for longer term storage, you can use things like the rollup APIs.

You can't do joins in Elasticsearch and we usually suggest flattening everything. So each event would contain all the device info in it. You can put that info into a different index, and then merge the data at query time, but that would be outside the scope of the standard stack (aka you'd DIY).

Security wise, check out https://www.elastic.co/products/stack/security

[brolly759 (Steve)]

I would definitely flatten things out as much as I can but there are some things like UI drop down menu choices or reference data like if I said "2" = "temperature". I could use some type of table storage for that or figure out a way to have that in JSON format.

No worries on the nit picking I have heard about cold / hot storage and one option is storing the messages in raw/blob/azure tables in cold storage and using ES for everything else. choices choices....