Dremio Jekyll

Installing Dremio and Oracle Database on the Oracle Cloud

Intro

The second post in the serie about Dremio and most popular cloud providers covers deployment Dremio and Oracle database on the Oracle Cloud platform. It will guide you through the process of launching Linux virtual machine and Oracle database instances, as well as Dremio installation and connecting it with the database.

Prerequisites

You must have an Oracle cloud account to follow the tutorial. If you don’t have one, open https://www.oracle.com/index.html, choose Sign in and follow the guidelines. If you have an Oracle account, it requires a few steps to get a Cloud account. Sign in into http://www.oracle.com, choose Account » Cloud Account » Sign in and follow the guidelines.

If you are new to the Oracle cloud, you can use Free Trial option. On screenshots of this tutorial you will see the free promotion balance.

Launching Oracle Database Service

Once you have Oracle cloud account, Oracle database instance can be launched.

  1. Sign in to Oracle cloud account, then select appropriate Data Center and choose My services:

Sign into your Oracle cloud account

  1. Choose Create Instance

Create your instance on Oracle Cloud

  1. Choose Quick Start Services tab Create for Database instance.

Quick Start on Oracle Cloud

  1. Choose Create Service.

Enter Service Name.

Enter service name on Oracle Cloud

  1. Choose **Next **to accept default configuration.

Enter Administration password. In this tutorial backup and recovery strategy is not covered because of demonstration purposes, therefore choose None for the Backup destination.

Setting up Dremio on Oracle Cloud

  1. Choose Edit to set up SSH Public key.

Choose Create a New Key.

Create a new key on Oracle Cloud

Click Enter, then Download - your key pair will be saved on the local disk as a sshkeybundle.zip file.

Then choose Done to return to the Service Details page.

  1. Choose Next to get confirmation form.

Confirm your choices

  1. Choose Create.

You will be forwarded to the Services list. It takes some time to start the service, Status should be checked.

Checking the status of your instance on Oracle Cloud

  1. After service is started, choose Service name to open Service Overview.

Setting up your service name on Oracle Cloud

Accessing your database service

Tutorial shows how to access database service using Oracle SQL Developer, it is a is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments.

First we need to allow public internet inbound connections on port 1521.

  1. Choose Access Rule dropdown menu.

Configure your Access Rule for Oracle Cloud

Access Rule form will be opened.

  1. By default the most ports for inbound connections are blocked. Select Enable for the public internet rule on port 1521.Setting up Dremio on Oracle Cloud

  2. Open SQL Developer and choose New connection.

Create new connection to database on Oracle cloud with SQL Developer.

  1. New / Select Database Connection dialog will be opened.

Enter required information:

  • Connection Name

  • system as User Name

  • Administration Password as Password

  • Public IP **as **Hostname

  • Copy service name from Connect String (substring after ‘/’ character) as Service name. It establishes a connection with the pluggable database PDB1.

Choose Test to test a connection.

Test your connection on Oracle Cloud

Choose Connect to save the connection and open new SQL worksheet.

Creating a TEST table

Just for the learning purposes let us create simple table and insert a data.

  1. Add new Oracle user DREMIO.

create user dremio identified by pass;

grant dba to dremio;

  1. Create TEST table in DREMIO schema.

create table dremio.test

(test varchar(2000));

  1. Insert a data.

insert into dremio.test values (‘Hello world!’);

commit;

Inserting test values into Oracle database on Oracle cloud

User DREMIO will be used later to create Oracle datasource in Dremio.

The next task is to create Compute Classic service and install Dremio on a virtual machine.

Launching Linux virtual machine

  1. Open Dashboard, then Create Instance, open Quick Start tab and choose Compute Classic service.

Quick start for instance on Oracle Cloud

  1. Enter Instance Name and SSH Key name. Choose Download.

  2. After downloading private key as Downloads/DremioInstance.key, choose Customize. The instance, which will be configured in this tutorial, is not large, but it is enough to install Dremio and test database connectivity..

  3. Select Image, here OL_7.2_UEKR4_x86_64 is used. Choose Next.

Select the Linux type for your instance on Oracle Cloud

  1. Select Shape. The minimal oc3 is used. Choose Next.

Setting up the instance size on Oracle Cloud

  1. Configure Instance.

Enter Name, Label, Description and select previously saved SSH Key DremioInstance. Choose Next.

