Sqoop is designed to import tables from a database into HDFS. To do so, you must specify a connect string that describes how to connect to the database. The connect string is similar to a URL, and is communicated to Sqoop with the --connect argument. This describes the server and database to connect to; it may also specify the port. For example:

$ sqoop import --connect jdbc:mysql://database.example.com/employees

This string will connect to a MySQL database named employees on the host database.example.com. It’s important that you do not use the URL localhost if you intend to use Sqoop with a distributed Hadoop cluster. The connect string you supply will be used on TaskTracker nodes throughout your MapReduce cluster; if you specify the literal name localhost, each node will connect to a different database (or more likely, no database at all). Instead, you should use the full hostname or IP address of the database host that can be seen by all your remote nodes.

You might need to authenticate against the database before you can access it. You can use the --username to supply a username to the database. Sqoop provides couple of different ways to supply a password, secure and non-secure, to the database which is detailed below.

Secure way of supplying password to the database. You should save the password in a file on the users home directory with 400 permissions and specify the path to that file using the --password-file argument, and is the preferred method of entering credentials. Sqoop will then read the password from the file and pass it to the MapReduce cluster using secure means with out exposing the password in the job configuration. The file containing the password can either be on the Local FS or HDFS. For example:

$ sqoop import --connect jdbc:mysql://database.example.com/employees \
    --username venkatesh --password-file ${user.home}/.password

Warning

Sqoop will read entire content of the password file and use it as a password. This will include any trailing white space characters such as new line characters that are added by default by most of the text editors. You need to make sure that your password file contains only characters that belongs to your password. On the command line you can use command echo with switch -n to store password without any trailing white space characters. For example to store password secret you would call echo -n "secret" > password.file.

Another way of supplying passwords is using the -P argument which will read a password from a console prompt.

Protecting password from preying eyes. Hadoop 2.6.0 provides an API to separate password storage from applications. This API is called the credential provided API and there is a new credential command line tool to manage passwords and their aliases. The passwords are stored with their aliases in a keystore that is password protected. The keystore password can be the provided to a password prompt on the command line, via an environment variable or defaulted to a software defined constant. Please check the Hadoop documentation on the usage of this facility.

Once the password is stored using the Credential Provider facility and the Hadoop configuration has been suitably updated, all applications can optionally use the alias in place of the actual password and at runtime resolve the alias for the password to use.

Since the keystore or similar technology used for storing the credential provider is shared across components, passwords for various applications, various database and other passwords can be securely stored in them and only the alias needs to be exposed in configuration files, protecting the password from being visible.

Sqoop has been enhanced to allow usage of this funcionality if it is available in the underlying Hadoop version being used. One new option has been introduced to provide the alias on the command line instead of the actual password (--password-alias). The argument value this option is the alias on the storage associated with the actual password. Example usage is as follows:

$ sqoop import --connect jdbc:mysql://database.example.com/employees \
    --username dbuser --password-alias mydb.password.alias

Similarly, if the command line option is not preferred, the alias can be saved in the file provided with --password-file option. Along with this, the Sqoop configuration parameter org.apache.sqoop.credentials.loader.class should be set to the classname that provides the alias resolution: org.apache.sqoop.util.password.CredentialProviderPasswordLoader

Example usage is as follows (assuming .password.alias has the alias for the real password) :

$ sqoop import --connect jdbc:mysql://database.example.com/employees \
    --username dbuser --password-file ${user.home}/.password-alias

	Warning
	The --password parameter is insecure, as other users may be able to read your password from the command-line arguments via the output of programs such as ps. The -P argument is the preferred method over using the --password argument. Credentials may still be transferred between nodes of the MapReduce cluster using insecure means. For example:

$ sqoop import --connect jdbc:mysql://database.example.com/employees \
    --username aaron --password 12345

Sqoop automatically supports several databases, including MySQL. Connect strings beginning with jdbc:mysql:// are handled automatically in Sqoop. (A full list of databases with built-in support is provided in the "Supported Databases" section. For some, you may need to install the JDBC driver yourself.)

You can use Sqoop with any other JDBC-compliant database. First, download the appropriate JDBC driver for the type of database you want to import, and install the .jar file in the $SQOOP_HOME/lib directory on your client machine. (This will be /usr/lib/sqoop/lib if you installed from an RPM or Debian package.) Each driver .jar file also has a specific driver class which defines the entry-point to the driver. For example, MySQL’s Connector/J library has a driver class of com.mysql.jdbc.Driver. Refer to your database vendor-specific documentation to determine the main driver class. This class must be provided as an argument to Sqoop with --driver.

For example, to connect to a SQLServer database, first download the driver from microsoft.com and install it in your Sqoop lib path.

Then run Sqoop. For example:

$ sqoop import --driver com.microsoft.jdbc.sqlserver.SQLServerDriver \
    --connect <connect-string> ...

When connecting to a database using JDBC, you can optionally specify extra JDBC parameters via a property file using the option --connection-param-file. The contents of this file are parsed as standard Java properties and passed into the driver while creating a connection.

	Note
	The parameters specified via the optional property file are only applicable to JDBC connections. Any fastpath connectors that use connections other than JDBC will ignore these parameters.

The --null-string and --null-non-string arguments are optional.\ If not specified, then the string "null" will be used.

Sqoop typically imports data in a table-centric fashion. Use the --table argument to select the table to import. For example, --table employees. This argument can also identify a VIEW or other table-like entity in a database.

By default, all columns within a table are selected for import. Imported data is written to HDFS in its "natural order;" that is, a table containing columns A, B, and C result in an import of data such as:

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.

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.

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.

User can override the --num-mapers by using --split-limit option. Using the --split-limit parameter places a limit on the size of the split section created. If the size of the split created is larger than the size specified in this parameter, then the splits would be resized to fit within this limit, and the number of splits will change according to that.This affects actual number of mappers. If size of a split calculated based on provided --num-mappers parameter exceeds --split-limit parameter then actual number of mappers will be increased.If the value specified in --split-limit parameter is 0 or negative, the parameter will be ignored altogether and the split size will be calculated according to the number of mappers.

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.

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.

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 26, “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 --connect <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 --connect <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.

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.

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).

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

Notice that specifying commas in --map-column-hive option, you should use URL encoded keys and values, for example, use DECIMAL(1%2C%201) instead of DECIMAL(1, 1).

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

When sqoop imports data from an enterprise store, table and column names may have characters that are not valid Java identifier characters or Avro/Parquet identifiers. To address this, sqoop translates these characters to _ as part of the schema creation. Any column name starting with an _ (underscore) character will be translated to have two underscore characters. For example _AVRO will be converted to __AVRO.

In the case of HCatalog imports, column names are converted to lower case when mapped to HCatalog columns. This may change in future.

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:

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.

You can import data in one of these file formats: delimited text, SequenceFiles, Avro and Parquet.

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.

$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES --as-parquetfile --parquet-configurator-implementation kite

$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES --as-parquetfile --parquet-configurator-implementation hadoop

$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES --as-parquetfile --compression-codec snappy \
--parquet-configurator-implementation hadoop --hs2-url "jdbc:hive2://hs2.foo.com:10000" --hs2-keytab "/path/to/keytab"

To enable the use of logical types in Sqoop’s avro schema generation, i.e. used both during avro and parquet imports, one has to use the sqoop.avro.logical_types.decimal.enable property. This is necessary if one wants to store values as decimals in the avro file format.

In case of a parquet import, one has to use the sqoop.parquet.logical_types.decimal.enable property.

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.

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:

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 necessary when delimiter characters appear in the imported text. The enclosing character can therefore be specified as optional:

$ sqoop import --optionally-enclosed-by '\"' (the rest as above)...

Which would result in the following import:

"Some string, with a comma.",1,2,3...
"Another \"string with quotes\"",4,5,6...

Note

Even though Hive supports escaping characters, it does not handle escaping of new-line character. Also, it does not support the notion of enclosing characters that may include field delimiters in the enclosed string. It is therefore recommended that you choose unambiguous field and record-terminating delimiters without the help of escaping and enclosing characters when working with Hive; this is due to limitations of Hive’s input parsing abilities.

