sqoop学习，这一篇文章就够了

发表于

这是我参与8月更文挑战的第6天，活动详情查看：8月更文挑战

一、环境信息

Sqoop version: 1.4.7-cdh6.3.2

二、Sqoop导入

1. 目的

该import工具将单个表从 关系型数据库导入到 HDFS。表中的每一行在 HDFS 中都表示为单独的记录。记录可以存储为文本文件（每行一条记录），或以二进制表示形式存储为 Avro 或 SequenceFiles。

2. 语法

I. 链接数据库带服务器

官方文档解释：Sqoop 旨在将表从数据库导入 HDFS。为此，您必须指定一个描述如何连接到数据库的连接字符串。简单来说，我们必须制定一个源表，也就是关系型数据库的数据库连接的URL

1
2
3
4
5
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
import: 导入标记，证明后续参数全部都是从关系型数据库导入到大数据平台的参数
–connect import的参数，表明我们源数据所在的数据库连接
–username import的参数，表明我们数据库的用户名
–password import的参数，数据库密码
–table import的参数，元数据表名称

官方警告：Sqoop 将读取密码文件的全部内容并将其用作密码。这将包括任何尾随空白字符，例如大多数文本编辑器默认添加的换行符。您需要确保您的密码文件只包含属于您的密码的字符。

Sqoop 自动支持多种数据库，包括 MySQL。以 开头的连接字符串jdbc:mysql://在 Sqoop 中自动处理。（“支持的数据库”部分提供了具有内置支持的完整数据库列表。对于某些数据库，您可能需要自己安装 JDBC 驱动程序。）

您可以将 Sqoop 与任何其他符合 JDBC 的数据库一起使用。首先，为要导入的数据库类型下载适当的 JDBC 驱动程序，并将 .jar 文件安装在$SQOOP_HOME/lib客户端计算机上的目录中。（/usr/lib/sqoop/lib如果您是从 RPM 或 Debian 软件包安装的，则会出现这种情况。）每个驱动程序.jar文件还有一个特定的驱动程序类，用于定义驱动程序的入口点。例如，MySQL 的 Connector/J 库的驱动程序类为com.mysql.jdbc.Driver. 请参阅特定于数据库供应商的文档以确定主要驱动程序类。此类必须作为 Sqoop 的参数提供--driver。

Sqoop 通常以表为中心的方式导入数据。使用 --table参数选择要导入的表。例如，--table employees。此参数还可以标识数据库中的一个VIEW或其他类似表的实体。

II. 基本参数
–append 将数据附加到 HDFS 中的现有数据集
–as-avrodatafile 将数据导入 Avro 数据文件
–as-sequencefile 将数据导入到 SequenceFiles
–as-textfile 以纯文本形式导入数据（默认）
–as-parquetfile 将数据导入 Parquet 文件
–boundary-query 用于创建拆分的边界查询
–columns <col,col,col…> 要从表中导入的列
–delete-target-dir 删除导入目标目录（如果存在）
–direct 如果数据库存在，则使用直接连接器
–fetch-size 一次从数据库读取的条目数。
–inline-lob-limit 设置内联 LOB 的最大大小
-m,–num-mappers 使用n个map任务并行导入
-e,–query 导入结果*statement*。
–split-by 用于拆分工作单元的表列。不能与--autoreset-to-one-mapper选项一起使用 。
–split-limit 每个拆分大小的上限。这仅适用于整数和日期列。对于日期或时间戳字段，它以秒为单位计算。
–autoreset-to-one-mapper 如果表没有主键且未提供拆分列，则导入应使用一个映射器。不能与--split-by <col>选项一起使用 。
–table 表名称
–target-dir HDFS 目标目录
–temporary-rootdir 导入期间创建的临时文件的 HDFS 目录（覆盖默认的“_sqoop”）
–warehouse-dir 表目标的 HDFS 父级
–where 导入期间使用的 WHERE 子句
-z,–compress 启用压缩
–compression-codec 使用 Hadoop 编解码器（默认 gzip）
–null-string 要为字符串列的空值写入的字符串
–null-non-string 要为非字符串列的空值写入的字符串
–fields-terminated-by 设置字段分隔符
–lines-terminated-by 设置行尾字符
–mysql-delimiters 使用 MySQL 的默认分隔符集：fields: , lines: \n escaped-by: \ optional-enclosed-by:'

该--mysql-delimiters参数是一个速记参数，它使用程序的默认分隔符mysqldump。如果将mysqldump分隔符与直接模式导入（使用--direct）结合使用，则可以实现非常快的导入。

三、基本使用

1. mysql导入到hdfs

