Internet of things metadata management based on Tablestore

Abstract: # There are two common enterprise-class wireless access schemes for backgrounds, called thin APS and Fat APs, respectively. Thin AP (AC+AP) architecture is a more traditional enterprise-class wireless access scheme, the main advantage is that the roaming experience is good, but the AC outage will cause the AP all can not work. For large office space, roaming demand is relatively weak, new type of fat ap (no AC, not due to AC downtime caused by the network is not available) + Cloud Controller architecture has become a new enterprise wireless access scheme, operation and maintenance personnel through the cloud to monitor and manage APs.

Background
There are two common enterprise-class wireless access schemes, called Thin APS and Fat APs, respectively. Thin AP (AC+AP) architecture is a more traditional enterprise-class wireless access scheme, the main advantage is that the roaming experience is good, but the AC outage will cause the AP all can not work. For large office space, roaming demand is relatively weak, new type of fat ap (no AC, not due to AC downtime caused by the network is not available) + Cloud Controller architecture has become a new enterprise wireless access scheme, operation and maintenance personnel through the cloud to monitor and manage APs.

A company has a wireless AP about 10,000 units, Access terminal (STA) 100,000. The device reports its status to the cloud in a certain period of time, and the cloud will persist monitoring data for users to view.

Business description
Each AP device reports its current status as a 10s cycle, with the format of the escalated data as JSON, in the following format:
AP Status:

{
"Ap_mac": "11:22:33:86:d9:e8",//AP Wan Port MAC address, AP device Unique identification
"Report_time": 1532315501985,//escalation timestamp, MS
"On_time": 1531417181972,//device on-line timestamp, MS
"Sta_cnt": 2,//number of terminals
"Cpu_usage"://CPU Usage
"Memory_usage": $,//CPU utilization
"Wan_recv_speed": 280,//WAN Port downlink rate Unit bps
"Wan_sent_speed": 45348,//WAN port upstream rate unit bps
}
Requirements and architecture selection
Demand
View the latest status of each AP through the MAC address.
Users need to query the device based on various conditions on the management system.
The various indicators of the AP need to be sorted to find the faulty device.
We divide the above requirements into two types:
Multidimensional queries.
Sort.
Based on these two types of requirements, we give the following architecture selection comparison.
Architecture Selection
For the device status monitoring data for this IoT scenario, the following comparisons are made for several common scenarios.

Mysql
The state data reported by the device is written directly to MySQL, and the data is analyzed using MySQL's own query and sort statement, which is the simplest architecture and the user's operation and maintenance cost is lower.

This architecture is only suitable for small-scale data, and in the case of large-scale data, the internal architecture of MySQL also leads to the inability to create a universal index to meet the needs of multidimensional queries. and MySQL Bottom uses the B + number as the storage structure, there will be random write problems, poor write performance.
MySQL must specify the table structure before use, that is, the following new requirements, it is necessary to modify the table structure, in the case of large amounts of data to modify the table structure can easily lead to the lock table caused by a line failure.

MySQL + self-built Elasticsearch
MySQL + Elasticsearch is also a common scenario in the industry due to the weak retrieval capabilities of MySQL. The user writes data to MySQL and uses the Binlog subscription tool (such as canal) to write data asynchronously to the Elasticsearch, as shown in the schema:

Where the canal client needs to be written and deployed by the user itself. Compared to the single MySQL architecture, this solution solves the problem that MySQL is weak in multi-dimensional queries and the ability to sort the specified columns. But there are a lot more problems with it:

Canal and Elasticsearch require users to deploy their own deployment, resulting in a relatively higher operational costs.
The canal client side is responsible for reading the canal transmission over MySQL incremental change data, data consistency is required by the user's own assurance.
Using the Searchindex feature of table storage
Table storage The LSM model used in the underlying storage is a good solution to poor MySQL write performance, especially for IoT, a less read-write scenario.

After the user writes the data to the table storage, the system synchronizes the data asynchronously to the SEARCHINDEX, the data writes the Tablestore to the data to be able to check about milliseconds to the second level delay, the user does not need to concern the operation dimension related question, the data consistency also has the system internal guarantee, achieves out of the box to use.

Conclusion
Based on the above comparison, table storage is more suitable for storing AP state data, and it is easy to complete multidimensional queries and sorting through Searchindex. The overall architecture of a concise system is as follows:

Table structure Design
Table store the first column using the primary key is used to distribute the data to the corresponding partition for load balancing purposes. We know the MAC address of the first 3 bytes for the manufacturer code, that is, if the same manufacturer produced the device MAC address of the first 3 bytes will be the same, if the direct use of MAC address key words may lead to data hotspots, so we recommend to MAC address do MD5 after the first column of the primary key. The best practices for table structure design are described here.

Latest State data
AP Status
Table Name: Wifi_ap_status