Configure the instance on Oracle CLoud

  1. Configure Network. Only Shared Network is configured, since it suits well for the case with only two nodes - Dremio and Oracle database.

Enter DNS Hostname Prefix, choose Next.

Configure DNS on Oracle Cloud

  1. Choose Next to Accept default Storage options

Accept default storage option on Oracle Cloud

  1. Review the instance. Choose Create.

Create instance on Oracle Cloud

  1. It takes a few minutes to start new instance. You can open Instances list by selecting Dashboard -> Compute Classic -> Open Service Console

Starting new instance on Oracle CLoud

Accessing Linux instance

You can use SSH to log in to your instance as the default ‘opc’ user by using the following command:

1.Open terminal window, set appropriate permissions for the private key and establish the connection.

$ chmod 400 Downloads/DremioInstance.key

$ ssh opc@129.158.100.83 -i Downloads/DremioInstance.key

Setting up keys on Oracle Cloud

Allow inbound traffic for the Dremio service on port 9047.

By default only SSH access is allowed on port 22. Therefore it is required to configure TCP access on port 9047 for the Dremio web console.

  1. Open Network » Shared Network » Security Applications

Configure TCP for Dremio port on Oracle Cloud

  1. Choose Create Security Application.

Enter Name and Port Range. Choose Create.

Setting up Security on Oracle Cloud

  1. Open Network » Shared Network » Security Rules

Configuring network on Oracle Cloud

  1. Choose Create Security Rule.

Enter Name, optional Description** and select Security Application, Source and Destination. Choose Create.

Creating security rule on Oracle Cloud

Installing Dremio

  1. Before installing Dremio, we need to Install Java 8 OpenJDK.

Open SSH session and type the following command.

$ sudo yum install java-1.8.0-openjdk

Install Java on Oracle Cloud

After installing Java, you can check the result.

$ java -version

Check Java install on Oracle Cloud

  1. Download Dremio rpm installation.

Download link for Dremio can be copied here.

Type the following command.

$ curl -O http://download.dremio.com/community-server/1.1.0-201708121825170680-436784e/dremio-community-1.1.0-201708121825170680_436784e_1.noarch.rpm

Download Dremio on Oracle Cloud

  1. Install Dremio.

$ sudo yum localinstall dremio-community-1.1.0-201708121825170680_436784e_1.noarch.rpm

Install Dremio on Oracle Cloud

Configuring and starting Dremio

  1. Oracle Linux has a service, which is running on TCP port 45678.

$ sudo netstat -tulpn

Start your Linux process

Since Dremio also uses this port for one of its services, it requires to make some editions in dremio.conf file.

  1. It can be done using command line text editor Vi.

$ sudo vi ../../etc/dremio/dremio.conf

  1. After opening file, do the following:

Press Insert key to enter insert mode

Add services section below paths to change default port for the fabric service.

services: {

fabric: {

port: 45679

}

}

  1. Press Esc and Shift-“:” to enter command mode.

Type** wq, then **Enter to save changes and exit.

Save your config on Oracle Cloud

  1. Start Dremio

$ sudo service dremio start

Start Dremio on Oracle Cloud

Accessing Oracle database using Dremio

1.Open Dremio on Linux instance Public IP

http://129.158.67.61:9047

Launch Dremio on Oracle Cloud

After entering information on the first page you will see the main Dremio screen.

View Dremio home page on Oracle Cloud

Accessing Oracle database

  1. Choose Add Source » Oracle

Enter connection parameters the same way as for SQL Developer.

  • Name oracle-cloud

  • Public IP as Hostname

  • Copy service name from Connect String (substring after ‘/’ character) as Service name. It establishes a connection with the pluggable database PDB1.

  • Username dremio

  • Password pass

Choose Save.

Save your Oracle connection

It can take a time to get metadata from Oracle database.

  1. After finishing the process choose oracle-cloud under the Sources to get available Oracle schemas.

Preview Oracle schemas

  1. Open DREMIO schema and then click TEST table.

That is what we need - get data from the Oracle table using Dremio query.

Query Oracle from Dremio

  1. To save new virtual dataset into your own space choose Save As:

Save virtual dataset for Oracle into your Dremio space

Conclusion

In this tutorial has been built typical scenario, how to use Dremio and relational database within Oracle cloud. Scenario includes the following steps:

  • Launching Oracle database service
  • Launching virtual Linux machine compute service
  • Running Dremio within Linux machine
  • Establish connection between Dremio and Oracle