sudo -u hdfs 命令

I. 全表导入

1
2
3
4
5
6
7
8
9
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

II. 自由格式导入

如果要并行导入查询的结果，那么每个 map 任务都需要执行查询的副本，结果由 Sqoop 推断的边界条件进行分区。您的查询必须包含$CONDITIONS 每个 Sqoop 进程将替换为唯一条件表达式的标记。您还必须选择带有 的拆分列--split-by。

Sqoop 从大多数数据库源并行导入数据。您可以指定（并行处理）的地图任务的数量使用通过执行进口-m或--num-mappers争论。这些参数中的每一个都采用一个整数值，该值对应于要采用的并行度。默认情况下，使用四个任务。某些数据库可能会通过将此值增加到 8 或 16 来提高性能。不要将并行度增加到大于 MapReduce 集群中可用的程度；任务将连续运行，并且可能会增加执行导入所需的时间。同样，不要将并行度增加到高于您的数据库可以合理支持的程度。将 100 个并发客户端连接到您的数据库可能会将数据库服务器上的负载增加到性能受到影响的程度。

在执行并行导入时，Sqoop 需要一个标准来分割工作负载。Sqoop 使用拆分列来拆分工作负载。默认情况下，Sqoop 将识别表中的主键列（如果存在）并将其用作拆分列。从数据库中检索拆分列的低值和高值，并且映射任务对总范围的大小均匀的组件进行操作。例如，如果您有一个表，其主键列的 id最小值为 0，最大值为 1000，并且 Sqoop 被指示使用 4 个任务，则 Sqoop 将运行四个进程，每个进程执行形式为 的 SQL 语句SELECT * FROM sometable WHERE id >= lo AND id < hi，(lo, hi)设置为到 (0, 250)、(250, 500)、(500, 750) 和 (750, 1001) 在不同的任务中。

如果主键的实际值未在其范围内均匀分布，则可能会导致任务不平衡。您应该使用--split-by参数明确选择不同的列。例如，--split-by employee_id。Sqoop 目前无法在多列索引上拆分。如果您的表没有索引列，或者有多列键，那么您还必须手动选择拆分列。

用户可以--num-mapers使用--split-limit选项覆盖。使用该--split-limit参数会限制创建的拆分部分的大小。如果创建的 split 的大小大于此参数中指定的大小，则将调整 split 的大小以适应此限制，并且 split 的数量将根据此变化。这会影响实际的 mapper 数量。如果根据提供的--num-mappers参数计算的拆分大小超过--split-limit参数，则实际映射器的数量将增加。如果参数中指定的值为--split-limit 0 或负数，则该参数将被完全忽略，并根据数量计算拆分大小映射器。

如果表没有定义主键--split-by <col> 且未提供，则导入将失败，除非使用--num-mappers 1选项或使用--autoreset-to-one-mapper选项将映射器的数量显式设置为 1 。该选项 --autoreset-to-one-mapper通常与 import-all-tables 工具一起使用，以自动处理模式中没有主键的表。

1
2
3
4
5
6
7
8
9
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query 'select id,name,sex from sqoop_test where id>2 and $CONDITIONS'

III. 条件导入

1
2
3
4
5
6
7
8
9
10
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--where "id<=2" \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

IV. 导入制定的列

1
2
3
4
5
6
7
8
9
10
11
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--where "id>=2" \
--columns name,sex \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

V. 事物隔离级别

默认情况下，Sqoop 使用映射器中的读提交事务隔离来导入数据。这在所有 ETL 工作流中可能不是理想的，并且可能需要减少隔离保证。该--relaxed-isolation选项可用于指示 Sqoop 使用读取未提交隔离级别。

该read-uncommitted隔离级别不支持所有数据库（例如Oracle），因此指定选项--relaxed-isolation 可能无法在所有数据库的支持。

VI. 增量导入

争论 描述
–check-column (col) 指定在确定要导入的行时要检查的列。（该列的类型不应为 CHAR/NCHAR/VARCHAR/VARNCHAR/LONGVARCHAR/LONGNVARCHAR）
–incremental (mode) 指定 Sqoop 如何确定哪些行是新行。mode includeappend和 的合法值lastmodified。
–last-value (value) 指定上次导入的检查列的最大值。

Sqoop 支持两种类型的增量导入：append和lastmodified. 您可以使用该–incremental参数来指定要执行的增量导入的类型。

每次导入都产生一个新文件：

1
2
3
4
5
6
7
8
9
10
11
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--incremental append \
--check-column id \
--last-value 5 \
--target-dir /user/company \
--num-mappers 1 \
--fields-terminated-by "\t"

