hive学习

最新推荐文章于 2024-07-24 20:46:25 发布

每天起床第一句今天真美

最新推荐文章于 2024-07-24 20:46:25 发布

阅读量865

点赞数

文章标签： hive sqoop hadoop

本文链接：https://blog.csdn.net/limuqing_134/article/details/117063291

版权

1.表的操作

create [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment],...)]
[COMMENT table_name]
[PARTITION BY (col_name data_type [COMMENT col_comment],...)]  --分区：跟mapreduce的分区不一样 --不从表名后直接定义的字段出
[CLUSTERED BY (col_name, col_name, ...)] --分桶：跟mapreduce的分桶一样，从表名后直接定义的字段出
[SORTED BY (col_name [ASC|DESC],...)] INTO num_buckets BUCKETS] --在创建表时排序，一般不使用
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_location]

row format delimited fields terminated by ',' --列分隔符
collection items terminated by '_' --map struct 和array的分隔符 --可能需要对数据进行处理
map keys terminated by ':' --map中的key和value的分隔符
lines terminated by ';' --行分隔符

2.hive是一个分析数据的框架

/bin/hive -e "select * from table_name;"
/bin/hive -f /file_dir/file_name

3.退出hive窗口

exit;
quit;

4.查看hdfs文件系统

dfs -ls /;

5.查看本地文件系统

! ls /;

查看历史命令 --如果之前的窗口关了，可以查到之前的语句

cat .hivehistory

6.参数配置信息
6.1查看参数配置信息

set mapred.reduce.tasks;

6.2数据库默认的default创建位置
在hive-default.xml.template里的hive.metastore.warehouse.dir下
6.3查询信息显示
在hive-default.xml.template里的hive.cli.print.current.db下
6.4修改运行日志存储位置（hive.log默认放到tmp目录下）
在hive-default.xml.template里的hive.log.dir下
6.5一次性修改配置信息（仅仅在本次窗口中有效）
bin/hive -hiveconf mapred.reduce.tasks=10;
set mapred.reduce.tasks=100;
7.hive的数据类型
基本数据类型跟java一样，多了集合的数据类型

CAST('1' AS INT) --将字符串'1'转换成整数1
--如果强制类型转换失败，如CAST('X' AS INT)则会返回空值NULL

8.DDL数据定义语言
8.1创建数据库

create database [if not exists ] db_hive2 [location '/db.hive2.db'];

如果不指定存储位置，则默认为/user/hive/warehouse
8.2修改数据库
数据库的其他元数据信息，如数据库名，数据库所在目录位置不可更改。
只可以更改一些描述属性，如创建时间等，并且这些信息要用extended才可以查看到

desc database extended hive_db; --查看扩展的数据库信息
alter database hive_db set dbproperties("CTtime"="2020-06-28"); --改变数据库的属性
desc database hive_db; --查看数据库信息
drop database [if exists] hive_db3; --删除空的数据库
drop database hive_db3 cascade; --级联删除不为空的数据库

9.开通服务

/bin/hiveserver2
通过jdbc的驱动连接
cd /opt/module/hive/
bin/binline
!connect jdbc:hive2://hadoop102:10000
输入用户名：跟数据库的owner一样就可以

10.管理表和外部表
管理表：当我们删除一个管理表时，也会删除这个表的数据。
外部表：建表时加了EXTERNAL，当我们删除一个管理表时，不会删除这个表的数据，只会删除元数据信息。
查看是什么类型的表：
desc formatted table_name;
一般而言，原始信息用外部表，中间数据用管理表。
管理表和外部表的互相转换：EXTERNAL区分大小写，TRUE不区分大小写,并且只能写单引号

alter table table_name set tblproperties('EXTERNAL'='TRUE');
alter table table_name set tblproperties('EXTERNAL'='FALSE');

11.将文件导入数据表

load data local inpath 'file_dir/filename' into table table_name; --从本地
load data  inpath 'file_dir/filename' into table table_name; --从hdfs

12.表分区
12.1给表增加分区

一次加一个分区
alter table table_name add partition(month="20200626");
一次加多个分区
alter table table_name add partition(month="20200626") partition(month="20200627"); --多个分区之间用空格分开

12.2给表删除分区

一次删除一个分区
alter table table_name drop partition(month="20200626"); 
一次删除多个分区
alter table table_name drop partition(month="20200626"),partition(month="20200627"); --多个分区之间用,分开

12.3查询分区

select * from tmp_emp3 where stat_dt = '2020-12-31';
查询多个分区
select * from tmp_emp3 where stat_dt = '2020-12-31' or stat_dt = '2021-12-31' ;
以union方式查询多个分区
select * from tmp_emp3 where stat_dt = '2020-12-31'
union
select * from tmp_emp3 where stat_dt = '2021-12-31';

12.4二级分区
创建一个二级分区的表

create table tmp_emp5(emp_id int                                         
,emp_name  string                                      
,loc_id  int  )
partitioned by(month string, day string)
row format delimited fields terminated by '\t';

导入二级分区表数据

load data local inpath 'file_dir/filename' into table database_name.table_name partition (month="202006",day="30"); --从本地

12.5分区目录和分区文件操作
（1）直接创建一个分区目录

dfs -mkdir -p /user/hive/warehouse/edm.db/tmp_emp3/stat_dt=2020-06-30;

