hive表操作

最新推荐文章于 2023-09-20 15:19:47 发布

lz_matlab

最新推荐文章于 2023-09-20 15:19:47 发布

阅读量218

点赞数

分类专栏： Hive 文章标签： hive hadoop 大数据

本文链接：https://blog.csdn.net/asdsdwuwang/article/details/129897681

版权

Hive 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

hive表操作

Hive表分区的实质是分目录（将超大表的数据按指定标准细分到指定目录），且分区的字段不属于Hive表中存在的字段；分桶的实质是分文件（将超大文件的数据按指定标准细分到分桶文件），且分桶的字段必须在Hive表中存在。

分桶的意义在于：

可以提高多表join的效率（因为通过分桶已经将超大数据集提取出来了。假如原数据被分了4个桶，此时2表join的时候只需要读取符合条件的一个分桶，则理论上效率可提升4倍）
加速数据抽样的效率（理由同上，只需要按照指定规则抽取指定分桶的数据即可，不需要扫描全表）

需要Hive表分桶的时候，我们可以观察到Reduce的任务数量 = 分桶的数量，也就是最终产生的分桶文件的个数，因为分桶表就是通过MapReduce任务计算而来。由此可见，其实桶的概念就是MapReduce的分区的概念，两者完全相同。

需要注意的是，不论每次插入分桶表的数据量有多少，对应分桶里面是否需要写入新的数据，Reduce总会启动分桶数量那么多的task去计算，故可能产生许多空白文件（原因是reduce写的时候，不知道会不会有数据需要输出，所以默认初始化了一个文件）。

为了避免产生大量空白文件，给namenode增加无谓的压力，我们可以通过以下设置方法告知reduce作业不要产生空白文件：

//导入jar包
import org.apache.hadoop.mapreduce.lib.output.LazyOutputFormat;
//使用LazyOutputFormat
LazyOutputFormat.setOutputFormatClass(job, TextOutputFormat.class);

1，创建分区表

spark开启分区表

 .enableHiveSupport
spark.sql("set hive.exec.dynamic.partition.mode=nonstrict")

create table `tank_test1` (  

`log_time` int,  

`aid` string,  

`aid_name`  string,  

`ip` string  

)  

PARTITIONED BY (log_date int)  

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;

2、创建分桶表 2个

create table `tank_test2` (  

`log_date` int,  

`log_time` int,  

`aid` string,  

`aid_name` string,  

`ip` string  

)  

CLUSTERED BY(log_date) SORTED BY(log_date ASC) INTO 2 BUCKETS  

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;

3、创建分区与分桶混合表

create table `tank_test3` (  

`log_time` int,  

`aid` string,  

`aid_name` string,  

`ip` string  

)  

PARTITIONED BY (log_date int)  

CLUSTERED BY(aid) SORTED BY(aid ASC) INTO 2 BUCKETS  

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;

4、修改分区和分桶

4.1修改分区

增加分区

A、创建单个分区

hive (default)> alter table dept_partition add partition(day='20200404');

B、同时创建多个分区

hive (default)> alter table dept_partition add partition(day='20200405') partition(day='20200406');

删除分区

A、删除单个分区

hive (default)> alter table dept_partition drop partition (day='20200406');

B、同时删除多个分区

hive (default)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

C、查看分区表有多少分区

hive> show partitions dept_partition;

查看分区表结构

hive> desc formatted dept_partition;
# Partition Information
# col_name data_type comment
month string

2. 二级分区

思考: 如何一天的日志数据量也很大，如何再将数据拆分?

创建二级分区表

hive (default)> create table dept_partition2(
	deptno int, dname string, loc string
) partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

2. 正常的加载数据

加载数据到二级分区表中

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401', hour='12');

查询分区数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='12';

把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

方式一：上传数据后修复

A、上传数据

hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

B、查询数据 (查询不到刚上传的数据)

hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

C、执行修复命令

hive> msck repair table dept_partition2;

D、再次查询数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

方式二：上传数据后添加分区

A、上传数据

hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;

B、执行添加分区

hive (default)> alter table dept_partition2 add partition(day='201709',hour='14');

