当前位置:   article > 正文

Hive基础 (一)

skip.header.line.count 分区表

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数据模型:

  1. 数据表
    • Table 内部表,表结构和数据都由Hive管理,当Hive进行删除(drop)操作的时候,表结构和数据都会被删除掉,如果要恢复已被删除的内部表,就必须重新建表,然后导入数据。
    • External Table 外部表,表结构由Hive管理,而数据则不由Hive进行管理,当Hive进行删除(drop)操作的时候,只有表结构被删除,而数据则依然保存在本地磁盘或者HDFS上,如果要恢复已被删除的外部表,只需要重新建表即可,查询的时候会自动定位到数据所在的位置
  2. 分区表 Partition: 依然是数据表,只是一种不同于普通的建表方式
    • 通常在where语句中经常出现的字段比较适合做分区,分区字段的值不会太多,工作中常用的是时间、物品类型之类的字段来做分区。
    • Hive表的数据在HDFS上的体现是所有的数据都放在一个以表名命名的文件夹中,和普通的RDBMS不一样,Hive其实没有严格的索引概念,也只提供有限的索引功能,所以大部分情况下查询的时候会需要遍历文件夹中的所有文件。如果以日期字段做了分区,那么想要获取分析某天的数据,就只要对相应日期对应的分区文件夹的数据进行遍历(where条件),大大提高了查询的效率。
  3. 分桶表 Bucket: 也是数据表,只是一种不同于普通的建表方式
    • Bucket的用途经常是用于抽样
    • Hive会针对某一列进行桶的组织,通常会对列做哈希
    • 进行分桶之后,做join的时候会自动激活map端的map-site join,可以优化查询,方便采样

Hive建表

数据表

在实际操作过程中,经常会出现得到的数据源csv文件内容中是带有表头信息的,如果建表导入数据的话,就会导致有一行数据实际是表头信息。有些同学在操作的时候会选择将源数据(csv文件)的表头数据删除,然后再进行建表操作。这种操作比较危险,一般而言为了保证数据的完整、安全和有效,是不建议对源数据直接进行修改的。那么可以在建表时加入表属性"skip.header.line.count"="1"来保证在导入数据时可以忽略文件第一行数据。

  1. hive> create table orders
  2. hive> (
  3. hive> order_id string,
  4. hive> user_id string,
  5. hive> eval_set string,
  6. hive> order_number string,
  7. hive> order_dow string,
  8. hive> order_hour_of_day string,
  9. hive> days_since_prior_order string)
  10. hive> row format delimited fields terminated by ','
  11. hive> tblproperties("skip.header.line.count"="1");
  12. hive> load data local inpath '/usr/bigdata/download/orders.csv'
  13. hive> into table orders;

字段和字段类型之间不要用tab来分割,HQL和SQL一样,喜欢空格来对命令进行分割

这里创建的是一个普通表,如果查看表的属性,可以看到Hive默认建新表保存为Text格式

  1. hive> show create table orders;
  2. OK
  3. ...
  4. STORED AS INPUTFORMAT
  5. 'org.apache.hadoop.mapred.TextInputFormat'
  6. OUTPUTFORMAT
  7. 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  8. ...

Text格式的表创建简单,但是当和其他组件一起使用的时候,可能会再次出现表头的问题,比如Spark SQL,在Spark SQL中对Text格式的Hive表进行查询,会再次出现表头的问题。这种情况可以采取对表进行转储来解决,也就是建一个新表,指定存储格式为ORC或者Parquet,将Text格式的表的数据插入新表

  1. hive> create table orders_orc
  2. hive> (
  3. hive> order_id string,
  4. hive> user_id string,
  5. hive> eval_set string,
  6. hive> order_number string,
  7. hive> order_dow string,
  8. hive> order_hour_of_day string,
  9. hive> days_since_prior_order string)
  10. hive> row format delimited fields terminated by ','
  11. hive> stored as orc;
  12. hive> insert into table order_orc
  13. hive> select * from orders;

Hive从一个表将数据载入另一张表需要用插入的方式

这时可以看到新表的存储方式为ORC格式

  1. hive> show create table orders_orc;
  2. OK
  3. ...
  4. STORED AS INPUTFORMAT
  5. 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
  6. OUTPUTFORMAT
  7. 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
  8. ...

这里所建都是内部表,建外部表也很简单,create external table就可以了

  1. hive> create external table orders_ext
  2. hive> (
  3. hive> order_id string,
  4. hive> user_id string,
  5. hive> eval_set string,
  6. hive> order_number string,
  7. hive> order_dow string,
  8. hive> order_hour_of_day string,
  9. hive> days_since_prior_order string)
  10. hive> row format delimited fields terminated by ','
  11. hive> tblproperties("skip.header.line.count"="1");

分区表

建分区表的时候,和建内\外部表的命令差不多,只是把需要作为分区的字段单独拿出来进行声明。

  1. hive> create table orders_part
  2. hive> (
  3. hive> order_id string,
  4. hive> user_id string,
  5. hive> eval_set string,
  6. hive> order_number string,
  7. hive> order_hour_of_day string,
  8. hive> days_since_prior_order string)
  9. hive> partitioned by (order_dow string)
  10. hive> row format delimited fields terminated by ','
  11. hive> tblproperties("skip.header.line.count"="1");

