Using SQL commands

You can use CREATE TABLE command in SQL to create NoSQL tables.

The following section highlights different options that can be used while creating a table using the CREATE TABLE DDL statement.

Example 1: Create an airline baggage tracking application table that holds baggage information of passengers in an airline system.

CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)

In the schema above, you use the CREATE TABLE statement to define a BaggageInfo table. The passenger's ticket number, ticketNo is the primary key of the table. The fullName, gender, contactPhone, and confNo (reservation number) fields store the passenger's information, which is part of fixed schema. The bagInfo column is a schema-less JSON array, which represents the tracking information of a passenger's checked-in baggage.

For more details on airline baggage tracking application, see Airline baggage tracking application.

Example 2: Create a streaming media service table with a JSON field to keep track of the subscriber's current activity.

CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON, 
PRIMARY KEY(acct_id)
)USING TTL 5 DAYS

In the schema above, you use the CREATE TABLE statement to create a stream_acct table. The subscriber's account ID, acct_id field is the primary key in this table. The fields profile_name, account_expiry store the viewership details, which is a part of fixed schema. The acct_data column is a schema-less JSON field, which stores the details of the shows viewed by a subscriber.

For more details on streaming media service application, see Streaming Media Service - Persistent user profile store

You also specify a TTL value, after which the rows automatically expire and are not available anymore. The TTL value must be in either HOURS or DAYS. In this schema, the rows of the table expire after 5 days.

You can check the hours remaining until a row expires using the remaining_hours functions. For details, see Functions on Rows.

Example 3: Create a streaming media service table with various fixed-schema definitions.

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data RECORD (
firstName STRING,
lastName STRING,
country STRING,
shows JSON
),
PRIMARY KEY(acct_id)
)

In the schema above, you define a variation of the fixed-schema by including the acct_data field as a RECORD data type.

A record is an ordered collection of one or more key-item pairs. The keys in a record must be strings and the associated items can be of different data types. The fields in a record are a part of the fixed-schema and you will not be able to add or remove them. In the example above, the fields firstName, lastName, country, and shows are the keys for the acct_data record. Defining a record is helpful when you want to store data as part of a bigger data set. You can insert/update/fetch the whole subset in a record using the field step expressions.

You can also nest the records as follows:

CREATE TABLE IF NOT EXISTS  stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9), 
acct_data RECORD (
   firstName STRING,
   lastName STRING,
   country STRING,
   shows RECORD (
           showName STRING,
           showId INTEGER,
           type JSON,
           numSeasons INTEGER,
           seriesInfo ARRAY(JSON)
   )
),
PRIMARY KEY(acct_id)
)

The shows field is a nested RECORD type used to track the details of the viewed shows.

Example 4: Create a streaming media service table as a hierarchical table structure.

In the following schemas, you create stream_acct table as a parent table and acct_data table as a child table:

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
PRIMARY KEY(acct_id))

CREATE TABLE IF NOT EXISTS stream_acct.acct_data(
profile_id INTEGER,
user_name STRING,
firstName STRING,
lastName  STRING,
country   STRING,
shows     JSON,
PRIMARY KEY(profile_id))

With the parent-child table definition above, the streaming media service can support multiple user profiles under a single subscription.

You define the acct_data table as a child table with a primary key profile_id to identify a user's profile. In addition to defining a primary key for the table, the acct_data table implicitly inherits the acct_id primary key column of its parent stream_acct table.

You can define multiple child tables under the same stream_acct parent table. You can further define child tables for the acct_data table. All the tables in the hierarchy have the same shard key column, which is specified in the create table statement of the highest parent table in the hierarchy. In this example, the primary key acct_id of the parent table is also the shard key for the stream_acct and acct_data tables.

Example 5: Create a streaming media service table with primary key as an IDENTITY column.

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 4 INCREMENT BY 1 NO CYCLE),
profile_name STRING,
account_expiry TIMESTAMP(9), 
acct_data JSON, 
PRIMARY KEY(acct_id)
)

In this example, you create the stream_acct table with the same fields defined in Example 2. However, the schema defines the primary key acct_id as an IDENTITY column. When you define an IDENTITY column as GENERATED BY DEFAULT AS IDENTITY, the system generates the IDENTITY column values automatically if you do not supply one. The system starts to generate acct_id values from 4 incrementing by 1. So, values for the acct_id column will be 4, 5, 6... and so forth up to the maximum value of the LONG data type. As the schema defines the NO CYCLE option, the system raises an exception after the maximum value as it has reached the end of the sequence generator.

