【sqoop】Linux环境下使用一个sh脚本将Mysql的表导入hive

Linux环境下使用一个sh脚本将Mysql的表导入hive

前提条件已安装好hadoop+hive+mysql+sqoop；并在mysql下创建了一张表并插入数据，在hive里创建了一张空表
--Mysql库建表并插入数据

CREATE TABLE emp(
    id INT NOT NULL,
    name VARCHAR(100),
    deg VARCHAR(100),
    salary BIGINT,
    dept VARCHAR(50)
);

insert into emp values(1201,'gopal','manager','50000','TP');
insert into emp values(1202,'manisha','Proof reader','50000','TP');
insert into emp values(1203,'khalil','php dev','30000','AC');
insert into emp values(1204,'prasanth','php dev','30000','AC');
insert into emp values(1205,'kranthi','admin','20000','TP');

--hive建表

CREATE TABLE emp(
    id INT,
    name STRING,
    deg STRING,
    salary BIGINT,
    dept STRING
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE;
;

新建一个sqoop_imp_emp.sh文件，脚本如下：

#!/bin/sh

#mysql
mysql_name=system123
mysql_passwd=123
mysql_conn=jdbc:mysql://192.168.1.120:3306/mtest

#hive_table_name
hive_table=emp;

#tmp_dir
target_dir=/user/hive/warehouse/import/htest/${hive_table}

#删除临时目录，支持二次运行
hadoop dfs -rm ${target_dir}

sqoop import                                \
--hive-import                               \
--hive-table htest.${hive_table}            \
--connect ${mysql_conn}                     \
--username ${mysql_name}                    \
--password ${mysql_passwd}                  \
--query "SELECT id
               ,name
               ,deg
               ,salary
               ,dept
           FROM emp
          WHERE 1=1
            AND \$CONDITIONS"               \
--target-dir "${target_dir}"                \
--hive-overwrite                            \
--null-string '\\N'                         \
--null-non-string '\\N'                     \
--hive-drop-import-delims                   \
-m 1

执行脚本

sh sqoop_imp_emp.sh

等执行成功后，查看hive对应的表数据

hive (htest)> select * from emp;
OK
emp.id  emp.name        emp.deg emp.salary      emp.dept
1201    gopal   manager 50000   TP
1202    manisha Proof reader    50000   TP
1203    khalil  php dev 30000   AC
1204    prasanth        php dev 30000   AC
1205    kranthi admin   20000   TP
Time taken: 1.505 seconds, Fetched: 8 row(s)