Table of Contents

How-To: Importing Data (flat file or JSON) Using Vena API

Looking to automate data upload using web tools? Vena API allows you to easily import your data.

Why use this feature?

Use Vena API to automate your data upload process. Vena API allows you to easily achieve web-based automation and upload a variety of data formats, including JSON, CSV, PSV and TDF.

Before you begin

To follow the instructions in this article, you will need at least Modeler access.

You will also need an application token from Vena. You can get your application token by following these instructions.

Every API endpoint requires you to know your region ID. Your region is the three characters in the URL that appear before .vena.io. In the example below, the region is us2.

Region ID

How to

Import data (flat file) using Vena API

These instruction are suitable for importing data in CSV, PSV and TDF format.

  1. You will use the Start with File endpoint. _Start with File_allows you to create a job, upload a CSV file and start the job in one combined step.
  2. The request URI for Start with File is available below. Replace the URI parameters {region} and {templateID} with the appropriate values. 
POST https://{region}.vena.io/api/public/v1/etl/templates/{templateId}/startWithFile
  1. Include your API key at the end of the request. Instructions for finding your API key are available here.
  2. Create the request body. The request body is composed of the file and metadata.
    • The file is the file path. The value must be the same as that of the partName in the metadata.
    • The metadata contains the partName, fileFormat, fileEncoding and fileName in JSON format.
Name Type Description
partName String Field name for storing file in request body.
fileFormat CSV, PSV, TDF Type of file to be uploaded.
fileEncoding UTF-8, UTF-16, Windows Latin-1 The encoding of file to be uploaded.
fileName String File name

A sample payload is available below

Sample file (CSV):

A1,B1,C1
A2,B2,C2
A3,B3,C3

Sample metadata:

{
  "input": {
    "partName": "file", 
    "fileFormat": "CSV", 
    "fileEncoding": "UTF-8", 
    "fileName": "data.csv"
  }
}
  1. Combine the request URI, headers and request body to complete your API request. A sample request is included below:
curl --location --request POST 'https://us2.vena.io/api/public/v1/etl/templates/1050582343200276480/startWithFile' \
-u 498265219441623040.498264904105459712:db30a250930e4b1e975c7a3f139bb7bf \
--form 'file=@"data.csv"' \
--form 'metadata="{\"input\":{\"partName\":\"file\",\"fileFormat\":\"CSV\",\"fileEncoding\":\"UTF-8\",\"fileName\":\"data.csv\"}}"'
  1. After submitting your API request, a 200 status code indicates that an ETL job is in progress. A sample response is included below:
{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "RUNNING",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}
  1. If you receive an error response code when calling this endpoint, you can use this resource for troubleshooting. To monitor the job status, follow these steps. To cancel a job, follow these steps.

Import data (JSON) using Vena API

These instruction are suitable for importing data in JSON format.

  1. You will use the Start with Data endpoint. Start with Data allows you to create a job, upload JSON data and start the job in one combined step.
  2. The request URI for Start with Data is available below. Replace the URI parameters {region} and {templateID} with the appropriate values. 
POST https://{region}.vena.io/api/public/v1/etl/templates/{templateId}/startWithData
  1. Include your API key at the end of the request. Instructions for finding your API key are available here.
  2. Create the request body. The request body is composed of the JSON data. The current limitation is 25MB records. A sample payload is available below:
{
  "input": {
    "data": [
      ["1", "2", "3"],
      ["4", "5", "6"],
      ["7", "8", "9"]
    ]
  }
}

5.Combine the request URI, headers and request body to complete your API request. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/templates/1050582343200276480/startWithData -X POST -H "Content-Type: application/json" -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391 -d '{"input": {"data": [[1, 2, 3], [4, 5, 6]]}}'
  1. After submitting your API request, a 200 status code indicates that an ETL job is in progress. A sample response is included below:
{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "RUNNING",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}
  1. If you receive an error response code when calling this endpoint, you can use this resource for troubleshooting. To monitor the job status, follow these steps. To cancel a job, follow these steps.

Other useful endpoints

List Templates

You can use List Template to get a list of all the ETL templates that you have permission to access.

  1. Replace the region parameter with your region ID in the request URI available below.

Request URI

GET https://{region}.vena.io/api/public/v1/etl/templates

Include your API key at the end of the request. Instructions for finding your API key are available here. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/templates -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391

Get Template
You can use Get Template to get information about a specific ETL template. Get Template returns information such as the template name, associated model ID and model name.

  1. The request URI is available below, you will need to replace the {region} parameter with your region ID and the {templateID} parameter with the template you want to retrieve the information of.
    • Instructions for finding a list of templateIDs that you have access to is available here.

Request URI

GET https://{region}.vena.io/api/public/v1/etl/templates/{templateId}

Include your API key at the end of the request. Instructions for finding your API key are available here. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/templates/1193658473950284544 -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391

Get Job
You can use Get Job to get information about an ETL job including but not limited to the associate model ID and model name, the creation date and the job status.

  1. The request URI is available below, you will need to replace the {region} parameter with your region ID and the {jobId} parameter with the job you want to retrieve the information of.

Request URI

GET https://{region}.vena.io/api/public/v1/etl/jobs/{jobId}
  1. Include your API key at the end of the request. Instructions for finding your API key are available here. A sample request is included below:
curl https://us2.vena.io/api/public/v1/etl/jobs/1193658473950284544 -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391
  1. The response will include the ETL job status. The ETL job status key is available here.

Cancel Job

You can use Cancel Job to cancel an ETL job.

  1. The request URI is available below, you will need to replace the {region} parameter with your region ID and the {jobId} parameter with the job you want to retrieve the information of.

Request URI

GET https://{region}.vena.io/api/public/v1/etl/jobs/{jobId}
  1. Include the headers and your API key at the end of the request. Instructions for finding your API key are available here. A sample request is included below:
curl https://us2.vena.io/api/public/v1/etl/jobs/1193658473950284544/cancel -X POST -H "Content-Type: application/json" 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391
  1. If successful, the job status will be CANCELLED in the response.