def migration_delete(sqlserver_db, aurora_db, table_name, where_clause):
# See if there is a primary writer specific to this table
primary = ah_config.get(
'database.primaryWriter.%s' % table_name,
ah_config.get('database.primaryWriter', 'sqlserver'))
if (primary == 'sqlserver'):
primary_db_name = sqlserver_db
secondary_db_name = aurora_db
else:
primary_db_name = aurora_db
secondary_db_name = sqlserver_db
primary_table = _get_table(primary_db_name, table_name)
secondary_table = _get_table(secondary_db_name, table_name)
with ah_db.open_db_connection(primary_db_name) as primary_connection:
with ah_db.open_db_connection(
secondary_db_name) as secondary_connection:
# First, the primary
delete = primary_table.delete().where(text(where_clause))
primary_connection.execute(delete)
# Now, the secondary
delete = secondary_table.delete().where(text(where_clause))
secondary_connection.execute(delete)
def migration_select_df(sqlserver_db,
aurora_db,
table_name,
sqlserver_query,
aurora_query=None):
log = ah_config.getLogger('ah.dbmigration')
# See if there is a primary reader specific to this table
primary = ah_config.get(
'database.primaryReader.%s' % table_name,
ah_config.get('database.primaryReader', 'sqlserver'))
if primary == 'sqlserver':
log.info(
'Migration read table {0} from sqlserver. Primary is {1}'.format(
table_name, primary))
with ah_db.open_db_connection(sqlserver_db) as connection:
return pd.read_sql(sqlserver_query, con=connection)
elif primary == 'aurora':
log.info('Migration read table {0} from aurora. Primary is {1}'.format(
table_name, primary))
if (aurora_query == None):
aurora_query = sqlserver_query
with ah_db.open_db_connection(aurora_db) as connection:
return pd.read_sql(aurora_query, con=connection)
else:
log.exception(
'Invalid primary DB for table {0}. Primary is {1}'.format(
table_name, primary))
def migration_update(sqlserver_db, aurora_db, table_name, data, where_clause):
# Normalize the column names
lower_data = _lower_data(data)
# See if there is a primary writer specific to this table
primary = ah_config.get(
'database.primaryWriter.%s' % table_name,
ah_config.get('database.primaryWriter', 'sqlserver'))
if (primary == 'sqlserver'):
primary_db_name = sqlserver_db
secondary_db_name = aurora_db
else:
primary_db_name = aurora_db
secondary_db_name = sqlserver_db
primary_table = _get_table(primary_db_name, table_name)
secondary_table = _get_table(secondary_db_name, table_name)
with ah_db.open_db_connection(primary_db_name) as primary_connection:
# First, the primary
column_data = _match_data_to_column_names(primary_table, lower_data)
update = primary_table.update().where(
text(where_clause)).values(column_data)
primary_connection.execute(update)
if _do_secondary_write():
# Now, the secondary
with ah_db.open_db_connection(
secondary_db_name) as secondary_connection:
column_data = _match_data_to_column_names(
secondary_table, lower_data)
update = secondary_table.update().where(
text(where_clause)).values(column_data)
secondary_connection.execute(update)
def _get_table(db_name, table_name):
metadata = _get_metadata(db_name)
schema = ah_config.get('database.' + db_name + '.schema', '')
full_table_name = (schema + '.' if schema else '') + table_name
if (full_table_name not in metadata.tables):
with ah_db.open_db_engine(db_name) as engine:
logging.getLogger('ah_db.misc').info("Creating table %s from %s",
full_table_name, db_name)
table = Table(table_name,
metadata,
autoload=True,
autoload_with=engine,
schema=schema)
# See if this is a table with a sequence primary key
sequence_tables = ah_config.get('database.sequenceTables', [])
table.is_sequence = table_name.lower() in sequence_tables
return metadata.tables[full_table_name]
def record_factory(*args, **kwargs):
record = old_factory(*args, **kwargs)
kwargs["message"] = record.msg
kwargs["serviceName"] = ah_config.get('app.riskName', 'risk')
if request:
if request_context and hasattr(
request_context,
'extras') and request_context.extras is not None:
kwargs = {**kwargs, **request_context.extras}
if hasattr(request, 'view_args') and request.view_args is not None:
kwargs = {**kwargs, **request.view_args}
if hasattr(request, 'remote_addr'):
kwargs["ip"] = request.remote_addr
if hasattr(request, 'url'):
kwargs["urlPath"] = request.path
record.msg = json.dumps(kwargs, separators=(',', ':'))
return record
def migration_insert(sqlserver_db, aurora_db, table_name, data):
# Normalize the column names
lower_data = _lower_data(data)
# See if there is a primary writer specific to this table
primary = ah_config.get(
'database.primaryWriter.%s' % table_name,
ah_config.get('database.primaryWriter', 'sqlserver'))
if (primary == 'sqlserver'):
primary_db_name = sqlserver_db
secondary_db_name = aurora_db
else:
primary_db_name = aurora_db
secondary_db_name = sqlserver_db
primary_table = _get_table(primary_db_name, table_name)
secondary_table = _get_table(secondary_db_name, table_name)
with ah_db.open_db_connection(primary_db_name) as primary_connection:
# Insert into the primary
column_data = _match_data_to_column_names(primary_table, lower_data)
ins = primary_table.insert().values(column_data)
result = primary_connection.execute(ins)
primary_keys = result.inserted_primary_key
new_pk_value = primary_keys[0] if len(primary_keys) > 0 else 'n/a'
logging.getLogger('ah_db.misc').info("Inserted into primary %s.%s: %s",
primary_db_name, table_name,
new_pk_value)
if not _do_secondary_write():
return new_pk_value
with ah_db.open_db_connection(
secondary_db_name) as secondary_connection:
# Now insert into secondary. See if this is a 'sequence table'
if (secondary_table.is_sequence):
# Since this is a "sequence" table, first get the Primary Key (we assume it is a single column)
pk_col = list(secondary_table.primary_key.columns)[0]
lower_data[pk_col.name.lower()] = new_pk_value
# Try to do the insert. We may get an exception
try:
column_data = _match_data_to_column_names(
secondary_table, lower_data)
ins = secondary_table.insert().values(column_data)
result = secondary_connection.execute(ins)
logging.getLogger('ah_db.misc').info(
"Inserted into secondary %s.%s: %s", secondary_db_name,
table_name, result.inserted_primary_key[0])
except:
# Let's do an update
update = secondary_table.update().where(
pk_col == new_pk_value).values(column_data)
secondary_connection.execute(update)
logging.getLogger('ah_db.misc').info(
"Updated into secondary %s.%s: %s", secondary_db_name,
table_name, new_pk_value)
else:
# This is not a "sequence" table. We just need to do an insert
column_data = _match_data_to_column_names(
secondary_table, lower_data)
ins = secondary_table.insert().values(column_data)
secondary_connection.execute(ins)
logging.getLogger('ah_db.misc').info(
"Inserted into secondary %s.%s", secondary_db_name,
table_name)
return new_pk_value
sys.path.append('src/')
# TODO not really necessary to go through here
import datadog_events
import deployments_db
import deployment_sources.earnin_standard
import deployment_sources.nativeapi_legacy
if __name__ == "__main__":
ah_config.initialize()
logging.getLogger().setLevel(logging.INFO)
logger = logging.getLogger('deployment_ingester')
datadog_events.initiatilize_datadog(
api_key=ah_config.get("datadog_credentials.api_key"),
app_key=ah_config.get("datadog_credentials.app_key"))
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
with ah_db.open_db_connection('engineering_metrics') as conn:
logger.info("Connected to %s", conn.engine.url.__repr__())
start = datetime.datetime.now() - datetime.timedelta(50)
end = datetime.datetime.now()
# deployments_db.upsert_deploys(conn,
# deployment_sources.nativeapi_legacy.query(start, end)
# )
deployments_db.upsert_deploys(