topic Re: Upload to Databricks via ODBC is very slow in SAS Programming

Upload to Databricks via ODBC is very slow

js5 — Fri, 05 Sep 2025 11:46:27 GMT

Hello,

we have been experimenting with uploading results generated with SAS to a Databricks SQL warehouse. The code looks as follows:

libname dbx odbc prompt="Driver={Simba Spark ODBC Driver};
Host=foo.cloud.databricks.com;
Port=443;
HTTPPath=/sql/1.0/warehouses/bar;
SSL=1;
ThriftTransport=2;
AuthMech=3;
UID=token;
PWD=baz;
Catalog=foofoo;
Schema=barbar;
DefaultStringColumnLength=32767"
dbcommit=10000
insertbuff=10000
readbuff=1000
dbcreate_table_opts="TBLPROPERTIES('delta.columnMapping.mode' = 'name', 'delta.checkpoint.writeStatsAsStruct' = 'false', 'delta.autoOptimize.optimizeWrite' = 'true')"
preserve_col_names=yes;

proc sql;
create table dbx.test as
	select * from test;
run;

The performance leaves a lot to be desired unfortunately. As an example, a dataset with 122 variables and 127 thousand observations takes between five and six minutes to upload, whereas it takes less than 20 seconds to upload to a Microsoft SQL Server DB (also via ODBC).

Is this expected? According to the documentation, Databricks can also be accessed via JDBC and Spark SAS ACCESS modules, but we unfortunately do not have them licensed. Are there any options which could improve ODBC upload performance? I tried increasing insertbuff and dbcommit further, but I get the following error when going above 12k:

ERROR: CLI execute error: [Simba][Hardy] (130) An error occurred while an INSERT statement which causes the driver to reconnect to the server.

Thanks for your help in advance!

Re: Upload to Databricks via ODBC is very slow

LinusH — Fri, 05 Sep 2025 12:39:51 GMT

There are a lot of parameters that can affect performance in such use case.

If few questions:

- Where does your comparison SQL Server reside (vs Databricks)?

- Have you tried to load this data by other means into Databricks? Locally (to rule out actual load problems), remotely (compare with SAS).

Some hints might be given if you active tracing:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

Re: Upload to Databricks via ODBC is very slow

Ksharp — Sat, 06 Sep 2025 08:42:24 GMT

Did you try to use PROC COPY instead of PROC SQL?

proc sql;
create table dbx.test as
	select * from test;
run;
------------>
proc copy in=work out=dbx;
select test;
run;

Re: Upload to Databricks via ODBC is very slow

SASKiwi — Sat, 06 Sep 2025 22:32:22 GMT

What is the download performance for the same table? If both are taking a similar amount of time then I suggest that it is your network connection to Databricks that is the problem.

Re: Upload to Databricks via ODBC is very slow

js5 — Mon, 08 Sep 2025 06:21:45 GMT

There is no meaningful difference when using proc datasets. This is likely explained by the fact that it still uses SQL in the background according to the ODBC trace.

Re: Upload to Databricks via ODBC is very slow

js5 — Mon, 08 Sep 2025 06:41:47 GMT

Download performance is not an issue. If it does not fail due to insufficient memory (I need to investigate why this happens), the entire table is downloaded in around 10 seconds.

Re: Upload to Databricks via ODBC is very slow

js5 — Mon, 08 Sep 2025 06:51:33 GMT

The architecture is somewhat complex due to historical reasons:

the library I am copying from is locaten on an on-prem network share
SAS runs on AWS EC2
SQL Server runs on an on-prem VM
Databricks runs on AWS

Creating the table directly in databricks (CTAS from the SQL Server table connected as catalog) takes two minutes. So still much longer than creating a SQL Server table, but nevertheless two to three times faster than creating it via ODBC.

Re: Upload to Databricks via ODBC is very slow

SASKiwi — Mon, 08 Sep 2025 21:04:56 GMT

