Sqoop 1.4.6 User Guide：数据库导入与工具详解

需积分: 9 48 浏览量更新于2024-07-19 收藏 851KB PDF 举报

"sqoop1-1.4.6英文文档主要涵盖了Sqoop工具的使用，包括基本操作、命令别名、Hadoop安装控制、参数控制、选项文件传递、工具使用等多个方面。文档详细阐述了`sqoop-import`、`sqoop-import-all-tables`和`sqoop-import-mainframe`等子命令的用途、语法和示例，以及各种导入数据到Hadoop生态系统（如Hive、HBase、Accumulo）的方法，特别提到了增量导入、文件格式、大型对象处理和事务隔离控制等高级功能。" Sqoop是Apache Hadoop生态中的一个工具，用于在关系型数据库和Hadoop之间高效地转移数据。在1.4.6版本的文档中，它介绍了以下几个关键知识点： 1. 简介：这部分通常包含Sqoop的基本概念、目标和适用场景，帮助用户理解其在大数据处理中的角色。 2. 支持的版本：列出当前文档对应的支持的Hadoop和Sqoop版本，确保用户使用的是兼容的软件环境。 3. Sqoop版本：介绍不同版本的Sqoop特性，可能包括新功能、改进和已知问题。 4. 先决条件：列出使用Sqoop所需的软件和硬件配置，包括Hadoop集群、JDK版本等。 5. 基本使用：提供 Sqoop 命令行的基本用法，包括如何启动、连接数据库、导出数据等。 6. Sqoop工具： - 命令别名：解释如何使用命令别名简化复杂的命令输入。 - Hadoop安装控制：指导用户如何指定Hadoop安装路径，以便Sqoop与之交互。 - 参数控制：详述通用和特定的命令行参数，用于定制数据导入导出行为。 - 选项文件：说明如何通过文件传递大量参数，以避免命令行过长。 - 使用工具：涵盖 Sqoop 提供的各种工具的使用方法。 7. sqoop-import：这是最常用的子命令，用于将数据库中的数据导入到Hadoop。它涵盖： - 目的：解释该命令的作用。 - 语法：列出命令的基本结构和参数。 - 连接数据库服务器：介绍如何设置连接信息。 - 选择导入数据：说明如何指定要导入的表或查询。 - 自由形式查询导入：允许用户使用SQL查询来导入数据。 - 并行性控制：通过并行任务提高导入速度。 - 分布式缓存：管理在Hadoop作业中使用的文件。 - 导入过程控制：如事务隔离级别、类型映射的设定。 - 增量导入：描述如何只导入自上次导入以来变化的数据。 - 文件格式：支持的不同数据存储格式。 - 大型对象：处理如BLOB和CLOB等大对象。 - 导入到Hive：将数据直接导入到Hive表。 - 导入到HBase：利用HBase的列式存储特性。 - 导入到Accumulo：集成NoSQL数据库Accumulo。 - 额外的导入配置属性：提供更深入的配置选项。 8. sqoop-import-all-tables：用于自动导入数据库中所有表，同样包含其目的、语法和示例。 9. sqoop-import-mainframe：专门针对大型机数据导入，包括连接大型机、选择数据的细节。每个部分都提供了示例调用，方便用户实际操作时参考。通过这些内容，用户可以深入了解并熟练掌握 Sqoop 在大数据环境中的数据迁移操作。

A1,B1,C1

A2,B2,C2

...

You can select a subset of columns and control their ordering by using the --columns argument.

This should include a comma-delimited list of columns to import. For example: --columns

"name,employee_id,jobtitle".

You can control which rows are imported by adding a SQL WHERE clause to the import statement.

By default, Sqoop generates statements of the form SELECT <column list> FROM <table name>. You can

append a WHERE clause to this with the --where argument. For example: --where "id > 400". Only rows

where the id column has a value greater than 400 will be imported.

By default sqoop will use query select min(<split-by>), max(<split-by>) from <table name> to find out

boundaries for creating splits. In some cases this query is not the most optimal so you can

specify any arbitrary query returning two numeric columns using --boundary-query argument.

7.2.3. Free-form Query Imports

Sqoop can also import the result set of an arbitrary SQL query. Instead of using the --table, --

columns and --where arguments, you can specify a SQL statement with the --query argument.

When importing a free-form query, you must specify a destination directory with --target-dir.

If you want to import the results of a query in parallel, then each map task will need to execute

a copy of the query, with results partitioned by bounding conditions inferred by Sqoop. Your

query must include the token $CONDITIONS which each Sqoop process will replace with a unique

