Creating a table in the Oracle NoSQL Database Cloud Service

Steps to create a table in the Oracle NoSQL Database Cloud Service and populate it with data.

Create and populate a table

You can create a table in the Oracle NoSQL Database Cloud Service and populate it with data. To do this, you must be able to connect to the NoSQL Cloud Service using either your own credentials or as an authorized Instance Principal from an Oracle Cloud Compute Instance. If you are new to the NoSQL Cloud Service, the 30-minute tutorial & lab can help you get started. That tutorial presents a basic example that shows you how to create and populate a simple table in the NoSQL Cloud Service using your own credentials.

Example Application: Creating and Populating a Complex Table

The program presented here shows how to create a table in the NoSQL Cloud Service and populate that table with complex data. To use the application presented in this section, first, copy the Java program CreateLoadComplexTable.java to the directory examples/nosql/cloud/table in your environment. You can then compile and execute the application.
package nosql.cloud.table;

import java.io.File;
import java.math.BigDecimal;
import java.security.SecureRandom;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import oracle.nosql.driver.NoSQLHandle;
import oracle.nosql.driver.NoSQLHandleConfig;
import oracle.nosql.driver.NoSQLHandleFactory;
import oracle.nosql.driver.Region;
import oracle.nosql.driver.iam.SignatureProvider;
import oracle.nosql.driver.ops.DeleteRequest;
import oracle.nosql.driver.ops.DeleteResult;
import oracle.nosql.driver.ops.GetRequest;
import oracle.nosql.driver.ops.GetResult;
import oracle.nosql.driver.ops.PutRequest;
import oracle.nosql.driver.ops.PutResult;
import oracle.nosql.driver.ops.QueryRequest;
import oracle.nosql.driver.ops.QueryResult;
import oracle.nosql.driver.ops.TableLimits;
import oracle.nosql.driver.ops.TableRequest;
import oracle.nosql.driver.ops.TableResult;
import oracle.nosql.driver.util.TimestampUtil;
import oracle.nosql.driver.values.ArrayValue;
import oracle.nosql.driver.values.LongValue;
import oracle.nosql.driver.values.MapValue;
import oracle.nosql.driver.values.StringValue;

