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

aidanradford commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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.

snowplow / data-models

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

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]