def sync_non_binlog_streams(mssql_conn, non_binlog_catalog, config, state):
mssql_conn = MSSQLConnection(config)
for catalog_entry in non_binlog_catalog.streams:
columns = list(catalog_entry.schema.properties.keys())
if not columns:
LOGGER.warning(
"There are no columns selected for stream %s, skipping it.",
catalog_entry.stream)
continue
state = singer.set_currently_syncing(state,
catalog_entry.tap_stream_id)
# Emit a state message to indicate that we've started this stream
singer.write_message(singer.StateMessage(value=copy.deepcopy(state)))
md_map = metadata.to_map(catalog_entry.metadata)
replication_method = md_map.get((), {}).get("replication-method")
replication_key = md_map.get((), {}).get("replication-key")
primary_keys = md_map.get((), {}).get("table-key-properties")
LOGGER.info(
f"Table {catalog_entry.table} proposes {replication_method} sync")
if replication_method == "INCREMENTAL" and not replication_key:
LOGGER.info(
f"No replication key for {catalog_entry.table}, using full table replication"
)
replication_method = "FULL_TABLE"
if replication_method == "INCREMENTAL" and not primary_keys:
LOGGER.info(
f"No primary key for {catalog_entry.table}, using full table replication"
)
replication_method = "FULL_TABLE"
LOGGER.info(
f"Table {catalog_entry.table} will use {replication_method} sync")
database_name = common.get_database_name(catalog_entry)
with metrics.job_timer("sync_table") as timer:
timer.tags["database"] = database_name
timer.tags["table"] = catalog_entry.table
if replication_method == "INCREMENTAL":
LOGGER.info(f"syncing {catalog_entry.table} incrementally")
do_sync_incremental(mssql_conn, config, catalog_entry, state,
columns)
elif replication_method == "FULL_TABLE":
LOGGER.info(f"syncing {catalog_entry.table} full table")
do_sync_full_table(mssql_conn, config, catalog_entry, state,
columns)
else:
raise Exception(
"only INCREMENTAL and FULL TABLE replication methods are supported"
)
state = singer.set_currently_syncing(state, None)
singer.write_message(singer.StateMessage(value=copy.deepcopy(state)))
def sync_table(mssql_conn, config, catalog_entry, state, columns):
mssql_conn = MSSQLConnection(config)
common.whitelist_bookmark_keys(BOOKMARK_KEYS, catalog_entry.tap_stream_id,
state)
catalog_metadata = metadata.to_map(catalog_entry.metadata)
stream_metadata = catalog_metadata.get((), {})
replication_key_metadata = stream_metadata.get("replication-key")
replication_key_state = singer.get_bookmark(state,
catalog_entry.tap_stream_id,
"replication_key")
replication_key_value = None
if replication_key_metadata == replication_key_state:
replication_key_value = singer.get_bookmark(
state, catalog_entry.tap_stream_id, "replication_key_value")
else:
state = singer.write_bookmark(state, catalog_entry.tap_stream_id,
"replication_key",
replication_key_metadata)
state = singer.clear_bookmark(state, catalog_entry.tap_stream_id,
"replication_key_value")
stream_version = common.get_stream_version(catalog_entry.tap_stream_id,
state)
state = singer.write_bookmark(state, catalog_entry.tap_stream_id,
"version", stream_version)
activate_version_message = singer.ActivateVersionMessage(
stream=catalog_entry.stream, version=stream_version)
singer.write_message(activate_version_message)
LOGGER.info("Beginning SQL")
with connect_with_backoff(mssql_conn) as open_conn:
with open_conn.cursor() as cur:
select_sql = common.generate_select_sql(catalog_entry, columns)
params = {}
if replication_key_value is not None:
if catalog_entry.schema.properties[
replication_key_metadata].format == "date-time":
replication_key_value = pendulum.parse(
replication_key_value)
select_sql += " WHERE \"{}\" >= %(replication_key_value)s ORDER BY \"{}\" ASC".format(
replication_key_metadata, replication_key_metadata)
params["replication_key_value"] = replication_key_value
elif replication_key_metadata is not None:
select_sql += " ORDER BY \"{}\" ASC".format(
replication_key_metadata)
common.sync_query(cur, catalog_entry, state, select_sql, columns,
stream_version, params)
def do_sync_incremental(mssql_conn, config, catalog_entry, state, columns):
mssql_conn = MSSQLConnection(config)
md_map = metadata.to_map(catalog_entry.metadata)
stream_version = common.get_stream_version(catalog_entry.tap_stream_id, state)
replication_key = md_map.get((), {}).get("replication-key")
write_schema_message(catalog_entry=catalog_entry, bookmark_properties=[replication_key])
LOGGER.info("Schema written")
incremental.sync_table(mssql_conn, config, catalog_entry, state, columns)
singer.write_message(singer.StateMessage(value=copy.deepcopy(state)))
def main_impl():
args = utils.parse_args(REQUIRED_CONFIG_KEYS)
mssql_conn = MSSQLConnection(args.config)
log_server_params(mssql_conn)
if args.discover:
do_discover(mssql_conn, args.config)
elif args.catalog:
state = args.state or {}
do_sync(mssql_conn, args.config, args.catalog, state)
elif args.properties:
catalog = Catalog.from_dict(args.properties)
state = args.state or {}
do_sync(mssql_conn, args.config, catalog, state)
else:
LOGGER.info("No properties were selected")
def do_sync_full_table(mssql_conn, config, catalog_entry, state, columns):
key_properties = common.get_key_properties(catalog_entry)
mssql_conn = MSSQLConnection(config)
write_schema_message(catalog_entry)
stream_version = common.get_stream_version(catalog_entry.tap_stream_id, state)
full_table.sync_table(mssql_conn, config, catalog_entry, state, columns, stream_version)
# Prefer initial_full_table_complete going forward
singer.clear_bookmark(state, catalog_entry.tap_stream_id, "version")
state = singer.write_bookmark(
state, catalog_entry.tap_stream_id, "initial_full_table_complete", True
)
singer.write_message(singer.StateMessage(value=copy.deepcopy(state)))
def main():
# Parse command line arguments
args = utils.parse_args(REQUIRED_CONFIG_KEYS)
mssql_conn = MSSQLConnection(args.config)
# If discover flag was passed, run discovery mode and dump output to stdout
if args.discover:
catalog = discover(mssql_conn, args.config)
print(json.dumps(catalog.to_dict(), indent=2))
# Otherwise run in sync mode
else:
# 'properties' is the legacy name of the catalog
if args.properties:
catalog = args.properties
# 'catalog' is the current name
elif args.catalog:
catalog = args.catalog
else:
catalog = discover(mssql_conn, args.config)
sync(mssql_conn, args.config, args.state, catalog)
def sync_table(mssql_conn, config, catalog_entry, state, columns,
stream_version):
mssql_conn = MSSQLConnection(config)
common.whitelist_bookmark_keys(generate_bookmark_keys(catalog_entry),
catalog_entry.tap_stream_id, state)
bookmark = state.get("bookmarks", {}).get(catalog_entry.tap_stream_id, {})
version_exists = True if "version" in bookmark else False
initial_full_table_complete = singer.get_bookmark(
state, catalog_entry.tap_stream_id, "initial_full_table_complete")
state_version = singer.get_bookmark(state, catalog_entry.tap_stream_id,
"version")
activate_version_message = singer.ActivateVersionMessage(
stream=catalog_entry.stream, version=stream_version)
# For the initial replication, emit an ACTIVATE_VERSION message
# at the beginning so the records show up right away.
if not initial_full_table_complete and not (version_exists
and state_version is None):
singer.write_message(activate_version_message)
with connect_with_backoff(mssql_conn) as open_conn:
with open_conn.cursor() as cur:
select_sql = common.generate_select_sql(catalog_entry, columns)
params = {}
common.sync_query(cur, catalog_entry, state, select_sql, columns,
stream_version, params)
# clear max pk value and last pk fetched upon successful sync
singer.clear_bookmark(state, catalog_entry.tap_stream_id, "max_pk_values")
singer.clear_bookmark(state, catalog_entry.tap_stream_id,
"last_pk_fetched")
singer.write_message(activate_version_message)
def get_test_connection():
db_config = get_db_config()
connection = _mssql.connect(**db_config)
try:
connection.execute_non_query('DROP DATABASE {}'.format(DB_NAME))
except:
pass
try:
connection.execute_non_query('CREATE DATABASE {}'.format(DB_NAME))
except:
pass
finally:
connection.close()
db_config['database'] = DB_NAME
mssql_conn = MSSQLConnection(db_config)
return mssql_conn
def get_non_binlog_streams(mssql_conn, catalog, config, state):
"""Returns the Catalog of data we're going to sync for all SELECT-based
streams (i.e. INCREMENTAL, FULL_TABLE, and LOG_BASED that require a historical
sync). LOG_BASED streams that require a historical sync are inferred from lack
of any state.
Using the Catalog provided from the input file, this function will return a
Catalog representing exactly which tables and columns that will be emitted
by SELECT-based syncs. This is achieved by comparing the input Catalog to a
freshly discovered Catalog to determine the resulting Catalog.
The resulting Catalog will include the following any streams marked as
"selected" that currently exist in the database. Columns marked as "selected"
and those labled "automatic" (e.g. primary keys and replication keys) will be
included. Streams will be prioritized in the following order:
1. currently_syncing if it is SELECT-based
2. any streams that do not have state
3. any streams that do not have a replication method of LOG_BASED
"""
mssql_conn = MSSQLConnection(config)
discovered = discover_catalog(mssql_conn, config)
# Filter catalog to include only selected streams
selected_streams = list(
filter(lambda s: common.stream_is_selected(s), catalog.streams))
streams_with_state = []
streams_without_state = []
for stream in selected_streams:
stream_metadata = metadata.to_map(stream.metadata)
# if stream_metadata.table in ["aagaggpercols", "aagaggdef"]:
for k, v in stream_metadata.get((), {}).items():
LOGGER.info(f"{k}: {v}")
# LOGGER.info(stream_metadata.get((), {}).get("table-key-properties"))
replication_method = stream_metadata.get((),
{}).get("replication-method")
stream_state = state.get("bookmarks", {}).get(stream.tap_stream_id)
if not stream_state:
streams_without_state.append(stream)
else:
streams_with_state.append(stream)
# If the state says we were in the middle of processing a stream, skip
# to that stream. Then process streams without prior state and finally
# move onto streams with state (i.e. have been synced in the past)
currently_syncing = singer.get_currently_syncing(state)
# prioritize streams that have not been processed
ordered_streams = streams_without_state + streams_with_state
if currently_syncing:
currently_syncing_stream = list(
filter(
lambda s: s.tap_stream_id == currently_syncing and
is_valid_currently_syncing_stream(s, state),
streams_with_state,
))
non_currently_syncing_streams = list(
filter(lambda s: s.tap_stream_id != currently_syncing,
ordered_streams))
streams_to_sync = currently_syncing_stream + non_currently_syncing_streams
else:
# prioritize streams that have not been processed
streams_to_sync = ordered_streams
return resolve_catalog(discovered, streams_to_sync)
def discover_catalog(mssql_conn, config):
"""Returns a Catalog describing the structure of the database."""
LOGGER.info("Preparing Catalog")
mssql_conn = MSSQLConnection(config)
filter_dbs_config = config.get("filter_dbs")
if filter_dbs_config:
filter_dbs_clause = ",".join(
["'{}'".format(db) for db in filter_dbs_config.split(",")])
table_schema_clause = "WHERE c.table_schema IN ({})".format(
filter_dbs_clause)
else:
table_schema_clause = """
WHERE c.TABLE_SCHEMA NOT IN (
'information_schema',
'performance_schema',
'sys'
)"""
with connect_with_backoff(mssql_conn) as open_conn:
cur = open_conn.cursor()
LOGGER.info("Fetching tables")
cur.execute("""SELECT TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES c
{}
""".format(table_schema_clause))
table_info = {}
for (db, table, table_type) in cur.fetchall():
if db not in table_info:
table_info[db] = {}
table_info[db][table] = {
"row_count": None,
"is_view": table_type == "VIEW"
}
LOGGER.info("Tables fetched, fetching columns")
cur.execute("""with constraint_columns as (
select c.TABLE_SCHEMA
, c.TABLE_NAME
, c.COLUMN_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
on tc.TABLE_SCHEMA = c.TABLE_SCHEMA
and tc.TABLE_NAME = c.TABLE_NAME
and tc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
and tc.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE'))
SELECT c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
case when cc.COLUMN_NAME is null then 0 else 1 end
FROM INFORMATION_SCHEMA.COLUMNS c
left join constraint_columns cc
on cc.TABLE_NAME = c.TABLE_NAME
and cc.TABLE_SCHEMA = c.TABLE_SCHEMA
and cc.COLUMN_NAME = c.COLUMN_NAME
{}
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME
""".format(table_schema_clause))
columns = []
rec = cur.fetchone()
while rec is not None:
columns.append(Column(*rec))
rec = cur.fetchone()
LOGGER.info("Columns Fetched")
entries = []
for (k, cols) in itertools.groupby(
columns, lambda c: (c.table_schema, c.table_name)):
cols = list(cols)
(table_schema, table_name) = k
schema = Schema(
type="object",
properties={c.column_name: schema_for_column(c)
for c in cols})
md = create_column_metadata(cols)
md_map = metadata.to_map(md)
md_map = metadata.write(md_map, (), "database-name", table_schema)
is_view = table_info[table_schema][table_name]["is_view"]
if table_schema in table_info and table_name in table_info[
table_schema]:
row_count = table_info[table_schema][table_name].get(
"row_count")
if row_count is not None:
md_map = metadata.write(md_map, (), "row-count", row_count)
md_map = metadata.write(md_map, (), "is-view", is_view)
key_properties = [
c.column_name for c in cols if c.is_primary_key == 1
]
md_map = metadata.write(md_map, (), "table-key-properties",
key_properties)
entry = CatalogEntry(
table=table_name,
stream=table_name,
metadata=metadata.to_list(md_map),
tap_stream_id=common.generate_tap_stream_id(
table_schema, table_name),
schema=schema,
)
entries.append(entry)
LOGGER.info("Catalog ready")
return Catalog(entries)
def discover_catalog(mssql_conn, config):
"""Returns a Catalog describing the structure of the database."""
LOGGER.info("Preparing Catalog")
mssql_conn = MSSQLConnection(config)
filter_dbs_config = config.get("filter_dbs")
if filter_dbs_config:
filter_dbs_clause = ",".join(["'{}'".format(db) for db in filter_dbs_config.split(",")])
table_schema_clause = "WHERE c.table_schema IN ({})".format(filter_dbs_clause)
else:
table_schema_clause = """
WHERE c.table_schema NOT IN (
'information_schema',
'performance_schema',
'sys'
)"""
with connect_with_backoff(mssql_conn) as open_conn:
cur = open_conn.cursor()
LOGGER.info("Fetching tables")
cur.execute(
"""SELECT table_schema,
table_name,
table_type
FROM information_schema.tables c
{}
""".format(
table_schema_clause
)
)
table_info = {}
for (db, table, table_type) in cur.fetchall():
if db not in table_info:
table_info[db] = {}
table_info[db][table] = {"row_count": None, "is_view": table_type == "VIEW"}
LOGGER.info("Tables fetched, fetching columns")
cur.execute(
"""with constraint_columns as (
select c.table_schema
, c.table_name
, c.column_name
from information_schema.constraint_column_usage c
join information_schema.table_constraints tc
on tc.table_schema = c.table_schema
and tc.table_name = c.table_name
and tc.constraint_name = c.constraint_name
and tc.constraint_type in ('PRIMARY KEY', 'UNIQUE'))
SELECT c.table_schema,
c.table_name,
c.column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale,
case when cc.column_name is null then 0 else 1 end
FROM information_schema.columns c
left join constraint_columns cc
on cc.table_name = c.table_name
and cc.table_schema = c.table_schema
and cc.column_name = c.column_name
{}
ORDER BY c.table_schema, c.table_name
""".format(
table_schema_clause
)
)
columns = []
rec = cur.fetchone()
while rec is not None:
columns.append(Column(*rec))
rec = cur.fetchone()
LOGGER.info("Columns Fetched")
entries = []
for (k, cols) in itertools.groupby(columns, lambda c: (c.table_schema, c.table_name)):
cols = list(cols)
(table_schema, table_name) = k
schema = Schema(
type="object", properties={c.column_name: schema_for_column(c) for c in cols}
)
md = create_column_metadata(cols)
md_map = metadata.to_map(md)
md_map = metadata.write(md_map, (), "database-name", table_schema)
is_view = table_info[table_schema][table_name]["is_view"]
if table_schema in table_info and table_name in table_info[table_schema]:
row_count = table_info[table_schema][table_name].get("row_count")
if row_count is not None:
md_map = metadata.write(md_map, (), "row-count", row_count)
md_map = metadata.write(md_map, (), "is-view", is_view)
key_properties = [c.column_name for c in cols if c.is_primary_key == 1]
md_map = metadata.write(md_map, (), "table-key-properties", key_properties)
entry = CatalogEntry(
table=table_name,
stream=table_name,
metadata=metadata.to_list(md_map),
tap_stream_id=common.generate_tap_stream_id(table_schema, table_name),
schema=schema,
)
entries.append(entry)
LOGGER.info("Catalog ready")
return Catalog(entries)