1.Mysql配置
service mysqld start
mysql -uroot
create user 'hive' identified by 'hive';
create database tpin;
grant all privileges on *.* to 'hive'@'localhost' identified by 'hive';
grant all privileges on tpin.* to 'hive'@'%' identified by 'hive';
grant all privileges on tpin.* to 'hive'@'cloud-03' identified by 'hive';
flush privileges;
mysql -uhive -p
2.Hive配置：修改hive-site.xml文件
:%s#${system:java.io.tmpdir}/${system:user.name}#/opt/hive/tmp/hive#g
:%s#${system:java.io.tmpdir}#/opt/hive/tmp/hive#g
将javax.jdo.option.ConnectionURL设置为
jdbc:mysql://cloud-03:3306/tpin
将javax.jdo.option.ConnectionPassword设置为
hive
将javax.jdo.option.ConnectionUserName设置为
hive
将javax.jdo.option.ConnectionDriverName设置为
com.mysql.jdbc.Driver
3.Hive建表
CREATE TABLE nsr_tzf(zjhm string,nsrdzdah string,tzbl double,tzje double,tzfxz_id int) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "\\"", "escapeChar" = "\\\\") STORED AS TEXTFILE;
CREATE TABLE tpin_vertex_wwd(
id BIGINT,
type INT,
sbh STRING,
ishuman Boolean,
community_id BIGINT
);
4.载入csv文件
LOAD DATA LOCAL INPATH '/root/tpinDataCsv/nsr_tzf.csv' INTO TABLE nsr_tzf;
5.导出文本文件
INSERT OVERWRITE LOCAL DIRECTORY 'tpinDataCsv/tpin_vertex_wwd' SELECT * FROM tpin_vertex_wwd;
6.导入文本文件
LOAD DATA LOCAL INPATH 'tpinDataHive/tpin_vertex_wwd/00000*_0' INTO TABLE tpin_vertex_wwd;
7、sqoop导出到Oracle
sqoop export --connect jdbc:oracle:thin:@202.117.16.188:1521:tpin --username myTpin --password myTpin --table TPIN_VERTEX_WWD --export-dir /user/hive/warehouse/tpin_vertex_wwd --input-fields-terminated-by '\\001' --input-null-string '\\\\N' --input-null-non-string '\\\\N'
8.从sqoop Oracle导入hive表（hive表不必事先建）
sqoop import --hive-import --connect jdbc:oracle:thin:@202.117.16.188:1521:tpin --username myTpin --password myTpin --verbose --hive-database default --m 1 --table TPIN_2015_FP
