X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Managing Autonomous Data Warehouse Using oci-curl

Yasin Baskan
Senior Principal Product Manager

Every now and then we get questions about how to create and manage an Autonomous Data Warehouse (ADW) instance using REST APIs. ADW is an Oracle Cloud Infrastructure (OCI) based service, this means you can use OCI REST APIs to manage your ADW instances as an alternative to using the OCI web interface.

I want to provide a few examples to do this using the bash function oci-curl provided in the OCI documentation. This was the easiest method for me to use, you can also use the OCI command line interface, or the SDKs to do the same operations.

oci-curl

oci-curl is a bash function provided in the documentation that makes it easy to get started with the REST APIs. You will need to complete a few setup operations before you can start calling it.

Start by copying the function code from the documentation into a shell script on your machine. I saved it into a file named oci-curl.sh, for example.

You will see the following section at the top of the file. You need to replace these four values with your own.

 TODO: update these values to your own
        local tenancyId="ocid1.tenancy.oc1..aaaaaaaaba3pv6wkcr4jqae5f15p2b2m2yt2j6rx32uzr4h25vqstifsfdsq";
        local authUserId="ocid1.user.oc1..aaaaaaaat5nvwcna5j6aqzjcaty5eqbb6qt2jvpkanghtgdaqedqw3rynjq";
        local keyFingerprint="20:3b:97:13:55:1c:5b:0d:d3:37:d8:50:4e:c5:3a:34";
        local privateKeyPath="/Users/someuser/.oci/oci_api_key.pem";

How to find or generate these values is explained in the documentation here, let's walk through those steps now.

Tenancy ID

The first one is the tenancy ID. You can find your tenancy ID at the bottom of any page in the OCI web interface as indicated in this screenshot.

Copy and paste the tenancy ID into the tenancyID argument in your oci-curl shell script.

Auth User ID

This is the OCI ID of the user who will perform actions using oci-curl. This user needs to have the privileges to manage ADW instances in your OCI tenancy. You can find your user OCI ID by going to the users screen as shown in this screenshot.

Click the Copy link in that screen which copies the OCI ID for that user into the clipboard. Paste it into the authUserId argument in your oci-curl shell script.

Key Fingerprint

The first step for getting the key fingerprint is to generate an API signing key. Follow the documentation to do that. I am running these commands on a Mac and for demo purposes, I am not using a passphrase, see the documentation for Windows commands and for using a passphrase to encrypt the key file.

mkdir ~/.oci
openssl genrsa -out ~/.oci/oci_api_key.pem 2048
chmod go-rwx ~/.oci/oci_api_key.pem
openssl rsa -pubout -in ~/.oci/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem

For your API calls to authenticate against OCI you need to upload the public key file. Go to the user details screen for your user on the OCI web interface and select API keys on the left.

Click the Add Public Key button and copy and paste the contents of the file oci_api_key_public.pem into the text field, click Add to finish the upload.

After you upload your key you will see the fingerprint of it in the user details screen as shown below.

Copy and paste the fingerprint text into the keyFingerprint argument in your oci-curl shell script.

Private Key Path

Lastly, change the privateKeyPath argument in your oci-curl shell script to the path for the key file you generated in the previous step. For example, I set it as below in my machine.

local privateKeyPath="/Users/ybaskan/.oci/oci_api_key.pem";

At this point, I save my updated shell script as oci-curl.sh and I will be calling this function to manage my ADW instances.

Create an ADW instance

Let's start by creating an instance using the function. Here is my shell script for doing that, createdb.sh.

#!/bin/bash

. ./oci-curl.sh

oci-curl database.us-phoenix-1.oraclecloud.com post ./request.json "/20160918/autonomousDataWarehouses"

Note that I first source the file oci-curl.sh which contains my oci-curl function updated with my OCI tenancy information as explained previously. I am calling the CreateAutonomousDataWarehouse REST API to create a database. Note that I am running this against the Phoenix data center (indicated by the first argument, database.us-phoenix-1.oraclecloud.com), if you want to create your database in other data centers you need to use the relevant endpoint listed here. I am also referring to a file named request.json which is a file that contains my arguments for creating the database. Here is the content of that file.

{
  "compartmentId" : "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
  "dbName" : "adwdb1",
  "displayName" : "adwdb1",
  "adminPassword" : "WelcomePMADWC18",
  "cpuCoreCount" : 1,
  "dataStorageSizeInTBs" : 1,
  "licenseModel" : "LICENSE_INCLUDED"
}

As seen in the file I am creating a database named adwdb1 with 1 CPU and 1TB storage. You can create your database in any of your compartments, to find the compartment ID which is required in this file, go to the compartments page on the OCI web interface, find the compartment you want to use and click the Copy link to copy the compartment ID into the clipboard. Paste it into the compartmentId argument in your request.json file.

Let's run the script to create an ADW instance.

./createdb.sh 

