Part 1: Creating databases with SQL

InterSystems IRIS® data platform provides a strong backbone for the IT architecture and initiatives for any company.

It provides multi-model (SQL and NoSQL) and multi-workload (transactions and real time analytics) DBMS capabilities, deep embedded integration and analytics capabilities to handle integration, transformation, API management, business logic, and a range of powerful analytics capabilities including BI, machine learning and natural language processing, all without moving data or requiring additional products.  And it supports all major programming environments including Python, Java, .NET, Node.js, and R, enabling application developers to be productive quickly with their existing skills, both for client- and server-side development.

In this tutorial, we’re going to create the basic information management infrastructure for a small manufacturing company. In this case, our company will be roasting, packaging and selling delicious, freshly roasted coffee beans. Along the way, you’ll learn how the InterSystems IRIS data platform can serve as the backbone of your IT architecture.

The tutorial is divided into three parts and introduces the processes that you, as a coffee manufacturer, follow to set up everything from inventorying raw coffee beans to selling them in an online portal.

• Part One: Use standard SQL to create tables and load data. You will also use Python to process order manifests arriving in JSON format. At the end of this section, we’ll be able to process new coffee bean deliveries into our company’s inventory!
• Part Two: Enable the different parts of the business — inventory, roasting, and sales — to communicate via RESTful services. The coffee roastery requests beans from the inventory, and after roasting and packaging, the roastery uses a REST service to put the final product into the catalog for sale online. All this will happen through the RESTful services we’ll build in just a few minutes.
• Part Three: Build an online storefront to sell your artisan coffee beans using the popular JavaScript framework, Vue.js.
  

  Setup

  To complete this tutorial, you’ll need the InterSystems Sandbox, which includes the InterSystems IRIS data platform and a development environment. Create that below, registering and/or logging in as needed. The Sandbox will take about a minute to create, and then you will see a list of sandbox settings and links which will be used later on in the tutorial.

  
  

  

  Need InterSystems IRIS?

  Get a free, online development sandbox here. Log in with your InterSystems login account, or register for one below.

  Login Register

You’ll also need the GitHub repo where all the code and sample files you’ll need are located. Open the IDE -- cannot display value - please provision a sandbox (or the Sandbox IDE link above) and clone the GitHub repo by typing this in the terminal:

git clone https://github.com/intersystems/quickstarts-full-stack.git

Now you’re ready to get started!

Note:

This IDE you’re using is Theia, which works a lot like Visual Studio Code. The file explorer is on the left. Your code editing panel is on the right. And your terminal window is below the editing panel.

Database creation

IRIS Coffee Company has three major divisions in the company:

• the warehouse stores the raw coffee bean inventory. Its data will be in the table ICO.inventory
• the roastery roasts the beans, and doesn’t need to store data
• the storefront, where the company sells the roasted coffee. Its data will be in the table ICO.catalog

Let’s use the SQL client built into the InterSystems IRIS Terminal to create those two tables using SQL CREATE statements:

1. Open the Sandbox IDE -- cannot display value - please provision a sandbox
2. From the InterSystems menu, select Web Terminal
3. Log in with username tech and password demo
4. You should see a USER > command line prompt.
5. Type /sql.
6. Paste the following SQL CREATE statement and hit return.

   CREATE TABLE ICO.inventory
   (
   vendor_id VARCHAR(128),
   vendor_product_code VARCHAR(128),
   quantity_kg DECIMAL(10,2),
   date_arrival DATE
   )
   

   If your query is successful, you will see Nothing to display, as CREATE statements don’t return anything.

   

7. Copy and paste the following SQL CREATE statement to create ICO.catalog table and hit return.

   CREATE TABLE ICO.catalog
   (
   catalog_id BIGINT IDENTITY,
   product_code VARCHAR(128),
   quantity INTEGER,
   price DECIMAL(10,2),
   time_roasted DATETIME,
   roasting_notes VARCHAR(2048),
   img VARCHAR(2048)
   )
   

8. You can test a bit more by running SELECT * FROM ICO.inventory and SELECT * FROM ICO.catalog and you should get back a table with no rows (yet).
9. Type /sql again to exit SQL mode in the InterSystems IRIS Terminal

Python data loading

Before exploring the details of the Python data loading script, it’s good to have a high-level view of what’s going on. We want to populate the database with raw coffee bean orders, simulating shipments of raw beans from vendors around the world. Assume that all the shipments are consolidated in a single order manifest in JSON format.

Our manifest can be found at quickstarts-full-stack/setup/order_manifest.json.

To load the data, we’ll use a Python program to parse the order manifest file and insert the deliveries into the database, also using SQL, but this time within Python.

