Modelling and Design - Alter table AND complex types

[dario-vega]

🧑‍💻 Schema flexibility in Oracle NoSQL Database mostly takes the form of non-scalar data types. These non-scalar data types can be used to embed flexible structures inside your tables (JSON, Records, Arrays). If 100's of million records is a requirement for you then you'll most likely want to trade off flexibility for storage efficiency and consider using a fixed schema.

Here is an example to evolve your schema if you have decided to use fixed schema instead of purely JSON document style.

CREATE TABLE foo(
   id INTEGER, 
   r RECORD(ri INTEGER, rs STRING), 
   m MAP(RECORD(mi INTEGER, ms STRING)), 
   a ARRAY(RECORD(ai INTEGER, as STRING))
   , PRIMARY KEY(id)
)

ALTER TABLE foo(
   ADD r.rb BOOLEAN, 
   ADD m.values().mb BOOLEAN, 
   ADD a[].ab BOOLEAN
)

sql-> show ddl foo

CREATE TABLE IF NOT EXISTS foo (
id INTEGER, 
r RECORD(ri INTEGER, rs STRING, rb BOOLEAN), 
m MAP(RECORD(mi INTEGER, ms STRING, mb BOOLEAN)), 
a ARRAY(RECORD(ai INTEGER, as STRING, ab BOOLEAN)), 
PRIMARY KEY(SHARD(id)))

learn more about NoSQL Modelling and Design

[dario-vega]

If using Terraform, you will use the final create-table statement in the Terraform config to apply changes. The NoSQL service will identify schema changes and apply an alter-table to the table accordingly.

provider "oci" {}
resource "oci_nosql_table" "foo" {
	compartment_id = "ocid1.compartment.oc1..aaaaaaaa4mlehopmvdluv2wjcdp4tnh2ypjz3nhhpahb4ss7yvxaa3be3diq"
	ddl_statement = "CREATE TABLE IF NOT EXISTS foo (id INTEGER, r RECORD(ri INTEGER, rs STRING, rb BOOLEAN), m MAP(RECORD(mi INTEGER, ms STRING, mb BOOLEAN)), a ARRAY(RECORD(ai INTEGER, as STRING, ab BOOLEAN)), PRIMARY KEY(SHARD(id)))"
	name = "foo"
	table_limits {
		capacity_mode = "PROVISIONED"
		max_read_units = "50"
		max_storage_in_gbs = "50"
		max_write_units = "1"
	}
}

Terraform will perform the following actions:

  # oci_nosql_table.foo will be updated in-place

  ~ resource "oci_nosql_table" "foo" {

      ~ ddl_statement       = "create table foo(id integer, r record(ri integer, rs string), m map(record(mi integer, ms string)), a array(record(ai integer, as string)), primary key(id))"" -> ""CREATE TABLE IF NOT EXISTS foo (id INTEGER, r RECORD(ri INTEGER, rs STRING, rb BOOLEAN), m MAP(RECORD(mi INTEGER, ms STRING, mb BOOLEAN)), a ARRAY(RECORD(ai INTEGER, as STRING, ab BOOLEAN)), PRIMARY KEY(SHARD(id)))"

        id                  = "ocid1.nosqltable.oc1.iad.amaaaaaaknuwtjian42ktwjkad..."

        name                = "foo"

        # (10 unchanged attributes hidden)

        # (1 unchanged block hidden)

    }

Plan: 0 to add, 1 to change, 0 to destroy.

oci_nosql_table.foo: Modifying... [id=ocid1.nosqltable.oc1.iad.amaaaaaaknuwtjian42ktwjkadkofi4y53kihihy5yeqhw5p56fmmjs7ob7a]

oci_nosql_table.foo: Modifications complete after 10s [id=ocid1.nosqltable.oc1.iad.amaaaaaaknuwtjian42ktwjkad....]

Apply complete! Resources: 0 added, 1 changed, 0 destroyed.