--hive-overwrite \ --input-null-string='' not working


#1

In my practice below, I want to overwrite an existing hive and also replace null with blank ‘’

Original hive table:
image

sqoop import -m 1
–connect jdbc:mysql://ms.itversity.com/retail_export
–username=retail_user
–password=itversity
–table=departments_hive01
–hive-import
–hive-table retail_db.departments_hive01
–hive-overwrite
–input-null-string=’’

result:
image

As you can see from the screenshots, there is no any difference before and after the sqoop import.

Why? How do I fix it?

Thank you very much.


#2

Hi …
Use --null-string ‘’ instead of input-null-string…
For import we have to use --null-string
For export we have to use --input-null-string


#3

Thank you.

This is so annoying:
sqoop import -m 1
–-connect jdbc:mysql://ms.itversity.com/retail_export
–-username=retail_user
–-password=itversity
–-table=departments_hive01
–-hive-import
–-hive-table retail_db.departments_hive01
–-hive-overwrite
–-null-string=’’

Error:
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Error parsing arguments for import:
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-connect
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: jdbc:mysql://ms.itversity.com/retail_export
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-username=retail_user
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-password=itversity
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-table=departments_hive01
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-hive-import
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-hive-table
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: retail_db.departments_hive01
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-hive-overwrite
18/02/02 09:57:42 ERROR tool.BaseSqoopTool: Unrecognized argument: –-null-string=

What went wrong here? I have carefully removed all the trailing space after ‘’


#4

This has become a more serious mess.

I tried to figure out why the correct argument is not working, here you can see the following is working:


sqoop import -m 1 --connect jdbc:mysql://ms.itversity.com/retail_export --username=retail_user --password=itversity --table=departments_hive01

Everything in one line.

However, if I break it down to several lines as I always did:
sqoop import -m 1
–-connect jdbc:mysql://ms.itversity.com/retail_export
–-username=retail_user
–-password=itversity
–-table=departments_hive01

You can see the errors come out again:

Would this be possibly linked to Cygwin? or the Lab itself? Does anyone else hve this issue too? Can someone please investigate the issue as quickly as possible to sort it out?

Much appreciated.


#5

try this below one

image


Lab is NOT working for me, can someone please sort it out ASAP? Thanks
#6

Thank you Vani, I actually doubted it that too and already tried get the code ready in Notepad first before I run them. No use, not working.


#7

Hi Vani,

Your suggestion works now. However, it is for string field only, what about I want to replace a null non-string field to a default value, in this case, avg_salary contains null value, how do I set it to 0?

Thanks.


#8

@paslechoix I used this sqoop command and it automatically replaces Null to 0 in integer fields.

sqoop import --connect jdbc:mysql://ms.itvexport --username=retail_user --password=itversity --table=balu_departments_hive01 --m 1 --hive-import --hive-table balutest1.balu_departments_hive01 --hive-overwrite --null-string=''

image


#9

Thank you BaLu, here is what produced from my side and apparently something went wrong despite I used the same command as suggested by you:

MySQL data:
mysql> create table paslechoix_departments_hive01 (department_id int, department_name varchar(20), avg_salary int);

mysql> insert into paslechoix_departments_hive01 select *, NULL from retail_db.departments;

mysql> select * from paslechoix_departments_hive01;
±--------------±----------------±-----------+
| department_id | department_name | avg_salary |
±--------------±----------------±-----------+
| 2 | Fitness | NULL |
| 3 | Footwear | NULL |
| 4 | Apparel | NULL |
| 5 | Golf | NULL |
| 6 | Outdoors | NULL |
| 7 | Fan Shop | NULL |
±--------------±----------------±-----------+

Command:
sqoop import --connect jdbc:mysql://ms.itversity.com/retail_export --username=retail_user --password=itversity --table=paslechoix_departments_hive01 --m 1 --hive-import --hive-table paslechoix.departments_hive01 --hive-overwrite --null-string=’’

Hive table:
hive (default)> select * from paslechoix.departments_hive01;
OK
2 Fitness NULL
3 Footwear NULL
4 Apparel NULL
5 Golf NULL
6 Outdoors NULL
7 Fan Shop NULL
Time taken: 0.227 seconds, Fetched: 6 row(s)

I am wondering maybe it stems to mysql data, the “NULL” in salary (int) column is actually not NULL? but the column is int, if it is not NULL, how can I make its value NULL?

Thanks.


#10

try this below command:

sqoop import --connect jdbc:mysql://ms.itversity.com/retail_export --username=retail_user --password=itversity --table=paslechoix_departments_hive01 --m 1 --hive-import --hive-table paslechoix.departments_hive01 --hive-overwrite --null-string=’’
–null-non-string ‘0’


#11

Thank you Vani,

I know your suggestion is correct and should work, however, these days I have really been bothered by the environments (lab and maybe notepad++ or whatever reason):

So, I ended up typing the code in command line instead of preparing it in notepadd++ and then copy the whole script

And it works!

and here is the expected result:

Thank you for your help, any idea what would be the best way to prepare my script offline? It turns out when I copy stuff from web page, there are always something wrong like quote symbol, etc. There got to be some editor that won’t transform it.


#12

Use sublime text editor


#13

Thank you Vani.

Do you know what would be available in the exam? are we allowed to use our own favorite text editor like sublime?


#14

not sure … i think sublime text editor or vi editor will be available … anyone in this group who complete certificate please suggest … thanks


#15