DML数据操作

一、数据导入

• hive到hdfs
  load data local inpath ‘路径/文件’ [overwrite] into table table_name [partition(column=value,…)]
  从本地文件系统(hive所在服务器的本地)导入数据到hive表。hive表存储在hdfs上，所以数据就导入到hive所在的hdfs的存储路径
• hdfs到hdfs
  load data inpath ‘路径/文件’ [overwrite] into table table_name [partition(column=value,…)]
  从hdfs导入数据到hive表，数据和hive表都存储在hdfs上，所以数据就直接移动(而非复制)到hive表中，移动后源数据就不存在了
• 直接插入数据
  insert into/overwrite table table_name [partition(column=value,…)] values(value1,value2,…)
• 查询其他表数据导入hive表
  insert into/overwrite table table_name [partition(column=value,…)] select * from table_name1 [where column=value and …]
  使用overwrite的时候不用加into
• 通过查询其他表建表
  create table if not exists table_name(column data_type,…) as select * from table_name1

示例

hive本地文件系统导入数据到hdfs的hive表

hive> load data local inpath '/data/st.txt' into table st;
hive> select * from st;

hdfs文件导入到hdfs的hive表

#hdfs有文件
hive> load data inpath '/hive/data/st.txt' into table st;
hive> select * from st;

#hdfs上没有文件，可以先从hive上传文件到hdfs，然后再导入表
hive> dfs -put /data/st.txt /hive/data/;
hive> load data inpath '/hive/data/st.txt' overwrite into table st; #覆盖
hive> select * from st;

建表并制定hdfs上的位置，然后put本地数据入表

hive> create table if not exists st1(id int,name string) row format delimited fields terminated by '\t' location '/hive/data/st1';
hive> dfs -put '/data/st.txt' '/hive/data/st1';
hive> select * from st1;

直接插入数据

hive> insert into table st values(1,'cindy');
hive> select * from st;

查询其他表数据导入hive表

hive> insert into table st partition(month=‘202001’) select id,name from hive_test where month='202001'; #单表查询
hive> select * from st;

hive> insert overwrite table st select id,name from hive_test union select id,name from hive_test1; #多表查询
hive> select * from st;

通过查询其他表建表

hive> create table if not exists st1 as select * from st;
hive> select * from st1;

二、数据导出

insert overwrite [local] directory ‘路径’ row format delimited fields terminated by row_format select * from table_name;

• 把表的数据导出到指定路径，且指定字段分隔符
• 导出会自动创建路径，且若路径重复，overwrite会覆盖原有路径
• local代表导出到本地文件系统，否则导出到hdfs

示例

导出到本地

hive> insert overwrite local directory '/data/st' row format delimited fields terminated by '\t' select * from st;

[root@Hadoop01 /] cd /data
[root@Hadoop01 data] ll
[root@Hadoop01 data] cd st
[root@Hadoop01 st] ll
[root@Hadoop01 st] cat 000000_0

导出到hdfs

hive> insert overwrite directory '/hive/data/st' row format delimited fields terminated by '\t' select * from st;

[root@Hadoop01 /] hadoop fs -cd /hive/data
[root@Hadoop01 data] hadoop fs -ll
[root@Hadoop01 data] hadoop fs -cd st
[root@Hadoop01 st] hadoop fs -ll
[root@Hadoop01 st] hadoop fs -cat 000000_0

hadoop命令get数据到本地

导出库表数据时，先导出到Hadoop，再get到本地，不要直接get库表到本地，否则会乱码

[root@Hadoop01 /] hadoop fs -get /user/hive/warehouse/hive_test/month=202001 /data/test.txt
[root@Hadoop01 /] cat /data/test.txt

用hive交互命令导出数据到本地

[root@Hadoop01 /] hive -e 'select * from test.hive_test' > /data/test.txt
[root@Hadoop01 /] cat /data/test.txt

三、数据查询

查询都是类sql，排序重点了解一下

• order by：全局排序，排序结果是一个文件，且数据也会排序
• sort by：每个MapReduce内部排序，按照每个reduce产生的文件里的记录数排序，但数据不会排序
• distribute by：分区排序，对每个reduce产生的文件里的数据进行排序
• cluster by：当sort by与distribute by的字段相同时，可以使用cluster by，但只能升序不能降序

示例

MapReduce内部排序

hive> set mapreduce.job.reduces=3; #设置MapReduce任务的个数
hive> show mapreduce.job.reduces; #显示MapReduce任务的个数
hive> select * from st sort by id; #直接查询看不到结果，因为他是三个reduce，会产生三个文件
hive> insert overwrite local directory '/data/st_sortby' select * from st sort by id desc; #将结果导入本地路径，查看文件

[root@Hadoop01 /] cd /data/st_sortby
[root@Hadoop01 /] ll
[root@Hadoop01 /] cat 000000_0
[root@Hadoop01 /] cat 000001_0
[root@Hadoop01 /] cat 000002_0

MapReduce内部排序+分区排序

hive> insert overwrite local directory '/data/st_distributeby' select * from st distribute by id sort by id desc; #按mapreduce内部排序，且按照每个文件内的id降序

[root@Hadoop01 /] cd /data/st_distributeby
[root@Hadoop01 /] ll
[root@Hadoop01 /] cat 000000_0
[root@Hadoop01 /] cat 000001_0
[root@Hadoop01 /] cat 000002_0

cluster by升序

hive> insert overwrite local directory '/data/st_distributeby' select * from st cluster by id; #按mapreduce内部排序，且按照每个文件内的id升序

[root@Hadoop01 /] cd /data/st_distributeby
[root@Hadoop01 /] ll
[root@Hadoop01 /] cat 000000_0
[root@Hadoop01 /] cat 000001_0
[root@Hadoop01 /] cat 000002_0

四、数据删除

全量删除

• truncate table table_name：清空表
• drop table tabl_name：删除表

部分删除

• 清空表中的部分文件
  hadoop fs -rm 文件
• 将查询结果覆盖添加到表中
  insert overwrite table table_name select column1,column2,… from table_name where …
• 将查询结果覆盖添加到分区表的对应分区中
  insert overwrite table table_name partition(partition_name=指定分区) select column1,column2,… from table_name where …
• 删除指定分区
  alter table table_name drop partition(partition_name=指定分区)