基于SQL-on-Hadoop的网络日志分析

doi:10.3969/j.issn.1000-436x.2014.z1.004

Abstract

Abstract:

With the rapid expansion of network bandwidth,devices and applications,log management is facing the challenge of exploding data volumes.Log analysis platform built on SQL-on-Hadoop is capable of storing and querying hundreds of billions of log entries effectively.Columnar and compressed data formats for Hadoop are benchmarked with real-world multi-TB dataset.Conditional and statistical querying efficiency of Hive and Impala is tested.With gzipped parquet format,log data can be compressed by 80%,and querying with impala is 5 times faster.On this platform,six security incident analysis and detection applications are already deployed.

Key words: og analysis, big data, Hadoop, SQL, network security

Si-yu ZHANG,Kai-da JIANG,Jian-wen WEI,Xuan LUO,Hai-yang WANG. Network log analysis with SQL-on-Hadoop[J]. Journal on Communications, 2014, 35(Z1): 14-19.

Figures/Tables 8

References 11

[1]	OLINER A , GANAPATHI A , XU W . Advances and challenges in log analysis[J]. Communications of the ACM, 2012,55(2): 55-61.
[2]	WEI J , ZHAO Y , JIANG K ,et al. Analysis farm:a cloud-based scalable aggregation and query platform for network log analysis[A]. 2011 International Conference on Cloud and Service Computing[C]. Hong Kong,China, 2011. 354-359.
[3]	RABKIN A , KATZ R H . Chukwa:a system for reliable large-scale log collection[A]. 24th International Conference on Large Installation System Administration[C]. San Jose,CA,USA, 2010. 163-177.
[4]	The apache software foundation.Apache flume[EB/OL]. .
[5]	The apache software foundation.Apache sqoop[EB/OL]. .
[6]	THUSOO A , SARMA J S , JAIN N ,et al. Hive-a petabyte scale data warehouse using hadoop[A]. 26th IEEE International Conference on Data Engineering Long Beach[C]. CA,USA, 2010. 996-1005.
[7]	Cloudera. Impala[EB/OL]. .
[8]	FLORATOU A , MINHAS U F , OZCAN F . SQL-on-Hadoop:full circle back to shared-nothing database architectures[J]. Proceedings of the VLDB Endowment, 2014,7(12): 1295-1306.
[9]	HE Y , LEE R , HUAI Y ,et al. RCFile:a fast and space-efficient data placement structure in MapReduce-based warehouse systems[A]. 27th IEEE International Conference on Data Engineering[C]. Hannover,Germany, 2011. 1199-1208.
[10]	Parquet. Parquet[EB/OL]. .
[11]	姜开达, 李霄, 孙强 . 基于搜索引擎关键词的校园网安全分析[A]. 中国教育和科研计算机网 CERNET 第十九届学术年会[C]. 太原,中国, 2012. 83-87. JIANG K , LEE X , SUN Q . Campus network security analysis with search engine keywords[A]. 19th CERNET Annual Conference[C]. Taiyuan,China, 2012. 83-87.

Metrics

Recommended 0

No Suggested Reading articles found!

文件格式	Hive		Impala
文件格式	查询	插入/导入	查询	插入/导入
Text	√	√	√	√
RCFile	√	√	√
ORC	√	√
Parquet	√	√	√	√

格式/压缩	体积/TB	Hive查询/s	Impala查询/s
Text	9.35	1 146.1	662.6
Parquet	6.78	1 106.2	489.7
Parquet / Snappy	3.10	713.9	231.2
Parquet / Gzip	1.73	726.2	134.6
RCFile	9.21	1 044.7	672.3
RCFile / Snappy	3.58	461.2	302.4
ORC	2.42	505.8	—
ORC / Snappy	1.15	357.9	—
ORC / Zlib	0.87	359.4	—

格式/压缩	Hive		Impala
格式/压缩	CPU	磁盘读取/(GB·s^?1)	CPU	磁盘读取/(GB·s^?1)
Text	99.9%	9.8	19.5%	16.9
Parquet	99.9%	8.4	26.0%	17.1
Parquet / Snappy	99.5%	5.5	56.3%	17.3
Parquet / Gzip	98.7%	3.0	99.9%	14.3
RCFile	99.2%	10.6	22.0%	16.8
RCFile / Snappy	99.2%	9.8	44.3%	17.2
ORC	99.3%	5.9	—	—
ORC / Snappy	98.7%	4.0	—	—
ORC / Zlib	98.8%	3.3	—	—

类型	源地址	请求数
SQL注入	1 034	611 136
文件包含/下载	276	42 237
WebShell尝试	297	197 183
路径探测	14 933	1 023 730
扫描器	799	1 037 806

