网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
当导入数据到分区表时,分区的值被显式指定:
LOAD DATA INPATH ’/user/root/path’
INTO TABLE logs
PARTITION (dt=‘2001-01-01’,country='GB’);
实际 SQL 中,灵活指定分区将大大提高其效率,如下代码将仅会扫描 `2001-01-01`下的 `GB` 目录。
SELECT ts , dt , line FROM logs WHERE dt=‘2001-01-01’ and country=‘GB’
###### ( 2 )分桶
在表或者分区中使用桶通常有两个原因:
* 一是为了高效查询 。桶在表中加入了特殊的结果, Hive 在查询的时候可以利用这些结构提高效率。例如,如果两个表根据相同的字段进行分桶,则在对这两个表进行关联的时候,可以使用 map-side 关联高效实现,前提是关联的字段在分桶字段中出现。
* 二是可以高效地进行抽样。 在分析大数据集时,经常需要对部分抽样数据进行观察和分析,分桶有利于高效实现抽样。
为了让 Hive 对表进行分桶,通过 `CLUSTERED BY` 从句在创建表的时候指定:
CREATE TABLE bucketed users(id INT, name STRING)
CLUSTERED BY (id) INTO 4 BUCKETS;
指定表根据 id 字段进行分桶,并且分为 4 个桶 。分桶时, Hive 根据字段哈希后取余数来决定数据应该放在哪个桶,因此每个桶都是整体数据的随机抽样。
在 map-side 的关联中,两个表根据相同的宇段进行分桶,因此处理左边表的 bucket 时,可以直接从外表对应的 bucket 中提取数据进行关联操作。 map-side 关联的两个表不一定需要完全相同 bucket 数量,只要成倍数即可。
需要注意的是, Hive 并不会对数据是否满足表定义中的分桶进行校验,只有在查询时出现异常才会报错 。因此,一种更好的方式是将分桶的工作交给 Hive 来完成(设 `hive.enforce.bucketing` 属性为 true 即可)。
#### Hive DDL
##### 1. 创建表
* CREATE TABLE:用于创建一个指定名字的表 。如果相同名字的表已经存在,则抛出异常 用户可以用 IF NOT EXIST 选项来忽略这个异常。
* EXTERNAL :该关键字可以让用户创建一个外部表,在创建表的同时指定一个指向实际数据的路径(LOCATION)。
* COMMENT :可以为表与字段增加描述。
* ROW FORMAT :用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。
* STORED AS :如果文件数据是纯文本,则使用 STORED AS TEXTFILE ;如果数据需要压缩, 则使用 STORED AS SEQUENCE 。
* LIKE: 允许用户复制现有的表结构,但是不复制数据。
hive> CREATE TABLE empty key value store
LIKE key value store;
还可以通过 CREATE TABLE AS SELECT 的方式来创建表,示例如下:
Hive> CREATE TABLE new key value store
ROW FORMAT
SERDE “org.apache.Hadoop.hive.serde2.columnar.ColumnarSerDe”
STORED AS RCFile
AS
SELECT (key % 1024) new_key, concat(key, value) key_value_pair
FROM key_value_store
SORT BY new_key, key_value_pair;
##### 2. 修改表
修改表名的语法如下:
hive> ALTER TABLE old_table_name RENAME TO new_table_name;
修改列名的语法如下:
ALTER TABLE table_name CHANGE (COLUMN) old_col_name new_col_name column_type
[COMMENT col_comment) (FIRST|AFTER column_name)
上述语法允许改变列名 数据类型 注释 列位 它们的任意组合 建表后如果要新增一列,则使用如下语法:
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT COMMENT ‘new col comment’);
##### 3. 删除表
DROP TABLE 语句用于删除表的数据和元数据 。对于外部表,只删除 Metastore 中的元数据,而外部数据保存不动,示例如下:
drop table my_table;
如果只想删除表数据,保留表结构,跟 MySQL 类似,使用 TRUNCATE 语句:
TRUNCATE TABLE my_table;
##### 4. 插入表
###### ( 1 )向表中加载数据
相对路径的示例如下:
hive> LOAD DATA LOCAL INPATH ’./exarnples/files/kvl.txt ’ OVERWRITE INTO
TABLE pokes;
###### ( 2 )将查询结果插入 Hive
将查询结果写入 HDFS 文件系统。
INSERT OVERWRITE TABLE tablenamel [PARTITION (partcoll=val1, partcol2=val2 … )]
select_statement1 FROM from_statement
这是基础模式,还有多插入模式和自动分区模式,这里就不再叙述。
#### Hive DML
##### 1. 基本的 select 操作
SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[ CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY | ORDER BY col_list]
]
[LIMIT number]
* 使用 ALL、 DISTINCT 选项区分对重复记录的处理 。默认是 ALL ,表示查询所有记录, DISTINCT 表示去掉重复的记录
* WHERE 条件:类似于传统 SQL的 where 条件,支持 AND 、OR 、BETWEEN、 IN 、NOT IN 等
* ORDER BY 与 SORT BY 的不同: ORDER BY 指全局排序,只有一个 Reduce 任务,而 SORT BY 只在本机做排序
* LIMIT :可以限制查询的记录数,如 SELECT \* FROM tl LJMIT5 ,也可以实现 Topk 查询,比如下面的查询语句可以查询销售记录最多的 5个销售代表
SET mapred.reduce.tasks = 1
SELECT * FROM test SORT BY amount DESC LIMIT 5
* REGEX Column Specification : select 语句可以使用正则表达式做列选择,下面的语句查询除了 ds 和 hr 之外的所有列
SELECT (ds|hr)?+.+
FROM test
##### 2. join 表
join_table:
table_reference (INNER] JOIN table_factor (join_condition]
| table_reference {LEFTIRIGHTjFULL} (OUTER] JOIN table_reference join_ condition
| table_reference LEFT SEM JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference (join_condition] (as of Hive 0.10)
table reference:
table_factor
| join_table
table_factor:
tbl_name [alias]
| table_subquery alias
| (table_references)
join_condition:
on expression
* Hive中只支持等值连接,外连接和左半连接(left semi join),(从2.2.0版本后支持非等值连接);
* 可以连接2个以上的表,如:
select a.val, b.val,c.val
from a
join b
on (a.key=b.key1)
join c
on(c.key = b.key2);
* 如果连接中多个表的join key是同一个,则连接会被转化为单个Map/Reduce任务
select a.val,b.val,c.val
from a
join b
on (a.key=b.key1)
join c
on(c.key=b.key1);
* join时大表放在最后: Reduce会缓存join序列中除最后一个表之外的所有表的记录,再通过最后一个表将结果序列化到文件系统
* 如果想限制join的输出, 应该在where子句中写过滤条件,或是在join子句中写。
* 但是有表分区的情况,比如下面的第一个 SQL 语句所示,如果d表中找不到对应c表的记录, d表的所有列都会列出 NULL ,包括 ds列。 也就是说, join 会过滤d表中不能找到匹配c表 join key 的所有记录。 这样, LEFT OUTER 就使得查询结果与 WHERE 子句无关,解决办法是在join 时指定分区(如下面的第二个 SQL 语句所示)
–第一个 SQL 语句
SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)
WHERE a.ds=‘2010-07-07’ AND b.ds=‘2010-07-07’
– 第二个 SQL 语句
SELECT c.val, d.val FROM c LEFT OUTER JOIN d
ON (c.key=d.key AND d.ds=’ 2009-07-07 ’ AND c.ds=‘2009-07-07’)
* left semi join是in/exists子查询的一种更高效的实现,join子句中右边的表只能在on子句中设置过滤条件,在where子句、select子句中或其他方式过滤都不行
SELECT a.key, a.value
FROM a
WHERE a.key in
(SELECT b.key FROM B);
–可以被重写为:
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)
### 三、Hive SQL 执行原理图解
我们都知道,一个好的的 Hive SQL 和写得不好的 Hive SQL ,对底层计算和资源的使用可能相差百倍甚至千倍、万倍。
![img](https://img-blog.csdnimg.cn/img_convert/3a79623b0831ae1f9011abbf7698c033.png)
![img](https://img-blog.csdnimg.cn/img_convert/9a072296a5ea0a580cdde0f69be24ac8.png)
![img](https://img-blog.csdnimg.cn/img_convert/8cf72b639120cd36dcc1c977555c61a5.png)
**既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!**
**由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
mg-RlPXcuWW-1715693886561)]
[外链图片转存中...(img-kiOLfqpP-1715693886562)]
[外链图片转存中...(img-oyY73uYC-1715693886562)]
**既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!**
**由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/topics/618545628)**