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