KB485937: Dremio Connection Best Practice

Install the Driver

To complete the data source set up, you need to install additional components.

1. Navigate to the driver provider and download latest JDBC driver.
2. Save the driver file in the JDBC installation locations. Default path is listed below.

Windows: C:\Program Files (x86)\Common Files\MicroStrategy\JDBC
Linux: /opt/mstr/MicroStrategy/install/JDBC
 

Create Connection

To create connection against Dremio, the best practice is to use Workstation, this will help us create a JDBC based connection to Dremio.

1. In Workstation, after connect to your environment, click on “Data Sources” add button

1. Select Dremio

1. Create Data Source
   
   Input Name, Description field. And choose Projects from drop-down list to assign these Data Source to projects.

 
In Default Database Connection field, you can choose existing connection or “Add new database connection”. For adding new connection, assign a name to the connection and enter the Host Name, Port Number.). 
Best practices: 

• If we want to choose the schema, we can add schema parameter into Advanced tab. We can also add other connection parameter in Advanced tab, the supported parameter could be got from the page .
• Here, we connect directly to Dremio coordinator node, not with ZooKeeper.

 
In Database Login page, enter Username/password.
 

 
You can click “Test” button to test out the connection, then click “Save”.
 

Create Data Import Cube

To create Data Import Cube against Dremio, the best practice is to use Workstation.
 

1. “Enable New Data Import Experience” in the “Help” tab.  

1. In workstation, click “Add” button on Datasets.

1. Choose “Data Import Cube” type of dataset and click OK.

1. On the left-side Data Catalog panel, click on the Data Source created in section 2 Create Connection, list of available schema will show up, and you can select one or more schemas, then click “Connect”. 

1. A list of available tables will show up in the left panel, double click on the table to add it into Cube.

Use Dremio for Schema Modeling

To create schema object against Dremio, the best practice is to use Workstation.
 

1. In Workstation, connect to your environment, and select “Schema” in left panel.

1. Double Click on the Project you want to edit schema. On the left panel “Data Catalog”, all the Data Sources assigned for the project will show up. 

1. Click on a Dremio Data Source, a list of namespaces will be listed. Select the namespaces you want to connect.

1. All tables in the namespace will be showed up. Right-Mouse-Click the table, it will be added into schema.

1. Click “Save and Update Schema” button on the upper-left corner of Schema Editor.
2. We can create Schema Objects based on the newly added table.

Unsupported functionality/Limitations

• Driver has not shipped
• MTDI Web and Old Dataset workflow CANNOT create or see the Dremio DB connection
• Parameterized Query is not supported by Dremio.
• OAuth Authentication Method for JDBC driver is not supported currently.
• SSL connection is not certified.