New DB (Labs) nodes and SAS JDBC

KNIME Extensions
1

So I’ve been attempting to use the new DB (Labs) nodes with SAS JDBC.

I am pleased to report I can connect & for the most part retrieve metadata, however the method KNIME is using to retrieve data is problematic with SAS.

SELECT * FROM (SELECT * 
from SCHEMA.TABLE
) tempTable_270228094122408981 LIMIT 10

Sharenet.S589.ex.txt: Method not supported.

I’m sure it is the wrapper & / or the LIMIT keyword which are problematic.

Additionally some JDBC parameters seem to be ignored, for example ‘remarks=false’ forces SAS to provide the column names & not column labels with the metadata being fetched, however KNIME reports errors if this is set. Without it set it will pull metadata if the columns are labeled (have descriptions in SAS).

I can work around the metadata issue, however the real problem is the wrapped SQL. Is there any way to configure KNIME to not wrap the SQL it sends to SAS?

Thanks in advance,
Burke

2


Could be more adequate for your task.

3

Thanks for the suggestion, I’ve used the SAS7BDAT reader node, which is fine for smaller datasets but not ideal for my particular use case.

4

Just to follow-up on this for anyone interested.

For now we are utilizing a python script & odbc to pull data out of SAS into KNIME, however would really like to eventually be able to leverage native KNIME DB (labs) nodes.

#####################################################
import pyodbc
import pandas as pd

cnxn = pyodbc.connect('DSN='+flow_variables['sas_odbc_dsn']+';UID=SAS_USERNAME;PWD=SAS_PASSWORD',autocommit=True) 

sql = flow_variables['sas_sql']

output_table = pd.read_sql(sql,cnxn)

cnxn.close()
1 Like