Upload to Databricks via ODBC is very slow

[js5 · Accepted solution]

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

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

[belgeric]

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;