Nginx日志导入到Hive0.13.1,同步Hbase0.96.2,设置RowKey为autoincrement(ID自增长)

最新推荐文章于 2021-03-24 18:04:05 发布
最新推荐文章于 2021-03-24 18:04:05 发布
阅读量105 收藏
点赞数
文章标签： 大数据 运维 java
原文链接：http://www.cnblogs.com/lion.net/p/3932741.html
版权
----------------------------------------
博文作者：迦壹
转载声明：可以转载, 但必须以超链接形式标 明文章原始出处和作者信息及版权声明，谢谢合作！
---------------------------------------
 
目录：
　　一、Nginx的相关配置，以及测试数据
　　二、Hadoop中创建使用目录
　　三、Hive的日志表创建，并同步到Hbase中
　　　　1)创建自增长ID的类,并添加到Hive环境中
　　　　2)在Hive中创建日志表
　　　　3)将Nginx的日志数据导入到Hive日志表中
　　　　　　方法一：将本地目录中的文件导入到hive中
　　　　　　方法二：从HDFS导入数据
　　　　　　方法三：通过如Flume、Kafka一类的工具监控日志数据，自动导入
　　　　4)在Hive中创建Hbase可以识别的表
　　　　5)将Hive的日志表，使用自增长Id做为rowkey导入到Hbase可识别的表中
　　　　6)到Hbase中验证效果使用get、scan、filter获取指定数据
　　四、FAQ
　　五、扩展阅读
 
　　一、Nginx的相关配置，以及测试数据
　　#Nginx的Conf中log格式化部分的内容
?
1
2
3
4
5
log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                                 '$status $body_bytes_sent "$http_referer" '
                                 '"$http_user_agent" "$http_x_forwarded_for" '
                                 '"$gzip_ratio" $request_time $bytes_sent $request_length '
                                 '"$upstream_addr" $upstream_status $upstream_response_time' ;
　　#Nginx生成后部分格式如下(测试数据)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET / HTTP/1.1" 200 2373 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "2.78" 0.004 2683 369 "unix:/var/run/php5-fpm.sock" 200 0.004
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/css/base.css HTTP/1.1" 200 940 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 1247 373 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/a3e2e507/jquery.min.js HTTP/1.1" 200 93636 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.152 93976 359 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/logo.png HTTP/1.1" 200 6059 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 6369 377 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/p02.jpg HTTP/1.1" 200 22177 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 22489 376 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/p03.png HTTP/1.1" 200 3012 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 3321 376 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/two-dimension-code1.png HTTP/1.1" 200 761 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 1069 392 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/bg.png HTTP/1.1" 200 11474 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 11785 375 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/p04.png HTTP/1.1" 200 2860 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.000 3169 376 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/p06.png HTTP/1.1" 200 74097 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.062 74409 376 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:45 +0800] "GET 8.8.8.8/b519d8ca/image/p05.png HTTP/1.1" 200 132072 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.256 132385 376 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:46 +0800] "GET 8.8.8.8/b519d8ca/image/p07.png HTTP/1.1" 200 207987 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.592 208300 376 "-" - -
8.8.8.8 - - [22 /Aug/2014 :20:23:46 +0800] "GET 8.8.8.8/b519d8ca/image/p01.png HTTP/1.1" 200 310418 "http://xxx.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36" "-" "-" 0.918 310731 376 "-" - -
 
　　二、Hadoop中创建使用目录
　　#在Hadoop2.2.0中创建Hive外部表中要用到的目录
?
1
2
3
4
5
root@m1: /home/hadoop # /home/hadoop/hadoop-2.2.0/bin/hadoop fs -mkdir /user/hive/warehouse/nginxlog
root@m1: /home/hadoop # /home/hadoop/hadoop-2.2.0/bin/hadoop fs -ls /user/hive/warehouse
Found 1 items
drwxr-xr-x   - root supergroup          0 2014-01-22 23:13 /user/hive/warehouse/nginxlog
root@m1: /home/hadoop #
　　三、Hive的日志表创建，并同步到Hbase中
　　　　1)创建自增长ID的java类 UDFRowSequence.java ,并添加到Hive环境中
 　 　#在Eclipse中创建Maven项目， 然后使用Maven将项目打包成Jar文件，过程中需要引入 hadoop-common-2.2.0.jar、hive-exec-0.13.1.jar这两个文件，可以在Hadoop2.2.0目录以及Hive0.13.1目录中找到。 如果不会使用Maven将项目打包，可以参考这篇文章《 Golang、Php、Python、Java基于Thrift0.9.1实现跨语言调用 》中实现Java客户端部分，有如何打包的方法。
