100%(1)1 out of 1 people found this document helpful
This preview shows page 85 - 88 out of 94 pages.
See AlsoMore details about NULLvalues are available in Recipes 2.10 and 126.96.36.199. Using the upsert Feature When Exporting into MySQLProblemYou’ve modified data sets in Hadoop and you want to propagate those changes back toyour MySQL database. Your transformations both update existing rows and create newones. While using Sqoop’s upsertfunctionality in the --update-mode allowinsertparameter, you notice that Sqoop doesn’t use any of the columns specified in --update-keyin order to determine whether to update an existing row or insert a new one.SolutionYou need to create a unique key on all columns that you are going to use with the--update-keyparameter. For example, to create a unique key on the column cityofthe citiestable, you would execute the following MySQL query:ALTERTABLEcities ADDUNIQUEKEY(city);7.6. Using the upsert Feature When Exporting into MySQL | 67
DiscussionThe MySQL database does not support the MERGESQL operator as Oracle does. Instead,MySQL provides the ON DUPLICATE KEY UPDATEclause that Sqoop uses when exportingin upsertmode. The MERGEoperator allows you to specify a condition to determinewhether an update or insert operation should be performed. MySQL’s clause will alwaystry to insert. Only if the insert fails because such an operation would violate a uniquekey constraint does it update the existing row instead. Since MySQL does not allow youto specify a condition, the table’s unique key is always used. Since Sqoop uses the ONDUPLICATE KEY UPDATEclause, columns specified in the --update-keyparameter arenot 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 thecolumns are not used in upsertmode.See AlsoThe functionality of upsertand its Sqoop implementation are further explained inRecipe 188.8.131.52. Importing from OracleProblemSqoop can’t find any columns when importing data from Oracle. For example, you seethe following exception:java.lang.IllegalArgumentException: Attempted to generate class with no columns!SolutionMake 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. Inaddition, if a different user created the transferred table, you will need to specify thisuser in the --tableparameter in the form user.table_name. For example, to importtable citiescreated by user kathleenfrom Oracle using user sqoop, you would executethe following Sqoop command:sqoop import \--connect jdbc:oracle:thin:@oracle.example.com:1521/ORACLE \--username SQOOP \--password sqoop \--table KATHLEEN.cities68 | Chapter 7: Specialized Connectors
DiscussionThe Oracle connector uses the following catalog query for retrieving table structureinformation (number of columns, their names, and associated data types):SELECTCOLUMN_NAMEFROMALL_TAB_COLUMNSWHEREOWNER= ? ANDTABLE_NAME= ? ORDERBYCOLUMN_IDAs the equals operator is case sensitive, you must enter both the table name and ownerin the same way as is recorded in the database catalog.