Skip to main content
Oracle is one of the database vendors supported on NexusOne. In this guide, you’ll deploy an Oracle database instance in a public cloud using Terraform, add datasets into the database, and ingest the database into NexusOne.

​
Prerequisites

  • An existing AWS account
  • Appropriate NexusOne permission: nx1_ingest, nx1_monitor, nx1_s3_admin, airflow_user, superset_user, spark_sql, and trino_admin
  • AWS command line tool installed
  • sqlplus + oracle instant client command line tool installed
  • Terraform installed

​
Authenticate to your public cloud

Use your public cloud’s command line tool to authenticate to your cloud account. This allows Terraform to interact with your cloud account. The following AWS command requests that you enter the following AWS credentials:
  • Access key ID
  • Secret access key
  • Default region
# User account login
aws configure
To create an access key ID and secret access key, refer to How IAM users can manage their own access keys in the AWS documentation.

​
Define a Terraform and cloud provider

After authenticating to your cloud account using the command line tool, create the following Terraform file. 
# Terraform provider
terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = ">=6.32.0"
    }
  }
}

# AWS provider
provider "aws" {
  region = "us-west-2"
}
The defined cloud provider automatically uses the credentials you defined when interacting with the cloud provider’s command line tool.

​
Define an Oracle instance

Create the following Terraform file with the Oracle instance resources defined. 
# Create the database instance
resource "aws_db_instance" "oracle" {
  allocated_storage   = 20
  storage_type        = "gp2"
  engine              = "oracle-se2"
  license_model       = "bring-your-own-license"
  instance_class      = "db.t3.small"
  username            = "oracleadmin"
  password            = "NexusOracle!"
  skip_final_snapshot = true
  publicly_accessible = true
}

# Output the database address
output "database_address" {
  value = aws_db_instance.oracle.address
}
Public access to the database is for demonstration purposes only.

​
Deploy the Oracle instance

After defining the Oracle Terraform resource specific to your cloud provider, deploy the Terraform resources.
  1. Initialize the Terraform configuration. 
    terraform init
  2. Create the Oracle instance. 
    terraform apply --auto-approve
Once the resources are successfully created, Terraform outputs the database address.

​
Add datasets to the database

To ingest datasets from the database into NexusOne, these datasets need to exist first. Take the following steps to add datasets to the database:
  1. Authenticate to the database using the sqlplus command line tool. 
    sqlplus oracleadmin@//<database_address>/ORCL
  2. When prompted, enter your password, NexusOracle!.
  3. Create a schema. 
    CREATE USER tutorial IDENTIFIED BY "NexusOracle!"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
    In Oracle, USER is also the schema name.
  4. Create a table inside the tutorial schema. 
    CREATE TABLE tutorial.test (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(4000)
);
  5. Insert data into the table. 
    INSERT INTO tutorial.test (name) VALUES ('Nexus');
INSERT INTO tutorial.test (name) VALUES ('Cognitive');
  6. View all rows in the test table. 
    SELECT * FROM tutorial.test;
  7. Permanently save the changes to make the data visible to NexusOne. 
    COMMIT;

​
Ingest the database into NexusOne

Now that a dataset is available on the Oracle database, you can ingest the database into NexusOne using the following steps:
  1. Log in to NexusOne.
  2. From the NexusOne homepage, navigate to Ingest > Database > From Table.
  3. Enter the table and schema name:
    • Source Schema: tutorial
    • Source Table: test
  4. Add connection details:
    • Database URL: jdbc:oracle:thin:@//<database_address>:1521/ORCL
    Port 1521 is Oracle’s default port number.
    • Username: oracleadmin
    • Password: NexusOracle!
  5. Add ingest details:
    • Name: ingest_database
    • Schema: oracle_schema
    • Table: oracle_table
    • Schedule: Run Once
    • Mode: append
    • Tags: Don’t add any tags.
  6. After configuring all fields, click Ingest to submit the job. Wait for a few minutes until you see a success message appear with an option to click View Jobs.

​
Monitor job

When you ingest the database, this creates an Airflow job. To monitor the status of the job, use the following steps:
  1. Click View Jobs, or navigate to the NexusOne homepage and then click Monitor.
  2. Find your job name, ingest_database, in the list, and watch its current status.
  3. Wait for a few minutes, and then refresh your browser until the status changes to Completed.

​
Visualize your dataset

Use the following steps to visualize your dataset:
  1. On the NexusOne homepage, click Discover to launch Superset.
  2. Hover your mouse over SQL, and then select SQL Lab.
  3. Enter the following command in the query box:
SELECT * FROM oracle_schema.oracle_table
visualize-oracle-v4-1-2

Visualize your dataset

​
Delete the schema and Terraform resources

After completing the tutorial, delete the created schema and Oracle instance using the following steps:
  1. Delete the schema. 
    DROP USER tutorial CASCADE;
  2. Destroy the Terraform resources. 
    terraform destroy --auto-approve

​
Additional resources