Hello everyone, I ran into a weird problem trying to connect Postgres to Bigquery using the default tap and target. In short, the JSON schema describing the data structure that tap-postgres creates is incompatible with target-bigquery and causes it to fail. The Postgres table has an array column, but since the Postgres spec doesn't distinguish between arrays of one, two or more dimensions, in the json schema the array type is mapped to a recursive definition that allows for infinitely nested arrays, eg [1, [1, [1, [1, ...]]]. The result is valid JSON schema. (pipelinewise-tap-postgres, discovery_utils.py, line 286). But then, target-bigquery does some simplifications on the JSON schema, and tries to expand all recursive ref's to their definitions. In this case this would result in a infinite loop so it throws a RecursionError and fails. (target-bigquery, simplify_json_schema.py, line 604) I can't really blame target-bigquery for not accepting infinitely recursive schemas, even if technically it's valid JSON schema. Tap-postgres also seems to have its reasons to generate such a weird scheme. But from what I can see this means that something as simple as a column of arrays will cause the transfer to fail. Has anyone ran into something like this before? Is there something I can tell tap-postgres to ensure it that the datatype is a simple 1D array and doesn't require any of the recursive stuff?

Is there something I can tell tap-postgres to ensure it that the datatype is a simple 1D array

You should be able to override the discovered schema for the column using the schema extra. https://docs.meltano.com/concepts/plugins#schema-extra

Thank you for the tip, I missed that and it definitely sounds like it could help. But I also couldn't get it to work so far. My meltano config ... list shows this:

Custom extras, plugin-specific options handled by Meltano:

`_schema.postgres_schema_name-table_name.column_name.type [env: TAP_POSTGRES__DBNAME__TABLE_NAME__SCHEMA_TABLE_NAME_IMAGES_TYPE] current value: 'array' (from

meltano.yml

)` `_schema.postgres_schema_name-table_name.column_name.type [env: TAP_POSTGRES__DBNAME__TABLE_NAME__SCHEMA_TABLE_NAME_IMAGES_ITEMS_TYPE] current value: ['null', 'string'] (from

meltano.yml

)` Which doesn't look bad, but when running the ELT the debug messages show that the schema being used isn't getting the override and I still get the same RecursionError. I'll try to check for syntax errors.

I'm facing a similar issue and haven't been able to override. Were you able to resolve it?

No, we didn't find a solution yet. Since we're still in an experimental phase we just removed the column with the array type form the source database. I don't know when we'll get back to this problem, but the first thing I'd try is running the tap standalone with

meltano invoke

or directly and seeing if the catalog override works.

That didn't work for me either but used a workaround by creating a view in postgres using string instead of array type.