topic Re: SAS Viya 3.5 - WRITE EXTERNAL TABLE DATABRICKS in SAS Programming

SAS Viya 3.5 - WRITE EXTERNAL TABLE DATABRICKS

claudiodejesusa — Thu, 23 Nov 2023 13:19:09 GMT

Hi everyone,

I'm preparing scripts to create table on Databricks from SAS Viya 3.5 RH 7.9. Now I'm using the SAS/ACCESS TO JDBC which works Good about reading table from Databricks (tested by me), but I have problems with write table to Databricks. It's important to mention that the Physical space of Databricks Schema is ADLS2. I tried this with other database Hadoop with SAS/ACCESS TO HADOOP and have statement to work with EXTERNAL TABLE and EXTERNAL_LOCATION, but with the connector SAS-JDBC (Databricks), i can't use that statements.

@sasviya @sasprogramming

Please your help,

Thanks,

Claudio

This is my code:

Also permissions I have on the space

Re: SAS Viya 3.5 - WRITE EXTERNAL TABLE DATABRICKS

LinusH — Thu, 23 Nov 2023 15:19:27 GMT

A log would be nice, to understand what the problem is.

But if you take the SQL that is returned by your SASTRACE, and excute it within Databricks - what happens then?

Re: SAS Viya 3.5 - WRITE EXTERNAL TABLE DATABRICKS

claudiodejesusa — Thu, 23 Nov 2023 17:11:44 GMT

Sorry for not send the log. I understand that this error is because the privilege that I have on my blob storage is just CREATE EXTERNAL TABLE. The security area of my company choose that setting. What alternative does i have considering that i have connection with SAS/ACCESS JDBC to Databricks. When i try the options DBCREATE_TABLE_LOCATION, it is not supported by JDBC and got syntax error on SAS.

https://documentation.sas.com/doc/da/pgmsascdc/v_042/acreldb/p0hbggr9cfmadzn1px40rne6o59n.htm

thanks,

Claudio

Re: SAS Viya 3.5 - WRITE EXTERNAL TABLE DATABRICKS

claudiodejesusa — Fri, 24 Nov 2023 02:48:13 GMT

Hi everone,

Thanks for the help, i found the mistake on my code, i use the preffix https instead of abfss. I want to share the correct code to write a external table on databricks-adls2 with SAS Viya using JDBC Connector. You have to use POST_STMT_OPTS on Data-Step Statement.

Claudio