Data Loading Made Easy for Oracle Autonomous Database

Introduction

Did you know that Oracle Autonomous Database includes an easy-to-use set of data loading tools that can be used to load data into the Database and link external (virtual) tables to data in cloud stores such as Oracle Cloud Infrastructure Object Store? If not, it’s time to learn about these terrific tools. They are a great set of tools to add to your database management toolkit.

The Autonomous Database data loading tools focus on ease of use, allowing you to quickly create new tables and update existing tables with data on local computers or cloud stores such as Oracle Cloud Infrastructure (OCI) Object Storage, Amazon S3, Azure Blob Storage, Google Cloud Storage, and others, in a variety of formats such as CSV/text, Excel, JSON, Parquet and Avro. All without writing a single line of code. In just a few minutes.

These tools are complementary to full-featured ELT tools such as Oracle Data Integrator. For example, you might quickly load data into the Autonomous Database from your local computer or cloud store using the built-in data loading tools and continue the data preparation journey using Oracle Data Integrator.

Accessing Database Actions Tools

All the Autonomous Database built-in data loading tools are part of the Database Actions tool set. You can access Database Actions from the Autonomous Database Service Console or bookmark the URL to access it directly.

There are dedicated data loading tools and data loading tools built into SQL Worksheet.

If you are using SQL Worksheet and want to quickly load files from your PC or Mac, the SQL Worksheet data loader is a good choice. It’s quick and easy, and easily accessible within your SQL Worksheet session.

Use the Database Actions data loading tools for quick and easy loading from your local computer and cloud stores. The Data Load tools provide some extra capabilities. For example, merging into existing tables and linking to the cloud stores as external tables (where data is accessed from cloud store location at query time rather than being loaded into a table).

I should also mention that the new Data Transform tool for Autonomous Database provides data loading capabilities from other databases including, IBM DB2 and Microsoft SQL Server, and applications such as Oracle Sales Cloud and NetSuite (as well as many more data sources). And as the name implies, the Data Transform tool includes a set of easy-to-use, but powerful data transformation tools.

Access SQL Worksheet using the SQL card and the data loading tools using the Data Load card.

Data Loading Capabilities

The Data Load tools provide three main capabilities:

• Loading data from your PC or Mac. Data is loaded from files into Autonomous Database tables.
• Linking tables in Autonomous Database to either files in object storage using external tables or tables in a different instance of the Autonomous Database using database links. In both cases, data is accessed from the data source only when needed by a query.
• Feeding data into a table into Autonomous Database automatically when new data is loaded into an object storage bucket.

The Data Load tools also allow you to register cloud storage locations for future data load jobs, review past data load jobs, and view data in tables. Data Load capabilities are accessed using the cards below. Choose an activity, then the location of the data, and click your way to loading data and linking.

Whether you are loading from a drive on your PC or Mac or a cloud storage location, files are displayed in a familiar UI pattern. In the case of loading from cloud storage, the Cloud Storage Location plays the role of the drive and files are displayed within folders. Just double-click a file or a folder with a collection of related files to create a data load job.

The Data Load tools provide several different data loading options, from creating new tables to merging into existing tables. They do a stellar job at examining the data files and recommending data types and date format masks. In most cases, you can use the recommendations as is. In the example below, over 100 files on the OCI Object Storage will be loaded into a single table. All with just a few clicks.

You can use the Data Tool interactively to load data directly within the tool. Just smash the green button to start all the data load jobs in the cart. The jobs will be sent to the Autonomous Database job scheduler and run in the background.

Data load complete!  That was easy.

GUIs are great for setting up data load jobs and running them once or occasionally, but what about running them periodically or embedding them into a larger data flow process? Time to write some code, right? Wrong! The Data Load tool writes the code for you. Whether you are creating a new table and loading data, appending data into an existing table, or even merging data into an existing table, the data load tool generates all code. Just copy it and embed it in your scripts. Just think of all the time you will save! And because it uses documented DBMS_CLOUD procedures, you can modify the code (but you probably will never need to).

If you’ve overlooked the buildiin Data Load tools, it’s time to give them a try.

Learn More About Oracle Autonomous Database

Learn more about Oracle Autonomous Database  and all of the Database Actions tools at Oracle.com.

Try for Yourself Using LiveLabs

Give the data loading tools a try for yourself by running the Load and Update MoviesStream Data in Oracle ADW using Data Tools.

Watch the Data Loading Tools in Action

Get a preview of the Data Load tools by watching my Data Loading in Oracle Autonomous Database video on YouTube. I give five data loading demos: Excel and JSON from files on my Mac, creating a table from a cloud store folder with a large collection of files CSV files, merging data in an existing table, and loading Parquet file from a Oracle Cloud Store folder. No code, all in less than twenty minutes!