hive分区、数据加载、数据导出、数据类型_hive load data local inpath 分区

一、hive分区

1、特点：

        分区表与其他表不同点在于，分区字段的值为表目录下的子目录格式 ,为: 分区字段=值

2.建表语句

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;

3.分区表插入数据

（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="文科二班");

（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 方法上传数据(直接在hdfs上上传数据)
            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查询语句

INSERT INTO TABLE learn2.partition_student3 PARTITION(clazz) SELECT id,name,age,gender,clazz FROM learn2.partition_student2
            分区的规则：默认是查询语句中后几列

4、查询分区

         （1）show partitions 表名;
                   show partitions learn2.partition_student;

5、删除分区

  （1）alter table 表名 drop PARTITION(分区字段=值)
            alter table learn2.partition_student drop PARTITION(clazz="文科二班");
        注意： 如果分区表是外部表，那么删除分区操作只能删除HIVE中的元数据 数据依然存在

        
 （2）强制删除分区
            dfs -rmr /user/hive/warehouse/learn2.db/partition_student2/clazz=文科二班
            alter table learn2.partition_student2 drop PARTITION(clazz="文科二班");

6、恢复被删除分区

        msck repair table 表名;

7、添加分区

    alter table 表名 add PARTITION(分区字段=值)
            alter table learn2.partition_student2 add PARTITION(clazz="理科一班");

8、 创建多级分区

（1）  特点： 
              1.查询和操作分区时 格式为： clazz=文科一班/gender=女 
              2.在HDFS上多级分区表现为多层级目录
              3.根据具体的数据情况进行区分，尽量将大一级划分放在前
              4.多级分区常用来将数据按天进行存储

（2）建表语句
CREATE TABLE IF NOT EXISTS learn2.partition_student4(
id STRING COMMENT "学生ID",
name STRING COMMENT "学生姓名",
age int COMMENT "年龄"
) PARTITIONED BY (clazz STRING COMMENT "班级",gender STRING COMMENT "性别") 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
STORED AS TEXTFILE;

插入多级分区：
            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="男");

二、数据加载，加载至表中

1、从本地文件系统加载数据至表中

    load data local inpath "本地路径" into table 表名 
    load data local inpath "本地路径" overwrite into table 表名   //overwrite会覆盖原有数据

2、从HDFS上加载数据至表中

        dfs -mkdir /data;  //在hdfs上创建文件夹
        dfs -put "/usr/local/soft/hive-3.1.2/data/理科一班.txt"  /data    //将文件传输到hdfs

        load data inpath "/data/理科一班.txt" into table learn2.partition_student5;
        注意: 通过HDFS中的数据加载至表中时，原路径中的数据会移动至表目录下，原路径文件会消失。

3.INSERT 方式：

   INSERT INTO：
        INSERT INTO TABLE 表名 SELECT 查询语句

INSERT INTO TABLE learn2.partition_student6 SELECT id,name,age,clazz as gender FROM learn2.partition_student5;
    注意：查询语句中的字段需要与当前表的字段顺序一致，不然会导致数据错位。

    INSERT OVERWRITE：
        INSERT OVERWRITE TABLE 表名 SELECT 查询语句
        通过查询的数据覆盖表中原有数据

4. AS SELECT

通过查询结果创建表，并输入数据
    CREATE TABLE IF NOT EXISTS learn2.partition_student7 AS SELECT id,name,age,clazz as gender FROM learn2.partition_student5;
    如果只想创建表而不需要加载数据,那么可以使用 LIKE
    CREATE TABLE IF NOT EXISTS learn2.partition_student8 LIKE learn2.partition_student5;

5. LOCATION

    通过创建表并指定数据所在位置，然后加载数据(数据已存在于HDFS目录中)

    LOCATION "/testDatabase/total_score";   //在hdfs中的存储地址

6.import table

     需要搭配export使用，导入表时，可以指定新表名
    import table 表名 FROM "路径"
    IMPORT TABLE learn2.partition_student6 FROM "/data/export_data";

    IMPORT TABLE learn2.partition_student9 FROM "/data/export_data";

三、数据导出

1.INSERT OVERWRITE LOCAL DIRECTORY "路径" SELECT 查询语句
    INSERT OVERWRITE LOCAL DIRECTORY "/usr/local/soft/hive-3.1.2/data/output" SELECT * FROM learn2.partition_student6;
    导出数据时，通过执行MapReduce任务导出到本地文件系统，查看导出数据，发现数据没有分隔符

    INSERT OVERWRITE LOCAL DIRECTORY "/usr/local/soft/hive-3.1.2/data/output" ROW FORMAT DELIMITED FIELDS TERMINATED BY ":" SELECT * FROM learn2.partition_student6;
    对导出的数据加个分隔符

2.INSERT INTO LOCAL DIRECTORY "路径" SELECT 查询语句

3. HDFS get 方法
    dfs -get 表所在路径  本地路径
    dfs -get hdfs://master:9000/user/hive/warehouse/learn2.db/partition_student6 /本地路径

4. hive -e "SQL语句" > 本地文件系统
    分析：  
        hive -e "SQL语句" 表示通过-e 将SQL语句传入CLI并执行，最后退出CLI
        > 表示Linux中的重定向
    hive -e "SELECT * FROM learn2.partition_student6" > /usr/local/soft/hive-3.1.2/data/output/partition_student6.txt

5. export方法
    注：数据导出包括数据及其表的元数据信息 
    export table 库.表名 to HDFS
    export table learn2.partition_student6 to "/data/export_data"

四、数据类型

1、基本数据类型

        整型 TINYINT — 微整型，只占用1个字节，只能存储0-255的整数。

        SMALLINT– 小整型，占用2个字节，存储范围–32768 到 32767。

        INT– 整型，占用4个字节，存储范围-2147483648到2147483647。

        BIGINT– 长整型，占用8个字节，存储范围-2^63到2^63-1。

        布尔型BOOLEAN — TRUE/FALSE 浮点型FLOAT– 单精度浮点数。

        DOUBLE– 双精度浮点数。 字符串型STRING– 不设定长度。

2、日期类型：

        Timestamp 格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小数位精度）

        Date DATE值描述特定的年/月/日，格式为YYYY-MM-DD。

3、复杂数据类型： Structs，Maps，Arrays

（1）ARRAY 使用 （数组）
create table  learn2.person(
name string, 
work_locations array<string> 
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','; --表示Array中的数据是以,作为分隔

ArrayTest.txt文件中的数据：

biansutao    beijing,shanghai,tianjin,hangzhou
linan    changchu,chengdu,wuhan

上传数据：
    dfs -put /usr/local/soft/hive-3.1.2/data/ArrayTest.txt  hdfs://master:9000/user/hive/warehouse/learn2.db/person

查询：
    SELECT work_locations[0], work_locations[1] FROM learn2.person;  //可通过数组形式查询数据

（2）MAP使用 （kv结构）
create table learn2.score(name string, score map<string,int> )
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' --列的分隔符
COLLECTION ITEMS TERMINATED BY ','  -- 集合的分隔符
MAP KEYS TERMINATED BY ':';    -- 每个KV数据对的分隔符

score.txt 文件中的数据：

biansutao    '数学':80,'语文':89,'英语':95
jobs    '语文':60,'数学':80,'英语':99

上传数据：

dfs -put /usr/local/soft/hive-3.1.2/data/score.txt hdfs://master:9000/user/hive/warehouse/learn2.db/score

SELECT score["'数学'"] FROM learn2.score;  //可通过key查询values

3 Struct的使用

创建数据表
CREATE TABLE test(id int,course struct<course:string,score:int> )
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' --列分隔符
COLLECTION ITEMS TERMINATED BY ','; -- 集合分隔符
 

数据
1    english,80
2    math,89
3    chinese,95

dfs -put /usr/local/soft/hive-3.1.2/data/struct.txt hdfs://master:9000/user/hive/warehouse/learn2.db/test

SELECT course.score FROM learn2.test;

注意：struct就相当于一个对象通过 列名.属性 获取里面的数据

4、数据类型转换 

（1）强制转换小数型为int
        select cast("1.1" as int);

（2）小数转字符串
        select cast(1.1 as STRING);