时间	网址	User-Agent	内容尺寸
3/8 11:15	61.160.221.*:88/fuck	Wget/1.10.2(RedHatmodified)	6 772
3/8 11:16	61.160.221.*:88/xz32	Wget/1.10.2(RedHatmodified)	1 351 181
3/9 2:54	61.160.221.*:88/fuck3.sh	Wget/1.10.2(RedHatmodified)	404
3/9 2:54	61.160.221.*:88/fuck	Wget/1.10.2(RedHatmodified)	6 772
3/9 2:55	61.160.221.*:88/sshd8	Wget/1.10.2(RedHatmodified)	1 513 570
3/9 2:57	61.160.221.*:88/ssh.py	Wget/1.10.2(RedHatmodified)	1 324
3/9 2:57	61.160.221.*:88/mafix.tar.gz	Wget/1.10.2(RedHatmodified)	446 713

Network log analysis with SQL-on-Hadoop

RichHTML

PDF

Knowledge

Abstract

Cite this article

share this article

Figures/Tables 8

References 11

Related Articles 15

Metrics

Recommended 0

[1]	Shiqi ZHAO, Xiaohong HUANG, Zhigang ZHONG. Research and implementation of reputation-based inter-domain routing selection mechanism [J]. Journal on Communications, 2023, 44(6): 47-56.
[2]	Haiyan KANG, Molan LONG. Research on network attack analysis method based on attack graph of absorbing Markov chain [J]. Journal on Communications, 2023, 44(2): 122-135.
[3]	Wei JIN, Fenghua LI, Mingjie YU, Yunchuan GUO, Ziyan ZHOU, Liang FANG. HDFS-oriented cryptographic key resource control mechanism [J]. Journal on Communications, 2022, 43(9): 27-41.
[4]	Hongbin ZHANG, Yan YIN, Dongmei ZHAO, Bin LIU. Network security situational awareness model based on threat intelligence [J]. Journal on Communications, 2021, 42(6): 182-194.
[5]	Yimin MAO, Qianhu DENG, Zhigang CHEN. Parallel association rules incremental mining algorithm based on information entropy and genetic algorithm [J]. Journal on Communications, 2021, 42(5): 122-136.
[6]	Bolin MA, Zheng ZHANG, Hao LIU, Jiangxing WU. SQLMVED: SQL injection runtime prevention system based on multi-variant execution [J]. Journal on Communications, 2021, 42(4): 127-138.
[7]	Tengfei ZHANG, Shunzheng YU. Research prospects of user information detection from encrypted traffic of mobile devices [J]. Journal on Communications, 2021, 42(2): 154-167.
[8]	Xu CHENG, Yingying WANG, Nianjie ZHANG, Zhangjie FU, Beijing CHEN, Guoying ZHAO. Multi-level loss object tracking adversarial attack method based on spatial perception [J]. Journal on Communications, 2021, 42(11): 242-254.
[9]	Tao HUANG, Jiang LIU, Shuo WANG, Chen ZHANG, Yunjie LIU. Survey of the future network technology and trend [J]. Journal on Communications, 2021, 42(1): 130-150.
[10]	Zhiyong LUO,Xu YANG,Jiahui LIU,Rui XU. Network intrusion intention analysis model based on Bayesian attack graph [J]. Journal on Communications, 2020, 41(9): 160-169.
[11]	Liang YUAN, Xiao YU, Enjie DING, Xiaohu ZHAO, Shimin FENG, Da ZHANG, Tongyu LIU, Weidong WANG, Yanqiu HUANG. Research on key technologies of human-machine-environment states perception in mine Internet of things [J]. Journal on Communications, 2020, 41(2): 1-12.
[12]	Hanxun ZHOU,Chen CHEN,Runze FENG,Junkun XIONG,Hong PAN,Wei GUO. Mobile malware traffic detection approach based on value-derivative GRU [J]. Journal on Communications, 2020, 41(1): 102-113.
[13]	. Dual-architecture Internet supporting intelligent governance of cyber content [J]. Journal on Communications, 2019, 40(9): 1-14.
[14]	JIANG Lyu,ZHANG Hengwei,WANG Jindong. Optimal strategy selection method for moving target defense based on signaling game [J]. Journal on Communications, 2019, 40(6): 128-137.
[15]	Wei JIN,Mingjie YU,Fenghua LI,Zhengkun YANG,Kui GENG. High-performance and high-concurrency encryption scheme for Hadoop platform [J]. Journal on Communications, 2019, 40(12): 29-40.