def query_to_redshift_create_table(self, sql, table_name):
if self.schema_file:
query = load_query(self.schema_file, CONFIG_DIR)
create_table = query.rstrip("; \n")
create_table += self.create_table_keys()
return create_table
else:
desc = self.get_sql_description(sql)
create_table = """CREATE TABLE "{}"."{}" (\n """.format(
self.destination_schema_name, table_name
)
field_strs = []
for (name, type_code, _, _, precision, scale, null_ok,) in desc:
# Note: mysql overreports this number by up to 4 places, which
# should't cause problems
size_str = "({}".format(precision) if precision else ""
size_str += ",{}".format(scale) if scale else ""
size_str += ")" if size_str else ""
sql_type = "{}{}".format(
MysqlTypes.mysql_type_to_name[type_code], size_str
)
red_type = self._mysql_to_redshift_type(sql_type)
field_strs.append(
'"{name}" {type}{null_ok}'.format(
name=name,
type=red_type,
null_ok="" if null_ok else " NOT NULL",
)
)
create_table += "\n , ".join(field_strs)
create_table += "\n)\n"
create_table += self.create_table_keys()
return create_table
def query_to_redshift_create_table(self, sql, table_name):
if self.schema_file:
query = load_query(self.schema_file, CONFIG_DIR)
create_table = query.rstrip("; \n")
create_table += self.create_table_keys()
return create_table
else:
msg = "auto generated queries not yet supported for MSSQL"
raise NotImplementedError(msg)
def query_to_redshift_create_table(self, sql, table_name):
if self.schema_file:
create_table = load_query(self.schema_file, CONFIG_DIR).rstrip("; \n\t")
create_table += self.create_table_keys()
return create_table
else:
if self.query_file is not None:
self.logger.warning(
(
"Cannot obtain `null_ok` attribute for columns in postgres "
'source necessary to create target table "%s", assuming '
"that all columns should be NOT NULL. Please manually "
"rebuild the target table if some columns should be "
"nullable."
),
table_name,
)
desc = self.get_sql_description(sql)
create_table = """CREATE TABLE "{}"."{}" (\n """.format(
self.destination_schema_name, table_name
)
field_strs = []
for (name, type_code, _, internal_size, precision, scale, null_ok,) in desc:
size_str = "({}".format(precision) if precision else ""
size_str += ",{}".format(scale) if scale else ""
size_str += ")" if size_str else ""
if self.type_map.get(type_code, type_code) == "varchar":
length = internal_size if internal_size > 0 else "max"
size_str = "({})".format(length)
red_type = "{}{}".format(
self.type_map.get(type_code, type_code), size_str
)
field_strs.append(
'"{name}" {type}{null_ok}'.format(
name=name,
type=red_type,
null_ok="" if null_ok else " NOT NULL",
)
)
create_table += "\n , ".join(field_strs)
create_table += "\n)\n"
create_table += self.create_table_keys()
return create_table
def query_to_redshift_create_table(self, sql, table_name):
if self.schema_file:
create_table = load_query(self.schema_file,
CONFIG_DIR).rstrip("; \n\t")
create_table += self.create_table_keys()
create_table += ";\n"
# TODO: add support for table and column comments in yaml config file.
return create_table
else:
if self.query_file is not None:
self.logger.warning(
("Cannot obtain `null_ok` attribute for columns in postgres "
'source necessary to create target table "%s", assuming '
"that all columns should be NOT NULL. Please manually "
"rebuild the target table if some columns should be "
"nullable."),
table_name,
)
table_attributes, columns = self.get_sql_description(sql)
create_table = """CREATE TABLE "{}"."{}" (\n """.format(
self.destination_schema_name, table_name)
field_strs, comments = [], []
table_comment = table_attributes.get("comment")
if self.include_comments and table_comment is not None:
comments.append(
"""COMMENT ON TABLE "{}"."{}" IS '{}'""".format(
self.destination_schema_name,
table_name,
table_comment.replace(
"'", "''"
), # escape single quotes in the creation statement
))
for (
name,
type_code,
_,
internal_size,
precision,
scale,
null_ok,
comment,
) in columns:
size_str = self._get_column_size(type_code, internal_size,
precision, scale)
redshift_type = self._format_redshift_type(
self.type_map.get(type_code, type_code), size_str, name)
field_strs.append('"{name}" {type}{null_ok}'.format(
name=name,
type=redshift_type,
null_ok="" if null_ok else " NOT NULL",
))
if self.include_comments and comment is not None:
comments.append(
"""COMMENT ON COLUMN "{}"."{}"."{}" IS '{}';""".format(
self.destination_schema_name,
table_name,
name,
comment.replace(
"'", "''"
), # escape single quotes in the creation statement
))
create_table += "\n , ".join(field_strs)
create_table += "\n)\n"
create_table += self.create_table_keys()
create_table += ";\n"
create_table += ";\n".join(comments)
return create_table