Hive中DML数据操作

一、数据导入

1.1 向表中装载数据

1、语法

hive> load data [local] inpath '/opt/module/datas/student.txt' [overwrite] | into table student 
[partition (partcol1=val1,…)]; 

(1) load data：表示加载数据
(2) local：表示从本地加载数据到 hive 表，否则从 HDFS 加载数据到 hive 表
(3) inpath：表示加载数据的路径
(4) overwrite：表示覆盖表中已有数据，否则表示追加
(5) into table：表示加载到哪张表
(6) student：表示具体的表
(7) partition：表示上传到指定分区

2、案例实操
(1) 创建一张表

hive (db_hive1)>  create table student(id string, name string) row format delimited fields terminated by '\t'; 
OK
Time taken: 0.215 seconds

(2) 加载本地文件到 hive

hive (db_hive1)> load data local inpath '/opt/module/datas/student.txt' into table db_hive1.student;
Loading data to table db_hive1.student
Table db_hive1.student stats: [numFiles=1, totalSize=24]
OK
Time taken: 0.31 seconds
hive (db_hive1)> select * from student;
OK
student.id	student.name
1	a
2	b
3	c
4	d
5	e
6	f
Time taken: 0.083 seconds, Fetched: 6 row(s)

(3) 加载 HDFS 文件到 hive 中
A、上传文件到 HDFS

hive (db_hive1)> dfs -put /opt/module/datas/student.txt /user/hive/student.txt;

B、加载 HDFS 上数据

hive (db_hive1)> load data inpath '/user/hive/student.txt' into table db_hive1.student; 
Loading data to table db_hive1.student
Table db_hive1.student stats: [numFiles=2, totalSize=48]
OK
Time taken: 0.379 seconds
hive (db_hive1)> select * from student;
OK
student.id	student.name
1	a
2	b
3	c
4	d
5	e
6	f
1	a
2	b
3	c
4	d
5	e
6	f
Time taken: 0.074 seconds, Fetched: 12 row(s)

(4) 加载数据覆盖表中已有数据
A、上传文件到 HDFS

hive (db_hive1)> dfs -put /opt/module/datas/student.txt /user/hive/student1.txt;

B、加载数据覆盖表中已有的数据

hive (db_hive1)> load data inpath '/user/hive/student1.txt' overwrite into table db_hive1.student;
Loading data to table db_hive1.student
Moved: 'hdfs://hadoop151:9000/user/hive/warehouse/db_hive1.db/student/student.txt' to trash at: hdfs://hadoop151:9000/user/test/.Trash/Current
Moved: 'hdfs://hadoop151:9000/user/hive/warehouse/db_hive1.db/student/student_copy_1.txt' to trash at: hdfs://hadoop151:9000/user/test/.Trash/Current
Table db_hive1.student stats: [numFiles=1, numRows=0, totalSize=24, rawDataSize=0]
OK
Time taken: 0.36 seconds
hive (db_hive1)> select * from student;
OK
student.id	student.name
1	a
2	b
3	c
4	d
5	e
6	f
Time taken: 0.068 seconds, Fetched: 6 row(s)

1.2 通过查询语句向表中插入数据(Insert)

1、创建一张分区表

hive (db_hive1)> create table student1(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';
OK
Time taken: 0.918 seconds

2、基本插入数据

hive (db_hive1)> insert into table  student1 partition(month='201709') values(1,'wangwu'); 
Query ID = test_20200217212020_42f330b2-1238-4e8a-bb82-6853bb21feda
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1581943453985_0001, Tracking URL = http://hadoop152:8088/proxy/application_1581943453985_0001/
Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job  -kill job_1581943453985_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2020-02-17 21:20:40,968 Stage-1 map = 0