每次将新数据或更新后的数据同原有数据进行合并：

1
2
3
4
5
6
7
8
9
10
11
12
shell复制代码sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--incremental lastmodified \
--check-column date \
--last-value "2021-07-26 11:53:50" \
--target-dir /user/company \
--num-mappers 1 \
--fields-terminated-by "\t" \
--merge-key id

您应该append在导入表时指定模式，在该表中不断添加新行并增加行 id 值。您使用 指定包含行 id 的列--check-column。Sqoop 导入检查列的值大于指定值的行--last-value。

Sqoop 支持的另一种表更新策略称为lastmodified 模式。当源表的行可能被更新时，您应该使用它，并且每次这样的更新都会将最后修改列的值设置为当前时间戳。--last-value导入检查列保存的时间戳比指定的时间戳更近的行。

在增量导入结束时，应--last-value为后续导入指定的值 将打印到屏幕上。在运行后续导入时，您应该--last-value以这种方式指定以确保仅导入新的或更新的数据。这是通过将增量导入创建为保存的作业来自动处理的，这是执行重复增量导入的首选机制。有关详细信息，请参阅本文档后面有关已保存作业的部分。

例如，当我们使用时间增量的时候：

1
2
3
shell复制代码--incremental lastmodified \
--check-column date_col \
--last-value "2021-08-02 17:17:23"

2. mysql导入到hive

I. 基本参数
--hive-home <dir> 覆盖 $HIVE_HOME
–hive-import 将表导入 Hive（如果没有设置，则使用 Hive 的默认分隔符。）
–hive-overwrite 覆盖 Hive 表中的现有数据。
–create-hive-table 如果设置，那么如果目标配置单元，作业将失败
–hive-table 设置导入到 Hive 时要使用的表名。
–hive-drop-import-delims 导入到 Hive 时，从字符串字段中 删除*\n*、\r和*\01*。
–hive-delims-replacement 导入到 Hive 时，将字符串字段中的*\n*、\r和\\01\ 替换为用户定义的字符串。
–hive-partition-key 要分区的 hive 字段的名称被分片
–hive-partition-value 在此作业中用作此导入到配置单元的分区键的字符串值。
–map-column-hive 为配置的列覆盖从 SQL 类型到 Hive 类型的默认映射。如果在此参数中指定逗号，请使用 URL 编码的键和值，例如，使用 DECIMAL(1%2C%201) 而不是 DECIMAL(1, 1)。

II. 入门演示

1
2
3
4
5
6
7
8
9
10
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "\t" \
--hive-overwrite \
--hive-table sqoop_test_hive

事实上，这里有两个步骤，sqoop在同步到hive的时候，事实上sqoop会将数据先导入到hdfs中，再从hdfs中转移到hive中去！

他会先将数据写入HDFS中的一个临时目录，然后，再复制到/user/hive/warehouse/sqoop_test_hive目录下！

III. 官方警告

官方：如果您有多个 Hive 安装，或者hive不在您的 .hive 中$PATH，请使用该 **--hive-home**选项来标识 Hive 安装目录。Sqoop$HIVE_HOME/bin/hive将从这里开始使用。

尽管 Hive 支持转义字符，但它不处理换行符的转义。此外，它不支持可能在封闭字符串中包含字段分隔符的封闭字符的概念。因此，建议您在使用 Hive 时选择明确的字段和记录终止分隔符，而不需要转义和封闭字符；这是由于 Hive 的输入解析能力的限制。如果您确实使用 --escaped-by, --enclosed-by, 或--optionally-enclosed-by在将数据导入 Hive 时，Sqoop 将打印一条警告消息。

如果您的数据库行包含的字符串字段中存在 Hive 的默认行分隔符（\n和\r字符）或列分隔符（\01字符），则 Hive 在使用 Sqoop 导入的数据时会出现问题。您可以使用该--hive-drop-import-delims选项在导入时删除这些字符以提供与 Hive 兼容的文本数据。或者，您可以使用该--hive-delims-replacement选项在导入时将这些字符替换为用户定义的字符串，以提供与 Hive 兼容的文本数据。仅当您使用 Hive 的默认分隔符时才应使用这些选项，如果指定了不同的分隔符，则不应使用这些选项。

Sqoop 默认将 NULL 值导入为 string null。然而，Hive 使用字符串\N来表示NULL值，因此处理NULL（如IS NULL）的谓词将无法正常工作。你应该追加参数--null-string和--null-non-string进口工作或情况 --input-null-string，并--input-null-non-string在出口工作的情况下，如果你要妥善保存NULL价值。因为 sqoop 在生成的代码中使用这些参数，所以您需要正确地将值转义\N为\\N：

