Author | Yaheng Technology – Huang Yong

Guangzhou Yaheng Information Technology Co., Ltd. focuses on providing high-quality Internet overall solutions, and has mature and rich experience in the development of digital governance system. The digital governance system is based on the concept of urban and rural governance. It records and manages the governance process in an information-based manner, aided by big data analysis, intelligent hardware monitoring and drone inspections. Urban and rural governance refers to the process in which the grassroots government divides the network within the administrative region and assigns the responsibility of each grid member to the grid member, who reports, assigns, deals with and tracks the problems found by carrying out inspections.

Encounter TDengine

The first time I heard about TDengine in 2019, I really got in touch with it when I received a link to a live broadcast. Tao Jianhui was broadcasting on Geek Time, and the title was described as follows: a 35-year veteran programmer will tell you how TDengine’s ultra-high performance is achieved. Live, the teacher was sweating, everyone said no air conditioning, the host explained, the teacher now has a high fever, insisted on explaining to you. Sensibility and reason. Later, the shenzhen meeting, the boss time is very busy, catch in the last day to go, did not see teacher Tao, saw our teacher Hou. A chat, a brief encounter.

The scene is introduced

Yaheng has been working on the “grassroots digital grid management system” for nearly two years. This management system can divide towns and streets into grids of large and small size, take buildings and core components in the grids as basic information points, carry out inspection activities such as fire fighting, drug control, sanitation and two violations in the grids, and form work process specifications for uploading and issuing norms. But we have been thinking about whether the architecture of the system is old and how to optimize it. At this time, I happened to know the concept of sequential database through TDengine. Although the system cannot be greatly improved after application, shouldn’t the product be improved in this way?

In our application scenario, when using APP and small program to carry out patrol activities, grid personnel will check the movement distance every 30 seconds, and if it exceeds 20 meters, a coordinate will be uploaded to the server. There are about 100 grid members in one street, and the effective patrol distance is more than 100 kilometers per month, about 1.2 million meters per year; A single street generates about 6, 000 coordinate records a year (Wuhan has about 17, 000 grid workers). We could flip through a patrol line from three years ago. According to the structure design of the original relational database, it is necessary to retrieve coordinate points one by one from the coordinate record table according to the inspection line ID, and then recombine them into a inspection line. The total number of records in three years is close to 20 million. At this time, the query response of relational database is very slow. It is necessary to improve the performance by dividing database into tables according to time, but the subsequent problem is the trouble to deal with the query time of cross-database and cross-table (just think about it and feel tired).

Based on TDengine’s features and usage principles, we migrate coordinate recording data to this:

  1. One grid account and one coordinate record table, in which coordinate records generated by the grid member’s inspection are recorded in chronological order;
  2. According to the time range of the patrol line, the time processing of the sequential database is an inherent advantage, which also reduces the cross-library coupling.
  3. We also make use of the characteristics of low coupling and easy expansion of micro-services to independently create coordinate reading and writing modules. The following is the complete system architecture for your reference, and your criticism and corrections are welcome.

Users directly interact with the application layer, including the web application of the browser, wechat mini program or Android platform APP. Web applications also interact with GIS system to obtain map tiles and coordinate loading and display information. The application layer supports the background service of service operations, including user management, permission management, and problem management. The underlying storage uses MySQL+ TDengine. The business computing background will also interact with the third-party services of other portal data centers to realize various requirements of the entire digital governance system.

The underlying datastore thinking goes like this. Data related to service logic requires a large amount of account information management, associated query, and service subordination processing, which are typical application scenarios of relational databases. Therefore, this data is stored in MySQL. Patrol coordinate data is the massive time sequence location information mentioned above, which involves reading data by time and calculating track by account, etc. It is a typical time sequence structured data storage and analysis scenario, which is very suitable for storing TDengine. Here, after the location data is extracted from the original MySQL, a coordinate calculation microservice is used to save and calculate the coordinate data of patrol. The interaction between the original business operation background and TDengine is realized without a lot of code changes in the original operation background, which is very convenient.

