hive常用命令

建表

  • 重要的数据建表最好为externel ,在drop表时,不会drop对应hadoop文件,在重新create table后能够快速恢复数据。
  • 可以显示指定format为orc,注意最后3行顺序
  • location可以不写,但是建完后需要检测存储路径是否和预期一致
1
2
3
4
5
6
7
8
9
10
11
12
13
create [external]
table if not exists app.test
(
sku string,
csku string
)
partitioned by
(
dt string
)
stored as orc
location 'xxxx/app.db/test'
tblproperties ('orc.compress'='SNAPPY');

增加字段(列)

  • 添加字段(在所有存在的列后面,但是在分区列之前添加一个字段)
    • 不添加说明:
      alter table 表名 add columns (字段名 字段数据类型, 字段名 字段数据类型)
    • 添加说明:
      alter table 表名 add columns (字段名 字段数据类型 comment '字段说明', 字段名 字段数据类型 comment '字段说明')

增加表记录

  • 添加一条记录:insert into 表名(字段名[,字段名]) values('值'[,'值']);
  • 或使用select

    1
    2
    insert into 表名(字段名[,字段名]) 
    select ‘...’

    使用insert into向表中追加数据,可能追加的数据与表中已有的数据相同,不会覆盖,因此会出现相同的两条记录。

  • 添加记录(同记录覆盖)
    使用insert overwrite
    注意 insert overwrite table 表名 select…..
    table不能省略。
    会将之前的记录全部删除,即时与追加的记录不同

增加分区

1
2
3
4
5
6
7
ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1'; 

-- 一次添加一个分区
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (dt='20130101') LOCATION '/user/hadoop/warehouse/table_name/dt=20130101';

-- 一次添加多个分区
ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';

删除表格

  • 删除表格:drop table 表名;
  • 删除所有记录(行),不删除表结构
    truncate table 表名;

删除分区

1
2
3
4
5
6
ALTER TABLE login DROP IF EXISTS PARTITION (dt='2008-08-08');

ALTER TABLE page_view DROP IF EXISTS PARTITION (dt='2008-08-08', country='us');

-- 删除多个分区
ALTER TABLE app.xxx DROP IF EXISTS PARTITION (dt<'2019-04-01');

删外部表数据

外部表,直接rm -r hdfs数据,对表show partitions $table_name 分区依然存在只是没有数据。

使用alter table app.app_discovery_content_info_da_tag drop partition (dt='${drop_dt}')show partitions $table_name分区不存在,但是hadoop fs -ls 路径,改分区数据依然存在。

方案一:

将表改为内部表external =false,删分区(hdfs数据会删掉),再改回为外部表external=True。(对外部表的其他操作(如rename)也可以使用这个模式)

1
2
3
4
5
ALTER TABLE db_name.test_1 SET TBLPROPERTIES('EXTERNAL'='FALSE'); 

ALTER TABLE app.xxx DROP IF EXISTS PARTITION (dt<'2019-04-01');

ALTER TABLE db_name.test_1 SET TBLPROPERTIES('EXTERNAL'='TRUE');

方案二:

先删分区,再用rm -r删数据

1
2
ALTER TABLE some.table DROP IF EXISTS  PARTITION (part="some")
hdfs dfs -rm -R /path/to/table/basedir

修改表记录

  • update 修改表记录
    • UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    • 例如 update tt set name='Joe', salary=70000, managerid=3 where id=1;

修改字段名(列名)

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE test_change (a int, b int, c int);

-- 修改列名a为a1
ALTER TABLE test_change CHANGE a a1 INT;

-- 修改列名a为a1,将a1的数据类型置为string,并将a1放在b列后
-- 表的新结构: b int, a1 string, c int
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;

-- 修改列名b为b1,并将其置为第一列
-- 表的新结构: b1 int, a string, c int
ALTER TABLE test_change CHANGE b b1 INT FIRST;

修改表属性

1
2
3
4
5
-- 内部表转外部表 
alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE');

-- 外部表转内部表
alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE');

表的重命名

1
2
-- 非外部表会同时修改location
ALTER TABLE table_name RENAME TO new_table_name

