Need help in identifying elasticsearch schema name while testing with the tableau desktop

[ravipalaparthi]

Hello team,

I followed the instructions that are available in https://github.com/opendistro-for-elasticsearch/sql-jdbc/blob/master/docs/tableau.md

I am able to connect but after selecting the database "elasticsearch". It is asking me to select or enter the schema name which I could not understand. Can you please let me know what should be the schema name for ES indexes.

Thanks In advance.

Regards, Ravi

[dai-chen]

@abbashus have you met this problem before?

[abbashus]

I have not encountered this issue before. This is the first time I am seeing this option to select Database and Schema; it should automatically list out all the tables.

@ravipalaparthi What Tableau Desktop version you are using? What is the ES version and OpenDistro SQL-JDBC version are you using?

[ravipalaparthi]

I am using the following versions:

• Tableau Desktop version 2018.3.13 (Windows 64 bit version)
• ES 6.6.2
• OpenDistro SQL JDBC opendistro-sql-jdbc-1.3.0.0.jar

[abbashus]

I used the following config

• Tableau Desktop version 2019.3.2 (MacOS version)
• ES 7.4.2
• OpenDistro SQL JDBC opendistro-sql-jdbc-1.3.0.0.jar (downloaded from here)

It automagically lists out all the tables for me.

This possibly looks like Tableau issue. Could you try changing the Tableau Version and see if it works, may be upgrade ES as well? Can you also share the Tableau Desktop logs with error/stack trace - you can find them in C:\Documents and Settings\user\My Documents\My Tableau Repository\Logs\jdbcserver.log for Windows.

[ravipalaparthi]

@abbashus trying with the suggested versions. will update you shortly.

[ravipalaparthi]

@abbashus As you said, installed

• Tableau Desktop version 2019.3.2 (Windows version)
• ES 7.4.2
• OpenDistro SQL JDBC opendistro-sql-jdbc-1.3.0.0.jar (downloaded from the suggested link)

This time it's failing to connect. Please find the jdbcserver.log jdbcserver.log

