I built the structure on cassandra DB to store the time series data of the OS data like services, process and other information. To understand how to works Cassandra about storing JSON data and retrieval the data by CQL queries with condition I prefered to simplify the model. Because in the total model DB I'll have the TYPE more complex than report_object like hashMap of array of hashMap for example:
Type NETSTAT--> Object[n] --> {host:192.168.0.23, protocol: TCP ,LocalAddress : 0.0.0.0}
so the Type NETSTAT will have a list of hashMaps that will contain the fields key -> value.
For simplify I have choosen to show the following schema:
CREATE TYPE report_object (RTIME varchar, RMINORVER int, RUSER varchar, RLANG varchar, RSCRIPT varchar, RMAJORVER int, RHOST varchar, RPATH varchar);
CREATE TABLE test (
REPORTUUID uuid PRIMARY KEY,
report frozen<report_object>);
Inside the table I injectioned the JSON data with the followed query inside java class:
INSERT INTO test JSON '{"REPORTUUID": "9fb21fb9-333e-4017-ab77-0fa6ee1e20e3" ,"REPORT":{"RTIME":"6/MAR/2016 6:0:0 PM","RMINORVER":0,"RUSER":"Administrator","RLANG":"vbs","RSCRIPT":"Main","RMAJORVER":5,"RHOST":"WIN-SAPV9MUEMNS","RPATH":"C:\\Users\\ADMINI~1\\AppData\\Local\\Temp\\IXP000.TMP"}}';
I inectioned other data with the query above. The questions to clarify my concepts are: - I would like to do the queries with conditions that check inside TYPE defined, is it possible with CQL or is necessary to use spark SQL?
- Is design DB model right for the purpose (Because I have passed from RDBMS to DB NoSQL) ?