The --mysql-delimiters argument is a shorthand argument which uses the default delimiters for the mysqldump program. If you use the mysqldump delimiters in conjunction with a direct-mode import (with --direct), very fast imports can be achieved.

While the choice of delimiters is most important for a text-mode import, it is still relevant if you import to SequenceFiles with --as-sequencefile. The generated class' toString() method will use the delimiters you specify, so subsequent formatting of the output data will rely on the delimiters you choose.

When Sqoop imports data to HDFS, it generates a Java class which can reinterpret the text files that it creates when doing a delimited-format import. The delimiters are chosen with arguments such as --fields-terminated-by; this controls both how the data is written to disk, and how the generated parse() method reinterprets this data. The delimiters used by the parse() method can be chosen independently of the output arguments, by using --input-fields-terminated-by, and so on. This is useful, for example, to generate classes which can parse records created with one set of delimiters, and emit the records to a different set of files using a separate set of delimiters.

Sqoop’s import tool’s main function is to upload your data into files in HDFS. If you have a Hive metastore associated with your HDFS cluster, Sqoop can also import the data into Hive by generating and executing a CREATE TABLE statement to define the data’s layout in Hive. Importing data into Hive is as simple as adding the --hive-import option to your Sqoop command line.

If the Hive table already exists, you can specify the --hive-overwrite option to indicate that existing table in hive must be replaced. After your data is imported into HDFS or this step is omitted, Sqoop will generate a Hive script containing a CREATE TABLE operation defining your columns using Hive’s types, and a LOAD DATA INPATH statement to move the data files into Hive’s warehouse directory.

The script can be executed in two ways:

	Note
	This function is incompatible with --as-avrodatafile and --as-sequencefile.

Even though Hive supports escaping characters, it does not handle escaping of new-line character. Also, it does not support the notion of enclosing characters that may include field delimiters in the enclosed string. It is therefore recommended that you choose unambiguous field and record-terminating delimiters without the help of escaping and enclosing characters when working with Hive; this is due to limitations of Hive’s input parsing abilities. If you do use --escaped-by, --enclosed-by, or --optionally-enclosed-by when importing data into Hive, Sqoop will print a warning message.

Hive will have problems using Sqoop-imported data if your database’s rows contain string fields that have Hive’s default row delimiters (\n and \r characters) or column delimiters (\01 characters) present in them. You can use the --hive-drop-import-delims option to drop those characters on import to give Hive-compatible text data. Alternatively, you can use the --hive-delims-replacement option to replace those characters with a user-defined string on import to give Hive-compatible text data. These options should only be used if you use Hive’s default delimiters and should not be used if different delimiters are specified.

Sqoop will pass the field and record delimiters through to Hive. If you do not set any delimiters and do use --hive-import, the field delimiter will be set to ^A and the record delimiter will be set to \n to be consistent with Hive’s defaults.

Sqoop will by default import NULL values as string null. Hive is however using string \N to denote NULL values and therefore predicates dealing with NULL (like IS NULL) will not work correctly. You should append parameters --null-string and --null-non-string in case of import job or --input-null-string and --input-null-non-string in case of an export job if you wish to properly preserve NULL values. Because sqoop is using those parameters in generated code, you need to properly escape value \N to \\N:

$ sqoop import  ... --null-string '\\N' --null-non-string '\\N'

The table name used in Hive is, by default, the same as that of the source table. You can control the output table name with the --hive-table option.

Hive can put data into partitions for more efficient query performance. You can tell a Sqoop job to import data for Hive into a particular partition by specifying the --hive-partition-key and --hive-partition-value arguments. The partition value must be a string. Please see the Hive documentation for more details on partitioning.

You can import compressed tables into Hive using the --compress and --compression-codec options. One downside to compressing tables imported into Hive is that many codecs cannot be split for processing by parallel map tasks. The lzop codec, however, does support splitting. When importing tables with this codec, Sqoop will automatically index the files for splitting and configuring a new Hive table with the correct InputFormat. This feature currently requires that all partitions of a table be compressed with the lzop codec.

$ sqoop import --hive-import --connect $CONN --table $TABLENAME --username $USER --password $PASS --external-table-dir /tmp/external_table_example

$ sqoop import --hive-import --create-hive-table --connect $CONN --table $TABLENAME --username $USER --password $PASS --external-table-dir /tmp/foobar_example --hive-table foobar

$ sqoop import -Dsqoop.avro.decimal_padding.enable=true -Dsqoop.parquet.logical_types.decimal.enable=true
            -Dsqoop.avro.logical_types.decimal.default.precision=38 -Dsqoop.avro.logical_types.decimal.default.scale=10
            --hive-import --connect $CONN --table $TABLENAME --username $USER --password $PASS --as-parquetfile

Sqoop supports additional import targets beyond HDFS and Hive. Sqoop can also import records into a table in HBase.

By specifying --hbase-table, you instruct Sqoop to import to a table in HBase rather than a directory in HDFS. Sqoop will import data to the table specified as the argument to --hbase-table. Each row of the input table will be transformed into an HBase Put operation to a row of the output table. The key for each row is taken from a column of the input. By default Sqoop will use the split-by column as the row key column. If that is not specified, it will try to identify the primary key column, if any, of the source table. You can manually specify the row key column with --hbase-row-key. Each output column will be placed in the same column family, which must be specified with --column-family.

	Note
	This function is incompatible with direct import (parameter --direct).

If the input table has composite key, the --hbase-row-key must be in the form of a comma-separated list of composite key attributes. In this case, the row key for HBase row will be generated by combining values of composite key attributes using underscore as a separator. NOTE: Sqoop import for a table with composite key will work only if parameter --hbase-row-key has been specified.

If the target table and column family do not exist, the Sqoop job will exit with an error. You should create the target table and column family before running an import. If you specify --hbase-create-table, Sqoop will create the target table and column family if they do not exist, using the default parameters from your HBase configuration.

Sqoop currently serializes all values to HBase by converting each field to its string representation (as if you were importing to HDFS in text mode), and then inserts the UTF-8 bytes of this string in the target cell. Sqoop will skip all rows containing null values in all columns except the row key column.

By default Sqoop will retain the previously imported value for columns updated to null during incremental imports. This can be changed to delete all previous versions of the column by using --hbase-null-incremental-mode delete.

To decrease the load on hbase, Sqoop can do bulk loading as opposed to direct writes. To use bulk loading, enable it using --hbase-bulkload.

Sqoop supports importing records into a table in Accumulo

By specifying --accumulo-table, you instruct Sqoop to import to a table in Accumulo rather than a directory in HDFS. Sqoop will import data to the table specified as the argument to --accumulo-table. Each row of the input table will be transformed into an Accumulo Mutation operation to a row of the output table. The key for each row is taken from a column of the input. By default Sqoop will use the split-by column as the row key column. If that is not specified, it will try to identify the primary key column, if any, of the source table. You can manually specify the row key column with --accumulo-row-key. Each output column will be placed in the same column family, which must be specified with --accumulo-column-family.

	Note
	This function is incompatible with direct import (parameter --direct), and cannot be used in the same operation as an HBase import.

If the target table does not exist, the Sqoop job will exit with an error, unless the --accumulo-create-table parameter is specified. Otherwise, you should create the target table before running an import.

Sqoop currently serializes all values to Accumulo by converting each field to its string representation (as if you were importing to HDFS in text mode), and then inserts the UTF-8 bytes of this string in the target cell.

By default, no visibility is applied to the resulting cells in Accumulo, so the data will be visible to any Accumulo user. Use the --accumulo-visibility parameter to specify a visibility token to apply to all rows in the import job.

For performance tuning, use the optional --accumulo-buffer-size\ and --accumulo-max-latency parameters. See Accumulo’s documentation for an explanation of the effects of these parameters.

In order to connect to an Accumulo instance, you must specify the location of a Zookeeper ensemble using the --accumulo-zookeepers parameter, the name of the Accumulo instance (--accumulo-instance), and the username and password to connect with (--accumulo-user and --accumulo-password respectively).

As mentioned earlier, a byproduct of importing a table to HDFS is a class which can manipulate the imported data. If the data is stored in SequenceFiles, this class will be used for the data’s serialization container. Therefore, you should use this class in your subsequent MapReduce processing of the data.