Column type column name Type example notes
Primary key column Pk0 String 1b5de627b4a25553baf1f72af9afb96d MD5 (AP_MAC), Ap_mac do MD5
Value column Ap_mac String 11:22:33:44:55:66 ap MAC Address
Report_time Integer? 1537363646533 UTC Timestamp, MS
On_time I?nteger 1537363646533 Ibid.
STA_CNT I?nteger 10 Connected terminal number
Cpu_usage I?nteger CPU Usage
Memory_usage I?nteger 50 Memory utilization
Wan_recv_speed I?nteger 817 receiving data rate, Unit bps
Wan_sent_speed I?nteger 2411 Data rate, Unit bps
code example
The following is an example of an AP status, giving an example of a full-process code.

Initialization
Create Tablestore Client
Syncclient syncclient = new Syncclient (
"$endpoint",
"$accessKeyId",
"$accessKeySecret",
"$instanceName"
);
The Syncclient object is thread safe and can be injected into other objects as a singleton bean if spring is used

Create a Tablestore table
Table creation can be done on the console or through the SDK, if you use the SDK code examples such as the following

Create an AP Status table
Specify Table name
Tablemeta Tablemeta = new Tablemeta ("Wifi_ap_status");
Specify the primary key column, according to the above table structure design, this side only pk0 a primary key column
Tablemeta.addprimarykeycolumn (New Primarykeyschema ("Pk0", primarykeytype.string));
Createtablerequest createtablerequest = new Createtablerequest (Tablemeta, New Tableoptions (-1, 1));
Syncclient.createtable (createtablerequest);
Create Searchindex
As with creating a table, the creation of Searchindex can be done through the console, and if you use the SDK, the example is as follows:

Create AP Status Searchindex
Createsearchindexrequest createsearchindexrequest = new Createsearchindexrequest ();
Createsearchindexrequest.setindexname ("Wifi_ap_status");
Createsearchindexrequest.settablename ("Wifi_ap_status");
IndexSchema IndexSchema = new IndexSchema ();
Indexschema.setindexsetting (New Indexsetting (5));
Indexschema.setfieldschemas (Arrays.aslist (
New FieldSchema ("Ap_mac", Fieldtype.text). Setindex (True),//searchable
New FieldSchema ("Report_time", Fieldtype.long). Setindex (True). Setenablesortandagg (True),//searchable and sortable
New FieldSchema ("sta_cnt", Fieldtype.long). Setindex (True). Setenablesortandagg (True),
New FieldSchema ("Cpu_usage", Fieldtype.long). Setindex (True). Setenablesortandagg (True),
New FieldSchema ("Memory_usage", Fieldtype.long). Setindex (True). Setenablesortandagg (True)
));
Createsearchindexrequest.setindexschema (IndexSchema);
Createsearchindexresponse resp = Syncclient.createsearchindex (createsearchindexrequest);
Data Write
The user simply writes the data using the Write function of the original table store, and the table store automatically imports the data into the searchindex without concern for internal implementations.

Putrowrequest putrowrequest = new Putrowrequest ();
Rowputchange Rowputchange = new Rowputchange ("Wifi_ap_status");

String Apmac = "11:22:33:86:d9:e8";
Compute MD5 with AP Mac to prevent data hotspots, using Apache's Commons-codec library here
String pk0 = Digestutils.md5hex (APMAC);
PrimaryKey pk = new PrimaryKey (New primarykeycolumn[]{
New Primarykeycolumn ("Pk0", Primarykeyvalue.fromstring (Pk0))
});

Rowputchange.setprimarykey (PK);
Rowputchange.addcolumns (New column[]{
New Column ("Ap_mac", Columnvalue.fromstring (Apmac)),
New Column ("Report_time", Columnvalue.fromlong (System.currenttimemillis ())),
New Column ("On_time", Columnvalue.fromlong (System.currenttimemillis ())),
New Column ("Cpu_usage", Columnvalue.fromlong (56)),
New Column ("sta_cnt", Columnvalue.fromlong (4)),
New Column ("Memory_usage", Columnvalue.fromlong (43)),
New Column ("Wan_recv_speed", Columnvalue.fromlong (280)),
New Column ("Wan_sent_speed", Columnvalue.fromlong (45348)),
});

Putrowrequest.setrowchange (Rowputchange);

Syncclient.putrow (putrowrequest);
Data read
There are two types of data reads:
1. Primary key acquisition based on native table storage
2. Based on Searchindex function acquisition
The following examples of these two types of non-modal reading are illustrated separately

Read by primary key
Obtaining AP status from a primary key is directly obtained directly from tables stored in the table. That is, you do not need to pass the Searchindex function when you get the data through the primary key, the code example is as follows:

Getrowrequest getrowrequest = new Getrowrequest ();
String Apmac = "11:22:33:86:d9:e8";
Compute MD5 with AP Mac to prevent data hotspots, using Apache's Commons-codec library here
String pk0 = Digestutils.md5hex (APMAC);
Set Primary key
PrimaryKey pk = new PrimaryKey (New primarykeycolumn[]{
New Primarykeycolumn ("Pk0", Primarykeyvalue.fromstring (Pk0))
});

