Using Components: Rest API Destination

In this short tutorial, we'll show how to create contacts in Hubspot as an example of writing data to any REST API destination.

Writing data to REST APIs essentially works in the same manner as reading data from REST APIs - using simple HTTP requests. HTTP requests are comprised of 4 parts:

  • Method - the most common are GET (used to read data) and POST (to write data).
  • URL - specifies the resource to read/write
  • Headers - specify additional information to the server (e.g. authentication keys, request body format)
  • Body - usually contains the data to send to the server in JSON or query string format)

In this tutorial, we'll use the Curl function to make HTTP requests to create Contact in Hubspot.

According to Hubspot docs, for each Contact, we have to create an Authorization header containing your Hubspot token and a JSON string of the Contact properties in the following form to submit in the request body:

  "properties": {
      "property_name1": "property value",
      "property_name2": "property value",
"property_name3": "property value",
...     } }

In our package, we first read data from a Contacts table in a source database.

thumbnail image

In the second component, we create a field to hold the URL string, and then generate the headers and the properties JSON string.

thumbnail image

Use the CONCAT function to create the Authorization header (see here for more information on CONCAT.) The Authorization header contains the literal string '"Authorization": "Bearer ' concatenated with the Hubspot token (which we store in a package variable - $HUBSPOT_TOKEN).

CONCAT('{"Authorization": "Bearer ','$HUBSPOT_TOKEN','","Content-type": "application/json"}')

The properties JSON string is generated using ETL's functions to build nested objects TOMAP and ToJson that returns a JSON string. 

ToJson(TOMAP('properties', TOMAP('first_name',first_name,'last_name',last_name,'email',email,'company',company,'phone',phone,'address',address,'city',city,'state',state,'zip',zip)))

In the third component, the request is made, per record coming in from the source table, and a response is returned.

thumbnail image

Using the Curl function (for more information about Curl see this doc), we pass in the url, headers, and properties fields and define the method as "POST". 

Curl(url, 'POST', headers, properties)

In the fourth component we parse the API response. The response contains a status code, headers and a body. The status code determines whether the request was successful or not (200 is returned on success. Read more here). The body contains the Contact data that was written if the status was successful or an error message if the status wasn't successful.

thumbnail image



Finally, the Contact's first name, last name, company, and email along with the response code and body field are stored into a database log table. This helps us monitor the process.

In the same manner, you can write data to any REST API endpoint - simply go through the API docs, build the URL and request body using the source data and check the response to make sure the returned response indicates success.