1 cdnweblog和judgedoc hive数据仓库搭建

外部表所在目录：/usr /testspace/

临时内部表所在目录：/usr/workspace/hive/databases/default/

1.1 数据仓库搭建基本原理

在指定目录（如：/usr/workspace/hive/databases/default/）下建立一个外部表，同时外部表使用orc存储格式，并指定字段分隔符（使用外部表是为了后期对数据进行操作时，避免误操作删除hive中的数据）。

在指定的目录（如：/usr/testspace）下建立一个临时内部表，指定字段分隔符（使用临时表是为了把未压缩的数据先暂时存放到临时表中，然后从临时表中加载到外部表中）。

1.2 创建表语句

1.2.1 创建临时内部表tmp_cdnweblog

create table if not exists tmp_cdnweblog( id bigint, ip varchar(15), docid  varchar(100), pagetype  char(3), area varchar(40), m tinyint, d tinyint,  h tinyint, w tinyint, visittime string, browsetype vcarchar(50))partitioned by(dt string) row format delimited fields terminated by ‘\t’ 
   Location ‘/usr /testspace//tmp_cdnweblog’;

1.2.2 创建临时内部表tmp_judgedoc

create table tmp_judgedoc(id int, upload  string, docid string, levelonecourt int,leveltwocourt int, levelthreecourt int, levelfourcourt  int, courtname int, courttype varchar(10), doctype varchar(10), casetype varchar(10), trialprogram varchar(10), supercourtname int, courtlevel int, casereason varchar(200),refereedate string, refereeyear int, refereemonth int)row
Format delimited fields terminated by ‘\t’
Location ‘/usr /testscpace/tmp_judgedoc’;

 

1.2.3 创建外部表out_cdnweblog

create external table if  not exists out_cdnweblog( id bigint, ip varchar(15), docid  varchar(100), pagetype  char(3), area varchar(40), m tinyint, d tinyint,  h tinyint, w tinyint, visittime string, browsetype vcarchar(50))partitioned by(dt string) row format delimited fields terminated by ‘\t’ stored as orc
Location ‘/usr/workspace/hive/databases/default/out_cdnweblog’;

1.2.4 创建外部表out_judgedoc

create external table out_judgedoc(id int, upload  string, docid string, levelonecourt int,leveltwocourt int, levelthreecourt int, levelfourcourt  int, courtname int, courttype varchar(10), doctype varchar(10), casetype varchar(10), trialprogram varchar(10), supercourtname int, courtlevel int, casereason varchar(200),refereedate string, refereeyear int, refereemonth int)row
   Format delimited fields terminated by ‘\t’
   Stored as orc
   Location ‘/usr/workspace/hive/databases/default/judgedoc’;

备注：查看表的结构和详细信息语句：

describe extended tablename;
或
desc formatted tablename;

1.3 向临时内部表tmp_cdnweblog添加分区

a
lter table tmp_cdnweblog add partition(dt=’2016-03-01’);

注：dt=’指定一个日期，作为分区名’

注：judgedoc表中没有分区，就一张表

1.4 把数据从windows本地上传到Linux本地上

通过xssh、securecrt等客户端都可以实现

1.5 对数据文档进行转码

在Linux本地对windows上传过来的数据进行编码调整，保证中文能正常显示：

iconv -f  GBK -t UTF-8 weblogs20160301court.txt (待转文件) -o 0301.txt(转码后的文件)

1.6 加载数据到临时表中

注：这里是通过hdfs的方式把数据加载到临时表中的，（有分区的加载到对应的分区下）

h
dfs  dfs  -put  ./weblogs20160301court.txt  /user/tmp/tmp_cdnweblog/dt=2016-03-01;

1.7 把临时表中的数据插入到外部表中

i
nsert overwrite table out_cdnweblog partition(dt=’2016-03-01’) select ID ,IP,DocID,PageType,Area,M,D,H,W,VisitTime,BrowseType from tmp_cdnweblog where dt=’2016-03-01’;

测试：select * from out_cdnweblog where dt=’2016-03-01’;

1.8 查询结果导出到hdfs上

i
nsert overwrite directory ‘/user/mydir/’ select * from out_cdnweblog where dt=’2016-03-01’
;

1.9 数据仓库建立注意事项

1.临时表目录不能包含外部表所在的目录，否则后期删除临时内部表时，容易错删除外部表和外部表中的数据。

2.从windows上传到Linux本地，从Linux本地上传到hdfs上，这两个过程中，都有可能因为网络问题等不确定的因素导致数据传输不完整，所以每次成功插入到外部中的数据，都要进行测试一下，主要测试数据量够不够，和数据能不能正常显示。

3.对文档进行编码转换：较快的方式是使用转换语句：

   iconv -f GBK -t UTF-8 weblog20160301court.txt -o 0301.txt

但有些文档可能使用这个转换语句进行转码，可能会导致转码后数据量丢失一部分，对于这样的文档可以在windows本地利用UltraEdit等工具打开，进行转码，然后在上传到Linux。

4.往临时表中加载数据时，如果需要分区要提前把这个分区创建好， 在这里操作要这样做。

5.两个表联合查询时，数据量较小的表放在左边。

2 hive sql总结

2.1 查看表结构

describe tbname;

2.2 将一个表的统计结果插入另一个表中

Insert overwrite table tablename partition(dt=
’
2016-03-01
’
) select * from out_cdnweblog where dt=
’
2016-03-01
’

2.3 将两表联合查询的结果导入到另一张表中：

Insert overwrite table tablename select c.ID,j.docid from out_cdnweblog c JOIN out_judgedoc j ON (c.DocID=j.docid) where dt=
’
2016-03-01
’
;