Hive 概念
Hive是一个SQL解析引擎,也就是说Hive本身是没有存储数据以及对数据进行计算处理的能力的,它是一种MapReduce和Hadoop的封装,由Hadoop提供数据存储能力,由MapReduce提供计算能力。Hive的出现,让很多只懂SQL的工程师也可以操作大量需要具备Java能力才可以进行的MapReduce作业。
因此MapReduce和Hadoop的特点也就一并带给了Hive
- RDBMS的SQL提供增删改查(insert, delete, update, select)的能力,而HQL对改(update)支持得非常差,虽然从Hive 2.0提供了一定的改能力,但是依然不推荐进行改操作
- MapReduce的计算需要将中间结果存盘,然后进行shuffle阶段,所以Hive也继承了这种慢速批处理计算的特性
Hive数据模型:
- 数据表
- Table 内部表,表结构和数据都由Hive管理,当Hive进行删除(drop)操作的时候,表结构和数据都会被删除掉,如果要恢复已被删除的内部表,就必须重新建表,然后导入数据。
- External Table 外部表,表结构由Hive管理,而数据则不由Hive进行管理,当Hive进行删除(drop)操作的时候,只有表结构被删除,而数据则依然保存在本地磁盘或者HDFS上,如果要恢复已被删除的外部表,只需要重新建表即可,查询的时候会自动定位到数据所在的位置
- 分区表 Partition: 依然是数据表,只是一种不同于普通的建表方式
- 通常在where语句中经常出现的字段比较适合做分区,分区字段的值不会太多,工作中常用的是时间、物品类型之类的字段来做分区。
- Hive表的数据在HDFS上的体现是所有的数据都放在一个以表名命名的文件夹中,和普通的RDBMS不一样,Hive其实没有严格的索引概念,也只提供有限的索引功能,所以大部分情况下查询的时候会需要遍历文件夹中的所有文件。如果以日期字段做了分区,那么想要获取分析某天的数据,就只要对相应日期对应的分区文件夹的数据进行遍历(where条件),大大提高了查询的效率。
- 分桶表 Bucket: 也是数据表,只是一种不同于普通的建表方式
- Bucket的用途经常是用于抽样
- Hive会针对某一列进行桶的组织,通常会对列做哈希
- 进行分桶之后,做join的时候会自动激活map端的map-site join,可以优化查询,方便采样
Hive建表
数据表
在实际操作过程中,经常会出现得到的数据源csv文件内容中是带有表头信息的,如果建表导入数据的话,就会导致有一行数据实际是表头信息。有些同学在操作的时候会选择将源数据(csv文件)的表头数据删除,然后再进行建表操作。这种操作比较危险,一般而言为了保证数据的完整、安全和有效,是不建议对源数据直接进行修改的。那么可以在建表时加入表属性"skip.header.line.count"="1"
来保证在导入数据时可以忽略文件第一行数据。
- hive> create table orders
- hive> (
- hive> order_id string,
- hive> user_id string,
- hive> eval_set string,
- hive> order_number string,
- hive> order_dow string,
- hive> order_hour_of_day string,
- hive> days_since_prior_order string)
- hive> row format delimited fields terminated by ','
- hive> tblproperties("skip.header.line.count"="1");
-
- hive> load data local inpath '/usr/bigdata/download/orders.csv'
- hive> into table orders;
字段和字段类型之间不要用tab来分割,HQL和SQL一样,喜欢空格来对命令进行分割
这里创建的是一个普通表,如果查看表的属性,可以看到Hive默认建新表保存为Text格式
- hive> show create table orders;
- OK
- ...
- STORED AS INPUTFORMAT
- 'org.apache.hadoop.mapred.TextInputFormat'
- OUTPUTFORMAT
- 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
- ...
Text格式的表创建简单,但是当和其他组件一起使用的时候,可能会再次出现表头的问题,比如Spark SQL,在Spark SQL中对Text格式的Hive表进行查询,会再次出现表头的问题。这种情况可以采取对表进行转储来解决,也就是建一个新表,指定存储格式为ORC或者Parquet,将Text格式的表的数据插入新表
- hive> create table orders_orc
- hive> (
- hive> order_id string,
- hive> user_id string,
- hive> eval_set string,
- hive> order_number string,
- hive> order_dow string,
- hive> order_hour_of_day string,
- hive> days_since_prior_order string)
- hive> row format delimited fields terminated by ','
- hive> stored as orc;
-
- hive> insert into table order_orc
- hive> select * from orders;
Hive从一个表将数据载入另一张表需要用插入的方式
这时可以看到新表的存储方式为ORC格式
- hive> show create table orders_orc;
- OK
- ...
- STORED AS INPUTFORMAT
- 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
- OUTPUTFORMAT
- 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
- ...
这里所建都是内部表,建外部表也很简单,create external table就可以了
- hive> create external table orders_ext
- hive> (
- hive> order_id string,
- hive> user_id string,
- hive> eval_set string,
- hive> order_number string,
- hive> order_dow string,
- hive> order_hour_of_day string,
- hive> days_since_prior_order string)
- hive> row format delimited fields terminated by ','
- hive> tblproperties("skip.header.line.count"="1");
分区表
建分区表的时候,和建内\外部表的命令差不多,只是把需要作为分区的字段单独拿出来进行声明。
- hive> create table orders_part
- hive> (
- hive> order_id string,
- hive> user_id string,
- hive> eval_set string,
- hive> order_number string,
- hive> order_hour_of_day string,
- hive> days_since_prior_order string)
- hive> partitioned by (order_dow string)
- hive> row format delimited fields terminated by ','
- hive> tblproperties("skip.header.line.count"="1");
分区表导入数据有两种方式
- load命令导入原始数据
- 导入数据的时候必须要声明分区字段以及字段值,所以需要进行多次导入操作
- insert命令从其他表插入数据
- 静态插入:和load命令一样,必须要声明分区字段以及字段值,童谣需要进行多次插入操作
- 动态插入:可以让Hive动态的识别分区字段值,进行分区操作,需要打开动态分区功能
set hive.exec.dynamic.partition=true;
和set hive.exec.dynamic.partition.mode=nonstrict;
从其他表插入数据到分区表,在select的时候分区字段要放在最后- hive> set hive.exec.dynamic.partition=true;
- hive> set hive.exec.dynamic.partition.mode=nonstrict;
- hive> insert into table orders_part partition (order_dow)
- hive> select order_id,user_id,eval_set,order_number,
- hive> order_hour_of_day,days_since_prior_order,order_dow from orders;
分桶表
分桶表是无法直接使用load的方式载入数据的,所以使用分桶表,首先需要有辅助表的存在
- hive> create table bucket_aux(num int);
- hive> load data inpath '/usr/bigdata/download/bucket_test.txt' into table bucket_aux;
然后创建分桶表,并载入数据。创建分桶表的时候需要注意:
- 分桶参数要设置
set hive.enforce.bucketing=true
- 建表的时候要说明分多少个桶
- hive> set hive.enforce.bucketing=true;
- hive> create table bucket_test(num int) clustered by (num) into 32 buckets;
- hive> insert overwrite table bucket_test select num from bucket_aux;
然后可以在HDFS上看到分桶情况
- [bigdata@master download]$ hadoop fs -ls /usr/hive/warehouse/badou.db/bucket_test
- Found 32 items
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000000_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000001_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000002_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000003_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000004_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000005_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000006_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000007_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000008_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000009_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000010_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000011_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000012_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000013_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000014_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000015_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000016_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000017_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000018_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000019_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000020_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000021_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000022_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000023_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000024_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000025_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000026_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000027_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000028_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000029_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000030_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000031_0
而如果分桶为30个,情况则是这个样子的
- hive> create table bucket_test_30(num int) clustered by (num) into 30 buckets;
- hive> insert overwrite table bucket_test_30 select num from bucket_aux;
- [bigdata@master download]$ hadoop fs -ls /usr/hive/warehouse/badou.db/bucket_test_30
- Found 30 items
- -rwx-wx-wx 2 bigdata supergroup 5 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000000_0
- -rwx-wx-wx 2 bigdata supergroup 5 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000001_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000002_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000003_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000004_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000005_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000006_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000007_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000008_0
- -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000009_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000010_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000011_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000012_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000013_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000014_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000015_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000016_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000017_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000018_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000019_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000020_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000021_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000022_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000023_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000024_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000025_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000026_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000027_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000028_0
- -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000029_0
查看30个分桶的第0个和第1个分桶,可以看到这样的结果
- [bigdata@master download]$ hadoop fs -cat /usr/hive/warehouse/badou.db/bucket_test_30/000000_0
- 0
- 30
- [bigdata@master download]$ hadoop fs -cat /usr/hive/warehouse/badou.db/bucket_test_30/000001_0
- 31
- 1
也就是说,进行分桶的时候,是以数据所在行号对分桶个数进行取模运算来进行存放的。即桶号=(行号)%(分桶个数)
对分桶表进行查询,一般需要使用采样语句tablesample
- hive> select * from bucket_test tablesample (bucket 1 out of 32 on num);
- OK
- 0
这个查询语句从32个桶的第一个桶里取数据,所以取到的是0。
- hive> select * from bucket_test tablesample (bucket 1 out of 9 on num);
- OK
- 0
- 9
- 18
- 27
这个查询语句首先将32个桶分成4份,也就是ceiling(32/9)
- 1-9号桶为第一份,对应值为0-8
- 10-18号桶为第二份,对应值为9-17
- 19-27号桶为第三份,对应值为18-26
- 28-32号桶为第四份,对应值为27-31
然后在每份里取第一个值
- hive> select * from bucket_test tablesample (bucket 7 out of 9 on num);
- OK
- 6
- 15
- 24
这个查询将32个同样分成4份,但是由于最后一份只有5个桶,查询不到第7个,所以只返回3个值
- hive> select * from bucket_test tablesample (bucket 3 out of 35 on num);
- OK
- 2
这个查询要将32个桶分成1份ceiling(32/35)
,然后取第3个桶