#2341 Add declarative way to define tables and their replication methods and keys on DB taps, that doesn't require user to understand Singer metadata Issue created by MeltyBot Migrated from GitLab: https://gitlab.com/meltano/meltano/-/issues/2385 Originally created by @DouweM on 2020-10-08 225717 --- This could be inspired by or a subset of PipelineWise's YAML configuration format (see https://transferwise.github.io/pipelinewise/connectors/taps/postgres.html), which we could support in

meltano.yml

by translating the schema and table mappings into catalog metadata rules and tap and target settings: schemas: - source_schema: "public" # Source schema in postgres with tables target_schema: "repl_pg_public" # Target schema in the destination Data Warehouse target_schema_select_permissions: # Optional: Grant SELECT on schema and tables that created - grp_stats # List of tables to replicate from Postgres to destination Data Warehouse # # Please check the Replication Strategies section in the documentation to understand the differences. # For LOG_BASED replication method you might need to adjust the source mysql/ mariadb configuration. tables: - table_name: "table_one" replication_method: "INCREMENTAL" # One of INCREMENTAL, LOG_BASED and FULL_TABLE replication_key: "last_update" # Important: Incremental load always needs replication key # OPTIONAL: Load time transformations #transformations: # - column: "last_name" # Column to transform # type: "SET-NULL" # Transformation type # You can add as many tables as you need... - table_name: "table_two" replication_method: "LOG_BASED" # Important! Log based must be enabled in MySQL # You can add as many schemas as you need... # Uncomment this if you want replicate tables from multiple schemas #- source_schema: "another_schema_in_postgres" # target_schema: "another To support the

source_schema

and

target_schema

settings, which correspond to PipelineWise targets'

schema_mapping

settings (see https://gitlab.com/meltano/meltano/-/issues/2442), we'd need to find a way to extract these as (for example)

TAP_FOO__SCHEMA_MAPPING

(similar in spirit to the existing `TAP_FOO__LOAD_SCHEMA`: https://meltano.com/docs/plugins.html#load-schema-extra). meltano/meltano