Tag: Object storage

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

Data Warehouse and Visualizations for Flight Analytics

Data Warehouse and Visualizations for Credit Risk Analysis

Written by Sai Valluri and Philip Li