def insert(table, values, overwrite=False, select="", columns=[]):
if len(values) == 0:
return
if type(select) is tuple:
select = ', '.join(select)
if select:
select = f'SELECT {select} FROM '
overwrite = ' OVERWRITE' if overwrite else ''
columns = f' ({", ".join(columns)})' if columns else ''
sql = (f"INSERT{overwrite}\n"
f" INTO {table}{columns}\n"
f" {select}VALUES {sql_value_placeholders(len(values))}\n"
f";")
jsony = (dict, list, tuple, Exception, datetime)
params_with_json = [[
v.isoformat() if isinstance(
v, datetime) else utils.json_dumps(v) if isinstance(v, jsony) else
utils.format_exception(v) if isinstance(v, Exception) else v
for v in vp
] for vp in values]
return execute(sql, params=params_with_json, fix_errors=False)
def record_metadata(metadata, table, e=None):
ctx = connect()
if e is None and 'EXCEPTION' in metadata:
e = metadata['EXCEPTION']
del metadata['EXCEPTION']
if e is not None:
exception_only = utils.format_exception_only(e)
metadata['ERROR'] = {
'EXCEPTION': utils.format_exception(e),
'EXCEPTION_ONLY': exception_only,
}
if exception_only.startswith(
'snowflake.connector.errors.ProgrammingError: '):
metadata['ERROR']['PROGRAMMING_ERROR'] = exception_only[45:]
metadata.setdefault('ROW_COUNT', {
'INSERTED': 0,
'UPDATED': 0,
'SUPPRESSED': 0,
'PASSED': 0
})
metadata['END_TIME'] = datetime.utcnow()
metadata['DURATION'] = str(metadata['END_TIME'] - metadata['START_TIME'])
metadata['START_TIME'] = str(metadata['START_TIME'])
metadata['END_TIME'] = str(metadata['END_TIME'])
record_type = metadata.get('QUERY_NAME', 'RUN')
metadata_json_sql = "'" + json.dumps(metadata).replace(
'\\', '\\\\').replace("'", "\\'") + "'"
sql = f'''
INSERT INTO {table}(event_time, v)
SELECT '{metadata['START_TIME']}'
, PARSE_JSON(column1)
FROM VALUES({metadata_json_sql})
'''
try:
ctx.cursor().execute(sql)
log.info(f"{record_type} metadata recorded.")
except Exception as e:
log.error(f"{record_type} metadata failed to log.", e)
def record_metadata(metadata, table, e=None):
ctx = connect()
if e is None and 'EXCEPTION' in metadata:
e = metadata['EXCEPTION']
del metadata['EXCEPTION']
if e is not None:
exception_only = utils.format_exception_only(e)
metadata['ERROR'] = {
'EXCEPTION': utils.format_exception(e),
'EXCEPTION_ONLY': exception_only,
}
if exception_only.startswith(
'snowflake.connector.errors.ProgrammingError: '):
metadata['ERROR']['PROGRAMMING_ERROR'] = exception_only[45:]
metadata.setdefault('ROW_COUNT', {
'INSERTED': 0,
'UPDATED': 0,
'SUPPRESSED': 0,
'PASSED': 0
})
metadata['START_TIME'] = str(metadata['START_TIME'])
metadata['END_TIME'] = str(datetime.utcnow())
metadata['DURATION'] = (
datetime.fromisoformat(metadata['END_TIME']) -
datetime.fromisoformat(metadata['START_TIME'])).total_seconds()
record_type = metadata.get('QUERY_NAME', 'RUN')
metadata_json_sql = value_to_sql(metadata)
sql = f'''
INSERT INTO {table}(event_time, v)
SELECT '{metadata['START_TIME']}'
, {metadata_json_sql}
'''
try:
ctx.cursor().execute(sql)
log.info(f"{record_type} metadata recorded.")
except Exception as e:
log.error(f"{record_type} metadata failed to log.", e)
def do_insert(table, values, overwrite=False, select="", columns=[], dryrun=False):
if len(values) == 0:
return {'number of rows inserted': 0}
if type(values[0]) is dict:
select, columns = determine_cols(values)
values = [tuple(v.get(c) for c in columns) for v in values]
if type(select) in (tuple, list):
select = ', '.join(select)
if select:
select = f'SELECT {select} FROM '
overwrite = ' OVERWRITE' if overwrite else ''
columns = f' ({", ".join(columns)})' if columns else ''
sql = (
f"INSERT{overwrite}\n"
f" INTO {table}{columns}\n"
f" {select}VALUES {sql_value_placeholders(len(values))}\n"
f";"
)
params_with_json = [
[
v.isoformat()
if isinstance(v, datetime)
else utils.json_dumps(v)
if isinstance(v, JSONY)
else utils.format_exception(v)
if isinstance(v, Exception)
else v
for v in vp
]
for vp in values
]
if dryrun:
print('db.insert', table, columns, values)
return {'number of rows inserted': len(values)}
return next(fetch(sql, params=params_with_json, fix_errors=False))