X

All Things Database: Education, Best Practices,
Use Cases & More

Data Warehouse 101: Setting up Object Store

Philip Li
Product Marketing Manager - Cloud Business Group

In the previous posts we discussed how to set up a trial account, provision Oracle Autonomous Data Warehouse, and connect using SQL Developer.

Get Started With a Free Data Warehouse Trial

The next step is to load data. There are multiple ways of uploading data for use in Oracle Autonomous Data Warehouse. Let's explore how to set up OCI Object Store and load data into OCI Object Store.

Here are step-by-step instructions on how to set up OCI Object Store, load data, and create auth token and database credential for users.

  • From the Autonomous Data Warehouse console, pull out the left side menu from the top-left corner and select Object Storage. To revisit signing-in and navigating to ADW, visit our introduction to data warehouses.

To learn more about the OCI Object Storage, refer to its documentation .

  • You should now be on the Object Storage page. Choose the root compartment in the Compartment dropdown if it is not already chosen.

Create a Bucket for the Object Storage

In OCI Object Storage, a bucket is the terminology for a container of multiple files.

  • Click the Create Bucket button:

  • Name your bucket ADWCLab and click the Create Bucket button.

Upload Files to Your OCI Object Store Bucket

  • Click on your bucket name to open it:

  • Click on the Upload Object button:

  • Using the browse button or drag-and-drop, select the file you downloaded earlier and click Upload Object:

  • Repeat this for all files you downloaded for this lab.
  • The end result should look like this with all files listed under Objects:

Construct the URLs of the Files on Your OCI Object Storage

  • Construct the base URL that points to the location of your files staged in the OCI Object Storage. The URL is structured as follows. The values for you to specify are in bold:

https://swiftobjectstorage.<region_name>.oraclecloud.com/v1/<tenant_name>/<bucket_name>/

  • The simplest way for you to find this information would be to be look at the details of your recently uploaded files.

  • In this example below, the region name is us-phoenix-1, the tenant name is labs, and the bucket name is ADWCLab. This is all of the information you need to construct the swift storage URL above.

  • Save the base URL you constructed to a note. We will use the base URL in the following steps.

Creating an Object Store Auth Token

To load data from the Oracle Cloud Infrastructure(OCI) Object Storage you will need an OCI user with the appropriate privileges to read data (or upload) data to the Object Store. The communication between the database and the object store relies on the Swift protocol and the OCI user Auth Token.

  • Go back to the Autonomous Data Warehouse Console in your browser. From the pull-out menu on the top left, under Identity, click Users.

  • Click the user's name to view the details. Also, remember the username as you will need that in the next step. This username could also be an email address.

  • On the left side of the page, click Auth Tokens.

  • Click Generate Token.

  • Enter a friendly description for the token and click Generate Token.

  • The new Auth Token is displayed. Click Copy to copy the Auth Token to the clipboard. You probably want to save this in a temporary notepad document for the next few minutes (you'll use it in the next step).
    Note: You can't retrieve the Auth Token again after closing the dialog box.

Create a Database Credential for Your User

In order to access data in the Object Store you have to enable your database user to authenticate itself with the Object Store using your OCI object store account and Auth token. You do this by creating a private CREDENTIAL object for your user that stores this information encrypted in your Autonomous Data Warehouse. This information is only usable for your user schema.

  • Connected as your user in SQL Developer, copy and paste this code snippet to SQL Developer worksheet.

Specify the credentials for your Oracle Cloud Infrastructure Object Storage service: The username will be your OCI username (usually your email address, not your database username) and the password is the OCI Object Store Auth Token you generated in the previous step. In this example, the credential object named OBJ_STORE_CRED is created. You reference this credential name in the following steps.

  • Run the script.

  • Now you are ready to load data from the Object Store.

Loading Data Using the Data Import Wizard in SQL Developer

  • Click ‘Tables’ in your user schema object tree. Clicking the right mouse button opens the context-sensitive menu in SQL Developer; select ‘Import Data’:

  • The Data Import Wizard is started. Enter the following information:
    • Select Oracle Cloud Storage as source for the data load
    • Enter the URL of channels.csv as the file to load. You constructed the URL in STEP 8 Construct the URLs of the Files on Your OCI Object Storage. For example, the URL might look something like: https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/ADWCTenancy/ADWCLab/channels.csv
    • Select the Credential you previously created for authentication with the Object Store, OBJ_STORE_CRED
    • Click the Preview button

When you are satisfied with the data preview, click NEXT.

Note: If you see an object not found error here, your user may not be set up properly to have data access to the object store. Please contact your Cloud Administrator.

  • On the Import Method page, you can click on Load Options to see some of the available options. For this exercise, leave the options at their defaults. Enter CHANNELS_CLOUD as the table name and click NEXT to advance to the next page of the wizard.

  • On the Column Definition page, you can control how the fields of the file map to columns in the table. You can also adjust certain properties such as the Data Type of each column. This data needs no adjustment, we can simply proceed by clicking Next.

  • The last screen before the final data load enables you to test a larger row count than the sample data of the beginning of the wizard, to see whether the previously made decisions are satisfying for your data load. Note that we are not actually loading any data into your database during these Tests. Click TEST and look at the Test Results log, the data you would load, any mistakes and what the external table definition looks like based on your inputs.

When done with your investigation, click NEXT.

  • The final screen reflects all your choices made in the Wizard. Click FINISH when you are ready to load the data into the table.

In the next series of posts, we will explore different industries, review industry data sets, query the data, and analyze industry problems with the help of visualizations:

Data Warehouse and Visualizations for Flight Analytics

Data Warehouse and Visualizations for Credit Risk Analysis

Written by Sai Valluri and Philip Li

Join the discussion

Comments ( 2 )
  • Pradeep Thursday, December 20, 2018
    Good to know all these
  • Vijay Saturday, January 19, 2019
    Where do I find lab data to upload into object store? I'm not using autonomous trial account.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha