一、开启HIVE中分区表支持中文字段 1.根据HIVE开启分区支持中文.txt中的操作步骤去MySQL中执行,修改HIVE元数据库中的编码格式 2. 插入数据 INSERT INTO TABLE filetest.partition_student PARTITION(gender="女生") SELECT "1500100002","吕金鹏",24,"文科六班"
二、分区表操作 特点: 1.分区表与其他表不同点在于,分区字段的值为表目录下的子目录格式为: 分区字段=值
1.建表语句create database learn2; CREATE TABLE IF NOT EXISTS learn2.partition_student( id STRING COMMENT "学生ID", name STRING COMMENT "学生姓名", age int COMMENT "年龄", gender STRING COMMENT "性别" ) PARTITIONED BY (clazz STRING COMMENT "班级") ROW FORMAT DELIMITED FIELDS TERMINATED BY "," STORED AS TEXTFILE;
2.分区表插入数据 1. load data local inpath "本地路径" into table 表名 PARTITION(分区字段 = 值) load data local inpath "/usr/local/soft/hive-3.1.2/data/文科一班.txt" into table learn2.partition_student PARTITION(clazz="文科一班"); load data local inpath "/usr/local/soft/hive-3.1.2/data/文科二班.txt" into table learn2.partition_student PARTITION(clazz="文科二班"); 插入多级分区: load data local inpath "/usr/local/soft/hive-3.1.2/data/文科一班女.txt" into table learn2.partition_student4 PARTITION(clazz="文科一班",gender="女"); load data local inpath "/usr/local/soft/hive-3.1.2/data/文科二班男.txt" into table learn2.partition_student4 PARTITION(clazz="文科二班",gender="男");2. 覆盖原先分区中的数据 load data local inpath "本地路径" overwrite into table 表名 PARTITION(分区字段 = 值) load data local inpath "/usr/local/soft/hive-3.1.2/data/新文科一班.txt" overwrite into table learn2.partition_student PARTITION(clazz="文科一班"); 3. -put 方法上传数据 dfs -put /usr/local/soft/hive-3.1.2/data/理科一班.txt /user/hive/warehouse/learn2.db/partition_student2/clazz=理科一班/ 4.增加动态分区操作 set hive.exec.dynamic.partition=true; -- 设置开启动态分区 set hive.exec.dynamic.partition.mode=nostrict; -- 设置动态分区的模式为非严格模式 set hive.exec.max.dynamic.partitions.pernode=1000; --设置分区的最大分区数 插入格式: INSERT INTO TABLE 表名 PARTITION(分区字段) SELECT查询语句 分区的规则:默认是查询语句中后几列CREATE TABLE IF NOT EXISTS learn2.partition_student3( id STRING COMMENT "学生ID", name STRING COMMENT "学生姓名", age int COMMENT "年龄", gender STRING COMMENT "性别" ) PARTITIONED BY (clazz STRING COMMENT "班级") ROW FORMAT DELIMITED FIELDS TERMINATED BY "," STORED AS TEXTFILE; INSERT INTO TABLE learn2.partition_student3 PARTITION(clazz) SELECT id,name,age,gender,clazz FROM learn2.partition_student2 注意: 错误:FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict 解决方式: 执行开启动态分区设置
3.查询分区 1. show partitions 表名; show partitions learn2.partition_student; 4.删除分区 1. alter table 表名 drop PARTITION(分区字段=值) alter table learn2.partition_student drop PARTITION(clazz="文科二班"); 注意: 如果分区表是外部表,那么删除分区操作只能删除HIVE中的元数据 数据依然存在CREATE EXTERNAL TABLE IF NOT EXISTS learn2.partition_student2( id STRING COMM
Hadoop-hive2
最新推荐文章于 2023-11-12 22:31:26 发布
本文详细介绍了Hive中的分区表操作,包括如何开启对中文字段的支持,以及创建、插入数据到分区表的方法。还讨论了动态分区、数据加载、数据导出的各种方式,并涉及数据类型转换和简单的数据查询操作,如JOIN、GROUP BY、ORDER BY等。
摘要由CSDN通过智能技术生成