In my experience tuning INSERTBUFF works for SQL Server but it looks like to be not the case with Databricks. Have you talked to the Databricks DBAs to see if they have any ideas about the slowness? It might be worth trying bulk loading to see if that helps.

Re: Upload to Databricks via ODBC is very slow

js5 — Wed, 10 Sep 2025 06:25:23 GMT

With lower insertbuff values the upload is even slower as DBX ends up creating one parquet file per row. I am following this up with our DBX contacts in parallel. Bulkload with ODBC is unfortunately only supported for SQL Server. We would need SAS/Access to Spark in order to be able to bulkload the data to DBX.

Re: Upload to Databricks via ODBC is very slow

SASKiwi — Wed, 10 Sep 2025 20:19:55 GMT

Have you tried tracking this issue to Tech Support? It would be worth doing so if you haven't already.

Re: Upload to Databricks via ODBC is very slow

js5 — Thu, 11 Sep 2025 12:02:45 GMT

I have now.

Re: Upload to Databricks via ODBC is very slow

Tom — Thu, 11 Sep 2025 15:41:37 GMT

When we migrated from Teradata to Redshift we found similar poor file transfer performance. In that environment it was easier to dump the data into CSV files and move them via S3 buckets and reload using Redshifts COPY FROM and COPY TO commands. We made SAS macros to automate the process. Essentially the macro for upload would make an empty "table" in the remote database. Then dump the data into a gzipped CSV file (trivial with generic data step), move that file into S3 then use pass thru to issue the Redshift command to ingest the CSV file into the new table.

Does Databricks have a similar facility for ingesting files you could leverage for better performance?

Re: Upload to Databricks via ODBC is very slow

js5 — Fri, 12 Sep 2025 06:14:45 GMT

We could probably get this set up, but it would require considerably more adjustments than just changing which ODBC driver is being used by our ODBC libname. Right now, we have the SQL server set up as an external data catalogue in DBX, and we can just copy the data to corresponding databricks schemas using CTAS statements.

The goal of using ODBC to load to DBX directly would be to potentially eliminate the need for the SQL Server altogether. Having to go the S3 route would need to be analysed in terms of how much resources it would cost to set up.

Re: Upload to Databricks via ODBC is very slow

AhmedAl_Attar — Mon, 15 Sep 2025 17:58:19 GMT

Hi @js5

Have a look at this Databricks Blog

https://www.databricks.com/blog/2022/03/16/how-to-speed-up-data-flow-between-databricks-and-sas.html

Re: Upload to Databricks via ODBC is very slow

AhmedAl_Attar — Mon, 15 Sep 2025 18:44:27 GMT

One added information

SAS Help Center: BULKLOAD= LIBNAME Statement Option

Notes:	Support for Microsoft SQL Server, Spark in HDFS, and Yellowbrick was added in SAS 9.4M7.
Notes:	Support for Informix and for Spark in Databricks was added in SAS 9.4M9.

Re: Upload to Databricks via ODBC is very slow

js5 — Wed, 26 Nov 2025 14:33:24 GMT

Since we did not have SAS/Access to Spark licensed, I ended up implementing it myself. Performance is great in comparison. In brief:

create an empty table using implicit sql passthrough
export csv, formatting datetime and date to default spark formats
upload csv to S3
execute copy into statement on databricks warehouse using explicit sql passthrough

Re: Upload to Databricks via ODBC is very slow

belgeric — Wed, 11 Feb 2026 12:03:13 GMT

Hi,

As I se the thread is recent and just in case.

If you don't have SAS9.4M9 _and_ spark connector, I would use such approach but there are possible improvements: use parquet format.

And yes you can use parquet even with old SAS versions, via DuckDB -- look after dudckdb + ODBC

Once you have set up ODBC DSN you can use

proc sql; connect to odbc(dsn='DuckDB_Parquet'); /* This command tells DuckDB to write the SAS-linked table directly to a Parquet file */ execute ( COPY (SELECT * FROM main.final_output) TO 'C:\exports\data_output.parquet' (FORMAT 'PARQUET') ) by odbc; disconnect from odbc; quit;