How to Install OmniSciDB on Ubuntu
How to Install OmniSciDB on Ubuntu
In this article, we'll walk through the open source Ubuntu OS CPU Installation with Tarball.
Join the DZone community and get the full member experience.Join For Free
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.
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:
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
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.
We'll now run the installer:
cd $OMNISCI_PATH/systemd sudo ./install_omnisci_systemd.sh
This will prompt us for two paths:
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:
- 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.
- Copy the OmniSci JDBC driver jar file, located in $OMNISCI_PATH/bin, to the sqlline/bin directory.
- 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.
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.
Consult the OmniSciDB JDBC documentation for Supported and Unsupported features.
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.
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!
Opinions expressed by DZone contributors are their own.