　 　 UDFRowSequence.java 代码如下，使用Maven会打包成一个idoall.org-0.0.1-SNAPSHOT-jar-with-dependencies.jar文件，上传到Hive的lib目录下。本文后面会提供代码下载 ：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package idoall.org.hive;
 
 
import org.apache.hadoop.hive.ql.exec.Description;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.UDFType;
import org.apache.hadoop.io.LongWritable;
 
/**
  * UDFRowSequence.
  */
@Description (name = "row_sequence" ,
     value = "_FUNC_() - Returns a generated row sequence number starting from 1" )
@UDFType (deterministic = false , stateful = true )
public class UDFRowSequence extends UDF
{
   private LongWritable result = new LongWritable();
 
   public UDFRowSequence() {
     result.set( 0 );
   }
 
   public LongWritable evaluate() {
     result.set(result.get() + 1 );
     return result;
   }
}
　　　　2)在Hive中创建日志表
　　#然后将我们自定义的idoall.org-0.0.1-SNAPSHOT-jar-with-dependencies.jar，添加到Hive运行环境中
?
1
2
3
4
hive> ADD JAR /home/hadoop/hive-0.13.1/lib/idoall.org-0.0.1-SNAPSHOT-jar- with -dependencies.jar;
Added /home/hadoop/hive-0.13.1/lib/idoall.org-0.0.1-SNAPSHOT-jar- with -dependencies.jar to class path
Added resource: /home/hadoop/hive-0.13.1/lib/idoall.org-0.0.1-SNAPSHOT-jar- with -dependencies.jar
hive>
　　#在Hive中创建自定义函数对数据进行处理，如果不想每次都执行这个命令，可以在hive-site.xml里面的<name>hive.aux.jars.path</name> 节点下面，把jar文件加到<value>节点中。
?
1
2
3
hive> CREATE TEMPORARY FUNCTION rowSequence AS 'idoall.org.hive.UDFRowSequence' ;
OK
Time taken: 0.048 seconds
　　#在Hive中创建收集Nginx的日志表（nginx_accesslog）
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create external table nginx_accesslog(
host string,
hostuser string,
times string,
requestmethond string,
requesturl string,
requesthttp string,
status string,
body_bytes_sent string,
referer string,
useragent string,
http_x_forwarded_for string,
gzip_ratio string,
request_time string,
bytes_sent string,
request_length string,
upstream_addr string,
upstream_status string,
upstream_response_time string)
PARTITIONED BY ( YEAR STRING, MONTH STRING, DAY STRING)
row format SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ( "input.regex" = "([^ ]*)\\s+-\\s+(.+?|-)\\s+\\[(.*)\\]\\s+\"([^ ]*)\\s+([^ ]*)\\s+([^ |\"]*)\"\\s+(-|[0-9]*)\\s+(-|[0-9]*)\\s+\"(.+?|-)\"\\s+\"(.+?|-)\"\\s+\"(.+?|-)\"\\s+\"(.+?|-)\"\\s+(.+?|-)\\s+(.+?|-)\\s+(.+?|-)\\s+\"(.+?|-)\"\\s+(.+?|-)\\s+(.*)" , "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s %10$s %11$s %12$s %13$s %14$s %15$s %16$s %17$s %18$s" )
STORED AS TEXTFILE
location '/user/hive/warehouse/nginxlog' ;
　　　　3)将Nginx的日志数据导入到Hive日志表中
关于导入数据过程中overwrite的特性：
•目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
•如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代
如果不使用overwrite,改用into,只会增加记录。
 
