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