Hive基础知识点整理_row format delimited fields

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 //最小值，总和，平均值
1
2
3

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任务

• on子句中暂不支持or

排序

全局排序（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() 会根据顺序计算