HIve实战分析Hadoop的日志

HIve实战分析Hadoop的日志
1、日志格式分析
首先分析 Hadoop 的日志格式, 日志是一行一条, 日志格式可以依次描述为:日期、时间、级别、相关类和提示信息。如下所示:
Java代码
1. 2013-03-06 15:23:48,132 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: STARTUP_MSG:
2. /************************************************************
3. STARTUP_MSG: Starting DataNode
4. STARTUP_MSG: host = ubuntu/127.0.0.1
5. STARTUP_MSG: args = []
6. STARTUP_MSG: version = 1.1.1
7. STARTUP_MSG: build = https://svn.apache.org/repos/asf/hadoop/common/branches/branch-1.1 -r 1411108; compiled by 'hortonfo' on Mon Nov 19 10:48:11 UTC 2012
8. ************************************************************/
9. 2013-03-06 15:23:48,288 INFO org.apache.hadoop.metrics2.impl.MetricsConfig: loaded properties from hadoop-metrics2.properties
10. 2013-03-06 15:23:48,298 INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source MetricsSystem,sub=Stats registered.
11. 2013-03-06 15:23:48,299 INFO org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Scheduled snapshot period at 10 second(s).
12. 2013-03-06 15:23:48,299 INFO org.apache.hadoop.metrics2.impl.MetricsSystemImpl: DataNode metrics system started
13. 2013-03-06 15:23:48,423 INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source ugi registered.
14. 2013-03-06 15:23:48,427 WARN org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Source name ugi already exists!
15. 2013-03-06 15:23:53,094 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Registered FSDatasetStatusMBean
16. 2013-03-06 15:23:53,102 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Opened data transfer server at 50010
17. 2013-03-06 15:23:53,105 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Balancing bandwith is 1048576 bytes/s
18. 2013-03-06 15:23:58,189 INFO org.mortbay.log: Logging to org.slf4j.impl.Log4jLoggerAdapter(org.mortbay.log) via org.mortbay.log.Slf4jLog
19. 2013-03-06 15:23:58,331 INFO org.apache.hadoop.http.HttpServer: Added global filtersafety (class=org.apache.hadoop.http.HttpServer$QuotingInputFilter)
20. 2013-03-06 15:23:58,346 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: dfs.webhdfs.enabled = false
21. 2013-03-06 15:23:58,346 INFO org.apache.hadoop.http.HttpServer: Port returned by webServer.getConnectors()[0].getLocalPort() before open() is -1. Opening the listener on 50075
22. 2013-03-06 15:23:58,346 INFO org.apache.hadoop.http.HttpServer: listener.getLocalPort() returned 50075 webServer.getConnectors()[0].getLocalPort() returned 50075
23. 2013-03-06 15:23:58,346 INFO org.apache.hadoop.http.HttpServer: Jetty bound to port 50075
24. 2013-03-06 15:23:58,347 INFO org.mortbay.log: jetty-6.1.26
25. 2013-03-06 15:23:58,719 INFO org.mortbay.log: Started SelectChannelConnector@0.0.0.0:50075
26. 2013-03-06 15:23:58,724 INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source jvm registered.
27. 2013-03-06 15:23:58,726 INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source DataNode registered.
28. 2013-03-06 15:24:03,904 INFO org.apache.hadoop.ipc.Server: Starting SocketReader
29. 2013-03-06 15:24:03,909 INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source RpcDetailedActivityForPort50020 registered.
30. 2013-03-06 15:24:03,909 INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source RpcActivityForPort50020 registered.
31. 2013-03-06 15:24:03,910 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: dnRegistration = DatanodeRegistration(localhost.localdomain:50010, storageID=DS-2039125727-127.0.1.1-50010-1362105928671, infoPort=50075, ipcPort=50020)
32. 2013-03-06 15:24:03,922 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Finished generating blocks being written report for 1 volumes in 0 seconds
33. 2013-03-06 15:24:03,926 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Starting asynchronous block report scan
34. 2013-03-06 15:24:03,926 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: DatanodeRegistration(192.168.11.157:50010, storageID=DS-2039125727-127.0.1.1-50010-1362105928671, infoPort=50075, ipcPort=50020)In DataNode.run, data = FSDataset{dirpath='/home/hadoop/hadoop-datastore/dfs/data/current'}
35. 2013-03-06 15:24:03,932 INFO org.apache.hadoop.ipc.Server: IPC Server listener on 50020: starting
36. 2013-03-06 15:24:03,932 INFO org.apache.hadoop.ipc.Server: IPC Server Responder: starting
37. 2013-03-06 15:24:03,934 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Finished asynchronous block report scan in 8ms
38. 2013-03-06 15:24:03,934 INFO org.apache.hadoop.ipc.Server: IPC Server handler 0 on 50020: starting
39. 2013-03-06 15:24:03,934 INFO org.apache.hadoop.ipc.Server: IPC Server handler 1 on 50020: starting
40. 2013-03-06 15:24:03,950 INFO org.apache.hadoop.ipc.Server: IPC Server handler 2 on 50020: starting
41. 2013-03-06 15:24:03,951 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: using BLOCKREPORT_INTERVAL of 3600000msec Initial delay: 0msec
42. 2013-03-06 15:24:03,956 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Reconciled asynchronous block report against current state in 1 ms
43. 2013-03-06 15:24:03,961 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: BlockReport of 12 blocks took 1 msec to generate and 5 msecs for RPC and NN processing
44. 2013-03-06 15:24:03,962 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Starting Periodic block scanner.
45. 2013-03-06 15:24:03,962 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Generated rough (lockless) block report in 0 ms
46. 2013-03-06 15:24:03,962 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Reconciled asynchronous block report against current state in 0 ms
47. 2013-03-06 15:24:04,004 INFO org.apache.hadoop.util.NativeCodeLoader: Loaded the native-hadoop library
48. 2013-03-06 15:24:04,047 INFO org.apache.hadoop.hdfs.server.datanode.DataBlockScanner: Verification succeeded for blk_3810479607061332370_1201
49. 2013-03-06 15:24:34,274 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_8724520321365706382_1202 src: /192.168.11.157:42695 dest: /192.168.11.157:50010
50. 2013-03-06 15:24:34,282 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42695, dest: /192.168.11.157:50010, bytes: 4, op: HDFS_WRITE, cliID: DFSClient_NONMAPREDUCE_-328627796_1, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_8724520321365706382_1202, duration: 1868644
51. 2013-03-06 15:24:34,282 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_8724520321365706382_1202 terminating
52. 2013-03-06 15:24:36,967 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Scheduling block blk_3810479607061332370_1201 file /home/hadoop/hadoop-datastore/dfs/data/current/blk_3810479607061332370 for deletion
53. 2013-03-06 15:24:36,969 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Deleted block blk_3810479607061332370_1201 at file /home/hadoop/hadoop-datastore/dfs/data/current/blk_3810479607061332370
54. 2013-03-06 15:24:42,130 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_-7687594967083109639_1203 src: /192.168.11.157:42698 dest: /192.168.11.157:50010
55. 2013-03-06 15:24:42,135 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42698, dest: /192.168.11.157:50010, bytes: 3, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_-7687594967083109639_1203, duration: 1823671
56. 2013-03-06 15:24:42,135 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_-7687594967083109639_1203 terminating
57. 2013-03-06 15:24:42,159 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_8851175106166281673_1204 src: /192.168.11.157:42699 dest: /192.168.11.157:50010
58. 2013-03-06 15:24:42,162 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42699, dest: /192.168.11.157:50010, bytes: 38, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_8851175106166281673_1204, duration: 496431
59. 2013-03-06 15:24:42,163 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_8851175106166281673_1204 terminating
60. 2013-03-06 15:24:42,177 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42700, bytes: 42, op: HDFS_READ, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_8851175106166281673_1204, duration: 598594
61. 2013-03-06 15:24:42,401 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_-3564732110216498100_1206 src: /192.168.11.157:42701 dest: /192.168.11.157:50010
62. 2013-03-06 15:24:42,402 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42701, dest: /192.168.11.157:50010, bytes: 109, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_-3564732110216498100_1206, duration: 465158
63. 2013-03-06 15:24:42,404 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_-3564732110216498100_1206 terminating
64. 2013-03-06 15:24:42,593 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_2602280850343619161_1208 src: /192.168.11.157:42702 dest: /192.168.11.157:50010
65. 2013-03-06 15:24:42,594 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42702, dest: /192.168.11.157:50010, bytes: 111, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2602280850343619161_1208, duration: 457596
66. 2013-03-06 15:24:42,595 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_2602280850343619161_1208 terminating
67. 2013-03-06 15:24:42,620 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_-8499292753361571333_1208 src: /192.168.11.157:42703 dest: /192.168.11.157:50010
68. 2013-03-06 15:24:42,673 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_2168216133004853837_1209 src: /192.168.11.157:42704 dest: /192.168.11.157:50010
69. 2013-03-06 15:24:42,676 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42704, dest: /192.168.11.157:50010, bytes: 848, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 705024
70. 2013-03-06 15:24:42,676 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_2168216133004853837_1209 terminating
71. 2013-03-06 15:24:42,691 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42705, bytes: 340, op: HDFS_READ, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 512, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 913742
72. 2013-03-06 15:24:42,709 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42706, bytes: 856, op: HDFS_READ, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 462507
73. 2013-03-06 15:24:42,724 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42707, bytes: 340, op: HDFS_READ, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 512, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 364763
74. 2013-03-06 15:24:42,726 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42708, bytes: 856, op: HDFS_READ, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 432228
75. 2013-03-06 15:24:42,739 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42703, dest: /192.168.11.157:50010, bytes: 421, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_-8499292753361571333_1208, duration: 116933097
76. 2013-03-06 15:24:42,739 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_-8499292753361571333_1208 terminating
77. 2013-03-06 15:24:42,759 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_-6232731177153285690_1209 src: /192.168.11.157:42709 dest: /192.168.11.157:50010
78. 2013-03-06 15:24:42,764 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42709, dest: /192.168.11.157:50010, bytes: 134, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_-6232731177153285690_1209, duration: 2742705
79. 2013-03-06 15:24:42,765 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_-6232731177153285690_1209 terminating
80. 2013-03-06 15:24:42,803 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_6878738047819289992_1210 src: /192.168.11.157:42710 dest: /192.168.11.157:50010
81. 2013-03-06 15:24:42,806 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:42710, dest: /192.168.11.157:50010, bytes: 727, op: HDFS_WRITE, cliID: DFSClient_hb_m_localhost.localdomain,60000,1362554661390_792638511_9, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_6878738047819289992_1210, duration: 1048999
82. 2013-03-06 15:24:42,807 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: PacketResponder 0 for block blk_6878738047819289992_1210 terminating
83. 2013-03-06 15:24:49,347 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42716, bytes: 340, op: HDFS_READ, cliID: DFSClient_hb_rs_localhost.localdomain,60020,1362554662758_1605864397_26, offset: 512, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 317106
84. 2013-03-06 15:24:49,359 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42717, bytes: 856, op: HDFS_READ, cliID: DFSClient_hb_rs_localhost.localdomain,60020,1362554662758_1605864397_26, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 460452
85. 2013-03-06 15:24:49,455 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42718, bytes: 516, op: HDFS_READ, cliID: DFSClient_hb_rs_localhost.localdomain,60020,1362554662758_1605864397_26, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 264641
86. 2013-03-06 15:24:49,456 INFO org.apache.hadoop.hdfs.server.datanode.DataNode.clienttrace: src: /192.168.11.157:50010, dest: /192.168.11.157:42719, bytes: 516, op: HDFS_READ, cliID: DFSClient_hb_rs_localhost.localdomain,60020,1362554662758_1605864397_26, offset: 0, srvID: DS-2039125727-127.0.1.1-50010-1362105928671, blockid: blk_2168216133004853837_1209, duration: 224282
87. 2013-03-06 15:24:50,615 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: Receiving block blk_-55581707144444311_1211 src: /192.168.11.157:42722 dest: /192.168.11.157:50010
88. 2013-03-06 15:38:17,696 INFO org.apache.hadoop.hdfs.server.datanode.DataNode: SHUTDOWN_MSG:
89. /************************************************************
90. SHUTDOWN_MSG: Shutting down DataNode at ubuntu/127.0.0.1
91. ************************************************************/
2、Hive表的定义如下:
Sql代码
1. create table if not exists loginfo(
2. rdate string,
3. time array<string>,
4. type string,
5. relateclass string,
6. information1 string,
7. information2 string,
8. information3 string)
9. row format delimited fields terminated by ' '
10. collection items terminated by ','
11. map keys terminated by ':';
3、MySql表定义
Sql代码
1. drop table if exists hadooplog;
2. create table hadooplog(
3. id int(11) not null auto_increment,
4. rdate varchar(50) null,
5. time varchar(50) default null,
6. type varchar(50) default null,
7. relateclass tinytext default null,
8. information longtext default null,
9. primary key (id)
10. ) engine=innodb default charset=utf8;
4、程序代码
1）DBHelper: 负责建立与 Hive 和 MySQL 的连接
Java代码
1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.sql.SQLException;
5. /**
6. * 负责连接Hive及mysql数据库
7. *
8. * @author 吖大哥
9. *
10. */
11. public class DBHelper {
13. private static Connection connToHive = null;
14. private static Connection connToMySQL = null;
16. private DBHelper() {
17. }
19. // 获得与 Hive 连接,如果连接已经初始化,则直接返回
20. public static Connection getHiveConn() throws SQLException {
21. if (connToHive == null) {
22. try {
23. Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");
24. } catch (ClassNotFoundException err) {
25. err.printStackTrace();
26. System.exit(1);
27. }
28. // hadoop3 为集群hive所在节点的IP地址
29. connToHive = DriverManager.getConnection(
30. "jdbc:hive://hadoop3:10000/default", "hive", "mysql");
31. }
32. return connToHive;
33. }
35. // 获得与 MySQL 连接
36. public static Connection getMySQLConn() throws SQLException {
37. if (connToMySQL == null) {
38. try {
39. Class.forName("com.mysql.jdbc.Driver");
40. } catch (ClassNotFoundException err) {
41. err.printStackTrace();
42. System.exit(1);
43. }
45. // hadoop2为集群mysql安装IP地址
46. connToMySQL = DriverManager
47. .getConnection(
48. "jdbc:mysql://hadoop2:3306/ha?useUnicode=true&characterEncoding=UTF8",
49. "root", "hadoop"); // 编码不要写成UTF-8
50. }
51. return connToMySQL;
52. }
54. public static void closeHiveConn() throws SQLException {
55. if (connToHive != null) {
56. connToHive.close();
57. }
58. }
60. public static void closeMySQLConn() throws SQLException {
61. if (connToMySQL != null) {
62. connToMySQL.close();
63. }
64. }
66. public static void main(String[] args) throws SQLException {
67. System.out.println(getMySQLConn());
68. closeMySQLConn();
69. }
71. }
2）HiveUtil:针对 Hive 的工具类：
Java代码
1. import java.sql.Connection;
2. import java.sql.ResultSet;
3. import java.sql.SQLException;
4. import java.sql.Statement;
6. /**
7. * Hive 数据处理工具类
8. *
9. * @author 吖大哥
10. *
11. */
12. public class HiveUtil {
14. // 创建表
15. public static void createTable(String sql) throws SQLException {
16. Connection conn = DBHelper.getHiveConn();
17. Statement stmt = conn.createStatement();
18. ResultSet res = stmt.executeQuery(sql);
19. }
21. // 依据条件查询数据
22. public static ResultSet queryData(String sql) throws SQLException {
23. Connection conn = DBHelper.getHiveConn();
24. Statement stmt = conn.createStatement();
25. ResultSet res = stmt.executeQuery(sql);
26. return res;
27. }
29. // 加载数据
30. public static void loadData(String sql) throws SQLException {
31. Connection conn = DBHelper.getHiveConn();
32. Statement stmt = conn.createStatement();
33. ResultSet res = stmt.executeQuery(sql);
34. }
36. // 把数据存储到 MySQL 中
37. public static void hiveToMySQL(ResultSet res) throws SQLException {
38. Connection conn = DBHelper.getMySQLConn();
39. Statement stmt = conn.createStatement();
40. while (res.next()) {
41. String rdate = res.getString(1);
42. String time = res.getString(2);
43. String type = res.getString(3);
44. String relateclass = res.getString(4);
45. String information = res.getString(5) + res.getString(6)
46. + res.getString(7);
47. StringBuffer sql = new StringBuffer();
48. sql.append("insert into hadooplog values(0,'");
49. sql.append(rdate + "','");
50. sql.append(time + "','");
51. sql.append(type + "','");
52. sql.append(relateclass + "','");
53. sql.append(information + "')");
55. int i = stmt.executeUpdate(sql.toString());
56. }
57. }
58. }
3）日志分析处理类 AnalyseHadoopLog
Java代码
1. import java.sql.ResultSet;
2. import java.sql.SQLException;
4. /**
5. * 分析Hadoop日志
6. *
7. * @author 吖大哥
8. *
9. */
10. public class AnalyseHadoopLog {
11. public static void main(String[] args) throws SQLException {
12. StringBuffer sql = new StringBuffer();
14. // 第一步:在 Hive 中创建表
15. sql.append("create table if not exists loginfo( ");
16. sql.append("rdate string, ");
17. sql.append("time array<string>, ");
18. sql.append("type string, ");
19. sql.append("relateclass string, ");
20. sql.append("information1 string, ");
21. sql.append("information2 string, ");
22. sql.append("information3 string) ");
23. sql.append("row format delimited fields terminated by ' ' ");
24. sql.append("collection items terminated by ',' ");
25. sql.append("map keys terminated by ':'");
27. System.out.println(sql);
28. HiveUtil.createTable(sql.toString());
30. // 第二步:加载 Hadoop 日志文件
31. sql.delete(0, sql.length());
32. sql.append("load data local inpath ");
33. sql.append("'/home/hadoop01/hadooplog'");
34. sql.append(" overwrite into table loginfo");
35. System.out.println(sql);
36. HiveUtil.loadData(sql.toString());
38. // 第三步:查询有用信息
39. sql.delete(0, sql.length());
40. sql.append("select rdate,time[0],type,relateclass,");
41. sql.append("information1,information2,information3 ");
42. sql.append("from loginfo where type='INFO'");
43. System.out.println(sql);
44. ResultSet res = HiveUtil.queryData(sql.toString());
46. // 第四步:查出的信息经过变换后保存到 MySQL 中
47. HiveUtil.hiveToMySQL(res);
49. // 第五步:关闭 Hive 连接
50. DBHelper.closeHiveConn();
52. // 第六步:关闭 MySQL 连接
53. DBHelper.closeMySQLConn();
54. }
55. }
5、查看操作结果
1）hive中的数据（部分数据）：
Java代码
1. hive> select * from loginfo
2. > ;
3. OK
4. 2013-03-06 ["15:23:48","132"] INFO org.apache.hadoop.hdfs.server.datanode.DataNode: STARTUP_MSG: NULL
5. /************************************************************ null NULL NULL NULL NULL NULL
6. STARTUP_MSG: ["Starting"] DataNode NULL NULL NULL NULL
7. STARTUP_MSG: [] host = ubuntu/127.0.0.1 NULL
8. STARTUP_MSG: [] args = [] NULL
9. STARTUP_MSG: [] version = 1.1.1 NULL
10. STARTUP_MSG: [] build = https://svn.apache.org/repos/asf/hadoop/common/branches/branch-1.1 -r
11. ************************************************************/ null NULL NULL NULL NULL NULL
12. 2013-03-06 ["15:23:48","288"] INFO org.apache.hadoop.metrics2.impl.MetricsConfig: loaded properties from
13. 2013-03-06 ["15:23:48","298"] INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source
14. 2013-03-06 ["15:23:48","299"] INFO org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Scheduled snapshot period
15. 2013-03-06 ["15:23:48","299"] INFO org.apache.hadoop.metrics2.impl.MetricsSystemImpl: DataNode metrics system
16. 2013-03-06 ["15:23:48","423"] INFO org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: MBean for source
17. 2013-03-06 ["15:23:48","427"] WARN org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Source name u
2）：mysql中的数据（部分数据）：
Java代码
1. mysql> select * from hadooplog;
2. +----+------------+----------+------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
3. | id | rdate | time | type | relateclass | information |
4. +----+------------+----------+------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
5. | 1 | 2013-03-06 | 15:23:48 | INFO | org.apache.hadoop.hdfs.server.datanode.DataNode: | STARTUP_MSG:null |
6. | 2 | 2013-03-06 | 15:23:48 | INFO | org.apache.hadoop.metrics2.impl.MetricsConfig: | loadedpropertiesfrom |
7. | 3 | 2013-03-06 | 15:23:48 | INFO | org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: | MBeanforsource |
8. | 4 | 2013-03-06 | 15:23:48 | INFO | org.apache.hadoop.metrics2.impl.MetricsSystemImpl: | Scheduledsnapshotperiod |
9. | 5 | 2013-03-06 | 15:23:48 | INFO | org.apache.hadoop.metrics2.impl.MetricsSystemImpl: | DataNodemetricssystem |
10. | 6 | 2013-03-06 | 15:23:48 | INFO | org.apache.hadoop.metrics2.impl.MetricsSourceAdapter: | MBeanforsource |
11. | 7 | 2013-03-06 | 15:23:53 | INFO | org.apache.hadoop.hdfs.server.datanode.DataNode: | RegisteredFSDatasetS
  来源： http://787141854-qq-com.iteye.com/blog/2068365