　　　　　　方法一：将本地目录中的文件导入到Hive中
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
hive> LOAD DATA LOCAL INPATH '/home/hadoop/hive-0.13.1/a.com.access.20140821.log' OVERWRITE INTO TABLE nginx_accesslog partition ( YEAR = '2014' , MONTH = '08' , DAY = '21' );
Copying data from file:/home/hadoop/hive-0.13.1/a.com.access.20140821.log
Copying file: file:/home/hadoop/hive-0.13.1/a.com.access.20140821.log
Loading data to table default .nginx_accesslog partition ( year =2014, month =08, day =21)
Partition default .nginx_accesslog{ year =2014, month =08, day =21} stats: [numFiles=1, numRows=0, totalSize=3483, rawDataSize=0]
OK
Time taken: 1.046 seconds
hive> select count (0) from nginx_accesslog;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time : 1
In order to change the average load for a reducer ( in bytes):
   set hive. exec .reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
   set hive. exec .reducers. max =<number>
In order to set a constant number of reducers:
   set mapreduce.job.reduces=<number>
Starting Job = job_1408550631561_0005, Tracking URL = http://m1:8088/proxy/application_1408550631561_0005/
Kill Command = /home/hadoop/hadoop-2.2.0/bin/hadoop job  -kill job_1408550631561_0005
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2014-08-22 23:19:55,322 Stage-1 map = 0%,  reduce = 0%
2014-08-22 23:20:01,669 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.74 sec
2014-08-22 23:20:08,926 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 1.59 sec
MapReduce Total cumulative CPU time : 1 seconds 590 msec
Ended Job = job_1408550631561_0005
MapReduce Jobs Launched:
Job 0: Map: 1  Reduce: 1   Cumulative CPU: 1.59 sec   HDFS Read : 3734 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 590 msec
OK
13
Time taken: 24.762 seconds, Fetched: 1 row(s)
hive>
 
　　　　　　方法二：从HDFS导入数据,我们先将表删除，重新创建一次，然后尝试从HDFS导入数据
　　 #先用Hadoop命令，将文件上传到HDFS中
?
1
2
3
4
5
root@m1: /home/hadoop # /home/hadoop/hadoop-2.2.0/bin/hadoop fs -copyFromLocal /home/hadoop/hive-0.13.1/a.com.access.20140821.log /user/hive/warehouse/nginxlog/
root@m1: /home/hadoop # /home/hadoop/hadoop-2.2.0/bin/hadoop fs -ls /user/hive/warehouse/nginxlog
Found 1 items
-rw-r--r--   3 root supergroup       3483 2014-08-22 23:18 /user/hive/warehouse/nginxlog/a .com.access.20140821.log
root@m1: /home/hadoop #
 