After that, you’ll build the program that imports order manifests into the database. You will read JSON in from disk with standard Python. Then you’ll set up your ODBC database connection. And finally, you write standard SQL INSERT commands to write the JSON data to the database.

Tip

If you’re confident reading Python by yourself, just jump to running the script.

Import script walkthrough

The full script is in setup/manifest_importer.py. Follow along below or if you’re confident reading the file yourself, just jump to running the script.

Notice some of the key elements of this script. In the main function definition, we simply import the JSON order manifest file and validate it, checking for the structure needed for the inventory database.

def main():
    with open('./order_manifest.json') as f:
    data = json.load(f)
    data, status, exp = validate_manifest(data)

The next thing we do in main() is read database credentials from the file, connection.config.

connection_detail = get_connection_info("connection.config")
ip = connection_detail["ip"]
port = int(connection_detail["port"])
namespace = connection_detail["namespace"]
username = connection_detail["username"]
password = connection_detail["password"]
driver = "{InterSystems ODBC}"

Then we set up a connection to the database using an ODBC driver.

connection_string = 'DRIVER={};SERVER={};PORT={};DATABASE={};UID={};PWD={}' \
.format(driver, ip, port, namespace, username, password)
connection = pyodbc.connect(connection_string)

If all goes well, we call load_manifest with the JSON data and the database connection object.

msg = load_manifest(data, connection)

In load_manifest, we iterate through all the items in the JSON file, inserting each one into the ICO.inventory table we created previously using SQL INSERT statements. Here’s how that works: First, we define our base SQL INSERT statement:

fieldnamesql = "INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival)"

On the next two lines, we create a new date string based on the current date, for example, ‘2020-06-18’. This will be the date the product arrived in the warehouse.

today = date.today()
mydate = today.strftime("%Y-%m-%d")

Now, the code loops through each item object in the JSON file, and uses the data there to construct the VALUES part of our SQL INSERT statement. The result is a completed SQL INSERT statement that looks something like this:

INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES (ETRADER, ETHIOPIA32, 200, ‘2020-06-18’)

Now the code runs the completed SQL statement. We defined our database cursor on the first line of the function, so now we can run execute on the cursor with the SQL statement as its input.

cursor.execute(valsql)

Run the Python import

With the code explained, let’s run the program.

Set up the ODBC driver.

Open the IDE -- cannot display value - please provision a sandbox. Click in the terminal panel and type:

cd /home/project/quickstarts-full-stack/setup
sudo odbcinst -i -d -f pyodbc_wheel/linux/odbcinst.ini

Database connection settings for your sandbox

1. Open /home/project/quickstarts-full-stack/setup/connection.config in your IDE
2. Change ip from localhost to -- cannot display value - please provision a sandbox
3. Change port from 5173 to -- cannot display value - please provision a sandbox
4. Save the file.

Type this in your terminal window:

 
python manifest_importer.py

You should see the following output.

Connected to InterSystems IRIS
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('ETRADER', 'ETHIOPA32', 200, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('BRZ221', 'BRAZILPREM', 100, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('GMLPROD', 'GUATEMALAALT30', 100, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('DKE', 'SUMATRA2', 100, '[year-month-day]')
Inserting: INSERT INTO ICO.inventory (vendor_id, vendor_product_code, quantity_kg, date_arrival) VALUES ('DKE', 'SUMATRA3', 200, '[year-month-day]')

Tip

You can also use Java, C#/.NET, or Node.JS as well as Python with InterSystems IRIS. Language-specific QuickStarts are on getting started.

SQL database queries

Let’s make sure the data was inserted by going back into the Web Terminal and running a SQL SELECT query.

1. -- cannot display value - please provision a sandbox
2. From the InterSystems menu, select Web Terminal
3. Log in with username tech and password demo
4. Type /sql.

Run:

select * from ICO.inventory

You should see five rows of raw coffee beans in inventory.

Try a couple queries to play with our inventory in more detail. See all large deliveries — over 100 kilograms. You might want to start roasting these first.

SELECT * FROM ICO.inventory WHERE quantity_kg > 100

Or you may need to see all inventory from a particular vendor.

SELECT * FROM ICO.inventory WHERE vendor_id LIKE 'DKE'

Add your own inventory

Finally, add more inventory on your own. Create your own JSON manifest and make up your own values for vendor id, product code and quantity.

1. Copy order_manifest.json to order_manifest-original.json (don’t try to comment out lines in the original JSON file. JSON doesn’t allow commented lines!)
2. Edit order_manifest.json in the IDE.
3. Change the values as you like.
4. Run python manifest_importer.py again.

Summary

Now you know how to use InterSystems IRIS as a relational database, using SQL either from a SQL client, or from Python. Move onto Part 2 to code REST services to move data throughout the company and onto the web storefront.