1.环境配置
CentOS6.5
hadoop2.2
jdk1.7.0
sqoop1.4.4
zookeeper3.4.5
Mysql 14.14
2.在mysql上创建表
先按照需求在mysql上创建表
CREATE DATABASE demo;
USE demo;
DROP TABLE IF EXISTS task2;
CREATE TABLE task2(
month TINYINT,
area VARCHAR(30),
amount BIGINT unsigned
)DEFAULT CHARSET=utf8;
hive中创建的表字段要与mysql表字段一致。
3.在hive上创建对应的表( CTAS)并导入查询结果
USE test;
DROP TABLE IF EXISTS task2;
CREATE EXTERNAL TABLE task2(
month TINYINT,
area STRING,
amount BIGINT
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE LOCATION '/user/test/hive2mysql/2';
INSERT OVERWRITE TABLE task2
SELECT month(regexp_replace(REPLYTIME,'/','-')) AS month, AREACODE,COUNT(*) AS CNT FROM
demo666 WHERE SUBSTR(transactionid,1,2)='JX'
GROUP BY month(regexp_replace(REPLYTIME,'/','-')),
AREACODE ORDER BY month DESC;
可以学习CTAS,在创建表的时候直接将查询得到的信息导入到表中。
4.使用sqoop将hive导入mysql
sqoop export --connect jdbc:mysql://localhost:3306/demo --username root --password xxxx --table task2 --export-dir /user/test/hive2mysql/2 --input-fields-terminated-by '\t' -input-null-string '\\N' -input-null-non-string '\\N'
sqoop依赖zookeeper,在使用时需要配置ZOOKEEPER_HOME变量。
使用sqoop导出时会出现乱码,原因是mysql中配置文件问题。需要在my.cnf中加入以下字段
character_set_server=utf8
init_connect='SET NAMES utf8'