1、常用命令

命令	说明
hive --version	查看hive版本
select version()	查看hive版本
show databases	查看数据库
show tables like 'test_*'	查看表
show functions	查看函数
show create table tbname	查看建表语句
show partitions tbname	查看分区
desc tbname	查看表字段注释
desc extended tbname	查看表字段注释
desc formatted tbname	查看表字段注释
show locks extended	查看锁
unlock table tbname	释放锁
msck repair table tbname	刷新所有分区元数据
drop database db_name cascade	删表删库
hive -hiveconf bizdate=20180101 -f test.hql	传参执行 hql 文件
ANALYZE TABLE tablename COMPUTE STATISTICS -- noscan	更新统计信息
alter table tablename set FILEFORMAT orc	修改表存储格式

2、常用调优设置

-- 启用本地模式
set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=52428800;
set hive.exec.mode.local.auto.input.files.max=10;

-- hive开启动态分区写入
set hive.exec.dynamici.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.max.dynamic.partitions.pernode=10000;

-- 使用spark引擎
-- spark引擎参数
-- 内存占用 cores * memory * instances 
-- 核数占用 cores * instances + 1 (driver)
set hive.execution.engine=spark;
set spark.executor.cores=2;
set spark.executor.memory=1G;
set spark.executor.instances=8;

-- hive 不能直接设置 map 数，只能通过设置块大小间接实现控制 map 数
-- 合并输入端的小文件，减少map数
set mapred.max.split.size=256000000;
set mapred.min.split.size.per.node=256000000;
set mapred.min.split.size.per.rack=256000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

--设置内存缓冲区大小，很多时候可以解决内存不足问题
set io.sort.mb=10;

--最大可用内存
set mapreduce.map.java.opts=-Xmx2048m;
set mapreduce.reduce.java.opts=-Xmx2048m;

--join优化 数据量大时使用
set hive.auto.convert.join=true;

--设置任务数
set mapred.reduce.tasks=20;

--任务并行，会消耗更多资源
set hive.exec.parallel=true;

3、常用建表语句

内部表/分区表

CREATE TABLE `t_gfecp_bigdata_admin_penalty`(
  `id` string COMMENT '主键',
  `case_name` string COMMENT '案件名称',
  `case_no` string COMMENT '行政处罚决定书文号',
  `penalty_name` string COMMENT '被处罚者',
  `penalty_type` string COMMENT '被处罚者类型：法人、个人、其他（这里一般指法人）',
  `authority` string COMMENT '执法部门',
  `penalty_date` string COMMENT '作出行政处罚的日期',
  `case_detail` string COMMENT '行政处罚决定书（正文）',
  `person_in_charge` string COMMENT '法定代表人或单位负责人',
  `notice_date` string COMMENT '公告日期',
  `severity` string COMMENT '非常严重、严重、一般',
  `detail_url` string COMMENT '处罚信息原文链接URL',
  `file_name` string COMMENT '附件名称',
  `file_id` string COMMENT '附件id',
  `spider_date` string COMMENT '数据采集日期',
  `site_id` string COMMENT '网站id',
  `enterprise_id` string COMMENT '企业ID：当前非必填，先用“被处罚者”与企业表的“企业名称”做关联',
  `insert_user` string COMMENT '创建者',
  `insert_time` string COMMENT '创建时间',
  `update_user` string COMMENT '修改者',
  `update_time` string COMMENT '修改时间',
  `data_dt` string COMMENT '数据日期',
  `keyid` string COMMENT '',
  `all_item` string COMMENT '正文带标签',
  `spider_name` string COMMENT '爬虫名称',
  `file_path` string COMMENT '文件路径',
  `area_name` string COMMENT '',
  `site_desc` string COMMENT '')
COMMENT '行政处罚结果信息'
PARTITIONED BY (
  `bizdate` string COMMENT '业务日期')

内部表/指定分隔符

CREATE TABLE `edw.f_gf_check_json`(
  `bizdate` string COMMENT '业务日期',
  `detail_url` string COMMENT '详情地址',
  `content` string COMMENT 'json串内容')
PARTITIONED BY (
  `check_tab_name` string COMMENT '表名',
  `check_site_id` string COMMENT '站点id')
row format delimited
fields terminated by '\001'
lines terminated by '\n'

内部表/snappy 压缩

CREATE EXTERNAL TABLE `spider.zhifang_list`(
  `keyid` string,
  `tit0` string COMMENT '字段注释',
  `tit1` string COMMENT '字段注释',
  `txt` string COMMENT '字段注释',
  `price` string COMMENT '字段注释',
  `name` string COMMENT '字段注释',
  `detail_url` string COMMENT '字段注释',
  `pkey` string COMMENT '等于 zhifang_detail.fkey',
  `pagenum` string COMMENT '下单页码',
  `sitename` string COMMENT '站点名称',
  `bizdate` string COMMENT '业务日期',
  `ctime` string COMMENT '入库时间',
  `spider` string COMMENT '爬虫名称')
STORED AS Parquet -- 或者 orc
TBLPROPERTIES ("orc.compress"="SNAPPY");

外部表/hbase

CREATE EXTERNAL TABLE edw.test_turboway_hbase(
  `keyid` string COMMENT 'from deserializer',
  `title` string COMMENT 'from deserializer',
  `bizdate` string COMMENT 'from deserializer',
  `loginid` string COMMENT 'from deserializer')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED BY
  'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
  'hbase.columns.mapping'=':key,cf:title,cf:bizdate,cf:loginid',
  'serialization.format'='1')

外部表/elasticsearch

--根据 id 追加更新，无法删除
add jar hdfs:/user/hive/es_hadoop/elasticsearch-hadoop-5.4.3/dist/elasticsearch-hadoop-hive-5.4.3.jar;

