Use ODBC to Connect Tableau to a Data Flow SQL Endpoint

1. In the global search bar, search for Data Flow and select Data Flow Service.
2. Select SQL Endpoints in the leftmost side navigation.
3. Select any SQL Endpoint from the list. The details page for that SQL Endpoint is displayed.
4. Under Connect, select Drivers.
5. Under ODBC, select Download ODBC driver.
6. Select 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. Select Drivers.
3. Under ODBC driver, select Show Details. A pop-up appears.
4. Select Show on the Host name string. The full Host name is displayed.
5. Select 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. Select User DSN or System DSN as appropriate.

3. Select Add.

4. Select Simba OCI Spark ODBC Driver.

5. Select 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. Select 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. Select 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 select Data Flow Service.
2. Select SQL Endpoints in the leftmost side navigation.
3. Select any SQL Endpoint from the list. The details page for that SQL Endpoint is displayed.
4. Under Connect, select Drivers.
5. Under ODBC, select Download ODBC driver.
6. Select Windows. The download begins.
7. When the download completes, extract the downloaded zip file.

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

1. Open the Details Page for the SQL Endpoint when it's active.
2. Select Drivers.
3. Under ODBC driver, select Show Details. A pop-up appears.
4. Select Show on the Host name string. The full Host name is displayed.
5. Select 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. Select User DSN or System DSN as appropriate.

3. Select Add.

4. Select Simba OCI Spark ODBC Driver.

5. Select 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. Select OK to save the settings.

   To use a token (browser-based)

   You can opt not to set up DFI Options entirely and only test the DSN (step iv), or select 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. Select OK to save the settings.
   4. To test the DSN, select Test.

8. Select OK to create the DSN.