HIVE学习笔记

用户1310455228169
136 阅读12分钟

HIVE是什么

1 ） Hive简介

Hive是由Facebook开源，基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

那为什么会有Hive呢？它是为了解决什么问题而诞生的呢？

下面通过一个案例，来快速了解一下Hive。

例如：需求，统计单词出现个数。

（1）在Hadoop课程中我们用MapReduce程序实现的，当时需要写Mapper、Reducer和Driver三个类，并实现对应逻辑，相对繁琐。

test表

id 列****

atguigu

atguigu

ss

ss

jiao

banzhang

xue

hadoop

（2）如果通过Hive SQL实现，一行就搞定了，简单方便，容易理解。

select count(*) from test group by id;

2 ） Hive 本质

Hive是一个Hadoop客户端，用于将HQL（Hive SQL）转化成MapReduce程序。

（1）Hive中每张表的数据存储在HDFS

（2）Hive分析数据底层的实现是MapReduce（也可配置为Spark或者Tez）

（3）执行程序运行在Yarn上

架构图

image.png

HIVE基础语法

表

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...)]

关键词

1 TEMPORARY

临时表，该表只在当前会话可见，会话结束，表会被删除。

2 EXTERNAL （重点）

外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。 3 data_type （重点）****

Hive中的字段类型可分为基本数据类型和复杂数据类型。

基本数据类型如下：

Hive说明定义
tinyint1byte有符号整数 
smallint2byte有符号整数 
int4byte有符号整数 
bigint8byte有符号整数 
boolean布尔类型，true或者false 
float单精度浮点数 
double双精度浮点数 
decimal十进制精准数字类型decimal(16,2)
varchar字符序列，需指定最大长度，最大长度的范围是[1,65535]varchar(32)
string字符串，无需指定最大长度 
timestamp时间类型 
binary二进制数据 

复杂数据类型如下；

类型说明定义取值
array数组是一组相同类型的值的集合arrayarr[0]
mapmap是一组相同类型的键-值对集合map<string, int>map['key']
struct结构体由多个属性组成，每个属性都有自己的属性名和数据类型struct<id:int, name:string>struct.id

注：类型转换

Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及显示转换。

4 PARTITIONED BY （重点）

创建分区表

5 CLUSTERED BY ... SORTED BY...INTO ... BUCKETS （重点）

创建分桶表

6 ROW FORMAT （重点）

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

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

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 ：行分隔符

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

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

7 STORED AS （重点）

指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

8 LOCATION

指定表所对应的HDFS路径，若不指定路径，其默认值为

${hive.metastore.warehouse.dir}/db_name.db/table_name

9 TBLPROPERTIES

用于配置表的一些KV键值对参数

查询语法

  SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference       -- 从什么表查
  [WHERE where_condition]   -- 过滤
  [GROUP BY col_list]        -- 分组查询
   [HAVING col_list]          -- 分组后过滤
  [ORDER BY col_list]        -- 排序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]                -- 限制输出的行数

JOIN 语法

select

    e.empno,

    e.ename,

    d.dname

from emp e

join dept d

on e.deptno = d.deptno;

主要的语法都是一致的，主要有一个满连接，满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。

排序

  1. SORT BY：

    • SORT BY用于在查询结果中对数据进行排序，但不保证全局排序。
    • SORT BY仅对当前查询的结果进行排序，不会对数据进行全局排序。
    • SORT BY可以与LIMIT一起使用，以获取排序后的前N行。

  2. ORDER BY：

    • ORDER BY用于对查询结果进行全局排序。
    • ORDER BY会对所有数据进行排序，然后返回排序后的结果。
    • ORDER BY可能需要更多的计算和资源，特别是在处理大量数据时。

  3. DISTRIBUTE BY：

    • DISTRIBUTE BY用于在Hive中进行数据分发，以便在后续的REDUCE阶段进行并行处理。
    • DISTRIBUTE BY将数据分发到不同的Reducer节点上，以便并行处理。
    • DISTRIBUTE BY通常与SORT BY或CLUSTER BY一起使用，以在分发数据的同时进行排序或聚合。

  4. CLUSTER BY：

    • CLUSTER BY 用于对表进行排序和分区，以便将相似的值聚集在一起。
    • CLUSTER BY 会对数据进行全局排序，并根据排序结果对数据进行分区。
    • CLUSTER BY 通常与 SORT BY 或 ORDER BY 一起使用，以在排序的同时进行分区。

  5. 区别：

    • CLUSTER BY 和 ORDER BY 都可以对数据进行全局排序，但 CLUSTER BY 还会根据排序结果对数据进行分区，而 ORDER BY 不会。
    • CLUSTER BY 和 SORT BY 都可以对数据进行排序，但 CLUSTER BY 会尽量将相似的值聚集在一起，而 SORT BY 只是简单地对数据进行排序。