　　#执行Hive命令，从HDFS导入数据，然后使用SELECT语句，可以看到Nginx的日志数据已经成功导入到Hive表中
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#先删除表
hive> drop table nginx_accesslog;
OK
Time taken: 0.363 seconds
hive>
#再创建表
....此处省略，参考上文命令重新创建一次
#从HDFS导入数据（如果文件存在，要先删除），从下图可以看到，数据导入成功
hive> LOAD DATA inpath '/user/hive/warehouse/nginxlog/a.com.access.20140821.log' overwrite INTO TABLE nginx_accesslog partition (YEAR= '2014' , MONTH= '08' ,DAY= '21' );
Loading data to table default.nginx_accesslog partition (year=2014, month=08, day=21)
Partition default.nginx_accesslog{year=2014, month=08, day=21} stats: [numFiles=1, numRows=0, totalSize=3483, rawDataSize=0]
OK
Time taken: 0.373 seconds
hive> select * from nginx_accesslog limit 100;
OK
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     /       HTTP /1 .1        200     2373    -       Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       2.78    0.004   2683    369     unix: /var/run/php5-fpm .sock     200     0.004   2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/css/base .css   HTTP /1 .1        200     940     http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   1247    373     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /a3e2e507/jquery .min.js  HTTP /1 .1        200     93636   http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.152   93976   359     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/logo .png HTTP /1 .1        200     6059    http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   6369    377     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/p02 .jpg  HTTP /1 .1        200     22177   http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   22489   376     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/p03 .png  HTTP /1 .1        200     3012    http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   3321    376     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/two-dimension-code1 .png  HTTP /1 .1        200     761     http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   1069    392     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/bg .png   HTTP /1 .1        200     11474   http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   11785   375     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/p04 .png  HTTP /1 .1        200     2860    http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.000   3169    376     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/p06 .png  HTTP /1 .1        200     74097   http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.062   74409   376     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:45 +0800      GET     8.8.8.8 /b519d8ca/image/p05 .png  HTTP /1 .1        200     132072  http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.256   132385  376     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:46 +0800      GET     8.8.8.8 /b519d8ca/image/p07 .png  HTTP /1 .1        200     207987  http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.592   208300  376     -       -       -       2014    08      21
8.8.8.8 -       22 /Aug/2014 :20:23:46 +0800      GET     8.8.8.8 /b519d8ca/image/p01 .png  HTTP /1 .1        200     310418  http: //xxx .com/ Mozilla /5 .0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit /537 .36 (KHTML, like Gecko) Chrome /38 .0.2125.0 Safari /537 .36 -       -       0.918   310731  376     -       -       -       2014    08      21
Time taken: 0.056 seconds, Fetched: 13 row(s)
hive>
 
　　　　　　方法三：通过如Flume、Kafka一类的工具监控日志数据，自动导入
　　　就不在这里细说，可以参考这两篇文章里面的实例，根据自己的业务需求修改代码：《 Flume1.5.0的安装、部署、简单应用(含分布式、与hadoop2.2.0、hbase0.96的案例) 》、《 Flume+Kafka+Strom基于分布式环境的结合使用 》
 
　　　　4)在Hive中创建Hbase可以识别的表
　　#在Hive中创建Hbase可以识别的表，相当于一张中间表了。同时将之前的测试数据导入到这张中间表，会自动同步到Hbase。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE h2b_nginx_accesslog(
key int ,
host string,
hostuser string,
times string,
requestmethond string,
requesturl string,
requesthttp string,
status string,
body_bytes_sent string,
referer string,
useragent string,
http_x_forwarded_for string,
gzip_ratio string,
request_time string,
bytes_sent string,
request_length string,
upstream_addr string,
upstream_status string,
upstream_response_time string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,log:host,log:hostuser,log:times,log:requestmethond,log:requesturl,log:requesthttp,log:status,log:body_bytes_sent,log:referer,log:useragent,log:http_x_forwarded_for,log:gzip_ratio,log:request_time,log:bytes_sent,log:request_length,log:upstream_addr,log:upstream_status,log:upstream_response_time" ) TBLPROPERTIES ( "hbase.table.name" = "h2b_nginx_accesslog" );
　　#这时在Hbase中，可以看到h2b_nginx_accesslog表自动创建了；
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
hbase(main):002:0> list
TABLE                                                                                                                                                                                                                                                   
h2b_nginx_accesslog                                                                                                                                                                                                         
1 row(s) in 0.1220 seconds
 
=> [ "h2b_nginx_accesslog" ]
/*　　查看表结构时，只会显示列族，而不会显示列。Hbase表中的每个列，都归属与某个列族。列族是表的chema的一部分(而列不是)。*/
hbase(main):003:0> describe "h2b_nginx_accesslog"
DESCRIPTION                                                                                                                                ENABLED                                                                    
  'h2b_nginx_accesslog' , { NAME => 'log' , DATA_BLOCK_ENCODING => 'NONE' , BLOOMFILTER => 'ROW' , REPLICATION_SCOPE => '0' , VERSIONS => '1' , CO true                                                                       
  MPRESSION => 'NONE' , MIN_VERSIONS => '0' , TTL => '2147483647' , KEEP_DELETED_CELLS => 'false' , BLOCKSIZE => '65536' , IN_MEMORY => 'false' ,                                                                            
   BLOCKCACHE => 'true' }                                                                                                                                                                                               
