Hive笔记 | 安静时刻

Hive相关笔记

CTE & Temporary tables & View & Materialized view

CTE

1 2	with table_name as (...) selece * from table_name

Hive中的CTE全称叫做Common Table Expression 公共表达式，意思就是大家公共的，都能用的结果，一般用在SQL中可以简化我们的逻辑，提高可读性，CTE主要有以下几个优点。

复用公共代码块，减少表的读取次数，降低IO 提高性能。达到一次查询（读），多次使用，目的是减少读的次数（需要配置参数来物化CTE结果）
提高代码的可读性：使代码更简洁，便于维护。如将子查询抽出来以后，放到with语句，可方便定位，维护代码，代码的可读性增强。
做递归查询，进行迭代计算。

注意：CTE默认在每次调用的时候都会重新计算一遍，设置hive.optimize.cte.materialize.threshold指标来强制物化CTE结果，当CTE被使用的次数大于该指标值的时候将被存储到磁盘中。

# 下面SQL可以检测是否物化到磁盘，设置hive.optimize.cte.materialize.threshold = 1 
# 如果两个随机数是相等的，证明被物化了，如果是不等的，证明计算了两次
with temp as (select rand())

select * from temp 
union all 
select * from temp

View

# 视图是保存了逻辑结构，没有真正的存储数据，所以如果逻辑复杂 需要重复计算多次 不建议使用视图
CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT table_comment]
AS SELECT ...

DROP VIEW view_name

视图作用：

简化SQL，使逻辑更加清晰
不对外公开所有的字段，例如对不同的部门建立不同的视图表
降低数据冗余

注意：

视图定义在创建时冻结，因此，如果视图定义为 select * from t，其中 t 是具有两列 a 和 b 的表，则稍后请求选择*从视图中看，即使稍后将新列 c 添加到表中，也应仅返回 a 和 b 列
视图是只读的，仅能查询，不能进行数据插入和修改
hive优先解析视图，比如，如果使用视图的查询语句和视图均包含limit子句，那么用户最终获取的数据条数将首先考虑视图中限制的输出记录数

Partitioned View

Hive还可以建立分区视图，有人可能疑惑分区视图这个业务场景在哪里？

需要对每天的分区表中的数据进行一些预处理比如去重 之后再按照天数据供查询，我们就需要分区视图，总不能针对每一个分区都建立一张视图。

# 先创建表
CREATE TABLE if not exists kantlin (
id int,
name string,
age int
)
PARTITIONED BY (date_id string)
row format delimited fields terminated by ','
stored as textfile;
#插入两条相同的数据
 insert into table kantlin partition(date_id='20210618')   values (1,'kantlin',3);
 insert into table kantlin partition(date_id='20210618')   values (1,'kantlin',3);

#创建分区视图
#注意 PARTITIONED ON后面跟的是分区字段，而且这个字段必须是select和group by的最后一个元素！
create view kantlin PARTITIONED ON(date_id) AS SELECT id,name,age,time,date_id  from kantlin GROUP BY id,name,age,time,date_id ;

Materialized views

Hive3.0.0引入了对物化视图的支持，需要注意的是引入物化视图的目的是为了优化数据访问的效率，物化视图相比视图来说修改要更加贴近底层，他会重新我们的查询SQL，自动优化我们的查询流程，可能我们两张表join的情况，存在满足我们结果的物化视图，那么他会直接从物化视图表拿数据。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)]
  [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
  [
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS
<query>;

在实例化视图创建语句中未指定的 SerDe 和存储格式的默认值(它们是可选的)分别使用配置属性hive.materializedview.serde和hive.materializedview.fileformat指定。

实例化视图可以使用自定义存储处理程序存储在外部系统中，例如Druid。例如，以下语句创建存储在 Druid 中的实例化视图：

CREATE MATERIALIZED VIEW druid_wiki_mv
STORED AS 'org.apache.hadoop.hive.druid.DruidStorageHandler'
AS
SELECT __time, page, user, c_added, c_removed
FROM src;

当实例化视图使用的源表中的数据发生更改时，例如，插入新数据或修改现有数据时，我们将需要刷新实例化视图的内容，以使其与这些更改保持最新。当前，物化视图的重建操作需要由用户触发。特别是，用户应执行以下语句：

1	ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

Hive 支持增量视图维护，即仅刷新受原始源表中的更改影响的数据。增量视图维护将减少重建步骤的执行时间。此外，它将为物化视图中的现有数据保留 LLAP 缓存。

默认情况下，Hive 将尝试以增量方式重建实例化视图，如果不可能的话，将退回到完全重建。当前实现仅在源表上执行INSERT操作时支持增量重建，而UPDATE和DELETE操作将强制对物化视图进行完全重建。

SQL

How to write SQL?

本章节记录写在开发过程中遇到一些SQL书写方法或者技巧。