hive的分捅表

参考文章：

hive分桶管理

https://blog.csdn.net/freefish_yzx/article/details/77150714

 

hive的分区和分桶

https://blog.csdn.net/wl1411956542/article/details/52931499

 

Hive中有数据分区的方案，也有数据分桶的方案，今天我们就来探讨下数据分桶 以及数据分桶使用的场景。

 

该篇文章主要分为一下几个部分：

 

1.数据分桶的适用场景

2.数据分桶的原理

3.数据分桶的作用

4.如何创建数据分桶表

5.如何将数据插入分桶表

6.针对于分桶表的数据抽样

7.数据分桶的一些缺陷

 

数据分桶的适用场景：

    分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，

尤其是需要确定合适大小的分区划分方式，（不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况）

试试分桶是将数据集分解为更容易管理的若干部分的另一种技术。

 

 

数据分桶的原理:

        

    跟MR中的HashPartitioner的原理一模一样

        MR中：按照key的hash值去模除以reductTask的个数

        Hive中：按照分桶字段的hash值去模除以分桶的个数

    Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

 

 

数据分桶的作用:

好处：
1、方便抽样

2、提高join查询效率

 

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

 

 

创建数据分桶表:

    创建数据分桶表与普通表的表区别并不太大，如下为一个创建数据分桶表的示例：

1. use clickcube;

2.  

3. CREATE EXTERNAL TABLE `clickcube_mid`(

4. `logtype` bigint,

5. `date` string,

6. `hour` bigint,

7. `projectid` bigint,

8. `campaignid` bigint,

9. `templateid` bigint,

10. `mediaid` bigint,

11. `slotid` bigint,

12. `channeltype` bigint,

13. `regioncode` string,

14. `campclick` bigint,

15. `campimp` bigint,

16. `mediaclick` bigint,

17. `mediaimp` bigint,

18. `templateimp` bigint,

19. `templatecampimp` bigint,

20. `mediaclickcost` double,

21. `campclickcost` double)

22. PARTITIONED BY (

23. `day` string)

24. CLUSTERED BY (

25. `campaignid`, `mediaid` ) INTO 100 BUCKETS

26. ROW FORMAT SERDE

27. 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'

28. STORED AS INPUTFORMAT

29. 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'

30. OUTPUTFORMAT

31. 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'

32. TBLPROPERTIES (

33. 'last_modified_by'='cloudera-scm',

34. 'last_modified_time'='1530676367',

35. 'transient_lastDdlTime'='1530676367')

 

其实主要注意的地方就如下的点:

  CLUSTERED BY (

   `campaignid`,  `mediaid` ) INTO 100 BUCKETS    

 

 

 

如何将数据插入分桶表

将数据导入分桶表主要通过以下步骤

第一步：

    从hdfs或本地磁盘中load数据，导入中间表

 

第二步：

    通过从中间表查询的方式的完成数据导入

 

      分桶的实质就是对 分桶的字段做了hash 然后存放到对应文件中，所以说如果原有数据没有按key hash ,

需要在插入分桶的时候hash, 也就是说向分桶表中插入数据的时候必然要执行一次MAPREDUCE,

这也就是分桶表的数据基本只能通过从结果集查询插入的方式进行导入

 

 

这里我们主要讲解第二步：

主要的过程我们写为一个SQL

1. use clickcube;

2.  

3. set hive.enforce.bucketing = true;

4.  

5. INSERT OVERWRITE TABLE clickcube_mid_bucket

6. PARTITION( day = '2018-07-03' )

7. SELECT

8. clickcube_mid.logtype,

9. clickcube_mid.`date`,

10. clickcube_mid.`hour`,

11. clickcube_mid.projectid,

12. clickcube_mid.campaignid,

13. clickcube_mid.templateid,

14. clickcube_mid.mediaid,

15. clickcube_mid.slotid,

16. clickcube_mid.channeltype,

17. clickcube_mid.regioncode,

18. clickcube_mid.campclick,

19. clickcube_mid.campimp,

20. clickcube_mid.mediaclick,

21. clickcube_mid.mediaimp,

22. clickcube_mid.templateimp,

23. clickcube_mid.templatecampimp,

24. clickcube_mid.mediaclickcost,

25. clickcube_mid.campclickcost

26. FROM clickcube_mid

27. WHERE day = '2018-07-03'

 

这里我们需要注意几点

我们需要确保reduce 的数量与表中的bucket 数量一致，为此有两种做法

 

1.让hive强制分桶，自动按照分桶表的bucket 进行分桶。(推荐)

set  hive.enforce.bucketing = true;

 

2.手动指定reduce数量

set mapreduce.job.reduces = num;

/

set mapreduce.reduce.tasks = num;

并在 SELECT 后增加CLUSTER BY 语句

 

下面展示下整体的数据导入脚本

主要分为3个文件：

-rw-r--r--. 1 root root  637 7月   4 20:37 insert_into_bucket.hql
-rw-r--r--. 1 root root   37 7月   4 20:26 insert_into_bucket.init
-rwxr-xr-x. 1 root root 1788 7月   4 20:27 insert_into_bucket.sh

insert_into_bucket.hql   数据导入HQL

insert_into_bucket.init   设置初始环境

insert_into_bucket.sh     主体执行脚本

 

insert_into_bucket.sh 

1. #! /bin/bash

2.  

3. set -o errexit

4.  

5. source /etc/profile

6. source ~/.bashrc

7.  

8. ROOT_PATH=$(dirname $(readlink -f $0))