1 row(s) in 0.5890 seconds
 
hbase(main):004:0>
　　　　5)将Hive的日志表，使用自增长Id做为rowkey导入到Hbase可识别的表中
　　#将Hive的日志表（nginx_accesslog）导入到表（h2b_nginx_accesslog）中，会自动同步到hbase。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
insert overwrite table h2b_nginx_accesslog
select a.*
from ( select
rowSequence(),
host,
hostuser,
times,
requestmethond,
requesturl,
requesthttp,
status,
body_bytes_sent,
referer,
useragent,
http_x_forwarded_for,
gzip_ratio,
request_time,
bytes_sent,
request_length,
upstream_addr,
upstream_status,
upstream_response_time
from nginx_accesslog) a;
　　#导入到中间表时运行的结果,如果你也能够看到和下面类似的日志，恭喜你成功了。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
hive> insert overwrite table h2b_nginx_accesslog
     > select a.*
     > from ( select
     > rowSequence(),
     > host,
     > hostuser,
     > times,
     > requestmethond,
     > requesturl,
     > requesthttp,
     > status,
     > body_bytes_sent,
     > referer,
     > useragent,
     > http_x_forwarded_for,
     > gzip_ratio,
     > request_time,
     > bytes_sent,
     > request_length,
     > upstream_addr,
     > upstream_status,
     > upstream_response_time
     > from nginx_accesslog) a;
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1408550631561_0017, Tracking URL = http://m1:8088/proxy/application_1408550631561_0017/
Kill Command = /home/hadoop/hadoop-2.2.0/bin/hadoop job  -kill job_1408550631561_0017
Hadoop job information for Stage-0: number of mappers: 1; number of reducers: 0
2014-08-24 11:57:24,051 Stage-0 map = 0%,  reduce = 0%
2014-08-24 11:57:32,403 Stage-0 map = 100%,  reduce = 0%, Cumulative CPU 1.96 sec
MapReduce Total cumulative CPU time : 1 seconds 960 msec
Ended Job = job_1408550631561_0017
MapReduce Jobs Launched:
Job 0: Map: 1   Cumulative CPU: 1.96 sec   HDFS Read : 3734 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 960 msec
OK
Time taken: 20.378 seconds
hive>
　　　　6)到Hbase中验证效果使用get、scan、filter获取指定数据
　　#我们再登录到Hbase中看数据是否已经导入
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
hbase(main):013:0> get "h2b_nginx_accesslog" ,1
COLUMN                                                 CELL                                                                                                                                                           
  log:body_bytes_sent                                   timestamp =1408852652522, value=2373                                                                                                                            
  log:bytes_sent                                        timestamp =1408852652522, value=2683                                                                                                                            
  log:gzip_ratio                                        timestamp =1408852652522, value=2.78                                                                                                                            
  log:host                                              timestamp =1408852652522, value=8.8.8.8                                                                                                                         
  log:hostuser                                          timestamp =1408852652522, value=-                                                                                                                               
  log:http_x_forwarded_for                              timestamp =1408852652522, value=-                                                                                                                               
  log:referer                                           timestamp =1408852652522, value=-                                                                                                                               
  log:request_length                                    timestamp =1408852652522, value=369                                                                                                                             
  log:request_time                                      timestamp =1408852652522, value=0.004                                                                                                                           
  log:requesthttp                                       timestamp =1408852652522, value=HTTP/1.1                                                                                                                        
  log:requestmethond                                    timestamp =1408852652522, value=GET                                                                                                                             
  log:requesturl                                        timestamp =1408852652522, value=/                                                                                                                               
  log:status                                            timestamp =1408852652522, value=200                                                                                                                             
  log:times                                             timestamp =1408852652522, value=22/Aug/2014:20:23:45 +0800                                                                                                      
  log:upstream_addr                                     timestamp =1408852652522, value=unix:/var/run/php5-fpm.sock                                                                                                     
  log:upstream_response_time                            timestamp =1408852652522, value=0.004                                                                                                                           
  log:upstream_status                                   timestamp =1408852652522, value=200                                                                                                                             
  log:useragent                                         timestamp =1408852652522, value=Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36          