public final class CreateLoadComplexTable {
    private static final SecureRandom generator = new SecureRandom();
    private final NoSQLHandle ociNoSqlHndl;
    private long nOps = 10; /* Default number of rows. */
    private long nRowsAdded;
    private boolean deleteExisting = false;
    private static String compartment = "";
    private static String tableName = "";
    public static void main(final String[] args) {
        try {
            final CreateLoadComplexTable loadData =
                new CreateLoadComplexTable(args);
            loadData.run();
            System.exit(0);
        } catch (Throwable e) {
            e.printStackTrace();
            System.out.println("Failed to create and populate the " +
                               "requested table [name = " + tableName +
                               "]");
            System.exit(1);
        }
    }
    private CreateLoadComplexTable(final String[] argv) {
        String tenantOcid = "";
        String userOcid = "";
        String fingerprint = "";
        String privateKeyFilename = "";
        String passStr = null;
        File privateKeyFile = null;
        char[] passPhrase = null;
        final int nArgs = argv.length;
        int argc = 0;

        if (nArgs == 0) {
            usage(null);
        }
        while (argc < nArgs) {
            final String thisArg = argv[argc++];
            if ("-tenant".equals(thisArg)) {
                if (argc < nArgs) {
                    tenantOcid = argv[argc++];
                } else {
                    usage("-tenant argument requires an argument");
                }
            } else if ("-user".equals(thisArg)) {
                if (argc < nArgs) {
                    userOcid = argv[argc++];
                } else {
                    usage("-user requires an argument");
                }
            } else if ("-fp".equals(thisArg)) {
                if (argc < nArgs) {
                    fingerprint = argv[argc++];
                } else {
                    usage("-fp requires an argument");
                }
            } else if ("-pem".equals(thisArg)) {
                if (argc < nArgs) {
                    privateKeyFilename = argv[argc++];
                    privateKeyFile = new File(privateKeyFilename);
                } else {
                    usage("-pem requires an argument");
                }
            } else if ("-compartment".equals(thisArg)) {
                if (argc < nArgs) {
                    compartment = argv[argc++];
                } else {
                    usage("-compartment requires an argument");
                }
            } else if ("-table".equals(thisArg)) {
                if (argc < nArgs) {
                    tableName = argv[argc++];
                } else {
                    usage("-table requires an argument");
                }
            } else if ("-n".equals(thisArg)) {
                if (argc < nArgs) {
                    nOps = Long.parseLong(argv[argc++]);
                } else {
                    usage("-n requires an argument");
                }
            } else if ("-phrase".equals(thisArg)) {
                passStr = argv[argc++];
                passPhrase = passStr.toCharArray();
            } else if ("-delete".equals(thisArg)) {
                deleteExisting = true;
            } else {
                usage("Unknown argument: " + thisArg);
            }
        }
        nRowsAdded = nOps;
        System.out.println("COMPARTMENT: " + compartment);
        System.out.println("TABLE: " + tableName);
        final SignatureProvider auth =
            new SignatureProvider(tenantOcid, userOcid, fingerprint,
                                  privateKeyFile, passPhrase);
        final NoSQLHandleConfig config =
            new NoSQLHandleConfig(Region.US_ASHBURN_1, auth);
        ociNoSqlHndl = NoSQLHandleFactory.createNoSQLHandle(config);
        createTable();
    }
    private void usage(final String message) {
        if (message != null) {
            System.out.println("\n" + message + "\n");
        }
        System.out.println("usage: " + getClass().getName());
        System.out.println
            ("\t-tenant <tenant ocid>\n" +
             "\t-user <user ocid>\n" +
             "\t-fp <fingerprint>\n" +
             "\t-pem <private key file>\n" +
             "\t-compartment <compartment name>\n" +
             "\t-table <table name>\n" +
             "\t-n <total records to create>\n" +
             "\t[-phrase <[pass phrase>]\n" +
             "\t-delete (default: false) [delete all “ +
                 “pre-existing data]\n");
        System.exit(1);
    }
    private void run() {
        if (deleteExisting) {
            deleteExistingData();
        }
        doLoad();
    }
    private void createTable() {
        final int readUnits = 10;
        final int writeUnits = 10;
        final int storageGb = 1;
        final int ttlDays = 1;
        /* Wait no more than 2 minutes for table create. */
        final int waitMs = 2 * 60 * 1000;
        /* Check for table existence every 2 seconds. */
        final int delayMs = 2 * 1000;
        /* Table creation statement. */
        final String statement =
            "CREATE TABLE IF NOT EXISTS " + tableName +
            " (" +
               "ID INTEGER," +
               "AINT INTEGER," +
               "ALON LONG," +
               "ADOU DOUBLE," +
               "ANUM NUMBER," +
               "AUUID STRING," +
               "ATIM_P0 TIMESTAMP(0)," +
               "ATIM_P3 TIMESTAMP(3)," +
               "ATIM_P6 TIMESTAMP(6)," +
               "ATIM_P9 TIMESTAMP(9)," +
               "AENU ENUM(S,M,L,XL,XXL,XXXL)," +
               "ABOO BOOLEAN," +
               "ABIN BINARY," +
               "AFBIN BINARY(16)," +
               "ARRY ARRAY (INTEGER)," +
               "AMAP MAP (DOUBLE)," +
               "AREC RECORD(" +
                            "BLON LONG," +
                            "BTIM_P6 TIMESTAMP(6)," +
                            "BNUM NUMBER," +
                            "BSTR STRING," +
                            "BRRY ARRAY(DOUBLE))," +
               "AJSON JSON," +
                   "PRIMARY KEY (SHARD(AINT), ALON, ADOU, ID)" +
              ")" +
              " USING TTL " + ttlDays + " days";
        System.out.println(statement);
        final TableRequest tblRqst = new TableRequest();
        tblRqst.setCompartment(compartment).setStatement(statement);
        final TableLimits tblLimits =
            new TableLimits(readUnits, writeUnits, storageGb);
        tblRqst.setTableLimits(tblLimits);
        final TableResult tblResult =
            ociNoSqlHndl.tableRequest(tblRqst);
        tblResult.waitForCompletion(ociNoSqlHndl, waitMs, delayMs);
        if (tblResult.getTableState() != TableResult.State.ACTIVE) {
            final String msg =
                "TIMEOUT: Failed to create table in OCI NoSQL “ +
                “[table=" + tableName + "]";
            throw new RuntimeException(msg);
        }
    }
    private void doLoad() {
        final List<MapValue> rows = generateData(nOps);
        for (MapValue row : rows) {
            addRow(row);
        }
        displayRow();
        final long nRowsTotal = nRowsInTable();
        if (nOps > nRowsAdded) {
            System.out.println(
                nOps + " records requested, " +
                nRowsAdded + " unique records actually added " +
                "[" + (nOps - nRowsAdded) + " duplicates], " +
                nRowsTotal + " records total in table");
        } else {
            System.out.println(
                nOps + " records requested, " +
                nRowsAdded + " unique records added, " +
                nRowsTotal + " records total in table");
        }
    }
    private void addRow(final MapValue row) {
        final PutRequest putRqst = new PutRequest();
        putRqst.setCompartment(compartment).setTableName(tableName);
        putRqst.setValue(row);
        final PutResult putRslt = ociNoSqlHndl.put(putRqst);
        if (putRslt.getVersion() == null) {
            final String msg =
                "PUT: Failed to insert row [table=" + tableName +
                ", row = " + row + "]";
        }
    }
    /* Retrieves and deletes each row from the table. */
    private void deleteExistingData() {
        final String selectAll = "SELECT * FROM " + tableName;
        final QueryRequest queryRqst = new QueryRequest();
        queryRqst.setCompartment(compartment).setStatement(selectAll);

        long cnt = 0;
        do {
            QueryResult queryRslt = ociNoSqlHndl.query(queryRqst);
            final List<MapValue> rowMap = queryRslt.getResults();
            for (MapValue row : rowMap) {
                final DeleteRequest delRqst = new DeleteRequest();
                delRqst.setCompartment(compartment)
                           .setTableName(tableName);
                delRqst.setKey(row);
                final DeleteResult delRslt =
                    ociNoSqlHndl.delete(delRqst);
                if (delRslt.getSuccess()) {
                    cnt++;
                }
            }
        } while (!queryRqst.isDone());
        System.out.println(cnt + " records deleted");
    }
    /* Counts the number of rows in the table. */
    private long nRowsInTable() {
        final String selectAll = "SELECT * FROM " + tableName;
        final QueryRequest queryRqst = new QueryRequest();
        queryRqst.setCompartment(compartment).setStatement(selectAll);
        long cnt = 0;
        do {
            QueryResult queryRslt = ociNoSqlHndl.query(queryRqst);
            final List<MapValue> rowMap = queryRslt.getResults();
            for (MapValue row : rowMap) {
                cnt++;
            }
        } while (!queryRqst.isDone());
        return cnt;
    }
    /* Convenience method for displaying output when debugging. */
    private void displayRow() {
        final String selectAll = "SELECT * FROM " + tableName;
        final QueryRequest queryRqst = new QueryRequest();
        queryRqst.setCompartment(compartment).setStatement(selectAll);
        do {
            QueryResult queryRslt = ociNoSqlHndl.query(queryRqst);
            final List<MapValue> rowMap = queryRslt.getResults();
            for (MapValue row : rowMap) {
                System.out.println(row);
            }
        } while (!queryRqst.isDone());
    }
    /* Generates randomized data with which to populate the table. */
    private List<MapValue> generateData(final long count) {
        List<MapValue> rows = new ArrayList<>();
        final BigDecimal[] numberArray = {
              new BigDecimal("3E+8"),
              new BigDecimal("-1.7976931348623157E+2"),
              new BigDecimal("12345.76455"),
              new BigDecimal("12345620.789"),
              new BigDecimal("1234562078912345678988765446777475657"),
              new BigDecimal("1.7976931348623157E+305"),
              new BigDecimal("-1.7976931348623157E+304")
        };
        final Timestamp[] timeArray_p0 = {
               TimestampUtil.parseString("2010-05-05T10:45:00"),
               TimestampUtil.parseString("2011-05-05T10:45:01"),
               Timestamp.from(Instant.parse("2021-07-15T11:31:21Z"))
        };
        final Timestamp[] timeArray_p3 = {
               TimestampUtil.parseString("2011-05-05T10:45:01.123"),
               Timestamp.from(
                   Instant.parse("2021-07-15T11:31:47.549Z")),
               Timestamp.from(
                   Instant.parse("2021-07-15T11:32:12.836Z"))
        };
        final Timestamp[] timeArray_p6 = {
               TimestampUtil.parseString(
                   "2014-05-05T10:45:01.789456Z"),
               TimestampUtil.parseString(
                   "2013-08-20T12:34:56.123456Z"),
               Timestamp.from(Instant.parse(
                   "2021-07-15T11:31:47.549213Z")),
               Timestamp.from(Instant.parse(
                   "2021-07-15T11:32:12.567836Z"))
        };
        final Timestamp[] timeArray_p9 = {
               Timestamp.from(Instant.parse(
                   "2021-07-15T12:46:35.574639954Z")),
               Timestamp.from(Instant.parse(
                   "2021-07-15T12:47:32.883922660Z")),
               Timestamp.from(Instant.parse(
                   "2021-07-15T12:48:11.321131987Z"))
        };
        final String[] enumArray =
            {"S", "M", "L", "XL", "XXL", "XXXL"};
        for(int i = 1; i <= count; ++i) {
            byte[] byteArray = new byte[16];
            generator.nextBytes(byteArray);

            MapValue row = new MapValue(true,16);

            row.put("ID", i);
            row.put("AINT", generator.nextInt());
            row.put("ALON", generator.nextLong());
            row.put("ADOU", generator.nextDouble());
            row.put("ANUM", 
                    numberArray[generator.nextInt(
                        numberArray.length)]);
            row.put("AUUID", UUID.randomUUID().toString());
            /* TIMESTAMP */
            row.put("ATIM_P0",
                    timeArray_p0[generator.nextInt(
                        timeArray_p0.length)]);
            row.put("ATIM_P3",
                    timeArray_p3[generator.nextInt(
                        timeArray_p3.length)]);
            row.put("ATIM_P6",
                    timeArray_p6[generator.nextInt(
                        timeArray_p6.length)]);
            row.put("ATIM_P9",
                    timeArray_p9[generator.nextInt(
                        timeArray_p9.length)]);
            /* ENUM */
            row.put("AENU", enumArray[i % enumArray.length]);
            /* BOOLEAN */
            row.put("ABOO", generator.nextBoolean());
            /* BINARY & FIXED_BINARY stored as strings */
            row.put("ABIN",  byteArray);
            row.put("AFBIN", byteArray);
            /* ARRAY of INTEGER */
            ArrayValue integerArr = new ArrayValue();
            for (int j = 0; j < 3; ++j) {
                integerArr.add(generator.nextInt());
            }
            row.put("ARRY", integerArr);
            /* MAP of DOUBLE */
            MapValue map = new MapValue(true,3);
            map.put("d1", generator.nextDouble());
            map.put("d2", generator.nextDouble());
            row.put("AMAP", map);
            /*
             * RECORD of: LONG, TIMESTAMP, NUMBER,
             *            STRING, ARRAY of DOUBLE
             */
            MapValue record = new MapValue(true,5);
            /* LONG element */
            record.put("BLON", generator.nextLong());
            /* TIMESTAMP element */
            record.put("BTIM_P6",
                       timeArray_p6[generator.nextInt(
                           timeArray_p6.length)]);
           /* NUMBER element */
            record.put("BNUM",
                       numberArray[generator.nextInt(
                           numberArray.length)]);
            /* STRING element */
            record.put("BSTR", Double.toString(
                                   generator.nextDouble()));
            /* ARRAY of DOUBLE element */
            ArrayValue doubleArr = new ArrayValue();
            for (int j = 0; j < 3; ++j) {
                doubleArr.add(generator.nextDouble());
            }
            record.put("BRRY", doubleArr);
            row.put("AREC", record);
            /* JSON */
            MapValue json = new MapValue(true,5);
            json.put("id", i);
            json.put("name", "name_" + i);
            json.put("age", i + 10);
            row.put("AJSON", json);
            rows.add(row);
        }
        return rows;
    }
}

