hive命令行使用反射

• HIVE自带反射

1.hive> add jar /home/work/metastore.online/hive/lib/commons-codec-1.3.jar;                                                           
2.Added /home/work/metastore.online/hive/lib/commons-codec-1.3.jar to class path
3.Added resource: /home/work/metastore.online/hive/lib/commons-codec-1.3.jar
4.hive> select reflect("org.apache.commons.codec.binary.Base64","decodeBase64","NGJkYjRhLS1iMTQ2ZjQ5Ni5jb20") from test.dual limit 1; 

hive命令行配置条件

set mapred.job.priority=HIGH;
set mapred.job.groups=ods;
set mapred.job.queue.name=ods;
set mapred.map.tasks.speculative.execution = false;
set mapred.reduce.tasks.speculative.execution = false;
set mapred.job.map.capacity=1000;
set mapred.job.reduce.capacity=500;
set hive.metastore.client.socket.timeout=100000;
set hive.groupby.skewindata=true;
add jar /home/work/metastore.online/hive/lib/nova-pb-schema-1.0.jar;
add jar /home/work/metastore.online/hive/lib/hive-serde-2.3.33.jar;
add jar /home/work/metastore.online/hive/lib/hive-contrib-2.3.33.jar;

hive中如何替换默认分隔符\001

Hive建表的时候虽然可以指定字段分隔符，不过用insert overwrite local directory这种方式导出文件时，字段的分割符会被默认置为\001,一般都需要将字段分隔符转换为其它字符，可以使用下面的命令:

     sed -e 's/\x01/|/g' file > file.new

可以将|替换成自己需要的分隔符，file为hive导出的文件。

对于高版本的hive，导出语句直接支持自定义分隔符：

标准语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
SELECT ... FROM ...

row_format相关语法：

DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char](Note: Only available starting with Hive 0.13)

说明： Directory 可以是一个全路径的 url。 如果指定 LOCAL，则会将数据写到本地文件系统。 输出的数据序列化为 text 格式，分隔符为 ^A，行于行之间通过换行符连接。如果存在不是基本类型的列，则这些列将被序列化为 JSON 格式。 在 Hive 0.11.0 可以输出字段的分隔符，之前版本的默认为 ^A。

例如：
hive> insert overwrite local directory '/home/wyp/Documents/result'
hive> row format delimited 
hive> fields terminated by '\t' 
hive> select * from test;

记低版本Hive的left outer join Bug.

由于公司使用的是低版本的hive(0.8)，同时也进行了自研发。因此线上的hive版本较低，并且存在left outer join的Bug.

Bug解释：

 left outer join（或者右外关联、全外关联）最重要的特性与原则就是主表的数据不能丢，例如有A，B两个表，都有a，b两个字段：

hive> select * from A order by a,b;
OK
a b
1 1
1 2
1 3
2 3
2 4
hive> select * from B order by a,b;
OK
a b
1 1
1 2
1 3

那么以下这个左外关联查询应该得到以下的结果：

hive> select a.*,b.* from A left outer join B on A.a = B.a and A.b = B.b;
OK
a b a b
1 1 1 1
1 2 1 2
1 3 1 3
2 3 NULL NULL
2 4 NULL NULL

但是实际情况却是，低版本的Hive在外关联上的一个重要缺陷。

select A.*,B.* from A left outer join B on A.a = B.a and A.b = B.b  where A.a =1 and B.b = 2;

上述SQL多了一条where条件。根据外关联的原则：主表数据不能丢失，即A表的数据不能丢失，当on里面的条件为真时，使用B的数据进行关联，当on里面的条件为假时，B的列全部为NULL。因此这个SQL的结果应该是：

a b a b
1 1 NULL NULL
1 2 1 2
1 3 NULL NULL

关系型数据库中验证此SQL的结果正确。
但是在Hive中结果却如下：

select A.*,B.* from A left outer join B on A.a = B.a and A.b = B.b  where A.a =1 and B.b = 2;
OK
a b a b
1 2 1 2

问题分析：

Hive在做left outer join时，首先进行join，结果集中B表对应的列会存在没有数据的情况，即NULL；然后，hive进行where条件过滤，但是此时B表的部分行的列为NULL（即A无法匹配到B表列的结果），这些行会被过滤掉，导致结果集错误。
hive的执行逻辑顺序有误，where条件会对关联后的B表列进行过滤。
正确的逻辑顺序： 过滤-->关联

规避方法：

将B表的where条件作为子查询，具体如下：(对于有分区限制的情况，也需要添加至子查询)

select A.*,C.* from A left outer join (select * from B where B.b = 2 ) C on A.a = C.a and A.b = B.b ;

Hive常用命令

执行hive sql文件：hive -f filePath

删除数据库：

DROP DATABASE是删除所有的表并删除数据库的语句。它的语法如下：

DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

下面的查询用于删除数据库。假设要删除的数据库名称为userdb。

hive> DROP DATABASE IF EXISTS userdb;

以下是使用CASCADE查询删除数据库。这意味着要全部删除相应的表在删除数据库之前。

hive> DROP DATABASE IF EXISTS userdb CASCADE;

以下使用SCHEMA查询删除数据库。

hive> DROP SCHEMA userdb;

此子句中添加在Hive0.6版本。

行转列：
lateral view explode(split(site,'\\|')) site as temp_site;

join加行转列：对转后的列进行临时命名，否则会有重名问题

SELECT   group_id,
                 temp_${OperateDate}_site as site,
                 site_price,
                 start_dt,
                 end_dt
from (
SELECT            ods_site.groupid as group_id,
                  site,
                              ods_site_price.price as site_price,
          
       '${OperateDate}' AS start_dt
       ,'20991231' AS end_dt
  FROM ods_site LEFT OUTER JOIN ods_site_price
                     ON ods_site_price.group_id = ods_site.groupid                          
WHERE ods_site.yymmdd=${OperateDate} and ods_site_price.yymmdd=${OperateDate})  
lateral view explode(split(site,'\\|')) site as temp_${OperateDate}_site   ;

添加分区表语法（表已创建，在此基础上添加分区）：
ALTER TABLE table_name ADD
partition_spec [ LOCATION 'location1' ]
partition_spec [ LOCATION 'location2' ] ...

for example:
ALTER TABLE day_table ADD
PARTITION (dt='2008-08-08', hour='08')
location '/path/pv1.txt'
 
删除分区语法：
ALTER TABLE table_name DROP
partition_spec, partition_spec,...
用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。

for example:
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

数据加载进分区表中语法：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

for example:
LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');
当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录

数据装载：

http://blog.csdn.net/macyang/article/details/7259416

insert into 和overwrite的用法：
INSERT INTO TABLE tablename1 [PARTITION \
(partcol1=val1, partcol2=val2 ...)] \
select_statement1 FROM from_statement;
INSERT OVERWRITE TABLE tablename1 [PARTITION \
(partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] \
select_statement1 FROM from_statement;


两种方式的相同点：
1.两个表的维度必须一样，才能够正常写入
2.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，比如如果查询出来的数据类型为int，插入表格对应的列类型为string，可以通过转换将int类型转换为string类型；但是如果查询出来的数据类型为string，插入表格对应的列类型为int，转换过程可能出现错误，因为字母就不可以转换为int，转换失败的数据将会为NULL。

不同点：
1.insert into是增加数据
2.insert overwrite是删除原有数据然后在新增数据，如果有分区那么只会删除指定分区数据，其他分区数据不受影响