The class is typically named after the table; a table named foo will generate a class named foo. You may want to override this class name. For example, if your table is named EMPLOYEES, you may want to specify --class-name Employee instead. Similarly, you can specify just the package name with --package-name. The following import generates a class named com.foocorp.SomeTable:

$ sqoop import --connect <connect-str> --table SomeTable --package-name com.foocorp

The .java source file for your class will be written to the current working directory when you run sqoop. You can control the output directory with --outdir. For example, --outdir src/generated/.

The import process compiles the source into .class and .jar files; these are ordinarily stored under /tmp. You can select an alternate target directory with --bindir. For example, --bindir /scratch.

If you already have a compiled class that can be used to perform the import and want to suppress the code-generation aspect of the import process, you can use an existing jar and class by providing the --jar-file and --class-name options. For example:

$ sqoop import --table SomeTable --jar-file mydatatypes.jar \
    --class-name SomeTableType

This command will load the SomeTableType class out of mydatatypes.jar.

There are some additional properties which can be configured by modifying conf/sqoop-site.xml. Properties can be specified the same as in Hadoop configuration files, for example:

  <property>
    <name>property.name</name>
    <value>property.value</value>
  </property>

They can also be specified on the command line in the generic arguments, for example:

sqoop import -D property.name=property.value ...

Sqoop is designed to import mainframe datasets into HDFS. To do so, you must specify a mainframe host name in the Sqoop --connect argument.

$ sqoop import-mainframe --connect z390

This will connect to the mainframe host z390 via ftp.

You might need to authenticate against the mainframe host to access it. You can use the --username to supply a username to the mainframe. Sqoop provides couple of different ways to supply a password, secure and non-secure, to the mainframe which is detailed below.

Secure way of supplying password to the mainframe. You should save the password in a file on the users home directory with 400 permissions and specify the path to that file using the --password-file argument, and is the preferred method of entering credentials. Sqoop will then read the password from the file and pass it to the MapReduce cluster using secure means with out exposing the password in the job configuration. The file containing the password can either be on the Local FS or HDFS.

Example:

$ sqoop import-mainframe --connect z390 \
    --username david --password-file ${user.home}/.password

Another way of supplying passwords is using the -P argument which will read a password from a console prompt.

	Warning
	The --password parameter is insecure, as other users may be able to read your password from the command-line arguments via the output of programs such as ps. The -P argument is the preferred method over using the --password argument. Credentials may still be transferred between nodes of the MapReduce cluster using insecure means.

Example:

$ sqoop import-mainframe --connect z390 --username david --password 12345

You can use the --dataset argument to specify a partitioned dataset name. All sequential datasets in the partitioned dataset will be imported.

Sqoop imports data in parallel by making multiple ftp connections to the mainframe to transfer multiple files simultaneously. 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. You can adjust this value to maximize the data transfer rate from the mainframe.

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.

By default, Sqoop will import all sequential files in a partitioned dataset pds to a directory named pds inside your home directory in HDFS. For example, if your username is someuser, then the import tool will write to /user/someuser/pds/(files). You can adjust the parent directory of the import with the --warehouse-dir argument. For example:

$ sqoop import-mainframe --connnect <host> --dataset foo --warehouse-dir /shared \
    ...

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

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

$ sqoop import-mainframe --connnect <host> --dataset foo --target-dir /dest \
    ...

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

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.

By default, each record in a dataset is stored as a text record with a newline at the end. Each record is assumed to contain a single text field with the name DEFAULT_COLUMN. When Sqoop imports data to HDFS, it generates a Java class which can reinterpret the text files that it creates.

You can also import mainframe records to Sequence, Avro, or Parquet files.

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.

Since mainframe record contains only one field, importing to delimited files will not contain any field delimiter. However, the field may be enclosed with enclosing character or escaped by an escaping character.

When Sqoop imports data to HDFS, it generates a Java class which can reinterpret the text files that it creates when doing a delimited-format import. The delimiters are chosen with arguments such as --fields-terminated-by; this controls both how the data is written to disk, and how the generated parse() method reinterprets this data. The delimiters used by the parse() method can be chosen independently of the output arguments, by using --input-fields-terminated-by, and so on. This is useful, for example, to generate classes which can parse records created with one set of delimiters, and emit the records to a different set of files using a separate set of delimiters.

Sqoop’s import tool’s main function is to upload your data into files in HDFS. If you have a Hive metastore associated with your HDFS cluster, Sqoop can also import the data into Hive by generating and executing a CREATE TABLE statement to define the data’s layout in Hive. Importing data into Hive is as simple as adding the --hive-import option to your Sqoop command line.

If the Hive table already exists, you can specify the --hive-overwrite option to indicate that existing table in hive must be replaced. After your data is imported into HDFS or this step is omitted, Sqoop will generate a Hive script containing a CREATE TABLE operation defining your columns using Hive’s types, and a LOAD DATA INPATH statement to move the data files into Hive’s warehouse directory.

The script can be executed in two ways:

	Note
	This function is incompatible with --as-avrodatafile and --as-sequencefile.

Even though Hive supports escaping characters, it does not handle escaping of new-line character. Also, it does not support the notion of enclosing characters that may include field delimiters in the enclosed string. It is therefore recommended that you choose unambiguous field and record-terminating delimiters without the help of escaping and enclosing characters when working with Hive; this is due to limitations of Hive’s input parsing abilities. If you do use --escaped-by, --enclosed-by, or --optionally-enclosed-by when importing data into Hive, Sqoop will print a warning message.

Hive will have problems using Sqoop-imported data if your database’s rows contain string fields that have Hive’s default row delimiters (\n and \r characters) or column delimiters (\01 characters) present in them. You can use the --hive-drop-import-delims option to drop those characters on import to give Hive-compatible text data. Alternatively, you can use the --hive-delims-replacement option to replace those characters with a user-defined string on import to give Hive-compatible text data. These options should only be used if you use Hive’s default delimiters and should not be used if different delimiters are specified.

Sqoop will pass the field and record delimiters through to Hive. If you do not set any delimiters and do use --hive-import, the field delimiter will be set to ^A and the record delimiter will be set to \n to be consistent with Hive’s defaults.

Sqoop will by default import NULL values as string null. Hive is however using string \N to denote NULL values and therefore predicates dealing with NULL (like IS NULL) will not work correctly. You should append parameters --null-string and --null-non-string in case of import job or --input-null-string and --input-null-non-string in case of an export job if you wish to properly preserve NULL values. Because sqoop is using those parameters in generated code, you need to properly escape value \N to \\N:

$ sqoop import  ... --null-string '\\N' --null-non-string '\\N'

The table name used in Hive is, by default, the same as that of the source table. You can control the output table name with the --hive-table option.

Hive can put data into partitions for more efficient query performance. You can tell a Sqoop job to import data for Hive into a particular partition by specifying the --hive-partition-key and --hive-partition-value arguments. The partition value must be a string. Please see the Hive documentation for more details on partitioning.

You can import compressed tables into Hive using the --compress and --compression-codec options. One downside to compressing tables imported into Hive is that many codecs cannot be split for processing by parallel map tasks. The lzop codec, however, does support splitting. When importing tables with this codec, Sqoop will automatically index the files for splitting and configuring a new Hive table with the correct InputFormat. This feature currently requires that all partitions of a table be compressed with the lzop codec.

$ sqoop import --hive-import --connect $CONN --table $TABLENAME --username $USER --password $PASS --external-table-dir /tmp/external_table_example

$ sqoop import --hive-import --create-hive-table --connect $CONN --table $TABLENAME --username $USER --password $PASS --external-table-dir /tmp/foobar_example --hive-table foobar

$ sqoop import -Dsqoop.avro.decimal_padding.enable=true -Dsqoop.parquet.logical_types.decimal.enable=true
            -Dsqoop.avro.logical_types.decimal.default.precision=38 -Dsqoop.avro.logical_types.decimal.default.scale=10
            --hive-import --connect $CONN --table $TABLENAME --username $USER --password $PASS --as-parquetfile