总结起来，SORT BY用于局部排序，ORDER BY用于全局排序，而DISTRIBUTE BY用于数据分发和并行处理。根据查询的需求和数据量的大小，选择适当的排序方式可以提高查询的性能和效率。

函数

数值函数

1） round：四舍五入 `

hive> select round(3.3);   3`

2 ） ceil ：向上取整

` hive> select ceil(3.1) ;   4`

3 ） floor ：向下取整

hive> select floor(4.8);  4

日期函数

1.unix_timestamp：返回当前或指定时间的时间戳

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

输出：

1659946088

说明：-前面是日期后面是指，日期传进来的具体格式 2.from_unixtime ：转化 UNIX 时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

3.current_date：当前日期，current_timestamp：当前的日期加时间， 并且精确的毫秒，month：获取日期中的月day：获取日期中的日hour：获取日期中的小时

4.datediff：两个日期相差的天数（结束日期减去开始日期的天数） 语法： datediff(string enddate, string startdate)

返回值：int

案例实操： hive> select datediff('2021-08-08','2022-10-09');
输出： -427

5.date_add：日期加天数

语法：date_add(string startdate, int days)

返回值：string 说明：返回开始日期 startdate 增加 days 天后的日期 案例实操： hive> select date_add('2022-08-08',2);

输出： 2022-08-10

6.date_sub：日期减天数

语法：date_sub (string startdate, int days)

返回值：string

说明：返回开始日期startdate减少days天后的日期。

案例实操：

hive> select date_sub('2022-08-08',2);   

输出：

2022-08-06

date_format:将标准日期解析成指定格式字符串

hive> select date_format('2022-08-08','yyyy年-MM月-dd日')  

输出：

2022年-08月-08日

集合函数

1）size：集合中元素的个数

hive> select size(friends) from test; --2/2 每一行数据中的friends集合里的个数

2）map：创建map集合