For reference, Attached elasticsearch.tdc elasticsearch.tdc.txt [Note: To attach this file, i renamed it to elasticsearch.tdc.txt"

[abbashus]

Hi @ravipalaparthi , if you have access to elasticsearch.log , you can see what SQL queries are being fired from Tableau. Can you share that as well? Need to reproduce from our end.

Can you also provide Index mappings for the indices in your cluster.

curl -XGET "http://localhost:9200/_mappings"

[ravipalaparthi]

Hi @abbashus Sorry for the delayed response. I just created a simple index with the below mapping and data

PUT /my-index { "mappings": { "properties": { "age": { "type": "integer" },
"email": { "type": "keyword" }, "name": { "type": "text" }
} } }

PUT /my-index/_doc/1 { "name": "John Doe", "age": 10, "email":"ravi@ravi.com" }

PUT /my-index/_doc/2 { "name": "Doe 2", "age": 10, "email":"ravi@ravi.com" }

PUT /my-index/_doc/3 { "name": "John 1", "age": 10, "email":"ravi@ravi.com" }

I Could not find the queries that were fired from Tableau. Attached are my ES logs ES logs.zip Can you please review them once and let me know if we are missing something.

Also, In Tableau Desktop 2019.3, I am not able to signin when I place the elasticsearch.tdc under C:\Users\<>\Documents\My Tableau Repository\Datasources but I am able to sign in, get the below view if I place the same file under C:\Program Files\Tableau\Tableau 2019.3\defaults\Datasources.

[ravipalaparthi]

Hello @abbashus it will be great if you can help us in addressing this issue.

[abbashus]

@ravipalaparthi I checked your elasticsearch.log , it does not have opendistro_sql plugin installed. The opendistro-sql-jdbc driver is compatible with opendistro_sql plugin . Instead it has got other proprietary plugins starting with x-. You can either build it locally or get it pre-packaged with dockerized OpenDisrto Elasticsearch version here

You can check opendistro_sql is installed by checking either

GET _cat/plugins?pretty

OR

hitting the _opendistro/_sql endpoint

curl -XPOST "http://localhost:9200/_opendistro/_sql/?" -H 'Content-Type: application/json' -d'{  "query" : "  SELECT * FROM my_index"}'

[ravipalaparthi]

Hi @abbashus As suggested I have downloaded odfe-1.3.0.zip, disabled HTPPS with opendistro_security.ssl.http.enabled:false, created "my-index" with mapping and sample data by login with admin/admin and tried to access the tables from the Tableau desktop but it's still asking me to select the schema.

More configuration details:

1. OpenDistro plugins

2. Access "my-index" data using SQL support

3. Tableau Desktop - Connect to ES using Other Databases (JDBC)

4. Tableau Desktop - After successful login; I was able to select the database "elasticsearch" and it's forcing me to select the schema name

5. Placed OpenDistro SQL JDBC driver at

6. Placed elasticsearch.tdc at

7. jdbcserver.log for reference jdbcserver.log

8. elastic search log for reference elasticsearch.log

Am I missing something? Please suggest.

I appreciate your help.

[ravipalaparthi]

Hi @abbashus @dai-chen and OpenDistroTeam, we are stuck with the above issue and looking for some inputs which can help us to resolve the schema selection.

[abbashus]

Hi @ravipalaparthi, please PM me on my public email ID to help you out offline. It's working correctly on our end. May be some Tableau version issue, or Windows issue.

[abbashus]

I see Tableau is firing below query resulting in connection error

CREATE TABLE `#Tableau___EAF_C_AE_A_B___E_A__F______C_DDBAEA__1_Connect_Chec` (
    `COL` INTEGER
    )

which implies either.tdc file is not being picked up or is not configured properly.

[jordanw-bq]

Hi @ravipalaparthi,

https://github.com/opendistro-for-elasticsearch/sql-jdbc/issues/48#issuecomment-577302621 In this comment at step 6, I noticed that you placed the TDC file under Documents\My Tableau Repository\Datasources\<version>. Can you move the file up to the parent folder and see if that helps? (Documents\My Tableau Repository\Datasources instead)

[ravipalaparthi]

Hi @abbashus, @jordanw-bq

As suggested, I moved the .tdc file back to C:\Users\<>\Documents\My Tableau Repository\Datasources and tried to access the ES from the Tableau desktop. This time, It did not through any errors (like CREATE TABLE .....) in ES log but after selecting the Database "elasticsearch" it's still looking schema details.

[jordanw-bq]

@ravipalaparthi Can you share the contents of your TDC file with us, so that we can confirm that the correct customizations are being set?

[ghost]

Hi @ravipalaparthi, I have noticed in one comment that you have saved customization file as “elasticsearch.tdc.txt”. Try renaming it to “elasticsearch.tdc”.

[ravipalaparthi]

@jordanw-bq Please find the attached TDC file.

elasticsearch.zip

@rupalmahajan yeah, I am using *.tdc only. To attach this file, I renamed it to elasticsearch.tdc.txt

[lyndonbauto]

@ravipalaparthi Could you please do the following:

1. Go to ( User )\Documents\My Tableau Repository\Logs (with Tableau closed)
2. Delete the existing logs
3. Open Tableau and connect with the JDBC driver to get to the point where the schema pops up
4. Open ( User )\Documents\My Tableau Repository\Logs\log.txt and post it here

We can review the log and look for issues there.

Thank you

[ravipalaparthi]

Hi @lyndonb-bq, I apologize for the late response. I came to the client's place, little busy with other stuff and could not find a chance to look at these updates. I will provide an update by the end of Monday.

[ravipalaparthi]

Hi @abbashus @dai-chen @jordanw-bq @rupalmahajan I just tried to connect the Opendistro ES from my tableau desktop and surprisingly it's working!!!

I did not make any changes

Thanks for all your support and help !!!

For future reference, I am just reposting my configuration details - Other Databases (JDBC)

Tableau Desktop with ES index details

Tableau Desktop Version:

TDC File Path:

TDC File: elasticsearch.zip No references of tdc files in any of the version-specific folders.

OpenDistro JDBC Driver path:

Opendistro ES Details:

ES Plugin Details

ES version details

[abbashus]

@ravipalaparthi Glad it worked out! Though it surprisingly worked, but we would want to know the root cause. If you find one, keep us posted.

[ravipalaparthi]

sure @abbashus Let me look at the opportunities to replicate the issue in one of my co-worker's work stations.