03：Hive从0到1系列学习：DML数据操作语言

淡淡的烟草味﹌ 2023-10-04 17:19 2阅读 0赞

#### 文章目录 ####

*   *  Hive的DML语言
     *   *  1、数据导入
         *   *  ①向表中装载数据
             *  ②从hdfs加载到hive表中
             *  ③通过查询向hive表中装载数据
             *  ④查询语句中创建表并加载数据（As Select）
             *  ⑤创建表时通过Location指定加载数据路径
             *  ⑥Import数据到指定Hive表中
         *  2、数据导出
         *   *  ①Insert导出
             *  ②Hadoop命令导出到本地
             *  ③Hive Shell 命令导出
             *  ④Export导出到HDFS上
         *  3、清除表中的数据Truncate

### Hive的DML语言 ###

#### 1、数据导入 ####

##### ①向表中装载数据 #####

语法：

load data [local] inpath '数据的path' [overwrite] into table table_name [partition (partcol1=val1,…)];

命令参数说明

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Load data</td> 
   <td>加载数据</td> 
  </tr> 
  <tr> 
   <td>Local</td> 
   <td>表示从本地加载数据到hive表，否则是从HDFS加载数据到Hive表</td> 
  </tr> 
  <tr> 
   <td>Inpath</td> 
   <td>表是加载数据的路径</td> 
  </tr> 
  <tr> 
   <td>Overwrite</td> 
   <td>表示覆盖表中已有数据，否则表示追加</td> 
  </tr> 
  <tr> 
   <td>Into table</td> 
   <td>表示加载数据到哪张表中</td> 
  </tr> 
  <tr> 
   <td>Partition</td> 
   <td>表示加载数据到指定分区</td> 
  </tr> 
 </tbody> 
</table>

1）创建一张表

create table student(
        id string,
        name string
    )
    row format delimited
    fields terminated by '\t';

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]

2）加载本地文件到hive

load data local inpath '/opt/module/hive/datas/student.txt' into table test.student;

![在这里插入图片描述][64a580331a834459806e3f11f67d0d8d.png]

3）查询数据是否load成功  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]

##### ②从hdfs加载到hive表中 #####

（1）首先将文件上传到HDFS中

dfs -put /opt/module/hive/datas/student.txt /input;

（2）将HDFS文件加载到hive表中

load data inpath '/input/student.txt' into table test.student;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]

（3）覆盖数据

load data inpath '/input/student.txt' overwrite into table test.student;

![在这里插入图片描述][a3466967a7194db9a27b87473d5116af.png]

**注意：从hdfs上加载数据到hive表中，类似于拷贝的方式，所以在加载之后数据已经被拷贝走了，这个时候再次执行加载就会报错，需要重新上传数据到hdfs上**

dfs -put /opt/module/hive/datas/student.txt /input;

load data inpath '/input/student.txt' overwrite into table test.student;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]

##### ③通过查询向hive表中装载数据 #####

insert overwrite table student2 select id, name from student ;

1）首先创建student2表

create table student2(
        id string,
        name string
    )
    row format delimited
    fields terminated by '\t';

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]

2）基本的插入数据

insert into table  student2 values(1,'wangwu'),(2,'zhaoliu');

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]

3）根据查询结果插入数据

insert overwrite table student2 select id, name from student;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 6]

查看插入结果  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 7]

**insert into 和 insert overwrite的区别**

<table> 
 <thead> 
  <tr> 
   <th>Insert into：</th> 
   <th>以追加数据的方式插入到表或分区，原有数据不会删除</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Insert overwrite：</td> 
   <td>会覆盖表中已存在的数据</td> 
  </tr> 
 </tbody> 
</table>

**insert不支持插入部分字段**

4）多表插入模式

from student
    insert overwrite table student2 
    	select id, name 
    insert overwrite table student3 
    	select id, name;

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eJTKxy48-1640059477245)(C:\\Users\\lenovo\\AppData\\Roaming\\Typora\\typora-user-images\\image-20211219191510853.png)\]

##### ④查询语句中创建表并加载数据（As Select） #####

在创建表的时候通过查询结果装载入表

create table if not exists student4
    as select
    	id, name
    from student;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 8]

##### ⑤创建表时通过Location指定加载数据路径 #####

创建时指定表的存储位置

create external table if not exists student5(
        id int, name string
    )
    row format delimited fields terminated by '\t'
    location '/input/student';

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 9]

##### ⑥Import数据到指定Hive表中 #####

#### 2、数据导出 ####

##### ①Insert导出 #####

1）将查询的结果导出到本地

insert overwrite local directory '/opt/module/hive/datas/export/student'
    select * from student;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 10]

查看导出的文件  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 11]

2）上面这种情况导出的数据都挤在了一起，还可以格式化导出文件

insert overwrite local directory '/opt/module/hive/datas/export/student1'
    ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY '\t'             
    select * from student;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 12]

查看文件  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 13]

3）还可以将查询的结果导出到hdfs上，只需要去掉语句中的lcoal即可

insert overwrite directory '/output/student'
    ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY '\t' 
    select * from student;

##### ②Hadoop命令导出到本地 #####

直接将hive存放在hdfs上的文件下载到本地即可

dfs -get /user/hive/warehouse/student/student.txt /opt/module/hive/datas/export/student3.txt;

##### ③Hive Shell 命令导出 #####

hive shell后面的参数加上e可以直接在命令窗口执行hql语句

bin/hive -e 'select * from test.student;' > /opt/module/hive/datas/export/student4.txt;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 14]

查看文件  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 15]

##### ④Export导出到HDFS上 #####

export table test.student to '/user/hive/warehouse/export/student';

export和import主要用于两个Hadoop平台集群之间Hive表迁移

⑤sqoop导出

⑥DataX导出

#### 3、清除表中的数据Truncate ####

注意：truncate只能清除管理表的数据，不可以清除外部表的数据

truncate table student4;

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 16]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/12131dd5d4ad4d0f8a9616eb2c1ff301.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[64a580331a834459806e3f11f67d0d8d.png]: https://img-blog.csdnimg.cn/64a580331a834459806e3f11f67d0d8d.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/b6ec466a0a85469eaf8303f31c981311.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/cf631ee1222447d7826ed0ec48c7375f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[a3466967a7194db9a27b87473d5116af.png]: https://img-blog.csdnimg.cn/a3466967a7194db9a27b87473d5116af.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/c860774293cc401eb2338e2f65fb0b90.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/f819304c7d35431eb569a40d950d3b09.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/5ddb0680e7e440df9fe1b0271a25f852.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/859fefa3507c4a5b969c1f6f7ffa3875.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/4961344617934c58a55c22e5475bb891.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 8]: https://img-blog.csdnimg.cn/046ea8ee711241fbb34ae697fa1078db.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 9]: https://img-blog.csdnimg.cn/3c2e4bd0ec554846a092725dfb611485.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 10]: https://img-blog.csdnimg.cn/b6d6401a4c684f82b004e25e65f211b5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 11]: https://img-blog.csdnimg.cn/b5bbaf17463e4cd3afa6d55ddccc7aaa.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 12]: https://img-blog.csdnimg.cn/62fc7a66feb84d80b4843fc7d78686b8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 13]: https://img-blog.csdnimg.cn/531245a6e60240499771ed141ac3fd9d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 14]: https://img-blog.csdnimg.cn/55e03f36703c426e8142a29b51ea20c2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 15]: https://img-blog.csdnimg.cn/5945446d931b4ba1aa765ce068273e57.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 16]: https://img-blog.csdnimg.cn/f826f6498f2148ba8675904dce6ff898.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16