相关阅读:
143、Java内部类之访问方法中定义的参数或变量
 142、Java内部类之在普通方法里面定义内部类
 141、Java内部类之实例化外部类对象
 140、Java内部类之实例化内部类对象
 139、Java内部类之使用this访问外部类属性
 138、Java内部类之访问内部类的私有属性
 137、Java内部类之把内部类放到外部
 136、Java的内部类
 135、Java中的静态块，构造方法和构造块
 134、Java中的构造方法和构造块
原文地址：https://www.cnblogs.com/charlist/p/7122145.html

HIve实战分析Hadoop的日志

1、日志格式分析
首先分析 Hadoop 的日志格式, 日志是一行一条, 日志格式可以依次描述为:日期、时间、级别、相关类和提示信息。如下所示:

2、Hive表的定义如下:

3、MySql表定义

4、程序代码

1）DBHelper: 负责建立与 Hive 和 MySQL 的连接

2）HiveUtil:针对 Hive 的工具类：

3）日志分析处理类 AnalyseHadoopLog

5、查看操作结果

1）hive中的数据（部分数据）：

2）：mysql中的数据（部分数据）：

HIve实战分析Hadoop的日志

1、日志格式分析首先分析 Hadoop 的日志格式, 日志是一行一条, 日志格式可以依次描述为:日期、时间、级别、相关类和提示信息。如下所示:

2、Hive表的定义如下:

3、MySql表定义

4、程序代码

1）DBHelper: 负责建立与 Hive 和 MySQL 的连接

2）HiveUtil:针对 Hive 的工具类：

3）日志分析处理类 AnalyseHadoopLog

5、查看操作结果

1）hive中的数据（部分数据）：

2）：mysql中的数据（部分数据）：

1、日志格式分析
首先分析 Hadoop 的日志格式, 日志是一行一条, 日志格式可以依次描述为:日期、时间、级别、相关类和提示信息。如下所示: