Connections

Databricks

Every connection goes through the Databricks ODBC Driver (the older Simba Spark ODBC Driver also works). It must be installed on the machine or agent that runs the conversion.

Related pages:

Connection details

Connect using

  • Workspace: the workspace host, the SQL warehouse's HTTP path and a credential. New connections start in this mode.
  • ODBC: a DSN or a connection string of your own. Projects saved before the workspace fields existed keep this mode.

Host

The workspace URL, for example adb-1234567890123456.7.azuredatabricks.net (Azure) or dbc-a1b2c3d4-e5f6.cloud.databricks.com (AWS). You can paste the full browser URL; Omni Loader keeps only the host name.

HTTP path

The SQL warehouse's HTTP path, for example /sql/1.0/warehouses/1234567890abcdef. In Databricks, open SQL Warehouses, select the warehouse and copy it from the Connection details tab.

Authentication

  • Personal access token: a token created under Settings → Developer → Access tokens. The connection acts as that user.
  • Service principal (Databricks OAuth secret): the Client ID and an OAuth secret created for a service principal in Databricks. Works on every cloud. Omni Loader requests the sql scope, so a secret limited to SQL access is enough.
  • Service principal (Microsoft Entra ID, Azure Databricks only): the TenaClient Secret of an Entra ID app registration. The app must first be added tothe workspace as a service principal.
A service principal needs the **Databricks SQL access** entitlement and **CAN USE** on the SQL warehouse. See Unity Catalog setup for the catalog privileges.

Catalog

The Unity Catalog catalog to work in. If empty, the workspace's default catalog is used. Unity Catalog stores catalog, schema and table names in lower case.

ODBC mode

  • DSN: pick a DSN configured for the Databricks ODBC Driver, or type its name.
  • Connection string: a full driver connection string, for example Driver={…;Port=443;HTTPPath=…;SSL=1;ThriftTransport=2;AuthMech=3;UID=token;PWD=<personal access token>.
  • Username / Password: for a personal access token, the username is token and the password is the token. ## Schemas

Every Unity Catalog catalog has a schema named default. A table from a sourcefault, and a source's own default schema maps to it (SQL Server dbobecomesDatabricksdefault`).

Data types

Omni Loader reads Databricks column types from information_schema.columns, because the ODBC driver reports many of them as plain strings: STRING, TIMESTAMP_NTZ, intervals, ARRAY, MAP, STRUCT, VARIANT, GEOMETRY and GEOGRAPHY. Every column type a Unity Catalog table accepts on a current SQL warehouse is supported, including collated strings, VOID and all interval qualifiers.

Known limits

  • TIME, FILE and nanosecond TIMESTAMP(p) are Beta features that most SQL warehouses reject. They are not supported yet.
  • Decimals with more than 28 significant digits are rounded on the way to targets that read them through .NET decimals (SQL Server); a value that no longer fits its column fails the table's batch.
  • A 3-D geometry (POINT Z) does not load into SQL Server's geometry.
  • Dates and timestamps outside the years 0001–9999 cannot be read.
Previous
CSV

If this was useful,
our newsletter will be too.

Get monthly insights on data engineering, AI, and building critical infrastructure - direct from the Spectral Core team and CEO Damir Bulic.