分区表导入数据有两种方式

  1. load命令导入原始数据
    • 导入数据的时候必须要声明分区字段以及字段值,所以需要进行多次导入操作
  2. insert命令从其他表插入数据
    • 静态插入:和load命令一样,必须要声明分区字段以及字段值,童谣需要进行多次插入操作
    • 动态插入:可以让Hive动态的识别分区字段值,进行分区操作,需要打开动态分区功能set hive.exec.dynamic.partition=true;set hive.exec.dynamic.partition.mode=nonstrict;
    1. hive> set hive.exec.dynamic.partition=true;
    2. hive> set hive.exec.dynamic.partition.mode=nonstrict;
    3. hive> insert into table orders_part partition (order_dow)
    4. hive> select order_id,user_id,eval_set,order_number,
    5. hive> order_hour_of_day,days_since_prior_order,order_dow from orders;
    从其他表插入数据到分区表,在select的时候分区字段要放在最后

分桶表

分桶表是无法直接使用load的方式载入数据的,所以使用分桶表,首先需要有辅助表的存在

  1. hive> create table bucket_aux(num int);
  2. hive> load data inpath '/usr/bigdata/download/bucket_test.txt' into table bucket_aux;

然后创建分桶表,并载入数据。创建分桶表的时候需要注意:

  • 分桶参数要设置set hive.enforce.bucketing=true
  • 建表的时候要说明分多少个桶
  1. hive> set hive.enforce.bucketing=true;
  2. hive> create table bucket_test(num int) clustered by (num) into 32 buckets;
  3. hive> insert overwrite table bucket_test select num from bucket_aux;

然后可以在HDFS上看到分桶情况

  1. [bigdata@master download]$ hadoop fs -ls /usr/hive/warehouse/badou.db/bucket_test
  2. Found 32 items
  3. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000000_0
  4. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000001_0
  5. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000002_0
  6. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:45 /usr/hive/warehouse/badou.db/bucket_test/000003_0
  7. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000004_0
  8. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000005_0
  9. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000006_0
  10. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000007_0
  11. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000008_0
  12. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000009_0
  13. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000010_0
  14. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000011_0
  15. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000012_0
  16. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000013_0
  17. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000014_0
  18. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000015_0
  19. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000016_0
  20. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000017_0
  21. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000018_0
  22. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000019_0
  23. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000020_0
  24. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000021_0
  25. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000022_0
  26. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000023_0
  27. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000024_0
  28. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000025_0
  29. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000026_0
  30. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000027_0
  31. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000028_0
  32. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000029_0
  33. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000030_0
  34. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:46 /usr/hive/warehouse/badou.db/bucket_test/000031_0

而如果分桶为30个,情况则是这个样子的

  1. hive> create table bucket_test_30(num int) clustered by (num) into 30 buckets;
  2. hive> insert overwrite table bucket_test_30 select num from bucket_aux;
  3. [bigdata@master download]$ hadoop fs -ls /usr/hive/warehouse/badou.db/bucket_test_30
  4. Found 30 items
  5. -rwx-wx-wx 2 bigdata supergroup 5 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000000_0
  6. -rwx-wx-wx 2 bigdata supergroup 5 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000001_0
  7. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000002_0
  8. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000003_0
  9. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000004_0
  10. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000005_0
  11. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000006_0
  12. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000007_0
  13. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000008_0
  14. -rwx-wx-wx 2 bigdata supergroup 2 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000009_0
  15. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000010_0
  16. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000011_0
  17. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:50 /usr/hive/warehouse/badou.db/bucket_test_30/000012_0
  18. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000013_0
  19. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000014_0
  20. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000015_0
  21. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000016_0
  22. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000017_0
  23. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000018_0
  24. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000019_0
  25. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000020_0
  26. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000021_0
  27. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000022_0
  28. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000023_0
  29. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000024_0
  30. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000025_0
  31. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000026_0
  32. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000027_0
  33. -rwx-wx-wx 2 bigdata supergroup 3 2019-06-22 22:51 /usr/hive/warehouse/badou.db/bucket_test_30/000028_0
  34. -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个分桶,可以看到这样的结果

  1. [bigdata@master download]$ hadoop fs -cat /usr/hive/warehouse/badou.db/bucket_test_30/000000_0
  2. 0
  3. 30
  4. [bigdata@master download]$ hadoop fs -cat /usr/hive/warehouse/badou.db/bucket_test_30/000001_0
  5. 31
  6. 1

也就是说,进行分桶的时候,是以数据所在行号对分桶个数进行取模运算来进行存放的。即桶号=(行号)%(分桶个数)
对分桶表进行查询,一般需要使用采样语句tablesample

  1. hive> select * from bucket_test tablesample (bucket 1 out of 32 on num);
  2. OK
  3. 0

这个查询语句从32个桶的第一个桶里取数据,所以取到的是0。

  1. hive> select * from bucket_test tablesample (bucket 1 out of 9 on num);
  2. OK
  3. 0
  4. 9
  5. 18
  6. 27

这个查询语句首先将32个桶分成4份,也就是ceiling(32/9)

  • 1-9号桶为第一份,对应值为0-8
  • 10-18号桶为第二份,对应值为9-17
  • 19-27号桶为第三份,对应值为18-26
  • 28-32号桶为第四份,对应值为27-31
    然后在每份里取第一个值
  1. hive> select * from bucket_test tablesample (bucket 7 out of 9 on num);
  2. OK
  3. 6
  4. 15
  5. 24

这个查询将32个同样分成4份,但是由于最后一份只有5个桶,查询不到第7个,所以只返回3个值

  1. hive> select * from bucket_test tablesample (bucket 3 out of 35 on num);
  2. OK
  3. 2

这个查询要将32个桶分成1份ceiling(32/35),然后取第3个桶

转载于:https://www.cnblogs.com/LuYin/p/11070869.html

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/花生_TL007/article/detail/562600
推荐阅读
相关标签
  

闽ICP备14008679号