def delete_model(self, model_name):
if not self.is_postgre_sql:
sql_statement = "DELETE FROM %s.%s where ENTITY_TYPE_ID = ? and MODEL_NAME = ?" % (
self.quoted_schema, self.quoted_store_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Deletion of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
else:
sql_statement = "DELETE FROM %s.%s" % (self.quoted_schema, self.quoted_store_tablename)
sql_statement += ' where entity_type_id = %s and model_name = %s'
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement, (self.entity_type_id, model_name))
except Exception as ex:
raise Exception(
'Deletion of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
logger.info('Model %s has been deleted from table %s.%s' % (
model_name, self.quoted_schema, self.quoted_store_tablename))
def _create_cache_table(self):
if not self.is_postgre_sql:
sql_statement = "CREATE TABLE %s.%s ( " \
"ENTITY_TYPE_ID BIGINT NOT NULL, " \
"PARQUET_NAME VARCHAR(2048) NOT NULL, " \
"PARQUET_FILE BLOB(2G), " \
"UPDATED_TS TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, " \
"CONSTRAINT %s UNIQUE(ENTITY_TYPE_ID, PARQUET_NAME) ENFORCED ) " \
"ORGANIZE BY ROW" % (self.quoted_schema, self.quoted_cache_tablename,
dbhelper.quotingTableName('uc_%s' % self.cache_tablename,
self.is_postgre_sql))
try:
stmt = ibm_db.exec_immediate(self.db_connection, sql_statement)
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception('Execution of sql statement "%s" failed.' % sql_statement) from ex
else:
sql_statement = "CREATE TABLE %s.%s ( " \
"entity_type_id BIGINT NOT NULL, " \
"parquet_name VARCHAR(2048) NOT NULL, " \
"parquet_file BYTEA, " \
"updated_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, " \
"CONSTRAINT %s UNIQUE(entity_type_id, parquet_name))" % (
self.quoted_schema, self.quoted_cache_tablename,
dbhelper.quotingTableName('uc_%s' % self.cache_tablename, self.is_postgre_sql))
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement)
except Exception as ex:
raise Exception('Execution of sql statement "%s" failed.' % sql_statement) from ex
logger.info('Table %s.%s has been created.' % (self.quoted_schema, self.quoted_cache_tablename))
def _push_cache(self, cache_filename, cache_pathname):
if not self.is_postgre_sql:
sql_statement = "MERGE INTO %s.%s AS TARGET " \
"USING (VALUES (?, ?, ?, CURRENT_TIMESTAMP)) " \
"AS SOURCE (ENTITY_TYPE_ID, PARQUET_NAME, PARQUET_FILE, UPDATED_TS) " \
"ON TARGET.ENTITY_TYPE_ID = SOURCE.ENTITY_TYPE_ID " \
"AND TARGET.PARQUET_NAME = SOURCE.PARQUET_NAME " \
"WHEN MATCHED THEN " \
"UPDATE SET TARGET.PARQUET_FILE = SOURCE.PARQUET_FILE, " \
"TARGET.UPDATED_TS = SOURCE.UPDATED_TS " \
"WHEN NOT MATCHED THEN " \
"INSERT (ENTITY_TYPE_ID, PARQUET_NAME, PARQUET_FILE, UPDATED_TS) " \
"VALUES (SOURCE.ENTITY_TYPE_ID, SOURCE.PARQUET_NAME, SOURCE.PARQUET_FILE, " \
"SOURCE.UPDATED_TS)" % (self.quoted_schema, self.quoted_cache_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, cache_filename)
ibm_db.bind_param(stmt, 3, cache_pathname, ibm_db.PARAM_FILE, ibm_db.SQL_BLOB)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception('Storing cache file %s under name %s failed with sql statement "%s"' % (
cache_pathname, cache_filename, sql_statement)) from ex
else:
try:
f = open(cache_pathname, 'rb')
try:
blob = f.read()
finally:
f.close()
except Exception as ex:
raise Exception('The cache file %s could not be read from disc.' % cache_pathname) from ex
else:
statement1 = "INSERT INTO %s.%s (entity_type_id, parquet_name, parquet_file, updated_ts) " % (
self.quoted_schema, self.quoted_cache_tablename)
statement3 = "ON CONFLICT ON CONSTRAINT %s DO update set entity_type_id = EXCLUDED.entity_type_id, " \
"parquet_name = EXCLUDED.parquet_name, parquet_file = EXCLUDED.parquet_file, " \
"updated_ts = EXCLUDED.updated_ts" % dbhelper.quotingTableName(
('uc_%s' % self.cache_tablename), self.is_postgre_sql)
sql_statement = statement1 + " values (%s, %s, %s, current_timestamp) " + statement3
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement,
(self.entity_type_id, cache_filename, psycopg2.Binary(blob)))
except Exception as ex:
raise Exception('Storing cache under name %s failed with sql statement "%s"' % (
cache_filename, sql_statement)) from ex
logger.info('Cache has been stored under name %s in table %s.%s' % (
cache_filename, self.quoted_schema, self.quoted_cache_tablename))
def delete_all_caches(self):
# Delete all cache entries for this entity type locally
cache_filename, cache_pathname, base_path = self._get_cache_filename(
None, None)
if os.path.exists(base_path):
try:
file_listing = os.listdir(base_path)
except Exception as ex:
raise Exception('Failure to list content of directory %s' %
base_path) from ex
for filename in file_listing:
if filename.startswith(DBDataCache.CACHE_FILE_STEM):
full_path = '%s/%s' % (base_path, filename)
try:
os.remove(full_path)
except Exception as ex:
raise Exception('Removal of file %s failed' %
full_path) from ex
# Delete all cache entries for this entity type in database
if not self.is_postgre_sql:
sql_statement = "DELETE FROM %s.%s where ENTITY_TYPE_ID = ?" % (
self.quoted_schema, self.quoted_cache_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Deletion of cache files failed with sql statement "%s"' %
sql_statement) from ex
else:
sql_statement = "DELETE FROM %s.%s" % (
self.quoted_schema,
self.quoted_cache_tablename,
)
sql_statement += ' where entity_type_id = %s'
try:
dbhelper.execute_postgre_sql_query(self.db_connection,
sql_statement,
(self.entity_type_id, ))
except Exception as ex:
raise Exception(
'Deletion of cache files failed with sql statement %s' %
sql_statement) from ex
logger.info(
'All caches have been deleted from table %s.%s for entity type id %d'
% (self.quoted_schema, self.quoted_cache_tablename,
self.entity_type_id))
def store_model(self, model_name, model, user_name=None, serialize=True):
if serialize:
try:
model = pickle.dumps(model)
except Exception as ex:
raise Exception(
'Serialization of model %s that is supposed to be stored in ModelStore failed.' % model_name) from ex
if not self.is_postgre_sql:
sql_statement = "MERGE INTO %s.%s AS TARGET " \
"USING (VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)) " \
"AS SOURCE (ENTITY_TYPE_ID, MODEL_NAME, MODEL, UPDATED_TS, LAST_UPDATED_BY) " \
"ON TARGET.ENTITY_TYPE_ID = SOURCE.ENTITY_TYPE_ID " \
"AND TARGET.MODEL_NAME = SOURCE.MODEL_NAME " \
"WHEN MATCHED THEN " \
"UPDATE SET TARGET.MODEL = SOURCE.MODEL, " \
"TARGET.UPDATED_TS = SOURCE.UPDATED_TS " \
"WHEN NOT MATCHED THEN " \
"INSERT (ENTITY_TYPE_ID, MODEL_NAME, MODEL, UPDATED_TS, LAST_UPDATED_BY) " \
"VALUES (SOURCE.ENTITY_TYPE_ID, SOURCE.MODEL_NAME, SOURCE.MODEL, " \
"SOURCE.UPDATED_TS, SOURCE.LAST_UPDATED_BY)" % (
self.quoted_schema, self.quoted_store_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.bind_param(stmt, 3, model)
ibm_db.bind_param(stmt, 4, user_name)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception('Storing model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
else:
statement1 = "INSERT INTO %s.%s (entity_type_id, model_name, model, updated_ts, last_updated_by) " % (
self.quoted_schema, self.quoted_store_tablename)
statement3 = "ON CONFLICT ON CONSTRAINT %s DO update set entity_type_id = EXCLUDED.entity_type_id, " \
"model_name = EXCLUDED.model_name, model = EXCLUDED.model, " \
"updated_ts = EXCLUDED.updated_ts, last_updated_by = EXCLUDED.last_updated_by" % dbhelper.quotingTableName(
('uc_%s' % self.store_tablename), self.is_postgre_sql)
sql_statement = statement1 + " values (%s, %s, %s, current_timestamp, %s) " + statement3
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement,
(self.entity_type_id, model_name, psycopg2.Binary(model), user_name))
except Exception as ex:
raise Exception('Storing model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
logger.info('Model %s of size %d bytes has been stored in table %s.%s.' % (
model_name, len(model) if model is not None else 0, self.quoted_schema, self.quoted_store_tablename))
def delete_cache(self, dep_grain, grain, old_name=False):
# Delete single cache entry locally
cache_filename, cache_pathname, base_path = self._get_cache_filename(
dep_grain, grain, old_name)
if os.path.exists(cache_pathname):
try:
os.remove(cache_pathname)
except Exception as ex:
raise Exception('Removal of cache file %s failed' %
cache_pathname) from ex
# Delete single cache entry in database
if not self.is_postgre_sql:
sql_statement = "DELETE FROM %s.%s WHERE ENTITY_TYPE_ID = ? AND PARQUET_NAME = ?" % (
self.quoted_schema, self.quoted_cache_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, cache_filename)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Deletion of cache file %s failed with sql statement "%s"'
% (cache_filename, sql_statement)) from ex
else:
sql_statement = "DELETE FROM %s.%s" % (self.quoted_schema,
self.quoted_cache_tablename)
sql_statement += ' where entity_type_id = %s and parquet_name = %s'
try:
dbhelper.execute_postgre_sql_query(
self.db_connection, sql_statement,
(self.entity_type_id, cache_filename))
except Exception as ex:
raise Exception(
'Deletion of cache file %s failed with sql statement %s' %
(cache_filename, sql_statement)) from ex
logger.info(
'Cache file %s has been deleted from table %s.%s' %
(cache_filename, self.quoted_schema, self.quoted_cache_tablename))