Singlerowquerycriteria Singlerowquerycriteria = new Singlerowquerycriteria ("Wifi_ap_status", PK);
Singlerowquerycriteria.setmaxversions (1);
Getrowrequest.setrowquerycriteria (Singlerowquerycriteria);

Getrowresponse rowresponse = Syncclient.getrow (getrowrequest);
Row row = Rowresponse.getrow ();
Get primary key columns
PrimaryKey PrimaryKey = Row.getprimarykey ();
For (Primarykeycolumn primaryKeyColumn:primaryKey.getPrimaryKeyColumns ()) {
System.out.println ("Primarykeycolumn: (" + primarykeycolumn.getname () + ":" + primarykeycolumn.getvalue () + ")");
}
Get Value column
For (Column column:row.getColumns ()) {
System.out.println ("Column: (" + column.getname () + ":" + column.getvalue () + ")");
}
Read through the Searchindex function
To facilitate the description, let's describe our scenario in the form of SQL (only for specific requirements, SEARCHINDEX not supported by SQL statements) + code to show examples.

Multidimensional query
If you need a multidimensional query through a non-primary key column, we can use the Syncclient search method, in the example above, we created the Searchindex for the Wifi_ap_status table and specified the index column.
If you want to implement the following SQL:

SELECT
*
From Wifi_ap_status
WHERE ap_mac like '%86:d9:e8% ' and sta_cnt >= 2
In the Java language implementation, the code is as follows

SearchQuery SearchQuery = new SearchQuery ();
Using Boolquery to implement a combined conditional query, this example searches for data ap_mac contains 86:d9:e8 and sta_cnt is greater than or equal to 2
Boolquery query = new Boolquery ();
Use phrase search for fuzzy matching Ap_mac
Matchphrasequery macquery = new Matchphrasequery ();
Macquery.setfieldname ("Ap_mac");
Macquery.settext ("86:d9:e8");
Using the scope query sta_cnt
Rangequery stacntquery = new Rangequery ();
Stacntquery.setfieldname ("sta_cnt");
Stacntquery.setfrom (Columnvalue.fromlong (2), true);
Query.setmustqueries (Arrays.aslist (
Macquery,
Stacntquery
));
Searchquery.setquery (query);

Build a search request
SearchRequest searchrequest = new SearchRequest (
"Wifi_ap_status",//Table storage table name
"Wifi_ap_status",//Searchindex index name
SearchQuery
);
Set the table columns that need to be returned
Searchrequest.columnstoget columnstoget = new Searchrequest.columnstoget ();
Set to return all columns
Columnstoget.setreturnall (TRUE);
Searchrequest.setcolumnstoget (Columnstoget);

Search request
SearchResponse SearchResponse = Syncclient.search (searchrequest);
list<row> rows = Searchresponse.getrows ();

for (Row row:rows) {
PrimaryKey PrimaryKey = Row.getprimarykey ();
For (Primarykeycolumn primaryKeyColumn:primaryKey.getPrimaryKeyColumns ()) {
System.out.println ("Primarykeycolumn: (" + primarykeycolumn.getname () + ":" + primarykeycolumn.getvalue () + ")");
}

for (Column column : row.getColumns()) {    System.out.println("Column:(" + column.getName() + ":" + column.getValue() + ")");}

}
Sort
Sorting is also a common requirement, for example, we need to look at the number of APS that mount the most terminals in a certain condition, as described in SQL statements:

SELECT
*
From Wifi_ap_status
WHERE ap_mac like '%11:22:33% '
ORDER by sta_cnt DESC
If the code is represented as follows:

SearchQuery SearchQuery = new SearchQuery ();
Use phrase search for fuzzy matching Ap_mac
Matchphrasequery macquery = new Matchphrasequery ();
Macquery.setfieldname ("Ap_mac");
Macquery.settext ("11:22:33");
Searchquery.setquery (Macquery);

Sort options, sta_cnt descending
Fieldsort stacntsorter = new Fieldsort ("sta_cnt");
Stacntsorter.setorder (SORTORDER.DESC);

Searchquery.setsort (New Sort (Collections.singletonlist (
Stacntsorter
)));

Build a search request
SearchRequest searchrequest = new SearchRequest (
"Wifi_ap_status",
"Wifi_ap_status",
SearchQuery
);
Set the table columns that need to be returned
Searchrequest.columnstoget columnstoget = new Searchrequest.columnstoget ();
Set to return all columns
Columnstoget.setreturnall (TRUE);
Searchrequest.setcolumnstoget (Columnstoget);

Search request
SearchResponse SearchResponse = Syncclient.search (searchrequest);
list<row> rows = Searchresponse.getrows ();

Original link

This article is the original content of the cloud-Habitat community and cannot be reproduced without permission.

Internet of things metadata management based on Tablestore