How to get Tableau connected to StarRocks

[alberttwong]

TL;DR: Not officially tested since it requires a license however use the MySQL drivers but with the TDC customization.

Since Starrocks is not fully compatible with MySQL, you can use Tableau data source customization (TDC) files to fine-tune the connection information, making the SQL emitted by Tableau more suitable for StarRocks , thereby improving performance. In addition, custom ODBC parameters can solve some compatibility problems.

TDC file must be suffixed with .tdc (the file name does not matter) and must be placed in the specified directory.

• Mac /Users/albert/Documents/My Tableau Repository/Datasources
• Windows C:\Users\albert\Documents\My Tableau Repository\Datasources

Save the following as a starrocks_odbc.tdc file in TDC specified directory.

<?xml version='1.0' encoding='utf-8' ?>
<connection-customization class='mysql' enabled='true' version='99.9'>
  <vendor name='mysql' />
  <driver name='mysql' />
  <customizations>
    <customization name='odbc-connect-string-extras' value='default_auth=mysql_native_password' />
    <customization name='CAP_QUERY_HAVING_REQUIRES_GROUP_BY' value='yes' />
  </customizations>
</connection-customization>

Why have those settings

1. ODBC-connect-string-extras is the ODBC parameter. The default authentication method in MySQL ODBC 8 is caching_sha2_password, which needs to be set to mysql_native_password.
2. When there is no dimension column and the index column is summed directly, the sql issued cannot hit the rollup table. CAP_QUERY_HAVING_REQUIRES_GROUP_BY can be optimized. the sql is as follows:

Unoptimized

SELECT SUM(`orders`.`sales`) AS `sum_sales_ok`
FROM `orders`
HAVING COUNT(1) > 0

Optimized

SELECT SUM(`orders`.`sales`) AS `sum_sales_ok`
FROM `orders`
GROUP BY 1.1000000000000001