HIVE学习笔记
2024-07-29 14:30:40 # hive # 学习笔记 #hive

Hive学习笔记

本笔记不做hive源码和其他说明，只做HQL的一些关键说明

创建表

关键字说明

临时表

1
CREATE TEMPORARY TABLE table_name (col1 type, col2 type, ...);

临时表，会话结束表就会删除

外部表

删除表的时候不会删除hdfs数据，而是删除元数据

1
2
3
4
5
6
7
8
CREATE EXTERNAL TABLE external_table (
  id INT,
  name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/data/mydata';

内部表

删除表的时候会同时删除hdfs数据，默认就是内部表

内部表和外部表，可以创建一个表对应上hdfs上的路径的文件，当向这个文件追加写入数据的时候hive上的数据也会变

  1. 创建外部表
1
2
3
4
5
6
create external table if not exists student(
    id int, 
    name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';
  1. 创建文件并且上传到hive表对应的hdfs路径上
    1
    vim /opt/module/datas/student.txt
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    1001	student1
    1002	student2
    1003	student3
    1004	student4
    1005	student5
    1006	student6
    1007	student7
    1008	student8
    1009	student9
    1010	student10
    1011	student11
    1012	student12
    1013	student13
    1014	student14
    1015	student15
    1016	student16
  2. 上传文件到hdfs上
1
hadoop fs -put student.txt /user/hive/warehouse/student
  1. 查看表数据
    查看表数据会发现hive上的数据已经更新了

数据格式

基本数据格式
数据类型 说明 定义
tinyint 1byte有符号整数
smallint 2byte有符号整数
int 4byte有符号整数
bigint 8byte有符号整数
boolean 布尔类型，true或者false
float 单精度浮点数
double 双精度浮点数
decimal 十进制精准数字类型 decimal(16,2)
varchar 字符序列，需指定最大长度，最大长度的范围是[1,65535] varchar(32)
string 字符串，无需指定最大长度
timestamp 时间类型
binary 二进制数据
复杂数据格式
类型 说明 定义 取值
array 数组是一组相同类型的值的集合 array<string> arr[0]
map map是一组相同类型的键-值对集合 map<string, int> map['key']
struct 结构体由多个属性组成，每个属性都有自己的属性名和数据类型 struct<id:int, name:string> struct.id

分区表

1
CREATE TABLE table_name (col1 type, col2 type) PARTITIONED BY (col2);

分桶表

1
CLUSTERED BY ... SORTED BY...INTO ... BUCKETS

ROW FORMAT

指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde。语法说明如下：

语法一：DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。

1
2
3
4
5
6
ROW FORAMT DELIMITED 
[FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char] 
[NULL DEFINED AS char]

注：

  • fields terminated by ：列分隔符
  • collection items terminated by ： map、struct和array中每个元素之间的分隔符
  • map keys terminated by ：map中的key与value的分隔符
  • lines terminated by ：行分隔符
1
2
3
4
5
6
create table if not exists student(
    id int, 
    name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';

语法二：SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。

1
ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

STORED AS

指定存储格式，默认是TEXTFILE。

LOCATION

指定表的hdfs路径

显示转换

cast函数实现类型转换

1
select '1' + 2, cast('1' as int) + 2;

复杂建表案例

假如说我们有一个以下格式的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
    "name": "dasongsong",
    "friends": [
        "bingbing",
        "lili"
    ],
    "students": {
        "xiaohaihai": 18,
        "xiaoyangyang": 16
    },
    "address": {
        "street": "hui long guan",
        "city": "beijing",
        "postal_code": 10010
    }
}

这里使用专门负责JSON文件的JSON Serde

1
2
3
4
5
6
7
8
9
create table teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<city:string,street:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';
1
vim /opt/module/datas/teacher.txt
1
{"name":"dasongsong","friends":["bingbing","lili"],"students":{"xiaohaihai":18,"xiaoyangyang":16},"address":{"street":"hui long guan","city":"beijing","postal_code":10010}}
1
hadoop fs -put teacher.txt /user/hive/warehouse/teacher

alt text

导出和导入

1
2
3
4
5
6
7
--导出

export table default.student to '/user/hive/warehouse/export/student';

--导入

import table student2 from '/user/hive/warehouse/export/student';

having和where不同掉

where后面不能使用聚集函数，having后面可以使用
having只能用于group by分组统计的语句

简单来说就是where是在分组前过滤，having是在分组后过滤

1
2
3
SELECT sale_id, product_id, quantity, price
FROM sales
WHERE quantity > 5;
1
2
3
4
SELECT product_id, SUM(quantity) as total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 10;

笛卡尔集

笛卡尔集会在下面条件下产生：

（1）省略连接条件

（2）连接条件无效

（3）所有表中的所有行互相连接

union和union all区别

union是去重

union all不去重

分区

Distribute By

指定按照哪个字段分配到reduce中,优化Hive查询性能，特别是在处理大数据集时

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE student_scores (
  student_id INT,
  subject STRING,
  score INT
);

INSERT INTO student_scores VALUES
(1, 'Math', 85),
(2, 'Math', 90),
(1, 'Science', 88),
(2, 'Science', 82),
(3, 'Math', 75),
(3, 'Science', 80);

有一个学生表，我们现在希望将相同的student_id分配到同一个reuce任务中

1
2
3
SELECT student_id, subject, score
FROM student_scores
DISTRIBUTE BY student_id;

Cluster By

分区排序，但是配许只能是升序，也就是在distribute by基础上加上在同一个reduce中排序

以下两种写法对等

1
2
3
4
select 
    * 
from emp 
cluster by deptno;
1
2
3
4
5
select 
    * 
from emp 
distribute by deptno 
sort by deptno;

代替null

nvl函数

1
select nvl(null,1); 
1
nvl(A,B)

若A的值不为null，则返回A，否则返回B。

按照指定的分隔符分割字符串或者字符串数组

1
select concat_ws('-','beijing','shanghai','shenzhen');

输出：beijing-shanghai-shenzhen

解析json字符串

1
select get_json_object('[{"name":"chen","sex":"男","age":"23"},{"name":"liu","sex":"男","age":"47"}]','$.[0].name');

输出chen

返回指定时间戳

1
select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');

输出：1659946088

两个日期相差的天数

1
select datediff('2021-08-08','2022-10-09');

输出：-427

case when

case when a then b [when c then d]* [else e] end

如果a为true，则返回b；如果c为true，则返回d；否则返回 e 

1
select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tabl eName;

输出：mary

创建map集合

1
select map('xiaohai',1,'dahai',2);

输出：{xiaohai:1, dahai:2}

返回map中的key

1
select map_keys(map('xiaohai',1,'dahai',2));

输出：["xiaohai","dahai"]

返回map中的value

1
select map_values(map('xiaohai',1,'dahai',2));

输出：[1,2]

创建array

1
select array('1','2','3','4');

输出：
["1","2","3","4"]

判断array中是否包含某个元素

select array_contains(array('a','b','c','d'),'a');

上一页
2024-07-29 14:30:40 # hive # 学习笔记 #hive
下一页