Load or query files from a file system directory on Autonomous Database

March 4, 2023 | 3 minute read
Nilay Panchal
Principal Product Manager
Text Size 100%:

If you've spent time with Autonomous Database (ADB-S) on Oracle Cloud, you have likely taken advantage of the DBMS_CLOUD package that provides users with a simple & secure syntax to load data from cloud object stores, as well as help manage data files between the object store and database file system. Today, we're extending DBMS_CLOUD's capabilities enabling users to load data from files in file system directories directly into their database, providing another convenient and efficient method for importing data.

Directory files

Previously, loading data in ADB-S or creating external tables from data in directories required the additional step of sending your data files to your object store of choice, or using the Data Pump tool. While we recommend keeping most of your data in your cloud object storage of choice, this new feature in DBMS_CLOUD allows you to quickly load data you may have lying in your database file system or in a mounted external network file system.

To use this new feature, users may simply load data or create an external table using DBMS_CLOUD while pointing their file URIs to their directory of choice, as in the example below. The DBMS_CLOUD package takes care of all the underlying complexity, such as file access, data parsing, and loading into the database. Note, if you would like a complete walkthrough of how to load data using DBMS_CLOUD in ADB-S please visit this comprehensive LiveLab workshop.

 

-- Example 1: If users want to copy/load a single CSV file from a directory "MY_DIR"
BEGIN

  DBMS_CLOUD.copy_data(

     table_name => 'DWCS_IT1',

     file_uri_list => 'MY_DIR:test.csv',

     format => JSON_OBJECT('type' value 'csv')  );

END;

/



-- Example 2: If users want to copy/load all files in a directory "MY_DIR" use the wildcard character:

BEGIN

  DBMS_CLOUD.copy_data(

     table_name => 'DWCS_IT1',

     file_uri_list => 'MY_DIR:*',

     format => JSON_OBJECT('type' value 'csv'));

END;

/



-- Example 3: If users want to copy files starting with "test" in directory "MY_DIR", they can also use the wildcard character:

BEGIN

  DBMS_CLOUD.copy_data(

     table_name => 'DWCS_IT1',

     file_uri_list => 'MY_DIR:test*',

     format => JSON_OBJECT('type' value 'csv'));

END;

/



-- Example 4: If users want to copy directory files from different directories, separate the directory files with a comma:

BEGIN

  DBMS_CLOUD.copy_data(

     table_name => 'DWCS_IT1',

     file_uri_list => 'MY_DIR:test1.csv, MY_DIR2:test2.csv',

     format => JSON_OBJECT('type' value 'csv'));

END;

/

This method of directory file access can be used to copy data or create external tables over all the datatypes ADB-S supports with object store access, including:

  • CSV, Text
  • JSON
  • Datapump
  • Parquet
  • Avro
  • ORC

Support for directory file support with external and hybrid partioned tables is not currently supported and will arrive soon. For additional detail with respect to directory file support, refer here to the ADB-S documentation.

 

This new feature in DBMS_CLOUD allows users to load data from directory files directly into their databases and is another step forward in simplifying and streamlining the data loading process. With its simplicity and efficiency, it is sure to be a valuable tool for deployments of all sizes.

 

Like what I write? Follow me on the Twitter!

Nilay Panchal

Principal Product Manager

Nilay is a principal product manager at Oracle, responsible for adoption and feature development of Oracle's flagship converged cloud database - Autonomous Database. He was previously a developer and data scientist, and has a decade worth of experience in data warehousing, dimensional modeling, search engines and machine learning. A global Carnegie Mellon graduate, he has had the opportunity to work, travel and study in several different countries in various fields. His avocation is music; in his downtime he enjoys playing guitar or piano with a strong cup of chai nearby.

Nilay blogs regularly, and often speaks at cloud and database events. Follow his work on the Twitter @theproductlad


Previous Post

Serving up gourmet data in the data-driven enterprise

Mike Matthews | 6 min read

Next Post


Keep backups long-term (upto 10 years!) on Autonomous Database, fully managed by Oracle

Nilay Panchal | 5 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider