SQL `NULL` read at column 1 (JDBC type Char) but mapping is to a non-Option type

For engineers
1

Hi, I setup the newest pipeline that is referencing an old PRE r35 Redshift database in a pipeline that used EMR for RDB loader. I believe the issue is with the schema, but I’m stuck.

I checked the post at RDB Loader: SQL `NULL` read at column 1 (JDBC type Char) and what jbrusch describes makes sense, but the table-migrate command was removed from igluctl because I believe RDB loader is now supposed to migrate tables automatically.

In the RDB loader logs, there’s nothing indicating an issue other than a stack trace right before this error:

Jun 3 22:50:02 ip-10-0-1-222 sp-rdbloader: doobie.util.invariant$NonNullableColumnRead: SQL NULL read at column 1 (JDBC type Char) but mapping is to a non-Option type; use Option here. Note that JDBC column indexing is 1-based.

How could I troubleshoot further?

2

Hi @pt-mike

Can you share any more of the stack trace? It might give me more of a clue where the error is coming from.

My initial guess is that this is that one of your tables is missing a table comment. The RDB loader uses comments to store the current iglu version of the table. It some point it runs a query like:

SELECT obj_description(oid)::TEXT
  FROM pg_class
  WHERE relnamespace = (
     SELECT oid
     FROM pg_catalog.pg_namespace
     WHERE nspname = <YOUR_SCHEMA>
  AND relname = <YOUR_TABLE>

I think if this query returns a null then it would give the error message you described. It might be worth checking if your tables’ comments to see if they have the iglu versions.

3

Hi @istreeter , that’s exactly the issue… when I added a table comment for version 1-0-0 of one of the schemas, RDB loader migrated the table w/o issue. Is there a requirement migration step to place missing comments on tables? I’m new to snowplow in the past two years and going through my first upgrade so it’s not clear if older version of snowplow automatically used comments in tables or if that was skipped through custom DDL scripts.

4

@pt-mike , the auto-migration feature of RDB Loader relies on the COMMENT of the table to guide it with table migration when a new patch version of the JSON schema is introduced. Moreover, the existing table has to have the columns created in the right order following the same logic RDB Loader would use.

The best is to run Igluctl to generate the SQL for each version of the schema locally and see which version the existing table corresponds to. Once the match is found you need to make sure the COMMENT of the table in Redshift reflects that version. Setting the comment could be done like this

COMMENT ON TABLE <table> IS 'iglu:<vendor>/<name>/jsonschema/<version>';

where <vendor>/<name>/jsonschema/<version> corresponds to the JSON schema in question.

However, if the number of properties match but their order in the current table is different from the order reflected in the generated SQL, you need to migrate the table manually to ensure the column order match first (and again the COMMENT reflects that version).

Once all that is in order, the automigration feature should work provided the following JSON schema versions are non-breaking.

1 Like
5

thanks @ihor for that explanation! I marked your response as the solution.

Also a note to other Snowplowers if you find comments are missing from a table and there’s no data in that table, it’s lower effort to delete the table and let RDB loader recreate it and confirming the schema version for that table in the comment.

In a couple of my cases, I had to manually migrate the tables with alter statements. Some of my preexisting columns were CHAR (for whatever reason, I don’t know) and I had to manually change column types to VARCHAR.

2 Likes