Use ODBC to Connect Tableau to a Data Flow SQL Endpoint

1. In the global search bar, search for Data Flow and click Data Flow Service.
2. Click SQL Endpoints in the leftmost side navigation.
3. Click any SQL Endpoint from the list. The details page for that SQL Endpoint is displayed.
4. Under Connect, click Drivers.
5. Under ODBC, click Download ODBC driver.
6. Click Mac. The download begins.
7. When the download completes, extract the downloaded zip file.

/Library/simba/ocispark/lib/universal/libsparkodbc_sbu.dylib

1. Download the ODBC Manager from the download site.
2. Double-click the downloaded .dmg file.
3. Double-click ODBC Manager.pkg file.
4. Follow the prompts to install it.

1. Open the Details Page for the SQL Endpoint when it's active.
2. Click Drivers.
3. Under ODBC driver, click Show Details. A pop-up appears.
4. Click Show on the Host name string. The full Host name is displayed.
5. Click Copy on the Host name string. The full Host name is copied to the clipboard.
6. Save the Host name for later use.

1. Start ODBC Manager.

2. Click User DSN or System DSN as appropriate.

3. Click Add.

4. Select Simba OCI Spark ODBC Driver.

5. Click OK.

6. Enter the name of the DSN, for example, Data Flow SQL Endpoint and, optionally, add a description.

7. Create a Keyword Value pair for each DSN parameter.

8. Click Add to create a Keyword Value pair.

9. Select Keyword and replace it with the parameter name.

10. Select Value and replace it with the value for the parameter.

11. Repeat steps g to j for each parameter.
12. Add the connection authentication details:
    1. For connecting to a SQL Endpoint using an API signing key not at the default location ~/.oci/config, add the parameter:

       OCIConfigFile=ConfigFileLocation

       where ConfigFileLocation is the absolute path to the OCI configuration file to use with the connection.
       Optionally, set the OCIProfile field to the name of the OCI profile in the configuration file to use with the connection:

       OCIProfile=<profile_to_use>

       If no profile is specified, the connector try to use the profile named DEFAULT:

       OCIProfile=<profile_to_use>

    2. For connecting to a SQL Endpoint using a token (through the browser), omit the OCIConfigFile parameter from the DSN configuration.

13. Click OK to create the DSN.

1. If creating the first DSN in the empty odbc.ini file, create a section for ODBC Data Sources (for the first time only), by adding the following line to the odbc.ini file:

   [ODBC Data Sources]

2. For each DSN, specify the following:

   1. Under [ODBC Data Sources] specify the DSN name and ODBC driver name (which is Simba OCI Spark ODBC Driver), in the form, <dsn_name> = <driver_name>. For example:

      [ODBC Data Sources]dsn_name = Simba OCI Spark ODBC Driver

   2. Create a section for DSN configuration, by adding [dsn_name] in a new line at the end of the file.

      [dsn_name]

   3. Under the created DSN Section specify the directory path of the driver file, in the form:

      Driver = /Library/simba/ocispark/lib/universal/libsparkodbc_sbu.dylib

   4. Provide the connection parameters such as host, and config file. Those connection parameters you add to the DSN don't need to be specified in the ODBC connect string.

      • For connecting to SQL Endpoint using an API signing key (not at the default location of ~/.oci/config), add the parameter:

        OCIConfigFile=ConfigFileLocation

        where ConfigFileLocation is the absolute path to the OCI configuration file to use for the connection.

        Optionally, set the OCIProfile field to the name of the OCI profile in the config file to use for the connection. If no profile is specified, the connector tries to use the profile named, DEFAULT:

        OCIProfile=PROFILE_TO_USE

      • To connect to a SQL Endpoint using a token (browser-based), you can omit the OCIConfigFile parameter from the DSN configuration.
   5. Any other parameters are specified in the form of <parameter_name> = <value>. The following example shows an odbc.ini file that configures the data sources using the API key:

      [ODBC Data Sources]
      Data Flow SQL Endpoint = Simba OCI Spark ODBC Driver
      [Data Flow SQL Endpoint]
      Driver = /Library/simba/ocispark/lib/universal/libsparkodbc_sbu.dylib
      Description = Description of the connection
      Host = amaaaaaaezym32iajmhpmxybehc7plnvpxsmjhleiav37x5rm7pjx476ewqa.interactive.dataflowclusters.us-ashburn-1.oci.oc-test.com
      OCIConfigFile = /Users/username/.oci/config
      OCIProfile = OCIProfileToUse
      OCIIgnoreDriverNoPrompt = 1

3. Confirm that path of the OCI configuration file to use the API signing key for authentication is correct.
4. Validate the private key file path in the configuration.

1. In the global search bar, search for Data Flow and click Data Flow Service.
2. Click SQL Endpoints in the leftmost side navigation.
3. Click any SQL Endpoint from the list. The details page for that SQL Endpoint is displayed.
4. Under Connect, click Drivers.
5. Under ODBC, click Download ODBC driver.
6. Click Windows. The download begins.
7. When the download completes, extract the downloaded zip file.

1. Click Change....
2. Browse to the required directory.
3. Click OK.

1. Open the Details Page for the SQL Endpoint when it's active.
2. Click Drivers.
3. Under ODBC driver, click Show Details. A pop-up appears.
4. Click Show on the Host name string. The full Host name is displayed.
5. Click Copy on the Host name string. The full Host name is copied to the clipboard.
6. Save the Host name for later use.

1. Open the ODBC Data Sources App.

2. Click User DSN or System DSN as appropriate.

3. Click Add.

4. Select Simba OCI Spark ODBC Driver.

5. Click Finish.

6. Enter the name of the DSN, for example, Data Flow SQL Endpoint and, optionally, add a description.

7. For authentication, you can use an API signing key to connect to a SQL Endpoint, or you can use a token (browser-based) to connect to a SQL Endpoint.

   To use an API signing key

   1. Set OCI Config File to the absolute path to the OCI configuration file to use for the connection.
   2. Optionally, set the OCI Profile field to the name of the OCI profile to use for the connection. If no profile is specified, the connector tries to use the profile named DEFAULT.
   3. Check Ignore SQL_DRIVER_NOPROMPT.
   4. Click OK to save the settings.

   To use a token (browser-based)

   You can choose not to set up DFI Options entirely and only test the DSN (step iv), or click DFI Options and continue with the following steps:

   1. Set the OCI Config File field to a path that doesn't contain an OCI configuration file to use token-based authentication.
   2. To display a web browser used to complete the token-based authentication flow even when SQL_DRIVER_NOPROMPT is enabled, check Ignore SQL_ DRIVER_NOPROMPT.
   3. Click OK to save the settings.
   4. To test the DSN, click Test.

8. Click OK to create the DSN.