background

There are two common enterprise-class wireless access solutions, called lean AP and fat AP respectively. The thin AP (AC+AP) architecture is a traditional enterprise-class wireless access solution. It provides good roaming experience. However, when an AC breaks down, all aps that belong to it cannot work. For large offices, roaming is relatively weak. The new fat AP (without AC, the network will not be unavailable due to AC breakdown) + cloud controller architecture has become an emerging enterprise wireless access solution. O&m personnel can monitor and manage AP through the cloud.

A company has about 10,000 wireless aps and 100,000 access terminals (STAs). The device reports its status to the cloud center periodically, and the cloud center makes the monitoring data persistent for users to view.

Business description

Each AP reports its status every 10 seconds in the following json format: AP status:

{
    "ap_mac": "11:22:33:86:D9:E8", // THE MAC address of the AP WAN port, which uniquely identifies the AP"report_time": 1532315501985, // Report time stamp, ms"on_time": 1531417181972, // device online timestamp, ms"sta_cnt": 2, // Number of terminals"cpu_usage": 12, // CPU usage"memory_usage": 38, // CPU usage"wan_recv_speed": 280, // WAN downstream rate unit BPS"wan_sent_speed": 45348, // WAN port upstream rate unit BPS}Copy the code

Requirements and architecture selection

demand

1. View the latest status of each AP by MAC address.
2. Users need to query devices based on various conditions in the management system.
3. You need to sort various AP indicators to locate faulty devices. We divide the above requirements into two categories:
4. Multidimensional queries.
5. Sorting. Based on these two categories of requirements, we provide the following comparison of architecture selection.

Selection of the architecture

For device status monitoring data in such IOT scenarios, the following compares common solutions.

MySQL

The status data reported by the device is directly written into MySQL and the query and sorting statements of MySQL are used to analyze the data. This architecture is the simplest and has low operation and maintenance costs.

This architecture is only suitable for small-scale data. In the case of large-scale data, the internal architecture of MySQL also makes it impossible to create a universal index to meet the requirements of multidimensional queries. In addition, the bottom layer of MySQL uses B+ number as the storage structure, resulting in random write problems and poor write performance. The table structure must be specified before MySQL is used. That is to say, the table structure must be modified if new requirements are added later. Modifying the table structure in the case of a large amount of data may easily lead to table locking and online faults.

MySQL + create Elasticsearch

MySQL + Elasticsearch is also a common solution in the industry due to MySQL’s weak retrieval capability. The user writes data to MySQL and asynchronously writes data to Elasticsearch using a binlog subscription tool such as Canal.

The Canal Client needs to be written and deployed by users themselves. Compared with single MySQL architecture, this scheme solves the problem that MySQL is weak in multi-dimensional query and sorting of specified columns. But with more questions:

1. Canal and Elasticsearch need to be deployed by users themselves, resulting in higher operation and maintenance costs.
2. The Canal Client side is responsible for reading MySQL incremental change data transmitted by Canal, and the consistency of data needs to be guaranteed by users themselves.

Use the SearchIndex function stored in tables

Table storage LSM model used by the underlying storage, a good solution to the problem of poor MySQL write performance, especially suitable for IOT scenarios such as write more than read.

After the user writes the data to the table storage, the data will be asynchronously synchronized to SearchIndex within the system, and the data will be written to the TableStore for data query with a delay of milliseconds to seconds. The user does not need to pay attention to the problems related to operation and maintenance, and the data consistency is also guaranteed by the system internally, which can be done out of the box.

conclusion

Based on the comparison above, table stores are better suited for storing AP state data, and multidimensional queries and sorting are easy to do with SearchIndex. A concise overall system architecture is shown in the figure below:

Table structure design

The table storage layer uses the first column of the primary key to evenly divide data into corresponding partitions to achieve load balancing. We know that the first three bytes of the MAC address are the manufacturer’s code. That is to say, most of the first three bytes of the MAC address of the device produced by the same manufacturer are the same. If the MAC address is directly used as the primary key, data may be hot. Best practices for table structure design can be found here.

Latest status data

The AP status

The table name: wifi_ap_status

Code sample

The code sample for the entire process is shown below, using the AP state as an example.

Initialize the

Create TableStore client

SyncClient syncClient = new SyncClient(
                    "$endpoint"."$accessKeyId"."$accessKeySecret"."$instanceName"
            );Copy the code

The SyncClient object is thread-safe and can be injected into other objects as a singleton Bean if you use Spring

Create TableStore table

Table creation can be done on the console or through an SDK, which is used in the following code example

Create an AP status table

TableMeta TableMeta = new TableMeta("wifi_ap_status"); / / specified primary key columns, according to the above table structure design, this way 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);Copy the code

