How to decrypt data from mySQL in Google Data Studio Custom Query

Question

I'm creating a GDS Dashboard using data from a MySQL DB.

There are some fields in the DB that use AES ENCRYPTION and I'd like to decrypt them only in GDS so I CAN'T create a view on MySQL DB with the decrypted data, cause I can't leave the decrypted data on the DB.

I used to create views in my MySQL DB like this:

CREATE VIEW ViewName AS SELECT table1.field1, AES_DECRYPT(table1.field2, "password") FROM table1

Then I used to create a GDS mySQL data source selecting the entire view, LEAVING THE DECRYPTED DATA ON THE DB and using it on GDS.

Later on I found out that I can use GDS custom query to connect to mySQL DB, so I tried as follow:

SELECT table1.field1, AES_DECRYPT(table1.field2, "password") FROM table1

As a result I got this field in GDS and the data are not decrypted:

GDS Fields

AES_DECRYPT function seems not to be supported by GDS custom query, is there any way to decrypt data only in the GDS data source? Is there any documentation about GDS custom query functions?

Thanks

Answer 1

If you're storing the encrypted data as binary in the DB, try casting since Google Datastudio supports only text as data type (ref. https://support.google.com/datastudio/answer/9514333?hl=en):

SELECT table1.field1, CAST(AES_DECRYPT(table1.field2, "password") AS Char) FROM table1