使用Sqoop将数据从Hive导入MySQL（一）
使用Sqoop将数据从Hive导入MySQL
首先查看csv数据类型
创建类似的hive表并导入数据
CREATE TABLE data (
province STRING,
code INT,
city STRING,
district STRING,
year INT,
model STRING,
company STRING,
brand STRING,
type STRING,
use_type STRING,
seat_count INT,
engine_no STRING,
displacement DOUBLE,
power DOUBLE,
fuel_type STRING,
total_weight DOUBLE,
kerb_mass DOUBLE,
max_mass DOUBLE,
tire_size STRING,
tire_count INT,
length DOUBLE,
width DOUBLE,
height DOUBLE,
note STRING,
extra_company STRING,
original_model STRING,
production_company STRING,
vehicle_type STRING,
register_date INT,
owner_sex STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
导入数据
LOAD DATA LOCAL INPATH '/opt/mg/cars.txt' INTO TABLE data;
查询下表数据
select * from data limit 10;
创建mysql相同的表
CREATE TABLE data (
province VARCHAR(255),
code INT,
city VARCHAR(255),
district VARCHAR(255),
year INT,
model VARCHAR(255),
company VARCHAR(255),
brand VARCHAR(255),
type VARCHAR(255),
use_type VARCHAR(255),
seat_count INT,
engine_no VARCHAR(255),
displacement FLOAT,
power FLOAT,
fuel_type VARCHAR(255),
total_weight FLOAT,
kerb_mass FLOAT,
max_mass FLOAT,
tire_size VARCHAR(255),
tire_count INT,
length FLOAT,
width FLOAT,
height FLOAT,
note VARCHAR(255),
extra_company VARCHAR(255),
original_model VARCHAR(255),
production_company VARCHAR(255),
vehicle_type VARCHAR(255),
register_date INT,
owner_sex VARCHAR(255)
);
Ps如果字段问题可以全写varchar不建议
CREATE TABLE data1 (
`date` VARCHAR(255),
hour VARCHAR(255),
type VARCHAR(255),
Dongsi VARCHAR(255),
Tiantan VARCHAR(255),
Guanyuan VARCHAR(255),
Wanshouxigong VARCHAR(255),
Aotizhongxin VARCHAR(255),
Nongzhanguan VARCHAR(255),
Wanliu VARCHAR(255),
Beibuxinqu VARCHAR(255),
Zhiwuyuan VARCHAR(255),
Fengtaihuayuan VARCHAR(255),
Yungang VARCHAR(255),
Gucheng VARCHAR(255),
Fangshan VARCHAR(255),
Daxing VARCHAR(255),
Yizhuang VARCHAR(255),
Tongzhou VARCHAR(255),
Shunyi VARCHAR(255),
Changping VARCHAR(255),
Mentougou VARCHAR(255),
Pinggu VARCHAR(255),
Huairou VARCHAR(255),
Miyun VARCHAR(255),
Yanqing VARCHAR(255),
Dingling VARCHAR(255),
Badaling VARCHAR(255),
Miyunshuiku VARCHAR(255),
Donggaocun VARCHAR(255),
Yongledian VARCHAR(255),
Yufa VARCHAR(255),
Liulihe VARCHAR(255),
Qianmen VARCHAR(255),
Yongdingmennan VARCHAR(255),
Xizhimennei VARCHAR(255),
Nansanhuan VARCHAR(255),
Dongsihuan VARCHAR(255)
);
执行sqoop命令从hive导入mysql
./sqoop export \
--connect jdbc:mysql://172.16.121.194:3306/test \
--username root \
--password 123456 \
--table data \
--export-dir /user/hive/warehouse/data \
--input-fields-terminated-by '\0001' \
--input-lines-terminated-by '\n' \
--null-string 'NULL' \
--null-non-string '\\N'
查看表数据
select * from data limit 10;