Compile and run the application

Compiling the Application

To compile the CreateLoadComplexTable.java application, first change the directory to the parent of the examples directory. Then, assuming you have installed the Oracle NoSQL SDK for Java under that directory, type the command:
javac -classpath oracle-nosql-java-sdk/lib/nosqldriver.jar:examples
      examples/nosql/cloud/table/CreateLoadComplexTable.java

Running the Application

After successful compilation, the application can be executed by typing the command:
java -classpath oracle-nosql-java-sdk/lib/nosqldriver.jar:examples nosql.cloud.table.CreateLoadComplexTable
-tenant <ocid-of-your-tennancy> -user <your-user-ocid> -fp <your-finger-print> -pem <path-to-your-oci-private-key-file>
-compartment <compartment-ocid-for-the-table> -table tableName1 [-n 11] [-delete]

In the example above, all of the arguments related to your user credentials are required (-tenant, -fp, -pem, and -compartment), as is the argument -table, which specifies the name of the table to create and load with data. The remaining arguments (-n and -delete) are optional.

If the -n argument is specified, the value specified represents the number of new rows to generate and write to the table. If the argument is not specified, then 10 rows will be written to the table by default.

If the -delete argument is specified, then all existing rows written to the table by previous executions of the application will first be deleted from the table before adding any new rows.

After the application completes executing, you can verify that the table exists and is populated with data by logging into the Oracle Cloud Console, navigating to the tables section of the Oracle NoSQL Database service, and querying the table having the name you specified for the -table argument.