condition expression. You must also select a splitting column with --split-by.

For example:

$ sqoop import \

--query 'SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS' \

--split-by a.id --target-dir /user/foo/joinresults

Alternately, the query can be executed once and imported serially, by specifying a single map

task with -m 1:

$ sqoop import \

--query 'SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS' \

-m 1 --target-dir /user/foo/joinresults

Note

If you are issuing the query wrapped with double quotes ("), you will have

to use \$CONDITIONS instead of just $CONDITIONS to disallow your shell from

treating it as a shell variable. For example, a double quoted query may look

like: "SELECT * FROM x WHERE a='foo' AND \$CONDITIONS"

Note

The facility of using free-form query in the current version of Sqoop is

limited to simple queries where there are no ambiguous projections and no

OR conditions in the WHERE clause. Use of complex queries such as queries that

have sub-queries or joins leading to ambiguous projections can lead to

unexpected results.

7.2.4. Controlling Parallelism

Sqoop imports data in parallel from most database sources. You can specify the number of map

tasks (parallel processes) to use to perform the import by using the -m or --num-mappers argument.

Each of these arguments takes an integer value which corresponds to the degree of parallelism

to employ. By default, four tasks are used. Some databases may see improved performance by

increasing this value to 8 or 16. Do not increase the degree of parallelism greater than that

available within your MapReduce cluster; tasks will run serially and will likely increase the

amount of time required to perform the import. Likewise, do not increase the degree of

parallism higher than that which your database can reasonably support. Connecting 100

concurrent clients to your database may increase the load on the database server to a point

where performance suffers as a result.

When performing parallel imports, Sqoop needs a criterion by which it can split the workload.

Sqoop uses a splitting column to split the workload. By default, Sqoop will identify the primary

key column (if present) in a table and use it as the splitting column. The low and high values for

the splitting column are retrieved from the database, and the map tasks operate on evenly-sized

components of the total range. For example, if you had a table with a primary key column of id

whose minimum value was 0 and maximum value was 1000, and Sqoop was directed to use 4

tasks, Sqoop would run four processes which each execute SQL statements of the form SELECT *

FROM sometable WHERE id >= lo AND id < hi, with (lo, hi) set to (0, 250), (250, 500), (500, 750), and (750,

1001) in the different tasks.

If the actual values for the primary key are not uniformly distributed across its range, then this

can result in unbalanced tasks. You should explicitly choose a different column with the --split-by

argument. For example, --split-by employee_id. Sqoop cannot currently split on multi-column

indices. If your table has no index column, or has a multi-column key, then you must also

manually choose a splitting column.

If a table does not have a primary key defined and the --split-by <col> is not provided, then import

will fail unless the number of mappers is explicitly set to one with the --num-mappers 1 option or the

--autoreset-to-one-mapper option is used. The option --autoreset-to-one-mapper is typically used with the

import-all-tables tool to automatically handle tables without a primary key in a schema.

7.2.5. Controlling Distributed Cache

Sqoop will copy the jars in $SQOOP_HOME/lib folder to job cache every time when start a Sqoop

job. When launched by Oozie this is unnecessary since Oozie use its own Sqoop share lib which

keeps Sqoop dependencies in the distributed cache. Oozie will do the localization on each worker

node for the Sqoop dependencies only once during the first Sqoop job and reuse the jars on

worker node for subsquencial jobs. Using option --skip-dist-cache in Sqoop command when

launched by Oozie will skip the step which Sqoop copies its dependencies to job cache and save

massive I/O.

7.2.6. Controlling the Import Process

By default, the import process will use JDBC which provides a reasonable cross-vendor import

channel. Some databases can perform imports in a more high-performance fashion by using

database-specific data movement tools. For example, MySQL provides the mysqldump tool which can

export data from MySQL to other systems very quickly. By supplying the --direct argument, you

are specifying that Sqoop should attempt the direct import channel. This channel may be higher

performance than using JDBC.

Details about use of direct mode with each specific RDBMS, installation requirements, available

options and limitations can be found in Section 25, “Notes for specific connectors”.

By default, Sqoop will import a table named foo to a directory named foo inside your home

directory in HDFS. For example, if your username is someuser, then the import tool will write to

/user/someuser/foo/(files). You can adjust the parent directory of the import with the --warehouse-dir

argument. For example:

$ sqoop import --connnect <connect-str> --table foo --warehouse-dir /shared \

...

This command would write to a set of files in the /shared/foo/ directory.

You can also explicitly choose the target directory, like so:

$ sqoop import --connnect <connect-str> --table foo --target-dir /dest \

...

This will import the files into the /dest directory. --target-dir is incompatible with --warehouse-dir.

When using direct mode, you can specify additional arguments which should be passed to the

underlying tool. If the argument -- is given on the command-line, then subsequent arguments

are sent directly to the underlying tool. For example, the following adjusts the character set

used by mysqldump:

$ sqoop import --connect jdbc:mysql://server.foo.com/db --table bar \

--direct -- --default-character-set=latin1

By default, imports go to a new target location. If the destination directory already exists in

HDFS, Sqoop will refuse to import and overwrite that directory’s contents. If you use the --append

argument, Sqoop will import data to a temporary directory and then rename the files into the

normal target directory in a manner that does not conflict with existing filenames in that

directory.

7.2.7. Controlling transaction isolation

By default, Sqoop uses the read committed transaction isolation in the mappers to import data.

This may not be the ideal in all ETL workflows and it may desired to reduce the isolation

guarantees. The --relaxed-isolation option can be used to instruct Sqoop to use read uncommitted

isolation level.

The read-uncommitted isolation level is not supported on all databases (for example, Oracle), so

specifying the option --relaxed-isolation may not be supported on all databases.

7.2.8. Controlling type mapping

Sqoop is preconfigured to map most SQL types to appropriate Java or Hive representatives.

However the default mapping might not be suitable for everyone and might be overridden by --

map-column-java (for changing mapping to Java) or --map-column-hive (for changing Hive mapping).

Table 4. Parameters for overriding mapping

Argument Description

--map-column-java <mapping>

Override mapping from SQL to Java type for configured columns.

--map-column-hive <mapping>

Override mapping from SQL to Hive type for configured columns.

Sqoop is expecting comma separated list of mapping in form <name of column>=<new type>.

For example:

$ sqoop import ... --map-column-java id=String,value=Integer

Sqoop will rise exception in case that some configured mapping will not be used.

7.2.9. Incremental Imports

Sqoop provides an incremental import mode which can be used to retrieve only rows newer than

some previously-imported set of rows.

The following arguments control incremental imports:

Table 5. Incremental import arguments:

Argument Description

--check-

column (col)

Specifies the column to be examined when determining which rows to import. (the

column should not be of type CHAR/NCHAR/VARCHAR/VARNCHAR/

LONGVARCHAR/LONGNVARCHAR)

--incremental

(mode)

Specifies how Sqoop determines which rows are new. Legal values for mode include

append and lastmodified.

--last-value

(value)

Specifies the maximum value of the check column from the previous import.

Sqoop supports two types of incremental imports: append and lastmodified. You can use the --

incremental argument to specify the type of incremental import to perform.

You should specify append mode when importing a table where new rows are continually being

added with increasing row id values. You specify the column containing the row’s id with --check-

column. Sqoop imports rows where the check column has a value greater than the one specified

with --last-value.

An alternate table update strategy supported by Sqoop is called lastmodified mode. You should use

this when rows of the source table may be updated, and each such update will set the value of a

last-modified column to the current timestamp. Rows where the check column holds a

timestamp more recent than the timestamp specified with --last-value are imported.

At the end of an incremental import, the value which should be specified as --last-value for a

subsequent import is printed to the screen. When running a subsequent import, you should

specify --last-value in this way to ensure you import only the new or updated data. This is handled

automatically by creating an incremental import as a saved job, which is the preferred

mechanism for performing a recurring incremental import. See the section on saved jobs later in

this document for more information.

7.2.10. File Formats

You can import data in one of two file formats: delimited text or SequenceFiles.

Delimited text is the default import format. You can also specify it explicitly by using the --as-

textfile argument. This argument will write string-based representations of each record to the

output files, with delimiter characters between individual columns and rows. These delimiters

may be commas, tabs, or other characters. (The delimiters can be selected; see "Output line

formatting arguments.") The following is the results of an example text-based import:

1,here is a message,2010-05-01

2,happy new year!,2010-01-01

3,another message,2009-11-12

Delimited text is appropriate for most non-binary data types. It also readily supports further

manipulation by other tools, such as Hive.

SequenceFiles are a binary format that store individual records in custom record-specific data

types. These data types are manifested as Java classes. Sqoop will automatically generate these

data types for you. This format supports exact storage of all data in binary representations, and

is appropriate for storing binary data (for example, VARBINARY columns), or data that will be

principly manipulated by custom MapReduce programs (reading from SequenceFiles is higher-

performance than reading from text files, as records do not need to be parsed).

Avro data files are a compact, efficient binary format that provides interoperability with

applications written in other programming languages. Avro also supports versioning, so that

when, e.g., columns are added or removed from a table, previously imported data files can be

processed along with new ones.

By default, data is not compressed. You can compress your data by using the deflate (gzip)

algorithm with the -z or --compress argument, or specify any Hadoop compression codec using the -

-compression-codec argument. This applies to SequenceFile, text, and Avro files.

7.2.11. Large Objects

Sqoop handles large objects (BLOB and CLOB columns) in particular ways. If this data is truly large,

then these columns should not be fully materialized in memory for manipulation, as most

columns are. Instead, their data is handled in a streaming fashion. Large objects can be stored

inline with the rest of the data, in which case they are fully materialized in memory on every

access, or they can be stored in a secondary storage file linked to the primary data storage. By

default, large objects less than 16 MB in size are stored inline with the rest of the data. At a

larger size, they are stored in files in the _lobs subdirectory of the import target directory. These

files are stored in a separate format optimized for large record storage, which can accomodate

records of up to 2^63 bytes each. The size at which lobs spill into separate files is controlled by

the --inline-lob-limit argument, which takes a parameter specifying the largest lob size to keep

inline, in bytes. If you set the inline LOB limit to 0, all large objects will be placed in external

storage.

Table 6. Output line formatting arguments:

Argument Description

--enclosed-by <char>

Sets a required field enclosing character

--escaped-by <char>

Sets the escape character

--fields-terminated-by

<char>

Sets the field separator character

--lines-terminated-by

<char>

Sets the end-of-line character

--mysql-delimiters

Uses MySQL’s default delimiter set: fields: , lines: \n escaped-by: \

optionally-enclosed-by: '

--optionally-enclosed-by

<char>

Sets a field enclosing character

When importing to delimited files, the choice of delimiter is important. Delimiters which appear

inside string-based fields may cause ambiguous parsing of the imported data by subsequent

analysis passes. For example, the string "Hello, pleased to meet you" should not be imported with the

end-of-field delimiter set to a comma.

Delimiters may be specified as:

a character (--fields-terminated-by X)

an escape character (--fields-terminated-by \t). Supported escape characters are:

\b (backspace)

\n (newline)

\r (carriage return)

\t (tab)

\" (double-quote)

\\' (single-quote)

\\ (backslash)

\0 (NUL) - This will insert NUL characters between fields or lines, or will

disable enclosing/escaping if used for one of the --enclosed-by, --optionally-

enclosed-by, or --escaped-by arguments.

The octal representation of a UTF-8 character’s code point. This should be of the form

\0ooo, where ooo is the octal value. For example, --fields-terminated-by \001 would yield the

^A character.

The hexadecimal representation of a UTF-8 character’s code point. This should be of

the form \0xhhh, where hhh is the hex value. For example, --fields-terminated-by \0x10 would

yield the carriage return character.

The default delimiters are a comma (,) for fields, a newline (\n) for records, no quote character,

and no escape character. Note that this can lead to ambiguous/unparsible records if you import

database records containing commas or newlines in the field data. For unambiguous parsing,

both must be enabled. For example, via --mysql-delimiters.

If unambiguous delimiters cannot be presented, then use enclosing and escaping characters.

The combination of (optional) enclosing and escaping characters will allow unambiguous parsing

of lines. For example, suppose one column of a dataset contained the following values:

Some string, with a comma.

Another "string with quotes"

The following arguments would provide delimiters which can be unambiguously parsed:

$ sqoop import --fields-terminated-by , --escaped-by \\ --enclosed-by '\"' ...

(Note that to prevent the shell from mangling the enclosing character, we have enclosed that

argument itself in single-quotes.)

The result of the above arguments applied to the above dataset would be:

"Some string, with a comma.","1","2","3"...

"Another \"string with quotes\"","4","5","6"...

Here the imported strings are shown in the context of additional columns ("1","2","3", etc.) to

demonstrate the full effect of enclosing and escaping. The enclosing character is only strictly

剩余96页未读，继续阅读

partynew

粉丝: 4
资源: 3

Sqoop 1.4.6 User Guide：数据库导入与工具详解

sqoop官方文档学习笔记

sqoop1使用必备

Sqoop中文文档

sqoop2-1.99.7 documentation 英文文档

sqoop-1.4.6-cdh5.14.0

sqoop-1.4.6-cdh5.9.3

sqoop-1.4.6-hadoop-2.6最小资源包

sqoop-1.4.6-cdh5.13.2.tar

sqoop-1.4.6-cdh5.5.2.tar

sqoop-1.4.6

最新资源