hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文

转载

mob64ca140b82e3 2023-10-27 00:55:44

文章标签 hive离线数仓怎么做大宽表做数据整合 hive sql 大数据 hadoop 文章分类 Hive 大数据

Hive是SQL的抽象，高延迟。由Facebook研发

Hive基本架构

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_大数据

Hive通过CLI/JDBC/ODBC或者HWI接受相关的HiveSQL查询，并通过Driver组件进行编译，分析优化最后变成可执行的MapReduce。Hive主要组件执行过程如下：

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_大数据_02

Hive SQL关键概念

内部表（managed table）：Hive管理的表，包含实际的物理意义。删除数据也没了。

CREATE TABLE mu_managed_table（coll STRING）；

LOAD DATA INPATH’/user/root/tesst_data.txt’ INTO table my_managed_table

真实把数据移动到目录，但是仍旧非常快，因为hive不会对数据是否符合定义的Schema做校验。

外部表（external table）：只是一个引用。不会移动数据，甚至不会校验外部表是否存在，这使得我们可以在创建表之后再创建数据。

如何选择：如果数据的所有处理都在hive中进行，那么更倾向内部表。
一种常见的模式是使用外部表访问存储的HDFS中的初始数据，然后使用HIve转换数据并将其结果放在内部表中。相反，外部表也可以用于将hive的处理结果导出供去其他应用使用。使用外部表的另一种场景是针对一个数据集，关联多个schema。

分区：针对每一天或者某几天数据的查询会变得很高效，因为只需要扫描对应分区的文件。分区并不会导致跨度大的查询变得抵消。

CREATE TABLE logs（ts BIGINT , line STRING）
PARTITONED BY （st STRING ,country STRING）;

当导入数据到分区表时，分区的值被显式指定：

LOAD DATA INPATH ‘/user/root/path’

INTO TABLE logs

PARTITION （dt=‘2001-01-01’,country=‘GB’);

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_hive离线数仓怎么做大宽表做数据整合_03

分桶：表或者分区可以进一步被划分为桶（bucket）。
一是为了高效查询，例如两个表根据相同的字段进行分桶，则在对这两个表进行关联的时候可以使用map-side关联高效实现。2个表的BUCKET要成倍数
二是可以高效的进行抽样。
、CREATE TABLE bucketed_users(id INT， name STRING)
clustered BY (id) INTO 4 BUCKETS;

Hive的三种集合数据类型
ARRAT
MAP
STRUCT

Hive数据库的各种操作语句

1创建数据库

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_sql_04

例如

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_hive离线数仓怎么做大宽表做数据整合_05

2切换数据库

hive>use my_hive database;

3.查看数据库
hive> describe database my_hive_databse;

4.删除数据库
hive >drop database my_hive_database;

默认情况下，hive不允许用户删除一个包含表的数据库，用户要先删除数据库中的表，再删除数据库；或者在删除命令中的最后加上关键字CASCADE；
hive >drop database my_hive_database CASCADE

5.查看所有数据库
hive> show databases;

hive表的定义操作

1.创建表

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_sql_06

*ROW FORMAT: 用户在创建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT或者ROW FORMAT DELIMITED,将会使用自带的SerDe；Hive通过SerDe确定表的具体的列的数据。

DELIMITED(delimited）样子：

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_hive_07

*STORED(stored） AS :如果文件数据是纯文本，则使用STORED AS TEXTFILE；如果数据需要压缩，则使用STORED AS SWQUENCE。

*LIKE：允许用户复制现有的表结构，但是不复制数据，

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_hadoop_08

另外还可以通过查询语句来建表。

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_hadoop_09

2.修改表

重命表：ALTER TABLE old_table_name REMANE TO new_table_name;

重名列：ALTER TABLE table_name CHANGE old_col_name new_col_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

*上述语法允许改变列名、数据类型、注释、列位置

新增列：ALTER TABLE table_name ADD COLUMN(new_col INT );

3.删除表
DROP TABLE 语句用于删除表的数据和元数据。对于外部表，只删除METASTORE中的元数据，而外部数据保存不动。
DROP TABLE my_table；
如果只想删除表数据，保留表结构，跟Mysql类似，使用truncate语句：
TRUNCATE TABLE my_table；

4.加载数据

1）向表中加载数据
 LOAD DATA [LOCAL] INPATH ‘filepath’
 [OVERWRITE] INTO TABLE tablename[PARTITION]
 *LOCOL 本地系统用，拷贝 不选择就是移动
 *OVERWRITE 删掉原来的2）将查询结果插入到Hive表
 基本模式：
 INSERT OVERWRITE TABLE table_mane
 PARTITION (partcol=val1,partcol=val2,…)
 SELECT * FROM tablename2
 WHERE XXXXX多插入模式：
 FROM tablename2
 INSERT OVERWRITE TABLE table_mane
 PARTITION (partcol=val1,partcol=val2,…)
 SELECT *
 WHERE XXXXX
 INSERT OVERWRITE TABLE table_mane
 PARTITION (partcol=val1,partcol=val2,…)
 SELECT *
 WHERE XXXXX自动分区模式：
 INSERT OVERWRITE TABLE table_name
 PARTITION(country,state)
 SELECT col1,col2,cnty,st)
 FROM table_name2
 *根据SELECT最后2个列名来确定分区country和state的值。

hive表的DML（数据操作）

1.基本的select操作

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_sql_10

*默认ALL，distinct表示去除重复记录

*WHERE 类似于传统的where ，支持and or between in not in

*ORDER BY 全局排序，只有一个Reduce任务。而SORT BY只在本机做排序

*LIMIT 限制查询的记录数

*可以用正则表达式制定列名

*当选择的列是集合数据类型时，Hive会使用JSON语法应用于输出。

引用集合数据类型中的元素方法：

array：col_name[0]

map: col_mane[“健值”]

struct: col_name.键值对前面的

2.JOIN语句
Hive只支持等值连接。2.2.0版本后开始支持非等值连接
1）INNER JOIN 、left outer join 、right outer join、outer join
2）join达标放在最后。因为Reduce会缓存join序列中除了最后一个表之外的表
3）LEFT SEMI JOIN 是 IN/EXISTS子查询的一种更高效的实现。

HiveSQL 执行原理图解
一个好的HiveSQL 和一个写的不好的HiveSQL 对底层计算和资源的使用可能相差百倍甚至千倍、万倍，而一个计算集群的slot是有限的。
SQL基本模式可以归为三类：
SELECT
GROUP BY
JOIN

SELECT

hive离线数仓怎么做大宽表做数据整合 hive的离线分析论文_hive离线数仓怎么做大宽表做数据整合_11