Documentation

Altering a Column in the Data Warehouse

Abstract

SummaryA description on how to change columns directly in the data warehouse via SQL and the resulting required changes to make via the Mapper.

If you need to alter columns in your data warehouse, you have two options. For some supported use cases, you can make the changes via the Mapper, and your changes will be reflected in the data warehouse. See Altering a Column via the Mapper for more information.

If you need to make other changes, or if you prefer to make changes outside of the UI, you can alter columns in your data warehouse directly via SQL and then manually update the mapping to match your changes, field-by-field in the Mapper (see below).

When you alter columns via SQL, you may need to alter the columns in the corresponding _log tables as well, as they need to be identical. Whether you need to alter the _log table depends on the type of event source: append-only or consolidated.

For append-only events (for example, a stream of logs) you can just update the table and alter the columns directly. For example (in Redshift):

Copy
BEGIN TRANSACTION;
ALTER TABLE {schema}.{table_name} RENAME COLUMN "{col_name}" TO "{col_name}_old";
ALTER TABLE {schema}.{table_name} ADD COLUMN "{col_name}" {type};
UPDATE {schema}.{table_name} SET "{col_name}" = "{col_name}_old"::{type} WHERE "{col_name}" IS NULL;
ALTER TABLE {schema}.{table_name} DROP COLUMN "{col_name}_old" CASCADE;
COMMIT;

For consolidated events (databases with primary keys, for example) you need to run the ALTER statement on both the table and the corresponding table_log as they need to be identical. For example (again in Redshift):

Copy
BEGIN TRANSACTION;
ALTER TABLE {schema}.{table_name} RENAME COLUMN "{col_name}" TO "{col_name}_old";
ALTER TABLE {schema}.{table_name} ADD COLUMN "{col_name}" {type};
UPDATE {schema}.{table_name} SET "{col_name}" = "{col_name}_old"::{type} WHERE "{col_name}" IS NULL;
ALTER TABLE {schema}.{table_name} DROP COLUMN "{col_name}_old" CASCADE;

ALTER TABLE {schema}.{table_name}_log RENAME COLUMN "{col_name}" TO "{col_name}_old";
ALTER TABLE {schema}.{table_name}_log ADD COLUMN "{col_name}" {type};
UPDATE {schema}.{table_name}_log SET "{col_name}" = "{col_name}_old"::{type} WHERE "{col_name}" IS NULL;
ALTER TABLE {schema}.{table_name}_log DROP COLUMN "{col_name}_old" CASCADE;
COMMIT;

Regardless of the type of event, once you've made your changes in the warehouse, you MUST manually update the mapping to match your changes, field-by-field in the Mapper. Usually the Mapper detects these kinds of changes automatically and you can simply filter the listing by clicking on the blue "Invalid" box:

invalidbucket.png

Then for each column, click on the data type and adjust it, and then click Apply.

See Altering a Column via the Mapper for an example of how to alter columns via the Mapper. See the other articles in the Mapper section for more information.

If you have any questions about altering columns, or anything else for that matter, please feel free to reach out!

Search results

    No results found