Hi all, it's the first time i'm trying to setup a Meltano instance. I'm using the default tap-mysql and target-bigquery. When i run the elt to load data, i'm getting 2 different issues with target bigquery: 1. If i have a datetime field i'm getting this message: JSON parsing error in row starting at position 0: Couldn't convert value to timestamp: Day 0-0-0 does not exist for timestamp: 0000-00-00 000000 Field: c_created_time 2. on another small table i did the load without issues. Then i tried to add a new field on mysql and when i run the elt i get this error message: Provided Schema does not match Table ...myDB-test_table. Cannot add fields (field: created_date) Any help here would be much appreciated, thanks.

For the first issue it sounds like you have some bad data in the source DB. MySQL, especially in older versions, allows "zero dates", which don't translate to other DBs very well. You may be able to use a mapper to fix that but if possible I think the best solution is to fix the source data (what does a date of 0000-00-00 000000 even mean, really?)

Hi @thomas_briggs thanks for the reply. It's a bit tricky as i don't control the db. I've found that the records that throw this error have the field null in the db. Is there a way to set a default value for this kind of situation on meltano?

@jose re: the zero dates - that's interesting. That sounds like a BQ issue, but I don't know anything about BQ. 😕

Hi @thomas_briggs thanks for the reply. I know some stuff about BQ, but this seems to be an issue in the source. I was trying to understand if in meltano there's a way to "transform" this data before it's streamed into BQ. Do you know if this is possible?

It should be possible using a mapper - see here. It doesn't sound like a source issue to me though, FWIW... if the value is null the tap will report that. I would try using target-jsonl instead of target-bigquery and looking at the output files; that'll give you a better sense of what the tap is sending. From there you'll (hopefully!) have a better idea whether the tap, the target, or the database is causing the null to be translated to 0000-00-00.

That seems a good idea and i'll give it a try.