Before You Begin

Background

This is the second tutorial in the Integrate GitHub and Oracle Database with Oracle Big Data Manager series. Read the tutorials in the order listed.

• Register a GitHub Repository as a Storage Provider with Oracle Big Data Manager
• Register an Oracle Database as a Storage Provider with Oracle Big Data Manager
• Analyze Data from Multiple Sources with Oracle Big Data Manager Notebook

What Do You Need?

• Access to either an instance of Oracle Big Data Cloud Service or to an Oracle Big Data Appliance, and the required login credentials.
• Access to Oracle Big Data Manager, on either an instance of Oracle Big Data Cloud Service or on an Oracle Big Data Appliance, and the required sign in credentials. A port must be opened to permit access to Oracle Big Data Manager, as described in Enabling Oracle Big Data Manager.
• Access to an Oracle Database and the required login credentials.
• Basic familiarity with HDFS, Spark, database concepts and SQL, and optionally, Apache Zeppelin.

Create and Populate an Oracle Database Table

In this section, you create and populate a new Oracle database table named USERS in your Oracle database.

1. Sign in to Oracle Big Data Manager. See Access Oracle Big Data Manager.
2. Right-click the create-and-populate-users-table.sql file, select Save link as... from the context menu, and then save it to your machine where you will run your Oracle database. This file contains the script to create and populate the USERS table.
3. In a terminal window, connect to your Oracle Database using SQL*Plus as follows:

   $ sqlplus username/password@host:port/SID

   In the preceding command, substitute username and password with your Oracle Database username and password respectively. Substitute host and port with the host and port information where your Oracle Database is running. Substitute SID with your own database SID.

4. Create and populate the USERS table. At the SQL> command-line prompt, execute the create-and-populate-users-table.sql script file as follows:

   SQL> START create-and-populate-users-table.sql 

A "Table created. 1000 rows created." message is displayed.

5. Enter exit to exit SQL*Plus.

Register Oracle Database as a Storage Provider with Oracle Big Data Manager

In this section, you register your Oracle Database as a storage provider with Oracle Big Data Manager.

1. On the Oracle Big Data Manager page, click the Administration tab to display the Storage providers page. In our Oracle Big Data Manager instance, we now have five registered storage providers: Apache Hive, Github, HDFS, Oracle Cloud Infrastructure Object Storage, and Oracle Cloud Infrastructure Object Storage Classic. You registered the Github repository as a storage provider in the tutorial titled Register a GitHub Repository as a Storage Provider with Oracle Big Data Manager.
2. To register an Oracle Database as a storage provider with Oracle Big Data Manager, click Register a new storage provider. The Register storage providers wizard is displayed. It has three pages: General, Storage Details, and Confirmation.
3. In the General wizard page, enter oracle-database-demo in the Name field and enter Oracle Database Storage in the Description field. Select Oracle database from the Storage type drop-down list, and then click Next.
4. In the Storage Details wizard page, enter your Oracle database username and password in the Username and Password fields. In the JDBC URL field, edit the sample Oracle Database connect string using the following format:

   jdbc:oracle:thin:@//host:port/service_name

   In the preceding connect string, substitute host and port with your host and port information. Substitute service_name with your own database service name. In this example, we created the USERS table in our BDM_DEMO schema.

5. Click Test access to storage to make sure that you can access the Oracle Database storage. If the storage details that you provided are correct, the Successful, storage details are correct message is displayed. A Preview of storage content section is displayed on the page. Click Next.



6. In the Confirmation wizard page, review the settings. If you need to make a correction, click the back arrow . If you are satisfied with the settings, click Register. The Storage providers page is re-displayed and the newly registered Oracle database storage is displayed in the list of available storage providers.

   

You can click Manage this provider to edit the Oracle Database storage properties (Name, description, username, password, and the JDBC URL), disable the storage, and remove the storage.

Explore the Registered Oracle Database

In this section, you explore the content of the registered Oracle Database.

1. On the Oracle Big Data Manager page, click the Data tab.
2. In the Data explorer section, select Oracle database (oracle-database-demo) from the Storage drop-down list. Navigate to your schema that contains the USERS table that you created earlier. In this example, we navigated to our BDM_DEMO schema that contains the USERS table. The USERS table is displayed in the Name column.

   

3. Double-click the USERS table. The 'USERS' table preview dialog box is displayed. The USERS table contains records about each student's name, id, and class category. The category column contains a value of 0 or 1 which specifics whether a student is attending class 0 or class 1. Click Close to close the window.

   

   The results of a SQL query are returned in an arbitrary order; therefore, your results might not match the results shown in the image.

Next Tutorial

Analyze Data from Multiple Sources with Oracle Big Data Manager Notebook