def start_run (event, context):
if 'view_name' in event:
snowflake_view = event['view_name']
print("now running snowflake view: ", snowflake_view)
cs = get_snowflake_cursor()
try:
print("starting to execute query")
# cs.execute("use warehouse " + os.environ['SNOWFLAKE_WAREHOUSE'] + ";")
# cs.execute("use schema " + os.environ['SNOWFLAKE_SCHEMA'] + ";")
cs.execute("SELECT * from " + snowflake_view, _no_results=True)
query_id = cs.sfqid
print("query id: ", query_id)
if 'post_back_url' in event:
url = event['post_back_url']
print("now trying to post back to: ", url)
data = "Now running query_id: " + query_id
auth = AWS4Auth(os.environ['AWS_ACCESS_KEY_ID'], os.environ['AWS_SECRET_ACCESS_KEY'], 'us-east-1', 'execute-api', session_token = os.environ['AWS_SESSION_TOKEN'])
r = requests.post(url, auth=auth, data=data)
finally:
pass
#cs.close() <-- purposely not closing the connection since we want this to be async
return query_id
def post_back_results (event, context):
if 'query_id' in event:
query_id = event['query_id']
cs = get_snowflake_cursor()
try:
cs.execute("select * from table(result_scan('" + query_id + "')) limit 100;")
results = cs.fetchall()
finally:
cs.close()
columns = []
for col in cs.description:
columns.append(col[0])
json_results = []
for rec in results:
json_rec = {}
for col in columns:
#print('%s: %s' % (col, rec[columns.index(col)]))
json_rec[col] = str(rec[columns.index(col)])
print(json_rec)
json_results.append(json_rec)
json_root = {}
json_root['query_id'] = query_id
json_root['results'] = json_results
#print(json.dumps(json_root))
if 'post_back_url' in event:
url = event['post_back_url']
print("now trying to post back to: ", url)
auth = AWS4Auth(os.environ['AWS_ACCESS_KEY_ID'], os.environ['AWS_SECRET_ACCESS_KEY'], 'us-east-1', 'execute-api', session_token = os.environ['AWS_SESSION_TOKEN'])
r = requests.post(url, auth=auth, json=json_root)
print(r.status_code)
def get_execution_status (event, context):
if 'query_id' in event:
query_id = event['query_id']
cs = get_snowflake_cursor()
try:
cs.execute("select execution_status from table(information_schema.query_history()) where query_id like '" + query_id + "';")
status = cs.fetchone()[0]
#print(one_row[0])
finally:
cs.close()
return status
def fetch_results(event, context):
if 'body' in event:
body = json.loads(event['body'])
if 'query_id' in body:
query_id = body['query_id']
if 'offset' in body:
offset = body['offset']
else:
offset = "0"
cs = get_snowflake_cursor()
try:
cs.execute("use warehouse " +
os.environ['SNOWFLAKE_WAREHOUSE'] + ";")
cs.execute("use schema " + os.environ['SNOWFLAKE_SCHEMA'] +
";")
cs.execute("select * from table(result_scan('" + query_id +
"')) limit 100 offset " + offset + ";")
#print(','.join([col[0] for col in cs.description]))
columns = []
for col in cs.description:
columns.append(col[0])
results = cs.fetchall()
json_results = []
for rec in results:
json_rec = {}
for col in columns:
#print('%s: %s' % (col, rec[columns.index(col)]))
json_rec[col] = str(rec[columns.index(col)])
print(json_rec)
json_results.append(json_rec)
json_root = {}
json_root['query_id'] = query_id
json_root['results'] = json_results
print(json.dumps(json_root))
message = json.dumps(json_root)
url = _get_postback_url(event)
auth = AWS4Auth(os.environ['AWS_ACCESS_KEY_ID'],
os.environ['AWS_SECRET_ACCESS_KEY'],
'us-east-1',
'execute-api',
session_token=os.environ['AWS_SESSION_TOKEN'])
r = requests.post(url, auth=auth, json=json_root)
print(r.status_code)
finally:
cs.close()
else:
message = "No query_id provided."
return _get_response(200, message)
def post_back_error_message (event, context):
if 'query_id' in event:
query_id = event['query_id']
cs = get_snowflake_cursor()
try:
cs.execute("select error_message from table(information_schema.query_history()) where query_id like '" + query_id + "';")
error_message = cs.fetchone()[0]
if 'post_back_url' in event:
url = event['post_back_url']
print("now trying to post back to: ", url)
auth = AWS4Auth(os.environ['AWS_ACCESS_KEY_ID'], os.environ['AWS_SECRET_ACCESS_KEY'], 'us-east-1', 'execute-api', session_token = os.environ['AWS_SESSION_TOKEN'])
r = requests.post(url, auth=auth, data=error_message)
finally:
cs.close()