DBMS_JSON_DUALITY

106 DBMS_JSON_DUALITY

The package DBMS_JSON_DUALITY simplifies the creation and access of JSON-relational duality views. Given existing set of JSON documents, a corresponding relational schema and JSON-relational duality views are automatically created. In addition, the data can also be automatically imported from the JSON collection. The package also provides functions for non-interactive transactions.

This chapter contains the following topics:

106.1 DBMS_JSON_DUALITY Overview

Package DBMS_JSON_DUALITY provides subprograms for converting JSON collections to JSON-relational duality views. The package also provides subprograms for importing data into the duality views.

JSON documents are imported from document databases into a set of tables with a single JSON column. This can be done using existing tools provided by Oracle and document Databases. Following list points out to the appropriate sections that helps in loading JSON data and migrating data from MongoDB to Oracle Database:

The DBMS_JSON_DUALITY package contains a set of procedures and functions that allows you to find, create relational schema, import JSON documents to duality views and perform non-interactive transactions on the imported duality views.

JSON-To-Duality Converter : Execute one of the appropriate JSON-To-Duality Migrator PL/SQL functions listed below to find and create the relational schema for the input collections (JSON tables).
- INFER_SCHEMA : Infers the JSON schema that represents all of the input document sets.
- GENERATE_SCHEMA: Produces the code to create the required database objects for each duality view.
- INFER_AND_GENERATE_SCHEMA: performs both operations of INFER_SCHEMA and GENERATE_SCHEMA.
JSON-To-Duality Importer :
This feature is a PL/SQL procedure IMPORT to import application data from sets of JSON documents into duality views defined using the JSON-To-Duality Converter feature. Based on the relational schema created by the JSON-To-Duality Converter, input data gets decomposed and normalized into relational tables automatically. Any data that cannot be successfully imported is logged in an error log with the reason for the error and suggestions to fix the issue, if possible.

Note:
Use the importer feature only when you are satisfied with the relational schema recommended by the JSON Relational Duality Converter feature.
Non-Interactive Transactions
This feature is a set of PL/SQL procedures that allows you to perform a multi-operational transaction on duality views. The following are the non-interactive procedures from package DBMS_JSON_DUALITY:
- BEGIN_TRANSACTION : Begins a multi-operational transaction.
- REGISTER: Check that the ETAG value of a document as last read matches that of the document in the database at the start of the transaction.
- COMMIT_TRANSACTION: Commits a multi-operational transaction.

106.2 DBMS_JSON_DUALITY Security

This section lists the security restrictions related to DBMS_JSON_DUALITY package.

In terms of security, JSON-To-Duality Migrator has the following restrictions:

It can not be run in SYS schema.
The migrator requires "select any table" privilege if targetSchema is not current schema. Otherwise, duality view creation fails while attempting to access the tables in targetSchema.
Example:
```
grant select any table to targetSchema;
```

106.3 Summary of DBMS_JSON_Duality Subprograms

This table lists the DBMS_JSON_DUALITY subprograms and briefly describes them.

DBMS_JSON_DUALITY Package Subprograms

Subprogram	Description
INFER_SCHEMA Function	Takes as input a set of JSON collections (tables with a single JSON column) and configuration parameters formatted as a JSON document. Returns the validated and normalized relational schema representing the collections as a JSON document
GENERATE_SCHEMA Function	Takes as input a normalized relational schema represented as a JSON document and returns the DDL script to generate the schema.
INFER_AND_GENERATE_SCHEMA Function	Takes as input a set of JSON collections (tables with a single JSON column) and configuration parameters formatted as a JSON document. Returns the DDL script to generate the validated and normalized relational schema representing the collections as a CLOB.
IMPORT Procedure	Imports data into the tables underlying the duality view(s). Logs any documents that fail import into an error log.
BEGIN_TRANSACTION Procedure	Starts a special multi-op transaction.
COMMIT_TRANSACTION Procedure	Commits the multi-op transaction.
REGISTER Procedure	Registers each document to be modified in the transaction, ensuring that the document has not been modified since the last read.

106.3.1 INFER_SCHEMA Function

This section describes the syntax, input and output formats of the INFER_SCHEMA function.

Overview

This function takes tables with a single JSON column and their configuration parameters formatted as a JSON document as input. The input fields of the JSON configuration document are described in the section Input Configuration Parameters. It returns the validated and normalized relational schema representing the collections as a JSON document.

Syntax

FUNCTION INFER_SCHEMA(config in JSON)
RETURN JSON;

Example 106-1 Example for INFER_SCHEMA Function

