There are different ways to load data to Autonomous Data warehouse Cloud. If you
have small volumes of data to load, you can load data by SQL*Net. But if you
have large volumes of data, the preferred technique is to load from files stored
in a cloud-based object store such as Oracle Object Storage, or Amazon
AWS S3. Let's find out how to upload data to Oracle Object Storage and then copy
this staged data into the database tables in Autonomous Data Warehouse Cloud.
Start with uploading the data. Sign into Oracle Cloud Interface, select Object
Storage from the storage menu. Create a bucket in a compartment of Oracle Object
Storage.
Click Upload Object to begin selection of the data files you want to upload to
the bucket. Navigate to the location of the data
files on your local computer. Drag and drop each file or click Upload
Object to upload each file. Here, the data files of the sales history tables from
an Oracle sample schema uploaded. You can also use curl commands to upload large
numbers of files. The data files are uploaded to the
bucket. These files stage to the cloud are ready to be copied into the tables
of your Autonomous Data Warehouse Cloud database. To load data from an Oracle
Cloud Infrastructure Object Storage objects store, you will need to create a
Swift password for your object store account. The communication between your
Autonomous Data Warehouse Cloud database and the object store relies on the Swift
protocol and username/password authentication. Click User Settings under
your user name on the Object Storage console.
Click Swift Passwords under Resources on the left of the console. Click Generate
Password. A pop-up dialog appears. Set the Swift password by performing the
following steps: In the pop-up dialog enter a description. Click the Generate
Password button. Copy the generated password to a text file. It will not be
shown again. Click Close. Next, store the credentials of the object store in the
target schema of your Autonomous Data Warehouse Cloud database. Open SQL
Developer and connect to your Autonomous Data Warehouse Cloud database as your
target user. In a sequel developer worksheet, use the
create_credential procedure of the DBMS_CLOUD package to store the object store
credentials in your target user schema. Create a credential name. You will
reference this credential name in the copy_data procedure in the next step.
Specify the credentials for your Oracle Data Infrastructure Object Storage
service: The Object Storage username and Swift password generated in the previous
step. After you run this script, your object store's credentials will be stored
in your Autonomous Data Warehouse Cloud user schema. In a SQL Developer
worksheet, use the copy_data procedure of the DBMS_CLOUD package to copy the data
staged in your object store, to your Autonomous Data Warehouse Cloud user
tables. For credential name, specify the name of the credential you defined. For
file_uri_list, specify the URL that points to the
location of the file staged in your object store. The URL is structured as
follows, where you specify the region name, tenant name, bucket name, and file
name. The data has been copied from the object
store to the tables in your Autonomous Data Warehouse Cloud database. All data
load operations done using the PL/SQL package DBMS_CLOUD are logged in two
tables: The dba_load_operations table shows all load operations. The user_load _operations
table shows the load operations in your schema.
This is a query on the user_load_operations table. It shows that five rows
were loaded into the channels table, with no failures. The log and bad files are
accessible as tables. For detailed steps on performing the example shown in this
video, go to the Oracle Learning Library and open the tutorial named "Loading Your
Data." Find out more at cloud.oracle.com/datawarehouse.