04 Load Data into OCI Object storage and import that data into Autonomous Data warehouse

Navigation
Goal

We will create a Bucket in OCI Object store , Load data files as Objects inside that Bucket , Use SQL Developer PLSQL Block to import the data into those tables

Create Bucket and Upload Object files

 

you can download all these objects from this location

Example chan_v3.dat

3|"Direct Sales"|"Direct"|12|"Channel total"|1|
9|"Tele Sales"|"Direct"|12|"Channel total"|1|
5|"Catalog"|"Indirect"|13|"Channel total"|1|
4|"Internet"|"Indirect"|13|"Channel total"|1|
2|"Partners"|"Others"|14|"Channel total"|1|

Inspecting URL of each of these dat files

Make note of URL of each of these files

https://objectstorage.us-ashburn-1.oraclecloud.com/n/sometenant/b/adw_bucket/o/chan_v3.dat

Region , Tenancy , bucket name and file name are to be noted. highlighted in red

Corresponding FILE URI will be

https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/chan_v3.dat

Generate Auth Token

Click on username, Auth Token and Generate token and save the random id thats generated we will need this for later use from SQL developer

for now we will name this as Random_Auth_Token

Connect to SQL Developer run the following PLSQL Block
begin
  DBMS_CLOUD.create_credential (
    credential_name => 'OBJ_STORE_CRED2',
    username => 'username of who created the OCI bucket',
    password => 'Random_Auth_Token'
  ) ;
end;
/

We would need to run few more PLSQL Blocks to actually fetch the data

/* You will need to swap in your table names, region name, tenant name, bucket name, and file names */

begin
 dbms_cloud.copy_data(
    table_name =>'CHANNELS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/chan_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'COUNTRIES',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/coun_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/

begin
 dbms_cloud.copy_data(
    table_name =>'CUSTOMERS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/cust1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true',  'dateformat' value 'YYYY-MM-DD-HH24-MI-SS')
 );
end;
/
 

begin
 dbms_cloud.copy_data(
    table_name =>'CUSTOMERS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/n/sometenant/b/adw_bucket/o/cust1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS')
 );
end;
/

begin
 dbms_cloud.copy_data(
    table_name =>'SUPPLEMENTARY_DEMOGRAPHICS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/dem1v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'SALES',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/dmsal_v3.dat',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'PRODUCTS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/prod1v3.dat',
    format => json_object('delimiter' value '|', 'quote' value '^', 'ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/

begin
 dbms_cloud.copy_data(
    table_name =>'PROMOTIONS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/prom1v3.dat',
    format => json_object('delimiter' value '|', 'quote' value '^', 'ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/

begin
 dbms_cloud.copy_data(
    table_name =>'TIMES',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/time_v3.dat',
    format => json_object('delimiter' value '|', 'quote' value '^', 'ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/
 
begin
 dbms_cloud.copy_data(
    table_name =>'COSTS',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/costs.dat',
    format => json_object('delimiter' value '|', 'quote' value '^', 'ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/

 begin
 dbms_cloud.copy_data(
    table_name =>'SALES',
    credential_name =>'OBJ_STORE_CRED2',
    file_uri_list =>'https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/sometenant/adw_bucket/sale1v3.dat',
    format => json_object('delimiter' value '|', 'quote' value '^', 'ignoremissingcolumns' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
 );
end;
/

===============
SELECT channel_desc, TO_CHAR(SUM(amount_sold),'9,999,999,999') SALES$,
   RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
   RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
  AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
  AND sales.channel_id=channels.channel_id
  AND times.calendar_month_desc IN ('2000-09', '2000-10')
  AND country_iso_code='US'
GROUP BY channel_desc;
 
Reality check for data in Autonomous Data Warehouse

Summary of Steps

We Generated Random Auth token for a given OCI user, The same user created OCI Object Bucket and Uploaded Objects , these objects were data files ,

We used the login id ( used to login to cloud dashboard ) and random token in the sql function to generate a variable OBJ_STORE_CRED2 , we used this variable to pull data from Object stored into ADW tables using SQL Developer.

What Next >> Create Autonomous Analytics cloud ADW Connection and create Data Visualization

Reference :  Loading Data

Files Location : Data Files Zip