def __(source_db_alias: str, target_db_alias: str, target_table: str, timezone: str = None, csv_format: bool = None, delimiter_char: str = None): return copy_command(dbs.db(source_db_alias), dbs.db(target_db_alias), target_table=target_table, timezone=timezone, csv_format=csv_format, delimiter_char=delimiter_char)
def __(alias: str, header: bool = None, footer: bool = None, delimiter_char: str = None, csv_format: bool = None): return copy_to_stdout_command(dbs.db(alias), header=header, footer=footer, delimiter_char=delimiter_char, csv_format=csv_format)
def _target_name(self): db = dbs.db(self.target_db_alias) if isinstance(db, dbs.PostgreSQLDB): return 'target-postgres' elif isinstance(db, dbs.RedshiftDB): return 'target-redshift' elif isinstance(db, dbs.SQLiteDB): return 'target-sqlite' else: raise Exception( f'Not supported DB type {type(db)} for command SingerTapToDB')
def __(alias: str, target_table: str, csv_format: bool = None, skip_header: bool = None, delimiter_char: str = None, quote_char: str = None, null_value_string: str = None, timezone: str = None): return copy_from_stdin_command(dbs.db(alias), target_table=target_table, csv_format=csv_format, skip_header=skip_header, delimiter_char=delimiter_char, quote_char=quote_char, null_value_string=null_value_string, timezone=timezone)
def _create_target_config(self, config: dict): db = dbs.db(self.target_db_alias) if isinstance(db, dbs.PostgreSQLDB): # Reference: https://github.com/datamill-co/target-postgres#configjson config.update({ 'postgres_host': db.host, 'postgres_port': db.port, 'postgres_database': db.database, 'postgres_username': db.user, 'postgres_password': db.password, 'postgres_schema': self.target_schema, 'postgres_sslmode': db.sslmode, 'postgres_sslrootcert': db.sslrootcert, 'postgres_sslcert': db.sslcert, 'postgres_sslkey': db.sslkey }) elif isinstance(db, dbs.RedshiftDB): # Reference: https://github.com/datamill-co/target-redshift#usage config.update({ 'redshift_host': db.host, 'redshift_port': db.post, 'redshift_database': db.database, 'redshift_username': db.user, 'redshift_password': db.password, 'redshift_schema': self.target_schema, 'target_s3': { 'aws_access_key_id': db.aws_access_key_id, 'aws_secret_access_key': db.aws_secret_access_key, 'aws_s3_bucket_name': db.aws_s3_bucket_name } }) elif isinstance(db, dbs.SQLiteDB): # NOTE: self.target_schema is not used here because target-sqlite doesn't support this! ; we use optimistic behavior here and don't throw an error # It would probably be better to implement a prefix option in target-sqlite instead of just ignoring self.target_schem # Reference: https://gitlab.com/meltano/target-sqlite config.update({'database': db.file_name}) else: raise Exception( f'Not supported DB type {type(db)} for command SingerTapToDB')
def compose_delete_query(db_alias: str, target_table: str, replication_method: ReplicationMethod, replication_key=None) -> str: """ Composes a delete query to a target table before starting sync. Args: db_alias target_table replication_method replication_key """ db = dbs.db(db_alias) if replication_method == ReplicationMethod.FULL_TABLE: if isinstance(db, dbs.BigQueryDB): return f'DELETE FROM {target_table} WHERE 1=1' # BigQuery needs a where clause! else: return f'DELETE FROM {target_table}' elif replication_method == ReplicationMethod.INCREMENTAL: raise NotImplementedError() elif replication_method == ReplicationMethod.LOG_BASED: return None # delete nothing
def __(alias: str, timezone: str = None, echo_queries: bool = None): return query_command(dbs.db(alias), timezone=timezone, echo_queries=echo_queries)
def draw_schema(db_alias: str, schemas: str): """Shows a chart of the tables and FK relationships in a given database and schema list""" import graphviz if db_alias not in config.databases(): flask.abort(404, f'unkown database {db_alias}') db = dbs.db(db_alias) assert (isinstance(db, mara_db.dbs.PostgreSQLDB) ) # currently only postgresql is supported schema_names = schemas.split('/') hide_columns = flask.request.args.get('hide-columns') engine = flask.request.args.get('engine') # get all table inheritance relations as dictionary: {(child_schema, child_table): (parent_schema, parent_table) inherited_tables = {} with mara_db.postgresql.postgres_cursor_context(db_alias) as cursor: cursor.execute(""" SELECT rel_namespace.nspname, rel.relname , parent_namespace.nspname, parent.relname FROM pg_inherits JOIN pg_class parent ON parent.oid = pg_inherits.inhparent JOIN pg_class rel ON rel.oid = pg_inherits.inhrelid JOIN pg_namespace parent_namespace ON parent_namespace.oid = parent.relnamespace JOIN pg_namespace rel_namespace ON rel_namespace.oid = rel.relnamespace""") for schema_name, table_name, parent_schema_name, parent_table_name in cursor.fetchall( ): inherited_tables[(schema_name, table_name)] = (parent_schema_name, parent_table_name) # get all tables that have foreign key constrains on them or are referenced by foreign key constraints fk_constraints = set( ) # {((table_schema, table_name), (referred_schema_name, referred_table_name)} constrained_columns = {} # {(schema_name, table_name): {columns}} tables = set() # {(schema_name, table_name)} with mara_db.postgresql.postgres_cursor_context(db_alias) as cursor: cursor.execute( f''' SELECT constrained_table_schema.nspname, constrained_table.relname, array_agg(constrained_column.attname), referenced_table_schema.nspname, referenced_table.relname FROM pg_constraint JOIN pg_class constrained_table ON constrained_table.oid = pg_constraint.conrelid JOIN pg_namespace constrained_table_schema ON constrained_table.relnamespace = constrained_table_schema.oid JOIN pg_class referenced_table ON referenced_table.oid = pg_constraint.confrelid JOIN pg_namespace referenced_table_schema ON referenced_table.relnamespace = referenced_table_schema.oid JOIN pg_attribute constrained_column ON constrained_column.attrelid = constrained_table.oid AND attnum = ANY (conkey) WHERE constrained_table_schema.nspname = ANY ({'%s'}) GROUP BY constrained_table_schema.nspname, constrained_table.relname, referenced_table_schema.nspname, referenced_table.relname; ''', (schema_names, )) for schema_name, table_name, table_columns, referred_schema_name, referred_table_name in cursor.fetchall( ): referring_table = (schema_name, table_name) if referring_table in inherited_tables: referring_table = inherited_tables[referring_table] tables.add(referring_table) referred_table = (referred_schema_name, referred_table_name) if referred_table in inherited_tables: referred_table = inherited_tables[referred_table] tables.add(referred_table) fk_constraints.add((referring_table, referred_table)) if referring_table in constrained_columns: constrained_columns[referring_table].update(table_columns) else: constrained_columns[referring_table] = set(table_columns) # get enum usages enums = set() # {(schema_name, table_name)} with mara_db.postgresql.postgres_cursor_context(db_alias) as cursor: cursor.execute( f''' SELECT DISTINCT pg_namespace_table.nspname AS table_schema, pg_class_table.relname AS table_name, pg_namespace_enum.nspname AS enum_schema, pg_type.typname AS enum_type FROM pg_attribute JOIN pg_class pg_class_table ON pg_class_table.oid = attrelid JOIN pg_namespace pg_namespace_table ON pg_namespace_table.oid = pg_class_table.relnamespace JOIN pg_type ON atttypid = pg_type.OID JOIN pg_namespace pg_namespace_enum ON typnamespace = pg_namespace_enum.oid JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid WHERE pg_namespace_table.nspname = ANY ({'%s'})''', (schema_names, )) for table_schema, table_name, enum_schema, enum_name in cursor.fetchall( ): if (table_schema, table_name) in tables: tables.add((enum_schema, enum_name)) fk_constraints.add( ((table_schema, table_name), (enum_schema, enum_name))) enums.add((enum_schema, enum_name)) # get all columns of all tables table_columns = {} # {(schema_name, table_name): [columns]} with mara_db.postgresql.postgres_cursor_context(db_alias) as cursor: cursor.execute(''' SELECT table_schema, table_name, array_agg(column_name :: TEXT ORDER BY ordinal_position) FROM information_schema.columns GROUP BY table_schema, table_name''') for schema_name, table_name, columns in cursor.fetchall(): table_columns[(schema_name, table_name)] = columns graph = graphviz.Digraph(engine=engine, graph_attr={ 'splines': 'True', 'overlap': 'ortho' }) schema_colors = {} fk_pattern = re.compile(config.schema_ui_foreign_key_column_regex()) for schema_name, table_name in sorted(tables): if schema_name not in schema_colors: colors = [ '#ffffcc', '#bbffcc', '#cceeff', '#eedd99', '#ddee99', '#99ddff', '#dddddd' ] schema_colors[schema_name] = colors[len(schema_colors) % len(colors)] label = '< <TABLE BORDER="1" CELLBORDER="0" CELLSPACING="0" CELLPADDING="1" BGCOLOR="' \ + schema_colors[schema_name] + '"><TR>' node_name = schema_name + '.' + table_name if hide_columns: label += '<TD ALIGN="LEFT"> ' + table_name.replace( '_', '<BR/>') + ' </TD></TR>' elif (schema_name, table_name) in enums: label += '<TD ALIGN="LEFT"> ' + table_name.replace( '_', '<BR/>') + ' </TD></TR>' else: label += '<TD ALIGN="LEFT"><U><B> ' + table_name + ' </B></U></TD></TR>' for column in table_columns[(schema_name, table_name)]: label += '<TR><TD ALIGN="LEFT" > ' if fk_pattern.match(column) \ and (schema_name, table_name) in constrained_columns \ and column not in constrained_columns[(schema_name, table_name)]: label += '<B><I><FONT COLOR="#dd55dd"> ' + column + ' </FONT></I></B>' else: label += column label += ' </TD></TR>' label += '</TABLE> >' graph.node(name=node_name, label=label, _attributes={ 'fontname': 'Helvetica, Arial, sans-serif', 'fontsize': '10', 'fontcolor': '#555555', 'shape': 'none' }) for (schema_name, table_name), (referred_schema_name, referred_table_name) in fk_constraints: graph.edge(schema_name + '.' + table_name, referred_schema_name + '.' + referred_table_name, _attributes={'color': '#888888'}) response = flask.Response(graph.pipe('svg').decode('utf-8')) response.headers[ 'Content-Disposition'] = f'attachment; filename="{datetime.date.today().isoformat()}-{db_alias}.svg"' return response
def __(alias: str, sql_query: str): return read_dataframe(dbs.db(alias), sql_query=sql_query)
def __(alias: str): return schemas_with_foreign_key_constraints(dbs.db(alias))
def __(alias: str, schema_names: [str]): return extract_schema(dbs.db(alias), schema_names)
def __(source_db: dbs.DB, target_db_alias: str, target_table: str, timezone: str = None): return copy_command(source_db, dbs.db(target_db_alias), target_table, timezone)
def __(alias: str, header: bool = False, footer: bool = False): return copy_to_stdout_command(dbs.db(alias), header=header, footer=footer)
def __(alias: str): return supports_extract_schema(dbs.db(alias))
def __(alias: str): return copy_to_stdout_command(dbs.db(alias))