当前位置：首页 > news >正文

导入数据到hive的几种方式

news 来源：原创 2024/9/21 20:24:37

–创建hive表
CREATE TABLE test(
id string,
name string)
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (
‘field.delim’=‘|’,
‘line.delim’=‘\n’,
‘serialization.format’=‘|’)
STORED AS INPUTFORMAT
‘org.apache.hadoop.mapred.TextInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’
LOCATION
‘hdfs://hacluster/user/hive/warehouse/test/test’
TBLPROPERTIES (
‘bucketing_version’=‘2’,
‘transient_lastDdlTime’=‘1715822045’)
;

–查询hive表
select * from test;
±---------±-----------+
| test.id | test.name |
±---------±-----------+
±---------±-----------+
No rows selected (0.623 seconds)

1.load导入
cat test.txt
1|one
2|two

load data local inpath ‘/tpdata/ypg/shell/work/work0625/test.txt’ into table test_db.test;
load data inpath ‘/user/hive/warehouse//test2/test.txt’ into table test_db.test;
select * from test_db.test;
±---------±-----------+
| test.id | test.name |
±---------±-----------+
| 1 | one |
| 2 | two |
±---------±-----------+

2.sql导入
insert导入
insert into test_db.test values(‘1’,‘one’);
insert into test_db.test values(‘2’,‘two’);
select * from test_db.test;
±---------±-----------+
| test.id | test.name |
±---------±-----------+
| 1 | one |
| 2 | two |
±---------±-----------+

insert into test_db.test
select ‘1’ as id, ‘one’ as name union all select ‘2’ as id, ‘two’ as name
select * from test_db.test;
±---------±-----------+
| test.id | test.name |
±---------±-----------+
| 1 | one |
| 2 | two |
±---------±-----------+

3.创建外部表映射hdfs文件
CREATE external TABLE test_copy(
id string,
name string)
ROW FORMAT SERDE
‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (
‘field.delim’=‘|’,
‘line.delim’=‘\n’,
‘serialization.format’=‘|’)
STORED AS INPUTFORMAT
‘org.apache.hadoop.mapred.TextInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’
LOCATION
‘hdfs://hacluster/user/hive/warehouse/test2’
TBLPROPERTIES (
‘bucketing_version’=‘2’,
‘transient_lastDdlTime’=‘1715822045’)
;
msck repair table test_db.test_copy;

insert into test_db.test
select * from test_db.test_copy;

select * from test_db.test;
±---------±-----------+
| test.id | test.name |
±---------±-----------+
| 1 | one |
| 2 | two |
±---------±-----------+

4.sqoop导入
sqoop import
-Dorg.apache.sqoop.splitter.allow_text_splitter=true
–connect jdbc:mysql://11.22.33.44:2883/test
–username dmltest
–password ‘test#123’
–table test
–fields-terminated-by ‘,’
–delete-target-dir
–hive-import
–hive-table test_db.test
-m 1