Hive(五)索引、视图、定时器

一：索引

索引

           适用于大多数索引情况：

                      create index table01_index on table table01 (column2) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild; 

           适用于排序后值比较小情况：

                      create index table01_index on table table01 (column2) as 'bitmap' with deferred rebuild; 

           刷新索引：alter index table02_index on table2 rebuild;

           显示索引：show formatted index on table02;

           删除索引：drop index table02_index on table02;

           分区索引：alter index table10_index on table10 partition (columnx='valueq', columny='valuer') rebuild;;

二：视图

视图

           创建视图：create view onion_referrers comment 'referrers to the onion website' as select distinct referrer_url from page_view where page_url='http://www.theonion.com';

           删除视图：drop view if exists onion_referrers; 

           修改视图：alter table view as select *from;   alter table view set tblproperties ('external' = 'true');  

           显示视图：show views in test1 "test_*";        

物化视图（相当于实体表）

           创建物化视图：create materialized view onion_referrers comment 'referrers to the onion website' as select distinct referrer_url from page_view where page_url='http://www.theonion.com';

           删除物化视图：drop materialized view onion_referrers;

           刷新物化视图：alter materialized view onion_referrers enable|disable rewrite;

           显示物化视图：show materialized views [in/from database_name] [like 'pattern_with_wildcards’];

三：定时器

定时器

创建定时器：create scheduled query sc1 cron '0 */10 * * * ? *' as insert into t values (1);

                     create scheduled query mv_rebuild cron '0 */10 * * * ? *' defined as alter materialized view mv1 rebuild;

                     create scheduled query ingest every 10 minutes defined as alter materialized view mv1 rebuild;

取消定时器：alter scheduled query t_analyze disable;

查询定时器：select * from information_schema.scheduled_queries s where schedule_name='sc1';

查询定时器结果：select * from information_schema.scheduled_executions s where schedule_name='sc1' order by scheduled_execution_id desc limit 1;