Hive

一. hive中的数据类型

　　hive中的数据类型主要分为：

　　（1）基本数据类型

　　　　1.数值型：主要就是用来保存数字

　　　　　整数型

　　　　　　tinyint　：1字节

　　　　　　smallint ：2字节

　　　　　　int　　：4字节

　　　　　　bigint ：8字节

　　　　　浮点型

　　　　　　float　：4字节

　　　　　　double ：8字节

　　　　2.字符串类型

　　　　　　String，长度可变的字符串

　　　　3.布尔类型

　　　　　　boolean

　　　　4.时间类型

　　　　　　date：大时间类型，一般只保存年月日

　　　　　　timestamp：时间戳类型，可以精确到毫秒和纳秒

　　（2）复杂数据类型：

　　　　1.array：数组，用来保存一组相同数据类型的数据的集合

　　　　　数组元素：数组中保存的每一个数据叫做数组元素

　　　　　数组长度：数组中保存元素的个数

　　　　　在hive中数组使用[]来表示

　　　　　在hive中建表的时候数组的定义语法：

　　　　　　字段名　　array<数据类型>

　　　　2.map：字典，集合

　　　　　主要用来保存一对数据，里面保存的数据都要成对出现，键值对

　　　　　在hive中建表的时候map字典的定义语法：

　　　　　　字段名 map<数据类型，数据类型>

　　　　3.struct：结构体，功能类似于c语言中的结构体

　　　　　在创建表的时候必须要将键设置好

　　　　　在hive中建表的时候struct字典的定义语法：

　　　　　　字段名 struct<键:数据类型，键:数据类型，...>

　　　　　　例如：

　　　　　　　　(city:"深圳市")

　　　　　　获取键对应的值：结构体名.键

二. hive中建表方式

　　（1）语法：

　　　　create [extarnal] tabl [if not exists] 表名(

　　　　　　字段名数据类型,

　　　　　　字段名数据类型

　　　　　　... ...

　　　　)

　　　　row format delimited

　　　　fields terminated by '分隔符';　　　　　　用来设置列之间的分隔符

　　　　collection items terminated by '分隔符' 　　用来设置数组，集合，结构体中的元素之间的分隔符

　　　　map keys terminated by '分隔符'　　　　用来设置map集合中键和值之间的分隔符

　　　 stored as 文件存储方式　　　　　　　　用来设置文件的存储方式

　　　　location '数据文件存放的位置'　　　　　　设置数据文件存放的路径

　　　　注意：如果在创建表的时候指定了location，则在hdfs上不会产生一个表名对应的文件夹

　　　　　　　也就是说没有设置location的时候，代表没有给表

　　（2）文件的存储方式

　　　　1. textfile：hive中默认的存储方式

　　　　　缺点：不会压缩数据，磁盘开销大，进行数据解析时开销也比较大，可以借助于Gzip或Bzip等压缩方式，但是无法分块。

　　　　　优点：由于对数据不会进行压缩计算，所以在使用该种方式保存数据的时候不会启用MR程序，所以速度比较快

　　　　　数据加载支持方式：put 和 load

　　　　2. sequencefile:

　　　　　优点：使用二进制进行存储数据，使用比较方便，并且支持对数据进行切块，整体效率比较高，也可以对数据进行压缩

　　　　　数据加载方式：由于要对数据进行压缩处理，所以要启动MR程序，不能使用 put 和 load，必须先创建一个中间表，

　　　　　　　　　　　　然后将元数据使用textfile的方式加载到中间表，然后再从中间表中查询出来添加到当前表中

　　　　3. rcfile：

　　　　　会将数据按照进行切块，然后每一块中的数据再按照列存储，可以保证同一行的数据不会保存到不同块中，所以在查询的时候就避免了多个块之间进行查询数据，效率比较高

　　　　4. orcfile：

　　　　　是在hive0.11版本中添加进来的，是rcfile的升级版，存储方式和rcfile一样

