A Better data import experience for LOAD DATA

In recent versions of IRIS, a powerful new data loading command has been introduced to SQL: LOAD DATA. This feature has been highly optimized to import data into IRIS extremely fast, allowing hundreds of gigabytes of data to be inserted in seconds instead of hours or days. 

This is a very exciting improvement. However, a big problem in the data loading experience still exists. Namely, the time and hassle it takes to:

1. Define the schema for the table in which you want to load data.
2. Figure out the syntax for the LOAD DATA command.

I've developed a user interface that invisibly handles the CREATE TABLE step and then generates the syntax for LOAD DATA, all in a handy wizard!

At least in my case -- although I've been in the database business for decades -- I only import data a few times a year. Therefore, my CREATE TABLE skills get rusty, and it's really nice to have a tool take care of that for me. And this tool doesn't just handle syntax. It also inspects the input CSV file using a utility from the SQL Utilities library from @benjamin.DeBoe to figure out the data types and their length. Then it asks a few more questions to define the syntax of the required LOAD DATA command. The app can run it, or you can just copy the syntax and tweak it yourself. 

Here's a walkthrough.

Step 1: Install the app and review the CSV file

After following the instructions to install the solution, you will have an Angular app published as a CSP application and a backend ObjectScript application that serves as the API to interface with the database.

Take a look at my sample data set (using the Rainbow CSV extension in VS Code). It has a mix of numeric, text and empty columns.

Step 2: Go to the app

You will probably find the app at http://localhost:52773/csp/dataloadapp/index.html if you use the default IRIS port and web application name.

Step 3: Specify the CSV file location

Step 4: Specify the CSV file's format

LOAD DATA needs to know some things like the column delimiter character and where to start in the file. 

Step 5: Define a destination table name, with the schema name as well

Step 6: Fine tune the field names and data types

Most of this will be filled in for you, and should be pretty accurate, but you will probably want to adjust some names or field lengths.

And that's it! Press "Load CSV" and the client-side app will make a call to the server to run a CREATE TABLE SQL command, then run LOAD DATA with the syntax shown in the black box on the right. Going into the Management Portal (or any other SQL client), you can see I now have the CSV file loaded into IRIS.

 

I must apologize in advance that there isn't much error checking yet, but this is open source so if you find this tool useful, join me in improving it on GitHub.