Load data from MS SQL table to snappyData

Question

I am using Tibco ComputeDB, which is new to me. It uses sparkDB and snappyData. I want to add data from MS SQL to in memory table of snappyData.

I can read data from CSV and load that in snappyDaya with below command.

=> CREATE EXTERNAL TABLE IF NOT EXISTS AirlineData USING csv OPTIONS(path '/home/ubuntu/Downloads/airline-data-small/*.csv')

Now same way I want to read data from MS SQL and want to add it in snappyData but not able to find proper way to do it. I followed below documentation and able to connect to MS SQL server and display data using spark/scala. But didn't find way to add it in snappyData.

=> https://learn.microsoft.com/en-us/azure/databricks/data/data-sources/sql-databases

There is also documentation which shows how to do it for MySQL but not for MS SQL.

=> https://snappydatainc.github.io/snappydata/howto/load_data_from_external_data_stores/

Came across below link and made changes for sqlserver but getting error.

=> How can I get external table jdbc url in SnappyData

For SQL Server:-

create external table Test_1 using jdbc options(url 'jdbc:sqlserver://server:port;database=dbname;user=username;password=pswd', driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver',dbtable 'CERTIFICATES');

Error:-

ERROR 42000: (SQLState=42000 Severity=20000) (Server=localhost/127.0.0.1[1528] Thread=ThriftProcessor-0) Syntax error or analysis exception: com.microsoft.sqlserver.jdbc.SQLServerDriver

score 3 · Accepted Answer · answered Feb 05 '20 at 09:09

Looks like JDBC driver jar for SQL server is not added to the classpath.

In order to do that you will have to deploy JDBC driver jar of SQL server using the following SQL command:

deploy jar <unique-alias-name> 'path-to-jar'

Check this link for more details: https://snappydatainc.github.io/snappydata/reference/sql_reference/deploy_jar/

The latest JDBC driver can be downloaded from here: https://www.microsoft.com/en-US/download/details.aspx?id=11774

After deploying jar try creating the external table and it should work. Tested with the following query with Microsoft SQL Server 2016:

CREATE  EXTERNAL TABLE external_table USING jdbc OPTIONS (dbtable 'T1',driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver',user 'sqldb', password 'pw',url 'jdbc:sqlserver://sql-server-host:1435;DatabaseName=testdatabase');

Load data from MS SQL table to snappyData

1 Answers1