　　　　5. parquet：

　　　　　类似于orc

　　　　注意：由于2，3，4，5都会对数据进行压缩处理，所以数据加载方式都和第二种一样　　

　　　　压缩比率排序：orcfile>parquet>　　

　　　　有列表时语法：

　　　　　　create [extarnal] tabl [if not exists] 表名(

　　　　　　　　字段名数据类型,

　　　　　　　　字段名 array<数据类型>

　　　　　　　　... ...

　　　　　　)

　　　　　　row format delimited

　　　　　　fields terminated by '分隔符'

　　　　　　collection items terminated by '分隔符';

　　　　有map时的语法：　　

　　　　　　create [extarnal] tabl [if not exists] 表名(

　　　　　　　　字段名数据类型,

　　　　　　　　字段名 map<数据类型，数据类型>

　　　　　　　　... ...

　　　　　　)

　　　　　　row format delimited

　　　　　　fields terminated by '分隔符'

　　　　　　collection items terminated by '分隔符'

　　　　　　map keys terminated by '分隔符';

　　　　hive 中表结构的查看

　　　　　　desc 表名：简单查看表列名以及数据类型

　　　　　　desc formatted 表名：查看完整的表结构信息

　　（3）内部表和外部表：

　　　　内部表特征：

　　　　　　1. 在创建表的时候不需要设置external关键字

　　　　　　2. 如果表创建的时候没有设置location，在删除时会将hdfs上的数据文件以及表对应的文件夹删除

　　　　　　使用场景：一般在使用的过程中，将一些临时文件或者是中间文件创建为内部表

　　　　外部表特征：

　　　　　　1. 在创建表的时候需要设置external关键字

　　　　　　2. 在删除外部表的时候，只会删除hive中对应表的结构，并不会删除所对应的文件夹以及数据文件

　　　　　　使用场景：一般在项目中一些ods层的原始数据一般都使用外部表进行创建

　　　　内部表和外部表之间的转换：

　　　　　　语法：alter table 表名 set tblproperties('EXTERNAL' = 'FALSE') 转换为内部表

　　　　　　　　　alter table 表名 set tblproperties('EXTERNAL' = 'TRUE') 转换为内部表

　　（4）表中加载数据的方式

　　　　1. 使用put命令

　　　　　　主要就是将本地的文件加载到hdfs平台

　　　　　　语法：hdfs dfs -put 本地文件地址 hdfs上文件的地址

　　　　　　注意：该命令必须要在linux平台中进行执行

　　　　2. 使用load命令

　　　　　　语法：load data [local] inpath '数据文件路径' into table 表名;

　　　　　　注意：load 命令必须要在hive中执行，local代表加载的数据文件是本地文件，如果不写local则代表加载数据文件从hdfs上加载

　　　　以上两种方式加载数据的时候都不会启用MR程序

　　　　3. 使用 insert into 方式

　　　　　　语法：insert into 表名 select 语句;　　　　　　追加数据

　　　　　　　　　insert overwrite table 表名 select 语句;　覆盖数据

　　　　　　注意：使用 insert 加载数据的时候会启用MR程序

　　　　注意：hive 表中的数据只支持新增和查询，不能执行delete和update

　　　　　　　如果要删除和修改表中的某一个数据，只能去数据文件进行修改

三. hive中的表分区

　　hive 中也可以进行表分区，是用来提高查询效率

　　　　一般情况下，一个表中的数据量比较大的时候建议使用分区

　　hive中的分区主要是分文件夹

　　hive中的分区分类

　　　1. 静态分区

　　　　　语法：

　　　　　create [external] table [if not exists] 表名(

　　　　　　字段名数据类型,

　　　　　　字段名数据类型

　　　　　　... ...

　　　　　)partitioned by (分区名数据类型，分区名数据类型，......)

　　　　　　row format delimited

　　　　　　fields terminated by '分隔符';　　　　　　用来设置列之间的分隔符