18 row(s) in 0.0440 seconds
hbase(main):015:0> get "h2b_nginx_accesslog" ,1,{ COLUMN => 'log:useragent' }
COLUMN                                                 CELL                                                                                                                                                           
  log:useragent                                         timestamp =1408852652522, value=Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36          
1 row(s) in 0.0080 seconds
　　#使用Filter过滤requesturl列中包含p04.png的记录,使用之前要先导入子串
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
hbase(main):031:0> import org.apache.hadoop.hbase.filter.CompareFilter
hbase(main):032:0> import org.apache.hadoop.hbase.filter.SingleColumnValueFilter
hbase(main):033:0> import org.apache.hadoop.hbase.filter.SubstringComparator
hbase(main):034:0> import org.apache.hadoop.hbase.util.Bytes
hbase(main):035:0> scan "h2b_nginx_accesslog" ,{FILTER => SingleColumnValueFilter.new(Bytes.toBytes( 'log' ),Bytes.toBytes( 'requesturl' ),CompareFilter::CompareOp.valueOf( 'EQUAL' ),SubstringComparator.new( 'p04.png' ))}
ROW                                                    COLUMN +CELL                                                                                                                                                    
  9                                                     column =log:body_bytes_sent, timestamp =1408852652522, value=2860                                                                                                
  9                                                     column =log:bytes_sent, timestamp =1408852652522, value=3169                                                                                                     
  9                                                     column =log:gzip_ratio, timestamp =1408852652522, value=-                                                                                                        
  9                                                     column =log:host, timestamp =1408852652522, value=8.8.8.8                                                                                                        
  9                                                     column =log:hostuser, timestamp =1408852652522, value=-                                                                                                          
  9                                                     column =log:http_x_forwarded_for, timestamp =1408852652522, value=-                                                                                              
  9                                                     column =log:referer, timestamp =1408852652522, value=http://xxx.com/                                                                                             
  9                                                     column =log:request_length, timestamp =1408852652522, value=376                                                                                                  
  9                                                     column =log:request_time, timestamp =1408852652522, value=0.000                                                                                                  
  9                                                     column =log:requesthttp, timestamp =1408852652522, value=HTTP/1.1                                                                                                
  9                                                     column =log:requestmethond, timestamp =1408852652522, value=GET                                                                                                  
  9                                                     column =log:requesturl, timestamp =1408852652522, value=8.8.8.8/b519d8ca/image/p04.png                                                                           
  9                                                     column =log:status, timestamp =1408852652522, value=200                                                                                                          
  9                                                     column =log:times, timestamp =1408852652522, value=22/Aug/2014:20:23:45 +0800                                                                                    
  9                                                     column =log:upstream_addr, timestamp =1408852652522, value=-                                                                                                     
  9                                                     column =log:upstream_response_time, timestamp =1408852652522, value=-                                                                                            
  9                                                     column =log:upstream_status, timestamp =1408852652522, value=-                                                                                                   
  9                                                     column =log:useragent, timestamp =1408852652522, value=Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Sa
                                                        fari/537.36                                                                                                                                                    
1 row(s) in 0.0320 seconds
 
hbase(main):036:0>
　　四、FAQ
　　1)如果导入的内容使用select查看时，发现是null，那可能是你的正则写的有问题，可以使用下面这个工具验证你的正则是否有问题。
　　# http://regex101.com，一个在线的正则表达式工具，很好用，如下图。
　　#在线测试通过后，将正则表达式，复制到一个文本文件中，进行两次转义字符的替换就可以使用了，第1次，将一个右斜杠去你的成两个右斜杠：'\'替换成'\\'；第二次将双引号替换成斜框双引号'"'替换成'\"'

 
　　2)如果提示“org.apache.hadoop.hive.contrib.serde2.RegexSerDe”相关的错误，请在hive中执行以下命令：
?
1
hive> add jar /home/hjl/hive/lib/hive_contrib.jar;
 
