Hive工作—学习笔记_hive diff

Hive

创建表指定分隔符

create table tablename(......) row format delimited 
fields terminated by '\t' # 指定分隔符
stored as textfile  # 指定类型
location '/hdfs/data' # 指定文件地址

create database db location '/db';
1
2
3
4
5
6

插入数据

INSERT INTO TABLE tab1 select * from tab2;
INSERT OVERWRITE TABLE tab1 select * from tab2;
1
2

导入数据

# 本地
load data local inpath '/opt/module/data' into table tablename;
# HDFS
load data inpath '/opt/module/data' into table tablename;
1
2
3
4

导出数据

# 本地
insert overwrite local directory '/root/data' select * from tablename;
# HDFS
insert overwrite directory '/root/data' select * from tablename;
1
2
3
4

HIve查看分区

show partitions tableName;
1

优化

# 执行计划
# explain [ 详细信息 | 依赖 | 权限 ] sql     
explain [extended | dependency | authorization] query-sql
1
2
3

建表优化

# 建分区表（动态分区），开启动态分区，设置非严格模式
# 建分桶表 （针对数据文件）hash
# 设置合理的文件格式 行（textfile 、sequencefile）列（orc、parquet）
# 压缩格式 （lzo，Snappy）
1
2
3
4

HQL语法优化

# 列裁剪与分区裁剪
# 尽量指定字段，where的时候加分区条件，先做where操作
# group by 开启Map端聚合，设置map端的聚合条数，开启负载均衡
# 开始矢量计算，一次扫描批量进行运算
# 多重模式 from
# in/exists 改为 join
# CBO优化+谓词下推，如（where语句提前执行）
# （大表小表join）Mapjoin (小表做左连接会失效)
# （大表大表join）分而治之 建立分桶表（桶数是倍数关系hash）
# 不允许笛卡尔积
# 单表数据倾斜（开启负载均衡，多key，增加reduce个数）
# 多表jion产生数据倾斜（开启参数，MapJoin）
# Hive的Job优化（减小map的最大分片大小，让它小于128M，从而增加maper的数量）
# 小文件合并（会占据一个元数据信息，占namenode的大小是一样的）map端预聚合
# 调整reduce的数量（启动和关闭也需要时间）
# 数据量小，使用local本地模式
# Hive on Spark 问题（时间字段 dt String 不是date，在子查询使用date_dif 会出现空指针异常）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Hive源码

1

HIve 中文 注释 乱码

-- Mysql配置文件/etc/my.cnf,重启mysql服务
character_set_server=utf8

-- hive3.1.2 对应 Mysql数据库元数据库 修改对应 表 的 字段 字符编码。
show variables like '%character%';
-- 修改表字段注解和表注解
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE mediumtext character set utf8;
-- 修改分区字段注解
alter table PARTITION_PARAMS  modify column PARAM_VALUE varchar(4000) character set utf8;
alter table PARTITION_KEYS  modify column PKEY_COMMENT varchar(4000) character set utf8;
-- 修改索引注解
alter table  INDEX_PARAMS  modify column PARAM_VALUE  varchar(4000) character set utf8; 
1
2
3
4
5
6
7
8
9
10
11
12
13

Hive函数

timestampdiff(DAY,startdate,enddate)
# Flink插入数据到hive的外部表，不出现数据。
# 修复分区
msck repair table tablename;
# 批量删除分区信息
alter table tab drop partition(col!='');
substr(str,0,10) #字符串分割
1
2
3
4
5
6
7