Sqoop supports additional import targets beyond HDFS and Hive. Sqoop can also import records into a table in HBase.

By specifying --hbase-table, you instruct Sqoop to import to a table in HBase rather than a directory in HDFS. Sqoop will import data to the table specified as the argument to --hbase-table. Each row of the input table will be transformed into an HBase Put operation to a row of the output table. The key for each row is taken from a column of the input. By default Sqoop will use the split-by column as the row key column. If that is not specified, it will try to identify the primary key column, if any, of the source table. You can manually specify the row key column with --hbase-row-key. Each output column will be placed in the same column family, which must be specified with --column-family.

	Note
	This function is incompatible with direct import (parameter --direct).

If the input table has composite key, the --hbase-row-key must be in the form of a comma-separated list of composite key attributes. In this case, the row key for HBase row will be generated by combining values of composite key attributes using underscore as a separator. NOTE: Sqoop import for a table with composite key will work only if parameter --hbase-row-key has been specified.

If the target table and column family do not exist, the Sqoop job will exit with an error. You should create the target table and column family before running an import. If you specify --hbase-create-table, Sqoop will create the target table and column family if they do not exist, using the default parameters from your HBase configuration.

Sqoop currently serializes all values to HBase by converting each field to its string representation (as if you were importing to HDFS in text mode), and then inserts the UTF-8 bytes of this string in the target cell. Sqoop will skip all rows containing null values in all columns except the row key column.

By default Sqoop will retain the previously imported value for columns updated to null during incremental imports. This can be changed to delete all previous versions of the column by using --hbase-null-incremental-mode delete.

To decrease the load on hbase, Sqoop can do bulk loading as opposed to direct writes. To use bulk loading, enable it using --hbase-bulkload.

Sqoop supports importing records into a table in Accumulo

By specifying --accumulo-table, you instruct Sqoop to import to a table in Accumulo rather than a directory in HDFS. Sqoop will import data to the table specified as the argument to --accumulo-table. Each row of the input table will be transformed into an Accumulo Mutation operation to a row of the output table. The key for each row is taken from a column of the input. By default Sqoop will use the split-by column as the row key column. If that is not specified, it will try to identify the primary key column, if any, of the source table. You can manually specify the row key column with --accumulo-row-key. Each output column will be placed in the same column family, which must be specified with --accumulo-column-family.

	Note
	This function is incompatible with direct import (parameter --direct), and cannot be used in the same operation as an HBase import.

If the target table does not exist, the Sqoop job will exit with an error, unless the --accumulo-create-table parameter is specified. Otherwise, you should create the target table before running an import.

Sqoop currently serializes all values to Accumulo by converting each field to its string representation (as if you were importing to HDFS in text mode), and then inserts the UTF-8 bytes of this string in the target cell.

By default, no visibility is applied to the resulting cells in Accumulo, so the data will be visible to any Accumulo user. Use the --accumulo-visibility parameter to specify a visibility token to apply to all rows in the import job.

For performance tuning, use the optional --accumulo-buffer-size\ and --accumulo-max-latency parameters. See Accumulo’s documentation for an explanation of the effects of these parameters.

In order to connect to an Accumulo instance, you must specify the location of a Zookeeper ensemble using the --accumulo-zookeepers parameter, the name of the Accumulo instance (--accumulo-instance), and the username and password to connect with (--accumulo-user and --accumulo-password respectively).

As mentioned earlier, a byproduct of importing a table to HDFS is a class which can manipulate the imported data. You should use this class in your subsequent MapReduce processing of the data.

The class is typically named after the partitioned dataset name; a partitioned dataset named foo will generate a class named foo. You may want to override this class name. For example, if your partitioned dataset is named EMPLOYEES, you may want to specify --class-name Employee instead. Similarly, you can specify just the package name with --package-name. The following import generates a class named com.foocorp.SomePDS:

$ sqoop import-mainframe --connect <host> --dataset SomePDS --package-name com.foocorp

The .java source file for your class will be written to the current working directory when you run sqoop. You can control the output directory with --outdir. For example, --outdir src/generated/.

The import process compiles the source into .class and .jar files; these are ordinarily stored under /tmp. You can select an alternate target directory with --bindir. For example, --bindir /scratch.

If you already have a compiled class that can be used to perform the import and want to suppress the code-generation aspect of the import process, you can use an existing jar and class by providing the --jar-file and --class-name options. For example:

$ sqoop import-mainframe --dataset SomePDS --jar-file mydatatypes.jar \
    --class-name SomePDSType

This command will load the SomePDSType class out of mydatatypes.jar.

Support for Generation Data Group and Sequential data sets. This can be specified with the --datasettype option followed by one of: p for partitioned dataset (default) g for generation data group dataset s for sequential dataset

In the case of generation data group datasets, Sqoop will retrieve just the last or latest file (or generation).

In the case of sequential datasets, Sqoop will retrieve just the file specified.

Support of datasets that are stored on tape volumes by specifying --tape true.

By default, mainframe datasets are assumed to be plain text. Attempting to transfer binary datasets using this method will result in data corruption. Support for binary datasets by specifying --as-binaryfile and optionally --buffersize followed by buffer size specified in bytes. By default, --buffersize is set to 32760 bytes. Altering buffersize will alter the number of records Sqoop reports to have imported. This is because it reads the binary dataset in chunks specified by buffersize. Larger buffer size means lower number of records.

Use the --ftp-commands with a comma separated list of commands to send custom FTP commands prior to file retrieval. This is useful for letting the mainframe know to embed data into the binary files like Record Descriptor Words for variable length records so downstream processes can separate each record. The mainframe will otherwise discard this metadata in the file transmission.

	Note
	The responses from the mainframe of these commands are logged ONLY. It is up to the user to check for errors responses from the mainframe.

$ sqoop import-mainframe -D hadoop.security.credential.provider.path=jceks://file/my/folder/mainframe.jceks \
  --connect <host> --username user1 --password-alias alias1 --dataset SomeDS --tape true \
  --as-binaryfile --datasettype g --ftp-commands "SITE RDW,SITE RDW READTAPEFORMAT=V"

There are some additional properties which can be configured by modifying conf/sqoop-site.xml. Properties can be specified the same as in Hadoop configuration files, for example:

  <property>
    <name>property.name</name>
    <value>property.value</value>
  </property>

They can also be specified on the command line in the generic arguments, for example:

sqoop import -D property.name=property.value ...

For example, if the hive partition keys for the table to export/import from are defined with partition key names year, month and date and a specific partition with year=1999, month=12, day=31 is the desired partition, then the values for the two options will be as follows:

To provide backward compatibility, if --hcatalog-partition-keys or --hcatalog-partition-values options are not provided, then --hive-partitition-key and --hive-partition-value will be used if provided.

It is an error to specify only one of --hcatalog-partition-keys or --hcatalog-partition-values options. Either both of the options should be provided or neither of the options should be provided.

The following Sqoop options are also used along with the --hcatalog-table option to provide additional input to the HCatalog jobs. Some of the existing Hive import job options are reused with HCatalog jobs instead of creating HCatalog-specific options for the same purpose.

HCatalog integration in Sqoop has been enhanced to support direct mode connectors (which are high performance connectors specific to a database). Netezza direct mode connector has been enhanced to take advatange of this feature.

	Important
	Only Netezza direct mode connector is currently enabled to work with HCatalog.

The following options are ignored with HCatalog jobs.

To import data from RDBMS into an S3 bucket in incremental mode the temporary-rootdir option always has to be set and has to point to a location in the S3 bucket.

$ sqoop import \
  -Dfs.s3a.access.key=$AWS_ACCESS_KEY \
  -Dfs.s3a.secret.key=$AWS_SECRET_KEY \
  --connect $CONN \
  --username $USER \
  --password $PWD \
  --table $TABLE_NAME \
  --target-dir s3a://example-bucket/target-directory \
  --incremental append \
  --check-column $CHECK_COLUMN \
  --last-value $LAST_VALUE \
  --temporary-rootdir s3a://example-bucket/temporary-rootdir

$ sqoop import \
  -Dfs.s3a.access.key=$AWS_ACCES_KEY \
  -Dfs.s3a.secret.key=$AWS_SECRET_KEY \
  --connect $CONN \
  --username $USER \
  --password $PWD \
  --table $TABLE_NAME \
  --target-dir s3a://example-bucket/target-directory \
  --incremental lastmodified \
  --check-column $CHECK_COLUMN \
  --merge-key $MERGE_KEY \
  --last-value $LAST_VALUE \
  --temporary-rootdir s3a://example-bucket/temporary-rootdir

To import data from RDBMS into an external Hive table backed by S3 the AWS credentials have to be set in the Hive configuration file (hive-site.xml) too. For learning more about Hive on Amazon Web Services please see the Hive documentation at https://cwiki.apache.org/confluence/display/Hive/HiveAws.

The current implementation of Sqoop requires that both target-dir and external-table-dir options are set where external-table-dir has to point to the Hive table location in the S3 bucket.

Import into an external Hive table backed by S3 for example:

$ sqoop import \
  -Dfs.s3a.access.key=$AWS_ACCES_KEY \
  -Dfs.s3a.secret.key=$AWS_SECRET_KEY \
  --connect $CONN \
  --username $USER \
  --password $PWD \
  --table $TABLE_NAME \
  --hive-import \
  --target-dir s3a://example-bucket/target-directory \
  --external-table-dir s3a://example-bucket/external-directory

Create an external Hive table backed by S3 for example:

$ sqoop import \
  -Dfs.s3a.access.key=$AWS_ACCES_KEY \
  -Dfs.s3a.secret.key=$AWS_SECRET_KEY \
  --connect $CONN \
  --username $USER \
  --password $PWD \
  --table $TABLE_NAME \
  --hive-import \
  --create-hive-table \
  --hive-table $HIVE_TABLE_NAME \
  --target-dir s3a://example-bucket/target-directory \
  --external-table-dir s3a://example-bucket/external-directory

Data from RDBMS can be imported into an external Hive table backed by S3 as Parquet file format too.

MySQL allows values of '0000-00-00\' for DATE columns, which is a non-standard extension to SQL. When communicated via JDBC, these values are handled in one of three different ways:

You specify the behavior by using the zeroDateTimeBehavior property of the connect string. If a zeroDateTimeBehavior property is not specified, Sqoop uses the convertToNull behavior.

You can override this behavior. For example:

$ sqoop import --table foo \
    --connect jdbc:mysql://db.example.com/someDb?zeroDateTimeBehavior=round

Columns with type UNSIGNED in MySQL can hold values between 0 and 2^32 (4294967295), but the database will report the data type to Sqoop as INTEGER, which will can hold values between -2147483648 and \+2147483647. Sqoop cannot currently import UNSIGNED values above 2147483647.

Sqoop’s direct mode does not support imports of BLOB, CLOB, or LONGVARBINARY columns. Use JDBC-based imports for these columns; do not supply the --direct argument to the import tool.

Sqoop is currently not supporting import from view in direct mode. Use JDBC based (non direct) mode in case that you need to import view (simply omit --direct parameter).

Sqoop is currently not supporting import from view in direct mode. Use JDBC based (non direct) mode in case that you need to import view (simply omit --direct parameter).

Oracle JDBC represents DATE and TIME SQL types as TIMESTAMP values. Any DATE columns in an Oracle database will be imported as a TIMESTAMP in Sqoop, and Sqoop-generated code will store these values in java.sql.Timestamp fields.

When exporting data back to a database, Sqoop parses text fields as TIMESTAMP types (with the form yyyy-mm-dd HH:MM:SS.ffffffff) even if you expect these fields to be formatted with the JDBC date escape format of yyyy-mm-dd. Dates exported to Oracle should be formatted as full timestamps.

Oracle also includes the additional date/time types TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE. To support these types, the user’s session timezone must be specified. By default, Sqoop will specify the timezone "GMT" to Oracle. You can override this setting by specifying a Hadoop property oracle.sessionTimeZone on the command-line when running a Sqoop job. For example:

$ sqoop import -D oracle.sessionTimeZone=America/Los_Angeles \
    --connect jdbc:oracle:thin:@//db.example.com/foo --table bar

Note that Hadoop parameters (-D …) are generic arguments and must appear before the tool-specific arguments (--connect, --table, and so on).

Legal values for the session timezone string are enumerated at http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#i637736.

When connecting to DB2 and using the import-all-tables or the list-tables tools, one can use the --schema tool option to specify the schema to use. If the option is not present, then the schema of the current user (specified with the --username option) will be used as a default. Please note that this option doesn’t work for any other tools at the moment, (such as the import tool or the export tool).

Examples:

$ sqoop list-tables --connect $CONN  --username $USER --password $PASS -- --schema DB2INST2
$ sqoop import-all-tables --connect $CONN  --username $USER --password $PASS -- --schema DB2INST2

MySQL JDBC Connector is supporting upsert functionality using argument --update-mode allowinsert. To achieve that Sqoop is using MySQL clause INSERT INTO … ON DUPLICATE KEY UPDATE. This clause do not allow user to specify which columns should be used to distinct whether we should update existing row or add new row. Instead this clause relies on table’s unique keys (primary key belongs to this set). MySQL will try to insert new row and if the insertion fails with duplicate unique key error it will update appropriate row instead. As a result, Sqoop is ignoring values specified in parameter --update-key, however user needs to specify at least one valid column to turn on update mode itself.

Utilities mysqldump and mysqlimport should be present in the shell path of the user running the Sqoop command on all nodes. To validate SSH as this user to all nodes and execute these commands. If you get an error, so will Sqoop.

For performance, each writer will commit the current transaction approximately every 32 MB of exported data. You can control this by specifying the following argument before any tool-specific arguments: -D sqoop.mysql.export.checkpoint.bytes=size, where size is a value in bytes. Set size to 0 to disable intermediate checkpoints, but individual files being exported will continue to be committed independently of one another.

Sometimes you need to export large data with Sqoop to a live MySQL cluster that is under a high load serving random queries from the users of your application. While data consistency issues during the export can be easily solved with a staging table, there is still a problem with the performance impact caused by the heavy export.

First off, the resources of MySQL dedicated to the import process can affect the performance of the live product, both on the master and on the slaves. Second, even if the servers can handle the import with no significant performance impact (mysqlimport should be relatively "cheap"), importing big tables can cause serious replication lag in the cluster risking data inconsistency.

With -D sqoop.mysql.export.sleep.ms=time, where time is a value in milliseconds, you can let the server relax between checkpoints and the replicas catch up by pausing the export process after transferring the number of bytes specified in sqoop.mysql.export.checkpoint.bytes. Experiment with different settings of these two parameters to archieve an export pace that doesn’t endanger the stability of your MySQL cluster.

	Important
	Note that any arguments to Sqoop that are of the form -D parameter=value are Hadoop generic arguments and must appear before any tool-specific arguments (for example, --connect, --table, etc). Don’t forget that these parameters are only supported with the --direct flag set.

List of all extra arguments supported by Microsoft SQL Connector is shown below:

You can allow inserts on columns that have identity. For example:

$ sqoop export ... --export-dir custom_dir --table custom_table -- --identity-insert

You can override the default and use resilient operations during export or import. This will retry failed operations, i.e. if the connection gets dropped by SQL Server, the mapper will try to reconnect and continue from where it was before.

In case of export, the --resilient option will ensure that Sqoop will try to recover from connection resets.

In case of import, however, one has to use both the --resilient option and specify the --split-by column to trigger the retry mechanism. An important requirement is that the data must be unique and ordered ascending by the split-by column, otherwise records could be either lost or duplicated.

Example commands using resilient operations:

$ sqoop export ... --export-dir custom_dir --table custom_table -- --resilient

Importing from a table:

$ sqoop import ... --table custom_table --split-by id -- --resilient

Importing via a query:

$ sqoop import ... --query "SELECT ... WHERE $CONDITIONS" --split-by ordered_column -- --resilient

Schema support
^^^^^^^^^^^^^^

If you need to work with tables that are located in non-default schemas, you can
specify schema names via the +\--schema+ argument. Custom schemas are supported for
both import and export jobs. For example:

$ sqoop import … --table custom_table — --schema custom_schema