C、查询数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='14';

方式三：创建文件夹后 load 数据到分区

A、创建目录

hive (default)> dfs -mkdir -p /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;

B、上传数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition2 partition(day='20200401',hour='15');

C、查询数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='15';

3. 动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置。

开启动态分区参数设置

A、开启动态分区功能 (默认 true，开启)

hive.exec.dynamic.partition=true

B、设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

hive.exec.dynamic.partition.mode=nonstrict

C、在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认1000

hive.exec.max.dynamic.partitions=1000

D、在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100

E、整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

hive.exec.max.created.files=100000

F、当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

hive.error.on.empty.partition=false

1. hive> show partitions tank_test3; 
2. OK 
3. log_date=20190903 
4. log_date=20190904 
5. log_date=20190905 
6. log_date=20190906 
7. Time taken: 0.231 seconds, Fetched: 4 row(s) 
8. hive> ALTER TABLE tank_test3 ADD PARTITION (log_date=20190907); //添加分区 
9. OK 
10. Time taken: 0.489 seconds 
11. hive> show partitions tank_test3; 
12. OK 
13. log_date=20190903 
14. log_date=20190904 
15. log_date=20190905 
16. log_date=20190906 
17. log_date=20190907 
18. Time taken: 0.188 seconds, Fetched: 5 row(s) 
19. hive> ALTER TABLE tank_test3 PARTITION (log_date=20190907) RENAME TO PARTITION (log_date=20190908); //重命名分区 
20. OK 
21. Time taken: 1.998 seconds 
22. hive> show partitions tank_test3; 
23. OK 
24. log_date=20190903 
25. log_date=20190904 
26. log_date=20190905 
27. log_date=20190906 
28. log_date=20190908 
29. Time taken: 0.371 seconds, Fetched: 5 row(s) 
30. hive> ALTER TABLE tank_test3 DROP IF EXISTS PARTITION (log_date=20190908); //删除分区 
31. Dropped the partition log_date=20190908 
32. OK 
33. Time taken: 0.682 seconds 
34. hive> show partitions tank_test3; 
35. OK 
36. log_date=20190903 
37. log_date=20190904 
38. log_date=20190905 
39. log_date=20190906

4.2修改分桶

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。分区针对的是数据的存储路径；分桶针对的是数据文件。

B、创建分桶表

create table stu_buck(id int, name string) clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';

C、查看表结构

hive (default)> desc formatted stu_buck;
Num Buckets: 4

D、导入数据到分桶表中，load 的方式

hive (default)> load data inpath '/student.txt' into table stu_buck;

E、查看创建的分桶表中是否分成 4 个桶

F、查询分桶的数据

hive(default)> select * from stu_buck;

E、分桶规则：

根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

分桶表操作需要注意的事项:

A、reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数

B、从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题

C、不要使用本地模式

insert 方式将数据导入分桶表

hive(default)>insert into table stu_buck select * from student_insert;

1. hive> set hive.enforce.bucketing = true; //强制使用分桶 
2. hive> set map.reduce.tasks = 4; //设置分桶数 
3.  
4. //以前是2个分桶 
5. hive> dfs -ls /home/cdh6/hive/warehouse/tanktest.db/tank_test2; 
6. Found 2 items 
7. -rwxrwxrwt 2 root hive 46767339 2020-01-15 11:25 /home/cdh6/hive/warehouse/tanktest.db/tank_test2/000000_0 
8. -rwxrwxrwt 2 root hive 6683039 2020-01-15 11:25 /home/cdh6/hive/warehouse/tanktest.db/tank_test2/000001_0 
9.  
10. //插入数据 
11. hive> insert into tank_test2 select log_date,log_time,aid,aid_name,ip from user_info limit 2000000; 
12.  
13. //现在是4个分桶了 
14. hive> dfs -ls /home/cdh6/hive/warehouse/tanktest.db/tank_test2; 
15. Found 4 items 
16. -rwxrwxrwt 2 root hive 46767339 2020-01-15 11:25 /home/cdh6/hive/warehouse/tanktest.db/tank_test2/000000_0 
17. -rwxrwxrwt 2 root hive 71991116 2020-01-15 14:29 /home/cdh6/hive/warehouse/tanktest.db/tank_test2/000000_0_copy_1 
18. -rwxrwxrwt 2 root hive 6683039 2020-01-15 11:25 /home/cdh6/hive/warehouse/tanktest.db/tank_test2/000001_0 
19. -rwxrwxrwt 2 root hive 34985281 2020-01-15 14:30 /home/cdh6/hive/warehouse/tanktest.db/tank_test2/000001_0_copy_1

