Hive基础

表相关

创建表

• 如果加载数据后发现只有第一列有值，其它列都是null，而且第一列的值是所有列组合起来的，说明建表指定的分隔符和数据文件中的不一致

CREATE TABLE `beidou_al_dd_out_hive_iqiyi_pca_app_evt_new_device`(
  `u` string COMMENT '设备ID', 
  `p1` string COMMENT '平台ID', 
  `v` string COMMENT '版本号')
PARTITIONED BY ( 
  `dt` string COMMENT '数据日期', 
  `hashkey` string COMMENT 'hash分布键')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS Parquet tblproperties ('parquet.compression'='GZIP');

复杂类型（ARRAY，MAP，struct）

-- 建表
create table test_set(
id INT,
name STRING,
hobby ARRAY<STRING>,    -- array中元素为String类型
friend MAP<STRING,STRING>,    -- map中键和值均为String类型
mark struct<math:int,english:int>   -- Struct中元素为Int类型
)
row format delimited fields terminated by ','   -- 字段之间用','分隔
collection items terminated by '_'     -- 集合中的元素用'_'分隔
map keys terminated by ':'      -- map中键值对之间用':'分隔
lines terminated by '\n       -- 行之间用'\n'分隔

-- 插入数据 分别通过array,str_to_map,named_struct来包装插入的三种集合数据
INSERT INTO test_set SELECT 2,'xiaohua',array('basketball','read'),str_to_map('xiaoming:no,xiaohong:no'),named_struct('math',90,'english',90)

-- 查询
select 
	id,
	name,
	hobby[0],      -- 查询array用[下标]索引，下标从0开始
	friend['xiaohong'],       -- 查询map用[键值]索引
	mark.math       //查询struct用.结构名索引
from test_set

查看表schema

desc table	-- 查看表的schema
desc table partition(dt=xxxx)	-- 查看某个分区的schema 若有二级分区也必须把二级分区带上，否则报错 desc table partition(dt=xx, hashkey=xx)

查看建表语句

show create table beidou_al_dd_out_hive_chd_app_profile_device_wide;

修改表属性

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type comment '';
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

删除指定分区

-- 新增字段后重跑历史分区前需要删掉分区元数据，否则查出来的新增字段数据为null
alter table udw_mid_dd_inc_hive_iqiyi_app_itemview_abtest drop if exists partition(dt='${DT}');

修改分区列类型

alter table <表名> partition column (<分区列1> <新类型>);

删除分区后恢复数据

-- 注：用parquet格式存储时，内容文件里面会带元数据信息，如果修改了表字段名，虽然desc出来是新的元数据，但是由于内容文件里面是旧的字段名，所以按照新的字段查出来是null，只能查旧的字段，而且需要保证表的元数据和内容文件的元数据一致（因此以下方法修改元数据对parquet文件无效，只能重新建表导入数据）
alter table xxx set TBLPROPERTIES('EXTERNAL'='true');  //改成外表,保留文件
alter table xxxx drop partition(dt='2021-01-21');  //删除指定分区
alter table xxxx drop partition(dt>'2021-01-21');  //删除多个分区
alter table xxxx drop partition(dt>'2021-01-21', hashkey='-1');  //删除多个分区的指定内容
alter table xxx set TBLPROPERTIES('EXTERNAL'='false');
msck repair table xxx;  //修复分区

HADOOP HDFS相关

查看文件

hadoop fs -ls /hive/warehouse/udw.db/udw_dwd_hh_fact_hive_iqiyi_app_itemview
hadoop fs -count /hive/warehouse/udw.db/udw_dwd_hh_fact_hive_iqiyi_app_itemview		# 查看文件个数 显示为目录个数，文件个数，文件总计大小，输入路径

-- hive应用下查看 将hadoop fs换成dfs
dfs -ls /hive/warehouse/udw.db/udw_dwd_hh_fact_hive_iqiyi_app_itemview

删除hdfs文件

hadoop fs -rm ...

查看文件存储空间

hadoop fs -du -h /hive/warehouse/udw.db/udw_dwd_hh_fact_hive_iqiyi_app_itemview   //列出所有分区的大小
hadoop fs -du -s -h /hive/warehouse/udw.db/udw_dwd_hh_fact_hive_iqiyi_app_itemview  //所有分区总大小

查看分区

-- 查看指定一级分区下的二级分区
show partitions udw.udw_dwd_hh_fact_hive_nervi_app_click partition(dt='2021-10-31');

-- 多级分区时快速查看一级分区的数量
hadoop fs -ls /hive/warehouse/udw.db/beidou_al_dd_out_hive_iqiyi_app_evt_itemview_passport | head -10

-- show partitions显示分区太多的解决办法：使用linux管道符取前几个或后几个
hive -e "show partitions udw.udw_dwd_hh_fact_hive_nervi_app_click;" | head -10  //显示前10个分区
hive -e "show partitions udw.udw_dwd_hh_fact_hive_nervi_app_click;" | tail -10  //显示后10个分区

distcp数据文件同步

# 中云信上执行	hdfs路径前缀在大数据平台上查询（01或者02需要试验）通过show create table xxx或者在数据图谱上查询的不准确
# 将百度永丰数据文件同步至中云信	支持*正则匹配语法
hadoop distcp -D mapreduce.job.queuename=root.bi_sh.day -m 500 -bandwidth 10 hdfs://namenode02-bdyf.qiyi.hadoop/hive/warehouse/udw.db/udw_dwd_dd_fact_hive_nervi_app_play/dt=2021-01-* /hive/warehouse/udw.db/udw_dwd_dd_fact_hive_nervi_app_play/

# 将万国G3数据文件同步至中云信
namenode02-bdwg-g3.qiyi.hadoop

# Hive中修复元数据（在同步数据文件后恢复分区元数据 或者对于单个分区直接采用alter table add partition(dt='xxx')添加分区元数据信息）
msck repair table udw_dwd_dd_fact_hive_nervi_app_play

Hive设置

set mapreduce.job.queuename=bi_sh.day;	-- 设置队列
set hive.cli.print.header=true; 	-- 打印列名
set hive.cli.print.row.to.vertical=true;  -- 开启行转列功能, 前提必须开启打印列名功能
set hive.cli.print.row.to.vertical.num=1; -- 设置每行显示的列数

-- 打印日志
hive --hiveconf hive.root.logger=DEBUG,console
hive --hiveconf hive.root.logger=error,console

-- 显示当前数据库和表头
set hive.cli.print.current.db=true;
set hive.cli.print.header=true;

-- 设置jobname
set mapred.job.name=p0@videochannel_yule_201808m_reduce_default;

-- 动态分区
set hive.exec.max.dynamic.partitions.pernode=2000;
set hive.exec.max.dynamic.partitions=2000;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;

UDF

add jar viewfs://hadoop-bjzyx-g1/user/longyuan_udw/babel/uploaded_resources/UdwHiveUDF.jar;
create temporary function UidToHashKey as 'com.iqiyi.udw.util.UidToHashKey';
create temporary function StrToCombSequence as 'com.iqiyi.udw.util.StrToCombSequence';