How-To: Importing Multiple Data Files 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.

How to

Importing multiple data files using Vena API

These instruction are suitable for importing multiple data files using API. This process is composed of three steps, creating the job, uploading the files and starting the job.

Step 1: Creating the Job

1. You will use the Create Job endpoint. Create Job allows you to create a job and set it to the EDITING stage.
2. The request URI for Create Job is available below. Replace the URI parameters {region} and {templateID} with the appropriate values.
   • Instructions for finding your region ID are available here.
   • Instructions for finding the templateID is available here.

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

3. Include your API key at the end of the request. Instructions for finding your API key are available here.

4. A sample request is included below:

curl https://us2.vena.io/api/public/v1/etl/templates/1099180598582312960/jobs
-X POST -H "Content-Type: application/json" -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391

5. After submitting your API request, a 200 status code indicates that an ETL Job was created and is in the EDITING stage. A sample response is included below.

{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "EDITING",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}

6. If you have 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. To continue the process for importing multiple data files, continue to the next section.

Step 2: Loading Data to the ETL Step

Use the Get Template API to see all the steps in the job. Steps that require data input will have an inputId field.

This step can be done as many times as needed.

A. Import Data (JSON) to ETL Step
These instruction are suitable for importing data in JSON format.

1. You will use the Load JSON to Specific Input endpoint. Load JSON to Specific Input allows you to upload JSON data to an existing job without starting the job.
2. The request URI for Load JSON to Specific Input is available below. Replace the URI parameters {region}, {jobId} and {inputId} with the appropriate values.
   • Instructions for finding your region ID are available here.
   • Instructions for finding the jobId are available here.
   • The inputId can be found in the response of your Create Job call.

PUT https://{region}.vena.io/api/public/v1/etl/jobs/{jobId}/inputs/{inputId}

3. Include your API key at the end of the request. Instructions for finding your API key are available here.
4. 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:

{
  "data": [
    ["1", "2", "3"],
    ["4", "5", "6"],
    ["7", "8", "9"]
  ]
}

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

curl https://us2.vena.io/api/public/v1/etl/jobs/1113346477561741312/inputs/1111438645660745728
-X PUT -H "Content-Type:application/json" -u
1112140682300620800.1:51fd255f934040649f98d2a74d2a74d1c4391 -d'{"data": [[1, 2, 3], [4, 5, 6]]}'

6. After submitting your API request, a 204 status code indicates that the data was loaded to the job.
7. If you have 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.

B. Import Data (Flat File) to ETL Step
These instruction are suitable for importing data in CSV, PSV and TDF format.

You will use the Load File to Specific Input endpoint. Load File to Specific Input allows you to upload data in CSV, PSV and TDF format to an existing job, without starting the job.

1. The request URI for Load File to Specific Input is available below. Replace the URI parameters {region}, {jobId} and {inputId} with the appropriate values.
   • Instructions for finding your region ID are available here.
   • Instructions for finding the jobId are available here.
   • The inputId can be found in the response of your Create Job call.

PUT https://{region}.vena.io/api/public/v1/etl/jobs/{jobId}/inputs/{inputID}/file

2. Include your API key at the end of the request. Instructions for finding your API key are available here.
3. 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.

A sample payload file and metadata is included below:

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

{"input": {"partName": "file", "fileFormat": "CSV", "fileEncoding": "UTF-8", "fileName": "data.csv"}}

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

curl --location --request PUT 'https://us2.vena.io/api/public/v1/etl/jobs/1113346477561741312/inputs/1111438645660745728/file' \
-u 498265219441623040.498264904105459712:db30a250930e4b1e975c7a3f139bb7bf \
--form 'file=@"data.csv"' \
--form 'metadata="{\"input\":{\"partName\":\"file\",\"fileFormat\":\"CSV\",\"fileEncoding\":\"UTF-8\",\"fileName\":\"data.csv\"}}"'

5. After submitting your API request, a 204 status code indicates that the data was loaded to the job.

6. If you have 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.

Step 3: Submit Job

1. You will use the Submit Job endpoint. Submit Job allows you to submit an ETL job.
   The request URI for Submit Job is available below. 2. 2. Replace the URI parameters {region} and {jobId} with the appropriate values.
   • Instructions for finding your region ID are available here.
   • Instructions for finding the jobId is available here.

POST https://{region}.vena.io/api/public/v1/etl/jobs/{jobId}/submit

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

curl https://us2.vena.io/api/public/v1/etl/jobs/1099180598582312960/submit -X POST -H "Content-Type: application/json" -u 1112140682300620800.1:51fd255f934040649f98d2a74d1c4391

5. After submitting your API request, a 200 status code indicates that the job was submitted. A sample response is included below:

{
  "id": "1099180598582312960",
  "name": "LoadGL",
  "status": "SUBMITTED",
  "modelId": "988930839071293441",
  "modelName": "Financial Model",
  "createdDate": 1623338765403,
  "updatedDate": 1623338873829,
  "userName": "Admin User"
}

6. If you have 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 the 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

2. 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 modelID 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}

2. 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:

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}

2. 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

3. 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}

2. 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

3. If successful, the job status will be CANCELLED in the response.