　　　　　　collection items terminated by '分隔符' 　　用来设置数组，集合，结构体中的元素之间的分隔符

　　　　　　map keys terminated by '分隔符'　　　　用来设置map集合中键和值之间的分隔符

　　　　　　stored as 文件存储方式　　　　　　　　用来设置文件的存储方式

　　　　　　location '数据文件存放的位置'　　　　　　设置数据文件存放的路径

　　　　　注意：在创建分区表的时候，分区字段不能出现在create后面的括号，必须要写在partition by 后面

　　　　　　　　静态分区的时候，分区必须要在添加数据的时候手动设置

　　　2. 动态分区

　　　　　在加载数据的时候会根据指定分区的字段的值来动态创建不同的分区文件夹

　　　　　由于动态分区需要对数据进行计算处理，所以必须要启用MR程序，因此在加载数据的时候要使用insert into

　　　　　在hive中使用动态分区则需要设置以下参数：

　　　　　--打开hive动态分区

　　　　　　set hive.exec.dynamic.partition=(true/false); 开启/关闭

　　　　　--设置最多可以设置多少个动态分区(默认为1000)

　　　　　　set hive.exec.max.dynamic.partitions;

　　　　　--设置每一个MR节点上最大可以有多少个动态分区(默认为100)

　　　　　　set hive.exec.max.dynamic.partitions.pernode;

　　　　　--设置分区的模式

　　　　　　set hive.exec.dynamic.partition.mode=(nonstrict/strict); 非严格模式/严格模式

　　　　　在严格模式下进行动态分区必须设置一个静态分区

四. hive中的分桶操作

　　hive中的表分区主要就是分文件夹，也就是说将表中的数据分成多个文件夹来存储

　　hive中的表分桶主要就是将表中的数据文件拆分成多个文件存储

　　作用：提高查询效率，可以进行快速的数据抽样

　　注意：一般情况下进行表关联的时候，给两个表的关联字段都进行分桶时，可以提高查询效率

　　1. 创建表分桶的语法：

　　　　create [extarnal] tabl [if not exists] 表名(

　　　　　　字段名数据类型,

　　　　　　字段名数据类型

　　　　　　... ...

　　　　)clustered by(列名) into n buckets 　　　　根据表中指定的分桶列将数据分成n个桶

　　　　row format delimited

　　　　fields terminated by '分隔符';　　　　　　用来设置列之间的分隔符

　　　　collection items terminated by '分隔符' 　　用来设置数组，集合，结构体中的元素之间的分隔符

　　　　map keys terminated by '分隔符'　　　　用来设置map集合中键和值之间的分隔符

　　　 stored as 文件存储方式　　　　　　　　用来设置文件的存储方式

　　　　location '数据文件存放的位置'　　　　　　设置数据文件存放的路径

　　　　分桶规则：

　　　　　　先计算制定字段的hash值，然后将hash值对桶的数量进行取余，余数就是当前数据对应的桶的位置

　　2. 分桶时需要设置的参数

　　　　set hive.enforce.bucketing=true;　　　　开启分桶

　　　　set mapreduce.job.reduces=n;　　　　　设置开启的reduce的数量

　　　　一般情况下，reduce的数量和桶的数量保持一致，但是可以使用上面的参数进行设置，如果设置的值大于桶的数量，最终reduce的数量等于桶的数量，

　　　　如果设置的reduce的值小于桶的数量，则reduce的数量等于设置的值

　　3. 分桶数据抽样

　　　　分桶表和没有分桶的表都可以进行分桶抽样

　　　　抽样：从全部的数据中抽取出来一部分数据

　　　　语法：select * from 表名 tablesample(bucket x out of y [on 字段]);

　　　　　　x：获取第几桶数据，从1开始

　　　　　　y：将数据分为多少桶

　　　　注意：如果在为没有进行分桶的表进行抽样获取数据的时候，需要指定分桶的字段也就是（on 字段）

五. hive查询操作

