Unable to use hive jdbc-handler with SQL Server database

Question

I would like to define an external table in Hive metastore. This table should be external mapping to a SQL Server database table.

I'm using Dataproc in GCP to get all the hadoop stack including hive 3.1.2 (should support SQL Server).

I try using Beeline to create a table like this:

CREATE EXTERNAL TABLE Mytable
(
    ID INT,
    Name STRING,
    ts TIMESTAMP,
    IsHideSites TINYINT,
    DisplayName STRING
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type"="MSSQL",
"hive.sql.jdbc.driver"="net.sourceforge.jtds.jdbc.Driver",
"hive.sql.jdbc.url"="jdbc:jtds:sqlserver:///main",
"hive.sql.dbcp.username"="user",
"hive.sql.dbcp.password"="pass",
"hive.sql.dbcp.maxActive" = "1",
"hive.sql.query" = "SELECT t.ID,t.Name,t.TimeStamp,t.IsHideSites,t.DisplayName FROM Main.dbo.AdNetworks t"
);

But I get this error :

Caused by: org.apache.hadoop.hive.metastore.api.MetaException: org.apache.hadoop.hive.serde2.SerDeException
org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Error while trying to get column names: Unrecognized SQL escape 'limit' at line position 93.

at org.apache.hadoop.hive.metastore.HiveMetaStoreUtils.getDeserializer(HiveMetaStoreUtils.java:98) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.metastore.HiveMetaStoreUtils.getDeserializer(HiveMetaStoreUtils.java:77) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.metadata.Table.getDeserializerFromMetaStore(Table.java:289) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.metadata.Table.getDeserializer(Table.java:271) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:901) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:938) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4954) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:428) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:205) ~[hive- exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:97) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:2664) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:2335) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:2011) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1709) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1703) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:157) ~[hive-exec-3.1.2.jar:3.1.2]
at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:224) ~[hive-service-3.1.2.jar:3.1.2]

Is there someone that succeeded to map an hive table with a SQL Server table and can help?

Thanks

Unable to use hive jdbc-handler with SQL Server database

0 Answers