　　五、扩展阅读
 
 
　　本文中提到的Java代码下载地址： http://pan.baidu.com/s/1c0hD4n2
 
---------------------------------------
强烈谴责不尊重版权，盗用本文，不写原文出处的网站：
http://www.aboutyun.com/thread-8914-1-1.html
---------------------------------
 

转载于:https://www.cnblogs.com/lion.net/p/3932741.html

dianbutang4605
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Flume采集Nginx日志到新版Hive.rar
09-20
这里，我们指定了Nginx日志文件的路径，设置了一个内存型Channel用于短暂存储数据，并配置了HiveSink，将数据写入名为`nginx_logs`的Hive表。 然后，Flume需要与Hive、Hadoop集群进行交互，因此在运行Flume之前，...
Flume采集Nginx日志到Hive.rar
08-01
Flume采集Nginx日志到Hive的事务表时需要导入到Flume下的Jar文件，具体使用方式可参见博文：https://blog.csdn.net/l1028386804/article/details/97975539
Hadoop学习笔记之flume Nginx日志收集到HBase
望向星空
03-23 2425
简介 Nginx访问日志形式: $remote_addr – $remote_user [$time_local] “$request”$status $body_bytes_sent“$http_referer” ”$http_user_agent” 例如:192.168.241.1 - - [02/Mar/2017:15:22:57 +0800] “GET /favicon.ico HTT
学习笔记:从0开始学习大数据-39.综合实训二：hive+hbase对nginx日志分析
LinBSoft的专栏
03-01 551
本节实现 nginx日志 ->flume-> hbase ->hive ->file 即ngnix数据导入到hbase保存，使用hive创建外表使用sql查询，结果存入本地文件，当然结果也可以存入hdfs，存入文件再导入到mysql，直接通过ufd函数写入mysql，存入另外一个hive表，通过sqoop导入到mysql等，然后，通过网页读取文件生成web图表，但本节只...
hive hbase 映射表
qq_20064245的博客
12-06 362
1）创建表 create 'hive_table','json' 2） 插入了数据如下 hbase(main):012:0> scan 'hive_htable' ROW                                                          COLUMN+CELL                                          
Flume之——监听Nginx日志发送到Hive表
冰河的专栏
08-09 957
转载请注明出处：https://blog.csdn.net/l1028386804/article/details/98945268 一、环境准备 首先，有关Hadoop环境的搭建，大家可以参考博文《Hadoop之——基于3台服务器搭建Hadoop3.x集群（实测完整版）》，有关Nginx的安装和配置，可以参见博文《Nginx+Tomcat+Memcached负载均衡集群服务搭建》，有关Hiv...
Hive与HBase映射表 实现大数据的增量操作
千里之行始于足下
02-07 5314
先说下使用场景 ： HIve有Orc表 可以增删改 但是每一个事务执行的速度很慢 不适合于高并发的场景。 我之前mysql同步hadoop 创建了hive事务表 但是效果很差 这里另辟蹊径，将数据存入hbase 很快 建立映射 这样解决了效率问题。 概念 详解： HIve有 内部表 和外部表 针对于HBase来说 外部表 –》HBase已经存在这张表 hive只建立...
Nginx设置日志打印post请求参数
热门推荐
通往精英的成长之路
07-29 4万+
强烈推荐一个大神的人工智能的教程：http://www.captainbed.net/zhanghan 【前言】 我们项目的短信功能是接第三方，原来对接第三方给我们回执确认请求是get请求我们在排查问题的时候可以通过nginx的日志拿到对方给我们请求的参数；最近我们换了另外一家第三方，新的第三方给我们的确认请求是post，遇到问题排查，发现nginx没有打印具体参数，于是查阅一...
Nginx输出header到access日志文件
流氓兔的博客
03-24 1万+
Nginx输出header到access日志文件背景配置nginx配置如下http块配置如下日志效果如下 背景 为了排查线上的bug,需要在nginx的日志中，打印客户端上传上来的header头部信息，同时头部信息是自定义的。可以通过http_xx 的方式实现header输出。 我的nginx版本是1.19.3 下图是截取的header 配置 如下图，用Authorization和sec-ch-ua举例 nginx配置如下 1、Authorization，写成$http_Authorization； 2
nginx日志导入elasticsearch的方法示例
09-29
本篇文章将深入探讨如何将Nginx的日志数据导入到Elasticsearch，以便进行高效检索、分析和可视化。Nginx日志通常包含了丰富的信息，如访问者IP、请求时间、请求类型等，通过Elasticsearch可以方便地对这些数据进行...
windows下将nginx和tomcat设置为自动启动.rar
11-30
在Windows操作系统中，为了确保web服务的稳定性和连续性，常常需要将常用的服务如Nginx和Tomcat配置为自动启动。这样，当系统重启时，这些服务会自动运行，无需手动启动，极大地提高了运维效率。本教程将详细介绍...
Nginx日志自动归档备份shell脚本log.sh
最新发布
12-24
Nginx日志自动归档备份shell脚本log
Linux在有网的情况下安装nginx以及设置自启
10-11
本教程将详细介绍如何在有网络连接的环境中，一步步安装Nginx并设置其开机自启，适用于Linux的新手用户。 首先，你需要将Nginx的源代码包（例如：nginx-1.18.0.tar.gz）上传到服务器的/usr/local目录下。这通常可以...
实现自动定期删除Nginx日志的方法
09-30
为避免日志文件无限增长，我们可以采用日志轮换的方式。日志轮换是将旧日志文件重命名并创建新的日志文件的过程，这样既保留了历史记录，又防止了单个日志文件过大。在Nginx配置文件中，可以通过`logrotate`指令配合...
Nginx日志管理介绍
01-10
Nginx日志描述 通过访问日志，你可以得到用户地域来源、跳转来源、使用终端、某个URL访问量等相关信息；通过错误日志，你可以得到系统某个服务或server的性能瓶颈等。因此，将日志好好利用，你可以得到很多有价值的...
Hbase表映射成hive中
专注技术交流、咨询
01-26 6644
Hbase表映射成hive中 一、1.Hive内部表，语句如下 1、hive中建表 CREATE TABLE member( m_id string , address_contry string , address_province string , address_city string , info_age string , info_birthday string ,
Hbase中的列式表映射到hive的外表
春华秋实
04-03 4670
在做数据ETL中，可能原始数据在列式存储Hbase中，这个时候，如果我们想清洗数据，可以考虑把Hbase表映射为Hive的外表，然后使用Hive的HQL来清除处理数据，具体过程参考下面例子：步骤1，创建Hbase表 2，映射Hive表步骤一说明：cf 列簇名，只put了少量测试列 create ‘cofeed_info’,{NAME =&gt; ‘cf’, REPLICATION_SCOPE =&...
数据导入HBase最常用的三种方式及实践分析
架构师思考实践
01-07 2353
【编者按】要使用Hadoop，数据合并至关重要，HBase应用甚广。一般而言，需要 针对不同情景模式将现有的各种类型的数据库或数据文件中的数据转入至HBase 中。常见方式为：使用HBase的API中的Put方法； 使用HBase 的bulk load 工具；使用定制的MapReduce Job方式。《HBase Administration Cookbook》一书对这三种方式有着详尽描述，由
linux下shell处理nginx日志自动生成ip黑名单
01-20
#shell脚本处理nginx日志自动生成ip黑名单 ##统计访问量前10名的ip并写入文件 #!/bin/bash data=`date +%Y-%m-%d` #统计访问量最高的前10ip awk '{print $1}' '/PATH/'$data'.log' | sort -n |uniq -c | sort -rn | ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值