Description:-
- Steps to create oracle Autonomous Data Warehouse database here
- Steps to Connecting SQL Developer To Autonomous Data Warehouse And Creating Tables here
Data Loading Options
- Data loading via SQL*Net
- Suitable for small volumes of data
SQL*Loader from local filesystem
ETL scripts that use DML to insert/update data
Data loading from Oracle Object Storage - Preferred technique for large volumes of data
Additionally enables data-sharing with other cloud services - Stage data in Oracle Object Storage, then load into the database using new PL/SQL APIs
You can load data into Autonomous Data Warehouse using Oracle Database tools, and Oracle and 3rd party data integration tools. You can load data:
from files local to your client computer, or
from files stored in a cloud-based object store
- For the fastest data loading experience Oracle recommends uploading the source files to a cloud-based object store, such as Oracle Cloud Infrastructure Object Storage, before loading the data into your Autonomous Data Warehouse.
- To load data from files in the cloud into your Autonomous Data Warehouse database, use the new PL/SQL DBMS_CLOUD package. The DBMS_CLOUD package supports loading data files from the following Cloud sources: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, and Amazon AWS S3.
- Here we are going to see how to load data from Oracle Cloud Infrastructure Object Storage using two of the procedures in the DBMS_CLOUD package:
create_credential: Stores the object store credentials in your Autonomous Data Warehouse schema. - You will use this procedure to create object store credentials in your Autonomous Data Warehouse admin schema that you defined in a previous tutorial.
- copy_data: Loads the specified source file to a table. The table must already exist in Autonomous Data Warehouse.
- You will use this procedure to load tables in the admin schema with data from data files staged in the Oracle Cloud Infrastructure Object Storage cloud service.
- Here we are going to see how to load data to SH tables (sales history tables from an Oracle sample schema: SALES, COSTS, TIMES, PRODUCTS, CHANNELS, PROMOTIONS, CUSTOMERS, COUNTRIES, SUPPLEMENTARY_DEMOGRAPHICS).
Data files already uploaded to a staging area; otherwise follow the steps below, to upload your data files to the Oracle Cloud Infrastructure Object Storage service. To use data files already in an object store, your cloud administrator must provide you the object store credentials and the URL path to the files that you will be copying to your Autonomous Data Warehouse tables. If you will be uploading data files to an object store in Oracle Cloud Infrastructure Object Storage, your cloud administrator must provide you the cloud tenant name, and user name and password with read/write privileges to the object store location where the data is to be stored.
Upload Data Files to Your Object Store
Upload to your cloud-based object store the data files that you want to load to your Autonomous Data Warehouse database. here we are use an object store in the Oracle Cloud Infrastructure Object Storage service.
- Log in to your Oracle Cloud Infrastructure Console with the following credentials provided by your Oracle Cloud administrator: cloud tenant, user name, password.
- Select Object Storage from the menu at the top left of the Oracle Cloud Infrastructure console. Select Object Storage from the sub-menu.
- Select a compartment in which to create a bucket to upload your database table data.
- Click Create Bucket to create the storage bucket in which to upload your source files. You will later copy this staged data into database tables in your Autonomous Data Warehouse.
- Enter a bucket name, select the standard storage tier, and click Create Bucket
- Click Upload Object to begin selecting the data files 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 individually.
This example uploads the data files of the SH tables (sales history tables from an Oracle sample schema). Click here datafiles_for_sh_tables to download a zipfile of the 10 SH data files for you to upload to the object store.
Note: Alternatively, you can use curl commands to upload large numbers of files.
- The data files are uploaded to the bucket. These files staged in the cloud are ready to be copied into the tables of your Autonomous Data Warehouse database. Remain logged in to Oracle Cloud Infrastructure Object Storage.
Create an Object Store Auth Token
To load data from an Oracle Cloud Infrastructure Object Storage object store, you need to create an Auth Token for your object store account. The communication between your Autonomous Data Warehouse database and the object store relies on the Auth Token and username/password authentication.
- If you have logged out of Oracle Cloud Infrastructure Object Storage, log back in with the following credentials provided by your Oracle Cloud administrator: cloud tenant, user name, password.
- Hover your mouse cursor over the human figure icon at the top right of the console and click User Settings from the drop-down menu.
3. Click Auth Tokens under Resources on the left of the console.
4.Click Generate Token.
5.A pop-up dialog appears. Set the Auth Token by performing the following steps:
In the pop-up dialog, enter a description.
Click the Generate Token button.
Copy the generated token to a text file. The token does not appear again.
Click Close.
Create Object Store Credentials in your Autonomous Data Warehouse Schema
Now that you have created an object store Auth Token, store in your Autonomous Data Warehouse admin schema the credentials of the object store in which your data is staged.
Open SQL Developer and connect to your Autonomous Data Warehouse database as user admin. See here, Connecting to SQL Developer and Creating Tables, for steps to connect SQL Developer to your Autonomous Data Warehouse database. In that tutorial, WelcomeADWC1! was the suggested password for the user admin.
In a SQL Developer worksheet, use the create_credential procedure of the DBMS_CLOUD package to store the object store credentials in your admin schema.
Create a credential name. You reference this credential name in the copy_data procedure in the next step.
Specify the credentials for your Oracle Cloud Infrastructure Object Storage service: The username and the object store Auth Token you generated in the previous step.
After you run this script, your object store’s credentials are stored in your Autonomous Data Warehouse admin schema.
Copy Data from Object Store to Autonomous Data Warehouse Database Tables
The copy_data procedure of the DBMS_CLOUD package requires that target tables must already exist in in your Autonomous Data Warehouse database. In the previous article, Connecting SQL Developer and Creating Tables, you created in your Autonomous Data Warehouse admin schema all of the target tables.
Now run the copy_data procedure to copy the data staged in your object store to your Autonomous Data Warehouse admin tables.
- In a SQL Developer worksheet, use the copy_data procedure of the DBMS_CLOUD package to copy the data staged in your object store.
For credential_name, specify the name of the credential you defined in , Create Object Store Credentials in your Autonomous Data Warehouse Schema.
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. The values you specify are in bold:
https://swiftobjectstorage.<region name>.oraclecloud.com/v1/<tenant name>/<bucket name>/<file name>
Click here for an example script. In the script, use your own table names, region name, tenant name, bucket name, and file names.
Note: The region name, tenant name, and bucket name can all be found in one place by clicking the ellipsis option menu and going to file details. Note: If you receive an error message that your admin does not have read/write privileges into the Object Store, you may need to properly set up your user privileges or contact your administrator to do so.
2.After you run the procedure, observe that the data has been copied from the object store to the tables in your Autonomous Data Warehouse database.
Reference:-
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZsxfzXxHD6gTg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