　　语法：select [distinct] *| 列名,...

　　　　　from 表名

　　　　　where 过滤条件

　　　　　group by 分组字段

　　　　　having 分组过滤

　　hive中的排序操作：

　　　　1. order by：进行全局排序，将表中所有的数据进行统一排序，在排序的时候只会启用一个reduce程序进行计算，

　　　　　　　　　　如果现在要进行排序的数据量比较大，则排序的耗时会比较久

　　　　2. sort by：不可以进行全局排序，只能保证一个reduce中的数据是有顺序的

　　　　3. distirbute by：主要用来设置根据哪个字段进行分桶，分桶的数量是和当前的reduce的数量一致

　　　　　　　　　　　一般单独使用distribute by没有特别大的意义，都是和sort by一起使用

　　　　4. cluster by：对数据先进行分桶，然后再排序

　　　　　　　　　　如果distribute by 和sort by 的字段一致时就相当于cluster by

　　　　注意：cluster by 只能进行升序不能降序

六. hive中表连接操作

　　1. 普通关联

　　　　select * from 表A a，表B b where a.字段=b.字段

　　　　注意：hive中默认只支持等值连接,并且表关联的时候必须要设置关联条件，如果不写关联条件就要进行设置参数

　　　　set hive.mapred.mode=nonstrict;

　　2. 内连接

　　　　inner join ... on ...

　　3. 外连接

　　　　left join ... on ...

　　　　right join ... on ...

　　　　full join ... on ...

七. hive 中常用的函数

　　查询系统中的所有的函数：

　　　show functions;

　　查询函数的结构和用法：
　　　desc function 函数名查看函数的用户和语法结构
　　　desc function extended 函数名可以显示函数的用法语法结构和示例。
　　1.数字类函数：
　　　round(x,d):进行四舍五入。
　　　ceil()|ceiling():向上取整
　　　floor():向下取整
　　　mod():求余数
　　　rand():获取随机数,默认产生的是0-1之间，包含0但是不包含1.
　　　sqrt():开平方
　　　pow(x,y)|power(x,y):计算x的y次方
　　　bin():返回一个数字的二进制表示
　　2.时间类的函数：
　　　current_date():获取当前的年月日
　　　current_timestamp():获取当前的完成的日期时间，包含纳秒

　　　--获取当前经过的秒数
　　　　select unix_timestamp();
　　　--将时间字符串转换成秒数
　　　　select unix_timestamp('2021-3-15','yyyy-MM-dd');
　　　--将秒数转换成时间字符串
　　　　select from_unixtime(1615737600,'yyyyMMdd');
　　　--将日期字符串转换成日期对象,只获取年月日
　　　　select to_date('2022-3-20 12:30:56');
　　　--将时间字符串转换成时间戳
　　　　select cast('2022-3-20 12:30:56' as timestamp);
　　　--将一个数字字符串转换成int类型
　　　　select cast('19' as int);
　　　--获取年份
　　　　select year('2022-3-15');
　　　--获取月
　　　　select month('2022-3-15');
　　　--获取天
　　　　select day('2022-3-15');
　　　--获取小时
　　　　select hour('2022-3-15 13:56:30');
　　　--分钟
　　　　select minute('2022-3-15 13:56:30');
　　　--秒
　　　　select second('2022-3-15 13:56:30');
　　　--加上多少天
　　　　select date_add(`current_date`(),3);
　　　--减去多少天
　　　　select date_sub(`current_date`(),-3);
　　　--将时间定位到指定年份的第一天,注意参数必须是一个时间戳类型
　　　　select floor_year(`current_timestamp`());
　　　　select floor_year(cast('2022-4-15' as timestamp));
　　　--获取指定月份的第一天
　　　　select floor_month(`current_timestamp`());
　　　--获取指定周的第一天:周一
　　　　select floor_week(`current_timestamp`());
　　　--获取指定季度的第一天：
　　　　select floor_quarter(`current_timestamp`());
　　　--获取两个时间之间的天数
　　　　select datediff('2022-1-30','2022-5-18');
　　　--获取下个指定的星期几 :MO TU WED TH FR STA SUN　　　　
　　　　select next_day(`current_date`(),'FR');
　　　--加上多少个月
　　　　select add_months(`current_date`(),10);
　　　--获取指定时间的月份的最后一天
　　　　select last_day(`current_date`());

