def drop_remake(progargs,
nlpdef: NlpDefinition,
incremental: bool = False,
skipdelete: bool = False) -> None:
"""
Drop output tables and recreate them.
"""
# Not parallel.
# -------------------------------------------------------------------------
# 1. Progress database
# -------------------------------------------------------------------------
progengine = nlpdef.get_progdb_engine()
if not incremental:
log.debug("Dropping progress tables")
NlpRecord.__table__.drop(progengine, checkfirst=True)
log.info("Creating progress table (with index)")
NlpRecord.__table__.create(progengine, checkfirst=True)
# -------------------------------------------------------------------------
# 2. Output database(s)
# -------------------------------------------------------------------------
pretty_names = [] # type: List[str]
for processor in nlpdef.get_processors():
new_pretty_names = processor.make_tables(drop_first=not incremental)
for npn in new_pretty_names:
if npn in pretty_names:
log.warning("An NLP processor has tried to re-make a table "
"made by one of its colleagues: {}".format(npn))
pretty_names.extend(new_pretty_names)
# -------------------------------------------------------------------------
# 3. Delete WHERE NOT IN for incremental
# -------------------------------------------------------------------------
for ifconfig in nlpdef.get_ifconfigs():
with MultiTimerContext(timer, TIMING_DELETE_WHERE_NO_SOURCE):
if incremental:
if not skipdelete:
delete_where_no_source(
nlpdef,
ifconfig,
report_every=progargs.report_every_fast,
chunksize=progargs.chunksize)
else: # full
ifconfig.delete_all_progress_records()
# -------------------------------------------------------------------------
# 4. Overall commit (superfluous)
# -------------------------------------------------------------------------
nlpdef.commit_all()
def delete_where_no_source(nlpdef: NlpDefinition,
ifconfig: InputFieldConfig,
report_every: int = DEFAULT_REPORT_EVERY,
chunksize: int = DEFAULT_CHUNKSIZE) -> None:
"""
Delete destination records where source records no longer exist.
- Can't do this in a single SQL command, since the engine can't necessarily
see both databases.
- Can't use a single temporary table, since the progress database isn't
necessarily the same as any of the destination database(s).
- Can't do this in a multiprocess way, because we're trying to do a
DELETE WHERE NOT IN.
- So we fetch all source PKs (which, by definition, do exist), stash them
keep them in memory, and do a DELETE WHERE NOT IN based on those
specified values (or, if there are no PKs in the source, delete
everything from the destination).
Problems:
- This is IMPERFECT if we have string source PKs and there are hash
collisions (e.g. PKs for records X and Y both hash to the same thing;
record X is deleted; then its processed version might not be).
- With massive tables, we might run out of memory or (much more likely)
SQL parameter slots. -- This is now happening; error looks like:
pyodbc.ProgrammingError: ('The SQL contains 30807 parameter parkers, but
2717783 parameters were supplied', 'HY000')
A better way might be:
- for each table, make a temporary table in the same database
- populate that table with (source PK integer/hash, source PK string) pairs
- delete where pairs don't match -- is that portable SQL?
http://stackoverflow.com/questions/7356108/sql-query-for-deleting-rows-with-not-in-using-2-columns # noqa
- More efficient would be to make one table per destination database.
On the "delete where multiple fields don't match":
- Single field syntax is
DELETE FROM a WHERE a1 NOT IN (SELECT b1 FROM b)
- Multiple field syntax is
DELETE FROM a WHERE NOT EXISTS (
SELECT 1 FROM b
WHERE a.a1 = b.b1
AND a.a2 = b.b2
)
- In SQLAlchemy, exists():
http://stackoverflow.com/questions/14600619
http://docs.sqlalchemy.org/en/latest/core/selectable.html
- Furthermore, in SQL NULL = NULL is false, and NULL <> NULL is also false,
so we have to do an explicit null check.
You do that with "field == None" (disable
See http://stackoverflow.com/questions/21668606
We're aiming, therefore, for:
DELETE FROM a WHERE NOT EXISTS (
SELECT 1 FROM b
WHERE a.a1 = b.b1
AND (
a.a2 = b.b2
OR (a.a2 IS NULL AND b.b2 IS NULL)
)
)
"""
# -------------------------------------------------------------------------
# Sub-functions
# -------------------------------------------------------------------------
def insert(records_):
n_rows = len(records_)
log.debug("... inserting {} records".format(n_rows))
for db in databases:
session_ = db['session']
temptable_ = db['temptable'] # type: Table
session_.execute(temptable_.insert(), records_)
nlpdef.notify_transaction(session_,
n_rows=n_rows,
n_bytes=sys.getsizeof(records_))
def commit():
for db in databases:
nlpdef.commit(db['session'])
# -------------------------------------------------------------------------
# Main code
# -------------------------------------------------------------------------
# Use info log level, otherwise it looks like our code hangs with very
# large databases.
log.info("delete_where_no_source: examining source table {}.{}; "
"MAY BE SLOW".format(ifconfig.get_srcdb(),
ifconfig.get_srctable()))
# Start our list with the progress database
databases = [{
'session': nlpdef.get_progdb_session(),
'engine': nlpdef.get_progdb_engine(),
'metadata': nlpdef.get_progdb_metadata(),
'temptable': None, # type: Table
}]
# Add the processors' destination databases
for processor in nlpdef.get_processors(): # of type BaseNlpParser
session = processor.get_session()
if any(x['session'] == session for x in databases):
continue # already exists
databases.append({
'session': session,
'engine': processor.get_engine(),
'metadata': processor.get_metadata(),
})
# Make a temporary table in each database (note: the Table objects become
# affiliated to their engine, I think, so make separate ones for each).
log.info("... using {n} destination database(s)".format(n=len(databases)))
log.info("... dropping (if exists) and creating temporary table(s)")
for database in databases:
engine = database['engine']
temptable = Table(
nlpdef.get_temporary_tablename(),
database['metadata'],
Column(FN_SRCPKVAL, BigInteger), # not PK, as may be a hash
Column(FN_SRCPKSTR, String(MAX_STRING_PK_LENGTH)),
**TABLE_KWARGS)
temptable.drop(engine, checkfirst=True)
temptable.create(engine, checkfirst=True)
database['temptable'] = temptable
# Insert PKs into temporary tables
n = count_star(ifconfig.get_source_session(), ifconfig.get_srctable())
log.info("... populating temporary table(s): {} records to go; working in "
"chunks of {}".format(n, chunksize))
i = 0
records = [] # type: List[Dict[str, Any]]
for pkval, pkstr in ifconfig.gen_src_pks():
i += 1
if report_every and i % report_every == 0:
log.info("... src row# {} / {}".format(i, n))
records.append({FN_SRCPKVAL: pkval, FN_SRCPKSTR: pkstr})
if i % chunksize == 0:
insert(records)
records = [] # type: List[Dict[str, Any]]
if records: # remainder
insert(records)
# Commit
commit()
# Index, for speed
log.info("... creating index(es) on temporary table(s)")
for database in databases:
temptable = database['temptable'] # type: Table
index = Index('_temptable_idx', temptable.columns[FN_SRCPKVAL])
index.create(database['engine'])
# DELETE FROM desttable WHERE destpk NOT IN (SELECT srcpk FROM temptable)
log.info("... deleting from progress/destination DBs where appropriate")
# Delete from progress database
prog_db = databases[0]
prog_temptable = prog_db['temptable']
ifconfig.delete_progress_records_where_srcpk_not(prog_temptable)
# Delete from others
for processor in nlpdef.get_processors():
database = [
x for x in databases if x['session'] == processor.get_session()
][0]
temptable = database['temptable']
processor.delete_where_srcpk_not(ifconfig, temptable)
# Drop temporary tables
log.info("... dropping temporary table(s)")
for database in databases:
database['temptable'].drop(database['engine'], checkfirst=True)
# Commit
commit()