（2）直接删除一个分区目录

dfs -rmdir /user/hive/warehouse/edm.db/tmp_emp3/stat_dt=2020-06-30;

（3）直接删除一个分区文件

dfs -rm -f /user/hive/warehouse/edm.db/tmp_emp3/month=20200630;

12.6分区表与数据关联的三种方式
1.将数据直接上传到该目录下，再修复分区元数据信息

dfs -put /home/etluser/lmq/export/tmp_emp.txt /user/hive/warehouse/edm.db/tmp_emp3/stat_dt=2020-06-30; --注意这里没有引号

如果上传的目录不存在，则会将该目录名称作为文件名称，将文件上传上去。
这种方式会查不到数据，没有建立分区的元数据信息，接下来修复分区元数据信息。

msck repair table tmp_emp3;

2.直接创建分区

alter table tmp_emp3 add partition(stat_dt='2020-07-31');

3.上传数据后load数据到分区

dfs -mkdir -p /user/hive/warehouse/edm.db/tmp_emp3/stat_dt=2020-08-31;
load data local inpath 'file_dir/filename' into table table_name partition(stat_dt='2020-08-31'); --从本地

13.修改表
已经有的表尽量不要修改，尽量都是新建一个表，不然会影响其他人对那个表的使用
13.1重命名表

alter table table_name rename to new_table_name;

13.2增加/修改/替换列
修改只可以一个一个列的操作

alter table table_name change [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment];

增加和替换可以多个列一起操作

alter table table_name add|replace [COLUMNS] (col_old_name column_type [COMMENT col_comment], ...);

添加是直接在末尾添加，replace是替换所有的列

alter table tmp_emp6 add columns (emp_desc string comment  '员工描述'); 
（注意此处是columns 不是column）
alter table tmp_emp6 replace columns (emp_desc string comment  '员工描述'); 
（会删除原表所有列，替换成括号里的列）

14.DML数据操作语言
14.1.往hive中导入数据
14.11 从本地导入的时候
用load的方式

load data [LOCAL] inpath '' [overwrite] into table table_name [partition(partcol1=val1,...)];

从本地导入的时候，是cp模式，即本地文件不会被删除

load data local inpath '/home/etluser/lmq/export/tmp_emp.txt' into table edm.tmp_emp4;

修改字段分隔符：

alter table tmp_emp4 set serdeproperties('field.delim'='\t');

修改序列化分隔符：

alter table tmp_emp4 set serdeproperties('serialization.format'='\t');

14.12从本地上传到hdfs，再从hdfs上将数据导入表中
1.将文件上传到hdfs

dfs -put file_dir/file_name hdfs_file_dir

如：

dfs -put /home/etluser/lmq/export/tmp_emp.txt /tmp/export/;

2.再从hdfs上将数据导入进去表里

load data inpath '/tmp/export/tmp_emp.txt' into table edm.tmp_emp4;

此时 /tmp/export/tmp_emp.txt 文件不见了，此时操作是mv 将文件移动到对应的表的目录下了，如果该目录下已经有同名文件，则会自动在文件名后添加_copy_1

14.2插入
14.21插入一行

insert overwrite /into table table_name partition(month='202006',day='30')
values ()

14.22从其他表里查询插入

insert into table tmp_emp6 partition(month='202006',day='30')
select * from tmp_emp4;

14.3删除一列，只能用replace

alter table tmp_emp6 replace columns (emp_id int                                         
,emp_name  string                                      
,loc_id  int);

需要在replace里加上所有的列，因为会替换成replace语句的列
14.4从另外一个表里添加数据到分区表

insert into table edm.tmp_emp6 partition(month="202006",day="30")
select * from tmp_emp2;

注意；除了分区字段外，其他字段两个表都必须一模一样，否则不可select *
不是追加，而是覆盖就要用overwrite

14.5从一个表的多个分区(或者从多个表)插入到另一个表的多个分区(或者多个表里面)

from edm.tmp_emp3 --如果来自同一个表，可以将from提前
insert into table edm.tmp_emp6 partition(month="202006",day="30")
select emp_id,emp_name,loc_id where stat_dt="2020-12-31"
insert into table edm.tmp_emp6 partition(month="202007",day="31")
select emp_id,emp_name,loc_id where stat_dt="2020-12-31"
;

14.6创建表时通过location加载数据路径

create table tmp_emp22 like tmp_emp2;
dfs -put /home/etluser/lmq/export/tmp_emp.txt /user/hive/warehouse/edm.db/tmp_emp22;

或者先把文件放到hdfs上去

dfs -put /home/etluser/lmq/export/tmp_emp.txt /tmp/export/tmp_emp;

再在建表时指明表数据文件的路径

create external table edm.tmp_emp22 like edm.tmp_emp2  location '/tmp/export/tmp_emp';

此时最好使用外部表，这样的话删除表不会删除数据

14.7import数据到指定hive表中
必须要先用export导出后，再将数据导入
如果import导入，需要在该目录下有元数据信息，直接从数据位置导入时会报错

import table tmp_emp22 from '/tmp/export/tmp_emp/'

14.8导出到本地文件系统

insert overwrite local directory '/home/etluser/lmq/export/datas'
select * from tmp_emp22;

此时，数据导出的没有分隔符等格式

insert overwrite local directory '/home/etluser/lmq/export/datas'
row format delimited fields terminated by '\t'

