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)')]