Hive----数据插入、导入、导出

向表中填充数据：

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

 

load data local inpath '/opt/module/datas/student.txt' into table student;  --hadoop fs -put

load data inpath '/datas/test.txt' into table test2; --hadoop fs -mv

 

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

load data local inpath '/opt/module/datas/test.txt' overwrite into table test; --覆盖原有的数据

load data local inpath '/opt/module/datas/class.txt' into table class_par partition(uyear='2022',umonth='11',uday='02');

 

 

create table c1(udate string,cost string,yue double)

partitioned by(name string)

row format delimited fields terminated by '\t';

 

多分区联合插入：

from tmp

insert into table c1 partition(name='Tomi')

select utime,cost,yue where name='Tomi'

insert into table c1 partition(name='Jony')

select utime,cost,yue where name='Jony'

insert into table c1 partition(name='Lisi')

select utime,cost,yue where name='Lisi';

 

多表联合插入：

create table c2(udate string,name string,cost string,yue double);

create table c3(udate string,name string,cost string,yue double);

create table c4(udate string,name string,cost string,yue double);

 

from tmp

insert into table c2

select utime,name,cost,yue where name='Tomi'

insert into table c3

select utime,name,cost,yue where name='Jony'

insert into table c4

select utime,name,cost,yue where name='Lisi';

 

 

数据的导出：

insert方式：

insert overwrite  local directory '/opt/module/datas/info' row format delimited fields terminated by '\t'

collection items terminated by ','

select children,friends from test;

 

 

export table tmp to '/output';

create table tmp2 like tmp;

import table tmp2 from '/output';

 

导入：

①load 方式：

导入本地数据

导入集群数据

追加方式：load data （local） inpath '/opt/module/datas/student.txt' into table student;

覆盖方式：load data local inpath '/opt/module/datas/student.txt' （overwrite） into table student;

指定分区

 

②insert方式：

追加方式： insert into table student [(字段1，字段2)] values(1,"male"),(2,"female");

覆盖方式： insert overwrite table student values(1,"male"),(2,"female");

insert into table stud

select 1,'张三','1','A' union all

select 2,'李四','2','C' union all

select 3,'王五','1','B' union all

select 4,'黄六','3','D' union all

select 5,'朱八','2','C'

 

③建表时使用as关键字从别的表中查询某些列：create table …… as ……

④建表时通过location关键字指定数据路径：create table …… location ……

⑤import方式（import的数据源必须是export导出的结果目录）：

import table student2 partition(month='201709') from  '/user/hive/warehouse/export/student';

 

⑥sqoop工具：

 

 

导出：

 

①insert方式：

格式化后导出

 

②hadoop命令方式：

hive的命令行

dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;

hadoop的shell命令

hadoop fs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;

③hive交互命令：

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

 

④export方式：

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

⑤sqoop方式