IRIS Data Loading app and REST API

This application facilitates CSV file input into IRIS and IRIS for Health, providing a front-end client to the LOAD DATA SQL command. Takes as input a CSV file, infers data types, and allows the user to edit column names and data types.

The repository includes an Angular application and a data loading REST API that uses IRIS SQL Utilities for introspecting CSV files.

Warning
This only works for CSV files on the same file system as IRIS itself. Uploading the CSV file to a remote server is a desired future feature.

Application Installation

Edit src/environments/environments.ts to match your install of the data loading REST API
Execute ng build (if needed, set up Angular)
If installing in IRIS…
- look at index.html in the dist/proto folder and make sure base reads as follows:
```
<base href="https://github.com/isc-rsingh/iris-load-data-api/blob/main//csp/dataloadapp/">
```
- in an IRIS session, run zpm load <path to this directory>, or set up a CSP application in IRIS and copy all files in the dist/proto directory to that location.
If installing in an external web server…
- copy all files in the dist/proto directory to the web server

REST endpoints

POST /columns

Returns a list of column names with data types using https://github.com/bdeboe/isc-sql-utils to infer the schema.

Example body:

{
    "from": {
        "file": {
            "path": "/Users/rsingh/Downloads/iris.csv",
            "columnseparator": "\t"
        }
    },
    "verbose": 1,
    "readlines": 0
}

POST /createtable

Takes the fields object of a JSON configuration file into an InterSystems IRIS CREATE TABLE statement and executes it.

Example body:

{
  "tablename": "SQLUser.dataexport",
  "fields": [
    {
      "srcname": "care_site_id",
      "type": "BIGINT",
      "length": 0
    },
    {
      "srcname": "care_site_name",
      "type": "VARCHAR",
      "length": "1"
    },
    {
      "srcname": "place_of_service_concept_id",
      "type": "BIGINT",
      "length": 0,
      "destdatatype": "INT"
    },
    {
      "srcname": "location_id",
      "type": "VARCHAR",
      "length": "1"
    },
    {
      "srcname": "care_site_source_value",
      "type": "VARCHAR",
      "length": "11"
    },
    {
      "srcname": "place_of_service_source_value",
      "type": "VARCHAR",
      "length": "48"
    }
  ]
}

POST /load

Munges a JSON configuration file into an InterSystems IRIS
LOAD DATA statement and executes it.

Example body:

{
  "from": {
    "file": {
      "header": true,
      "skip": 0,
      "columnseparator": ","
    },
    "filepath": "/Users/rsingh/Downloads/dataexport.csv"
  },
  "tablename": "SQLUser.dataexport",
  "verbose": 0,
  "charset": "UTF-8",
  "fields": [
    {
      "srcname": "care_site_id",
      "type": "BIGINT",
      "length": 10,
      "destname": "caresiteid",
      "destdatatype": "INT"
    }
  ]
}

Docker

Prerequisites

Make sure you have git and Docker desktop installed.

Installation

Clone/git pull the repo into any local directory

$ git clone https://github.com/rcemper/DK_iris-load-data-api.git

To build and start the container run:

$ docker compose up -d --build && docker compose logs -f

To open IRIS Terminal do:

$ docker-compose exec iris iris session iris
USER>

or using WebTerminal

http://localhost:42773/terminal/

To access IRIS System Management Portal

http://localhost:42773/csp/sys/UtilHome.csp

IRIS Data Loading Client