[IMPORTANT] November 7, 2022: Redshift table migration for yauaa_context 1-0-4

Announcements Open Source Alerts
1

This notice is relevant for Snowplow users who use the yauaa enrichment and who load events to Redshift using the RDB loader.

At 09:00 UTC on 7th November 2022 we will publish version 1-0-4 of the yauaa_context schema to Iglu Central. The new schema increases the maximum allowed string length for some fields.

The Redshift RDB loader has a feature where it immediately migrates the yauaa context table, as soon as the new version is published. This means your loader might attempt to run a migration on 7th November 2022, to alter columns for the new string lengths.

We do not anticipate any problems for Snowplow pipelines. We have designed the new schema so that all versions of RDB loader should handle this migration easily.

But from an over-abundance of caution we are providing here the migration commands, in case you need to run them manually. For example, if you suspect you are missing some events, or if you see error messages in the RDB loader logs about a failed migration. In the unlikely event that your RDB Loader has problems migrating the table, these are the sql commmands that should get your loader unstuck.

Please do not attempt to run these manually before the 7th November release. It is safer to let the RDB loader run the migration for itself.

COMMENT ON TABLE atomic.nl_basjes_yauaa_context_1 IS 'iglu:nl.basjes/yauaa_context/jsonschema/1-0-4';
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN device_name TYPE VARCHAR(256);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN device_brand TYPE VARCHAR(128);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN devic_cpu TYPE VARCHAR(128);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN device_cpu_bits TYPE VARCHAR(128);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN device_firmware_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN device_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN operating_system_name TYPE VARCHAR(256);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN operating_system_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN operating_system_name_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN operating_system_version_build TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN layout_engine_name TYPE VARCHAR(256);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN layout_engine_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN layout_engine_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN layout_engine_name_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN layout_engine_name_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN layout_engine_build TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_name TYPE VARCHAR(256);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_name_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_name_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_build TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_language TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN agent_language_code TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN webview_app_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN webview_app_name_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN i_e_compatibility_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN i_e_compatibility_version_major TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN i_e_compatibility_name_version TYPE VARCHAR(1000);
ALTER TABLE atomic.nl_basjes_yauaa_context_1 ALTER COLUMN i_e_compatibility_name_version_major TYPE VARCHAR(1000);
2 Likes
3

For users of the Snowplow-web dbt package who use the yauaa enhancement, you may have issues (very long run times, struggle to complete) with the next run of your dbt if your web page views table is very large due to the way that dbt tries to handle changes in column types. To avoid this you can run the following code to alter the columns yourself and avoid dbt having to do it for you, replacing <derived_schema> with your schema name.

ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN device_name TYPE VARCHAR(256);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN device_brand TYPE VARCHAR(128);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN device_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN operating_system_name TYPE VARCHAR(256);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN operating_system_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN operating_system_name_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN layout_engine_name TYPE VARCHAR(256);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN layout_engine_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN layout_engine_version_major TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN layout_engine_name_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN layout_engine_name_version_major TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN agent_name TYPE VARCHAR(256);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN agent_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN agent_version_major TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN agent_name_version TYPE VARCHAR(1000);
ALTER TABLE <derived_schema>.snowplow_web_page_views ALTER COLUMN agent_name_version_major TYPE VARCHAR(1000);
2 Likes