Table hints
^^^^^^^^^^^

Sqoop supports table hints in both import and export jobs. Table hints are used only
for queries that move data from/to Microsoft SQL Server, but they cannot be used for
meta data queries. You can specify a comma-separated list of table hints in the
+\--table-hints+ argument. For example:

$ sqoop import … --table custom_table — --table-hints NOLOCK

PostgreSQL Connector
~~~~~~~~~~~~~~~~~~~~~

Extra arguments
^^^^^^^^^^^^^^^

List of all extra arguments supported by PostgreSQL Connector is shown below:

.Supported PostgreSQL extra arguments:
[grid="all"]
`----------------------------------------`---------------------------------------
Argument                                 Description

--schema <name> Scheme name that sqoop should use. \ Default is "public".

Schema support
^^^^^^^^^^^^^^

If you need to work with table that is located in schema other than default one,
you need to specify extra argument +\--schema+. Custom schemas are supported for
both import and export job (optional staging table however must be present in the
same schema as target table). Example invocation:

$ sqoop import … --table custom_table — --schema custom_schema

PostgreSQL Direct Connector
~~~~~~~~~~~~~~~~~~~~~~~~~~~

PostgreSQL Direct Connector allows faster import and export to/from PostgresSQL "COPY" command.

To use the PostgreSQL Direct Connector, specify the +\--direct+ argument for your import or export job.

When importing from PostgreSQL in conjunction with direct mode, you
can split the import into separate files after
individual files reach a certain size. This size limit is controlled
with the +\--direct-split-size+ argument.

The direct connector offers also additional extra arguments:

.Additional supported PostgreSQL extra arguments in direct mode:
[grid="all"]
`----------------------------------------`---------------------------------------
Argument                                 Description

--boolean-true-string <str> String that will be used to encode \ true value of boolean columns. Default is "TRUE". --boolean-false-string <str> String that will be used to encode \ false value of boolean columns. Default is "FALSE".

Requirements
^^^^^^^^^^^^

Utility +psql+ should be present in the shell path of the user running the Sqoop command on
all nodes. To validate SSH as this user to all nodes and execute these commands. If you get an error, so will Sqoop.


Limitations
^^^^^^^^^^^^

* Currently the direct connector does not support import of large object columns (BLOB and CLOB).
* Importing to HBase and Accumulo is not supported
* Import of views is not supported

pg_bulkload connector
~~~~~~~~~~~~~~~~~~~~~

Purpose
^^^^^^^
pg_bulkload connector is a direct connector for exporting data into PostgreSQL.
This connector uses
http://pgbulkload.projects.postgresql.org/index.html[pg_bulkload].
Users benefit from functionality of pg_bulkload such as
fast exports bypassing shared bufferes and WAL,
flexible error records handling,
and ETL feature with filter functions.

Requirements
^^^^^^^^^^^^
pg_bulkload connector requires following conditions for export job execution:

* The link:http://pgbulkload.projects.postgresql.org/index.html[pg_bulkload]
  must be installed on DB server and all slave nodes.
  RPM for RedHat or CentOS is available in then
  link:http://pgfoundry.org/frs/?group_id=1000261[download page].
* The link:http://jdbc.postgresql.org/index.html[PostgreSQL JDBC]
  is required on client node.
* Superuser role of PostgreSQL database is required for execution of pg_bulkload.

Syntax
^^^^^^
Use +--connection-manager+ option to specify connection manager classname.

$ sqoop export (generic-args) --connection-manager org.apache.sqoop.manager.PGBulkloadManager (export-args) $ sqoop-export (generic-args) --connection-manager org.apache.sqoop.manager.PGBulkloadManager (export-args)

This connector supports export arguments shown below.

.Supported export control arguments:
[grid="all"]
`----------------------------------------`---------------------------------------
Argument                                 Description

--export-dir <dir> HDFS source path for the export -m,--num-mappers <n> Use n map tasks to export in\ parallel --table <table-name> Table to populate --input-null-string <null-string> The string to be interpreted as\ null for string columns

There are additional configuration for pg_bulkload execution
specified via Hadoop Configuration properties
which can be given with +-D <property=value>+ option.
Because Hadoop Configuration properties are generic arguments of the sqoop,
it must preceed any export control arguments.

.Supported export control properties:
[grid="all"]
`------------------------------`----------------------------------------------
Property                       Description

mapred.reduce.tasks Number of reduce tasks for staging. \ The defalt value is 1. \ Each tasks do staging in a single transaction. pgbulkload.bin Path of the pg_bulkoad binary \ installed on each slave nodes. pgbulkload.check.constraints Specify whether CHECK constraints are checked \ during the loading. \ The default value is YES. pgbulkload.parse.errors The maximum mumber of ingored records \ that cause errors during parsing, \ encoding, filtering, constraints checking, \ and data type conversion. \ Error records are recorded \ in the PARSE BADFILE. \ The default value is INFINITE. pgbulkload.duplicate.errors Number of ingored records \ that violate unique constraints. \ Duplicated records are recorded in the \ DUPLICATE BADFILE on DB server. \ The default value is INFINITE. pgbulkload.filter Specify the filter function \ to convert each row in the input file. \ See the pg_bulkload documentation to know \ how to write FILTER functions. pgbulkload.clear.staging.table Indicates that any data present in\ the staging table can be dropped.

Here is a example of complete command line.

$ sqoop export \ -Dmapred.reduce.tasks=2 -Dpgbulkload.bin="/usr/local/bin/pg_bulkload" \ -Dpgbulkload.input.field.delim=$\t \ -Dpgbulkload.check.constraints="YES" \ -Dpgbulkload.parse.errors="INFINITE" \ -Dpgbulkload.duplicate.errors="INFINITE" \ --connect jdbc:postgresql://pgsql.example.net:5432/sqooptest \ --connection-manager org.apache.sqoop.manager.PGBulkloadManager \ --table test --username sqooptest --export-dir=/test -m 2

Data Staging
^^^^^^^^^^^^
Each map tasks of pg_bulkload connector's export job create
their own staging table on the fly.
The Name of staging tables is decided based on the destination table
and the task attempt ids.
For example, the name of staging table for the "test" table is like
+test_attempt_1345021837431_0001_m_000000_0+ .

Staging tables are automatically dropped if tasks successfully complete
or map tasks fail.
When reduce task fails,
staging table for the task are left for manual retry and
users must take care of it.

Netezza Connector
~~~~~~~~~~~~~~~~~

Extra arguments
^^^^^^^^^^^^^^^

List of all extra arguments supported by Netezza Connector is shown below:

.Supported Netezza extra arguments:
[grid="all"]
`-------------------------------------`----------------------------------------
Argument                              Description

--partitioned-access Whether each mapper acts on a subset\ of data slices of a table or all\ Default is "false" for standard mode\ and "true" for direct mode. --max-errors Applicable only for direct mode export.\ This option specifies the error threshold\ per mapper while transferring data. If\ the number of errors encountered exceed\ this threshold then the job will fail. Default value is 1. --log-dir Applicable only for direct mode export.\ Specifies the directory where Netezza\ external table operation logs are stored\ on the hadoop filesystem. Logs are\ stored under this directory with one\ directory for the job and sub-directories\ for each task number and attempt.\ Default value is the user home directory.\ The nzlog and nzbad files will be under (logdir)/job-id/job-attempt-id. --trunc-string Applicable only for direct mode export.\ Specifies whether the system \ truncates strings to the declared\ storage and loads the data. By default\ truncation of strings is reported as an\ error. --ctrl-chars Applicable only for direct mode export.\ Specifies whether control characters \ (ASCII chars 1 - 31) can be allowed \ to be part of char/nchar/varchar/nvarchar\ columns. Default is false. --crin-string Applicable only for direct mode export.\ Specifies whether carriage return \ (ASCII char 13) can be allowed \ to be part of char/nchar/varchar/nvarchar\ columns. Note that CR can no longer \ be a record delimiter with this option.\ Default is false. --ignore-zero Applicable only for direct mode export.\ Specifies whether NUL character \ (ASCII char 0) should be scanned \ and ignored as part of the data loaded\ into char/nchar/varchar/nvarchar \ columns.\ Default is false.

