Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Use DBeaver With OmniSciDB

DZone 's Guide to

How to Use DBeaver With OmniSciDB

Let's see how we can use DBeaver with OmniSciDB.

· Database Zone ·
Free Resource

Introduction

In the previous article, we discussed one installation recipe for OmniSciDB and briefly used the Command Line Interface (CLI) to test our installation. The CLI is very powerful and enables SQL commands to be quickly tested. However, an alternative method to connect to OmniSciDB is to use a tool with a Graphical User Interface (GUI). From the first article in this series, we know that the OmniSci Platform can be used with a browser-based tool called Immerse. However, there are many GUI-based tools available today that can also be used with OmniSciDB. One such popular tool is DBeaver. Let's see how we can use this with OmniSciDB.

Prerequisites

This article assumes that you have followed the installation steps described in the previous article and that you have loaded the sample database. In my environment I have now additionally installed a full Java Development Kit (JDK). My Java environment is as follows:

openjdk version "1.8.0_212"
OpenJDK Runtime Environment (build 1.8.0_212-8u212-b03-0ubuntu1.16.04.1-b03)
OpenJDK 64-Bit Server VM (build 25.212-b03, mixed mode)

Before we download and configure DBeaver, we should ensure that the OmniSciDB server is running. From a terminal window, we can do this as follows:

sudo systemctl status omnisci_server

If it is not running, we can start it as follows:

sudo systemctl start omnisci_server

This is shown in Figure 1.

Figure 1. Start OmniSciDB Server..

Figure 1. Start OmniSciDB Server.

We can now minimize the terminal window.

Download and Extract DBeaver

The DBeaver community download page contains several versions for Linux, as well as Microsoft Windows and Apple Mac platforms. In the previous article, we installed OmniSciDB on Ubuntu, so let's download the Linux tar.gz archive for this article. Next, we'll extract the software files from the archive, and in Figure 2, we can see that the files have been extracted to a folder called "dbeaver" under Downloads. For now, we'll test the software from this directory.

Figure 2. Extract the Software files.

Figure 2. Extract the Software files.

Launch DBeaver

Double-clicking on dbeaver will launch the software, as shown in Figure 3.

Figure 3. Launch DBeaver.

Figure 3. Launch DBeaver.

From the left-nav of the Connect to database window, if we select SQL and type-in mapd into the search window, the result should be similar to Figure 4.

Figure 4. Find MapD.

Figure 4. Find MapD.

Configure MapD Connection

Selecting MapD, we can now configure this connection by clicking Next >, as shown in Figure 5.

Figure 5. Select MapD and click Next >.

Figure 5. Select MapD and click Next >.

We are now presented with Generic JDBC Connection Settings, as shown in Figure 6.

Figure 6. Generic JDBC Connection Settings.

Figure 6. Generic JDBC Connection Settings.

We need to modify these settings. Select Edit Driver Settings as shown in Figure 7.

Figure 7. Edit Driver Settings.

Figure 7. Edit Driver Settings.

We'll modify these settings as follows:

Class Name: com.omnisci.jdbc.OmniSciDriver
URL Template: jdbc:omnisci:{host}:{port}:{database}
Default Port: 6274

Under Libraries, we also need to add the JDBC jar file, which is located in the directory $OMNISCI_PATH/bin (/opt/omnisci/bin). Click Add File, locate the JDBC jar file, and add it.

Our Edit Driver Settings page should now look like Figure 8. Note the JDBC jar file under Libraries.

Figure 8. New Driver Settings.

Figure 8. New Driver Settings.

We can also tune the settings under the Adv. parameters tab. Consult the OmniSciDB JDBC documentation for Supported and Unsupported features. For example, Indexes are not supported and should be unchecked under Adv. parameters.

We'll now save the changes by clicking OK.

On the Generic JDBC Connection Settings page, we need to make some modifications, as follows:

Port: 6274
Database/Schema: omnisci
User name: admin
Password: HyperInteractive

This is shown in Figure 9.

Figure 9. New Generic JDBC Connection Settings.

Figure 9. New Generic JDBC Connection Settings.

Clicking on the Test Connection... button should be successful, as shown in Figure 10.

Figure 10. Test Connection ...

Figure 10. Test Connection...

Click OK followed by Finish to save the changes.

Explore the Flights Database

In DBeaver, we can now explore the flights database that we loaded in the previous article, as shown in Figure 11.

Figure 11. Flights Database.

Figure 11. Flights Database.

Run an SQL Query

Using the SQL Editor, let's try the same SQL query from the previous article:

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 is shown in Figure 12.

Figure 12. Example SQL Query.

Figure 12. Example SQL Query.

The results should match the output produced by the query shown in the previous article.

Next Steps

Take the opportunity to explore the flights database further, run additional queries, and try loading your own data.

Summary

In this article, we have seen how to configure DBeaver to work with OmniSciDB. DBeaver is just one of a number of very popular tools that provide a GUI interface. This can be a quick and convenient way to explore a database, see the relationships between data, run queries and view the output. Next time, we'll look at how we can connect to OmniSciDB from a programming language. Stay tuned!

Topics:
dbeaver ,omniscidb ,database ,tutorial

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}