4.3开启支持分桶

set hive.enforce.bucketing=true;　　　　-- 默认：false --
设置为 true 之后，mr 运行时会根据 bucket 的个数自动分配 reduce task的个数。
　　当然，用户也可以通过 mapred.reduce.tasks 自己设置 reduce 任务个数，但分桶时不推荐使用。注意：一次作业产生的桶（文件数量）和 reduce task 个数一致

4.4往分桶表中加载数据

/* 往分桶表中插入数据的语法类似下面 */
insert into table bucket_table select columns from tbl;    -- 全新插入 --

insert overwrite table bucket_table select columns from tbl;    -- 覆盖重写 --

4.5分桶表数据抽样

/*
抽样语法：TABLESAMPLE(BUCKET x OUT OF y)。其中：
x：表示从第x个桶中抽取数据
y：表示每y个桶中抽取一次数据（必须是分桶数量的倍数 or 因子）
*/
select * from bucket_table tablesample(bucket 1 out of 4 on columns);

spark读写分桶表

1、写入hive表

customerDF
.write
.option("path", "/some/path")
.mode("overwrite")
.format("parquet")
.bucketBy(200, "customer_key")
.sortBy("customer_key")
.saveAsTable("table_name")

2、写入到指定路径

 giftDF.write
.partitionBy("plat","date")
.mode(SaveMode.Append)
.bucketBy(20,"room_id")
.parquet(path)

3、写入分桶分区表

import spark.implicits._
rdd.toDF("col1", "col2").orderBy("col1")
  .write.format("json")
  .partitionBy("col1")
  .bucketBy(2, "col2")
  .saveAsTable("bucket")

分区，分桶和数据重分区方法

分区：Partitioning:

分区数据通常用于水平分配负载，这具有性能优势，并有助于以逻辑方式组织数据。
分区表会更改持久化数据的结构，现在将创建反映此分区结构的子目录。
这可以显着提高查询性能，但前提是分区方案反映了常见的过滤。根据指定列进行分区存储，每个列值一个文件结构。

df.write.partitionedBy(column*) .parquet("")

分桶：Bucketing:

Bucketing是另一种将数据集分解为更易于管理的部分的技术 . 根据提供的列，将整个数据散列到用户定义数量的存储区（文件）中。

Bucket和Partition的区别，Bucket的最终目的也是实现分区，但是和Partition的原理不同，当我们根据指定列进行Partition的时候，Spark会根据列的名字对数据进行分区(如果没有指定列名则会根据一个随机信息对数据进行分区)。Bucketing的最大不同在于它使用了指定列的哈希值，这样可以保证具有相同列值的数据被分到相同的分区。

怎么用 Bucket，按Bucket保存，目前在使用 bucketBy 的时候，必须和 sortBy，saveAsTable 一起使用，如下。这个操作其实是将数据保存到了文件中（如果不指定path，也会保存到一个临时目录中）。

df.write
  .bucketBy(10, "name")
  .sortBy("name")
  .mode(SaveMode.Overwrite)
  .option("path","/path/to")
  .saveAsTable("bucketed")

将 columns 相同的数据分到同一文件中来分组数据 . 生成的文件个数由 n 控制。

重分区：Repartition:

它根据给定的分区表达式将一个新的 DataFrame 均衡地返回到给定数量的内部文件中。生成的DataFrame是散列分区的。coalesce()方法的参数shuffle默认设置为false，repartition()方法就是coalesce()方法shuffle为true的情况。
1、coalesce()方法源码

