Update mysql record from hadoop

Question

I completed a process that read iTunes EPF file and insert those record in mysql data base table.

In which, before inserting the record I need to check whether the given record exist in data base or not.If the record not exist then I shall insert the record.But if the record already exist in data base then I need to update the corresponding record in mysql.

How to check the existence of record in mysql. Whether this check can be done in Mapper or Reducer class ..? As well as,if record exist how to update the record.

Answer 1

It's more robust solution to use staging tables to integrate data from different platforms. Quering the database for each record in map stage is not a good approach and don't provide consistence of data loading. So,

1. create table like target table (with 2 add fieds: 'load_ts', 'update_ts') in STG schema (or similar) in mysql,
2. populate it from MapRed Job (or Sqoop, ...),
3. use merge clause (or analog if not exists) to populate target mysql table

Answer 2

Use sqoop

First there should be primary key or unique key for table.
--update-key Anchor column to use for updates. Use a comma separated list of columns if there are more than one column

sqoop export --connect jdbc:mysql://localhost/test --username root --password root --table tempo --export-dir /input_test_files/sqoop_col -m 1 --update-key "unique_key_column" --update-mode allowinsert

--udate-mode specify how updates are performed when new rows are found with non-matching keys in database.
legal values are:
updateonly : only updates rows if they exist in database and ignores if they do not exist.
allowinsert : updates rows if they exist in the database already or insert rows if they do not exist.