schema_sql :=
    dbms_json_duality.infer_schema(
    json('
{
  "tableNames"           : ["EMP", "DEPT"],
  "viewNames"            : ["EMPLOYEES", "DEPARTMENTS"],
  "useFlexFields"        : true,
  "updatability"         : true,
  "sourceSchema"         : "HR",
  "targetSchema"         : "HR",
  "tablespace"           : "HR_TBS",
  "ingestLimit"          : 100000,
  "outputFormat"         : "executable",
  "minFrequency"         : 10,
  "softnessThreshold"    : 98
}'));
  
   dbms_output.put_line(schema_sql);

Input Configuration Fields

Parameter	Value Type
tableNames	JSON array of strings of the table names of JSON collection to be processed
viewNames	JSON array of strings of the output duality view names to be generated
useFlexFields	Boolean parameter which indicates whether flex fields should be used in the duality views. When `true`, for each duality view, a flex column is added to each table that directly underlies the top-level fields of an object in the supported documents. Default value for this parameter is `true`.
updatability	Boolean parameter which indicates whether the duality views created by the migrator should be updatable by default. When `true`, the migrator will mark all duality views as updatable (i.e., annotations will be set for maximum updatability of the duality view). When `false`, all duality views created will be read-only. Default value for this parameter is `true`.
sourceSchema	This string parameter defines the schema name for the input tables. Default value for this parameter is the current user schema.
targetSchema	This string parameter defines the schema name for the output tables and duality views. There is no default value for this parameter. If this parameter is not specified, a string whose value is the name of the database schema that will own the output database views."
tablespace	This string parameter defines the `tablespace` name to use for all tables created by the migrator. This parameter has no default. If this parameter is not specified, then the `tablespace` for tables created by the migrator will remain unspecified.
ingestLimit	This numeric parameter defines the maximum number of documents that will be analyzed in each JSON collection to infer the relational schema. The default value of this parameter is 100000.
outputFormat	This string parameter defines the format of the output DDL script. The possible values for this parameter are "standalone" and "executable". The "executable" format creates a DDL script output which can be directly executed using the "execute immediate" PL/SQL construct. The "standalone" format creates a DDL script which needs to be copied into a SQL file and executed independently. It cannot be executed using the "execute immediate" PL/SQL construct. The default value of this parameter is "executable". Note: For cases where the output DDL statements can be larger than 32k characters, the user MUST use the "standalone" option.
minFrequency	This numeric parameter defines the threshold in percentage for identifying high entropy fields. The default value for this parameter is 5, meaning that fields which appear in less than 5% of documents in a collection will be pruned from the schema.
softnessThreshold	This numeric parameter defines the threshold in percentage for allowable 'softness' in identifying functional dependencies in the input data. The default value of this parameter is 99, meaning 1% of data could have missing or incorrect information. The value of this parameter can be set based on the 'dirtiness' of input data. The dirtier (containing more errors) the data, the smaller the value of this parameter should be.

106.3.2 GENERATE_SCHEMA Function

This section describes the syntax, input and output formats of the GENERATE_SCHEMA function.

Overview

This function takes a normalized relational schema represented as a JSON document as input and returns the DDL script to generate the schema.

Syntax

FUNCTION GENERATE_SCHEMA(er_schema in JSON)
  RETURN CLOB;

Example 106-2 Example for GENERATE_SCHEMA Function


schema_example :=dbms_json_duality.generate_schema(schema_sql);
   dbms_output.put_line(schema_example);

Note:

The schema_sql is the output from INFER_SCHEMA function. The details of INFER_SCHEMA function is available here.

106.3.3 INFER_AND_GENERATE_SCHEMA Function

This section describes the syntax, input and output formats of the INFER_AND_GENERATE_SCHEMA function.

Overview

This function takes a set of JSON collections (tables with a single JSON column) and configuration parameters formatted as a JSON document as input. The input fields of the JSON configuration document are identical to the INFER_SCHEMA function and listed here. It returns the DDL script to generate the validated and normalized relational schema representing the collections as a CLOB.

Syntax

FUNCTION INFER_AND_GENERATE_SCHEMA(config in JSON)
  RETURN CLOB;

Example 106-3 Example for INFER_AND_GENERATE_SCHEMA Function


schema_sql :=dbms_json_duality.infer_and_generate_schema(json('
{
  "tableNames"           : ["EMP", "DEPT"],
  "viewNames"            : ["EMPLOYEES", "DEPARTMENTS"],
  "useFlexFields"        : true,
  "updatability"         : true,
  "sourceSchema"         : "HR",
  "targetSchema"         : "HR",
  "tablespace"           : "HR_TBS",
  "ingestLimit"          : 100000,
  "outputFormat"         : "executable",
  "minFrequency"         : 10,
  "softnessThreshold"    : 98
}'));

dbms_output.put_line(schema_sql);

106.3.4 IMPORT Procedure

This section describes the syntax, input and output formats of the IMPORT procedure.

Overview

This procedure imports data from a table with a single JSON column into a Duality View. It also logs any documents that fail import into an error log.

Note:

In the process of normalization, data may be transformed, cast to different data types, and truncated to honor maximum size limits. In addition, any data not conforming to the resulting schema may be rejected during the import phase. Oracle recommends to verify that all data has been successfully imported by running verification tests and looking at the error log for resolving import errors. An easy way to ensure imported data is valid is by running a json_diff between the input collection and output duality view and checking that the duality view data only contains additional fields and no fields have been removed or modified.

select json_diff(input_collection.data, output_duality_view.data) from 
input_collection c, output_duality_view d where c.data.pk = d.data.pk;

Syntax

PROCEDURE IMPORT(table_owner_name   in VARCHAR2 DEFAULT NULL,
                 table_name         in VARCHAR2,
                 view_owner_name    in VARCHAR2 DEFAULT NULL,
                 view_name          in VARCHAR2,
                 err_log_owner_name in VARCHAR2 DEFAULT NULL,
                 err_log_name       in VARCHAR2 DEFAULT NULL,
                 reject_limit       in NUMBER   DEFAULT NULL);

Input Parameter Fields

Parameter Value Type

table_owner_name

The name of the owner of the input table with a single JSON column. You can specify the owner in table_name. Otherwise, the schema of the current connected user is used.

table_name

The name of the input table with a single JSON column.

view_owner_name

The name of the owner of the output duality view. You can specify the owner in veiw_name. Otherwise, the schema of the current connected user is used

view_name

The name of the output duality view.

err_log_owner_name

The name of the owner of the error log to use for document that cannot be imported successfully. You can specify the owner in err_log_name. Otherwise, the schema of the current connected user is used.

err_log_name

The name of the error log to use.The default value is NULL, so no errors will be logged by default.

reject_limit

Specifies the maximum number of errors that can be logged before the operation is aborted. The default (NULL) is unlimited, so the operation will never be aborted by default when error_log_name is specified.

Example 106-4 Example for IMPORT Procedure

exec dbms_errorlog.create_error_log(dml_table_name => 'COLLECTION_DUALITY', 
err_log_table_name => 'ERR_LOG', skip_unsupported => TRUE);
exec dbms_json_duality.import(table_name => 'COLLECTION', view_name => 'COLLECTION_DUALITY', 
err_log_name => 'ERR_LOG');

106.3.5 BEGIN_TRANSACTION Procedure

This section describes the syntax of the non-interactive procedure named BEGIN_TRANSACTION

Overview

The typical work-flow for a non-interactive transaction involves the following procedures in the below-mentioned sequence:

Begin the transaction
Register the documents
Perform queries or DMLs on the duality views
Commit the transaction

The BEGIN_TRANSACTION procedure starts a special multi-operation transaction with the following properties:

The multi-operation transaction provides repeatable reads, i.e., all reads run as of the snapshot when the transaction began.
Locks are only taken for rows that are modified, unmodified rows remain unlocked throughout the transaction

Syntax

 procedure begin_transaction;

Example 106-5 Example for BEGIN_TRANSACTION Procedure

dbms_json_duality.begin_transaction()

Note:

The BEGIN_TRANSACTION procedure is normally followed by REGISTER procedure. The detailed information about REGISTER procedure can be found here.

106.3.6 COMMIT_TRANSACTION Procedure

This section describes the syntax of the non-interactive procedure named COMMIT_TRANSACTION

Overview

This procedure commits the special multi-operation transaction. It validates all modified documents in the transaction, i.e., ensures that no other session has modified these documents.

Syntax

procedure commit_transaction;

Example 106-6 Example for COMMIT_TRANSACTION Procedure

COMMIT_TRANSACTION procedure is normally the last step in the non-interactive transaction after queries are performed on the duality views.

dbms_json_duality.commit_transaction();

106.3.7 REGISTER Procedure

This section describes the syntax of the non-interactive procedure named REGISTER

Overview

This procedure verifies that the current etag of the object is unchanged from a prior read. If the etag doesn’t match the expected etag, and error is thrown from this function. The register procedure relies on object ids (oid) to identify objects/documents to register and validate. The object id can be obtained by querying the resid hidden column from the duality view. Its value is simply a concatenated binary encoding of all the primary key columns of the root table in the duality view.

Syntax

 procedure register(view_name in VARCHAR2,  /* duality view name */                    
                    oid       in RAW,       /* duality view obj identifier */                    
                    etag      in RAW);      /* document etag */

Example 106-7 Example for REGISTER Procedure

This example assumes that a duality view team_dv is already created. The detailed information about creating the duality views are explained in Creating Duality Views topic in the Overview of JSON-Relational Duality Views. Obtaining the RESID and ETAG is mandatory step prior to calling the REGISTER procedure. The following shows an example code to obtain the RESID and ETAG of the duality view team_dv:

SELECT RESID, DATA FROM team_dv dv
  WHERE dv.DATA.name LIKE 'Mercedes%';

This produces an output that includes the oid (RESID) and the ETAG information as shown below:


RESID
-----
DATA
----
FB03C2040400
{"_id" : 303,
 "_metadata":
  {"etag" : "039A7874ACEE6B6709E06E42E4DC6355",
   "asof" : "00000000001BE239"},
 "name" : "Mercedes",
 ...}

Note:

REGISTER procedure is normally performed after executing the BEGIN_TRANSACTION procedure. The details of BEGIN_TRANSACTION procedure is provided here.


BEGIN
  DBMS_JSON_DUALITY.begin_transaction();
  DBMS_JSON_DUALITY.register('team_dv',
                             hextoraw('FB03C2040400'),
                             hextoraw('039A7874ACEE6B6709E06E42E4DC6355'));
  .............................................
  ****Perform the updating (DML) operations****
  .............................................

  DBMS_JSON_DUALITY.commit_transaction();
END