Import Mysql table into Hive

Hello,
I have been following the https://www.youtube.com/watch?v=S4gt5lO8W70&index=18&list=PLf0swTFhTI8rJvGpOp-LujOcpk-Rlz-yE video.
I have use below command to avoid NULL records using --append argument, But getting error

sqoop import --connect “jdbc:mysql://quickstart.cloudera/retail_db” --username retail_dba --password cloudera --table MyFamily --hive-import --hive-table myfamily --append -m 1

Error:

Warning: /usr/lib/sqoop/…/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/01/27 13:19:10 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.8.0
17/01/27 13:19:10 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/01/27 13:19:10 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
17/01/27 13:19:10 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
Append mode for hive imports is not yet supported. Please remove the parameter --append-mode

Then i tried with the below command:

sqoop import --connect “jdbc:mysql://quickstart.cloudera/retail_db” --username retail_dba --password cloudera --table MyFamily --hive-import --hive-table myfamily --fields-terminated-by , --lines-terminated-by \n --append -m 1

Still getting error like append mode is not supported to hive
Error:

Warning: /usr/lib/sqoop/…/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/01/27 13:21:29 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.8.0
17/01/27 13:21:29 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
Append mode for hive imports is not yet supported. Please remove the parameter --append-mode

Can anyone please resolve the issue with better approach to append data from mysql into hive.

Thank you,
Chandana

Hi,

Yes, Hive import does not support the --append statement. Try using the below statements to append in HIve:

–incremental append
–check-column
–last-value

Thanks

Thank you santy, I totally forgot about --incremental argument. But one doubt is not yet cleared can you help me. Now i have ued the below command

sqoop import --connect “jdbc:mysql://quickstart.cloudera/retail_db” --username retail_dba --password cloudera --table MyFamily --hive-home /user/hive/warehouse --hive-table myfamily --hive-import --incremental append --check-column ID --last-value 2 --fields-terminated-by \u0001 --lines-terminated-by ‘\n’ -m 1 --outdir java_files

Input File From MySQL: fields terminated by \u0001 and lines terminated by \n

ID NAME
1 Clothes
2 Pens
3 Soaps
4 Pencils
5 Eraser

Already existing file in Hive: fields terminated by \u0001 and lines terminated by \n

ID NAME
1 Clothes
2 Pens
3 Soaps
4 Pencils
5 Eraser
6 null

Output File: fields terminated by \u0001 and lines terminated by \n, --last-value 2

1 Clothes
2 Pens
3 Soaps
4 Pencils
5 Eraser
6 null
Null Null
Null Null
Null Null

In the command i have given exact fields and lines terminations but the output showed up with Null Values. Do you have any idea?

Thank you
Chandana

@chandana204 : try to give the last value as 0 or 6 instead of 2. Documentation suggests that --last-value should be the last value of the primary key in the source table (in your case mysql) from the previous import.
case 1: If your hadoop table doesn’t have any data and your are importing first time then --last-value is 0
case 2: If your hadoop table had already been imported with the data, then the --last-value will be the maximum primary key from previous import. In your case it is 6.

I think --last-value is not the issue here. Because i have used before to import file into hdfs with --last-value 2 using append. I got successful output with the expected records. In this scenario my problem with the formatting arguments. Already existing file has --fields-terminated-by \u0001 , --lines-terminated-by \n. So i have used same parameters in the command also but the output records were inserted with Null value.

Hi guys, I have been waiting for solution for this issue. If anyone has idea please respond to this query.

Thanks

Hi,

Please use –fields-terminated-by '\001’. The ‘\001’ will internally be converted to ‘\u0001’.

Thanks.