Importing data from SQL Server to HIVE using SQOOP

0
votes

I am able to successfully import data from SQL Server to HDFS using sqoop. However, when it tries to link to HIVE I get an error. I am not sure I understand the error correctly

sudo -u hdfs sqoop import  \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true  \
--connect "jdbc:sqlserver://XX.XX.X.X:1433;instanceName=data-engr-sql-svr;  databaseName=AdventureWorks2019"  \
--username sa  \
--password XXXXXXXX  \
--driver com.microsoft.sqlserver.jdbc.SQLServerDriver  \
--warehouse-dir "/user/hive/warehouse/AdventureWorks2019.db"  \
--hive-import  \
--create-hive-table  \
--fields-terminated-by ',' \
--hive-table AdventureWorks2019.Production.TransactionHistory  \
--table Production.TransactionHistory  \
--split-by TransactionID  \
-- --schema Production

I don't know how to handle schemas, most of the tutorial uses a dummy database without proper schemas which are not helpful.

Error

21/03/31 08:52:47 INFO conf.HiveConf: Using the default value passed in for log id: 95e2b831-cfe5-4108-be0f-0df1d9a8797e 21/03/31 08:52:47 INFO session.SessionState: Updating thread name to 95e2b831-cfe5-4108-be0f-0df1d9a8797e main 21/03/31 08:52:47 INFO conf.HiveConf: Using the default value passed in for log id: 95e2b831-cfe5-4108-be0f-0df1d9a8797e 21/03/31 08:52:47 INFO ql.Driver: Compiling command(queryId=hdfs_20210331085247_050638e8-593a-4d01-8020-c40b7db8e66a): CREATE TABLE IF NOT EXISTS AdventureWorks2019.Production.TransactionHistory ( TransactionID INT, ProductID INT, ReferenceOrderID INT, ReferenceOrderLineID INT, TransactionDate STRING, TransactionType STRING, Quantity INT, ActualCost DOUBLE, ModifiedDate STRING) COMMENT 'Imported by sqoop on 2021/03/31 08:52:45' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054' LINES TERMINATED BY '\012' STORED AS TEXTFILE 21/03/31 08:52:49 INFO hive.metastore: HMS client filtering is enabled. 21/03/31 08:52:49 INFO hive.metastore: Trying to connect to metastore with URI thrift://cnt7-naya-cdh63:9083 21/03/31 08:52:49 INFO hive.metastore: Opened a connection to metastore, current connections: 1 21/03/31 08:52:49 INFO hive.metastore: Connected to metastore. 21/03/31 08:52:49 INFO parse.SemanticAnalyzer: Starting Semantic Analysis FAILED: SemanticException [Error 10255]: Invalid table name AdventureWorks2019.Production.TransactionHistory 21/03/31 08:52:49 ERROR ql.Driver: FAILED: SemanticException [Error 10255]: Invalid table name AdventureWorks2019.Production.TransactionHistory

1
sql-serverhadoophivesqoop

1 Answers

1
votes

There is no such thing as schema inside the database in Hive. Database and schema mean the same thing and can be used interchangeably.

So, the bug is in using database.schema.table. Use database.table in Hive.

Read the documentation: Create/Drop/Alter/UseDatabase