See Also More details about NULL values are available in Recipes 210

See also more details about null values are available

This preview shows page 85 - 88 out of 94 pages.

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
Image of page 85
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
Image of page 86
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.
Image of page 87
Image of page 88

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture