def read_from_rds(event, context):
env = os.environ['env']
print('Setting environment to ' + env + '...')
dr = 'ODBC Driver 17 for SQL Server'
print('Getting parameters from parameter store...')
param = '/lambda-https-request/' + env + '/read-from-rds-ds-param'
ds = Functions.get_parameter(param, False)
param = '/lambda-https-request/' + env + '/read-from-rds-un-param'
un = Functions.get_parameter(param, False)
param = '/lambda-https-request/' + env + '/read-from-rds-pw-param'
pw = Functions.get_parameter(param, True)
conn = Functions.sql_server_conn(dr, ds, un, pw)
with conn:
with conn.cursor() as cur:
print('Building sql query...')
sql = 'SELECT element FROM dbo.vwLocalAreaDistrictLookup FOR JSON AUTO ;'
print('Attempting to read rows from sql query...')
cur.execute(sql)
result = cur.fetchall()
for row in result:
print('Building dictionary...')
row_dict = json.loads((row[0]))
print('Input json...' + row[0])
return row_dict
def invoke_snowflake_load_from_cloudwatch_event(event, context):
env = os.environ.get('env')
if env is None:
env = 'dev'
print('Setting environment to ' + env + '...')
print('Getting parameters from parameter store...')
# Snowflake connection parameters
param = '/snowflake/' + env + '/ac-param'
ac = Functions.get_parameter(param, False)
param = '/snowflake/' + env + '/un-param'
un = Functions.get_parameter(param, False)
param = '/snowflake/' + env + '/pw-param'
pw = Functions.get_parameter(param, True)
# Snowflake data load parameters
param = '/snowflake/' + env + '/role-param'
role = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/db-param'
db = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/schema-param'
schema = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/wh-param'
wh = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/file-format-param'
file_format = Functions.get_parameter(param, True)
# connect to snowflake data warehouse
try:
conn = sf.connect(
account=ac,
user=un,
password=pw,
role=role,
warehouse=wh,
database=db,
schema=schema,
ocsp_response_cache_filename="/tmp/ocsp_response_cache"
)
print('Snowflake connection opened...')
except Exception as e:
print(e)
try:
print('Got here')
try:
sql = 'ALTER WAREHOUSE {} RESUME'.format(wh)
print(sql)
with conn:
with conn.cursor() as cursor:
cursor.execute(sql)
except Exception as e:
print(e)
print('Got here2')
# get the object that triggered cloudwatch
# https://docs.aws.amazon.com/AmazonCloudWatch/latest/events/EventTypes.html#events-for-services-not-listed
try:
bucket = event['detail']['requestParameters']['bucketName']
key = event['detail']['requestParameters']['key']
print(
'bucket: ' + bucket
+ '\nkey: ' + key
)
except Exception as e:
print(e)
print('Got here3')
try:
sql = 'TRUNCATE ' + schema + '.OutputAreaJson'
print(sql)
with conn:
with conn.cursor() as cursor:
cursor.execute(sql)
except Exception as e:
print(e)
print('Got here4')
try:
sql = "copy into " + schema + ".OutputAreaJson from @" \
+ str.replace(bucket, "-", "_") + "/" + key[key.rindex('/')+1:len(key)] + \
" FILE_FORMAT = '" + file_format + "' ON_ERROR = 'ABORT_STATEMENT';"
print(sql)
with conn:
with conn.cursor() as cursor:
cursor.execute(sql)
except Exception as e:
print(e)
print('Got here5')
except Exception as e:
print(e)
finally:
conn.close()
print('Snowflake connection closed...')
if __name__ == "__main__":
# snowflake_validate({}, {})
json_event = "/var/task/event.json"
with open(json_event) as response:
_event = json.load(response)
invoke_snowflake_load_from_cloudwatch_event(_event, '')
def get_ons_oa_http_request(event, context):
env = os.environ['env']
print(f'Setting environment to {env}...')
print('Getting parameters from parameter store...')
param = '/lambda-https-request/' + env + '/s3-bucket-param'
s3bucket = Functions.get_parameter(param, False)
print(f'Parameter {param} value is: {s3bucket}')
param = '/lambda-https-request/' + env + '/ons-oa-lookup-url-param'
base_url = Functions.get_parameter(param, False)
print(f'Parameter {param} value is: {base_url}')
param = '/lambda-https-request/' + env + '/max-loops-param'
max_loops = int(Functions.get_parameter(param, False))
print(f'Parameter {param} value is: {max_loops}')
param = '/lambda-https-request/' + env + '/timeout-param'
timeout = int(Functions.get_parameter(param, False))
print(f'Parameter {param} value is: {timeout}')
param = '/lambda-https-request/' + env + '/result-record-count'
result_record_count = int(Functions.get_parameter(param, False))
print(f'Parameter {param} value is: {result_record_count}')
param = '/lambda-https-request/' + env + '/max-error-loops-param'
max_error_loops = int(Functions.get_parameter(param, False))
print(f'Parameter {param} value is: {max_error_loops}')
try:
exceeded_transfer_limit = True
counter = 1
error_counter = 1
offset = 0
url_result_record_count = 'resultRecordCount=' + str(result_record_count)
event_list = json.loads(json.dumps(event, indent=4))
attribute = event_list['attribute']
print(f'Lookup attribute is: {attribute}')
while exceeded_transfer_limit:
curr_datetime = datetime.now()
curr_datetime_str = curr_datetime.strftime('%Y%m%d_%H%M%S%f')
filekey = attribute + '_' + curr_datetime_str + '_' + str(counter) + '.json'
print('Building URL...')
url_offset = 'resultOffset=' + str(offset)
urls = [base_url, url_offset, url_result_record_count]
join_urls = '&'.join(urls)
url = join_urls.replace('<attribute>', attribute)
print(f'URL: {url} built...')
while error_counter < max_error_loops:
print('Attempting to load api (' + str(error_counter) + ') of (' + str(max_error_loops) + ')...')
data = Functions.load_api(filekey, timeout, url)
if data.get('error'):
print(f'API returned error message: {data}')
else:
break
error_counter += 1
if error_counter == max_error_loops:
print('You have reached the maximum number of error loops (' + str(max_error_loops) + ')')
break
if data.get('exceededTransferLimit'):
exceeded_transfer_limit = json.dumps(data['exceededTransferLimit'])
else:
exceeded_transfer_limit = False
offset = 0
if data.get('features'):
number_of_features = len(data['features'])
offset += number_of_features
print(f'Json string for {attribute} loop {counter} contains {number_of_features} features')
else:
print('Json data does not contain features objects')
break
if counter == max_loops:
print('You have reached the maximum number of loops (' + str(max_loops) + ')')
break
counter += 1
Functions.upload_file_to_s3(s3bucket, filekey, data)
except Exception as e:
print(e)
return event_list['elements']
#
#def snowflake_validate(event, context):
# env = os.environ['env']
# print('Setting environment to ' + env + '...')
# print('Getting parameters from parameter store...')
# param = '/snowflake/' + env + '/ac-param'
# ac = Functions.get_parameter(param, False)
# param = '/snowflake/' + env + '/un-param'
# un = Functions.get_parameter(param, False)
# param = '/snowflake/' + env + '/pw-param'
# pw = Functions.get_parameter(param, True)
# # connect to snowflake data warehouse
# conn = snowflake.connector.connect(
# account=ac,
# user=un,
# password=pw
# )
# sql = "SELECT current_version()"
# with conn:
# with conn.cursor() as cur:
# cur.execute(sql)
# one_row = cur.fetchone()
# print(one_row[0])
def invoke_snowflake_load_from_s3_event(event, context):
env = os.environ.get('env')
if env is None:
env = 'dev'
print('Setting environment to ' + env + '...')
print('Getting parameters from parameter store...')
# Snowflake connection parameters
param = '/snowflake/' + env + '/ac-param'
ac = Functions.get_parameter(param, False)
param = '/snowflake/' + env + '/un-param'
un = Functions.get_parameter(param, False)
param = '/snowflake/' + env + '/pw-param'
pw = Functions.get_parameter(param, True)
# Snowflake data load parameters
param = '/snowflake/' + env + '/role-param'
role = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/db-param'
db = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/schema-param'
schema = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/wh-param'
wh = Functions.get_parameter(param, True)
param = '/snowflake/' + env + '/file-format-param'
file_format = Functions.get_parameter(param, True)
# connect to snowflake data warehouse
try:
conn = sf.connect(
account=ac,
user=un,
password=pw,
role=role,
warehouse=wh,
database=db,
schema=schema,
ocsp_response_cache_filename="/tmp/ocsp_response_cache"
)
print(str(conn))
print('Snowflake connection opened...')
except Exception as e:
print(e)
try:
sql = 'USE ROLE {}'.format(role)
Functions.execute_query(conn, sql)
sql = 'SELECT current_role()'
print('role: ' + Functions.return_query(conn, sql))
sql = 'SELECT current_warehouse()'
print('warehouse: ' + Functions.return_query(conn, sql))
try:
sql = 'ALTER WAREHOUSE {} RESUME'.format(wh)
Functions.execute_query(conn, sql)
except Exception as e:
print(e)
sql = 'SELECT current_schema()'
print('schema: ' + Functions.return_query(conn, sql))
sql = 'SELECT current_database()'
print('database: ' + Functions.return_query(conn, sql))
# fs = s3fs.S3FileSystem(anon=False)
# fs.mkdir("tfgm-wallingtonp")
# fs.touch("tfgm-wallingtonp/test.txt")
# fs.ls("tfgm-wallingtonp/")
# get the object that triggered lambda
# https://docs.aws.amazon.com/AmazonS3/latest/dev/notification-content-structure.html
try:
bucket = event['Records'][0]['s3']['bucket']['name']
arn = event['Records'][0]['s3']['bucket']['arn']
for record in event['Records']:
key = record['s3']['object']['key']
size = record['s3']['object']['size']
print(
'bucket: ' + bucket
+ '\narn: ' + arn
+ '\nkey: ' + key
+ '\nsize: ' + str(size)
)
except Exception as e:
print(e)
try:
sql = 'TRUNCATE ' + schema + '.OutputAreaJson'
print(sql)
Functions.execute_query(conn, sql)
sql = "copy into " + schema + ".OutputAreaJson from @" + str.replace(bucket, "-", "_") + "/" + key + \
" FILE_FORMAT = '" + file_format + "' ON_ERROR = 'ABORT_STATEMENT';"
print(sql)
Functions.execute_query(conn, sql)
except Exception as e:
print(e)
# sql = 'SELECT current_version()'
# with conn:
# with conn.cursor() as cursor:
# cursor.execute(sql)
# result = cursor.fetchone()
# print(result)
# print('Got here2')
# for c in cursor:
# print(c)
# except Exception as e:
# print(e)
# finally:
# cursor.close()
# print(one_row[0])
# sql = 'select * from "TFGMDW"."STG"."OUTPUTAREAJSON"'
# with conn:
# with conn.cursor() as cur:
# cur.execute(sql)
# for c in cur:
# print(c)
# one_row = cur.fetchone()
# print(one_row[0])
except Exception as e:
print(e)
finally:
conn.close()
print('Snowflake connection closed...')