9. echo $ROOT_PATH

10.  

11. date_pattern_old='^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$'

12. date_pattern='^[0-9]{4}-((0([1-9]{1}))|(1[1|2]))-(([0-2]([0-9]{1}))|(3[0|1]))$'

13.  

14. #参数数量

15. argsnum=$#

16.  

17. #一些默认值

18. curDate=`date +%Y%m%d`

19. partitionDate=`date -d '-1 day' +%Y-%m-%d`

20. fileLocDate=`date -d '-1 day' +%Y-%m-%d`

21.  

22. #日志存放位置

23. logdir=insert_bucket_logs

24.  

25. function tips() {

26. echo "Usage : insert_into_bucket.sh [date]"

27. echo "Args :"

28. echo "date"

29. echo " date use this format yyyy-MM-dd , ex : 2018-06-02"

30. echo "============================================================"

31. echo "Example :"

32. echo " example1 : sh insert_into_bucket.sh"

33. echo " example2 : sh insert_into_bucket.sh 2018-06-02"

34. }

35.  

36. if [ $argsnum -eq 0 ] ; then

37. echo "No argument, use default value"

38. elif [ $argsnum -eq 1 ] ; then

39. echo "One argument, check date pattern"

40. arg1=$1

41. if ! [[ "$arg1" =~ $date_pattern ]] ; then

42. echo -e "\033[31m Please specify valid date in format like 2018-06-02"

43. echo -e "\033[0m"

44. tips

45. exit 1

46. fi

47. dateArr=($(echo $arg1 |tr "-" " "))

48. echo "dateArr length is "${#dateArr[@]}

49. partitionDate=${dateArr[0]}-${dateArr[1]}-${dateArr[2]}

50. else

51. echo -e "\033[31m Not valid num of arguments"

52. echo -e "\033[0m"

53. tips

54. exit 1

55. fi

56.  
57.  

58. if [ ! -d "$logdir" ]; then

59. mkdir -p $logdir

60. fi

61.  
62.  

63. cd $ROOT_PATH

64.  

65. #nohup hive -hivevar p_date=${partitionDate} -hivevar f_date=${fileLocDate} -f hdfs_add_partition_dmp_clearlog.hql >> $logdir/load_${curDate}.log

66.  

67. nohup beeline -u jdbc:hive2://master:10000 -n root --color=true --silent=false --hivevar p_date=${partitionDate} -i insert_into_bucket.init -f insert_into_bucket.hql >> $logdir/insert_bucket_${curDate}.log

68.  

 

insert_into_bucket.init 

set hive.enforce.bucketing = true;

 

insert_into_bucket.hql

1. use clickcube;

2.  

3. INSERT OVERWRITE TABLE clickcube_mid_bucket

4. PARTITION( day = '${hivevar:p_date}' )

5. SELECT

6. clickcube_mid.logtype,

7. clickcube_mid.`date`,

8. clickcube_mid.`hour`,

9. clickcube_mid.projectid,

10. clickcube_mid.campaignid,

11. clickcube_mid.templateid,

12. clickcube_mid.mediaid,

13. clickcube_mid.slotid,

14. clickcube_mid.channeltype,

15. clickcube_mid.regioncode,

16. clickcube_mid.campclick,

17. clickcube_mid.campimp,

18. clickcube_mid.mediaclick,

19. clickcube_mid.mediaimp,

20. clickcube_mid.templateimp,

21. clickcube_mid.templatecampimp,

22. clickcube_mid.mediaclickcost,

23. clickcube_mid.campclickcost

24. FROM clickcube_mid

25. WHERE day = '${hivevar:p_date}'

26.  

 

 

针对于分桶表的数据抽样：

分桶的一个主要优势就是数据抽样，

主要有两种方式

1）基于桶抽样

2）基于百分比抽样

 

1）基于桶抽样：

hive> SELECT * FROMbucketed_users 
>   TABLESAMPLE(BUCKET 1 OUT OF 4 ON id); 
0 Nat 
4 Ann 

桶的个数从1开始计数。因此，前面的查询从4个桶的第一个中获取所有的用户。 对于一个大规模的、均匀分布的数据集，这会返回表中约四分之一的数据行。我们 也可以用其他比例对若干个桶进行取样(因为取样并不是一个精确的操作，因此这个 比例不一定要是桶数的整数倍)。

 

说法一：

 

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUTOF y)

    y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。

    x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

 

说法二：

   分桶语句中的分母表示的是数据将会被散列的桶的个数，

分子表示将会选择的桶的个数。

 

示例：

 SELECT COUNT(1) 

FROM clickcube_mid_bucket 

TABLESAMPLE(BUCKET 10 OUT OF 100 ON rand()) 

WHERE day='2018-07-03';

 

 

 

 

 

 

 

2）基于百分比抽样：

    hive另外一种按照抽样百分比进行抽样的方式，该种方式基于行数，按照输入路径下的数据块的百分比进行抽样。

    这种抽样的最小单元是一个hdfs数据块，如果表的数据大小小于普通块大小128M,将返回所有行。

 

基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优种子信息：

<property>

    <name>hive.sample.seednumber</name>

    <value>0</value>

</property>

 

A number userd for percentage sampling. By changing this number, user will change the subsets of data sampled.

    

 

 

 

 

 

数据分桶存在的一些缺陷：

    

    如果通过数据文件LOAD 到分桶表中，会存在额外的MR负担。

    

    实际生产中分桶策略使用频率较低，更常见的还是使用数据分区。