Issues using custom transformation

Connector Questions & Issues
1

  • Is this your first time deploying Airbyte?: No

  • OS Version / Instance: Ubuntu

  • Memory / Disk: you can use something like 4Gb / 1 Tb

  • Deployment: Docker

  • Airbyte Version: 0.40.27

  • Source name/version:

  • Destination name/version:

  • Step: Durint custom transformation

  • Description: I’m trying to add a custom transformation, where the source is MySQL and the destination is SnowFlake. Since the DB encoding is binary (utf8mb4_bin) I saw (Source MySQL: support `utf8_bin` charset · Issue #9938 · airbytehq/airbyte · GitHub) that we will need to use a custom transformation. I followed the (excellent) guide, and now the custom transformation runs.
    The issue is that every modification I tried caused the transformation to fail.

This is the original code:

select
    {{ json_extract_scalar('_airbyte_data', ['id'], ['id']) }} as ID,
    {{ json_extract_scalar('_airbyte_data', ['email'], ['email']) }} as EMAIL
from {{ source('AIRBYTE_SCHEMA', '_AIRBYTE_RAW_LITEBOUND_USER') }} as table_alias
-- LITEBOUND_USER
where 1 = 1

I tried to modify to:

select
    {{ json_extract_scalar('_airbyte_data', ['id'], ['id']) }} as ID,
    {{ JSON_EXTRACT_PATH_TEXT(_airbyte_data, 'email') }} as EMAIL
from {{ source('AIRBYTE_SCHEMA', '_AIRBYTE_RAW_LITEBOUND_USER') }} as table_alias
-- LITEBOUND_USER
where 1 = 1

It failed with:
‘JSON_EXTRACT_PATH_TEXT’ is undefined.

I also tried:

select
    {{ json_extract_scalar('_airbyte_data', ['id'], ['id']) }} as ID,
 {{base64_decode_string(json_extract_scalar('_airbyte_data', ['email'], ['email']))}} as EMAIL
from {{ source('AIRBYTE_SCHEMA', '_AIRBYTE_RAW_LITEBOUND_USER') }} as table_alias
-- LITEBOUND_USER
where 1 = 1

but it failed with:
‘base64_decode_string’ is undefined.

Any suggestion on what kind of modification can I perform so that the data will be written decoded?

Thanks