Design And Query Optimization Of Highway Traffic Data Warehouse Based On Cloud Platform

With the development of Internet of Things technology, the increase of intelligent sensors, the data collected by the transportation industry is growing rapidly. Especially in the highway toll system, every day there will be massive highway toll station data. Through analyzing the structured data,we can sort out the valuable information such as the spatiotemporal distribution of traffic volume and the carrying capacity of expressway, the prosperous index of expressway transportation, the report of charges and so on, and provide the data support to the decision-makers of expressway.At present, the management system used by most traffic departments adopts Oracle-driven database. Faced with the increasingly large amount of data on the highway toll station data, these management systems have shown a lot of problems, such as complex and long-time data integration process, dependent on professionals, slow data query speed. Therefore, this paper studies the design and query optimization of the highway traffic data warehouse based on cloud platform. First of all, this paper designs and implements the data warehouse for the highway toll station data. This process includes three stages: data extraction, data preprocessing and data processing. Secondly, based on the comparison of Hive and Impala query features, the analysis of data warehouse partition granularity and the expressway service characteristics, three data warehouse query optimization methods are proposed and used. Based on cloud platform Hadoop and its distributed file storage system HDFS, data warehousing tools Hive and Impala, the data warehouse is built and the data visualization platform is designed to realize the functions of data query and special analysis. Finally, this paper uses the actual highway toll station data to construct the data warehouse of the expressway traffic based on the cloud platform successfully. The experimental results show that the data query optimization method can effectively improve the efficiency of data query and shorten the query time.