By Wang Tantan (Si Ming)
The tracking management system is widely used in daily life, such as take-out delivery tracking, express logistics flows, and vehicle positioning tracking. This scenario is similar to geographic location management. The core points and bottlenecks are in the storage performance and query capability of the database. At the same time, the time fields need to be arranged in a forward order to ensure the tracking point order. On the one hand, the storage service must support low-latency storage and reading of massive data. On the other hand, the storage service must also provide efficient multi-dimensional data retrieval and sorting. Table Store is still competent for tracking management scenarios, and is fully capable of implementing the tracking management system.
Let’s take a look at a sample of the Table Store-based management system for motorcycles at the scale of 100 million.
Due to safety considerations, motorcycles are restricted to certain areas in a city. A motorcycle rental company, in order to better manage the violation of regulations of motorcycles under its jurisdiction, are to install a positioning system for motorcycles under its jurisdiction and collect motorcycle positions regularly. Motorcycle rental companies can use the tracking management platform to query and collect violations. It can also be used as a basis to remind rental users who violate the rules that they will be blacklisted if they violate the rules too many times;
Query scenario: Query the driving tracking and violation of motorcycle id00001 on November 1, 2018;
Note: This sample provides tracking data at the scale of 100 million entries. Official console address: Project Sample
The sample is integrated in the Table Store console. You can log on to the console to experiment with the system. (If you are a new Table Store user, you need to click Activate Now for a trial of this service. The service activation is free. Order data is stored in public instances. A trial doesn’t consume storage, network traffic, or CUs.)
Table Store is used to easily build a management system for motorcycles at the scale of 100 million. SearchIndex function provides geo query and multi-dimensional query capabilities to obtain the tracking of tracked device by sorting time. At the same time, you can create indexes at any time and then complete automatic synchronization, without worrying about inventory data issues.
Table Store is a fully-hosted and distributed NoSQL data storage service from Alibaba Cloud that provides features such as storage of massive amounts of data, automatic sharding of hot data, and multi-dimensional retrieval of massive amounts of data. Table Store can effectively solve the challenge of large volumes of geo data.
SearchIndex function provides multi-dimensional data search, sorting and other capabilities on the basis of ensuring high availability of user data. You can create multiple indexes for multiple scenarios to achieve retrieval in multiple modes. You can create and activate indexes as needed. Table Store ensures the consistency of data synchronization, greatly reducing the work required for your solution design, service maintenance, and code development.
Preparation for Building
If you are satisfied with the management system for motorcycles at the scale of 100 million implemented based on Table Store and want to start to build your own system, simply follow these steps:
(1) Activate Table Store
Activate the Table Store service in the console. Table Store is out-of-the-box (post-paid) and billed on a pay-as-you-go basis. Table Store also provides a free quota that is sufficient for functional tests. For more information, visit Table Store Console and Free Quota Description.
(2) Create an Instance
Create a Table Store instance in the console and select a region that supports SearchIndex. (Currently the SearchIndex feature has not been commercialized and is supported in the following regions:Beijing, Shanghai, Hangzhou, and Shenzhen. This feature will be gradually available in other regions.)
After the instance is created, open a ticket to apply for the SearchIndex beta test invitation. (After becoming commercialized, SearchIndex will be enabled by default. No fees will be incurred if the feature is not used.)
- Beta test invitation request: Open a ticket, select”Table Store” > “Product Features and Characteristics” > “Create a Ticket”. The application content is as follows:
- Question description: Please enter “Apply for SearchIndex beta test invitation”
- Confidential information: Please enter region + Instance name, for example, Shanghai + myInstanceName
(3) Download SDKs
Use SDKs with SearchIndex (see the official website for more details). Currently, new functions are added for Java, Go, and Node.js SDKs.
$ go get github.com/aliyun/aliyun-tablestore-go-sdk
$ npm install firstname.lastname@example.org
(4) Design a Table
The shop search system sample only uses one shop table that mainly includes fields such as shop type, shop name, shop location, average shop ratings, and per capita consumption. The table is designed as follows:
Table name: geo_track
Start Building (Core Code)
(1) Create a Data Table
Users only need to create a motorcycle track table under the instance that has completed the test: Create and manage data tables on the console (they can also directly create data tables using the SDK). Other tables, such as the rental user table and the motorcycle information table, are created as needed. Only the track table is displayed here, and the table name is geo_track.
(2) Create a Data Table Index
Table Store automatically synchronizes full and incremental index data: Users can create and manage indexes through the console (or, they can also create indexes using the SDK)
(3) Data Import
Insert some test data (108 million entries of data — 10,000 motorcycles 70 days 24 hours * 6 ten-minute points — are inserted into the console sample. Users can insert a small amount of test data on the console);
Table name: geo_track
Motorcycle IDTrack point MD5 (mId + timestamp) (primary key)TimeShop locationid00001f50d55bec347253c24dc9144dff3e3b7154110360000030.30094,120.01278
Table name: moto_user
Motorcycle ID (primary key)Motorcycle colorMotorcycle brandMotorcycle rental userid00001Silver grayH-brand motorcycleYang Liu
(4) Data Reading
Data reading falls into two types:
1. Primary key reading (motorcycle information query)
The primary key column is obtained based on the native Table Store: getRow, getRange, batchGetRow. Primary key reading is used for index (automatic) reverse lookup. Users can also provide a single query page for the primary key (Motorcycle ID), and the query speed is extremely fast. Multi-dimensional retrieval is not supported for the single primary key query;
2. Index reading (track information query)
Query based on the new SearchIndex function: the search interface. Users can freely design multi-dimensional combination queries for index fields. By setting and selecting different query parameters, different query criteria and different sorting methods are built. Currently, exact query, range query, prefix query, match query, wildcard query, phrase match query, and word breaking string query are supported, and they are combined by boolean AND and OR.
For example, the query criteria for the driving track and violation of motorcycle id00001 on November 1, 2018 are as follows;
List<Query> mustQueries = new ArrayList<Query>();
List<String> polygonList = Arrays.asList(//Geo-fencing, no motorcycle area
String mId = "id00001";
Long timeStart = [2018-11-01timestamp];
Long timeEnd = [2018-11-02timestamp];GeoPolygonQuery geoPolygonQuery = new GeoPolygonQuery();
mustQueries.add(geoPolygonQuery);TermQuery termQuery = new TermQuery();
mustQueries.add(termQuery);RangeQuery rangeQuery = new RangeQuery();
mustQueries.add(rangeQuery);BoolQuery boolQuery = new BoolQuery();