Skip to main content

When replicating tables from a MySQL data source you may run into the following error message: column "order_details" is of type bytea but expression is of type bigint.

Looking at the data type of the order_details column, we can see that it contains binary data (blob).

mysql_blob.png

In order to replicate a column to your analytical storage, Data Virtuality needs to map the blob data type to a corresponding native data type in your analytical storage. This normally happens automatically without the need to manually intervene. In the case of binary data, however, Data Virtuality cannot automatically infer the right data type and you will have to manually provide this mapping by doing a type casting.

In our example case, the column contains textual data, so we can convert it to a clob using the to_chars function and create a view with from that query:

 

CREATE VIEW my_data_model.orders AS
SELECT to_chars("order_details", 'UTF-8')
FROM "MySQL.adventure.orders";;

 

Now instead of replicating the raw table, we choose the view instead that has all columns correctly mapped to non-binary data types.

Be the first to reply!

Reply