In a previous post, Oracle Autonomous Database: Accessing & Using Database Actions , I covered the topic of Database Actions available with Oracle Autonomous Database (and how to access them!), I followed this up with a post, Oracle Autonomous Database: Load Data from a Local File which covers how to load data into an Oracle Autonomous Database from a local file.

As a reminder, Database Actions is a web-based interface which leverages ORDS (Oracle REST Data Services) to provide a rich set of features for development, data studio, administration and monitoring Oracle Autonomous Database services. This is relevant for both Oracle Autonomous Data Warehouse (ADW) and Oracle Autonomous Transaction Processing (ATP) service instances. At a high-level, features of “database actions” include execution of SQL statements/scripts, the creation of data modeler diagrams, developing RESTful web services, managing JSON collections, using Data Load/Catalog/Insight features and data transformation tools. See further down this post for a full list and description of available features.

In this post we will look further at the data load feature of “database actions” including how to access this feature and how to use it specifically to load data to your database from Oracle object storage. In the example within this post, I will use data provisioned and held publicly in the “MovieStream” object storage instance, however if you are interested in how you can provision and access your own Object Storage instance then you may wish to see my posts: Creating an Object Storage Bucket in Oracle Cloud & Accessing an Object Storage Bucket in Oracle Cloud using Pre-Authenticated Requests. These data loading features are really simple to use allowing you to efficiently create tables or update existing tables with data that you might have stored locally or in the cloud, all without writing any code.

Note 1 – The details in this post, assume that you already have an Oracle Cloud free tier (or upgraded!) account. If you don’t you can get one here.
Note 2 –  The details below assume that you already have a provisioned & active Oracle Autonomous Database service instance. If you do not, you may wish to follow the steps in my posts: Creating an ATP Database in Oracle Cloud & Connecting to an ATP Database in Oracle Cloud
Note 3 – Given the regular release schedule present in Oracle Cloud, it is possible that the screens may change somewhat beyond the writing of this post. If this happens, please comment on this post and I will try to help you out. (and update this post!)

Accessing the Data Load feature

Step 1: Log into your Oracle Cloud account (free tier is suitable)

Step 2: Click on the menu icon (often referred to as “Hamburger” or “Pancake Stack” icon)

Step 3: Select “Oracle Database” and then select “Autonomous Database”

Step 4: Click on the database instance you will use – In my case I will use “ASG_ATP_DB” which is an ATP instance.

Step 5: Click “Database Actions”

Step 6: You will be presented with the Database Actions Launchpad. By default, this will open as the “admin” user that was provisioned when you created your instance. Click on “Data Load” underneath the “Data Studio” sub-section.

You will be presented with the data load feature.

Load Data From Oracle Object Storage

To load data from object storage follow the below steps:

Step 1: Select “Load Data”

Step 2: Click “Cloud Store” and then “Next”

Step 3: The “Load Data from Cloud Store” screen will appear. Enter an object storage URL in which the data you wish to load is located. In this case, I will use Oracle’s “MovieStream” data which is made publicly available at the following URL:

https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_landing/o

Step 4: Clicking enter will take you to the data loading tool. Here we see a list of folders on the left side that we can select and drag into the data loading job

Step 5: Now, drag the folders that you wish to upload. In this example I will upload movie, genre, sales_sample and pizza_location. Note that when doing so, a dialog box will appear requesting confirmation to all the files in the selected folder to a single target table. In some cases, the folder has only a single file but other times, data lake folders will contain many files of the same type, as you will see with sales data. Click Yes.

Step 5: Once the files have uploaded, the following screen is presented. Here you can see more about the files by clicking on the folder name e.g “movie/”

This screen provides the following “pre-data load” options:

• Alter data loading option by choosing one of the following option:
  • Create table: This will create a brand new table based on the table name given and load the data in the file into it
  • Insert into table: This will try to insert the data from the file into an existing database table, retaining any existing data that is already present in the database table
  • Replace data: This will remove the existing data from an existing database table and insert the data from the file
  • Drop table and create new table : If the database table already exists, this option will drop the table (and the data contained in the table), recreate the table and then load the data from the file into it
• Alter the table name (when creating a new table) or Select an existing table name (when performing other actions)
• Define CSV related options:
  • Encoding
  • Text enclosure
  • Field delimiter
  • Select to confirm the row with column headers
  • Select the row in which to process data from

The tool itself examines the file that has been uploaded and presents recommended data types (and it does a really good job of this too). When you have finished reviewing/amending the options, click “Close”.

Step 6: Now you are happy with the pre-load checks, you can hit the “start” button to begin the data load. The jobs will be sent to the Autonomous Database job scheduler and run in the background.

You can see in the below screenshot that the table does not currently exist in the database.

Step 7: Confirm that you wish to run the data load job by clicking the “Run” button

Step 8: When the job has completed, you will see the number of rows successfully loaded.

You can see in the below screenshot that the table now exists in the database with the data items loaded from the CSV file.

You can see in the below screenshot that the data load tool actual produced the code needed to upload this data which you could save and use in your own scripts if needed.