Create SearchIndex

Like creating a table, the creation of SearchIndex can be done from the console, if using an SDK, as shown in the following example:

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);Copy the code

Data is written to

Users can simply write data using the write function of the original table store, and the table store will automatically import the data into SearchIndex, regardless of the internal implementation.

PutRowRequest putRowRequest = new PutRowRequest();
RowPutChange rowPutChange = new RowPutChange("wifi_ap_status");

String apMac = "11:22:33:86:D9:E8"; // Use the APACHE Commons - Codec library String pk0 = digestUtils.md5HEX (apMac); // Use the apMac library 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);Copy the code

Data is read

Data access is divided into two types: primary key access based on native table storage 2. Primary key access based on SearchIndex The following illustrates how to access data in two different modes

Read by primary key

The AP state obtained by primary key is obtained directly from the table stored in the table. That is, there is no need to use SearchIndex to retrieve data from the primary key, as shown in the following code:

GetRowRequest getRowRequest = new GetRowRequest();
String apMac = "11:22:33:86:D9:E8"; // Use the APACHE Commons - Codec library String pk0 = digestUtils.md5HEX (apMac); // Use the apMac library String pk0 = digestutils.md5HEX (apMac); 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(); // obtain the PrimaryKey column PrimaryKey PrimaryKey = row.getprimarykey ();for (PrimaryKeyColumn primaryKeyColumn : primaryKey.getPrimaryKeyColumns()) {
    System.out.println("PrimaryKeyColumn:(" + primaryKeyColumn.getName() + ":" + primaryKeyColumn.getValue() + ")"); } // Get the value columnfor (Column column : row.getColumns()) {
    System.out.println("Column:(" + column.getName() + ":" + column.getValue() + ")");
}Copy the code

Read from the SearchIndex function

For the sake of description, the following example describes our scenario in the form of SQL (for specific requirements only, SearchIndex does not currently support SQL statements) + code.

Multidimensional query

If we need multidimensional queries with non-primary key columns, we can use syncClient’s search method. In the above example, we create SearchIndex for the wifi_AP_STATUS table and specify index columns. If you want to implement the following SQL:

SELECT
*
FROM  wifi_ap_status
WHERE ap_mac LIKE '%86:D9:E8%' AND sta_cnt >= 2Copy the code

In the Java language, the code is as follows

SearchQuery searchQuery = new SearchQuery(); // BoolQuery = new BoolQuery(); // BoolQuery = new BoolQuery(); Ap_mac MatchPhraseQuery macQuery = new MatchPhraseQuery(); macQuery.setFieldName("ap_mac");
macQuery.setText("86:D9:E8"); 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); SearchRequest = new SearchRequest("wifi_ap_status"// The table stores the table name"wifi_ap_status", // SearchIndex index searchQuery); / / set the need to return the table column SearchRequest. ColumnsToGet ColumnsToGet = new SearchRequest. ColumnsToGet (); / / set to return all the 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() + ")"); }}Copy the code

The sorting

Sorting is also a common requirement. For example, we need to view the aps with the largest number of mounted terminals under a certain condition, as described in SQL statements as follows:

SELECT
*
FROM  wifi_ap_status
WHERE ap_mac LIKE '% 11:22:33%'
ORDER BY sta_cnt DESCCopy the code

If expressed in code, it would look like this:

SearchQuery searchQuery = new SearchQuery(); Ap_mac MatchPhraseQuery macQuery = new MatchPhraseQuery(); macQuery.setFieldName("ap_mac");
macQuery.setText("11:22:33"); searchQuery.setQuery(macQuery); Sta_cnt descending FieldSort staCntSorter = new FieldSort("sta_cnt"); staCntSorter.setOrder(SortOrder.DESC); searchQuery.setSort(new Sort(Collections.singletonList( staCntSorter ))); SearchRequest = new SearchRequest("wifi_ap_status"."wifi_ap_status", searchQuery ); / / set the need to return the table column SearchRequest. ColumnsToGet ColumnsToGet = new SearchRequest. ColumnsToGet (); / / set to return all the columns columnsToGet. SetReturnAll (true); searchRequest.setColumnsToGet(columnsToGet); // Search request SearchResponse SearchResponse = syncclient.search (searchRequest); List<Row> rows = searchResponse.getRows();Copy the code

Author: hydrogen666