See Also
More details about
NULL
values are available in Recipes
2.10
and
5.8
.
7.6. Using the upsert Feature When Exporting into MySQL
Problem
You’ve modified data sets in Hadoop and you want to propagate those changes back to
your MySQL database. Your transformations both update existing rows and create new
ones. While using Sqoop’s
upsert
functionality in the
--update-mode allowinsert
parameter, you notice that Sqoop doesn’t use any of the columns specified in
--update-
key
in order to determine whether to update an existing row or insert a new one.
Solution
You need to create a unique key on all columns that you are going to use with the
--update-key
parameter. For example, to create a unique key on the column
city
of
the
cities
table, you would execute the following MySQL query:
ALTER
TABLE
cities
ADD
UNIQUE
KEY
(
city
);
7.6. Using the upsert Feature When Exporting into MySQL
|
67
Discussion
The MySQL database does not support the
MERGE
SQL operator as Oracle does. Instead,
MySQL provides the
ON DUPLICATE KEY UPDATE
clause that Sqoop uses when exporting
in
upsert
mode. The
MERGE
operator allows you to specify a condition to determine
whether an update or insert operation should be performed. MySQL’s clause will always
try to insert. Only if the insert fails because such an operation would violate a unique
key constraint does it update the existing row instead. Since MySQL does not allow you
to specify a condition, the table’s unique key is always used. Since Sqoop uses the
ON
DUPLICATE KEY UPDATE
clause, columns specified in the
--update-key
parameter are
not used for determining what operation should be performed. This is quite confusing,
as you always have to specify this parameter in order to enable update mode, yet the
columns are not used in
upsert
mode.
See Also
The functionality of
upsert
and its Sqoop implementation are further explained in
Recipe 5.5
.
7.7. Importing from Oracle
Problem
Sqoop can’t find any columns when importing data from Oracle. For example, you see
the following exception:
java.lang.IllegalArgumentException: Attempted to generate class with no columns!
Solution
Make sure that both the table and the username are specified with the correct case.
Usually, specifying both the table and usernames in uppercase will resolve this issue. In
addition, if a different user created the transferred table, you will need to specify this
user in the
--table
parameter in the form
user.table_name
. For example, to import
table
cities
created by user
kathleen
from Oracle using user
sqoop
, you would execute
the following Sqoop command:
sqoop import
\
--connect jdbc:oracle:thin:@oracle.example.com:1521/ORACLE
\
--username SQOOP
\
--password sqoop
\
--table KATHLEEN.cities
68
|
Chapter 7: Specialized Connectors
Discussion
The Oracle connector uses the following catalog query for retrieving table structure
information (number of columns, their names, and associated data types):
SELECT
COLUMN_NAME
FROM
ALL_TAB_COLUMNS
WHERE
OWNER
= ?
AND
TABLE_NAME
= ?
ORDER
BY
COLUMN_ID
As the equals operator is case sensitive, you must enter both the table name and owner
in the same way as is recorded in the database catalog.
