Tutorial: Real-Time Streaming into AMI DB

Today we are going to look at the AMI real-time database and how to stream data in and build real-time visualizations. We will show how to do this with 3 different environments, Python, PowerShell, and Bash but any tcp network connection from your favorite programming language or system would work, as long as you can establish a network connection and send some strings. The steps will roughly be to first stream in some data with the scripts, then just build a real-time table inside AMI. A prerequisite to this will be to have a copy of AMI installed, either locally or on a dedicated server. I am just running this locally, so the scripts will be sending the messages to localhost but you might need to alter the network addresses and ports if you’re running this on a different setup.

A quick bit of housekeeping before we start is to locate two ports from your config files that we will need for this exercise (if you are just running this on a local machine install of AMI and have not changed anything in your config, you can skip this step and go to the next step). The config options we are looking for are called ami.db.console.port and ami.port and can be found in one of two files in your config folder, either:

i) default.properties

or, if you have created a local configuration file with customized configuration:

ii) local.properties

The config folder location depends on your operating system but is for example by default on Windows machines C:\Program Files\ami\amione\config\ and ~/ami/amione/config on Linux.

I have not created a local.properties file in my config folder, so I check the default.properties file, and note down the ports corresponding to the properties ami.db.consiole.port and ami.port from the following lines:

ami.db.console.port=3290

and

ami.port=3289

How to stream data into the realtime database

To push data into the realtime database, we need to establish a raw network connection to the database. We will use the ami.port that we have extracted in the previous step. To demonstrate how this works, we have supplied scripts to do this in 3 different ways, using a python script, a powershell script, and a bash script. You only need to use one of the scripts and they work roughly the same way, and are just meant to elucidate the process on different platforms.

No matter which option of the script you choose to use, it does roughly the following:

  1. Establishes a connection to the AMI DB
  2. Logs in to the DB using the user demo
  3. Makes a list of common stock names
  4. Runs a for-loop of 1000 iterations, where for each loop:
  5. Creates a timestamp
  6. Randomly selects a stock from the list of common stock names
  7. Makes up a random price for the stock
  8. Concatenates the results from 5–7 into a string, or a row to send to the db
  9. Finally sends this row into the db

It uses a function called send_to_AMI which takes one argument, message which is the message it will send into the db, in this case simply a row.

Option 1: Python script

You can download the script here to avoid formatting errors from copy-pasting below.

The python script uses a couple of very generic standard packages (socket, random, and time) for creating a network connection, creating random numbers, and keeping time. It generates some random data, and injects this into the realtime db.

Note: is that in the example below, we are using the default “demo” user, if you are using a live environment, you might have to edit the username by editing the variable username on line 17 in the script.

import socket
import random
import time

#Initialize network connection and set timeout
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.connect(('127.0.0.1',3289))
s.settimeout(2)

# Define function that sends our string in the right encoding
# and a newline at the end.
def send_to_AMI(message):
    s.sendall((message+'\n').encode('utf-8'))
    msg=s.recv(4096).rstrip().decode('utf-8')
    return msg

# Login to AMIDB, this might look different on your system
# if you have created users with usernames and passwords.
username='demo'
login_message='L|I="'+username+'"'
login_msg=send_to_AMI(login_message)

# Make up some random stock ticker names to insert into
# our table.
stocks=['AAPL', 'TSLA', 'IBM', 'INTC', 'GOOG', 'AMZN']

# Inject the number of rows in the variable number_of_rows
# into the db. Each row injected is timestamped, random
# stock from list 'stocks' is chosen and a random price
# is attached to each row.
number_of_rows=10**3 

for i in range(0,number_of_rows):
    # Create timestamps, pick a stock, create random price
    timestamp=time.time()     
    random_stock=random.choice(stocks)     
    random_price=100*random.random()
    # Concatenate this into a string that we can send to 
    # the database. See documentation for syntax accepted here:   #https://3forge.com/documentation/v5/3Forge_Backend_API_(2.13)_files/Outbound_Instruction_Type_Object.htm
    row=(
        'O|T="streaming"|'+
        'timestamp='+str(timestamp)+'|'
        'name="'+random_stock+'"|'+
        'number='+str(random_price)
        )
    msg=send_to_AMI(row) 
    time.sleep(0.001) 
    print(msg)

Now, running this script on a python console of your choice, should yield a streaming output similar to:

M@1600247347817|Q=466|S=0|M="OK, object accepted"
M@1600247347828|Q=467|S=0|M="OK, object accepted"
M@1600247347839|Q=468|S=0|M="OK, object accepted"
M@1600247347850|Q=469|S=0|M="OK, object accepted"

This message indicates that we are successfully injecting data into the database, huzzah!

Option 2: Powershell script

You can download the script here to avoid formatting errors from copy-pasting below.

The powershell script uses windows built in classes for tcp connections and IO. It generates some random data, and injects this into the realtime db.

Note: is that in the example below, we are using the default “demo” user, if you are using a live environment, you might have to edit the username by editing the variable username on line 33 in the script.

# Powershell script to send data into the realtime AMI database,
# using simple low level networking 