此时，数据就有了分隔符。

14.9导出到hdfs文件系统

insert overwrite directory '/tmp/tmp_emp'
row format delimited fields terminated by '\t'

通过get命令导出到本地

dfs -get /tmp/tmp_emp/* /home/etluser/lmq/export/datas

通过 -e -f shell命令导出

/bin/hive -e "select * from edm.tmp_emp22" > /home/etluser/lmq/export/datas/tmp_emp22.txt;

export导出到hdfs上

export table edm.tmp_emp22 to '/tmp/export/tmp_emp22/';

导入文件到表里
必须是import一个新的表，或者空的表
如果是import一个新的表，则默认建的是一个外部表

import table edm.tmp_emp222 from '/tmp/export/tmp_emp22/';

14.10清空表数据
只可以清空管理表，不可以清空外部表
truncate table edm.tmp_emp22;

14.11查询

SELECT [ALL|DISTINCT] select_expr, select_expr,...
FROM 
WHERE
GROUP BY
ORDER BY
CLUSTER BY | DISTRIBUTE BY | SORT BY
LIMIT

如：

SELECT EMP_ID,EMP_NAME
FROM EDM.TMP_EMP;

SELECT COUNT(EMP_ID)|MAX(EMP_ID)|MIN(EMP_ID)|SUM(EMP_ID)|AVG(EMP_ID) EMP_CNT 
FROM EDM.TMP_EMP;

count(*) count(1) count(EMP_ID)的区别
从执行结果来说：
count(1) 和count()之间没有区别，因为count()和count（1）都不会去过滤空值，但count（列名）就有区别了，因为count（列名）会去过滤空值。
从执行效率来说：
他们之间根据不同情况会有些许区别，MySQL会对count()做优化
（1）如果列为主键，count(列名)效率优于count(1)
（2）如果列不为主键，count(1)效率优于count(列名)
（3）如果表中存在主键，count(主键列名)效率最优
（4）如果表中只有一列，则count()效率最优
（5）如果表有多列，且不存在主键，则count(1)效率优于count(*)>

count(1)，其实就是计算一共有多少符合条件的行。
1并不是表示第一个字段，而是表示一个固定值。
其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1.
count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

14.11常用查询关键字
BETWEEN A AND B --大于等于A且小于等于B
LIKE --%代替多个字符 _代替单个字符
IS [NOT] NULL
IN (,)
A RLIKE B (B是一个正则表达式)
AND OR NOT
GROUP BY
HAVING
JOIN（只支持等值连接，不支持非等值连接）
当所涉及字段为NULL时，一般的比较运算符都会返回NULL

SELECT * FROM EDM.TMP_EMP
WHERE EMP_ID LIKE '_2%'; --INT类型也可以使用LIKE查询 --第二位为2

SELECT * FROM EDM.TMP_EMP
WHERE EMP_ID RLIKE|REGEXP '[2]'; --RLIKE与REGEXP完全一样

select * from EDM.TMP_EMP where emp_id > 300 and loc_id >= 30;

建表：

create table edm.tb_dept(
deptno int,
dname string,
loc string
)
row format delimited fields terminated by '\t';

create table edm.tb_emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal float,
comm float,
deptno int
)
row format delimited fields terminated by '\t';

内连接 JOIN

SELECT t1.empno, t1.ename,t1.deptno,t2.dname FROM edm.tb_emp t1
join edm.tb_dept t2
on t2.deptno = t1.deptno;

外连接 LEFT|RIGHT JOIN

SELECT t1.empno, t1.ename,t1.deptno,t2.dname FROM edm.tb_emp t1
LEFT join edm.tb_dept t2
on t2.deptno = t1.deptno;

外连接 LEFT|RIGHT JOIN

SELECT t1.empno, t1.ename,t1.deptno,t2.dname FROM edm.tb_emp t1
RIGHT join edm.tb_dept t2
on t2.deptno = t1.deptno;

满外连接 FULL JOIN

SELECT t1.empno, t1.ename,t1.deptno,t2.dname FROM edm.tb_emp t1
FULL join edm.tb_dept t2
on t2.deptno = t1.deptno;

笛卡尔积
两个表join之后，最终产生了相乘的积，一般是没有意义的。
笛卡尔积在以下条件会产生:
1.省略连接条件
2.连接条件无效
3.所有表的所有行互相连接
严格模式

set hive.mapred.mode; --默认是nonstrict
set hive.mapred.mode=strict;

开启严格模式时，出现笛卡尔积是会报错的
连接谓词不支持or

14.12排序
1.全局排序（order by）
一个reducer
默认是升序:ASC ,可以指明DESC

select * from edm.tb_emp order by sal;
select * from edm.tb_emp order by sal desc;
select sal*2 n_sal from edm.tb_emp order by n_sal desc; --可以按照别名排序
select * from edm.tb_emp order by deptno, sal desc; --按照多个字段排序

2.分区排序 distribute by
类似MR中的分区，最终产生多个文件
MR中的分区字段和排序字段可以是同一个，或者不同
与SORT BY连用，在SORT BY 之前

insert overwrite local directory '/home/etluser/lmq/export/datas'
select * from edm.tb_emp distribute by deptno sort by sal;
insert overwrite directory '/tmp/export/datas'
select * from edm.tb_emp distribute by deptno sort by sal;

需要先设置reduce任务是分区的个数，才可以让distribute生效

set mapreduce.job.reduces = 3;

否则的话，不能先分区再排序，而是直接排序
但是我这么设置之后，导出的文件也没办法是三个分区的，不知道什么原因。

3.cluster by
当distribute by和sort by字段相同时，可以使用cluster by

insert overwrite directory '/tmp/export/datas'
select * from edm.tb_emp cluster by deptno;

不可使用ASC或者DESC指定顺序，默认为增序

4.sort by
单独用sort by的时候，也可以产生分区，但是是具体的随机分区（虽然随机，但是每次都一样的结果）
为了分散数据而产生的。

14.13分桶
分区针对数据存储路径，而分桶针对数据文件
和分区一样都是解决数据量过大的问题
有个属性来限定是否能够分桶：

set hive.enforce.bucketing=true;

create table edm.stu_bucket(id int,name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

 load data local inpath '/home/etluser/lmq/stu_bucket.txt' into table edm.stu_bucket;

直接执行以上命令会报错，需要设置一下参数

set hive.strict.checks.bucketing=false;

然后执行该命令，会发现这个表依然是一个文件，这是因为直接load是put的方式，不会走MR任务

使用insert的方式导入数据试试

truncate table edm.stu_bucket;
create table edm.stu_ordinary(id int,name string)
row format delimited fields terminated by '\t';
load data local inpath '/home/etluser/lmq/stu_bucket.txt' into table edm.stu_ordinary;
insert into table  edm.stu_bucket
select * from  edm.stu_ordinary;

这样就会导致产生4个
=如果上述操作不会产生4个桶，则需要set hive.enforce.bucketing=true;
让reduce按照bucket的数量来设定：set mapreduce.job.reduces = -1;
以上语句指执行hql语句时按照自己的规则来就行了
这个时候，select * from edm.stu_bucket;
会发现读取顺序会按照文件的顺序依次读取。
分桶表是数据量特别大的时候才建立的
抽样查询规则

select * from edm.stu_bucket tablesample(bucket x out of y);

y表示抽取比例，例如y=2，则4/2表示抽取2桶作为样本数据，如果y=8，则4/8，表示抽取1/2桶的样本数据。
y最好是z（桶数量）的因子或者倍数
x表示从哪个bucket开始抽取，如果需要抽取多个分区，以后的分区号为当前分区加上y.
x必须小于等于y的值: x + (z/y - 1) * y <= z ->> x <= y

从第一个桶开始取，取1                且只取4/4 = 1个桶
select * from edm.stu_bucket tablesample(bucket 1 out of 4 on id); 
从第一个桶开始取，取1 和 1 + 4/2 = 3 且只取4/2 = 2个桶
select * from edm.stu_bucket tablesample(bucket 1 out of 2 on id); 
从第一个桶开始取，取1 和 1 + 4/3 = 2 且只取4/3 = 4/3个桶
select * from edm.stu_bucket tablesample(bucket 1 out of 3 on id);

on id 也可以不写

14.14其他操作
1.空字段赋值–只有两个参数，没有三个参数的（可以自定义函数实现）
NVL(STIRNG1,REPLACE_WITH)
第一个为NULL 返回REPLACE_WITH
两个都是NULL 返回NULL
2.行转列–与oracle类似
case when then end

有时候语句包含中文时，从外边的编辑器敲代码比直接在secureCRT敲代码要好
因为有时候secureCRT的编码格式可能跟hive不一样，会报错
hive窗口里，tab也是过不去，会报错

select dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) male_count
from emp_sex
group by dept_id;

拼接两个列的内容，本身是同一行的数据
concat(col_1,’,’,col_2)
–concat不必须是string
–拼接很多列的时候，更方便，把分隔符写到前面去
–concat_ws必须是string 或者string类型的数组

select concat_ws(',',loc,dname) from edm.tb_dept;

4.拼接同一列的数据–聚合函数
collect_set --只接受基础数据类型
将某个字段的值进行去重汇总，产生array类型字段

select collect_set(dname) from edm.tb_dept;

select t1.col1, concat_ws('|',collect_set(t1.loc)) loc from 
(select concat_ws(',',loc,dname) col1, loc  from edm.tb_dept) t1
group by t1.col1;

5.列转行
将一列中复杂的array或者map结构转换成多行
explode
split：将字符串按照分隔符打散开

select movie,category_name
from movie_info
lateral view explode(category) tmpTable as category_name --侧写
;

lateral view udtf(expression) tableAlias as columnAlias
解释；用于和split,explode等UDTF一起使用，将某列拆分成多行数据，在此基础上对拆分后的数据进行聚合
6.窗口函数

select sum(sal) from emp;

查询在2017年4月份购买过的顾客及总人数

select name,count(*)
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

over() 指定分析函数的窗口大小
如果括号里什么都不加，则是全表
跟在一个聚合函数的后边，仅仅对前面的聚合函数生效
查询购买的客户及总客户人数

select name,count(*) over () --是group by之后的总窗口大小
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

查询所有明细和总购买总额

select *, sum(cost) over() from business;

select month(orderdate) from business; --返回月份
select *,sum(cost) over(distribute by month(orderdate)) from business; --每一条数据自己的窗口 --按照月份来开窗
select *,sum(cost) over(partition by month(orderdate)) from business; --distribute 和 partition 一样的

3.上述的场景里，将cost按照日期进行累加
select * from business sort by orderdate;
current row：当前行
n preceding：往前n行
n following：往后n行
unbounded：
unbounded preceding 往前没有界限
unbounded following 往后没有界限
lag(n)：往前第n行
lead(n)：往后第n行
ntile(n)：把有序分区中的行分发到指定数据的组中，各个组中又编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号
n为int类型
–按照日期排序，从第一行到当前行分区
select *,sum(cost) over(sort by orderdate rows between unbounded preceding and current row) from business;
–按照日期排序，从前一行到后一行分区 --总共三行
select *,sum(cost) over(sort by orderdate rows between 1 preceding and 1 following) from business;
–按照人名分区，在区里面，按照日期排序，从前一行到后一行分区 --总共三行
select *,sum(cost) over(distribute by name sort by orderdate rows between 1 preceding and 1 following) from business;
–从当前行到最后
select *,sum(cost) over(sort by orderdate rows between current row preceding and unbounded following) from business;
查询顾客上次购买时间 --可以通过lag lead查询”跳转率“的问题
select *, lag(orderdate,1) over(distribute by name order by orderdate)
, lead(orderdate,1) over(distribute by name order by orderdate) from business;

–查询按照名字分区，按照时间的，从初始行到当前行的汇总cost
select *, sum(cost) over(distribute by name order by orderdate) from business;

查询前20%时间的订单信息
select *, ntile(5) over(sort by orderdate) gid from business
where gid = 1; X
select *, ntile(5) over(sort by orderdate) gid from business
having gid = 1; X

select * from (
select , ntile(5) over(sort by orderdate) gid from business
)
where gid = 1; X --两个不可以

select * from (
select name,orderdate,cost, ntile(5) over(sort by orderdate) gid from business
) t
where gid = 1;
hive里面tab过不去，而且中文的话要注意编码格式

over在一个select语句里可以有多个窗口的
聚合函数和over()是连在一起用的，并且over()只对前面的一个聚合函数有效

rank()：排序相同时会重复，总数不变 1 1 3
dense_rank()：排序相同时会重复，总数会减少 1 1 2
row_number()：会根据顺序计算 1 2 3

rank后面一定要跟一个窗口函数
select *, rank() over() from score; --排序都是1
select *, row_number() over() from score; --按照1，2,3,4，…
–会将原表的数据倒过来因为先读的后面，先写入

select name, subject,score,
rank() over(partition by subject order by score),
dense_rank() over(partition by subject order by score),
row_number() over(partition by subject order by score)
from score;

set本身可以去重+排序

系统内置函数
1.查看系统自带函数
show functions;
2.显示自带函数用法
desc function upper;
3.详细显示自带函数用法
desc function extended upper;

自定义函数
1.UDF 一进一出 --一般常用的是这个
2.UDAF 聚合函数多进一出
3.UDTF 一进多出

编程步骤
1.继承org.apache.hadoop.hive.ql.UDF --可以不加@ovverride注释
2.需要实现evaluate函数，evaluate函数支持重载
3.在hive的命令行窗口创建函数
创建完函数之后需要打包
1.添加jar
add jar linux_jar_path
2.创建function
create [temporary] function [dbname.]function_name as class_name;
4.在hive的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;

–注意事项
UDF必须有返回类型，可以返回NULL，但是返回类型不能为void

add jar /opt/module/datas/udtf.jar
create temporary function myLower as “”;

hiveserver2
beeline
!connect jdbc:hive2://localhost:10000/default

!connect jdbc:hive2://sdwsmn1:10000/default;principal=hive/sdwsmn1@DWSP.COM

压缩和存储
snappy

进去bin目录下，敲入hadoop
敲入hadoop checknative
发现 snappy已经是true了，不是true要改为true
改snappy已经是true了，不是true要改为true
1.停掉集群
stopcluster.sh
cd /opt/software/
支持snappy的hadoop jar包–已经编译好的
拖入到sftp上
tar -zxvf tar包名
cd hadoop-2.7.2/lib/
cd native
–cp ./* /opt/module/hadoop-2.7.2/lib/native/ --将当前目录从包里解压缩的文件的native目录下的所有文件全部复制到集群目录native下

cd …/…
重启集群
startcluster.sh

这个时候，hadoop checknative发现 snappy已经是true了
snappy: true /opt/cloudera/parcels/CDH-6.2.0-1.cdh6.2.0.p0.967373/lib/hadoop/lib/native/libsnappy.so.1
要在hadoop中启用压缩，可以配置如下参数（mapred-site.xml中）
hive的调优和执行效率一定要追溯到底层的mapreduce中

开启hive中间传输数据压缩功能
set hive.exec.compress.intermediate;
set hive.exec.compress.intermediate=true;

开启mapreduce中map输出压缩功能
set mapreduce.map.output.compress;
set mapreduce.map.output.compress=true;

设置mapreduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec;
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

set hive.exec.compress.output;
set hive.exec.compress.output=true;

开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress;
set mapreduce.output.fileoutputformat.compress=true;

设置mapreduce最终输出数据压缩方式
set mapreduce.output.fileoutputformat.compress.codec;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.DefaultCodec;

设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

导出文件到本地—没有权限
insert overwrite local directory ‘/home/etluser/lmq/export/datas/tb-dept’ select * from edm.tb_dept;
导出文件到hdfs
insert overwrite directory ‘/tmp/export/tb-dept’ select * from edm.tb_dept;
–不知道为啥我设置这个SnappyCodec就贼慢
还报错 Blacklisting behavior can be configured via spark.blacklist
–不知道为啥我设置这个DefaultCodec也会报错
set mapreduce.output.fileoutputformat.compress=false; 这样就可以运行通过了。。。。

文件存储格式
hive支持的文件存储格式主要有textfile,sequecefile, orc,parquet
列式存储：每一列在一块
行式存储：每一行在一块
主要看对这个表的应用方式是怎样的，如果只取一行比较多，就用行式存储。
如果只取一列比较多，就用列存储。

textfile,sequecefile基于行存储
orc,parquet基于列存储

textfile
默认格式，数据不做压缩，磁盘开销比较大，可以结合Gzip，Bzip2使用
但是使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作

Orc
每个Orz文件由1个或者多个stripe组成，每个stripe250MB大小
这个stripe实际相当于RowGroup概念，不过大小由4MB->250MB，这样应该能提升顺序读的吞吐率
每个Stripe由3部分组成，分别是Index Data, Row Data， Stripe Footer

Parquet
底层是以二进制方式存储的，因此Parquet格式文件是自解析的
结构比Orc更复杂
也是RowGroup的概念

mysql底层存储是csv 默认分隔符是","
ALT+P 打开一个文件传输的secureCRT

create table log_orc_none(
)
row format delimited fields terminated by ‘\t’
stored as textfile
;
textfile
18.12 M
22.493 seconds

create table log_orc_none(
)
row format delimited fields terminated by ‘\t’
stored as orc
;
orc
2.78 M
20.565 seconds

create table log_orc_none(
)
row format delimited fields terminated by ‘\t’
stored as parquet
;
parquet
13 M
22.788 seconds
在公司中最好是使用orc+snappy

create table log_orc_none(
)
row format delimited fields terminated by ‘\t’
stored as orc tblproperties(“orc.compress”=“NONE”)
;
orc_none
7.73 M
create table log_orc_none(
)
row format delimited fields terminated by ‘\t’
stored as orc tblproperties(“orc.compress”=“SNAPPY”)
;
orc_snappy
3.78M

orc默认压缩是采用ZLIB压缩，比SNAPPY压缩小
压缩率更高，但是压缩效率更低

hive调优
hive在某些情况的查询不会走Mapreduce
set hive.fetch.task.conversion=more;
none：任何都走MR
minimal:
more: 更适用，select filter limit都不走MR

本地模式
set hive.exec.mode.local.auto; --默认为false 提交到yarn上执行
set hive.exec.mode.local.auto=true; --改为本地
本地模式效率很高
select count(1) from edm.tb_dept;

小表和大表的join
关闭mapjoin功能，默认是打开的
set hive.auto.convert.join=false;

可以发现hive底层已经做过优化了，不必特意写小表join大表
大表join大表
空key–空key过多，发给了一个reducer，导致数据倾斜
两种处理方式
1.直接过滤掉空key
直接在子查询中where过滤掉空key
insert overwrite table jointable
select n.* from (select * from nullidtable where id is not null) n full join ocl o on
n.id = o.id;
2.将空key转换
当某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，
此时我们可以为表a中key为空的字段赋值一个随机值，使得数据随机均匀地分布到不同的reducer上
不用NVL 要赋值一个随机值
insert overwrite table jointable
select n.* from nullidtable n full join ocl o on
case when n.id is null then concat(‘hive’,rand()) else n.id end = o.id;
这种情况可能会导致时间更长(在数据量少的情况下) 因为加了一个方法
但是解决了数据倾斜的问题

mapjoin
设置自动选择mapjoin --默认就是开启的
set hive.auto.convert.join=true;
set hive.auto.convert.join=false;

set hive.mapjoin.smalltable.filesize;
set hive.mapjoin.smalltable.filesize=25000000; 25M是小表

mapjoin
1.task A 是一个Local Task(在客户端本地执行的Task)，
扫描小表b的数据，将其转换成一个HashTable的数据结构，并且写入本地的文件中
之后将该文件加载到DistributedCache中
2.Task B，是一个没有Reduce的MR，启动MapTasks扫描大表a,
在Map阶段，根据a的每一条记录去跟DistributeCache的b表对应的HashTable关联
并且输出结果
3.由于Mapjoin没有Reduce，所以由Map直接输出结果文件，有多少个Map Task，就有多少个结果文件

Hive优化
group by

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时数据就倾斜了
并不是所有的聚合操作都需要在Reduce中完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果 --相当于
–map端进行聚合
set hive.map.aggr; --默认打开

–在map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval;
set hive.groupby.mapaggr.checkinterval=10000;
有数据倾斜时进行负载均衡
set hive.groupby.skewindata; --默认是false
set hive.groupby.skewindata=true;
加入combiner组件的时候，需要保证加入之后，业务逻辑不会改变
加入这个组件，可以减少数据传输量

count(distinct) 去重统计
需要放到一个reducer里面来处理的–产生了数据倾斜
select count(distinct id) from bigtable;
–设置reducer个数
set mapreduce.job.reducer = 5;
一般count(distinct) 使用先group by 再count的方式替换,虽然会多一个job来完成，但是数据量很大的话，这个绝对是值得的
select count(1) from (select id from bigtable group by id);

笛卡尔积
select e.ename,d.dname from emp e,dept d;
–会产生笛卡尔积
–严格模式开启情况下，这个语法过不去

行列过滤----优化
在select中只需要拿到需要的列
在两个表join的时候，如果把where放在join后面，这样谓词下推失效
如果先在子查询中对join的两个表过滤，就可以谓词下推----让数据先过滤

谓词下推(先走where，提前过滤数据)：
select * from emp where ename = ‘张三’;

select t1.id
from smalltable t1
left join bigtable t2
on t2.id = t1.id
where t2.id <= 10
;

首先找到表是谁
再用where过滤
再select *
select t1.id
from smalltable t1
left join (select id from bigtable where id <= 10) t2
on t2.id = t1.id
;

动态分区
开启动态分区
set hive.exec.dynamic.partition=true;
关闭动态分区严格模式
set hive.exec.dynamic.partition.mode=strict;
set hive.exec.dynamic.partition.mode=nonstrict;
–在strict严格模式下，要求你插入数据必须指定一个分区
–在所有执行MR的节点上，最大一共可以创建多少个动态分区
set hive.exec.max.dynamic.partitions=1000;
–在每个执行MR的节点上，最大一共可以创建多少个动态分区
set hive.exec.max.dynamic.partitions.pernode=100;
–在空分区生成时，不会产生异常
set hive.error.on.empty.partition=false;

创建分区表
加载数据到分区表
创建目标分区表–数据集过大的时候，将一个非分区的表转成一个分区表
insert overwrite table ori_partitioned_target partition(p_time)
select id,time,uid,keyword,url_rank,click_num,click_url,p_time --最后这个字段是分区字段，一定是这样按位置对应
from orl_partitioned;

数据倾斜
合理设置Map数
1.通常情况下，作业会通过input的目录产生一个或多个map任务
—切片，一个文件切一个
主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件大小
2.是不是map越多越好？
–视情况而定
–并不是小文件越多越好
3.是不是每个map处理接近127M的文件块就高枕无忧了？
–切片大小：最大值，最小值
–降低切片大小
–把最大值调小

小文件合并
set hive.input.format;
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
默认就是小文件合并的方式

复杂文件增加map数
set mapreduce.input.fileinputformat.split.maxsize;
mapreduce.input.fileinputformat.split.maxsize=256000000; --默认256MB–
set mapreduce.input.fileinputformat.split.minsize;

当mapreduce.input.fileinputformat.split.maxsize=-1; --会根据具体的文件个数来分块

一：调整reduce个数的方法一
–每个reducer默认处理的数据量默认是256M 我们好像不是256M
set hive.exec.reducers.bytes.per.reducer;
hive.exec.reducers.bytes.per.reducer=67108864;

–每个任务最大的reduce数，默认为1099
hive.exec.reducers.max=1099;

二：调整reduce个数的方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces;

–当mapreduce.job.reduces设置为-1时，才会按照方法一来走
set mapreduce.job.reduces=-1;

并行执行–默认并行执行都是打开的
在一个hql执行中，有很多个阶段
没有相互依赖的阶段，就可以同时进行
如果相互依赖的话，就不会同时进行
set hive.exec.parallel;
hive.exec.parallel=false; --我这边默认也是false呀
set hive.exec.parallel=true; --开启并行执行

set hive.exec.parallel.thread.number;
set hive.exec.parallel.thread.number=8; --设置并行执行的线程的并发度需要根据集群的性能去设置

严格模式：不能执行不好的查询
包括
笛卡尔积
一个分区表查询的时候不指定partition
order by without limit

严格模式
set hive.mapred.mode; --默认是nonstrict --为啥我这边显示没有这个属性
set hive.mapred.mode=strict; --开启严格模式时，出现笛卡尔积是会报错的

–而且我这边所有的非严格模式下的语句都可以运行啊

–公司中的严格模式默认是开启的
–但是我这边好像不是

JVM重用
一种特别好的优化方式

根据集群的配置来设置的一个值
在hadoop的配置文件中设置的
–在hive窗口里面可以设置这个值，一次性地设置
set mapreduce.job.jvm.numtasks;
–我这边也看不到这个属性，我的底层计算框架应该是spark，所以不是mapreduce

任务推测执行
set mapreduce.map.speculative;
set mapreduce.map.speculative=false; --我这边默认是false 不开启的

压缩
也是一种优化方式，减少传输量，减少存储量

执行计划EXPLAIN
hql转化成sql
explain select * from tmp_emp;

案例—谷粒影音
统计各种指标
–统计视频观看数
–数据结构
–视频类别数组类型
–视频日志
–视频id
–视频长度
–视频观看次数
–视频评分
–视频流量
–视频评论数
–相关视频id（0-20个）数组类型

用户表
–视频上传者
–视频所有数量

建表语句
数组类型的分隔符都得一致，而且必须和字段分隔符不一样
视频类别和相关视频id

hadoop
yarn jar etl.jar com.atguigu.ETLDriver /video/2008/0222/ /output

etl.jar是打包后的数据清洗的mapreduce

1.统计视频观看数Top10
select * from video_oci order by views desc limit 10;

2.统计视频观看热度Top20所属类别
a.炸开类别
select videoId, category_name from video_oci
lateral view explode(category_name) category_t as category_name; t1
b.统计总数
统计每种类别下的视频数量
select category_name,count(*) hot from t1 group by category_name; t2
c.按照热度排序
select category_name,hot from t2 order by hot desc limit 20;

将子查询写出来
select category_name,hot
from (select category_name,count(*) hot
from (select videoId, category_name
from video_oci
lateral view explode(category_name) category_t as category_name )t1
group by category_name) t2
order by hot desc limit 20;
3.统计视频观看数Top20所属类别
select videoId, category from video_oci order by views desc limit 20; t1
报错–发现order by 用到的字段需要包括在select下
select videoId, views, category from video_oci order by views desc limit 20; t1
select videoId, category_name from t1;
lateral view explode(category) category_t as category_name; t2
select distinct category_name from t2;

sqoop学习
查看帮助信息：bin/sqoop help
bin/sqoop list-databases --connect jdbc:mysql://mastercdh:3306/ --username root --password password
bin/sqoop list-databases --connect jdbc:oracle:thin:@//10.241.25.160:1521/ods --username OOPR --password OOPR22

使用命令导入数据（注意反斜杠，反斜杠后面不能有空格等）
bin/sqoop import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–table importhdfs

sqoop import
–connect jdbc:oracle:thin:@//10.241.25.160:1521/ods
–username OOPR
–password OOPR22
–table QUERY_INFO
-m 1
–fields-terminated-by “\t”

控制map任务的个数 -m,–num-mappers
输出目录路径如果存在则删除–delete-target-dir
指定输出目录 --target-dir
我们可以指定数据输出分隔符（mysql默认是 ‘,’）
分隔符参数fields-terminated-by

命令
bin/sqoop import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–delete-target-dir
–target-dir /sqoop_test
–table importhdfs
-m 1
–fields-terminated-by “\t”
–check-column (col)：指定要导入时检查的列，行的检测列，一般都是拿主键作为检测列。注意：char、varchar类型不可以使用
–incremental (mode)：有两种模式：append 和 lastmodifuied

append ：追加 lastmodifuied：时间戳
–last-value (value)：指定上次导入检测列最后字段的最大值，也就是最后一个值
注意：append和–delete-target-dir不能同时使用

bin/sqoop import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–direct
–target-dir /sqoop_test
–table importhdfs
-m 1
–fields-terminated-by “\t”
–check-column id
–incremental append
–last-value 5

在lastmodifuied模式下
如果check-column是时间列time的话
–check-column time time列下面的数据，指定的就是你添加数据时最后的时间戳
–last-value"2019-12-19 19:04:07" 最后一行的时间戳

创建表
create table inc_tab(
id int,
name varchar(20),
last_mod timestamp default current_timestamp on update current_timestamp
);

添加数据
insert into inc_tab(id,name) values(1,‘inc1’);
insert into inc_tab(id,name) values(2,‘inc2’);
insert into inc_tab(id,name) values(3,‘inc3’);
insert into inc_tab(id,name) values(4,‘inc4’);
insert into inc_tab(id,name) values(5,‘inc5’);

执行sqoop命令
bin/sqoop import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–table inc_tab
–target-dir /sqoop_test_inc
–fields-terminated-by ‘\t’
–num-mappers 1

再添加几条数据
insert into inc_tab(id,name) values(6,‘inc6’);
insert into inc_tab(id,name) values(7,‘inc7’);
insert into inc_tab(id,name) values(8,‘inc8’);

使用lastmodifuied模式进行增量导入指定增量数据是以–append（附加）还是 --merge-key（合并）模式进行添加

bin/sqoop import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–table inc_tab
–target-dir /sqoop_test_inc
–check-column last_mod
–fields-terminated-by ‘\t’
–incremental lastmodified
–last-value “2019-12-20 03:13:51”
–num-mappers 1
–merge-key id

bin/hdfs dfs -text /sqoop_test_inc/part-r-00000
这个地方是有reduce的

这些命令我们可以使用job执行：sqoop job
bin/sqoop job --create job01 创建
bin/sqoop job --delete
bin/sqoop job --exec
bin/sqoop job --show
bin/sqoop job --list 显示可执行的job
示例语句：
sqoop job --create myjob – import --connect jdbc:mysql://example.com/db
–table mytable

先导入数据
insert into importhdfs values(11,‘x11’);
insert into importhdfs values(12,‘x12’);
insert into importhdfs values(13,‘x13’);
insert into importhdfs values(14,‘x14’);
insert into importhdfs values(15,‘x15’);

执行命令
bin/sqoop job
–create job_importhdfs
–
import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–direct
–target-dir /sqoop_test
–table importhdfs
-m 1
–fields-terminated-by “\t”
–check-column id
–incremental append
–last-value 10

我们执行一下
bin/sqoop job --exec job_importhdfs
执行job的时候会确认密码，指定成mysql的登录密码

导出某几列数据或者几行数据
bin/sqoop import
–connect jdbc:mysql://mastercdh:3306/sqoop_test
–username root
–password password
–direct
–delete-target-dir
–target-dir /where_test_importhdfs
–table importhdfs
-m 1
–fields-terminated-by “\t”
–where ‘id<6’

导出某几列
–query使用要点：
不能使用–table
如果–query的sql语句带着where字句，
必须要加上标识符$CONDITIONS