Navigation
- Preparing ADW Client Machine
- Creating Autonomous Data Warehouse Instance
- Creating DB Tables in Autonomous Data Warehouse through SQL Developer
- Load Data into OCI Object storage and import the data into AWD
- Create Analytics Cloud ADW Connection and Create Data Visualization Reports
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