0

We are building a micro service based IOT platform with a dashboard and views of various sensors etc. I have seen a few data modelling videos but I am not able to arrive at a good data model in cassandra. I need help regarding the same.

So, the usecase is as follows:

We are rendering personalized dashboards per user per organization. For example sake, consider the iot platform is being built for a warehouse monitoring solution. A warehouse can have a bunch of equipments and each equipment is monitored by a bunch of devices. So, my hierarchy looks as follows:

organization >> has Many warehouses >> has many equipments >> has many devices. An organization has several users / employees each at different levels say warehouse manager, ceo, zonal manager etc

So the dashboard has a bunch of stats that have to be displayed based on the user that logs in . These stats are

1) Total number of warehouses that the user can see (say 500000 for a ceo and 50 for a zonal manager)

2) Total number of equipments which are available in those warehouses (say 10000000 for a ceo and 300 for a zonal manager)

3) The count for the equipment status for the 10000000 equipments ( say POWERED_ON (50000) , POWERED_OFF (50000), MAINTAINENECE(50000), DISMANTLED (50000) etc for a CEO and POWERED_ON (100) , POWERED_OFF (100), MAINTAINENECE(50), DISMANTLED (50))

4) The total number of alerts generated for the equipments and devices. (say 15000 equipments are in CRITICAL state and have to be fixed immediately for CEO and 15 equipments are in CRITICAL state for a zonal manager)

For the warehouse specific data , the model that I have come up with is as follows:

//Org >> to user >> to warehouse >> to equipment mapping
      @PartitionKey(0)
      private String organization;
      @PartitionKey(1)
      private String userId;
      @ClusteringColumn(0)
      private UUID warehouseUuid;
      @ClusteringColumn(1)
      private UUID equipmentUuid;
      @ClusteringColumn(2)
      private LocalDateTime timeOfEquipmentStatusUpdate;
      private EquipmentState currentEquipmentState;


//Equipment state Audit information
      @PartitionKey(0)
      private UUID warehouseUuid;
      @PartitionKey(1)
      private UUID equipmentUuid;
      @ClusteringColumn(0)
      private LocalDateTime timeOfEquipmentStatusUpdate;
      private EquipmentState currentEquipmentState;

The equipment state will keep getting updated as and when technicians are fixing it. This will run an update query on the first table in a background job for updating Equipment state information.

Since the dashboard only deals with counts , I was thinking, a single query to fetch a single row for a user and then compute the number warehouses and equipments in my application.

I am however struggling more with alerts, as when alerts are raised, all users who can view the site / equipment should see the alert.I dont think I can make AlertType (CRITICAL / INFO/ WARNING) as a partition key.

The sql query may look something like

select count(alertType) from <table> where userid = 'userId' and orgId ='orgId' and warehouse in ('1','2','3' ...n) group by alertType;

One of the models that I have come up with for alerts is along the lines of

//Alert audit information
  @PartitionKey(0)
  private UUID warehouseUuid;
 @PartitionKey(1)
  private String equipmentUuid;
  @ClusteringColumn(0)
  private LocalDateTime eventOccurrenceTime;
  @ClusteringColumn(1)
  private String alertName;
  private AlertType alertType;

I am not sure how to map user in an org to this alert information. Can someone please help me with the data modelling.

Thanks in advance.

2
  • You mean CQL, not SQL. Aug 24 '19 at 19:49
  • Yes.. just wanted to show how the SQL counterpart may look like Aug 24 '19 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.