{
  "compartmentId" : "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
  "connectionStrings" : null,
  "cpuCoreCount" : 1,
  "dataStorageSizeInTBs" : 1,
  "dbName" : "adwdb1",
  "definedTags" : { },
  "displayName" : "adwdb1",
  "freeformTags" : { },
  "id" : "ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a",
  "licenseModel" : "LICENSE_INCLUDED",
  "lifecycleDetails" : null,
  "lifecycleState" : "PROVISIONING",
  "serviceConsoleUrl" : null,
  "timeCreated" : "2018-09-06T19:56:48.077Z"

As you see the lifecycle state is listed as provisioning which indicates the database is being provisioned. If you now go to the OCI web interface you will see the new database as being provisioned.

Listing ADW instances

Here is the script, listdb.sh, I use to list the ADW instances in my compartment. I use the ListAutonomousDataWarehouses REST API for this.

#!/bin/bash

. ./oci-curl.sh


oci-curl database.us-phoenix-1.oraclecloud.com get "/20160918/autonomousDataWarehouses?compartmentId=ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a"

As you see it has one argument, compartmentId, which I set to the ID of my compartment I used in the previous example when creating a new ADW instance.

When you run this script it gives you a list of databases and information about them in JSON which looks pretty ugly.

./listdb.sh 
[{"compartmentId":"ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a","connectionStrings":{"high":"adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_high.adwc.oraclecloud.com","low":"adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_low.adwc.oraclecloud.com","medium":"adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_medium.adwc.oraclecloud.com"},"cpuCoreCount":1,"dataStorageSizeInTBs":1,"dbName":"adwdb1","definedTags":{},"displayName":"adwdb1","freeformTags":{},"id":"ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a","licenseModel":"LICENSE_INCLUDED","lifecycleDetails":null,"lifecycleState":"AVAILABLE","serviceConsoleUrl":"https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=ADWDB1&service_type=ADW","timeCreated":"2018-09-06T19:56:48.077Z"},{"compartmentId":"ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a","connectionStrings":{"high":"adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_testdw_high.adwc.oraclecloud.com","low":"adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_testdw_low.adwc.oraclecloud.com","medium":"adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_testdw_medium.adwc.oraclecloud.com"},"cpuCoreCount":1,"dataStorageSizeInTBs":1,"dbName":"testdw","definedTags":{},"displayName":"testdw","freeformTags":{},"id":"ocid1.autonomousdwdatabase.oc1.phx.abyhqljtcioe5c5sjteosafqfd37biwde66uqj2pqs773gueucq3dkedv3oq","licenseModel":"LICENSE_INCLUDED","lifecycleDetails":null,"lifecycleState":"AVAILABLE","serviceConsoleUrl":"https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=TESTDW&service_type=ADW","timeCreated":"2018-07-31T22:39:14.436Z"}]

You can use a JSON beautifier to make it human-readable. For example, I use Python to view the same output in a more readable format.

./listdb.sh | python -m json.tool
[
    {
        "compartmentId": "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
        "connectionStrings": {
            "high": "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_high.adwc.oraclecloud.com",
            "low": "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_low.adwc.oraclecloud.com",
            "medium": "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_medium.adwc.oraclecloud.com"
        },
        "cpuCoreCount": 1,
        "dataStorageSizeInTBs": 1,
        "dbName": "adwdb1",
        "definedTags": {},
        "displayName": "adwdb1",
        "freeformTags": {},
        "id": "ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a",
        "licenseModel": "LICENSE_INCLUDED",
        "lifecycleDetails": null,
        "lifecycleState": "AVAILABLE",
        "serviceConsoleUrl": "https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=ADWDB1&service_type=ADW",
        "timeCreated": "2018-09-06T19:56:48.077Z"
    },
    {
        "compartmentId": "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
        "connectionStrings": {
            "high": "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_testdw_high.adwc.oraclecloud.com",
            "low": "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_testdw_low.adwc.oraclecloud.com",
            "medium": "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_testdw_medium.adwc.oraclecloud.com"
        },
        "cpuCoreCount": 1,
        "dataStorageSizeInTBs": 1,
        "dbName": "testdw",
        "definedTags": {},
        "displayName": "testdw",
        "freeformTags": {},
        "id": "ocid1.autonomousdwdatabase.oc1.phx.abyhqljtcioe5c5sjteosafqfd37biwde66uqj2pqs773gueucq3dkedv3oq",
        "licenseModel": "LICENSE_INCLUDED",
        "lifecycleDetails": null,
        "lifecycleState": "AVAILABLE",
        "serviceConsoleUrl": "https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=TESTDW&service_type=ADW",
        "timeCreated": "2018-07-31T22:39:14.436Z"
    }
]

Scaling an ADW instance

To scale an ADW instance you need to use the UpdateAutonomousDataWarehouse REST API with the relevant arguments.

Here is my script, updatedb.sh, I use to do that.

#!/bin/bash

. ./oci-curl.sh

oci-curl database.us-phoenix-1.oraclecloud.com put ./update.json "/20160918/autonomousDataWarehouses/$1"

As you see it uses the file update.json as the request body and also uses the command line argument $1 as the database OCI ID. The file update.json has the following argument in it.

{
  "cpuCoreCount" : 2
}

I am only using cpuCoreCount as I want to change my CPU capacity, you can use other arguments listed in the documentation if you need to.

To find the database OCI ID for your ADW instance you can either look at the output of the list databases API I mentioned above or you can go the ADW details page on the OCI web interface which will show you the OCI ID.

Now, I call it with my database ID and the scale operation is submitted.

./updatedb.sh ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a

{
  "compartmentId" : "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
  "connectionStrings" : {
    "high" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_high.adwc.oraclecloud.com",
    "low" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_low.adwc.oraclecloud.com",
    "medium" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_medium.adwc.oraclecloud.com"
  },
  "cpuCoreCount" : 1,
  "dataStorageSizeInTBs" : 1,
  "dbName" : "adwdb1",
  "definedTags" : { },
  "displayName" : "adwdb1",
  "freeformTags" : { },
  "id" : "ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a",
  "licenseModel" : "LICENSE_INCLUDED",
  "lifecycleDetails" : null,
  "lifecycleState" : "SCALE_IN_PROGRESS",
  "serviceConsoleUrl" : "https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=ADWDB1&service_type=ADW",
  "timeCreated" : "2018-09-06T19:56:48.077Z"
}

If you go to the OCI web interface again you will see that the status for that ADW instance is shown as Scaling in Progress.

Stopping and Starting an ADW Instance

To stop and start ADW instances you need to use the StopAutonomousDataWarehouse and the StartAutonomousDataWarehouse REST APIs.

Here is my stop database script, stopdb.sh.

#!/bin/bash

. ./oci-curl.sh

oci-curl database.us-phoenix-1.oraclecloud.com POST ./empty.json /20160918/autonomousDataWarehouses/$1/actions/stop

As you see it takes one argument, $1, which is the database OCI ID as I used in the scale example before. It also refers to the file empty.json which is an empty JSON file with the below content.

{
}

As you will see this requirement is not mentioned in the documentation, but the call will give an error if you do not provide the empty JSON file as input.

Here is the script running with my database OCI ID.

./stopdb.sh ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a

{
  "compartmentId" : "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
  "connectionStrings" : {
    "high" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_high.adwc.oraclecloud.com",
    "low" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_low.adwc.oraclecloud.com",
    "medium" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_medium.adwc.oraclecloud.com"
  },
  "cpuCoreCount" : 1,
  "dataStorageSizeInTBs" : 1,
  "dbName" : "adwdb1",
  "definedTags" : { },
  "displayName" : "adwdb1",
  "freeformTags" : { },
  "id" : "ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a",
  "licenseModel" : "LICENSE_INCLUDED",
  "lifecycleDetails" : null,
  "lifecycleState" : "STOPPING",
  "serviceConsoleUrl" : "https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=ADWDB1&service_type=ADW",
  "timeCreated" : "2018-09-06T19:56:48.077Z"

Likewise, you can start the database using a similar call. Here is my script, startdb.sh, that does that.

#!/bin/bash

. ./oci-curl.sh

oci-curl database.us-phoenix-1.oraclecloud.com POST ./empty.json /20160918/autonomousDataWarehouses/$1/actions/start

Here it is running for my database.

./startdb.sh ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a

{
  "compartmentId" : "ocid1.tenancy.oc1..aaaaaaaaro2vctz2hianklgq77hguo6jzcs6ezyheouqfsald4x3nubpwr2a",
  "connectionStrings" : {
    "high" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_high.adwc.oraclecloud.com",
    "low" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_low.adwc.oraclecloud.com",
    "medium" : "adwc.uscom-west-1.oraclecloud.com:1522/nqpobuuiaedvlyf_adwdb1_medium.adwc.oraclecloud.com"
  },
  "cpuCoreCount" : 1,
  "dataStorageSizeInTBs" : 1,
  "dbName" : "adwdb1",
  "definedTags" : { },
  "displayName" : "adwdb1",
  "freeformTags" : { },
  "id" : "ocid1.autonomousdwdatabase.oc1.phx.abyhqljszen442afj6ogitgs3dwk2iunhv7zdndllf6o6is6xg2ku5a7uf3a",
  "licenseModel" : "LICENSE_INCLUDED",
  "lifecycleDetails" : null,
  "lifecycleState" : "STARTING",
  "serviceConsoleUrl" : "https://adwc.uscom-west-1.oraclecloud.com/console/index.html?tenant_name=OCID1.TENANCY.OC1..AAAAAAAARO2VCTZ2HIANKLGQ77HGUO6JZCS6EZYHEOUQFSALD4X3NUBPWR2A&database_name=ADWDB1&service_type=ADW",
  "timeCreated" : "2018-09-06T19:56:48.077Z"

Other Operations on ADW Instances

I gave some examples of common operations on an ADW instance, to use REST APIs for other operations you can use the same oci-curl function and the relevant API documentation. For demo purposes, as you saw I have hardcoded some stuff like OCIDs, you can further enhance and parameterize these scripts to use them generally for your ADW environment.

Next, I will post some examples of managing ADW instances using the command line utility oci-cli.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services