hive数据类型
基本数据类型
常用的 :
INT BIGINT DOUBLE STRING
集合数据类型
STRUCT: struct(street:string,city:string)
MAP: map(string,int)
ARRAY: array(string)
**注意:**我们在导入数据是一行一行导入,因此我们需要额外的字段来匹配文件中的字符
字段解释:
row format delimited fields terminated by ‘,’ 指定了逗号作为列分隔符
collection items terminated by ‘_’ 集合数据中的多个元素,例如数组中多个元素使用_隔开
map keys terminated by ‘:’ map中key与value的分隔符
lines terminated by ‘\n’ 行分隔符
DDL数据定义
创建数据库
略
查询数据库
- show databases;
- show databases like ‘db’;
- use db_hive;
- desc database extended db_hive; //显示数据库详细信息
- alter database db_hive set dbproperties(‘createtime’=‘XXXXXXX’) //修改数据库
删除数据库
drop 略
创建表
查看详情
1.建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
2.字段解释说明
(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
(3)COMMENT:为表和列添加注释。
(4)PARTITIONED BY创建分区表
(5)CLUSTERED BY创建分桶表
(6)SORTED BY不常用,对桶中的一个或多个列另外排序
(7)ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。
(8)STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
(9)LOCATION :指定表在HDFS上的存储位置。
(10)AS:后跟查询语句,根据查询结果创建表。
(11)LIKE允许用户复制现有的表结构,但是不复制数据。
管理表(内部表)
默认创建,删除时数据一起删除,hive可以控制数据的声明周期
外部表
删除时进删除 用来描述表的元数据信息。使用external关键字创建
再创建外部表的同时应该指定外部表的 路径
管理表与外部表的相互转换
通过alter修改表的属性
分区表
hive中分区就是分目录。一个分区对应一个独立的hdfs上的文件夹。
示例(创建):xxxxx partitioned by (month string) xxx
注意,我们这里创建分区表只是描述 这个表将以 什么作为分区标准。而导入数据时还需另外指名分区的属性,才能真正完成分区
示例(加载): xxxx default.dept_partition partition(month = ‘20200801’)
在加载分区数据时,必须指名分区
示例(查询): select * from table1 where month = “20200801”
union
select * from table2 where month = “20200802”
查询多个分区时,应该使用联合查询
示例(增删):alter table table1 add/drop partition(month = “20200801”) partition(month=“20200802”)
示例: show partition table1;//查看多少个分区
desc formatted table1;//查看分区结构
二级分区
略
DML数据操作
数据导入
装载数据(load)
语法: load data [local] inpath ‘opt/module/datas/student.txt’ [overwirte] into table student [patition(partcol1=val1)];
解释: overwrite 表示覆盖数据,否则表示追加
插入数据(Insert)
基本用法与sql相同,演示 : insert overwrite xxxx select xxxx
创建表同时加载数据(as select)
create xxx as select xx
创建表同时通过location加载数据路径
略
import导入数据到指定的表中
import table table1 partition(month = “20200801”) from ‘/path’
数据应该先export导出再导入
数据导出
Insert导出
示例:insert overwriter local directory ‘/path’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select * from student
Hadoop命令导出
直接将存储数据的文件get
Hive shell导出
export导出
export table default.student to’/path’;
Sqoop导出
清除数据
truncate table student;trncate命令只能山粗管理表中的数据
查询(极重要)
基本查询(select from)
与sql相同
别名查询(as)
与sql相同
常用函数(常用)
select count(*) cnt from emp; //总行数
select max(sal) max_sal from emp; // 最大值
min,sum,avg //最小值,总和,平均值
Limit语句
select * from emp limit 5; //只取前五条数据
where
比较运算符
between/in/is null
like 与 rlike(可通过正则表达式匹配)
逻辑运算符
and/or/not
分组
group by语句
group by语句常和聚合函数一起使用,按照某个标准进行分组,然后对每个组执行聚合操作
例如:计算每个岗位中薪水最高的
select t.deptno,t.job,max(t.sal) from emp t group by t.deptno,t.job;
查询将按照每个部门每个岗位进行分组后 完成聚合 输出想要的数据,而不是将每条数据都输出
having语句
having 与 where不同点
- where后面不能写分组函数,而having后面可以使用分组函数
- having只用于 group by 分组统计语句
示例:select t.deptno,t.job,avg(t.sal) avg_sal from emp t group by t.deptno having avg_sal>2000;
原先我们只统计每个部门的平均工资,加上having以后我们可以指定查看平均工资超过了2000的部门。注意having只能跟group by 使用
Join语句
等值Join
hive支持等值连接,不支持非等值连接
- 内连接 join
- 左外连接 left join
- 右外连接 right join
- 满外链接 full join msql不支持满外连接
- 条件 on xxxx
优化:hive会对每个join连接对象启动一个mapreduce任务,当对三个及以上表进行join时,如果每个on子句使用相同的连接条件,那么只产生一个mapreduce任务
排序
全局排序(order by)
只有一个reducer
asc 升序(默认) desc 降序
select * from emp order by sal desc;
sort by 每个reducer内部排序,因此可以通过设置多个reducer来提高效率
分区排序(distribute by)
类似于MR中的自定义分区(partition),结合sort by 使用
insert overwrite local directory ‘/path’ select * from emp distribute by deptno sort by empno desc;
按照部门编号进行分区,再按照员工编号降序排序
注意:
- distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除以后,余数相同的分到一个区
- hive要求distribute by 必须卸载sort by 之前
cluster by
当distibute by 与sort by字段相同时可以使用cluster by代替,但是只能升序排
分桶及抽样查询
对于一张表或者分区,hive可以将进一步组长成桶,是更为细粒度得的数据划分
分区针对的是数据的存储路径,分桶针对的是数据文件
创建一个分桶表
create table stu_buck(id int,name string)
cluster by(id) into 4 buckets row format delimited fields termianted by ‘\t’;
使用粪桶表需要事先设置分桶属性 : set.hive.enforce.bucketing=true;
分桶规则:hive分桶采用对分桶字段的值进行哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶中
分桶抽样查询
select * from stu_buck tablesample(bucket 1 out of 4 on id)
解释:tablesample(bucket x out of y on id)
y表示抽取多少个桶数据,y必须为桶的倍数或者因子
x表示从哪个桶开始抽取数据,如果有多个分区,以后的分区号 = 当前分区号+y
x<=y必须成立,否则报错
其他常用查询手段
空字段赋值
select comm,nvl(comm,-1) from emp;
如果员工的comm字段为null,用-1代替
case when
例如:求男女各有多少人 sum(case sex when ‘男’ then 1 else 0 end) male_count,sum(case sex when’女’ then 1 else 0 end) female_count
当性别为男时 male_count+1 否则 male_count+0
行转列
将多列数据转换为行,类比聚合函数
相关函数说明:
- CONCAT(string A/col,string B/col,…):返回字符串拼接后的结果
- CONCAT_WS(separatot,str,str,…):使用分隔符连接
- CONCAT_SET(col):函数只接受基本数据类型,将某个字段的值进行去重汇总,产生array类型字段
我们使用时,常分组后统计该组状况。例如统计 白羊座的有哪个同学
列转行
explode(col): 将一列中复杂的array或者map 结构拆分为多行
lateral view:用法 lateral view udtf(expression) tableAlias as columnAlias。用于和split,explode等UDTF一起使用,他能将一列数据拆分成多行数据,再次基础上再进行聚合
例: lateral view explode(category) table_tem as category_name;
窗口函数(开窗函数)
函数说明:
- over(): 指定分析函数工作的数据窗口大小,这个窗口大小可能随着行的变化而变化
- current row : 当前行
- n perceding: 往前n行数据
- n following: 往后n行数据
- unbouded:起点,unbouded preceding 表示从前面的起点,unbouded follwing 表示到后面终点
- lag(col,n,default_val):往前第n行数据
- lead(col,n,default):往后第n行数据
- ntile(n):将有序分区中的行分发到指定数据的组中,各组中有编号,编号从1开始,对于每行,ntile返回此行所属的组的编号。将行分组
开窗函数可以类别滑动窗口,它将一些函数放在一个窗口从而进行聚合等操作。开窗函数聚焦的问题在于从哪开窗,多大的窗。
示例:
1、查询在2017年4月份购买过的顾客及总人数
select name,count(*) over() from business where substring(orderdate,1,7) = ‘2017-04’ group by name;
开窗函数容易与分组函数混淆,两种不同之处在于 上述例子中如果删除over()函数,那么分组将按照整个表进行分组,就会统计整个够买次数
2、查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
对每个月开窗,由此统计数额
rank函数
RANK() 排序相同时会重复,总数不会变。适用于班级排名
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算