Tableau: Quoted identifiers not supported

[jordanw-bq]

Using Docker image amazon/opendistro-for-elasticsearch:1.2.0
Dataset: Flights sample data (loaded through Kibana)
SQL Plugin version: 1.2.0.0
Tableau version: 2019.3

Tableau generated SQL (MySQL dialect): SELECT `kibana_sample_data_flights`.`Carrier` from `kibana_sample_data_flights` GROUP BY 1

Result:

java.sql.SQLException: Error executing query
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryRequest(StatementImpl.java:84)
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryX(StatementImpl.java:61)
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.execute(StatementImpl.java:170)
	at ElasticSearchJdbcClient.runElasticQuery(ElasticSearchJdbcClient.java:45)
	at ElasticSearchJdbcClient.main(ElasticSearchJdbcClient.java:27)
Caused by: com.amazon.opendistroforelasticsearch.jdbc.protocol.http.HttpException: HTTP Code: 400. Message: Bad Request. Raw response received: {
  "error": {
    "reason": "Invalid SQL query",
    "details": "no such index [`kibana_sample_data_flights`]",
    "type": "IndexNotFoundException"
  },
  "status": 400
}
	at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpResponseHandler.checkResponseForErrors(JsonHttpResponseHandler.java:83)
	at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpResponseHandler.handleResponse(JsonHttpResponseHandler.java:52)
	at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpResponseHandler.handleResponse(JsonHttpResponseHandler.java:45)
	at com.amazon.opendistroforelasticsearch.jdbc.protocol.http.JsonHttpProtocol.execute(JsonHttpProtocol.java:88)
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryRequest(StatementImpl.java:72)
	... 4 more

Expected Behaviour: The index name should be interpreted as kibana_sample_data_flights, rather than `kibana_sample_data_flights`.

Impact: Since this syntax is used for every Tableau query, all queries to the Elasticsearch data source will fail. This means that the JDBC driver cannot currently be used with Tableau.

[dai-chen]

@jordanw-bq I'm figuring out the workaround. One option is to remove back ticks so the broken original code can work as before. I wonder what name does Tableau expect present in the result set returned?

For example, SELECT `Carrier` from `kibana_sample_data_flights` : does Tableau expect Carrier or `Carrier` or both are okay?

And the same question for SELECT `kibana_sample_data_flights`.`Carrier` from `kibana_sample_data_flights` and SELECT `kibana_sample_data_flights`.`Carrier` AS `alias` from `kibana_sample_data_flights` . Is name with backticks or without backticks expected or both are okay?

[dai-chen]

Confirmed that quoted identifier is just to make the SQL query valid and be able to pass SQL parser. After that, it's up to us to keep it or not. Finally, what is returned to Tableau should be unquoted for display.

[kyotoYaho]

Hi @jordanw-bq, how could your tableau generate sql with backquote instead of doublequote? Is there any specific setting?

[jordanw-bq]

@kyotoYaho We connected to Tableau using the Other Databases (JDBC) option and selecting the MySQL dialect, which uses backticks (`) instead of double-quotes (").