外部表需要修改location(更简洁的方法是,先将外部表转为内部表,重命名后再转为外部表)

  • mv 表数据存储的hdfs目录:例如:hdfs dfs -mv /user/xxx/xx.db/test_orc/* /user/xxx/xx.db/test(将test_orc下的数据全部移动到test目录下)
  • hdfs dfs -rm -r /user/mart_mobile/dev.db/test_orc(删除空目录)
  • 移动之后执行set hive.msck.path.validation=ignore;msck repair table dev.test;修复分区

修改分区

1
2
3
4
5
6
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new location";

ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

-- 将19分区修改到18(外部表的location可能不会变,待验证)
alter table app.xxxx partition(dt='2019-01-19') rename to partition(dt='2019-01-18');

修改location

一般location在create table if not exist后,不会被触发,而如果删除表则会删除location,因此如果新建表的location错误指向了已经存在的location,需要手动修改新表location

  • 命令行新建location目录: hadoop fs -mkdir 'hdfs://ns9/user/xxxx/xxx/app.db/table_name_new'
  • hive
  • alter table 表名 set location 'hdfs://ns9/user/xxxx/xxx/app.db/table_name_new'
  • 验证:show create table 表名查看LOCATION是否更新

常用查询语句

  • 查看表字段介绍: desc 表名
  • select列名:https://stackoverflow.com/questions/26181454/just-get-column-names-from-hive-table
    show columns in 表名
  • 查某一列:select 列名 from 表名
  • order by 排序(string类型字典序,数值类型按大小排)
    • order by colname 默认升序
    • order by colname desc 降序
  • limit 返回对应行
    • SELECT * FROM table LIMIT 3; 返回前3行
    • SELECT * FROM table LIMIT 0,3; 返回前3行
    • SELECT * FROM table LIMIT 5,10; // 从第6行开始,最多返回10行(可能后面的数据不到10行)。即检索记录行 6-15
    • SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last
  • 空值数计算:select sum(if(a is null,1,0)) from ...
  • 空值率计算:select avg(if(a is null,1,0)) from ...
  • 查询某个字段按字符分割后长度:size(split(sim_recall,','))
  • 直接查看表信息
    • 通过执行log:Table 表名 stats: [numFiles=4, numRows=623232, totalSize=234324, rawDataSize=32243]
    • 通过explain (发现有时通过这种方式查看numRows和count(*)结果不一致)
  • sql的模糊匹配
    • %:表示任意0个或多个字符。可匹配任意类型和长度的字符
      select distinct * from app.xxxxxx where tb_name like 'app.xxx%';%不能少(匹配a等)
    • _: 表示任意单个字符。匹配单个任意字符

查询重复的项

  • 定位重复行可先group by,记录下count,再对count排序,选count>1的
1
2
3
4
5
6
7
8
9
10
11
select
sku
from
(
select
sku, count(*) as cnt
from
app.xxxx
group by sku
)a
where a.cnt > 1 order by cnt desc limit 100;
  • 数据中有两列A和B,查看是否存在以下情况:
    A B
    1 2
    2 1
    可以先将AB按大小拼起来,再查询
1
2
3
4
5
6
7
8
9
10
11
select
sku,
csku,
round(score, 10) score,
if(sku>csku,concat_ws(',',csku,sku),concat_ws(',',sku,csku)) sku_csku_pair
from
app.xxx
where
dt = '2018-12-25'
and tab_name = 'B'
and sku_sr_type = '0'

tips

  • 使用严格模式优点
    • 禁止不指定分区查询
    • 避免join产生笛卡尔积
  • 通过时间可以定位hadoop中文件是否被修改,在同一个集市中,如果有其他表指定的location错误,则很可能被修改。
  • 如果location指定错误,例如新表指定到旧表路径中,那么会影响旧表。如果直接drop新表,而新表不是external表,则会删除location中文件,无法恢复。所以,需要修改新表的location,在修改之前不要对新表做任何操作。
  • concat_ws 需要数据类型为 string
  • null
    • where col=NULL 不会返回结果
    • where col is null 会返回为空的列
  • join
    • left join on 保留左边表关键字信息(即使右表中没有匹配,也从左表返回所有的行 ),可以使用where 右表.col is not null实现join效果
    • right join on 保留右边关键字信息(即使左表中没有匹配,也从右表返回所有的行)
    • inner join 两边都有才保留,也可写为join
    • full join 只要其中一个表中存在匹配,就返回行
  • 这么写中间表(${subSql}) t只查了一遍
1
2
3
4
5
6
7
8
9
10
11
12
from(${subSql}) t
insert into table ${num_rows_tb} partition
( tb_name = '${tb_name}'
)
select ct, '${select_dt}', sysdate()
insert overwrite table ${null_rate_tb} partition
( tb_name = '${tb_name}'
)
select
split(kv, ':') [0] as k,
cast(split(kv, ':') [1] as double) as v,
'${select_dt}' as select_dt lateral view explode(split(kvs, ',')) kvs as kv;"
  • 注意 lateral view explode要写在from的后面,上述如果不将from写在最上方,应该写为如下,而不能将from放在最后
1
2
3
4
5
6
 select
split(kv, ':') [0] as k,
cast(split(kv, ':') [1] as double) as v,
'${select_dt}' as select_dt
from(${subSql}) t
lateral view explode(split(kvs, ',')) kvs as kv;
  • map数过大不一定运行快,过大的map数会被pending。
  • 在join时,如果on的是double数据类型,需要注意小数的精度不同可能造成=判定失败。可以用round(score, 2) score 只保留2位小数后再对比。

hdfs文件操作命令

常用

  • hadoop fs -mkdir path创建文件夹
  • hadoop fs -put local_path hdfs_path 上传本机的HDFS文件。local_path指非hdfs路径,例如/home/name
  • hadoop fs -get hdfs_path local_path 把HDFS的文件下载到本机
  • hadoop fs -cat file_name 读取HDFS文件
    1. hadoop fs 和hdfs dfs 作用一样。都可以在本机上查看HDFS文件。
    2. HDFS下的文件可以压缩存储,这样能够减少表查询时对Hadoop集群的IO。

从文件导数据到表

  • 使用put将test.txt文件放到hdfs中:hadoop fs -put '/home/test/test.txt' /usr/xxxx/rec/xxx_data/test
  • 使用load hdfs地址将数据导入表中:load data inpath '/user/xxxx/rec/xxx_data/test/test.txt' into table app.test;
  • 导入成功,可以在select limit表检查确认一下。

hadoop fs -ls [path]查看HDFS文件名

后面不加目录参数的话,默认当前用户的目录。ls会显示文件详细信息,如果只想ls出文件名,可以这么写:(参考 https://stackoverflow.com/questions/21569172/how-to-list-only-the-file-names-in-hdfs

1
hadoop fs -ls <HDFS_DIR>|cut -d ' ' -f17

或者先sed '1d' 删除第一行(第一行是总述信息),将多个空格替换为一个空格(sed 's/要被取代的字串/新的字串/g')再cut:

获取文件名:hadoop fs -ls | sed '1d;s/ */ /g' | cut -d\ -f8

获取文件名最后一列(这里是dt=2018-10-27):hadoop fs -ls | sed '1d;s/ */ /g' | cut -d\ -f8 | xargs -n 1 basename

更优解:hadoop fs -ls /tmp | sed 1d | perl -wlne'print +(split " ",$_,8)[7]'

最终采用:hadoop fs -ls | sed '1d;s/ */ /g' | cut -d\ -f8 获取文件名列表

1
2
3
4
5
# 遍历文件。
hdfs dfs -ls ${path} | sed '1d;s/ */ /g' | cut -d\ -f8| while read line
do
echo ${line}
done

对每个文件,$(echo ${line}|cut -d "=" -f 2) 取分区数据。以=分割取第2个元素

补充:sed介绍: https://www.cnblogs.com/ggjucheng/archive/2013/01/13/2856901.html

常用设置

  • 集市限制小文件个数,可在脚本中加以下参数合并小文件:
1
2
3
4
set hive.merge.mapfiles = true
set hive.merge.mapredfiles = true
set hive.merge.size.per.task = 256000000
set hive.merge.smallfiles.avgsize = 104857600