Adding support for PostgreSQL json and RedShift Super

[nicolasaldecoa]

Hello devs.

My coworkers and I have recently been looking for a tool to compare tables that are replicated in PostgreSQL and RedShift. We discovered data-diff and gave it a try a couple of weeks ago, and found it to be useful for some tasks. Nevertheless, we have a lot of columns that contain bjson data (json in PG schema and super in RS) which aren't supported data types,

In order to deal with that problem, I forked this repo and drafted a solution that is working for us at the moment.
I've observed that the default normalization (adding ::varchar ) to RS super column makes it return a NULL output, and in PG the json string contains a space after each colon.
These are the normalizations that I'm using to obtain a minified json string in both cases:

Postgre json (I'm aware that this expression doesn't cover all cases, I need to put a bit more work into this one)

def normalize_json(self, value: str, _coltype: PostgresqlJSON) -> str:
    return f"replace({value}::text, '\": \"', '\":\"')"

RedShift super

def normalize_super(self, value: str, _coltype: RedShiftSuper) -> str:
    return f'nvl2({value}, json_serialize({value}), NULL)'

We would love to stay up-to-date with the development of this tool, so I'd like to ask you if you could take a look at this commit in my fork and consider giving official support to these database types.

I'm not creating a PR because I haven't had the time to study the entire project, so my code is probably suboptimal from a design point of view. I'd really appreciate if you could help me out with that.

Thank you in advance, cheers,
-Nico

[erezsh]

Hi @nicolasaldecoa ,

We'll be very happy to add support for this.

I think your implementation is pretty good as is. The main thing I would change is instead of overriding normalize_value_by_type, I would change it in the base to redirect on JSONType, with a default behavior of NotImplementedError. (and normalize_super would become normalize_json, etc.)

If you would like to open a PR, I'll be happy to guide you in the process. The main thing that's missing are tests that show this actually works. i.e. that normalize a few JSONs in both redshift and postgres, and show that they return the same result.

[nicolasaldecoa]

Hi,
I created PR datafold/sqeleton#15. I left some comments on the research that I did and the tests that I've conducted. Feel free to message me, hope you can provide me with some guidance.
Cheers.

[github-actions]

This issue has been marked as stale because it has been open for 60 days with no activity. If you would like the issue to remain open, please comment on the issue and it will be added to the triage queue. Otherwise, it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment and it will be reopened for triage.