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行