Example 6: Create a streaming media service table with primary key as a UUID data type.

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id STRING AS UUID GENERATED BY DEFAULT, 
profile_name STRING,
account_expiry TIMESTAMP(9), 
acct_data JSON, 
PRIMARY KEY(acct_id)
)

In this example, you create the stream_acct table with the same fields defined in Example 2. However, the schema defines the primary key acct_id as a UUID data type, GENERATED BY DEFAULT. The system automatically generates a value for the UUID column if you do not supply one. The potential advantage of defining primary key as a UUID data type is while the system guarantees the uniqueness of the IDENTITY columns only within a NoSQL data store in a region, UUID data type generates a globally unique identifier for the records in a table that span multiple regions.

MR_COUNTER Data Type

Example 7: Create a streaming media service table with an MR_COUNTER data type.

CREATE TABLE IF NOT EXISTS  stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9), 
firstName STRING,
lastName STRING,
country STRING,
shows JSON (counter as INTEGER MR_COUNTER),
PRIMARY KEY(acct_id)) IN REGIONS DEN,LON

In the schema above, you define the streaming media service table with an additional MR_COUNTER data type in the shows field. The MR_COUNTER data type is a Conflict-free Replicated Data Type (CRDT) counter. CRDTs provide a way for concurrent modifications to be merged across regions without user intervention. In a multi-region setup of an Oracle NoSQL Database, copies of the same data must be stored in multiple regions and data may be concurrently modified in different regions. The MR_COUNTER data type ensures that when data modifications happen simultaneously on different regions, data always gets automatically merged into a consistent state.

In this example, the system creates the table in two regions DEN and LON. Each counter field in the JSON document will track the latest count of the shows the user streams in that region. As all the Multi-Region tables in the participating regions are synchronized, the system automatically merges these concurrent modifications to reflect the latest updates of the counter without any user intervention.

JSON Collection Tables

Example 1: Create a streaming media service table as a JSON collection table.

In the following CREATE TABLE statement, you create the stream_acct table as a JSON collection table:

CREATE TABLE IF NOT EXISTS stream_acct(
acct_id INTEGER, 
PRIMARY KEY(acct_id)) AS JSON COLLECTION

This JSON collection table includes the acct_id as the primary key field. There is no need to supply any other field except the primary key field in the DDL command.

When you insert data into this table, the JSON collection table automatically considers the inserted fields other than the acct_id field to be JSON fields. You can use this JSON collection table to store and retrieve TV streaming data purely as documents.

Example 2: Create a shopping application table as a JSON collection table.

The following CREATE TABLE statement defines a storeAcct table, which is a JSON collection table created for a shopping application. This table includes the contactPhone as the primary key field of the type string.

CREATE TABLE storeAcct(
contactPhone string, 
primary key(contactPhone)) AS JSON COLLECTION

To insert data into the tables, see Inserting, Modifying, and Deleting Data.

Enabling Before-images During Table Creation

The before-image of any write is the table row before it gets updated or deleted by a DML operation. This feature is particularly useful in streams subscriptions wherein, streaming before-images enables applications to compute the change or delta made by write operations to tables in a data store.

The ENABLE BEFORE IMAGE clause enables before-images generation and persistent storage of those images. For more details on using before-images in a subscription stream, see Streams Developer's Guide.

Example: Enable before-images while creating a streaming media service table.

In the following CREATE TABLE statement, you create a stream_acct table with before-images enabled.

CREATE TABLE stream_acct(
acct_id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(9),
acct_data JSON, 
PRIMARY KEY(acct_id)
)USING TTL 5 DAYS ENABLE BEFORE IMAGE USING TTL 48 HOURS

You provide both table TTL and before-images TTL together in the CREATE TABLE statement above. Both TTL values work independently of each other.

The table TTL is set to 5 days and the before-images TTL is set to 48 hours. The generated before-images on the stream_acct table are stored on the disk for 48 hours. After this duration, the before-images expire freeing up the disk space and will not appear in the stream.

Without the TTL definition for before-images, the generated before-images remain for 24 hours, unless adjusted by a TTL value.

To enable before-images generation after the table is created, you must use the ALTER TABLE statement with an ENABLE BEFORE IMAGE clause.