Design and implementation of UN Comtrade data sharing platform
-
摘要:UN Comtrade(United Nations international trade statistics database)是全球最大且应用最为广泛的国际贸易数据库,具有高权威性与完整性.本文在框架体系结构设计与数据表结构设计基础上构建UN Comtrade数据共享平台,以期为地理学研究提供数据与工具支撑.在数据聚合策略方面,平台通过综合集成数据爬取、加载模块并嵌套多种错误修正方法,实现5亿多条商品贸易记录的动态高容错聚合.在检索策略方面,平台通过分区复合索引提升数据检索指令执行效率与可扩展性.检索试验表明,平台能够在80用户并发模式下稳定执行不同类型检索指令,并且通过调用ODBC/JDBC接口将计算过程融入检索任务,可以更加有效利用服务器端资源并节省数据传输与读写耗时,具有效率更高、简化数据处理过程等优点.2017年平台被应用于中-美商品显性比较优势特征检索-计算-格网化表达-对比分析,案例表明平台具有高效、稳定的并发检索效率,以及高可扩展性等优点,可以为贸易特征计算与分析提供便捷快速、形式多样的数据共享服务.Abstract:United Nations international trade statistics database (UN Comtrade) has provided important data support and application guidance on strengthening the recognition of trade system rules and their driving factors, developing international trade measurement methods, depicting global trade pattern and its changing process. Furthermore, it has been more and more widely applied to global ecological conservation, water-energy-food-land systems, pollution control, energy management, national security and other topics of geographical researches.In this paper, we establish the UN Comtrade data sharing platform with Oracle DBMS based on design of framework and data table structure.This platform is designed to make up for the database’s deficiencies in data sharing methods and retrieval interfaces as well as provide data and tool support for geographic research.We develop an automated data archiving module with Python 3.6 and its Scrapy framework 1.5.2, which achieves a dynamic, stable, and highly fault-tolerant processing of more than 500 million trade records by comprehensively integrating data crawling module, data loading module and nesting multiple error correction methods.In addition, the platform improves the efficiency and scalability of data retrieval instruction execution through range partitioning, partitioned composite indexing, and open ODBC/JDBC interfaces based on the structured characteristics of data.Experiments show that the platform can stably execute different types of retrieval instructions in a concurrent mode of 80 users.By invoking the ODBC/JDBC interface to integrate the calculation process into the retrieval task, the system can more effectively use server-side resources and save time for data transmission, reading and writing with higher efficiency and simplified data processing.Based on the platform, we develop a data query and sharing client and apply it to the retrieval, calculation, grid representation and comparative analysis of the explicit comparative advantage characteristics of Chinese and American products in 2017.The application shows that the platform has the advantages of high efficiency, stable concurrent retrieval efficiency and high scalability.It can provide more convenient, fast, and diverse data sharing services for the calculation and analysis of trade characteristics research.
-
Key words:
- international trade/
- big data/
- grid/
- revealed comparative advantage index/
- GIS
-
表 1数据共享平台并发检索效率对比
指令编号 指令描述 不同数量用户并发全周期耗时/执行耗时/s1) 检索结果
记录数/条1
用户5
并发10
并发25
并发50
并发80
并发① 查询2016年中国全部
HS6位编码商贸记录33.5 / 20.2 49.3 / 19.5 44.0 / 19.3 64.4 / 19.6 107.6 / 20.1 131.7 / 22.7 521543 ② 查询中-美1992—2018年
HS4位编码商贸记录87.6 / 24.8 310.3 / 32.8 419.5 / 39.7 1038.0 / 51.8 1906.0 / 80.5 2786.6 / 105.8 57352 ③ 查询全球不同国家
1992—2018年大豆记录94.7 / 51.9 299.7 / 116.6 534.4 / 200.9 1242.9 / 368.9 2643.0 / 583.1 3751.6 / 721.2 53296 ④ 查询计算全球不同国家
1992—2018年HS4位编码
商品进出口额平均值101.7 / 93.4 239.3 / 228.4 429.2 / 415.8 987.7 / 971.1 1682.6 / 1653.7 2124.8 / 2113.6 520882 ⑤ 计算2017年中国HS6位
商品比较优势指数36.7 / 36.6 35.7 / 35.7 36.4 / 36.4 37.2 / 37.2 38.2 / 38.2 41.6 / 41.6 5002 1)JMETER Ramp-up period参数设为1 s. -
[1] FADEYI O,BAHTA T,OGUNDEJI A,et al. Impacts of the SADC Free Trade Agreement on South African agricultural trade[J]. The International Journal of Entrepreneurship and Innovation,2014,43(1):53 [2] VENINGA W,IHLE R. Import vulnerability in the Middle East:effects of the Arab spring on Egyptian wheat trade[J]. Food Security,2018,10(1):183doi:10.1007/s12571-017-0755-2 [3] MATKOVSKI B,RADOVANOV B,ZEKIC S. The effects of foreign agri-food trade liberalization in South East Europe[J]. Ekonomicky Casopis,2018,66(9):945 [4] DINGEMANS A,ROSS C. Free Trade Agreements in Latin America since 1990:an evaluation of export diversification[J]. CEPAL Review,2012,108(108):27 [5] DONG C,YIN Q,LANE K,et al. Competition and transmission evolution of global food trade:a case study of wheat[J]. Physica A: Statistical Mechanics and its Applications,2018,509:998doi:10.1016/j.physa.2018.06.052 [6] ERMANN L,SHEPELYANSKY D. Ecological analysis of world trade[J]. Physics Letters Section A: General Atomic & Solid State Physics,2013,377(3/4):250 [7] ERMANN L,SHEPELYANSKY D. Google matrix analysis of the multiproduct world trade network[J]. The European Physical Journal B,2015,88(4):84doi:10.1140/epjb/e2015-60047-0 [8] 陈小强,袁丽华,沈石,等. 中国及其周边国家间地缘关系解析[J]. 地理学报,2019,74(8):1534doi:10.11821/dlxb201908004 [9] KIVEL M,ARENAS A,BARTHELEMY M,et al. Multilayer networks[J]. Journal of Complex Networks,2014,2(3):203doi:10.1093/comnet/cnu016 [10] LIU X M,STANLEY H E,GAO J X. Breakdown of interdependent directed networks[J]. PNAS,2016,113(5):1138doi:10.1073/pnas.1523412113 [11] LATAPY M,MAGNIEN C,VECCHIO N. Basic notions for the analysis of large two-mode networks[J]. Social Networks,2008,30(1):31doi:10.1016/j.socnet.2007.04.006 [12] VIDMER A,ZENG A,MEDO M,et al. Prediction in complex systems:the case of the international trade network[J]. Physica A,2015,436:188doi:10.1016/j.physa.2015.05.057 [13] HIDALGO C,KLINGER B,BARABSI A,et al. The product space conditions the development of nations[J]. Science,2007,317(5837):482doi:10.1126/science.1144581 [14] HIDALGO C A,HAUSMANN R. The building blocks of economic complexity[J]. PNAS,2009,106(26):10570doi:10.1073/pnas.0900943106 [15] MEALY P,FARMER J D,TEYTELBOYM A. Interpreting economic complexity[J]. Science Advances,2019,5(1):eaau1705doi:10.1126/sciadv.aau1705 [16] 杜彬,高培超,宋长青,等. 青海省出口贸易格局演化及其影响因素解析[J]. 必威精装版app官方下载苹果版 (自然科学版),2022,58(1):161 [17] MUPELA E, SZIRMAI A. Communication costs and trade in Sub Saharan Africa: a gravity approach[C]// BISSYANDÉ T, VAN STAM G. E-Infrastructure and e-services for developing countries. AFRICOMM 2013. Lecture notes of the institute for computer sciences, social informatics and telecommunications engineering, Springer: Cham, 2014: 135 [18] DE F,DE S,VIEIRA J. Evaluation of logistic performance indexes of Brazil in the international trade[J]. RAM. Revista de Administraç ã o Mackenzie,2015,16:213 [19] ZHENG H,WANG Z. Measurement and comparison of export sophistication of the new energy industry in 30 countries during 2000–2015[J]. Renewable and Sustainable Energy Reviews,2019,108:140doi:10.1016/j.rser.2019.03.038 [20] CAO X,HANSON-RASMUSSEN N. Dynamic change in the export technology structure of China’s environmental goods and its international comparison[J]. Sustainability,2018,10:3508doi:10.3390/su10103508 [21] HAO X,AN H,SUN X,et al. The import competition relationship and intensity in the international iron ore trade:from network perspective[J]. Resources Policy,2018,57(1):45 [22] YE S,CHENG C,SONG C,et al. Visualizing bivariate local spatial autocorrelation between commodity revealed comparative advantage index of China and USA from a new space perspective[J]. Environ Plan A Econ Sp,2021,53(2):223doi:10.1177/0308518X20957336 [23] PETERS G,MINX J,WEBER C,et al. Growth in emission transfers via international trade from 1990 to 2008[J]. Proceedings of the National Academy of Sciences,2011,108:8903doi:10.1073/pnas.1006388108 [24] MEYFROIDT P,RUDEL T K,LAMBIN E F. Forest transitions,trade,and the global displacement of land use[J]. PNAS,2010,107(49):20917doi:10.1073/pnas.1014773107 [25] HORNBORG A. Footprints in the cotton fields:the Industrial Revolution as time–space appropriation and environmental load displacement[J]. Ecological Economics,2006,59(1):74doi:10.1016/j.ecolecon.2005.10.009 [26] DALIN C,HANASAKI N,QIU H,et al. Water resources transfers through Chinese interprovincial and foreign food trade[J]. Proceedings of the National Academy of Sciences,2014,111(27):9774doi:10.1073/pnas.1404749111 [27] LEPAWSKY J. The changing geography of global trade in electronic discards:time to rethink the e‐waste problem[J]. The Geographical Journal,2015,181(2):147doi:10.1111/geoj.12077 [28] BROOKS A,WANG S,JAMBECK J. The Chinese import ban and its impact on global plastic waste trade[J]. Science Advances,2018,4(6):131 [29] CHEN X,CHENG S,WU L. Quantitative analysis of central Asian countries’ energy security and its political influence factors[J]. Journal of Resources and Ecology,2018,9(4):434doi:10.5814/j.issn.1674-764x.2018.04.011 [30] 洪菊花,骆华松,梁茂林. 主体间性视角下的“一带一路”能源安全共同体研究[J]. 世界地理研究,2017,26(2):11doi:10.3969/j.issn.1004-9479.2017.02.002 [31] 姜巍,高卫东. 中国能源消费增长特征及影响因素分析[J]. 世界地理研究,2013,22(3):160doi:10.3969/j.issn.1004-9479.2013.03.019 [32] JACKSON M O,NEI S. Networks of military alliances,wars,and international trade[J]. PNAS,2015,112(50):15277doi:10.1073/pnas.1520970112 [33] MORAN D D,WACKERNAGEL M C,KITZES J A,et al. Trading spaces:calculating embodied Ecological Footprints in international trade using a Product Land Use Matrix (PLUM)[J]. Ecological Economics,2009,68(7):1938doi:10.1016/j.ecolecon.2008.11.011 [34] 叶思菁,朱德海,姚晓闯,等. 基于移动GIS的作物种植环境数据采集技术[J]. 农业机械学报,2015,46(9):325doi:10.6041/j.issn.1000-1298.2015.09.048 [35] 叶思菁,张超,王媛,等. GF-1遥感大数据自动化正射校正系统设计与实现[J]. 农业工程学报,2017,33(S1):266 [36] 叶思菁. 大数据环境下遥感图谱应用方法研究:以作物干旱监测为例[J]. 测绘学报,2018,47(6):892doi:10.11947/j.AGCS.2018.20170535 [37] YE S, SONG C, CHENG C, et al. digital trade feature map: a new method for visualization and analysis of spatial patterns in bilateral trade[J]. ISPRS Int J Geo Inf, 2020, 9(6): 363 [38] HU L, SONG C, YE S, et al. Spatiotemporal statistical imbalance: a long-term neglected defect in UN Comtrade Dataset [J]. Sustainability, 2022, 14(3): 1431 [39] 陈小强,袁丽华,宋长青,等. 中美在中国周边地区的商品贸易发展及影响力对比[J]. 地理研究,2022,41(3):663doi:10.11821/dlyj020210716 [40] WANG Y, SONG C, SIGLEY G, et al. Using social networks to analyze the spatiotemporal patterns of the rolling stock manufacturing industry for countries in the Belt and Road Initiative[J]. ISPRS Int J Geo-Inf, 2020, 9, 431