[ https://issues.apache.org/jira/browse/SQOOP-3267?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16292487#comment-16292487 ]
Szabolcs Vasas commented on SQOOP-3267: --------------------------------------- Hi [~dvoros], Thank you for reporting this bug, this is a really nice catch! I have created a couple of test cases to clarify the issue. 1) Updating a column without history in HBase Let's create the test tables and import the initial values to HBase: {code:java} export MYCONN=... export MYUSER=... sqoop eval --connect $MYCONN --username $MYUSER --query "drop table hbase_test" sqoop eval --connect $MYCONN --username $MYUSER --query "create table hbase_test(id int primary key ,name varchar(30), date_modified DATETIME)" sqoop eval --connect $MYCONN --username $MYUSER --query "INSERT INTO hbase_test (id, name, date_modified) VALUES(1, 'name1', CURRENT_TIMESTAMP)" sqoop eval --connect $MYCONN --username $MYUSER --query "INSERT INTO hbase_test (id, name, date_modified) VALUES(2, 'name2', CURRENT_TIMESTAMP)" echo "disable 'hbase_test'" | hbase shell echo "drop 'hbase_test'" | hbase shell echo "create 'hbase_test', 'data'" | hbase shell sqoop import --connect $MYCONN --username $MYUSER --table "hbase_test" --hbase-table hbase_test --column-family data -m 1 echo "scan 'hbase_test'" | hbase shell scan 'hbase_test' ROW COLUMN+CELL 1 column=data:date_modified, timestamp=1513340516549, value=2017-12-15 12:17:26.0 1 column=data:name, timestamp=1513340516549, value=name1 2 column=data:date_modified, timestamp=1513340516549, value=2017-12-15 12:17:29.0 2 column=data:name, timestamp=1513340516549, value=name2 2 row(s) {code} The output of the HBase scan is the expected, let's update a column to null in the database and execute an incremental import: {code:java} sqoop eval --connect $MYCONN --username $MYUSER --query "update hbase_test set name=null, date_modified=CURRENT_TIMESTAMP where id = 1" sqoop import --connect $MYCONN --username $MYUSER --table "hbase_test" --hbase-table hbase_test --column-family data -m 1 --incremental lastmodified --check-column date_modified --last-value "2017-12-15 12:17:26.0" --merge-key id echo "scan 'hbase_test'" | hbase shell scan 'hbase_test' ROW COLUMN+CELL 1 column=data:date_modified, timestamp=1513340614595, value=2017-12-15 12:23:00.0 2 column=data:date_modified, timestamp=1513340614595, value=2017-12-15 12:17:29.0 2 column=data:name, timestamp=1513340614595, value=name2 2 row(s) {code} The output of the HBase scan is pretty much the behavior expected after SQOOP-3149, the name column is updated to null, so it "disappeared" from the output of the scan, if we check that column in HBase using the Java API we will get null value. So far so good. 2) Updating a column with history in HBase This test case is really similar to the previous one but first we update the name column to a non-null value: {code:java} export MYCONN=jdbc:mysql://`hostname`/test export MYUSER=sqoop sqoop eval --connect $MYCONN --username $MYUSER --query "drop table hbase_test" sqoop eval --connect $MYCONN --username $MYUSER --query "create table hbase_test(id int primary key ,name varchar(30), date_modified DATETIME)" sqoop eval --connect $MYCONN --username $MYUSER --query "INSERT INTO hbase_test (id, name, date_modified) VALUES(1, 'name1', CURRENT_TIMESTAMP)" sqoop eval --connect $MYCONN --username $MYUSER --query "INSERT INTO hbase_test (id, name, date_modified) VALUES(2, 'name2', CURRENT_TIMESTAMP)" echo "disable 'hbase_test'" | hbase shell echo "drop 'hbase_test'" | hbase shell echo "create 'hbase_test', 'data'" | hbase shell sqoop import --connect $MYCONN --username $MYUSER --table "hbase_test" --hbase-table hbase_test --column-family data -m 1 echo "scan 'hbase_test'" | hbase shell scan 'hbase_test' ROW COLUMN+CELL 1 column=data:date_modified, timestamp=1513335407294, value=2017-12-15 10:49:05.0 1 column=data:name, timestamp=1513335407294, value=name1 2 column=data:date_modified, timestamp=1513335407294, value=2017-12-15 10:49:09.0 2 column=data:name, timestamp=1513335407294, value=name2 2 row(s) sqoop eval --connect $MYCONN --username $MYUSER --query "update hbase_test set name='name_modified', date_modified=CURRENT_TIMESTAMP where id = 1" sqoop import --connect $MYCONN --username $MYUSER --table "hbase_test" --hbase-table hbase_test --column-family data -m 1 --incremental lastmodified --check-column date_modified --last-value "2017-12-15 10:49:09.0" --merge-key id echo "scan 'hbase_test'" | hbase shell scan 'hbase_test' ROW COLUMN+CELL 1 column=data:date_modified, timestamp=1513335602892, value=2017-12-15 10:58:44.0 1 column=data:name, timestamp=1513335602892, value=name_modified 2 column=data:date_modified, timestamp=1513335602892, value=2017-12-15 10:49:09.0 2 column=data:name, timestamp=1513335602892, value=name2 2 row(s) {code} Everything is expected so far, the output of the second HBase scan command shows us the updated name value. Now let's set that column to null and see what happens in HBase: {code:java} sqoop eval --connect $MYCONN --username $MYUSER --query "update hbase_test set name=null, date_modified=CURRENT_TIMESTAMP where id = 1" sqoop import --connect $MYCONN --username $MYUSER --table "hbase_test" --hbase-table hbase_test --column-family data -m 1 --incremental lastmodified --check-column date_modified --last-value "2017-12-15 10:58:44.0" --merge-key id echo "scan 'hbase_test'" | hbase shell scan 'hbase_test' ROW COLUMN+CELL 1 column=data:date_modified, timestamp=1513335804486, value=2017-12-15 11:01:35.0 1 column=data:name, timestamp=1513335407294, value=name1 2 column=data:date_modified, timestamp=1513335602892, value=2017-12-15 10:49:09.0 2 column=data:name, timestamp=1513335602892, value=name2 2 row(s) Took 0.4017 seconds {code} The output of the last scan shows the bug reported in this JIRA. We can see that the data:date_modified column is updated in row 1, but the value data:name column is not null but it is "reset" to its previous value. I think we all agree that the current behavior is not what we want the question is how we should address this bug. I am not a big fan of deleting the full history, since some users might rely on it, if it is possible we should put a null value to the HBase table as well. Regards, Szabolcs > Incremental import to HBase deletes only last version of column > --------------------------------------------------------------- > > Key: SQOOP-3267 > URL: https://issues.apache.org/jira/browse/SQOOP-3267 > Project: Sqoop > Issue Type: Bug > Components: hbase-integration > Affects Versions: 1.4.7 > Reporter: Daniel Voros > Assignee: Daniel Voros > Attachments: SQOOP-3267.1.patch > > > Deletes are supported since SQOOP-3149, but we're only deleting the last > version of a column when the corresponding cell was set to NULL in the source > table. > This can lead to unexpected and misleading results if the row has been > transferred multiple times, which can easily happen if it's being modified on > the source side. > Also SQOOP-3149 is using a new Put command for every column instead of a > single Put per row as before. This could probably lead to a performance drop > for wide tables (for which HBase is otherwise usually recommended). > [~jilani], [~anna.szonyi] could you please comment on what you think would be > the expected behavior here? -- This message was sent by Atlassian JIRA (v6.4.14#64029)