data hive load 内容冲突 分隔符 hive load data 分区

一、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.txt5. 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,95dfs -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);