Issue with MemSQL 6.7 and Tableau Connector

Help Connectors and Pipelines
1

Re-posted from #memsql-public-chat since this is a big question.

Hi Everyone

I am having a couple of issues with Tableau and MemSQL 6.7. I will post as separate messages to facilitate separate threads. One issue is with the built-in MemSQL connector and the other is from a workaround we are trying using the MySQL JDBC connector with Tableau (Tableau recently started supporting JDBC connectors).

Issue when using Tableau & MemSQL connector: When our columns are stored as text in MemSQL, Tableau is truncating them to 8 characters. This problem goes away when we change the MemSQL column type to VARCHAR. We have a ticket open with Tableau but wondering if anyone else has seen this issue.

The other issue can actually be recreated outside of Tableau with a SQL client. Tableau is generated the following query which isn’t too special:

SELECT ADDDATE( DATE_FORMAT( `fact_surveys_columnstore`.`launched_at`, '%Y-%m-01 00:00:00' ), INTERVAL 0 SECOND ) AS `tmn_launched_at_ok`
FROM `fact_surveys_columnstore`
  INNER JOIN `dim_survey` ON (`fact_surveys_columnstore`.`survey_id` = `dim_survey`.`id`)
GROUP BY 1

It then complains about the type of tmn_launched_at_ok not being correct. If I run this query in SequelPro, I can indeed see that the type is Char whereas the expected return type for ADDDATE is DATETIME: SingleStoreDB Cloud · SingleStore Documentation

2

We received confirmation on the first issue that Tableau is working on a fix. Happy for any advice on a workaround, including via JDBC driver (and fixing above issue).

3

We have written the following query to bulk change columns types. Use at your own risk!

SET sql_mode = 'PIPES_AS_CONCAT';

SELECT 
  'ALTER TABLE ' || table_schema || '.' || table_name || ' MODIFY ' || column_name || ' VARCHAR(21844);'

FROM information_schema.columns WHERE data_type = 'text';
4

You can also work around the truncation issue in Tableau by setting CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS to ‘yes’ in Tableau.

5

Additionally, the return type of ADDDATE is expected to be char when the input is not a temporal type. Can you describe the error you are seeing in Tableau for this case? I have seen similar queries issued by Tableau run successfully with MemSQL.

6

It was a JDBC originated "Unexpected Type" for tmn_launched_at_ok or similar; that was the error.

The MemSQL documentation does not specify different return types for the function, so it’s probably worth noting in the docs.

How / where do I set CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS?

7

CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS should be set on the .tdc file for the connection Tableau makes. This page describes how to change this: Example: Customize an ODBC Connection - Tableau.

2 Likes