def coalesce(numPartitions: Int, shuffle: Boolean = false)(implicit ord: Ordering[T] = null)
    : RDD[T] = withScope {
  if (shuffle) { 
  }
  else {
  }
}

返回一个经过简化到numPartitions个分区的新RDD。这会产生一个窄依赖，例如：你将1000个分区转换成100个分区，这个过程不会发生shuffle，相反如果10个分区转换成100个分区将会发生shuffle。如果想要减少分区数，考虑使用coalesce，这样可以避免执行shuffle。

2、repartition()方法源码

def repartition(numPartitions: Int)(implicit ord: Ordering[T] = null): RDD[T] = withScope {
  coalesce(numPartitions, shuffle = true)
}

返回一个恰好有numPartitions个分区的RDD，可以增加或者减少此RDD的并行度。内部使用shuffle重新分布数据。

根据columns内容重新分配partition。请注意，没有数据持久存储到存储，这只是基于类似 bucketBy 的约束的数据内部 Balance。

分桶有两个主要好处：

改进的查询性能：在连接时，我们可以在相同的分桶列上明确指定桶的数量。由于每个存储桶包含相同大小的数据，因此映射端连接的性能优于存储桶表上的非存储桶表。在 map-side join 中，左侧表存储桶将准确知道右侧存储桶包含的数据集，以便以结构良好的格式执行表联接。
改进的采样：数据已经被分成更小的块，因此采样得到了改进。

何时使用桶列

表大小很大（> 200G）。
该表具有高基数列，这些列经常用作过滤和/或连接键。
中等大小的表，但主要用于连接一个巨大的桶化表，桶化它仍然是有益的
排序合并连接（没有存储桶）由于随机播放而不是由于数据倾斜而变慢

如何配置存储桶列

选择高基数列作为桶列。
尽量避免数据倾斜。
至少 500 个桶（因为小桶数会导致并行执行不佳）。
排序桶是可选的，但强烈推荐。

如何在 Spark 中创建数据桶

下面是在 SparkAPI 中创建存储桶的示例。bucketBy是在 spark 中创建存储桶的函数。我们需要将桶的信息保存在某处，所以这里需要使用saveAsTable来保存桶表的元数据信息。

# n 是要创建的桶数df.write.mode(“save_mode”).option(“path”, “s3 path/hdfs path”) *.bucketBy(n, ‘col1’, ‘col2’…) **.sortBy(‘col1’, ’ col2’) *.saveAsTable(‘table_name’, format=‘parquet’)df = spark.table(‘table_name’)

在上面的示例中，我们使用了 bucketBy 和 sortBy，因为在某些情况下我们有多个连接键，并且希望将整数键放在 bucketBy 中，将字符串键放在 sortBy 中。当我们做数据桶时，sortBy 是可选的。
可以根据数据大小和我们对数据运行的查询来决定存储桶大小的数量。通常，每个存储桶可能更喜欢 100 MB 到 200 MB。
存储桶表将使用以下命名约定将表保存在路径中。

如何在 Spark 上启用分桶？
默认情况下启用分桶。或者，您可以在 Spark Shell 或属性文件中设置以下属性。

设置 spark.sql.sources.bucketing.enabled=true

Spark 中对表进行分桶的优点

优化表。
使用预洗牌分桶表时优化联接。
当您在分桶列上定义谓词时，启用更有效的查询。
优化了对表数据的访问。在桶列上使用 WHERE 条件时，您将最小化给定查询的表扫描。
将数据均匀分布在不同的存储桶中，从而实现对表数据的最佳访问。

lz_matlab

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
hive表操作

Hive表的实质是（将超大表的数据按指定标准细分到指定目录），且分区的字段不属于Hive表中存在的字段；的实质是（将超大文件的数据按指定标准细分到分桶文件），且分桶的字段必须在Hive表中存在。需要Hive表分桶的时候，我们可以观察到Reduce的任务数量 = 分桶的数量，也就是最终产生的分桶文件的个数，因为分桶表就是通过MapReduce任务计算而来。由此可见，其实桶的概念就是MapReduce的分区的概念，两者完全相同。
复制链接

扫一扫