0

i am trying to import data to hive table using sqoop2. I am using --hive-import but it is not working

Code:

sqoop import --connect jdbc:sqlserver://192.168.x.xxx:11xx --username user --password user --table xxxx.NOTIFICATION --hive-import

Error:

ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'XXXX.NOTIFICATION'.

What am I doing wrong?

animal
  • 994
  • 3
  • 13
  • 35

3 Answers3

1

Below observations are based on Sqoop 1.4.6

you are using . (dot) in your table name.

Internally, Sqoop will fire command

SELECT t.* FROM xxxx.NOTIFICATION AS t WHERE 1=0

to fetch metadata of your SQL Server table.

This command is interpreted as

  • xxxx - schame name
  • NOTIFICATION - Table name

To avoid this you can use escape character ( [ ] in case of SQL Server):

sqoop import --connect jdbc:sqlserver://192.168.x.xxx:11xx --username user --password user --table [xxxx.NOTIFICATION] --hive-import

This will generate 

SELECT t.* FROM [xxxx.NOTIFICATION] AS t WHERE 1=0

Now xxxx.NOTIFICATION will be treated as table name.

Dev
  • 13,492
  • 19
  • 81
  • 174
  • xxxx.NOTIFICATION is my table name i tried using `--query` in my command and then it was working. I don't know what is the issue here – animal Aug 25 '16 at 10:19
  • `$ sqoop import --connect "jdbc:sqlserver://192.168.x.xxx:11xx;database=SSSS;username=user ;password=user " --query "SELECT * FROM XXXX.NOTIFICATION where \$CONDITIONS" --target-dir /user/cloudera/xxxxx -m 1` – animal Aug 25 '16 at 10:40
  • i tried this and i am getting `ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Unclosed quotation mark after the character string '[XXXX.NOTIFICATION] AS t WHERE 1=0'. ` – animal Aug 25 '16 at 10:58
  • @animal try my query put `--verbose` in the end of the command. share complete error logs – Dev Aug 25 '16 at 11:00
  • ok i'll share, but in my `--query` i am using databasename but in yours i am not seeing it – animal Aug 25 '16 at 11:04
  • @animal I guess your issue will be resolved if you add databaseName is your connect parameter. e.g. `--connect 'jdbc:sqlserver://192.xxx.xxx.xxx:1433;databaseName=BCHN'` – Dev Aug 25 '16 at 11:06
  • i added it still the same issue is there – animal Aug 25 '16 at 11:11
  • @animal is `xxxx` your database name ? or `xxxx.NOTIFICATION` is your table name only? – Dev Aug 25 '16 at 11:17
  • `xxxx.NOTIFICATION` is my table name. My database name is `SSSS` – animal Aug 25 '16 at 11:18
  • @animal am just interested to see : `Execute getColumnInfoRawQuery : SELECT t.* FROM [[XXXX.NOTIFICATION]] AS t WHERE 1=0 ` you can revert back your question as it has become lengthy. In your case it's already wrapped in [] . just tell me the query I mentioned after removing [ ] in table name – Dev Aug 25 '16 at 11:22
  • `sqoop import --connect 'jdbc:sqlserver://192.168.x.xxx:11xx;databaseName=SSSS' --username user --password user --table xxxx.NOTIFICATION --hive-import --verbose` – animal Aug 25 '16 at 11:25
  • @animal no I am asking for `Execute getColumnInfoRawQuery : SELECT t.* FROM [[XXXX.NOTIFICATION]] AS t WHERE 1=0` in case of above query – Dev Aug 25 '16 at 11:27
  • I just want you to run- `sqoop import --connect 'jdbc:sqlserver://192.168.x.xxx:11xx;databaseName=SS‌​SS' --username user --password user --table xxxx.NOTIFICATION --hive-import --verbose` and look into the logs shown on shell and find similar line `Execute getColumnInfoRawQuery : SELECT t.* FROM [[XXXX.NOTIFICATION]] AS t WHERE 1=0` – Dev Aug 25 '16 at 11:33
  • sorry for late reply ! i executed and i am getting `Execute getColumnInfoRawQuery : SELECT t.* FROM [XXXX.NOTIFICATION] AS t WHERE 1=0 ` – animal Aug 25 '16 at 12:31
  • @animal then it should work. query seems good. `com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'XXXX.NOTIFICATION'.` this error is coming. right? – Dev Aug 25 '16 at 12:36
  • i have resolved this issue and posted my answer. Thanks for your help. I upvoted for your immense help. Thanks a lot. – animal Aug 25 '16 at 14:43
1

Hi after doing a bit research and discussing on the question with @dev i found the solution.

I am using sqoop2 so i changed my command and used below one and it worked for me.

$ sqoop import --connect "jdbc:sqlserver://192.168.x.xxx:11xx;database=SSSS;username=user;password=user" --query "SELECT * FROM xxxx.NOTIFICATION where \$CONDITIONS" --split-by xxxx.NOTIFICATION.ID --hive-import --hive-table NOTIFICATION  --target-dir NOTIFICATION

before executing this command we should create table in hive using create command. Here i have created hive table named NOTIFICATION.

animal
  • 994
  • 3
  • 13
  • 35
0

I assume the table name is NOTIFICATION and you are trying to mention database name xxxx when you write --table xxxx.NOTIFICATION

If this is the case, can you please try the below mentioned syntax instead?

sqoop import --connect jdbc:sqlserver://192.168.x.xxx:11xx;databaseName=xxxx --username user --password user --table NOTIFICATION --hive-import
Sumeet Gupta
  • 198
  • 1
  • 13
  • No table name is `xxxx.NOTIFICATION` not `NOTIFICATION` – animal Aug 25 '16 at 10:10
  • @animal did you get to try this? sqoop import --connect 'jdbc:sqlserver://192.168.x.xxx:11xx;databaseName=SS‌​SS' --username user --password user --table xxxx\.NOTIFICATION --hive-import – Sumeet Gupta Aug 25 '16 at 11:27
  • yes i used it and i am getting `ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'xxxx.NOTIFICATION'. ` – animal Aug 25 '16 at 11:30