hive使用技巧：把很多小文件导入一张表中、显示在文件中位置和行数等。

1.使用MSCK命令导入输入到hive表

我们有时候会遇到很多小文件需要导入到一张hive表里面，但是一个个导入非常麻烦。

假设创建一个外部表，这个表在hdfs的order文件夹里，但是这个文件夹现在是空的。所以用select * 是没有数据的。

CREATE EXTERNAL TABLE order(
    order STRING
  , time  STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/hive/order';

我们通过hdfs dfs -put 方式把数据导入到hdfs的order 文件夹下。

然后在hive里面输入命令 msck repair table order；

现在再select * 就有数据了。通过这种方式，可以很快导入数据到表格里面，而不用一个个alter ...add partition来导入。

2.使用describe formatted order_creates;可以查看表具体信息，包括位置，分隔符等。formatted不写就看简单点的。

3.关闭动态分区模式

 如果insert语句报错的时候Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

可以先进行如下设置在操作。set hive.exec.dynamic.partition.mode=nonstrict;

有关动态分区表模式

    hive.exec.dynamic.partition=false  #默认不允许动态分区表
    hive.exec.dynamic.partition.mode=strict #设置动态分区模式
    hive.exec.max.dynamic.partitions.pernode=100  #动态分区在每个map、reducer里面创建数量
    hive.exec.max.dynamic.partitions=1000         #动态分区被创建总数
    hive.exec.max.created.files=100000 #所有mapper创建最大HDFS文件数           
    hive.error.on.empty.partition=false  

4.查看每行在文件中的位置和行数

select INPUT__FILE__NAME,col1,col2, round(BLOCK__OFFSET__INSIDE__FILE / (length(col1) + length(col2) + 2) + 1) from  tablename; 

这里的BLOCK_OFFSET_FILE表示在文件中的位置，除以两个列宽度加2（行首和行尾的\t键），最后加1表示第0行显示成1.

5.修改文件格式

ALTER TABLE order PARTITION (col1='2014-06') SET SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe';
ALTER TABLE order PARTITION (col1='2014-06') SET FILEFORMAT textfile;
ALTER TABLE order PARTITION (clo1='2014-07') SET FILEFORMAT INPUTFORMAT 'parquet.hive.DeprecatedParquetInputFormat' OUTPUTFORMAT 'parquet.hive.DeprecatedParquetOutputFormat';

6.使用collect_set（不允许重复），collect_list(允许重复)对结果进行分组显示

select a, collect_set(b) as orders  from tablename  group by a;

在impala中可以使用group_concat（b,'|'）用|把分组结果连接一起显示出来。

7.使用array数组

假设原始数据格式是   a b   c|d|e    ,其中第三个字段是数组，用|作为分隔符，建表的时候使用collection item by

CREATE EXTERNAL TABLE ad_list (
    a    STRING
  , b      STRING
  , c array<STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '|'
LOCATION '/tmp';

8.使用LATERAL VIEW 横向视图 和explode函数进行横向展开

用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias

select a, c from tablename LATERAL VIEW explode(c1)  t AS c;这里的c1就是原表中的数组。

这种情况下，如果数组里面是空的，这一行就不会显示，要想显示，可以使用LATERALVIEW OUTER概念有的类似out join 。

9.使用sort_array()函数和array_contains()

sort_array()函数对数组进行排序，array_contains（）函数判断是否包含字符。

10.hive的map类型

创建表的时候，需要加上MAP KEYS TERMINATED BY ':'，可以加在location之前。下面介绍一下函数

创建map：map(),str_to_map()  

 例子，items这个字段是map类型的数据，比如a:2|b:5|c:7这种类型的：

CREATE EXTERNAL TABLE orders (
    user_id   STRING
  , items     map<STRING,BIGINT>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
LOCATION '/tmp';

取数：map_keys(),map_values()

使用：map与lateral view

select user_id, order_id, item, amount from f_orders LATERAL VIEW explode(items) t AS item, amount;

11.hive的窗口和分析函数

row_number()：取行号，比如取最大的三个where row_number<3,也可以row_number() OVER (PARTITION BY user_id ORDER BY weight DESC)

rank（）和dense_rank（）：排名

12.使用translate（）函数对字符进行替换，以便于保护用户隐私，比如邮箱替换，把u换成1，把s换成2.....

translate(email, 'userfxgmail1234567890', '1234567890userfxgmail')

13.增加delete和update

如果删除数据的时候遇到报错

FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

可以修改hive-site.xml增加如下信息，之后重启hive服务。

<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
  </property>
    <property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
  </property>
    <property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
  </property>
  <property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
  </property>
    <property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
  </property>
  <property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
  </property>
  <property>
	<name>hive.in.test</name>
	<value>true</value>
  </property>

如果要支持delete和update，则必须输出是AcidOutputFormat然后必须分桶。 
而且目前只有ORCFileformat支持AcidOutputFormat，不仅如此建表时必须指定参数('transactional' = true) 

如clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true')

14.hive窗口函数进阶

分析函数用法over(partition by xxx order by yyy rows between zzz)

unbounded preceding即第一行是指表中的第一行, unbounded following即最后一行是指表中的最后一行

• CUME_DIST（累积分布、）  小于等于当前行值的行数 / 总行数：CUME_DIST() OVER(PARTITION BY xxx ORDER BY xxx)
• PERCENT_RANK  当前rank值-1 / 总行数-1：CUME_DIST() OVER(PARTITION BY dept ORDER BY sal)
• NTILE 将窗口分成n片：ntile (num)  over ([partition_clause]  order_by_clause)
• LEAD(col, n, 0)  窗口内下n行值（后两个参数可以省略、第三个参数是 超出记录窗口时的默认值）：
• LAG(col, n , default)  窗口内上n行值：lag(name,1,0) over ( order by id ) ,可以找到name上个月对比，假设每月一行
• FIRST_VALUE 窗口内第一个值
• LAST_VALUE  窗口内最后一个值

以上函数都可以跟分析函数一起使用

• CURRENT ROW  当前行：可以与rows between and一起使用
• N-rows PRECEDING  前面N行
• N-rows FOLLOWING  后面N行