Column DDL¶

Functions to generate DDL queries to change columns directly in the datawarehouse.

ColumnDDL.__init__(tuple_or_tuple_list, change_type='data_type', has_log=False, case='UPPER')¶

Prints DDL for direct DB changes of data_type, drop, or add

Parameters:	tuple_or_tuple_list – A single tuple or list of tuples with information about the changes to make change_type – The type of DDL to create out of data_type, add, rename, combine or drop log_table – True if the event has a log table case – UPPER or LOWER case for the DDL statements

For a change_type of data_type or adding a column the tuple_or_tuple_list should contain the schema.table, the column name, and the column data type.

('schema_name.table_name', 'column_one', 'INT')
or
[('schema_name.table_name', 'column_one', 'INT'),
('schema_name.table_name', 'column_two', 'VARCHAR(16777216)')]

For a change_type of dropping a column a column the tuple_or_tuple_list should contain the schema.table and the column name

('schema_name.table_name', 'column_one')
or
[('schema_name.table_name', 'column_one'),
('schema_name.table_name', 'column_two')]

For a change_type of renaming a column a column the tuple_or_tuple_list should contain the schema.table, the current column name, and the new column name

('schema_name.table_name', 'my_bad_column', 'my_good_column', 'INT')
or
[('schema_name.table_name', 'my_bad_column_one', 'my_good_column_one', 'INT'),
('schema_name.table_name', 'my_bad_column_two', 'my_good_column_two', 'VARCHAR(16777216)')]

For a change_type of combining columns the tuple_or_tuple_list should contain the schema.table, the current column name, the new column name, and the column data type.

('schema_name.table_name', 'my_bad_column', 'my_good_column', 'INT')
or
[('schema_name.table_name', 'my_bad_column_name_one', 'my_good_column_name_one', 'INTEGER'),
('schema_name.table_name', 'my_bad_column_name_two', 'my_good_column_name_two', 'VARCHAR(16777216)')]

Convert Tuple to List¶

ColumnDDL.convert_tuple_to_list()¶

Converts single tuples into a list of tuples so list iteration on single event changes works

Parameters:	tuple_or_tuple_list – A tuple or a tuple list
Returns:	A list of tuple

Create DDL Statements¶

ColumnDDL.create_ddl_statements()¶

Creates DDL statements, prints the statements, and returns the statements

Parameters:	event_column_type_tuple – The tuple from the function specified change_type – The type of column change statements to print. Options are column, drop, combine_drop log_table – True if the columns to alter have log tables
Returns:	A string with all the queries generated

Add Column¶

ColumnDDL.add_column(schema_name, table_name, column_to_add, new_column_type)¶

Prints add column statements

Parameters:	schema_name – The name of the schema table_name – The name of the table column_to_add – The name of the column to add
Returns:	The query

For a change_type of data_type or adding a column the tuple_or_tuple_list should contain the schema.table, the column name, and the column data type.

('schema_name.table_name', 'column_one', 'INT')
or
[('schema_name.table_name', 'column_one', 'INT'),
('schema_name.table_name', 'column_two', 'VARCHAR(16777216)')]

Change Column Data Type¶

ColumnDDL.change_column_data_type(table_name, schema_name, column_name, new_column_type, new_column_type_no_count)¶

Prints the DB DDL statement for a single column

Parameters:	schema_name – The name of the schema table_name – The name of the table log_table – True if the table has a log table
Returns:	The query

For a change_type of data_type or adding a column the tuple_or_tuple_list should contain the schema.table, the column name, and the column data type.

('schema_name.table_name', 'column_one', 'INT')
or
[('schema_name.table_name', 'column_one', 'INT'),
('schema_name.table_name', 'column_two', 'VARCHAR(16777216)')]

Combine Columns¶

ColumnDDL.combine_columns(schema_name, table_name, column_to_drop, column_to_keep, column_to_keep_type)¶

Prints statement to combine data from 2 columns into a target column and drop the non-target column

Parameters:	fully_qualified_table_name_db – The schema.table_name of the table to change column – The tuple for an individual column log_table – True if the table has a log table
Returns:	The query

For a change_type of combining columns the tuple_or_tuple_list should contain the schema.table, the current column name, the new column name, and the column data type.

('schema_name.table_name', 'my_bad_column', 'my_good_column', 'INT')
or
[('schema_name.table_name', 'my_bad_column_name_one', 'my_good_column_name_one', 'INTEGER'),
('schema_name.table_name', 'my_bad_column_name_two', 'my_good_column_name_two', 'VARCHAR(16777216)')]

Drop Column¶

ColumnDDL.drop_column(schema_name, table_name, column_to_drop)¶

Prints drop column statements

Parameters:	schema_name – The name of the schema table_name – The name of the table column_to_drop – The name of the column to drop
Returns:	the query

For a change_type of dropping a column a column the tuple_or_tuple_list should contain the schema.table and the column name

('schema_name.table_name', 'column_one')
or
[('schema_name.table_name', 'column_one'),
('schema_name.table_name', 'column_two')]

Rename Column¶

ColumnDDL.rename_column(table_name, schema_name, existing_column_name, new_column_name)¶

Prints the DB DDL statement to rename a single column

Parameters:	schema_name – The name of the schema table_name – The name of the table existing_column_name – The existing column name of the colum new_column_name – The new column name for the column
Returns:	The query

For a change_type of renaming a column a column the tuple_or_tuple_list should contain the schema.table, the current column name, and the new column name

('schema_name.table_name', 'my_bad_column', 'my_good_column', 'INT')
or
[('schema_name.table_name', 'my_bad_column_one', 'my_good_column_one', 'INT'),
('schema_name.table_name', 'my_bad_column_two', 'my_good_column_two', 'VARCHAR(16777216)')]