赞
踩
前面已经讲了如何部署在hadoop集群上部署hive,现在我们就做一个很小的实例去熟悉HIVE QL.使用的数据是视频播放数据包括视频编码,播放设备编码,用户账号编码等,我们在这个数据基础上做一些简单查询统计等。
点击此处下载实例样本数据
这是20170901 14点的部分播放日志
实际上我这块数据是通过flume收集日志到hdfs上的,后续我也会简单介绍一下怎么通过flume收集日志到hdfs。当然,下载我们的样例数据以后也可以通过${HADOOP_HOME}/bin/hdfs dfs -put命令
建表 :
- create external table log_video_play_request (logindex string,request_date string,video_auiddigest string,puiddigest string ,
- ver int,auiddigest string comment 'account identify',duiddigest string comment 'device identify',
- device_sign string ,xy_app_key string,ip string,port bigint,user_agent string, fromparameter string,
- zone bigint,sns_name string,sns_type bigint,country_code string,consume_country_code string,
- play_duration bigint,video_duration bigint,trace_id string,review_state int)
- partitioned by (day string ,hour string) row format delimited
- fields terminated by '&'
- stored as textfile
- location '/user/admin/logs/video_play'
接下来就是hive表加载数据了,大家可以参考这篇博文Hive数据加载(内部表,外部表,分区表)
在这里大家在hive里面执行alter table log_video_play_request add partition(day='20170901',hour='14');
注:select * from .. limit 10;试一下,如果结果为空,使用Load data inpath '/user/admin/logs/vide_play/20170901/14' overwrite into table log_video_play_request partition(day='20170901',hour='14')
通用建表语句
- CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table\_name
- [(col\_name data\_type [col\_comment],...)]
- [COMMENT table\_comment]
- [PARTITIONED BY (col\_name data\_type [col\_comment], col\_name data\_type [COMMENT col\_comment],...)]
- [ROW FORMAT row\_format]
- [STORED AS file\_format]
- [LOCATION hdfs\_path]
删除表: DROP TABLE table_name
向数据表中加载文件:
- LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE]
- INOT TABLE table\_name
- [PARTITION (partcol1=val1,partcol2=val2 ...)]
将查询结果插入数据表中
- INSERT OVERWRITE TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]
- select ....
多路插入: multi insert
- FROM src
- insert overwrite table1 select ... where ...
- insert overwrite table2 select ... where ...
多路插入还是很常见并且非常好的应用,一张日志表往往有多次的计算,用multi insert 可以节省多次的IO开销
根据我们上面的log_video_play_request
- select * from log\_video\_play\_request where day = 20170901 limit 10;
- #查看各个模块播放
- select count(1) as total ,fromparameter from log\_video\_play\_request where day = 20170901 group by fromparameter order by total desc limit 100;
- #查看top创作者(视频被播放次数最多的用户)
- select count(1) as total,video\_auiddigest from log\_video\_play\_request where day = 20170901 group by video\_auiddigest order by total desc limit 100;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。