Direct Mode
^^^^^^^^^^^
Netezza connector supports an optimized data transfer facility using the
Netezza external tables feature.  Each map tasks of Netezza connector's import
job will work on a subset of the Netezza partitions and transparently create
and use an external table to transport data.  Similarly, export jobs will use
the external table to push data fast onto the NZ system.   Direct mode does
not support staging tables, upsert options etc.

Here is an example of complete command line for import using the Netezza
external table feature.

$ sqoop import \ --direct \ --connect jdbc:netezza://nzhost:5480/sqoop \ --table nztable \ --username nzuser \ --password nzpass \ --target-dir hdfsdir

Here is an example of complete command line for export with tab as the field
terminator character.

$ sqoop export \ --direct \ --connect jdbc:netezza://nzhost:5480/sqoop \ --table nztable \ --username nzuser \ --password nzpass \ --export-dir hdfsdir \ --input-fields-terminated-by "\t"

Null string handling
^^^^^^^^^^^^^^^^^^^^

Netezza direct connector supports the null-string features of Sqoop.  The null
string values are converted to appropriate external table options during export
and import operations.

.Supported export control arguments:
[grid="all"]
`----------------------------------------`---------------------------------------
Argument                                 Description

--input-null-string <null-string> The string to be interpreted as\ null for string columns. --input-null-non-string <null-string> The string to be interpreted as\ null for non string columns.

In the case of Netezza direct mode connector, both the arguments must be
left to the default values or explicitly set to the same value.  Furthermore
the null string value is restricted to 0-4 utf8 characters.

On export, for non-string columns, if the chosen null value is a valid
representation in the column domain, then the column might not be loaded as
null.  For example, if the null string value is specified as "1", then on
export, any occurrence of "1" in the input file will be loaded as value 1
instead of NULL for int columns.

It is suggested that the null value be specified as empty string for
performance and consistency.

.Supported import control arguments:
[grid="all"]
`----------------------------------------`---------------------------------------
Argument                                 Description

--null-string <null-string> The string to be interpreted as\ null for string columns. --null-non-string <null-string> The string to be interpreted as\ null for non string columns.

In the case of Netezza direct mode connector, both the arguments must be
left to the default values or explicitly set to the same value.  Furthermore
the null string value is restricted to 0-4 utf8 characters.

On import, for non-string columns, the chosen null value in current
implementations the null value representation is ignored for non character
columns.  For example, if the null string value is specified as "\N", then on
import, any occurrence of NULL for non-char columns in the table will be
imported as an empty string instead of '\N', the chosen null string
representation.

It is suggested that the null value be specified as empty string for
performance and consistency.

Data Connector for Oracle and Hadoop
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

About
^^^^^

The Data Connector for Oracle and Hadoop is now included in Sqoop.

It can be enabled by specifying the +\--direct+ argument for your import or
export job.

Jobs
++++

The Data Connector for Oracle and Hadoop inspects each Sqoop job and assumes
responsibility for the ones it can perform better than the Oracle manager built
into Sqoop.

Data Connector for Oracle and Hadoop accepts responsibility for the following
Sqoop Job types:

- *Import* jobs that are *Non-Incremental*.
- *Export* jobs
- Data Connector for Oracle and Hadoop does not accept responsibility for other
Sqoop job types. For example Data Connector for Oracle and Hadoop does not
accept *eval* jobs etc.

Data Connector for Oracle and Hadoop accepts responsibility for those Sqoop Jobs
with the following attributes:

- Oracle-related
- Table-Based - Jobs where the table argument is used and the specified object
is a table.
+
NOTE: Data Connector for Oracle and Hadoop does not process index-organized
tables unless the table is partitioned and +oraoop.chunk.method+ is set
to +PARTITION+

- There are at least 2 mappers — Jobs where the Sqoop command-line does not
include: +--num-mappers 1+

How The Standard Oracle Manager Works for Imports
+++++++++++++++++++++++++++++++++++++++++++++++++

The Oracle manager built into Sqoop uses a range-based query for each mapper.
Each mapper executes a query of the form:

SELECT * FROM sometable WHERE id >= lo AND id < hi

The *lo* and *hi* values are based on the number of mappers and the minimum and
maximum values of the data in the column the table is being split by.

If no suitable index exists on the table then these queries result in full
table-scans within Oracle. Even with a suitable index, multiple mappers may
fetch data stored within the same Oracle blocks, resulting in redundant IO
calls.

How The Data Connector for Oracle and Hadoop Works for Imports
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The Data Connector for Oracle and Hadoop generates queries for the mappers of
the form:

SELECT * FROM sometable WHERE rowid >= dbms_rowid.rowid_create(1, 893, 1, 279, 0) AND rowid ⇐ dbms_rowid.rowid_create(1, 893, 1, 286, 32767)

The Data Connector for Oracle and Hadoop queries ensure that:

- No two mappers read data from the same Oracle block. This minimizes
redundant IO.
- The table does not require indexes.
- The Sqoop command line does not need to specify a +--split-by+ column.

Data Connector for Oracle and Hadoop Exports
++++++++++++++++++++++++++++++++++++++++++++

Benefits of the Data Connector for Oracle and Hadoop:

- *Merge-Export facility* - Update Oracle tables by modifying changed rows AND
inserting rows from the HDFS file that did not previously exist in the Oracle
table. The Connector for Oracle and Hadoop's Merge-Export is unique - there is
no Sqoop equivalent.
- *Lower impact on the Oracle database* - Update the rows in the Oracle table
that have changed, not all rows in the Oracle table. This has performance
benefits and reduces the impact of the query on Oracle (for example, the Oracle
redo logs).
- *Improved performance* - With partitioned tables, mappers utilize temporary
Oracle tables which allow parallel inserts and direct path writes.

Requirements
^^^^^^^^^^^^

Ensure The Oracle Database JDBC Driver Is Setup Correctly
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

You may want to ensure the Oracle Database 11g Release 2 JDBC driver is setup
correctly on your system. This driver is required for Sqoop to work with Oracle.

The Oracle Database 11g Release 2 JDBC driver file is +ojdbc6.jar+ (3.2Mb).

If this file is not on your system then download it from:
http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

This file should be put into the +$SQOOP_HOME/lib+ directory.

Oracle Roles and Privileges
+++++++++++++++++++++++++++
The Oracle user for The Data Connector for Oracle and Hadoop requires the
following roles and privileges:

- +create session+

In addition, the user must have the select any dictionary privilege or
select_catalog_role role or all of the following object privileges:

- +select on v_$instance+
- +select on dba_tables+
- +select on dba_tab_columns+
- +select on dba_objects+
- +select on dba_extents+
- +select on dba_segments+ — Required for Sqoop imports only
- +select on dba_constraints+ — Required for Sqoop imports only
- +select on v_$database+ — Required for Sqoop imports only
- +select on v_$parameter+ — Required for Sqoop imports only

NOTE: The user also requires the alter session privilege to make use of session
tracing functionality. See "oraoop.oracle.session.initialization.statements"
for more information.

Additional Oracle Roles And Privileges Required for Export
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The Oracle user for Data Connector for Oracle and Hadoop requires:

- Quota on the tablespace in which the Oracle export tables are located.
+
An example Oracle command to achieve this is
+

alter user username quota unlimited on tablespace

- The following privileges:
+
[grid="all"]
`-----------------------------------------------------------------------------------------------`--------------------------------------------------------------------------
Type of Export                                                                                  Privileges Required

All Export create table select on dba_tab_partitions select on dba_tab_subpartitions select on dba_indexes select on dba_ind_columns Insert-Export with a template table into another schema select any table create any table insert any table alter any table (partitioning) Insert-Export without a template table into another schema select,insert on table (no partitioning) select,alter on table (partitioning) Update-Export into another schema select,update on table (no partitioning) select,delete,alter,insert on table (partitioning) Merge-Export into another schema select,insert,update on table (no partitioning) select,insert,delete,alter on table (partitioning)

Supported Data Types
++++++++++++++++++++

The following Oracle data types are supported by the Data Connector for
Oracle and Hadoop:

[grid="all"]
`----------------------------------------`-----------------------------------------------------------------
BINARY_DOUBLE                            NCLOB
BINARY_FLOAT                             NUMBER
BLOB                                     NVARCHAR2
CHAR                                     RAW
CLOB                                     ROWID
DATE                                     TIMESTAMP
FLOAT                                    TIMESTAMP WITH TIME ZONE
INTERVAL DAY TO SECOND                   TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH                   URITYPE
LONG                                     VARCHAR2
NCHAR

All other Oracle column types are NOT supported. Example Oracle column types NOT supported by Data Connector for Oracle and Hadoop include:

	Note
	Data types RAW, LONG and LOB (BLOB, CLOB and NCLOB) are supported for Data Connector for Oracle and Hadoop imports. They are not supported for Data Connector for Oracle and Hadoop exports.

Execute Sqoop. Following is an example command:

$ sqoop import --direct --connect … --table OracleTableName

If The Data Connector for Oracle and Hadoop accepts the job then the following text is output:

**************************************************
*** Using Data Connector for Oracle and Hadoop ***
**************************************************

Note

More information is available on the --connect parameter. See "Connect to Oracle / Oracle RAC" for more information. If Java runs out of memory the workaround is to specify each mapper’s JVM memory allocation. Add the following parameter for example to allocate 4GB: -Dmapred.child.java.opts=-Xmx4000M An Oracle optimizer hint is included in the SELECT statement by default. See "oraoop.import.hint" for more information. You can alter the hint on the command line as follows: -Doraoop.import.hint="NO_INDEX(t)" You can turn off the hint on the command line as follows (notice the space between the double quotes): -Doraoop.import.hint=" "

26.4.5.1. Match Hadoop Files to Oracle Table Partitions

-Doraoop.chunk.method={ROWID|PARTITION}

To import data from a partitioned table in such a way that the resulting HDFS folder structure in Hadoop will match the table’s partitions, set the chunk method to PARTITION. The alternative (default) chunk method is ROWID.

	Note
	For the number of Hadoop files to match the number of Oracle partitions, set the number of mappers to be greater than or equal to the number of partitions. If the table is not partitioned then value PARTITION will lead to an error.

26.4.5.2. Specify The Partitions To Import

-Doraoop.import.partitions=PartitionA,PartitionB --table OracleTableName

Imports PartitionA and PartitionB of OracleTableName.

Note

You can enclose an individual partition name in double quotes to retain the letter case or if the name has special characters. -Doraoop.import.partitions='"PartitionA",PartitionB' --table OracleTableName If the partition name is not double quoted then its name will be automatically converted to upper case, PARTITIONB for above. When using double quotes the entire list of partition names must be enclosed in single quotes. If the last partition name in the list is double quoted then there must be a comma at the end of the list. -Doraoop.import.partitions='"PartitionA","PartitionB",' --table OracleTableName Name each partition to be included. There is no facility to provide a range of partition names. There is no facility to define sub partitions. The entire partition is included/excluded as per the filter.

26.4.5.3. Consistent Read: All Mappers Read From The Same Point In Time

-Doraoop.import.consistent.read={true|false}

When set to false (by default) each mapper runs a select query. This will return potentially inconsistent data if there are a lot of DML operations on the table at the time of import.

Set to true to ensure all mappers read from the same point in time. The System Change Number (SCN) is passed down to all mappers, which use the Oracle Flashback Query to query the table as at that SCN.

	Note
	Values true | false are case sensitive. By default the SCN is taken from V$database. You can specify the SCN in the following command -Doraoop.import.consistent.read.scn=12345

Execute Sqoop. Following is an example command:

$ sqoop export --direct --connect … --table OracleTableName --export-dir /user/username/tablename

The Data Connector for Oracle and Hadoop accepts all jobs that export data to Oracle. You can verify The Data Connector for Oracle and Hadoop is in use by checking the following text is output:

**************************************************
*** Using Data Connector for Oracle and Hadoop ***
**************************************************

Note

OracleTableName is the Oracle table the data will export into. OracleTableName can be in a schema other than that for the connecting user. Prefix the table name with the schema, for example SchemaName.OracleTableName. Hadoop tables are picked up from the /user/username/tablename directory. The export will fail if the Hadoop file contains any fields of a data type not supported by The Data Connector for Oracle and Hadoop. See "Supported Data Types" for more information. The export will fail if the column definitions in the Hadoop table do not exactly match the column definitions in the Oracle table. The Data Connector for Oracle and Hadoop indicates if it finds temporary tables that it created more than a day ago that still exist. Usually these tables can be dropped. The only circumstance when these tables should not be dropped is when an The Data Connector for Oracle and Hadoop job has been running for more than 24 hours and is still running. More information is available on the --connect parameter. See "Connect to Oracle / Oracle RAC" for more information.

26.4.6.1. Insert-Export

Appends data to OracleTableName. It does not modify existing data in OracleTableName.

Insert-Export is the default method, executed in the absence of the --update-key parameter. All rows in the HDFS file in /user/UserName/TableName are inserted into OracleTableName. No change is made to pre-existing data in OracleTableName.

$ sqoop export --direct --connect … --table OracleTableName --export-dir /user/username/tablename

	Note
	If OracleTableName was previously created by The Data Connector for Oracle and Hadoop with partitions then this export will create a new partition for the data being inserted. When creating OracleTableName specify a template. See "Create Oracle Tables" for more information.

26.4.6.2. Update-Export

--update-key OBJECT

Updates existing rows in OracleTableName.

Rows in the HDFS file in /user/UserName/TableName are matched to rows in OracleTableName by the OBJECT column. Rows that match are copied from the HDFS file to the Oracle table. No action is taken on rows that do not match.

$ sqoop export --direct --connect … --update-key OBJECT --table OracleTableName --export-dir /user/username/tablename

Note

If OracleTableName was previously created by The Data Connector for Oracle and Hadoop with partitions then this export will create a new partition for the data being inserted. Updated rows will be moved to the new partition that was created for the export. For performance reasons it is strongly recommended that where more than a few rows are involved column OBJECT be an index column of OracleTableName. Ensure the column name defined with --update-key OBJECT is specified in the correct letter case. Sqoop will show an error if the letter case is incorrect. It is possible to match rows via multiple columns. See "Match Rows Via Multiple Columns" for more information.

26.4.6.3. Merge-Export

--update-key OBJECT -Doraoop.export.merge=true

Updates existing rows in OracleTableName. Copies across rows from the HDFS file that do not exist within the Oracle table.

Rows in the HDFS file in /user/UserName/TableName are matched to rows in OracleTableName by the OBJECT column. Rows that match are copied from the HDFS file to the Oracle table. Rows in the HDFS file that do not exist in OracleTableName are added to OracleTableName.

$ sqoop export --direct --connect … --update-key OBJECT -Doraoop.export.merge=true --table OracleTableName --export-dir /user/username/tablename

Note

Merge-Export is unique to The Data Connector for Oracle and Hadoop. It is not a standard Sqoop feature. If OracleTableName was previously created by The Data Connector for Oracle and Hadoop with partitions, then this export will create a new partition for the data being inserted. Updated rows will be moved to the new partition that was created for the export. For performance reasons it is strongly recommended that where more than a few rows are involved column OBJECT be an index column of OracleTableName. Ensure the column name defined with --update-key OBJECT is specified in the correct letter case. Sqoop will show an error if the letter case is incorrect. It is possible to match rows via multiple columns. See "Match Rows Via Multiple Columns" for more information.

26.4.6.4. Create Oracle Tables

-Doraoop.template.table=TemplateTableName

Creates OracleTableName by replicating the structure and data types of TemplateTableName. TemplateTableName is a table that exists in Oracle prior to executing the Sqoop command.

Note

The export will fail if the Hadoop file contains any fields of a data type not supported by The Data Connector for Oracle and Hadoop. See "Supported Data Types" for more information. The export will fail if the column definitions in the Hadoop table do not exactly match the column definitions in the Oracle table. This parameter is specific to creating an Oracle table. The export will fail if OracleTableName already exists in Oracle.

Example command:

$ sqoop export --direct --connect.. --table OracleTableName --export-dir /user/username/tablename -Doraoop.template.table=TemplateTableName