例如：

1
shell复制代码sqoop import ... --null-string '\\N' --null-non-string '\\N'

默认情况下，Hive 中使用的表名与源表的表名相同。您可以使用该--hive-table 选项控制输出表名称。

Hive 可以将数据放入分区以获得更高效的查询性能。您可以通过指定--hive-partition-key和 --hive-partition-value参数告诉 Sqoop 作业将 Hive 的数据导入特定分区。分区值必须是字符串。有关分区的更多详细信息，请参阅 Hive 文档。

您可以使用--compress和 --compression-codec选项将压缩表导入 Hive 。压缩导入 Hive 的表的一个缺点是许多编解码器无法拆分以供并行映射任务处理。但是，lzop 编解码器确实支持拆分。使用此编解码器导入表时，Sqoop 将自动索引文件以使用正确的 InputFormat 拆分和配置新的 Hive 表。此功能当前要求使用 lzop 编解码器压缩表的所有分区。

3. mysql导入到HBash

I. 基本参数

必须采用a的形式，逗号分隔的组合键列表属性！
--column-family <family> 设置导入的目标列族
–hbase-create-table 如果指定，则创建缺少的 HBase 表
–hbase-row-key
指定要用作行键的输入列, 如果输入表包含复合键，则
–hbase-table 指定要用作目标的 HBase 表而不是 HDFS
–hbase-bulkload 启用批量加载

如果输入表有复合键，则--hbase-row-key必须采用逗号分隔的复合键属性列表的形式。在这种情况下，HBase 行的行键将通过使用下划线作为分隔符组合复合键属性的值来生成。注意：只有--hbase-row-key在指定了参数的情况下，Sqoop 导入表才能使用复合键。

如果目标表和列族不存在，则 Sqoop 作业将退出并显示错误。您应该在运行导入之前创建目标表和列族。如果指定--hbase-create-table，Sqoop 将使用 HBase 配置中的默认参数创建目标表和列族（如果它们不存在）。

Sqoop 当前通过将每个字段转换为其字符串表示形式（就像您以文本模式导入到 HDFS 一样）将所有值序列化到 HBase，然后在目标单元格中插入此字符串的 UTF-8 字节。Sqoop 将跳过除行键列之外的所有列中包含空值的所有行。

为了减少 hbase 上的负载，Sqoop 可以进行批量加载而不是直接写入。要使用批量加载，请使用--hbase-bulkload.

争论 描述
–accumulo-table 指定要用作目标的 Accumulo 表而不是 HDFS
–accumulo-column-family 设置导入的目标列族
–accumulo-create-table 如果指定，则创建缺少的 Accumulo 表
–accumulo-row-key
指定要用作行键的输入列
–accumulo-visibility （可选）指定一个可见性标记以应用于插入到 Accumulo 中的所有行。默认为空字符串。
–accumulo-batch-size （可选）设置 Accumulo 的写入缓冲区的大小（以字节为单位）。默认为 4MB。
–accumulo-max-latency （可选）设置 Accumulo 批处理写入器的最大延迟（以毫秒为单位）。默认值为 0。
–accumulo-zookeepers host:port Accumulo 实例使用的 Zookeeper 服务器的逗号分隔列表
–accumulo-instance 目标 Accumulo 实例的名称
–accumulo-user 要导入的 Accumulo 用户的名称
–accumulo-password Accumulo 用户的密码

II. 入门演示

实例项目:

hbase创建项目：

hbase shell

1
shell复制代码create 'sqoop_test_hbase','info'
1
2
3
4
5
6
7
8
9
10
11
shell复制代码bin/sqoop import \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--num-mappers 1 \
--column-family "info" \
--hbase-create-table \
--hbase-row-key "id" \
--hbase-table "sqoop_test_hbase" \
--split-by id

四、导出

即从大数据群导入到非大数据集群，不支持HBash导入到Mysql;

》该export工具将一组文件从 HDFS 导出回 RDBMS。目标表必须已存在于数据库中。根据用户指定的分隔符读取输入文件并将其解析为一组记录。

》默认操作是将这些转换为一组INSERT 将记录注入数据库的语句。在“更新模式”下，Sqoop 将生成UPDATE替换数据库中现有记录的语句，而在“调用模式”下，Sqoop 将对每条记录进行存储过程调用。

1. 语法格式

1
2
shell复制代码$ sqoop export (generic-args) (export-args)
$ sqoop-export (generic-args) (export-args)

2. 常见参数

