Redshift webmodel custom 02-page-views-join-staged.sql table DDL uses CHAR instead of VARCHAR

[aidanradford]

Columns that are defined as VARCHAR in the main model are defined as CHAR in the custom table.

https://github.com/snowplow/data-models/blob/master/web/v1/redshift/sql-runner/sql/custom/02-page-views-join/01-page-views-join-setup.sql#L4-L9

This led to the following failure on deployment of the table: https://console.snowplowanalytics.com/03cb70fb-2221-40aa-a59e-512bcbc33617/jobs/datamodel-run/e135b8f28dd5d4f58fdde0519c52bb19f9e30a31567763a21a6aca254643a86c

[bill-warner]

Thanks for flagging Aidan. I can't seem to open the link relating the failure.

Was it the first_link_target column that was causing the issue? I believe this field is limited to 4096 characters in the link click schema vs. 2000 in this model which would explain the error.

Will look to update this in a future release. I should add that these example custom modules are not officially supported though. They are designed to illustrate how one might add custom modules, rather than to be used in production.

[aidanradford]

Hi Will,

Was it the first_link_target column that was causing the issue?
I can't see this specifically referenced in the error message, I assume it is all three of the columns created as CHAR in the DDL.

I've replicated the linked failure message below:

• Query 03-page-views-join-upsert /host-opt/mt-configs2/com.{{.client}}-datamodeling/sql-runner/sql/custom/02-page-views-join/03-page-views-join-upsert.sql (in step 03-page-views-join-upsert @ target com.{{.client}}-prod1 Redshift), ERROR:
  • ERROR #XX000 Invalid input:

  ---

  error: Invalid input
  code: 8001
  context: Only ASCII characters are allowed in fixed length strings. Invalid ASCII char: c3
  query: 116284368
  location: string.cpp:204
  process: query0_121_116284368 [pid=20418]

This error message is discussed on stackoverflow here: https://stackoverflow.com/questions/54155381/redshift-create-table-error-invalid-characters.

Understand that custom modules are not supported - I thought it would be best to let you know.