　　3.字符串类的函数：

　　　--将员工的姓名和职位进行拼接,如果拼接的字符串中有一个是空的则最后的结果是null
　　　　select concat(ename,job) from emp1;
　　　--将员工的姓名和奖金拼接到一起　　
　　　　select concat(ename,nvl(comm,'')) from emp1;
　　　--可以指定拼接符,第一个参数必须要指定拼接符，后面的参数必须是字符串类型。
　　　　select concat_ws('-',ename,cast(comm as string)) from emp1;
　　　--返回字符串的长度
　　　　select length(ename) from emp1;
　　　--将字符串进行反转
　　　　select reverse(ename) from emp1;
　　　--去除字符串中左右两边的空格
　　　　select trim(' sd f ') ;
　　　　select ltrim(' sdf ');
　　　　select rtrim(' sdf ');
　　　--字符串的截取substring(),最后参数代表截取的长度，如果不写则代表截取到字符串的末尾
　　　　select substr('border.png',7,2);
　　　--大小写转换
　　　--作用等同upper
　　　　select ucase('hello');
　　　--作用等同于lower
　　　　select lcase("ASADF");

　　　json数据：
　　　　json是javascript中的一种对象表示方式，是现在系统中前后段进行数据交互的一种主要形式。
　　　　json数据主要包含两种对象：
　　　　1.对象：主要是用{}括起来，里面数据都必须要成对出现，一个键要对应一个值，并且键不能重复，键的数据类型必须是字符串类型
　　　　　　　　值没有要求。
　　　　　{“name”:"张三","age":19,"sex":"男"}
　　　　2.数组：在json中数组使用[]表示，数组中元素之间使用逗号分开。

　　　--重点
　　　　--1.json数据解析函数
　　　　/*
　　　　解析json数据使用：$
　　　　语法：访问对象中的某一个键对应的值
　　　　$.键
　　　　访问数组中的某一条数据
　　　　$.[下标]

　　　　*/
　　　--get_json_object():
　　　　select get_json_object(json_str,'$.[0].content') from jsontest;

　　　--将一个字符串转换成一个map结构,第一个参数：要转换的字符串，第二个参数：元素之间的分隔符，第三个参数：键和值的分隔符
　　　　select str_to_map('name=zhangsan,age=18,sex=男',',','=');
　　　--将字符串按照指定的分隔符拆分成一个字符串数组
　　　　select split('wqeq,werwe,dgdf,xvzxc',',')[3];
　　　--将某一列的数据拼接成一行（数组）,功能类似于listagg,必须要对数据进行分组
　　　　select deptno,collect_set(ename)[1] from emp1 group by deptno;
　　　--查看某一个列表中的某一个元素的位置,第一个参数代表需要查询的子字符串，第二个参数就是原来的字符串
　　　　select find_in_set('dgdf','wqeq,werwe,dgdf,xvzxc');

　　4.选择函数
　　　--case when
　　　　select e.*,case when sal>=3000 then '高收入'
　　　　　when sal>=2000 then '中收入'
　　　　　else '低收入' end
　　　　from emp1 e;
　　　--if函数
　　　--语法：if(条件,语句1,语句2):当条件成立，则执行语句1，否则执行语句2
　　　　select e.*,if(comm is null,100,comm+100) from emp1 e;

　　5.分析函数：

　　　--分析开窗函数：和Oracle中的是一样
　　　--语法：函数() over(partition by 字段 order by 字段)
　　　--排名函数：row_number() rank() dense_rank()
　　　--移动函数：lead() lag()