Use Postman to make an API call; the Amazon API Gateway will trigger a Lambda function; the function will write into the s3 bucket; then, Snowpipe will start to write the data into a Snowflake. To not activate Snowpipe for every API call and optimize the process, we will use Amazon Kinesis data Stream to collect the data first (dump the data based on the buffer size or the time or if there are few messages), then use Kinesis Firehouse to leave the data into s3.
![Screen Shot 2024-01-02 at 12 55 54 PM](https://private-user-images.githubusercontent.com/74584964/293641972-3594ab96-fceb-4bce-90af-e6567cf32f83.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDE5NzItMzU5NGFiOTYtZmNlYi00YmNlLTkwYWYtZTY1NjdjZjMyZjgzLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTkzOWU1ZjMwMjY2YTlmYmI5ZWM2ZWQzODA4M2I1ODRiYWViZWIxN2I4NDRhYjRkNzEwMTM2M2FmMDVkYWFlYmYmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.uGrwqjBMz5EXB8Q5QcPjhVhLNqhv0MWf_V47iotMMtc)
Step 2: Create the lambda function to read the data from API Gateway and put it in Kinesis Data Stream
![Screen Shot 2024-01-02 at 12 57 50 PM](https://private-user-images.githubusercontent.com/74584964/293642049-8f5184fa-e91b-4922-b215-15cef8978c14.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDIwNDktOGY1MTg0ZmEtZTkxYi00OTIyLWIyMTUtMTVjZWY4OTc4YzE0LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTZjNTE0MDA2MmY0MmQwMTNkYWZkNDYyZTJhMGMzNmVlMjIxNDRiMTgxMDBkMGEwOGM0YTk2M2ZkNGJmNDc3MTEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.QqGtJxETM_e1rzFT7nZ3qyfx_XZ4S0crSmKrxPlUZ3M)
![Screen Shot 2024-01-02 at 12 57 59 PM](https://private-user-images.githubusercontent.com/74584964/293642073-0d5a7ea4-7224-4a1f-89e3-b7553e2eaf0d.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDIwNzMtMGQ1YTdlYTQtNzIyNC00YTFmLTg5ZTMtYjc1NTNlMmVhZjBkLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTgyYjk4N2Y1NThlMWZhMTQ2MmRhMTA5OTdkZTQ3NGM3ODYxZTdiMmUyYzcyMGZmYWFmMmQwMDgyOTk4YjY5ZGEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.vgAkCbQubQZNMbAxWeIyA5vwiMWb42HzRW0qxCUgahE)
import json
import datetime
import random
import boto3
client = boto3.client('kinesis')
def lambda_handler(event, context):
TODO implement
data = json.dumps(event['body'])
client.put_record(StreamName="project3_kinesis_apigateway", Data=data, PartitionKey="1")
print("Data Inserted")
- Create a simple HTTP API and integrate it with the lambda function.
![Screen Shot 2024-01-02 at 1 03 12 PM](https://private-user-images.githubusercontent.com/74584964/293642284-7463dcef-ea73-4f20-a476-c7cb484d2310.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDIyODQtNzQ2M2RjZWYtZWE3My00ZjIwLWE0NzYtYzdjYjQ4NGQyMzEwLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWZlM2E5YWNmOWZhOGI4MjNkZjRiNDhiNjYyMzE2NzQ3MTMzMWMxMTI4NTExNmM3OGM5ZDEyYTRlMTMzOGE1YmYmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.FWquyOfb1ZlpnqiDyNWsXlyoarED0DOckoAodj_VgNc)
- Configure the route: POST method.
![Screen Shot 2024-01-02 at 1 07 18 PM](https://private-user-images.githubusercontent.com/74584964/293642443-505f5ede-d66a-43ed-9dff-0a282159ba55.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDI0NDMtNTA1ZjVlZGUtZDY2YS00M2VkLTlkZmYtMGEyODIxNTliYTU1LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTE1MjdkNjRmNzgzZTExYzA5ZTA0MDY1NmU0NTRjNGZjMjcxM2NkNjExODY0MmJkY2RjMTExYWU4MTRhNWNjNGQmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.tvPG_mooy23Aq1fElj8ehLo4tODrOHBfb0F7-Dh2LVk)
![Screen Shot 2024-01-02 at 1 07 37 PM](https://private-user-images.githubusercontent.com/74584964/293642527-d77a0bb0-68be-4104-8147-56efb3fc24da.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDI1MjctZDc3YTBiYjAtNjhiZS00MTA0LTgxNDctNTZlZmIzZmMyNGRhLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWY1Mjc5OTZjZTlhMWNjOTRmZWE1NWMzM2JmNmJjZWM1ZjVjMWIwMjM0MmI1YWNjNmMzMWQ4NzRjY2M4NzMxOWQmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.iAQSXRXX5rJV4R2Eeg7PR3RxETclEKgIeM5hwYACHo4)
![Screen Shot 2024-01-02 at 1 08 08 PM](https://private-user-images.githubusercontent.com/74584964/293642673-d2f81373-df97-4cc3-8b8a-65f48ee9d8cf.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDI2NzMtZDJmODEzNzMtZGY5Ny00Y2MzLThiOGEtNjVmNDhlZTlkOGNmLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTRmNGFmNjEzODEwZmJkNDczZjg1YmQ1ZDYxNDVmMjdhNmJlODI4MmIyYTVmY2E0MGVkZGZlOTk5OTJiNmE0ODEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.3sx6XnHxEHnpLAlNYdBotM9J0jJ7OOTR7cESqRbm938)
![Screen Shot 2024-01-02 at 1 10 11 PM](https://private-user-images.githubusercontent.com/74584964/293642719-2bababbe-58cf-4821-b673-841575dc3b1a.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDI3MTktMmJhYmFiYmUtNThjZi00ODIxLWI2NzMtODQxNTc1ZGMzYjFhLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTcyMTg2OTM2ODAwMjQyOWZkZmNjNTY0NGQ2NTZiYWZiODZkZTJjZjg5YTQ3MTIyODA2ZDQ3Mzc3MjhjY2Y3NGImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.r3HtVwCx7prvxDXuikKzSXd2jzGF2Q5ITxY451tMn9k)
Get the API endpoint " https://fwpwl5qova.execute-api.us-east-1.amazonaws.com/dev/project3_lambda1_kinesis_apigateway"
![Screen Shot 2024-01-02 at 1 23 24 PM](https://private-user-images.githubusercontent.com/74584964/293642862-cad76676-cc3d-44d0-acdf-7afa49c27464.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDI4NjItY2FkNzY2NzYtY2MzZC00NGQwLWFjZGYtN2FmYTQ5YzI3NDY0LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTIyMDI1OTlkZDJkZWU3ZWM2NDgzOThhODZkZDQ2Y2VmZGY4ZDkwNjhhNzg2OGE2ZTQzMTBhZGRkMGFhMDI2NDgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.YDSDd1iK3Ah-SjJJTxxZ1160DMbJFMARE55JtawrTvI)
![Screen Shot 2024-01-02 at 1 36 27 PM](https://private-user-images.githubusercontent.com/74584964/293643130-06f00a74-b04e-47be-b75b-1f19db6889e2.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDMxMzAtMDZmMDBhNzQtYjA0ZS00N2JlLWI3NWItMWYxOWRiNjg4OWUyLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWY0MjdjMTdkNGNhODIyMzU2NDRmNjNjNTY5MDk2NzYwYjhkZTcxMjhkYjIwMTFlZDI1OWU4YzAyYzk2ODRlNjUmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.TfMAus8Akh1WV3CtcAqZ0PbjQ6sPM46AMIiCtpy1nek)
This second lambda function will transform the data( decode the data to put a delimiter in between each record) to make it easy to put on Snowflake. We will convert the binary data into string data. Then, add a \n to make a new line.
import json
import boto3
import base64
output = []
def lambda_handler(event, context):
print(event)
for record in event['records']:
payload = base64.b64decode(record['data']).decode('utf-8')
print('payload:', payload)
row_w_newline = payload + "\n"
print('row_w_newline type:', type(row_w_newline))
row_w_newline = base64.b64encode(row_w_newline.encode('utf-8'))
output_record = {
'recordId': record['recordId'],
'result': 'Ok',
'data': row_w_newline
}
output.append(output_record)
print('Processed {} records.'.format(len(event['records'])))
return {'records': output}
- Create kinesis firehouse
![Screen Shot 2024-01-02 at 1 40 45 PM](https://private-user-images.githubusercontent.com/74584964/293643417-66974308-33e6-489d-b09d-61db456a8173.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM0MTctNjY5NzQzMDgtMzNlNi00ODlkLWIwOWQtNjFkYjQ1NmE4MTczLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWE4MTBjODMwNjJmNGMyYTJkZGRmMjg5YWEwNjk2MGI1YmUxYTlkMDlmN2MyMjk3YWUwODIwMTkzMTE4MzAzMjImWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.91p_gakiwM1WrN8AxVQDKn6EL-1YQNBcmwuItemWpSU)
![Screen Shot 2024-01-02 at 1 41 48 PM](https://private-user-images.githubusercontent.com/74584964/293643461-09c44659-85b0-44f2-9f3a-a6fe35cb232c.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM0NjEtMDljNDQ2NTktODViMC00NGYyLTlmM2EtYTZmZTM1Y2IyMzJjLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWNhODMwNzFhZTliNGEzZTMzNzE1YTZkYWQ1ZmFlZjVlZDUwZjdjYzliMGQzY2I2ZWZjM2Y1MWMyMTcwZTdmNGYmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.W_p7Gbq15JG1_oVKszOPkTmhvNF660ezyq8XNYJuGGg)
- Activate Transform source records with AWS Lambda.
![Screen Shot 2024-01-02 at 1 43 21 PM](https://private-user-images.githubusercontent.com/74584964/293643487-7a9ef4c0-4557-4fcd-bbff-f55e2776ec82.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM0ODctN2E5ZWY0YzAtNDU1Ny00ZmNkLWJiZmYtZjU1ZTI3NzZlYzgyLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWMwMDVjN2FkYmIyY2QzZTBlYmQ4MDJiOGI3MDNhMTZkMzg1ZWJjMDU4YzU5MDhiZjVhNGUxMGRkYjFmMDQwYjgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.7aRkGPfdRwqwfqCEZA8vPQhUFbr_iMky26PEJPZjEF8)
![Screen Shot 2024-01-02 at 2 02 22 PM](https://private-user-images.githubusercontent.com/74584964/293643626-8ffa3bb8-b66e-4e64-b34a-d9a66f501de5.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM2MjYtOGZmYTNiYjgtYjY2ZS00ZTY0LWIzNGEtZDlhNjZmNTAxZGU1LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWJkMmVhNDk1ODM2YTU3OGQ5Y2E1ZThkODI1MDQ5OGMyYzdlMzE4NjI0M2JmNTFhNDY3YWY1ZGQ5MzA1YTI0YzgmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.9sukdvVaOF-kMB16ORCVBI0uoo-ctIBqKZylxPJpfCQ)
Storage Integration Creation: Copy the snowflake role arm into the snowflake console and copy the s3 bucket arm role.
create warehouse s3_to_snowflake_wh;
use s3_to_snowflake_wh;
--Specify the role
use role ACCOUNTADMIN;
drop database if exists s3_to_snowflake;
--Database Creation
create database if not exists s3_to_snowflake;
--Specify the active/current database for the session.
use s3_to_snowflake;
--Storage Integration Creation
create or replace storage integration s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::200105849428:role/project3_snwoflake_role'
STORAGE_ALLOWED_LOCATIONS = ('s3://gakas-project3-kinesis-apigateway')
COMMENT = 'Testing Snowflake getting refresh or not';
--Describe the Integration Object
DESC INTEGRATION s3_int;
--External Stage Creation
create stage mystage
url = 's3://gakas-project3-kinesis-apigateway'
storage_integration = s3_int;
list @mystage;
--File Format Creation
create or replace file format my_json_format
type = json;
--Table Creation
create or replace external table s3_to_snowflake.PUBLIC.Person with location = @mystage file_format ='my_json_format';
show external tables;
select * from s3_to_snowflake.public.person;
--Query the table
select parse_json(VALUE):Age as Age , trim(parse_json(VALUE):Name,'"') as Name from s3_to_snowflake.PUBLIC.Person;
Copy the 'STORAGE_AWS_IAM_USER_ARN' ARN and 'STORAGE_AWS_EXTERNAL_ID' from Snowflake and update the Trust Policy in the Snowflake role in IAM.
![Screen Shot 2024-01-02 at 2 19 25 PM](https://private-user-images.githubusercontent.com/74584964/293643688-52f82acd-e723-4280-918a-963af3ffb56f.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM2ODgtNTJmODJhY2QtZTcyMy00MjgwLTkxOGEtOTYzYWYzZmZiNTZmLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWZiNjZiNjUxZWYwMTM5Mjg0YWUyOWZlNmRlYmFlNDkxMmYwNjM0YTgwYjQyNmZiYjk5ZTU2ZGEwOGM2ZTY4NWUmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.j-yx2R6bVBY_U5lNYZcdLVVi1-oeRT2RODhWW1-5VZI)
Create an event notification for the s3 bucket.
![Screen Shot 2024-01-02 at 2 26 11 PM](https://private-user-images.githubusercontent.com/74584964/293643812-4d487de5-ef90-477c-ad28-64ed8f62b31e.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM4MTItNGQ0ODdkZTUtZWY5MC00NzdjLWFkMjgtNjRlZDhmNjJiMzFlLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWJjZjg4MTY4MWJmNjM5OWU4NTU3MzQ0MzU2ZmU3MTY5ZmRkOWRiNTVlNGFkMmFkOTlmMDRkMjdiODFiY2RkYzUmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.sOz5I4WrgkqtE1yPKZhi1RpRmz5sRzjxxRI2a3obvNQ)
![Screen Shot 2024-01-02 at 2 26 17 PM](https://private-user-images.githubusercontent.com/74584964/293643832-aa4106d0-68b9-4cea-a7b9-2dfa9b8e432b.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM4MzItYWE0MTA2ZDAtNjhiOS00Y2VhLWE3YjktMmRmYTliOGU0MzJiLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTY3NGZmYzAwYzk2OTBiMTE3Yzk2OGM2NjBkMWI3Nzk0ZWMxNjU2OTUxZmFlN2U2NzY4Y2NhYjNjMDQxZjc2ZTEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.4bHHEYUfv5zZxSqIjiqaGTygjcZvL-H1VoOz3ZWiWfw)
![Screen Shot 2024-01-02 at 2 26 26 PM](https://private-user-images.githubusercontent.com/74584964/293643850-4b502a0f-8d27-4596-b991-84a4f6cdcc07.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDM4NTAtNGI1MDJhMGYtOGQyNy00NTk2LWI5OTEtODRhNGY2Y2RjYzA3LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWU1YzlmMzA2ZDU0OTRmNTEwOTVmYjAyNTM0ODA3OWU3ZDk4Yzc0Y2JkYmJkYzJjODZiMTk4NmY1NmZkM2RjMDEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.qOZZyOlSYo-dDVl1nWfe2HirO2spUFmSxVAIwlpZ6j4)
-
Open Postman and pass a series of records {"Name": "wang", "Age":4} {"Name": "ali", "Age":32} {"Name": "li", "Age":54} {"Name": "Moctar", "Age":44} {"Name": "she", "Age":86} {"Name": "Abdoul", "Age":22} {"Name": "lie", "Age":34} {"Name": "Cheng", "Age":55} {"Name": "Karim", "Age":23} {"Name": "ram", "Age":34} {"Name": "li", "Age":23} {"Name": "she", "Age":36}
![Screen Shot 2024-01-02 at 3 27 12 PM](https://private-user-images.githubusercontent.com/74584964/293644413-88b99ad9-d590-42b9-9954-aa146f68300c.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDQ0MTMtODhiOTlhZDktZDU5MC00MmI5LTk5NTQtYWExNDZmNjgzMDBjLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWVhZDc5Y2Q3ZjUyNDlhNzI1NzllZjkxM2QyZGU1ZjA1ZjMyMmIxOGE5OGEzZjJjMjE5ZDdmNzYxYTdlOWU5ZjQmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.rybB6QVDwUqBqJlzVCDqYiRK_A9o6e4TbrSq-qSam1M)
- Check S3
![Screen Shot 2024-01-02 at 3 27 30 PM](https://private-user-images.githubusercontent.com/74584964/293644499-f7e6ac3e-8dcc-4884-b504-6b6e08b680d6.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDQ0OTktZjdlNmFjM2UtOGRjYy00ODg0LWI1MDQtNmI2ZTA4YjY4MGQ2LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTZjNTQ3YTVjNWNlM2QwZWFjYzMwYTAwOTgxY2YxZGVjNmNjMWNiNWVlZTk1YzFjNDIxYjM5YzkzYjg5OGRkODMmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.kp_BylhqvkdO5-1dUQ5IqQ86M1r5eZMralsbLwZOzrA)
- Check the data into snowflake
![Screen Shot 2024-01-02 at 3 07 30 PM](https://private-user-images.githubusercontent.com/74584964/293644072-52ab3533-281c-4dda-a626-b86333d488b6.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MTUyODY4NjgsIm5iZiI6MTcxNTI4NjU2OCwicGF0aCI6Ii83NDU4NDk2NC8yOTM2NDQwNzItNTJhYjM1MzMtMjgxYy00ZGRhLWE2MjYtYjg2MzMzZDQ4OGI2LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA1MDklMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNTA5VDIwMjkyOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWNjNjJjN2I5MzJkNmZlNWFlZmQ4MjJkMTZkNDM1YjJmYjc3MmY2YWVkYTZmYzBiZDY0MDE4MTQ2MjhjYjQ2YjYmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.07h4naEIOwxSOydSaUgx6ZQDvyWXGFUYGKjMR4pT3CQ)