How to configure an ODBC Datasource to DataBricks with Simba Spark ODBC Driver?

How to configure an ODBC Datasource to DataBricks with Simba Spark ODBC Driver?

FIRST CREATED ON 24 Jan 2025  I  AUTHOR Emma Camacho


CONNECTION STRING
For this Connection String, you can start with this example, but you can add more settings. 
Driver=Simba Spark ODBC Driver; Host=xxxx.azuredatabricks.net; Port=443; HTTPPath=/sql/1.0/warehouses/xxxx; SSL=1; ThriftTransport=2; AuthMech=3; Database=xxxx; UID=token; PWD=*****;Schema=your_schema;Catalog=your_catalog;
or
Driver=Simba Spark ODBC Driver; Host=xxxx.azuredatabricks.net; Port=443; HTTPPath=/sql/1.0/warehouses/xxxx; SSL=1; ThriftTransport=2; AuthMech=11; Auth_Flow=1; Auth_Client_ID=<client id>; Auth_Client_Secret=<secret>;Auth_Scope=all-apis;Schema=your_schema;Catalog=your_catalog;

Alert
Note: In order to use the Datasource Browser in M&D Designer and Tasks, the Schema and Catalog should be included in the Connection String and the following queries should include the where clause with the table_schema.

FIELD QUERY SQL
For this field, try the following query and add the where clause you need:
SELECT Distinct  table_name as TABLE_NAME, column_name as COLUMN_NAME FROM information_schema.columns where table_schema='<your_schema>'

TABLE QUERY SQL
For this field, try the following query and add the where clause you need:
SELECT Distinct  table_name as TABLE_NAME FROM information_schema.tables where TABLE_TYPE = 'MANAGED' and table_schema='<your_schema>'

VIEW QUERY SQL
For this field, try the following query and add the where clause you need:
SELECT Distinct  table_name as TABLE_NAME FROM information_schema.tables where TABLE_TYPE = 'VIEW' and table_schema='<your_schema>'

For more in information, see 

Info
Looking for Additional Guidance?
If you require more detailed information or further assistance, please visit our comprehensive Online Documentation. Our resources are designed to help you navigate all features and functionalities effectively.