Hadoop-hive2

一、开启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