key 描述
–connect 指定 JDBC 连接字符串
–connection-manager 指定要使用的连接管理器类
–driver 手动指定要使用的 JDBC 驱动程序类
–hadoop-mapred-home 覆盖 $HADOOP_MAPRED_HOME
–help 打印使用说明
–password-file 为包含认证密码的文件设置路径
-P 从控制台读取密码
–password 设置认证密码
–username 设置认证用户名
–verbose 工作时打印更多信息
–connection-param-file 提供连接参数的可选属性文件
–relaxed-isolation 将连接事务隔离设置为映射器未提交的读取。

出口控制参数：

争论 描述
–columns <col,col,col…> 要导出到表的列
–direct 使用直接导出快速路径
–export-dir 导出的 HDFS 源路径
-m,–num-mappers 使用n个map任务并行导出
–table 要填充的表
–call 调用的存储过程
–update-key 用于更新的锚列。如果有多于一列，请使用逗号分隔的列列表。
–update-mode 指定在数据库中发现具有不匹配键的新行时如何执行更新。法律值mode包括 updateonly（默认）和 allowinsert
–input-null-string 对于字符串列要解释为 null 的字符串
–input-null-non-string 对于非字符串列要解释为 null 的字符串
–staging-table 数据在插入目标表之前将在其中暂存的表。
–clear-staging-table 表示可以删除暂存表中存在的任何数据。
–batch 使用批处理模式进行底层语句执行。

该--export-dir参数和一个--table或者--call是必需的。它们指定要在数据库中填充的表（或要调用的存储过程），以及 HDFS 中包含源数据的目录。

默认情况下，选择表中的所有列进行导出。您可以选择列的子集并使用--columns参数控制它们的顺序 。这应该包括要导出的列的逗号分隔列表。例如：--columns "col1,col2,col3"。请注意，未包含在--columns参数中的列需要具有定义的默认值或允许NULL值。否则您的数据库将拒绝导入的数据，这反过来会使 Sqoop 作业失败。

3. hive导出到mysql

1
2
3
4
5
6
7
8
shell复制代码bin/sqoop export \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--num-mappers 1 \
--export-dir /user/hive/warehouse/sqoop_test_hive \
--input-fields-terminated-by "\t"

4. hdfs导出到mysql

1
shell复制代码vim test.txt
1
2
3
tex复制代码1,张三,男,2021-08-03 15:05:51
2,李四,女,2021-08-02 15:05:51
3,赵六,男,2021-08-01 15:05:51
1
2
3
4
5
6
7
8
shell复制代码sqoop export \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--num-mappers 1 \
--export-dir /user/test.txt \
--input-fields-terminated-by ","

5. 更新导出，只更新已经存在的数据

1
shell复制代码vim test1.txt
1
2
3
4
shell复制代码1,张三,未知,2021-08-03 15:05:51
2,李四,女,2021-08-02 15:05:51
3,赵六,男,2021-08-01 15:05:51
4,王五,男,2021-07-01 15:05:51
1
2
3
4
5
6
7
8
9
10
shell复制代码sqoop export \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--num-mappers 1 \
--export-dir /user/test1.txt \
--input-fields-terminated-by "," \
--update-key id \
--update-mode updateonly

updateonly模式:只会更新已经存在的数据，不会执行insert增加新的数据

6. 增量导出

1
shell复制代码vim test3.txt
1
2
3
4
tex复制代码1,张三,女,2021-08-03 15:05:51
2,李四,女,2021-08-02 15:05:51
3,赵六,男,2021-08-01 15:05:51
4,王五,男,2021-07-01 15:05:51
1
shell复制代码sudo -u hdfs hdfs dfs -put ./test3.txt /user/
1
2
3
4
5
6
7
8
9
10
shell复制代码sqoop export \
--connect jdbc:mysql://10.0.10.118:3306/sqoop_test \
--username root \
--password 123456 \
--table sqoop_test \
--num-mappers 1 \
--export-dir /user/test3.txt \
--input-fields-terminated-by "," \
--update-key id \
--update-mode allowinsert

allowinsert模式: 更新已经存在的数据，添加不存在的数据

五、脚本导出（导入同理）

1
shell复制代码vim sqp_export.opt

脚本信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
shell复制代码export
--connect
jdbc:mysql://10.0.10.118:3306/sqoop_test
--username
root
--password
123456
--table
sqoop_test
--num-mappers
1
--export-dir
/user/hive/warehouse/sqoop_test_hive
--input-fields-terminated-by
"\t"

执行命令：

1
shell复制代码sudo -u hdfs sqoop --options-file ./sqp_export.opt

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%