The data model

Follow the design idea of one table for one device. In our system, it is a table for a user, which is used to record all the historical track information of the user. All child tables are created based on a super table named “super”, and the device ID is defined as a tag to distinguish the table.

Rapid development of

Encapsulate the API and use SpringBoot to call JDBC directly to interact with the TDengine data source. In the following example, using SpringBoot+TDengine to store GPS coordinates in real time, the implementation process is very simple.

Step 1: Start TDengine service on Linux.

Step 2 Create a SpringBoot project and configure TDengine connection information in application.properties (ps: default port 0, user name root, password taosdata).

server.port=8085server.servlet.context-path=/api# taostaosdata. Url = JDBC: TAOS: / / 192.168.1.241:0 / db? user=root&password=taosdatataosdata.driverClassName=com.taosdata.jdbc.TSDBDriverCopy the code

Step 3 Configure the dependencies in POM.xml and download the JAR package.

<! -- taos Start --><dependency> <groupId>com.taosdata.jdbc</groupId> <artifactId>taos-jdbcdriver</artifactId> The < version > < / version 1.0.1 > < / dependency > <! -- taos END -->Copy the code

Step 4 Create a config file that corresponds to the URL in application. Properties and driverClassName.

@Component@ConfigurationProperties(prefix = "taosData")public class TaosdataConfig {private String URL; private String driverClassName; public StringgetUrl() {     return url;  }  public void setUrl(String url) {    this.url = url;  }  public String getDriverClassName() {    return driverClassName;  }  public void setDriverClassName(String driverClassName) {    this.driverClassName = driverClassName;  }}Copy the code

The fifth step is to implement the relevant business logic (PS: the same as the traditional relational database, using SQL syntax). Create a library, build a table, insert, query and other operations in Java implementation as follows.

Create database: String sqL1 = "create databaseifNot the exists coor "; stmt.executeUpdate(sql1); String sql2 = "use coor"; stmt.executeUpdate(sql2); String SQL3 = "Create tableifNot exists super (TS timestamp, LNG double, Lat double) tags (ID nchar(32)) "; stmt.executeUpdate(sql3); String SQL4 = "Create tableifNot exists "+ (" u" + userID) + "using super tags('" + userID + ") "; stmt.executeUpdate(sql4); String SQL5 = INSERT into "+ (" u" + userID)+ "VALUES (" + now.getTime()+", "+ LNG +", "+ Lat +") "; stmt.executeUpdate(sql5); Sql6 = new StringBuilder(" select * from u ").appEnd (userid).append("whereTs > = '"), append (stime). Append ("' and ts < = '"), append (etime.) append ("' "); ResultSet resSet = stmt.executeQuery(sql6.toString()); Timestamp ts = null;while(resset.next ()) {ts = resset.gettimestamp (" ts "); LNG = resSet. GetDouble (" LNG "); Lat = resSet. GetDouble (" lat "); // Business processing omitted... }Copy the code

conclusion

By transferring grid patrol location data from relational database MySQL to time sequence database TDengine, Yaheng solves the problems of performance and ease of use in large data volume and long period query, and avoids the trouble of database and table maintenance. TDengine is integrated into the original system by means of micro-service to provide time series data storage and computing services. Overall, it has little impact on the original system and the migration and transformation is relatively smooth. Data compression may also be a valuable objective to consider. In the future, you may continue to look at features such as streaming computing provided by TDengine to see if you can further reduce the computing pressure on the business layer and improve the utilization of computing resources.

Author’s brief introduction

Huang Yong, graduated from Hunan Institute of Technology, has been engaged in Java development for many years, and is currently a core r&d member of Yaheng. He has presided over the development of the grassroots grid management system of Guangdong Provincial Political and Legal Committee, and deeply participated in the development of system projects such as the education information literacy promotion platform of Panyu District Education Bureau in Guangzhou and the Noise monitoring system of Haidongsi.

The original start Yu Yaheng science and technology, public links: mp.weixin.qq.com/s/qHQI9dRCc…