Pulling json data from mysql jdbc connection

mskempri
Padawan

I am working on exasol version 6.2.5 and I am trying to import some data from mysql through a jdbc connection. I have tried the whole field (casting it as a char) or the path with a json_extract in mysql and casting it as either char or int, but I always get this error:

 

 

 

[ETL-1299: Failed to create transformator for column=20 (starting from 0 for selected columns) [ETL-1202: Not implemented - Transformation for this combination of column types is not possible in this version. A solution for this problem can be perhaps the conversion in another datatype in the database. Otherwise please contact support for additional information]]

 

 

I have also tried using a mysql function to return the json extract. The only thing I haven't tried is to create a temporary table in mysql with that data and try and pull it, but that would really defy the purpose of my ELT. Is there a way around this? Is this something that is solved in a later version of Exasol?

1 ACCEPTED SOLUTION

mskempri
Padawan

Thank you both for your input, I managed to get it to work, it seems I was mapping it to a wrong field within exasol. Once I tried getting a script to reproduce I noticed the error in my function. Can I go ahead and delete this post so that others don't get confused?

View solution in original post

5 REPLIES 5

mskempri
Padawan

Thank you both for your input, I managed to get it to work, it seems I was mapping it to a wrong field within exasol. Once I tried getting a script to reproduce I noticed the error in my function. Can I go ahead and delete this post so that others don't get confused?

View solution in original post

exa-MathiasHo
Community Manager
Community Manager

Hi @mskempri , 
In general, it is more confusing to delete posts. You did the right thing and reported your solution - thank you!

I'll set this as an accepted solution and everybody will see it. 

exa-Matze
Team Exasol
Team Exasol

Hi @mskempri ,

 

let me double check the cast. The error message states that is not possible to map the source to target data type.

Using a cast to char / varchar in the IMPORT (statement clause), and importing into an appropriate char / varchar column should do the job from my point of view. I will try to get a mysql database up to provide some snippets

exa-Matze
Team Exasol
Team Exasol

Hi @mskempri , 

I am sorry, but I did not manage to get my mysql DB running again. 
Maybe you can share (as @mwellbro suggested):

  1. DDL of mysql table (source)
  2. DDL of Exasol table (target)
  3. IMPORT statement

(in an anonymized way / simplified example)

 

mwellbro
Xpert

Hi @mskempri ,

could you provide the DDL for the table you are trying to import the data into ? That would give us at least the target data type that seems to be involved in this.
If you could additionally provide a snippet of the json then someone around here might be able to reproduce or solve this.

Without any such details I´m having a hard time making heads or tails of this - from what I remember jdbc normally behaves more reasonable when it comes to data types than for example a native Oracle connection but that´s not helping us in this case.

Looking forward to any details you can provide.


Cheers,
Malte