def run_commands(conn,
commands,
cw=None,
cluster_name=None,
suppress_errors=False):
for idx, c in enumerate(commands, start=1):
if c is not None:
comment('[%s] Running %s out of %s commands: %s' %
(str(os.getpid()), idx, len(commands), c))
try:
cursor = conn.cursor()
cursor.execute(c)
comment('Success.')
except:
# cowardly bail on errors
conn.rollback()
print(traceback.format_exc())
if not suppress_errors:
raise
# emit a cloudwatch metric for the statement
if cw is not None and cluster_name is not None:
dimensions = [{
'Name': 'ClusterIdentifier',
'Value': cluster_name
}]
if c.lower().startswith('analyze'):
metric_name = 'AnalyzeTable'
elif c.lower().startswith('vacuum'):
metric_name = 'VacuumTable'
else:
# skip to the next statement - not exporting anything about these statements to cloudwatch
continue
aws_utils.put_metric(cw, 'Redshift', metric_name, dimensions,
None, 1, 'Count')
if debug:
comment(
"Emitted Cloudwatch Metric for Column Encoded table")
return True
def analyze(table_info):
schema_name = table_info[0]
table_name = table_info[1]
dist_style = table_info[4]
owner = table_info[5]
if len(table_info) > 6:
table_comment = table_info[6]
# get the count of columns that have raw encoding applied
table_unoptimised = False
count_unoptimised = 0
encodings_modified = False
output = get_count_raw_columns(schema_name, table_name)
if output is None:
print("Unable to determine potential RAW column encoding for %s" % table_name)
return ERROR
else:
for row in output:
if row[0] > 0:
table_unoptimised = True
count_unoptimised += row[0]
if not table_unoptimised and not force:
comment("Table %s.%s does not require encoding optimisation" % (schema_name, table_name))
return OK
else:
comment("Table %s.%s contains %s unoptimised columns" % (schema_name, table_name, count_unoptimised))
if force:
comment("Using Force Override Option")
statement = 'analyze compression %s."%s"' % (schema_name, table_name)
if comprows is not None:
statement = statement + (" comprows %s" % int(comprows))
try:
if debug:
comment(statement)
comment("Analyzing Table '%s.%s'" % (schema_name, table_name,))
# run the analyze in a loop, because it could be locked by another process modifying rows and get a timeout
analyze_compression_result = None
analyze_retry = 10
attempt_count = 0
last_exception = None
while attempt_count < analyze_retry and analyze_compression_result is None:
try:
analyze_compression_result = execute_query(statement)
# Commiting otherwise anaylze keep an exclusive lock until a commit arrive which can be very long
execute_query('commit;')
except KeyboardInterrupt:
# To handle Ctrl-C from user
cleanup(get_pg_conn())
return TERMINATED_BY_USER
except Exception as e:
execute_query('rollback;')
print(e)
attempt_count += 1
last_exception = e
# Exponential Backoff
time.sleep(2 ** attempt_count * RETRY_TIMEOUT)
if analyze_compression_result is None:
if last_exception is not None:
print("Unable to analyze %s due to Exception %s" % (table_name, last_exception.message))
else:
print("Unknown Error")
return ERROR
if target_schema is None:
set_target_schema = schema_name
else:
set_target_schema = target_schema
if set_target_schema == schema_name:
target_table = '%s_$mig' % table_name
else:
target_table = table_name
create_table = 'begin;\nlock table %s."%s";\ncreate table %s."%s"(' % (
schema_name, table_name, set_target_schema, target_table,)
# query the table column definition
descr = get_table_desc(schema_name, table_name)
encode_columns = []
statements = []
sortkeys = {}
has_zindex_sortkeys = False
has_identity = False
non_identity_columns = []
fks = []
table_distkey = None
table_sortkeys = []
new_sortkey_arr = [t.strip() for t in new_sort_keys.split(',')] if new_sort_keys is not None else []
# count of suggested optimizations
count_optimized = 0
# process each item given back by the analyze request
for row in analyze_compression_result:
if debug:
comment("Analyzed Compression Row State: %s" % str(row))
col = row[1]
row_sortkey = descr[col][4]
# compare the previous encoding to the new encoding
# don't use new encoding for first sortkey
datatype = descr[col][1]
new_encoding = row[2]
new_encoding = new_encoding if not abs(row_sortkey) == 1 else 'raw'
old_encoding = descr[col][2]
old_encoding = 'raw' if old_encoding == 'none' else old_encoding
if new_encoding != old_encoding:
encodings_modified = True
count_optimized += 1
# fix datatypes from the description type to the create type
col_type = descr[col][1].replace('character varying', 'varchar').replace('without time zone', '')
# check whether columns are too wide
if analyze_col_width and ("varchar" in col_type or "int" in col_type):
new_col_type = reduce_column_length(col_type, descr[col][0], table_name)
if new_col_type != col_type:
col_type = new_col_type
encodings_modified = True
# link in the existing distribution key, or set the new one
row_distkey = descr[col][3]
if table_name is not None and new_dist_key is not None:
if col == new_dist_key:
distkey = 'DISTKEY'
dist_style = 'KEY'
table_distkey = col
else:
distkey = ''
else:
if str(row_distkey).upper()[0] == 'T':
distkey = 'DISTKEY'
dist_style = 'KEY'
table_distkey = col
else:
distkey = ''
# link in the existing sort keys, or set the new ones
if table_name is not None and len(new_sortkey_arr) > 0:
if col in new_sortkey_arr:
sortkeys[new_sortkey_arr.index(col) + 1] = col
table_sortkeys.append(col)
else:
if row_sortkey != 0:
# add the absolute ordering of the sortkey to the list of all sortkeys
sortkeys[abs(row_sortkey)] = col
table_sortkeys.append(col)
if row_sortkey < 0:
has_zindex_sortkeys = True
# don't compress first sort key column. This will be set on the basis of the existing sort key not
# being modified, or on the assignment of the new first sortkey
if (abs(row_sortkey) == 1 and len(new_sortkey_arr) == 0) or (
col in table_sortkeys and table_sortkeys.index(col) == 0):
compression = 'RAW'
else:
compression = new_encoding
# extract null/not null setting
col_null = descr[col][5]
if str(col_null).upper() == 'TRUE':
col_null = 'NOT NULL'
else:
col_null = ''
# get default or identity syntax for this column
default_or_identity = descr[col][6]
if default_or_identity:
ident_data = get_identity(default_or_identity)
if ident_data is None:
default_value = 'default %s' % default_or_identity
non_identity_columns.append('"%s"' % col)
else:
default_value = 'identity (%s, %s)' % ident_data
has_identity = True
else:
default_value = ''
non_identity_columns.append('"%s"' % col)
if debug:
comment("Column %s will be encoded as %s (previous %s)" % (
col, compression, old_encoding))
# add the formatted column specification
encode_columns.extend(['"%s" %s %s %s encode %s %s'
% (col, col_type, default_value, col_null, compression, distkey)])
# abort if a new distkey was set but we couldn't find it in the set of all columns
if new_dist_key is not None and table_distkey is None:
msg = "Column '%s' not found when setting new Table Distribution Key" % new_dist_key
comment(msg)
raise Exception(msg)
# abort if new sortkeys were set but we couldn't find them in the set of all columns
if new_sort_keys is not None and len(table_sortkeys) != len(new_sortkey_arr):
if debug:
comment("Requested Sort Keys: %s" % new_sortkey_arr)
comment("Resolved Sort Keys: %s" % table_sortkeys)
msg = "Column resolution of sortkeys '%s' not found when setting new Table Sort Keys" % new_sortkey_arr
comment(msg)
raise Exception(msg)
# if this table's encodings have not changed, then don't do a modification, unless force options is set
if (not force) and (not encodings_modified):
comment("Column Encoding resulted in an identical table - no changes will be made")
else:
comment("Column Encoding will be modified for %s.%s" % (schema_name, table_name))
# add all the column encoding statements on to the create table statement, suppressing the leading
# comma on the first one
for i, s in enumerate(encode_columns):
create_table += '\n%s%s' % ('' if i == 0 else ',', s)
create_table = create_table + '\n)\n'
# add diststyle all if needed
if dist_style == 'ALL':
create_table = create_table + 'diststyle all\n'
# add sort key as a table block to accommodate multiple columns
if len(sortkeys) > 0:
if debug:
comment("Adding Sortkeys: %s" % sortkeys)
sortkey = '%sSORTKEY(' % ('INTERLEAVED ' if has_zindex_sortkeys else '')
for i in range(1, len(sortkeys) + 1):
sortkey = sortkey + sortkeys[i]
if i != len(sortkeys):
sortkey = sortkey + ','
else:
sortkey = sortkey + ')\n'
create_table = create_table + (' %s ' % sortkey)
create_table = create_table + ';'
# run the create table statement
statements.extend([create_table])
# get the primary key statement
statements.extend([get_primary_key(schema_name, set_target_schema, table_name, target_table)])
# set the table owner
statements.extend(['alter table %s."%s" owner to %s;' % (set_target_schema, target_table, owner)])
if table_comment is not None:
statements.extend(
['comment on table %s."%s" is \'%s\';' % (set_target_schema, target_table, table_comment)])
# insert the old data into the new table
# if we have identity column(s), we can't insert data from them, so do selective insert
if has_identity:
source_columns = ', '.join(non_identity_columns)
mig_columns = '(' + source_columns + ')'
else:
source_columns = '*'
mig_columns = ''
insert = 'insert into %s."%s" %s select %s from %s."%s"' % (set_target_schema,
target_table,
mig_columns,
source_columns,
schema_name,
table_name)
if len(table_sortkeys) > 0:
insert = "%s order by \"%s\";" % (insert, ",".join(table_sortkeys).replace(',', '\",\"'))
else:
insert = "%s;" % (insert)
statements.extend([insert])
# analyze the new table
analyze = 'analyze %s."%s";' % (set_target_schema, target_table)
statements.extend([analyze])
if set_target_schema == schema_name:
# rename the old table to _$old or drop
if drop_old_data:
drop = 'drop table %s."%s" cascade;' % (set_target_schema, table_name)
else:
# the alter table statement for the current data will use the first 104 characters of the
# original table name, the current datetime as YYYYMMDD and a 10 digit random string
drop = 'alter table %s."%s" rename to "%s_%s_%s_$old";' % (
set_target_schema, table_name, table_name[0:104], datetime.date.today().strftime("%Y%m%d"),
shortuuid.ShortUUID().random(length=10))
statements.extend([drop])
# rename the migrate table to the old table name
rename = 'alter table %s."%s" rename to "%s";' % (set_target_schema, target_table, table_name)
statements.extend([rename])
# add foreign keys
fks = get_foreign_keys(schema_name, set_target_schema, table_name)
# add grants back
grants = get_grants(schema_name, table_name, db_user)
if grants is not None:
statements.extend(grants)
statements.extend(['commit;'])
if do_execute:
if not run_commands(get_pg_conn(), statements):
if not ignore_errors:
if debug:
print("Error running statements: %s" % (str(statements),))
return ERROR
# emit a cloudwatch metric for the table
if cw is not None:
dimensions = [
{'Name': 'ClusterIdentifier', 'Value': db_host.split('.')[0]},
{'Name': 'TableName', 'Value': table_name}
]
aws_utils.put_metric(cw, 'Redshift', 'ColumnEncodingModification', dimensions, None, 1, 'Count')
if debug:
comment("Emitted Cloudwatch Metric for Column Encoded table")
else:
comment("No encoding modifications run for %s.%s" % (schema_name, table_name))
except Exception as e:
print('Exception %s during analysis of %s' % (e, table_name))
print(traceback.format_exc())
return ERROR
print_statements(statements)
return (OK, fks, encodings_modified)