子夜枯灯

使用sqoop实现关系型数据库、HDFS、Hive之间数据的导入导出

2021/03/30

以下文章来源于泪痕残 以下文章来源于HoldBelief 以下文章来源于杨鑫newlfe

正文

sqoop概述：sqoop为大数据协作框架之一，以hdfs为核心，实现hdfs与关系型数据库之间的导入导出。

以下操作均建立在已配置好Hadoop、sqoop、hive、mysql等环境，已创建好相应表的基础上

1.mysql->hdfs

sqoop import \
--connect  jdbc:mysql://主机名:端口号/db_name\
--username root \
--password 123456 \
--table  table_name \
--num-mappers 1 \
--target-dir  /sqoop \
--delete-target-dir \
--direct \
--fields-terminated-by '\t'

参数解释：
num-mappers   指定map的个数
target-dir    指定目标路径
delete-target-dir     提前删除指定目录
fields-terminated-by '\t'    指定分隔符

增量导入

append 追加增量
sqoop import \
--connect  jdbc:mysql://主机名:端口号/db_name\
--username root \
--password 123456 \
--table  table_name \
--num-mappers 1 \
--target-dir  /sqoop \
--direct \
--fields-terminated-by '\t' \
--check-column id \
--incremental  append \
--last-value 1

2.hdfs->mysql

sqoop export \
--connect  jdbc:mysql://主机名:端口号/db_name\
--username root \
--password 123456 \
--table  table_name\
--num-mappers 1 \
--export-dir    /hdfs_mysql \
--input-fields-terminated-by '\t'

参数解释：
num-mappers   指定map的个数
export-dir    指定hdfs路径
input-fields-terminated-by '\t'    指定分隔符

3.mysql->hive

sqoop import \
--connect  jdbc:mysql://主机名:端口号/db_name\
--username root \
--password 123456 \
--table  table_name \
--num-mappers 1 \
--hive-import \
--delete-target-dir \
--hive-database  hive数据库名\
--hive-table  hive中对应的table_name \
--direct \
--fields-terminated-by '\t'

参数解释：
num-mappers   指定map的个数
delete-target-dir  提前删除指定目录
hive-database    hive数据库名
hive-table     hive数据库中表名
fields-terminated-by '\t'    指定分隔符

4.hive->mysql

sqoop export \
--connect  jdbc:mysql://主机名:端口号/db_name\
--username root \
--password 123456 \
--table  table_name \
--num-mappers 1 \
--export-dir    /user/hive/warehouse/数据库名/表名 \
--input-fields-terminated-by '\t'

参数解释：
num-mappers   指定map的个数
export-dir    指定hive数据库对应的hdfs路径
input-fields-terminated-by '\t'    指定分隔符

5.hdfs->Oracle

sqoop export \
--connect  jdbc:oracle:thin:@主机名:端口号:ora11g \
--username root\
--password 123456 \
--table  LIVE_VIEWER_SHIP \
--num-mappers 1 \
--export-dir    /user/cxfgs/c3/output/liveRatings/2017/04/30/ \
--input-fields-terminated-by '|'
参数解释：
num-mappers   指定map的个数
export-dir    指定hive数据库对应的hdfs路径
input-fields-terminated-by '\t'    指定分隔符

6.oracle->hive

sqoop import \
--connect  jdbc:oracle:thin:@主机名:端口号:ora11g \
--username root \
--password 123456 \
--table  table_name \
--num-mappers 1 \
--hive-import \
--delete-target-dir \
--hive-database  hive数据库名\
--hive-table  hive中对应的table_name \
--direct \
--fields-terminated-by '\t'

参数解释：
num-mappers   指定map的个数
delete-target-dir  提前删除指定目录
hive-database    hive数据库名
hive-table     hive数据库中表名
fields-terminated-by '\t'    指定分隔符

7.导入的字段分隔符（record delimiters）和行分隔符（row delimiters）问题

Sqoop从oracle导入数据到hive，示例：

sqoop import --connect jdbc:oracle:thin:@oracle-host:port:orcl --username name--password passwd --hive-import -table tablename

如果不加其他参数，导入的数据默认的列分隔符是’\001’，默认的行分隔符是’\n’。

这样问题就来了，如果导入的数据中有’\n’，hive会认为一行已经结束，后面的数据被分割成下一行。这种情况下，导入之后hive中数据的行数就比原先数据库中的多，而且会出现数据不一致的情况。

Sqoop也指定了参数 –fields-terminated-by和 –lines-terminated-by来自定义行分隔符和列分隔符。

可是当你真的这么做时………o(╯□╰)o就会出现如下错误：

INFO hive.HiveImport: FAILED: SemanticException 1:381 LINES TERMINATED BY only supports newline '\n' right now. 也就是说虽然你通过 --lines-terminated-by 指定了其他的字符作为行分隔符，但是hive只支持'\n'作为行分隔符。

简单的解决办法就是加上参数–hive-drop-import-delims来把导入数据中包含的hive默认的分隔符去掉。

Sqoop有两个参数用来处理数据中存在\0x01 (\n\r)这两个字符，如下：

--hive-delims-replacement        Replace Hive record \0x01

                                and row delimiters (\n\r)

                                from imported string fields

                                with user-defined string

--hive-drop-import-delims       Drop Hive record \0x01 and

                                row delimiters (\n\r) from

                                imported string fields

这两个配置项都是用来处理字段分隔符（或称列分隔符）和行分隔符的。

注：英文中字段分隔符称作record delimiters，行分隔符称作row delimiters

默认情况下：record delimiters是\0x01，这是16进制的1，等同于8进制的\001，（\0x01和\001对应ASCII同一个字符，只是进制不一样）。这个字符称作隐藏字符，打印出来的视觉效果与空格相同，但是相比空格或者“\t”的好处是，\0x01是无法从键盘输入的，所以保证了安全性，避免了由于数据中本身带有空格或者“\t”带来的尴尬。

但是如果数据中本身带有\0x01或者本身带有\n\r怎么办？虽然这些字符无法通过键盘输入，但是可以通过程序写入，

比如Java代码如下：

public static void main(String[] args) {
    String s = "";
    byte b1[] = {0x02};
    byte b2[] = {0x01};
    String str1 = new String(b1);
    String str2 = new String(b2);

    s = "第一个字符是\0x02: " + str1
            + ",第二个字符是\0x01 : " + str2
            + "第三个字符是空格: " + "  "
            + ",\0x01和\0x02无法从键盘输入,所以作为分隔符更为安全";

    System.out.println(s);
} 如果record中本身就带有/0x01的时候就需要使用--hive-delims-replacement <arg>或者--hive-drop-import-delims两个配置项处理这些字符。（这两个配置项不能同时使用。）

–hive-delims-replacement ：是将record中的/0x01和/r/n替换成

–hive-drop-import-delims：将record中的/0x01和/r/n去掉

8.过滤空值以及特殊格式

在使用Sqoop的时候常看到在使用的时候在目的数据库中出现 NULL值，本来源表是空，而目的数据库中的表信息确是null

为了避免这种情况，我们可以使用：

–null-string ‘ ‘ –null-non-string ‘ ‘

来过滤空字符串以及空值。

除了这种方式，参数还可以是：

  • \b (backspace)
  • \n (newline)
  • \r (carriage return)
  • \t (tab)
  • \" (double-quote)

Post Directory