语法：map (key1, value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

案例实操： hive> select map('xiaohai',1,'dahai',2);

输出： hive> {"xiaohai":1,"dahai":2}

3）map_keys： 返回map中的key

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

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

4）map_values: 返回map中的value

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

输出： hive>[1,2]

5）array 声明array集合

语法：array(val1, val2, …)

说明：根据输入的参数构建数组array类

案例实操： hive> select array('1','2','3','4');

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

6）array_contains: 判断array中是否包含某个元素

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

输出： hive> true

7）sort_array：将array中的元素排序

hive> select sort_array(array('a','d','c'));

输出： hive> ["a","c","d"]

8）struct声明struct中的各属性

语法：struct(val1, val2, val3, …)

说明：根据输入的参数构建结构体struct类

案例实操： hive> select struct('name','age','weight');

输出： hive> {"col1":"name","col2":"age","col3":"weight"}

9）named_struct声明struct的属性和值

hive> select named_struct('name','xiaosong','age',18,'weight',80);

输出： hive> {"name":"xiaosong","age":18,"weight":80}

炸裂函数

"炸裂函数"通常指的是 explode() 函数，它用于将数组或多个元素拆分成多行数据。explode() 函数将数组中的每个元素拆分成一行，并复制其他列的值。以下是 explode() 函数的示例用法：

假设你有一个包含数组的表 your_table，其中包含一个名为 array_col 的列，你可以使用 explode() 函数将数组拆分成多行数据：

sql
InsertCopy code
SELECT col1, col2, exploded_value
FROM your_table
LATERAL VIEW explode(array_col) exploded_table AS exploded_value;

在这个查询中，array_col 是包含数组的列名，col1 和 col2 是其他列的名称。explode() 函数将 array_col 拆分成多行数据，并将拆分后的值命名为 exploded_value。通过 LATERAL VIEW 子句，我们可以将拆分后的值与其他列的值一起返回。

SELECT cate, count(*)
FROM (
 SELECT movie, cate
 FROM (
     SELECT movie, split(category,',') cates
     FROM movie_info
 ) t1
 LATERAL VIEW explode(cates) tmp AS cate
) t2
GROUP BY cate;

这个查询的目的是从 movie_info 表中统计每个电影分类的数量。

  1. 内部嵌套查询：

    • 内部嵌套查询 t1 从 movie_info 表中选择 movie 和 split(category,',') cates。
    • split(category,',') 将 category 字段按逗号分隔为一个数组 cates。

  2. LATERAL VIEW explode()：

    • LATERAL VIEW explode(cates) tmp AS cate 将 cates 数组展开为多行，每行包含一个分类 cate。

  3. 外部嵌套查询：

    • 外部嵌套查询 t2 选择 movie 和 cate 字段，这是从内部嵌套查询中获得的结果。

  4. 聚合查询：

    • GROUP BY cate 对 cate 字段进行分组。
    • count(*) 统计每个分类的数量。

最终的结果将按分类（cate）分组，并统计每个分类的数量。

窗口函数

LAG和LEAD

LAG 和 LEAD 是窗口函数，用于在查询结果中访问当前行之前或之后的行的值。以下是 LAG 和 LEAD 函数的示例用法：

  1. LAG 函数示例：

    sql
InsertCopy code
SELECT col, LAG(col) OVER (ORDER BY order_col) AS lag_value
FROM your_table;

    在这个示例中，LAG 函数用于获取当前行之前的行的值。col 是要访问的列名，order_col 是用于排序的列名。LAG 函数将返回当前行之前的行的值。

  2. LEAD 函数示例：

    sql
InsertCopy code
SELECT col, LEAD(col) OVER (ORDER BY order_col) AS lead_value
FROM your_table;

    在这个示例中，LEAD 函数用于获取当前行之后的行的值。col 是要访问的列名，order_col 是用于排序的列名。LEAD 函数将返回当前行之后的行的值。

这些示例中的 your_table 是你要查询的表名，col 是你要访问的列名，order_col 是用于排序的列名。你可以根据你的实际情况进行相应的调整。

请注意，LAG 和 LEAD 函数需要在窗口函数中使用，并且需要指定排序顺序。它们可以帮助你在查询结果中访问当前行之前或之后的行的值。

first_value和last_value

  1. FIRST_VALUE 函数示例：

  sql
  InsertCopy code
  SELECT col, FIRST_VALUE(col) OVER (PARTITION BY partition_col ORDER BY order_col) AS first_value
  FROM your_table;

在这个示例中，FIRST_VALUE 函数用于获取窗口内第一个行的值。col 是要访问的列名，partition_col 是用于分区的列名，order_col 是用于排序的列名。FIRST_VALUE 函数将返回窗口内第一个行的值。

  1. LAST_VALUE 函数示例：
 sql
 InsertCopy code
 SELECT col, LAST_VALUE(col) OVER (PARTITION BY partition_col ORDER BY order_col ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
 FROM your_table;

在这个示例中，LAST_VALUE 函数用于获取窗口内最后一个行的值。col 是要访问的列名，partition_col 是用于分区的列名，order_col 是用于排序的列名。LAST_VALUE 函数将返回窗口内最后一个行的值。

这些示例中的 your_table 是你要查询的表名，col 是你要访问的列名，partition_col 是用于分区的列名，order_col 是用于排序的列名。你可以根据你的实际情况进行相应的调整。

请注意，FIRST_VALUE 和 LAST_VALUE 函数需要在窗口函数中使用，并且需要指定分区和排序。它们可以帮助你在窗口内获取第一个和最后一个行的值。

rank 、dense_rank、row_number

1.  `RANK` 函数示例：

```
sql
InsertCopy code
SELECT col, RANK() OVER (ORDER BY order_col) AS rank
FROM your_table;
```

在这个示例中，`RANK` 函数用于为查询结果中的行分配排名。`col` 是要返回的列名，`order_col` 是用于排序的列名。`RANK` 函数将为每一行分配一个排名。

  1. DENSE_RANK 函数示例：

    sql
InsertCopy code
SELECT col, DENSE_RANK() OVER (ORDER BY order_col) AS dense_rank
FROM your_table;

    在这个示例中，DENSE_RANK 函数用于为查询结果中的行分配密集排名。col 是要返回的列名，order_col 是用于排序的列名。DENSE_RANK 函数将为每一行分配一个密集排名。

  2. ROW_NUMBER 函数示例：

    sql
InsertCopy code
SELECT col, ROW_NUMBER() OVER (ORDER BY order_col) AS row_number
FROM your_table;

    在这个示例中，ROW_NUMBER 函数用于为查询结果中的行分配行号。col 是要返回的列名，order_col 是用于排序的列名。ROW_NUMBER 函数将为每一行分配一个行号。

这些示例中的 your_table 是你要查询的表名，col 是你要返回的列名，order_col 是用于排序的列名。你可以根据你的实际情况进行相应的调整。

avatar
文章
阅读
粉丝