{{announcement.body}}
{{announcement.title}}

How to Install OmniSciDB on Ubuntu

DZone 's Guide to

How to Install OmniSciDB on Ubuntu

In this article, we'll walk through the open source Ubuntu OS CPU Installation with Tarball.

· Database Zone ·
Free Resource

Introduction

In the previous article, we looked at the OmniSci Platform and briefly described its three main components:

  1. OmniSciDB
  2. OmniSci Render
  3. OmniSci Immerse

Let's now discuss how to install OmniSciDB and get some hands-on experience. The official documentation contains installation instructions for a range of different operating systems and environments, such as Centos or Ubuntu, Docker, AWS, Azure, and GCP.

Installation Steps

In this article, we'll walk through the open source Ubuntu OS CPU Installation With Tarball. Consider installing the GPU version instead if you have the appropriate hardware on your computer system. Check the system requirements before you begin.

Update and Reboot

First, we'll ensure that the system is up-to-date:

sudo apt update
sudo apt upgrade

Next, we'll install a headless Java Runtime Environment (JRE). Skip this step if a suitable JRE is already installed on your computer system.

sudo apt install default-jre-headless

NOTE: Installing a Java Development Kit (JDK) is recommended since we will need this later for code builds and some graphical development tools.

Next, we'll double-check that the following utility is installed:

sudo apt install apt-transport-https

Finally, we'll reboot:

sudo reboot

Create the OmniSci User

We need to create an omnisci group, omnisci user, and home directory, as follows:

sudo useradd -U -m omnisci

Download the OmniSci Archive File

In this article, we are using the tarball installation process. The tarball can be downloaded using a browser, curl, or wget. Let's use wget and save the file in the Downloads directory, as follows:

cd ~/Downloads
sudo wget https://releases.omnisci.com/os/tar/omnisci-os-latest-Linux-x86_64-cpu.tar.gz

Installation

We are now ready to install from the tarball. First, we'll create a directory in our home folder, as follows:

cd ~
sudo mkdir installs
cd installs

Next, we'll expand the tarball into our newly created installation directory:

sudo tar -xvf ~/Downloads/omnisci-os-latest-Linux-x86_64-cpu.tar.gz

Finally, we'll create a symbolic link:

cd /opt
sudo ln -s ~/installs/<omnisci-version> omnisci

Substitute <omnisci-version> for the version you have. For example, in my environment, I have the following at the time of writing this article:

cd /opt
sudo ln -s ~/installs/omnisci-os-4.7.0-20190614-4559864637-Linux-x86_64-cpu omnisci

Create the Environment Variables

We'll now add a number of variables to the .bashrc file in the home directory, as follows:

# User specific aliases and functions
export OMNISCI_USER=omnisci
export OMNISCI_GROUP=omnisci
export OMNISCI_STORAGE=/var/lib/omnisci
export OMNISCI_PATH=/opt/omnisci
export OMNISCI_LOG=/var/lib/omnisci/data/mapd_log

Once the changes have been saved to the .bashrc file, we can open a new terminal window to use these changes.

NOTE: The directory $OMNISCI_STORAGE (/var/lib/omnisci) should be dedicated for use only by the OmniSciDB server.

Perform Initialization

We'll now run the installer:

cd $OMNISCI_PATH/systemd
sudo ./install_omnisci_systemd.sh

This will prompt us for two paths:

  1. OMNISCI_PATH
  2. OMNISCI_STORAGE

These need to match the values we previously saved in the .bashrc file.

After running this script, a data directory is created in $OMNISCI_STORAGE.

Start the OmniSciDB Server

The installation is now complete and we can start the OmniSciDB server:

sudo systemctl start omnisci_server

We can start the server automatically on each system reboot as follows:

sudo systemctl enable omnisci_server

Load the Sample Dataset

To get the list of sample datasets, we can run:

cd $OMNISCI_PATH
sudo ./insert_sample_data

The output should be similar to the following:

Enter dataset number to download, or 'q' to quit:
 #     Dataset                   Rows    Table Name             File Name
 1)    Flights (2008)            7M      flights_2008_7M        flights_2008_7M.tar.gz
 2)    Flights (2008)            10k     flights_2008_10k       flights_2008_10k.tar.gz
 3)    NYC Tree Census (2015)    683k    nyc_trees_2015_683k    nyc_trees_2015_683k.tar.gz

We'll choose Option 2 as a quick load test. Once the data are loaded, we can connect to the database server using the built-in Command Line Environment (CLI), with the default password HyperInteractive, as follows:

$OMNISCI_PATH/bin/omnisql
password: ••••••••••••••••

Now we'll run an example SQL query:

SELECT origin_city AS "Origin",
dest_city AS "Destination",
AVG(airtime) AS "Average Airtime"
FROM flights_2008_10k
WHERE distance < 175
GROUP BY origin_city, dest_city;

This should produce the following output:

Origin|Destination|Average Airtime
West Palm Beach|Tampa|33.81818181818182
Norfolk|Baltimore|36.07142857142857
Ft. Myers|Orlando|28.66666666666667
Indianapolis|Chicago|39.53846153846154
Tampa|West Palm Beach|33.25
Orlando|Ft. Myers|32.58333333333334
Austin|Houston|33.05555555555556
Chicago|Indianapolis|32.7
Baltimore|Norfolk|31.71428571428572
Houston|Austin|29.61111111111111

Use SQLLine (optional)

As mentioned in the previous article, OmniSciDB can work with many third-party open source and commercial tools. A popular and lightweight SQL CLI is SQLLine. Here are the steps to get SQLLine working with OmniSciDB:

  1. Download and build the SQLLine project from GitHub, and it will create a jar-with-dependencies file in the sqlline/target directory. Copy this jar file into the sqlline/bin directory.
  2. Copy the OmniSci JDBC driver jar file, located in $OMNISCI_PATH/bin, to the sqlline/bin directory.
  3. Modify the executable script file in the sqlline/bin directory so that the classpath points to the correct directory, on line 5, as follows:
#!/bin/bash
# sqlline - Script to launch SQL shell on Unix, Linux or Mac OS

BINPATH=$(dirname $0)
exec java -cp "$BINPATH/*" sqlline.SqlLine "$@"

# End sqlline

Now run the following command from the sqlline/bin directory:

./sqlline -d com.omnisci.jdbc.OmniSciDriver -u jdbc:omnisci:localhost:6274:omnisci -n admin -p HyperInteractive

The output should be similar to Figure 1.

Figure 1. SQLLine Connection.

Figure 1. SQLLine Connection.

If we now run the same SQL query that we tested earlier, it will produce the same query output, as shown in Figure 2.

Figure 2. Example SQL Query.

Figure 2. Example SQL Query.

Consult the OmniSciDB JDBC documentation for Supported and Unsupported features.

Next Steps

OmniSciDB can be installed on a variety of different operating systems. Check out the online documentation for further details and installation recipes. Take some time to familiarize yourself with the omnisql CLI and try some additional interactive SQL queries. Optionally, use SQLLine.

In the next article, we'll look at how to configure a third-party graphical tool to work with OmniSciDB.

Summary

In this article, we have seen the step-by-step instructions of how to install an open source version of OmniSciDB on one operating system using a tarball. Now that our OmniSciDB server is installed and running, we can try accessing it using other tools. Next time, we'll look at one such popular graphical tool. Stay tuned!

Topics:
omniscidb ,database ,tutorial ,db tutorial ,ubuntu ,how to install omniscidb

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}