3 Using the Cloud Storage Command

Starting from SQLcl release 20.3, use the Cloud Storage (cs) command to set a default Cloud Storage URL, list compartments, buckets and objects, and view some of the contents of an object in cloud storage.

You can access objects in cloud storage using Oracle Cloud Infrastructure Object Storage or Oracle SWIFT Object Storage.

The OCI, LOAD and UNLOAD commands support the default URL specified.

Topics

• Prerequisites

• Cloud Storage Command Options

• Examples

See Also:

• Loading a File and Unloading Table Data

• Calling Oracle Cloud Infrastructure REST APIs Using the OCI Command

3.1 Prerequisites

For using the Cloud Storage command, you must first set the OCI profile for authentication and access.

For more information about how to set up the required SSH keys and configure your Oracle Cloud Infrastructure Software Development Kits, see Setup and Prerequisites in the Oracle Cloud Infrastructure Documentation.

Note the following points:

• Generate a RSA key pair in PEM format (minimum 2048 bits required) without a passphrase. Use this key_file in the profile.

• Do not use relative paths inside the configuration file because relative paths resolve based on the SQLcl launch directory. Therefore, the key_file may not be found.

• Use double backslashes (\\) instead of a single backslash (\) for specifying the path of the key_file in Windows.

To display the list of profiles contained in the OCI configuration file at ~/.oci/config, enter:

oci profile

To set the profile as "demo", enter:

oci profile demo

3.2 Cloud Storage Command Options

You can invoke the Cloud Storage command in SQLcl with cloudstorage or cloud_storage or cs.

For a list of the cloud storage command options in SQLcl, enter:

help cs

The command options are:

• cs [ url ] <url>: Sets the default Cloud Storage URL. The URL must contain the host and optionally the namespace, bucket and object.

  Example:
  --host
  cs https://objectstorage.us-ashburn-1.oraclecloud.com
  --namespace 
  cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test
  –-bucket
  cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
  --object 
  cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/employees.csv

• cs clear: Clears the Cloud Storage URL.

• cs show: Shows the current default Cloud Storage URL and profile settings.

• cs listc [ <compartment> ]: Lists the compartments in the specified compartment. If compartment is not specified, lists the compartments in the namespace as identified by the profile.

• cs listb <compartment>: Lists the buckets in a compartment.

• cs listo [ <bucket> ]: Lists the objects in a bucket. You must include the bucket in the set cloud storage cs command or the listo command.

• cs peek [ <qualified-name> ] [ { pos | position} <position> ] [ size <size> ]: Displays the contents of the object where:

  qualified-name is the name of the object, optionally qualified by the namespace and the bucket. The qualified name combined with the default URL specified must fully identify the object URL.

  position is the start location for display

  size is the number of characters to display. If omitted, 2000 characters are displayed.

3.3 Examples

This section provides some examples:

• Load a Cloud Storage File into a Table

• Unload Table Data into Cloud Storage

• List Compartments, Buckets and Objects

• Load a Local File into Cloud Storage using the OCI PUT command

• Get a File from Cloud Storage using the OCI GET Command

Load a Cloud Storage File into a Table

The following example shows how to load a cloud storage file into a database table.

--Set Cloud Storage URL to the bucket
cs https://objectstorage.us-ashburn-1.example.com/n/test/b/example

--Create Table "locations"
create table locations(location_id NUMBER(5),location_name VARCHAR2(40));
Table LOCATIONS created

--Load data from LOCATIONS_DATA_TABLE.csv in cloud storage into "locations" table of the schema for the current user
load locations cs /o/LOCATIONS_DATA_TABLE.csv
Cloud Storage Set: https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
Qualifier: LOCATIONS_DATA_TABLE.csv 
TargetURL: https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/LOCATIONS_DATA_TABLE.csv 
Profile:freedb

format csv

column_names on
delimiter ,
enclosure_left "
enclosure_right "
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names

