search

exasol / mysql-virtual-schema

Virtual Schema for connecting MySQL as a data source to Exasol
MIT License
2 stars 0 forks source link

binary16 columns #39

Closed djamadeus closed 1 year ago

djamadeus commented 1 year ago

some shopsystems like Shopware6 use binary16 columns to store UUIDs as IDs for all the entities and relations. these can be read as typical uuid-strings in mysql twe following way: LOWER(CONCAT( SUBSTR(HEX(id), 1, 8), '-', SUBSTR(HEX(id), 9, 4), '-', SUBSTR(HEX(id), 13, 4), '-', SUBSTR(HEX(id), 17, 4), '-', SUBSTR(HEX(id), 21) )) as id At the moment these columns just get left out when importing the shop-db as a virtual schema. is there anyway to make these columns availble as varchar-columns in the given format? or at least as varchar-columns containing the hex-display?

kaklakariada commented 1 year ago

@djamadeus thank you for your request! It is not possible to use the binary16 type at the moment. We will need to update the virtual schema to add support. I forwarded the feature request to product management and I will get back to you once they made a decision.

As a workaround you could create a view in MySQL that converts the binary16 column to varchar and use this view in the virtual schema.

kaklakariada commented 1 year ago

@djamadeus : I am sorry but we won't implement this because Exasol does not have a binary data type. I would recommend to create a view that maps the binary column to a varchar using the conversion you mentioned.

djamadeus commented 1 year ago

That's too bad, given that virtual schemas are usually used for ETL processes, and creating objects in the sources is usually not an option in ETL pipelines which run on read only access in 99% of the cases.