def execute_sql(redshift_engine, datastore, action):
"""
:type redshift_engine: dart.engine.redshift.redshift.RedshiftEngine
:type datastore: dart.model.datastore.Datastore
:type action: dart.model.action.Action
"""
cluster = RedshiftCluster(redshift_engine, datastore)
conn = cluster.get_db_connection()
txn = conn.begin()
try:
action = redshift_engine.dart.patch_action(action, progress=.1)
datastore_user_id = datastore.data.user_id if hasattr(
datastore.data, 'user_id') else 'anonymous'
sanitzed_sql = sanitized_query(action.data.args['sql_script'])
# Adding --USER-ID helps identify who this query run belongs to.
sql_to_execute = "---USER_ID={user_id}\n{sanitzed_sql}".format(
sanitzed_sql=sanitzed_sql, user_id=datastore_user_id)
conn.execute(sql_to_execute)
txn.commit()
redshift_engine.dart.patch_action(action, progress=1)
except:
txn.rollback()
raise
finally:
conn.close()
def unload_to_s3(action, conn):
""" :type action: dart.model.action.Action """
args = action.data.args
aws_access_key_id, aws_secret_access_key, security_token = lookup_credentials(action)
now = datetime.utcnow()
sql = """
UNLOAD ('{statement}') TO '{s3_path}'
CREDENTIALS 'aws_access_key_id={aws_access_key_id};aws_secret_access_key={aws_secret_access_key}{token}'
ALLOWOVERWRITE
NULL AS 'NULL'
ESCAPE
DELIMITER '{delimiter}'
PARALLEL {parallel}
GZIP;
""".format(
statement=sanitized_query(args['source_sql_statement'].replace("'", "''")),
s3_path=args['destination_s3_path'].replace(
'{YEAR}', now.strftime('%Y')).replace(
'{MONTH}', now.strftime('%m')).replace(
'{DAY}', now.strftime('%d')).replace(
'{HOUR}', now.strftime('%H')).replace(
'{MINUTE}', now.strftime('%M')).replace(
'{SECOND}', now.strftime('%S')),
delimiter=args['delimiter'] if args.get('delimiter') else '\t',
parallel='ON' if args['parallel'] else 'OFF',
aws_access_key_id=aws_access_key_id,
aws_secret_access_key=aws_secret_access_key,
token=';token=%s' % security_token if security_token else '',
)
conn.execute(sql)
def data_check(redshift_engine, datastore, action):
"""
:type redshift_engine: dart.engine.redshift.redshift.RedshiftEngine
:type datastore: dart.model.datastore.Datastore
:type action: dart.model.action.Action
"""
cluster = RedshiftCluster(redshift_engine, datastore)
conn = cluster.get_db_connection()
try:
action = redshift_engine.dart.patch_action(action, progress=.1)
result = list(conn.execute(sanitized_query(action.data.args['sql_script'])))[0][0]
if result:
redshift_engine.dart.patch_action(action, progress=1)
else:
raise Exception('Data check failed')
finally:
conn.close()
def execute_sql(redshift_engine, datastore, action):
"""
:type redshift_engine: dart.engine.redshift.redshift.RedshiftEngine
:type datastore: dart.model.datastore.Datastore
:type action: dart.model.action.Action
"""
cluster = RedshiftCluster(redshift_engine, datastore)
conn = cluster.get_db_connection()
txn = conn.begin()
try:
action = redshift_engine.dart.patch_action(action, progress=.1)
conn.execute(sanitized_query(action.data.args['sql_script']))
txn.commit()
redshift_engine.dart.patch_action(action, progress=1)
except:
txn.rollback()
raise
finally:
conn.close()
def data_check(redshift_engine, datastore, action):
"""
:type redshift_engine: dart.engine.redshift.redshift.RedshiftEngine
:type datastore: dart.model.datastore.Datastore
:type action: dart.model.action.Action
"""
cluster = RedshiftCluster(redshift_engine, datastore)
conn = cluster.get_db_connection()
try:
action = redshift_engine.dart.patch_action(action, progress=.1)
result = list(
conn.execute(sanitized_query(
action.data.args['sql_script'])))[0][0]
if result:
redshift_engine.dart.patch_action(action, progress=1)
else:
raise Exception('Data check failed')
finally:
conn.close()