json2oraparser

The 'json2oraparser' library parses a JSON file (nested upto n-th level) according to the metadata file provided by user and stores the Json data into Oracle database.

Program reads the user given metadata for one time and looks it up while traversing through the Json file level wise (it can well parse a complex Json nested upto n-th level) to build normalized path of each Json key at runtime and extracts all the required database related information like exact table name, column name, datatype by referencing the metadata to generate all insert sql statements which subsequently gets executed in database to finally load Json data into Oracle database.

Pre-Requisite and Assumption :

• Python should be installed in system. The recommended Python version for this library is v2.7. For higher version of Python, updated library version will be released later.
• Oracle client should be available in the system.
• Oracle database, tables, columns related to json data need to be available
• Library supports only the following date/timestamp format from Json file –
  Y-m-dTH:M:S.fZ (e.g. 2018-07-29T17:44:27.633Z), Y-m-dTH:M:S.f,
  Y-m-d H:M:S.f, Y-m-d H.M.S.f, Y-m-d H:M:S, Y-m-d H.M.S, Y-m-d

Installation :

pip install json2oraparser

Usage :

import json2oraparser

metadata = json2oraparser.createMetadata (C:/Event_Metadata.csv)

json2oraparser.loadJson (C:/Event2019.json, metadata, 111.11.11.11, 1111, EVNT, EVNT_USR, EvntPassword@1)

Overview :

The entire project operation is a 2 step process –

• STEP 1: METADATA CREATION :

  The sample code piece related to this step is -
  
  metadata = json2oraparser.createMetadata (C:/Event_Metadata.csv)
  

json2oraparser.createMetadata() :

This function will take a csv file (with absolute file path) as input and produces a list of metadata built according to the CSV. This CSV file template ( 'Metadata_Blank_Template.csv' ) is provided in the 'METADATA' folder which will be available after library installation. Please note, all the column names in the CSV should match with template file.

According to the Json file, user needs to create the CSV file which will contain information about different Json entity and attribute names and their corresponding database table and column details where the Json fields will be stored.

A sample Json data file - Sample_Json_File.json - is given for your reference in 'METADATA' folder.

A sample metadata file - Sample_Metadata_with remarks.csv - has been built (available in 'METADATA' folder) as per the Sample Json (with relevant explanation/remarks in '_Remarks_' column). Please note, '_Remarks_' column is added for your reference only, it should NOT be part of the metadata CSV file.

Metadata CSV File Description :

Metadata CSV file preparation with proper information and correct format is the backbone of this library's successful execution. The detailed description of different columns in the metadata CSV file as well as the instruction for filling up each of those columns have been given in the file ' Metadata_CSV_Preparation_Guide.txt' within 'METADATA' folder. The same description is also given below -

FIELD_ID : Unique identifier and serial number for each row in the csv.

ENTITY_NAME : Node names (e.g. object/list) from Json file.

ATTRIBUTE_NAME : Key names from Json file.

NODE_LEVEL : Level of any node in json file. Starting node level is denoted as '1', subsequent child level will be continued as 2, 3, 4... In the sample Json, the starting node name is 'event'.

TABLE_NAME : Database table name where the entity will be stored. This can be filled up either by table synonyms (e.g. T_RL_RE) or by prefixing schema name [SchemaName].[TableName] (e.g. MARKET.OVR). Typically one json ENTITY_NAME corresponds to one database TABLE_NAME. If you don't want to load an entire node's data into its corresponding database table, mark the TABLE_NAME as 'DUMMY' in CSV instead of leaving the field as blank.

COLUMN_NAME : Database table name where the attribute value will be stored. This basically represents the granularity of the entire metadata.

PARENT_NODE : Immediate parent node of any node in Json file. It is filled up as [parent ENTITY_NAME] | [starting FIELD_ID of that parent node] (e.g. Abs|7).

NODE_PATH : This field needs to be used for all node level of the json. For Level 1, starting node name from the json of this level should be given as NODE_PATH. For remaining levels, there is no need to fill up this column.

ROOT_FLAG : Starting ROOT_FLAG of each node will be 1, for other attributes of that node ROOT_FLAG = 0.

CURRENT_IND : This field must be filled up as 'Y' for loading any column in database. In case, you don't want to load any particular column of a table (even though it's corresponding attribute is present in Json), fill it up as 'N'.

LOGICAL_DATATYPE : Datatype of the database column where you want to store json attribute value.

PARENT_COLUMN : If you want to load a particular column of a node with the value of any attribute of immediate parent node then PARENT_COLUMN field needs to be used. Please note, the entry in PARENT_COLUMN field in CSV must exist in Json and should belong to immediate parent node of the current entity.

• STEP 2: LOAD JSON TO ORACLE DATABASE :

  The sample code piece related to this step is -
  
  json2oraparser.loadJson (C:/Event2019.json, metadata, 111.11.11.11, 1111, EVNT, EVNT_USR, EvntPassword@1)
  

json2oraparser.loadJson() :

User needs to provide the following parameters to this function as per the below sequence to load a Json file's data into Oracle database -

• A valid Json file (with absolute file path)
• Metadata variable created in STEP 1
• Oracle database 'Hostname'
• Oracle database 'Port'
• Oracle database 'SID' or 'Service name'
• Oracle database 'Username'
• Oracle database 'Password'

Contact Info :

For any query/clarification/issue regarding the 'json2oraparser' library, please mail to ntpythondev@gmail.com .