How do I use an OpenSearch Service database with an ODBC driver to connect to Tableau?

Resolution

Note: The OpenSearch SQL OBDC driver is compatible with ODBC 3.51 and supports the following operating systems:

• Windows 10, Windows 11 32-bit, 64-bit
• macOS Catalina 10.15.4, Mojave 10.14.6 64-bit

Create an OpenSearch domain

Create an OpenSearch domain. You can use any version, but you must activate fine-grained access control (FGAC) on the domain that you create. Also, make sure that you can use the primary username and password to connect to the OpenSearch Dashboard.

See the following domain access policy:

{  
  "Version": "2012-10-17",  
  "Statement": \[  
    {  
      "Effect": "Allow",  
      "Principal": {  
        "AWS": "\*"  
      },  
      "Action": "es:\*",  
      "Resource": "arn:aws:es:{region}:{account-id}:domain/{domain-name}/\*"  
    }  
  \]  
}

Use the OpenSearch Dashboard URL to access the domain that you created, and then choose Add sample data. Any data that you add is available to visualize on Tableau.

Create an Amazon EC2 Windows instance

1. Create and configure an Amazon Elastic Compute Cloud (Amazon EC2) Windows instance.
2. Use an RDP client to connect to your Windows instance.
3. Download a valid browser from the Windows instance.

Install the ODBC driver

To install the OpenSearch SQL ODBC driver, run the following .msi:

opensearch-sql-odbc -->> windows-x64 --->> signed\_opensearch-sql-odbc-win64-1.1.0.1.msi

For more information, see Download & get started on the OpenSearch website.

Install the SQL plugin

Install the appropriate SQL plugin for the version of the OpenSearch domain that you use.

Note: SQL support is available on domains that run OpenSearch 6.5 or later. If you use Amazon OpenSearch Service, then you don’t need to manually install the SQL plugin.

Configure a DSN on Windows

Note: If you don't correctly follow these steps, then you might receive an error similar to the following one:

"Connection error: [Open Distro For Elasticsearch][SQL ODBC Driver][SQL Plugin] Connection error: Failed to establish connection to DB"

1. Open the ODBC Data Source Administrator.
2. Choose the System DSN tab, and then choose Add.
3. Add the OpenSearch SQL OBDC driver, and then choose Configure.
4. Enter the following values in the DSN Setup window:
   Host: Enter the domain endpoint. Start with https://.
   Port: 443.
   Auth: BASIC.
   username: Enter the primary username.
   password: Enter the primary password.
5. Choose ADVANCED OPTIONS, and then turn on SSL and Hostname Verification.
6. Choose LOGGING OPTIONS. For Log level, choose LOG ALL from the dropdown list, and then choose OK.
7. Choose TEST. If there are no errors, then you see a Connection Successful message.

Connect to Tableau

1. Install and open Tableau 2022.3.
2. In the navigation pane, choose Other Databases (ODBC).
3. For DSN, enter the name of the DSN that you configured in the ODBC Data Source Administrator.
4. Choose Connect, and then choose Sign in.

Tableau then connects with your OpenSearch domain. Choose the Search icon to view tabular data from your domain.