大数据学习教程SD版第七篇【Hive】

• 隐式转换

整数类型、String类型 可以隐式的转换成Double类型

• 强制转换

cast (field as type)

• 库

# 1.建库
create database [if not exists] xxx
[comment 'xxx']
[location 'xxx'];
# 2.查库
show databases;
show databases like '*xxx*';
desc database xxx;
desc database extended xxx;
# 3.改库,鸡肋
alter database xxx set dbproperties("key"="value");
# 4.删库
drop database xxx;  # 空库
drop database xxx cascade; # 强制删 

• 表

表类型分为：管理表（内部表）【默认创建】、外部表

表删除时：内部表会连带删除HDFS上的数据，而外部表则不会，更安全

# 1.建表
create [external] table [if not exists] xxx(
col_name date_type [comment 'col xxx']
)
[comment 'table xxx']
[partitioned by (col type,col2 type2)]
[clustered by (col1,col2)]
[sorted by (col asc|desc) into num_buckets buckets]
[row format delimited fields terminated by 'xxx']
[stored as file_type]
[location file_path]
[as slect_statement];

# 1.m 建表(包含复合类型)
create table test(
name string,
friends array,
children map,
address struct
)
row format delimited fields terminated by ','
collection items terminated by '_'
map key terminated by ':';

# 2.改表
alter table xxx set tblpreperties('EXTERNAL'='TRUE')
alter table xxx rename to new_xxx;
# string 不能改为 int
alter table xxx change old_col new_col type;
# replace 整张表替换为新字段
alter table xxx add|replace columns(col type,col2 type2);
# 删除列，可以使用replace 来间接修改

# 3.查表
show tables;
show create table xxx;
desc xxx;
desc formatted xxx;

# 4.删表
drop table xxx;

• 数据导入

# 1.文件导入
load data [local] inpath 'xxx' [overwrite] into table xxx [partition (par_col=par_value)];
# 2.表查询导入
insert into|overwrite table table_name select * from table2_name;
# 3.表创建导入
create table if not exists table_name as select * from table_name2;
# 4.表创建指定路径导入
create table …… location 'xxx';
# 5.import方式导入,路径必须是export导出路径
import table xxx from 'hdfs_path';

• 数据导出

# 1.insert 导出
insert overwrite [local] directory 'xxx' [row format delimited fields terminated by ','] select * from table_name ;
# 2.get 导出
dfs -get /remote /local
# 3.client 导出
hive -f xxx.sql >> result.txt
# 4.export 导出
export table xxx to 'hdfs_path';

不同数据导入方式count(*)结果的问题：

insert方式：元数据中 numFiles numRows 均改变，不走mr，结果正确

put方式：元数据中上述参数不变，不走mr，结果不对

load方式：元数据中 numFiles 改变，numRows 参数不变，走mr，结果正确

如果load加载的是本地文件，则上传至HDFS对应位置，如果加载的是HDFS文件，则移动文件位置

• 清空数据

# 只能清空管理表
truncate table xxx;

执行顺序：

1. from
2. join on
3. where
4. group by
5. avg()，sum()……
6. having
7. select
8. distinct
9. order by
10. limit

• 基础语法

# Hive SQL 大小写不敏感，不用能tab缩进
select * from table_name;
select col1,col2 from table_name;

# 1.alias
as

# 2.数值运算
+ - * /

# 3.聚合函数
count  max min sum avg

# 4.limit
limit x;

# 5.过滤
where

# 6.比较
> >= < <= <>  != is [not] null  in (x,y)
[not] like % _
x<=>y : 一边为null,为false
between xx and yy： 左右皆闭合
and or not

# 7.分组
group by xxx having yyy

# 8.连接
## 1.inner join
xxx join yyy on
## 2.left outer join
xxx left join yyy on
## 3.right outer join
xxx right join yyy on
## 4.full outer join
xxx full join yyy on
## 5.xxx - yyy 左xxx表独有数据
xxx left join yyy on  where xxx.col is null
xxx left join yyy on  where xxx.col not in(select col from yyy)
## 6.yyy - xxx 和上面 *** 作相反 右yyy表独有数据
## 7.xxx 和yyy 各自独有的数据
xxx left join yyy on  where xxx.col is null or yyy.col is null;
## union 连接时两表查询字段必须一致; union all 不去重,效率更高
select mmm from ( ……t1 union ……t2)tmp;

# 9. 排序，全局排序，reduce个数始终是一个
order by xxx asc|desc
## 每个分区内部排序有序，整体无序，分区规则随机，可通过 distribute by 指定
sort by xxx asc|desc
distribute by col sort by xxx asc
## 当 distribute by 和 sort by 字段一致时,限制：只能asc
cluster by xxx

# 10.分区，多目录,可以避免全表扫描，正常按天导入数据
## 1.加分区
alter table table_name add partition(par_name=par_value);
## 2.删分区
alter table table_name drop partition(par_name=par_value);
## 3.查分区
show partitions;
desc formatted table_name;
## 二级分区，两个分区字段
## 分区调整：手动put方式加载到分区表目录，需要修复元数据
msck repair table table_name;
## 动态分区：根据查询结果动态的插入到指定分区中
### 1.关闭严格模式,下面的语句不加[],就可以不关
set hive.exec.dynamic.partition.mode=nonstrict;
### 2.查询的最后一个字段为分区字段
insert into table table_name [partition(par_name)]
select xxx,xxx,par_name from X;

# 11.分桶，多文件，不常用,抽样查询
create table table_name(id int,name string)
clusted by(id)
into 4 buckets
row format delimited fields terminated by 't';

• 常用函数

按照处理数据的行数，划分：

UDF：一进一出 ，比如：upper、nvl、split、concat

UDAF：多进一出，比如：count、sum、collect_set

UDTF：一进多出，比如：explode

# 显示Hive自带函数
show functions;
desc function fun_name;
desc function extended fun_name;

# 1.nvl
select nvl(col1,col2) from X;

# 2.case when then else end   |   if(condation,x,y)

# 3.concat、concat_ws、collect_set
select concat(col1,str,col2……) from
select concat_ws(sep,str1,str2,arr1,……) from
# set 去重
select collect_set(col) from

# 4.explode、lateral view:侧写
select explode(arr) from

select new_col from old_table
lateral view explode(arr) new_table as new_col

未完待续……