Quick Sight and BINARY data types

Q&A
, ,
1

Hi there,

I am trying to use Quick Sight to read data from an MySQL database of an application we are migrating to (version 6). We use Quick Sight already for the same application (version 5) and it works fine. There we have many dashboards and user count will grow the next months.

The problem with version 6, which I just discoverd now is, that they use UUIDs as database keys, stored in a BINARY field in all tables. Primary keys and foreign keys… Quick Sight will skip all those fields because of that unsupported datatype.

The result is a data set without primary and foreign keys. We can not create any JOINs.

Do you know a solution or workaround to still be able to use Quick Sight with version 6 of that application?

Thanks,
Dennis

2

Answering my own question here, as I found a solution that seems to work in my situation, which is, I need the UUIDs:

Instead of importing the tables directly, I decorate them in a custom SQL. Means I add a table and use a simple

SELECT * FROM order_line_item

as query and then add all the skipped fields that would contain a UUID like:

SELECT *, BIN_TO_UUID(id) AS _id FROM order_line_item

I do this with all the primary and foreign keys and now I am able to create the JOINs in Quick Sight that I need.

Edit: typo