--es_行政处罚结果信息
DROP TABLE IF EXISTS edw.f_gf_ent_penalty_info_es;
CREATE EXTERNAL TABLE IF NOT EXISTS edw.f_gf_ent_penalty_info_es(
id  string  comment '技术主键'
,case_name  string  comment '案件名称'
,case_no  string  comment '行政处罚决定书文号'
,penalty_name  string  comment '被处罚者'
,penalty_type  string  comment '被处罚者类型'
,authority  string  comment '执法部门'
,authority_clearing_text  string  comment '执法部门_清洗_text'
,authority_clearing_keyword  string  comment '执法部门_清洗_keyword'
,penalty_date  string  comment '行政处罚日期'
,case_detail  string  comment '行政处罚决定书'
,person_in_charge  string  comment '负责人'
,notice_date  string  comment '公告日期'
,detail_url  string  comment '详情链接'
,file_name  string  comment '附件名称'
,file_id  string  comment '附件id'
,corp_matching_degree  string  comment '企业信息匹配度'
,spider_date  string  comment '数据采集日期'
,bizdate  string  comment '业务日期'
,site_id string comment'网站id'
,data_source_area string comment'数据来源地区'
,data_source  string  comment '数据来源说明'
,data_sort_num  string  comment '数据排序号'
,del_flag  string  comment '数据逻辑删除标志'
,etldate  string  comment '数据加载更新日期'
)comment 'es_行政处罚结果信息'
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES('es.resource' = 'idx_f_gf_ent_penalty_info/f_gf_ent_penalty_info',
'es.nodes'='172.16.123.80 ',
'es.port'='8200',
'es.mapping.id' = 'id',
'es.write.operation'='upsert',
'es.net.http.auth.user'='elastic',
'es.net.http.auth.pass'='changeme'
);

4、常用语句

-- 加载数据文件，相当于直接 put 覆盖到指定 hdfs 目录
LOAD DATA LOCAL INPATH '/home/getway/tmp/way/data_test.txt'
OVERWRITE  INTO TABLE spider.test_way_20200818 ;

-- 创建临时表
create temporary table as select id from tb;

-- 分区表添加字段，需要使用 CASCADE
alter table test20200415 add columns (name string, age string) CASCADE;

-- hive删除外部表数据
ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False'); drop table xxx;

-- hive正则查找替换, 注释使用 \\
select regexp_replace(regexp_extract('asdas.doc','.docx|.xlsx|.xls|.pdf|.doc',0),'\\.','')

-- hive截取
SELECT SUBSTRING("6.5-8.5",INSTR("6.5-8.5",'-')+1,length("6.5-8.5")-INSTR("6.5-8.5",'-')),
SUBSTRING("6.5-8.5",0,INSTR("6.5-8.5",'-')-1)

-- 侧视图 完成 列转行
with tt as (
select '0001/0002/0003' as file_id, '1' as key
union all select '0004' as file_id, '2' as key
)
select *
from tt
lateral view explode(split(file_id,'/'))  b AS col5

-- 行合并
with tt AS(
select 'aaa' as a,'aaa' as b
union all select '1234' as a,'cccc' as b
)
select concat_ws(',',collect_set(a)) as ua,  concat_ws(',',collect_set(b)) as ub
from tt

-- json 解析函数 只有hive支持，impala不支持
-- get_json_object
with json_test as (
select '{"message":"2015/12/08 09:14:4","server": "passport.suning.com","request": "POST /ids/needVerifyCode HTTP/1.1"}' as js
)
select get_json_object(js,'$.message'), get_json_object(js,'$.server') from json_test;

-- json_tuple
with json_test as (
select '{"message":"2015/12/08 09:14:4","server": "passport.suning.com","request": "POST /ids/needVerifyCode HTTP/1.1"}' as js
)
select a.* 
from json_test
lateral view json_tuple(js,'message','server','request') a as f1,f2,f3;

5、元数据

--更新统计信息
ANALYZE TABLE tablename COMPUTE STATISTICS -- noscan

--hive元数据查看
select * from(
select a.TBL_NAME,
        sum(case when param_key='numRows' then  param_value else 0 end) 'rownum',
        sum(case when param_key='numRows' then  1 else 0 end) 'part_num' ,
        sum(case when param_key='totalSize' then  param_value else 0 end)/1024/1024/1024 'totalSize',
        sum(case when param_key='numFiles' then  param_value else 0 end) 'numFiles'
from TBLS a
left join TABLE_PARAMS b on a.TBL_ID = b.TBL_ID
where a.TBL_NAME not like '%_hbase'
group by a.TBL_NAME
) as a order by a.totalSize desc

-- 分区表，元数据不一致处理，统一更新
-- select  T1.TBL_NAME, T4.PART_NAME, T5.CD_ID, T3.CD_ID
-- from TBLS T1,DBS T2,SDS T3,PARTITIONS T4, SDS T5
UPDATE TBLS T1,DBS T2,SDS T3,PARTITIONS T4, SDS T5
SET T5.CD_ID = T3.CD_ID
WHERE T2.NAME = 'edw'
AND T1.TBL_NAME = 'test20200415'
AND T1.DB_ID = T2.DB_ID
AND T1.SD_ID = T3.SD_ID
AND T1.TBL_ID=T4.TBL_ID
AND T4.SD_ID = T5.SD_ID
and T5.CD_ID <> T3.CD_ID

版权声明：如无特殊说明，文章均为本站原创，转载请注明出处

本文链接：http://blog.turboway.top/article/hive2/

许可协议：署名-非商业性使用 4.0 国际许可协议

hive 常用命令

1、常用命令

2、常用调优设置

3、常用建表语句

4、常用语句

5、元数据