大数据技术架构(组件)20——Hive：External Table or Managed Table & Bucket & Scheduled Queries

1.6、External Table or Managed Table

表类型查看：

DESCRIBE EXTENDED table_name

具体见https://cwiki.apache.org/confluence/display/Hive/Managed+vs.+External+Tables

区别：

1、内部表又称为托管表，Hive管理其数据、元数据，统计信息；外部表的数据由HDFS管理，并不是只能通过Hive方式命令来操作数据。

2、基于第1条，当内部表被删除时，其数据也会删除；但外部表数据仍然存在。（Hive4.0.0通过配置external.table.purge=true可以在删除表的时候同样删除数据）

3、内部表的数据存储在hive.metastore.warehouse.dir参数配置下的路径。而外部表则对于存储位置没有限制。

4、对于CTAS语法不能应用到外部表上，或分桶表上。

CREATE TABLE page_view(viewTime INT, userid BIGINT,
    page_ur] STRING, referrer_url STRING,
    ip STRING COMMENT'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS SEOUENCEFILE:

5、对于archive/unarchive/truncate/merge/concatenate只能作用于内部表

6、对于事务/ACID只能作用域内部表

7、查询的结果缓存只能作用于内部表

8、当外部表的结构或者分区发生变化，需要使用msck repair table刷新元数据

1.7、Bucket

意义：

1、如果单个分区的数据量也很大，且不能更加细粒度划分数据的时候，那么这个时候就需要使用分桶再次对数据进行划分管理。

2、对于数据抽样和join操作效率(如mapjoin)会更好。

规则：

1、根据指定字段结合分桶策略进行划分。采用hash方式计算落入某个桶。

创建:

CREATE TABLE user info bucketed
(user_id BIGINT, firstname STRING, lastname STRING)
COMMENT 'A bucketed copy of user_info'
PARTITIONED BY(ds STRING)
CLUSTERED BY(user id) INTO 256 BUCKETS;

基于user_id字段进行分桶，总桶数为256个。

插入:

set hive.enforce.bucketing = true; 
-- hive2.0+之后不需要加这个参数。早期这个参数可以自动决定reduce和bucket数量;
-- 否则需要手动设置mapre.reduce.task=256来保障reduce个数和bucket个数一致。
INSERT OVERWRITE TABLE user_info_bucketed
PARTITION (ds='2009-02-25')
SELECT
    userid, firstname, lastname
FROM user_id
WHERE ds='2009-02-25':

查询使用：

-按照比例抽样
SELECT * FROM source TABLESAMPLE(0.1 PERCENT) S;
--按照大小抽样
SELECT * FROM source TABLESAMPLE(100M) s;
--按照行数抽样
SELECT * FROM source TABLESAMPLE(10 ROWS);
--按照指定桶数抽样
SELECT * FROM source TABLESAMPLE(BUCKET 3 OUT OF 32 0N rand()) s;

1.8、Scheduled Queries（Hive4.0）

底层：

HiverServer底层会定期轮询Metastore拿到定时查询计划进行执行。

语法：

--创建定时调度
CREATE SCHEDULED QUERY <scheduled_query_name>
    <scheduleSpecification> --具体调度策略
    [<executedAsSpec>] --执行人
    [<enableSpecification>] --是否开启
    <definedAsSpec> --具体的查询计划定义

--修改定时调度
ALTER SCHEDULED QUERY <scheduled_query_name>
    (<scheduleSpec>|<executedAsSpec>|<enablespecification>|<definedAsSpec>|<executeSpec>);

--删除定时调度
DROP SCHEDULED OUERY <scheduled query name>;

对于scheduleSpecification语法:

1、基于CRON

例如：CRON '0 /10 ? *' 每10分钟执行一次

2、基于EVERY

EVERY [integer] (SECOND|MINUTE|HOUR) [(OFFSET BY|AT) timeOrDate]

例如：

EVERY 2 MINUTES

EVERY HOUR AT '0:07:30'

EVERY DAY AT '11:35:30'

对于ExecutedAs syntax语法使用：

EXECUTED AS <user_name>

对于enableSpecification语法使用：

(ENABLE[D] | DISABLE[D])

对于Defined AS 的语法使用:

[DEFINED] AS

对于executeSpec的语法使用:

EXECUTE:将下次执行时间调整为现在。用于调试

例子:

--通过hive.scheduled.queries.create.as.enabled参数来启用或禁用
--每1@min插入一行
create scheduled query sc1 cron '0 */10 * * * ? *' as insert into t values (1);
--启用执行计划
alter scheduled query sc1 enabled;
--等待执行或者立即执行。
alter scheduled query sc1 execute;

元数据存储：

对于执行信息的保留策略通过metastore.scheduled.queries.execution.max.age参数配置。

1、information_schema.scheduled_queries：调度计划定义信息

2、information_schema.scheduled_executions：调度计划执行信息