# Define some constants and variables.
param(
         [string] $remoteHost = "localhost",
         [int] $port = 3289,
         [string] $inputObject
      )[string] $output = ""
  
# Initialize the network connection.
$socket = new-object System.Net.Sockets.TcpClient($remoteHost, $port)
$stream = $socket.GetStream()
$writer = new-object System.IO.StreamWriter $stream
$buffer = new-object System.Byte[] 1024
$encoding = new-object System.Text.UTF8Encoding
$outputBuffer = ""

# Define function that sends our string in the right encoding.
function send_to_ami
{
param( $message )
$writer.WriteLine($message)
$writer.Flush()
$read = $stream.Read($buffer, 0, 1024)
$outputBuffer += ($encoding.GetString($buffer, 0, $read))
$outputBuffer
}

# Login to AMIDB, this might look different on your system 
# if you have created users with usernames and passwords.
$username='demo'
$login_message='L|I="'+$username+'"'
send_to_ami $login_message

# Make up some random stock ticker names to insert into 
# our table.
$stocks='AAPL', 'TSLA', 'IBM', 'INTC', 'GOOG', 'AMZN'

# Inject the number of rows in the variable number_of_rows
# into the db. Each row injected is timestamped, random 
# stock from list 'stocks' is chosen and a random price 
# is attached to each row.

$number_of_rows=[Math]::Pow(10,3)
for($i=1 ;$i -le $number_of_rows;$i++)
{
    $timestamp=(New-TimeSpan -Start (Get-Date "01/01/1970") -End (Get-Date)).TotalSeconds
    $random_stock = Get-Random -InputObject $stocks
    $random_price = Get-Random -Minimum 0 -Maximum 100.00
 # Concatenate this into a string that we can send to 
 # the database. See documentation for syntax accepted here:
 #https://3forge.com/documentation/v5/3Forge_Backend_API_(2.13)_files/Outbound_Instruction_Type_Object.htm
    $row=('O|T="streaming"|'+
            'timestamp='+$timestamp +'|'+
            'name="+$random_stock+"|'+
            'number='+$random_price
            )
    $msg = send_to_ami $row
    Write-Host $msg
}

$writer.Close()
$stream.Close()

Running this on a powershell interpreter of your choice (I used the Windows Powershell ISE that comes with Windows 10) should yield an output similar to this:

M@1606307770935|Q=1|S=0|M="OK, object accepted"
M@1606307770948|Q=2|S=0|M="OK, object accepted"
M@1606307770950|Q=3|S=0|M="OK, object accepted"
M@1606307770952|Q=4|S=0|M="OK, object accepted"

This message indicates that we are successfully injecting data into the database, huzzah!

Option 3: Bash script

You can download the script here to avoid formatting errors from copy-pasting below.

The bash script uses some standard linux packages for tcp connections and built in bash functionality such as $RANDOM and linux netcat package. It generates some random data, and injects this into the realtime db and types out the data into a file called message.txt.

Note: is that in the example below, we are using the default “demo” user, if you are using a live environment, you might have to edit the username by editing the login message on line 3.

!/bin/bash

login_message='L|I="demo"'
echo $login_message >> message.txt # | nc -N -vv localhost 3289
stocks=(AAPL TSLA IBM INTC GOOG AMZN)
number_of_rows=1000
for i in $(seq 1 $number_of_rows)
do
    timestamp=$(date +"%s")
    random_price=$((1 + RANDOM % 100))
    random_stock=${stocks[$RANDOM % ${#stocks[@]}]}
    message='O|T="streaming"|timestamp='$timestamp'|name="'$random_stock'"|number='$random_price
    echo $message >> message.txt   
done
nc localhost -vv 3289 < message.txt

Running this using the command bash realtime_streaming_random_data.sh should yield something similar to this:

M@1606307770935|Q=1|S=0|M="OK, object accepted"
M@1606307770948|Q=2|S=0|M="OK, object accepted"
M@1606307770950|Q=3|S=0|M="OK, object accepted"
M@1606307770952|Q=4|S=0|M="OK, object accepted"

This message indicates that we are successfully injecting data into the database, huzzah!

How to create visualizations that use realtime data

Now that we have some data coming into the database, let’s set up a visualization for this so we can see it in action.

Open your browser and go to your AMI server and log in, in my case it is simply http://localhost:33332/ and go to the menu item Windows -> New Window.

Image for post

In the new window, select Create Realtime Table/Visualization.

Image for post

Select the Realtime Table table, and under Objects, select the streaming table and hit next.

Image for post

In the next dialog box, leave the selections of the columns as they are, and hit finish.

Image for post

And here is our first realtime table!

Of course, you can also access the table to do transformations to it, maybe you want to aggregate something or reshape the data somehow. To do this, you can access the table using the Data Modeler.

Image for post

Next, right click on the AMI database and select Add Datamodel to AMI. Next, select our new table that we created:

Image for post
Image for post

and hit next.

Image for post

Here you can edit the table as usual with AMI, using AMI script and the data modeler.

Summary

So, that’s it. Using a couple of simple scripts, we have shown how you can utilize the 3forge built in realtime database to stream in data. Notice that I didn’t have to write any code in the 3forge platform to get a simple table running.