Hive日常操作

模糊搜索表:show tables like '*name*';

查看表结构:desc table_name
查看表的详细信息:desc formatted table_name

查看分区信息:show partitions table_name;
根据分区查询数据:select table_coulm from table_name where partition_name = '2018-11-01';
删除分区:alter table table_name drop partition(dt='2018-11-01');

修改表名: alter table table_name rename to table_name_new;
复制表结构:CREATE TABLE table_name LIKE table_name_old;
添加字段:alter table table_name add columns(site_uv bigint comment '到站UV');

查看hdfs文件信息:dfs -ls /user/hive/warehouse/table_name;

查询结果导出到文件

1
2
3
4
5
6
7
8
insert overwrite local directory '/tmp/output' select * from table_name;
insert overwrite local directory '/tmp/output'
row format delimited fields terminated by ','
select * from table_name

-- 或者

hive -e 'select * from table_name' > /tmp/output

kill hive job:hadoop job -kill job_201811011453_58225

<> 这个符号是不等于的意思,相当于 !=
<=>代表ifNull( a != b,false) 如果 a!=b 的结果为null,则最终结果为false,简单理解就是 对 “!=” 做了一个非空判断

hive查询显示列名

1
2
3
set hive.cli.print.header=true; // 打印列名
set hive.cli.print.row.to.vertical=true; // 开启行转列功能, 前提必须开启打印列名功能
set hive.cli.print.row.to.vertical.num=1; // 设置每行显示的列数

从文件加载数据进表(OVERWRITE覆盖,追加不需要OVERWRITE关键字)

1
2
3
4
LOAD DATA LOCAL INPATH 'dim_config.txt' OVERWRITE into table default.dim_config;

--从查询语句给table插入数据
INSERT OVERWRITE TABLE dim_config PARTITION(ds) select * from default.dim_config_old where ds='2018-11-01';

仅复制表结构
CREATE TABLE new_table as select * from old_table where 1=0;

复制表结构和索引
CREATE TABLE new_table LIKE old_table;

自定义udf函数

  1. 继承UDF类
  2. 重写evaluate方法
  3. 把项目打成jar包
  4. hive中执行命令add jar /home/root/dw_etl/pub/udf/GetProperty.jar;
  5. 创建函数 create temporary function get_pro as ‘bi.Get_Property’;

开启简单模式不启用mr: set hive.fetch.task.conversion=more;

hive默认分隔符 \001

hive命令行中查看当前hive环境变量 !env

hive命令行中查看当前hive及hadoop环境变量 set -v

sqoop导出sequencefile格式的文件时需要特殊处理(自行百度),默认需要使用textfile

Hive将一行记录拆分成多行

1
2
3
select username,browse_product
from default.user_browse
lateral view explode(split(product, ',')) virtual_table_name as browse_product;

Hive查询时使用中文别名报错:

1
2
3
4
5
hive> select orderid as 订单号 from bdl_order_eb limit 1;

FAILED: ParseException line 1:18 character '订' not supported here
line 1:19 character '单' not supported here
line 1:20 character '号' not supported here

解决办法:中文字符前后加上单反引号

1
hive> select orderid as `订单号` from bdl_order_eb limit 1;

sum(cnt) over(partition by f_month,appd,life_type order by f_ds asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cnt
按天统计维度,每天累加从一号到当天的数值。如果某一天某个维度缺失,会照成这个维度的统计丢失,数据会少

HIVE SQL 中报错SemanticException The abstract syntax tree is null
报错SemanticException The abstract syntax tree is null是因为用union all的时候两张表的字段名不一致而导致的报错。必须检查来保证两张表的字段名一致。

hive子查询时的错误Unsupported SubQuery Expression : Correlating expression cannot contain unqualified column references
原因:hive子查询列名要具有确定性,需要给表加个别名 select b from tablename t1 where t1.b in (select b from t2);

hive不支持非等值关联,例如以下写法就是不对的

1
2
3
select t.*
from table1
left join table2 on t.date>t1.date

LEFT SEMI JOIN (左半连接)是 IN/EXISTS 子查询的一种更高效的实现。
1、select的字段只能是坐标中的字段
2、left semi join 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
3、left semi join 是只传递表的 join key 给 map 阶段,因此left semi join 中最后 select 的结果只许出现左表。
4、因为 left semi join 是 in(keySet) 的关系,遇到右表重复记录,左表会跳过,而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条,join 会产生多条,也会导致 left semi join 的性能更高。

hive优化原则

  • hive优化原则,减小map数,减少job数

hive优化参数

1
2
3
4
5
set hive.merge.mapfiles=true;
set hive.merge.mapredfiles=true;
set mapred.max.split.size=268435456;
set hive.merge.size.per.task=268435456;
set hive.merge.smallfiles.avgsize=134217728;

hive异常定位

  • hive创建有分区到外部表时,一定要先增加分区,然后才能查到数据,如果不手动新增分区是查不到数据的 LTER TABLE adcall ADD IF NOT EXISTS PARTITION(day='$date', hou='$hour');
  • hive查询时需要估算下结果的大小,特别时有子查询时,会把查询结果存放到本地,防止中间结果太多硬盘满了
  • hive查询不动了,假死,首先查看NodeManager的日志,多半是NodeManager挂掉了,或者处于unhealthy state状态
  • job执行报错,可能就是nodemanager的问题,而不是resourcemanager的问题

动态导入分区数据:

1
2
3
4
5
6
7
导一天的数据可以这样
insert into new_table_name partition (ds='2018-12-15')
select * from old_table_name where ds='2018-12-15'

-- 导入多天的数据
insert into new_table_name partition (ds)
select * from old_table_name where ds >= '2018-12-15'

create table … as … 比源表多出来几条记录
这是由于源表中有部分字段有换行符,导致查询是会多出来几条记录,一般在sequencefile中出现
解决办法:create table时指定orc格式,create table … stored as orc as …