5.1 Apache Hive DML语句与函数使用

一、Hive SQL DML语法之加载数据

1、Hive SQL-DML-Load加载数据

回顾

• 在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；
• 文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
  
• 不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功;
• 最原始暴力的方式就是使用hadoop fs –put|-mv等方式直接将数据移动到表文件夹下；
• 但是，Hive官方推荐使用Load命令将数据加载到表中
  

Load语法功能

• Load英文单词的含义为：加载、装载；
• 所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。
• 纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作

Load语法规则

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
1

语法规则之filepath

• filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。
• filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：

1. 相对路径，例如：project/data1
2. 绝对路径，例如：/user/hive/project/data1
3. 具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

语法规则之LOCAL

• 指定LOCAL， 将在本地文件系统中查找文件路径。
  若指定相对路径，将相对于用户的当前工作目录进行解释；
  用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1。
• 没有指定LOCAL关键字。
  如果filepath指向的是一个完整的URI，会直接使用这个URI；
  如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）

LOCAL本地是哪里？

• 如果对HiveServer2服务运行此命令
  本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。
  

练习：Load Data From Local FS or HDFS

Step1:建表

--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS 用于演示从HDFS加载数据
create external table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
1
2
3
4
5

Step2:load加载数据

--建议使用beeline客户端 可以显示出加载过程日志信息
--step2:加载数据
-- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
1
2
3
4
5
6
7

加载数据日志信息

2、Hive SQL-DML-Insert插入数据

Insert语法功能

• Hive官方推荐加载数据的方式：
  清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。
• 也可以使用insert语法把数据插入到指定的表中，最常用的配合是把查询返回的结果插入到另一张表中。

insert+select

• insert+select表示：将后面查询返回的结果作为内容插入到指定表中。

1. 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
2. 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

INSERT INTO TABLE tablename select_statement1 FROM from_statement;
1

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;
--step2：创建一张目标表 只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表
1
2
3
4
5
6
7
8
9