--Number of rows processed: 23
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors

select count(*) from locations;

COUNT(*)
–--------
50

Unload Table Data into Cloud Storage

This example shows how to unload data from the "locations" table into cloud storage. The data is unloaded to the cloud storage location and is named LOCATIONS_DATA_TABLE.csv.

--Set Cloud Storage URL to the bucket
cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example

–-Specify delimiter as | and terminator as <eof>
set loadformat delimiter | term <eof>

--Unload locations table into cloud storage
unload locations cs
Cloud Storage Set: https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
TargetURL: https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
Profile: DEFAULT

format csv

column_names on
delimiter |
enclosure_left "
enclosure_right "
encoding UTF8
row_terminator <eof>

** UNLOAD Start ** at 2020.09.10-17.08.34
Export Separate Files to https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example
DATA TABLE LOCATIONS
File Name: LOCATIONS_DATA_TABLE.csv
Number of Rows Exported: 23
** UNLOAD End ** at 2020.09.10-17.08.39

-- Show file contents
cs peek /o/LOCATIONS_DATA_TABLE.csv

"LOCATION_ID"|"LOCATION_NAME"<eof>01|"California"<eof>02|"Alaska<eof>03|"Florida"<eof>04|"Texas"<eof>05|"Virginia"<eof>
06|"Louisiana"<eof>07|"Massachusetts"<eof>08|"Arkansas"<eof>09|"Oregon"<eof>10|"Hawaii"<eof>11|"Maryland"<eof>
12|"Pennsylvania"<eof>13|"Colorado<eof>14|"Michigan"<eof>15|"Minnesota"<eof>16|"Ohio"<eof>17|"Maine"<eof>18|"Arizona"<eof>
19|"Missouri"<eof>20|"Vermont"<eof>21|"Kansas"<eof>22|"Nevada"<eof>23|"Illinois"

List Compartments, Buckets and Objects

This example shows how to list compartments, buckets and objects in cloud storage.

cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test

--Lists the compartments in the namespace
cs listc

COMP_APEX
dbtst
old
ords
pdf-fn-compartment
sandbox
sqldev

--Lists the compartments in the compartment sqldev
cs listc sqldev

sqldev1

--Lists the buckets in the compartment sqldev
cs listb sqldev

example
test 

--Lists the objects in the bucket transfer
cs listo /b/example

COUNTRIES_DATA_TABLE.csv
DEPARTMENTS_DATA_TABLE.csv
REGIONS_DATA_TABLE.csv
LOCATIONS_DATA_TABLE.csv

--Displays the first 2000 characters of LOCATIONS_DATA_TABLE file
cs peek /o/LOCATIONS_DATA_TABLE.csv
 

Load a Local File into Cloud Storage using the OCI PUT command

This example shows how to load a local file into cloud storage using the OCI PUT command and the Cloud Storage command.

--Set Cloud Storage URL to the bucket
cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example

--Put an object from local directory into cloud storage
oci put C:\\Users\\JDOE\\EMPLOYEES_DATA_TABLE.csv /o/myemployees.csv

Status:200 OK

--List the objects in the bucket
cs listo

COUNTRIES_DATA_TABLE.csv
DEPARTMENTS_DATA_TABLE.csv
REGIONS_DATA_TABLE.csv
LOCATIONS_DATA_TABLE.csv
myemployees.csv

Get a File from Cloud Storage using the OCI GET Command

The following example shows how to get a file from cloud storage using the OCI GET command and the Cloud Storage command.

--Set Cloud Storage URL to the bucket
cs https://objectstorage.us-ashburn-1.oraclecloud.com/n/test/b/example/o/

--Set the local directory
cd C:\\Users\JDOE\\TempCS

--Get the myemployees.csv file from cloud storage
oci get /o/myemployees.csv

Status:200 OK
Response Saved to C:\Users\JDOE\TempCs\myemployees.csv