Unlocking the Power of Fivetran: Effortlessly Transform API Data into Table Gold!

Think about how much time and effort you can save by loading data from an API into a table with just a few steps; a logical code with Fivetran can do that for you!

In this blog, you will learn how to use Fivetran to easily and quickly load data from an API into Redshift. This blog will give you a detailed guide on how to extract data from an API and load it into Redshift with Fivetran. You will also learn some of the best practises for using Fivetran to achieve efficient and reliable data integration between the API and Redshift.

Fivetran does not have a specific connector. So, it can easily load data from an API into the target destination.

To load data from the API into the data pipeline, you can use the AWS Lambda connector in Fivetran, which helps you to get data from the API and integrate it into the pipeline. To implement that, follow the steps below.

1.In Fivetran, click on

Destination → Add destination, Select the destination where we want to load the data, and then configure the selected destination.

2. Next, click on

Connectors → Add connectors → Select the destination → AWS Lambda

3. For setting up an AWS Lambda function we need to follow the setup guide.

4. When working with the Lambda function, you have the flexibility to choose between two distinct options. The first option involves syncing the data directly to our designated destination, while the second option entails loading the data as a file into an S3 Bucket and subsequently syncing it to our desired destination.

5. We will use secrets to pass the API URL, S3 bucket name and file path and make them dynamic.

6. Before clicking save and test, we need to define our AWS Lambda function.

7. Use the code underneath to obtain the data and convert it into a format that Fivetran recognizes.

import json
import boto3
import requests

def lambda_handler(request, context):
    
    url = request['secrets']['url']
    data = requests.get(url).json()
    sample=[data]
    insert={}
    insert['dummy'] = sample
    # dummy is the table name
    state={}
    # state['transactionsCursor'] = '2028-01-01T00:00:00Z'
    
    response = {}
    # Add updated state to response
    response['state'] =  state
    # Add all the records to be inserted in response
    response['insert'] = insert
    # Add hasMore flag
    response['hasMore'] = False
    response_s3={}
    # Add all the records to be inserted in response_s3
    response_s3['insert'] = insert
    json_str = bytes(json.dumps(response_s3).encode("utf-8"))
    client = boto3.client('s3')
    # To put the file in S3
    client.put_object(Body=json_str, Bucket=request["bucket"], Key=request["file"])
    return response

Create a virtual environment for installing the requests package, and lambda_function.py should be in the site-packages folder. After organizing the contents within the site-packages folder, compress them into a single zip file. And in lambda, use the upload from option to upload the zip file.

8. Below is a sample response that Fivetran accepts.

{
     'state': {'transactionsCursor': '2018-01-01T00:00:00Z'}, 
     'insert': {'transactions': 
          [{'date': '2017-12-31T05:12:05Z', 'order_id': 1001, 'amount': '$1200', 'discount': '$12'}, 
           {'date': '2017-12-31T06:12:04Z', 'order_id': 1001, 'amount': '$1200', 'discount': '$12'}]}, 
           
     'delete': {'transactions': 
          [{'date': '2017-12-31T05:12:05Z', 'order_id': 1000, 'amount': '$1200', 'discount': '$12'}, 
           {'date': '2017-12-31T06:12:04Z', 'order_id': 1000, 'amount': '$1200', 'discount': '$12'}]},
           
     'schema': {'transactions': 
          {'primary_key': ['order_id', 'date']}}, 'hasMore': False
}

The ‘state’ field is a mandatory requirement in this context, and although it can be left empty, it must be present. If necessary, you have the option to obtain the ‘state’ value from Fivetran’s request and utilize it accordingly.

The ‘transactions’ refer to the table name wherein records need to be either inserted or deleted. The ‘insert’ field serves the purpose of inserting records into the table, while the ‘delete’ field facilitates the removal of records from the specified table.

9. Click save and test in Fivetran to test the connection.

10. Within Fivetran, an AWS Lambda connector can be scheduled by utilizing the sync frequency option. The sync frequency allows you to configure the frequency of synchronization. The sync frequency offers a range of options, spanning from intervals as short as 5 minutes to as long as 24 hours.

CONCLUSION

Finally, by using Fivetran, while syncing the AWS Lambda connector, it calls the lambda function to get the data from the API and load the data to the destination.

With Fivetran, you can easily load data from any API to your preferred destination table in a few simple steps in a low code and without worrying about maintenance, or scalability issues.

About Us

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe, and we’re quickly building a brand that is well-known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies