Ingest JSON files in OCI NoSQL created in an OCI compartment
Introduction
To provide a unique customer experience, many modern applications require low latency and the capability to ingest huge volumes of data. At the same time, applications need to maintain high availability and zero fault tolerance.
Oracle NoSQL Database Cloud Service, a fully managed database service, is increasingly popular for modern applications because of the aforementioned requirements. It provides developers a way to build applications using documents, columnar, and key-value database models, delivering predictable single-digit millisecond response times with data replication for high availability. The service offers ACID transactions, serverless scaling, comprehensive security, and low pay-per-use pricing for both on-demand and provisioned capacity modes.
In this article, I would like to discuss how to ingest multiple JSON files in OCI NoSQL table. This can be achieved using various SDKs. In this context, I would like to discuss how this can be done in a table that is created in an OCI compartment using python SDK.
Prerequisite: To do this exercise you’ll need to have an OCI Cloud Free Tier account, available here: OCI Cloud Free Tier.
We can do this in 3 steps:
Step#1: Creating Environment
A. Create one OCI compartment. In case, you are not familiar with OCI compartment, please refer How to Create a Compartment in OCI — YouTube.
B. Create OCI compute machine. For more details, please refer Create a Linux Instance on Oracle Cloud Infrastructure.
C. Install Python in OCI compute.
sudo yum install -y python3Alternatively, python can be invoked from local machine as well.
D. Configure OCICLI. Refer to the Quickstart.
Step#2: Preparing Data
For our demo, we have created 2 sample JSON files.
A. JSON files are created in OCI compute /home/opc/sample path using vi editor.
First JSON file: /home/opc/sample/bakeryproduct_01.json
{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Strawberry" }
]
,
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
}Second JSON file: /home/opc/sample/bakeryproduct_02.json
{
"id": "0002",
"type": "donut",
"name": "Raised",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" }
]
,
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
}Step#3: Loading Data
A. Collect the OCI compartment information: Need to collect the OCID of the compartment where we want to create the table.
Login to OCI cloud portal and navigate to compartment:
B. Create a python file named loadjsondata.py to create the table and load the data. This part of the code would be used to connect to the compartment.
import sys
import os
import json
import globfrom borneo import (
AuthorizationProvider, DeleteRequest, GetRequest,
IllegalArgumentException, NoSQLHandle, NoSQLHandleConfig, PutRequest,
QueryRequest, Regions, TableLimits, TableRequest)
from borneo.iam import SignatureProvider
from borneo.kv import StoreAccessTokenProvider
from oci.config import from_file#
# EDIT: these values based on desired region and/or endpoint for a local
# server
#
cloud_region = None# Cloud Service Only
#
# EDIT: set these variables to the credentials to use if you are not using
# a configuration file in ~/.oci/config
# Use of these credentials vs a file is determined by a value of tenancy
# other than None.
#
tenancy = None # tenancy'd OCID (string)
user = None # user's OCID (string)
private_key = 'path-to-private-key-or-private-key-content'
fingerprint = 'fingerprint for uploaded public key'
# pass phrase (string) for private key, or None if not set
pass_phrase = Noneclass CloudsimAuthorizationProvider(AuthorizationProvider):
"""
Cloud Simulator Only.This class is used as an AuthorizationProvider when using the Cloud
Simulator, which has no security configuration. It accepts a string
tenant_id that is used as a simple namespace for tables.
"""def __init__(self, tenant_id):
super(CloudsimAuthorizationProvider, self).__init__()
self._tenant_id = tenant_iddef close(self):
passdef get_authorization_string(self, request=None):
return 'Bearer ' + self._tenant_iddef get_handle(nosql_env, nosql_compartment):
"""
Returns a NoSQLHandle based on the requested environment. The
differences among the supported environments are encapsulated in this
method.
"""
if nosql_env == 'cloud':
endpoint = cloud_region
#
# Get credentials using SignatureProvider. SignatureProvider has
# several ways to accept credentials. See the documentation:
# https://nosql-python-sdk.readthedocs.io/en/stable/api/borneo.iam.SignatureProvider.html
#
if tenancy is not None:
print('Using directly provided credentials')
#
# Credentials are provided directly
#
provider = SignatureProvider(tenant_id=tenancy,
user_id=user,
fingerprint=fingerprint,
private_key=private_key,
pass_phrase=pass_phrase)
else:
#
# Credentials will come from a file.
#
# By default the file is ~/.oci/config. A config_file = <path>
# argument can be passed to specify a different file.
#
print('Using credentials and DEFAULT profile from ' +
'~/.oci/config')
provider = SignatureProvider()
oci_config = from_file()endpoint= eval('Regions.'+str.upper(oci_config.get('region')).replace('-','_'))else:
raise IllegalArgumentException('Unknown environment: ' + nosql_env)return NoSQLHandle(NoSQLHandleConfig(endpoint, provider).set_default_compartment(nosql_compartment))
def main():table_name = 'Bakeryproduct'
data=''if len(sys.argv) != 3:
print('Usage: python createtable.py cloud compartment_OCID')
raise SystemExitnosql_env = sys.argv[1:][0]
nosql_compartment= sys.argv[2:][0]
print('Using compartment: ' + str(nosql_compartment))
print('Using environment: ' + str(nosql_env))
C. The following code would be used to create a table and load multiple JSON files.
handle = None
try:#
# Create a handle
#
handle = get_handle(nosql_env, nosql_compartment )#
# Create a table
#
statement = (
'CREATE TABLE if not exists {}( id INTEGER, type STRING, name STRING, ppu Double, batters JSON, PRIMARY KEY ( id ) )').format(table_name)
request = TableRequest().set_statement(statement).set_table_limits(
TableLimits(30, 10, 1))
handle.do_table_request(request, 50000, 3000)
print('Table is created')
# Put a few rows
#
#
path_to_json = '/home/opc/sample/'
json_files = ''
json_files = os.path.join(path_to_json, '*.json')
file_list = glob.glob(json_files)
for file in file_list:
with open(file) as jfile:
data = json.load(jfile)
request = PutRequest().set_table_name(table_name)
request.set_value(data)
handle.put(request)print('Data load is complete')
except Exception as e:
print(e)
finally:
# If the handle isn't closed Python will not exit properly
if handle is not None:
handle.close()if __name__ == '__main__':
main()
The entire code can be executed with the compartment OCID we created before.
python3 loadjsondata.py cloud <OCI compartment OCID>sample output:
Using compartment: ****************************
Using environment: cloud
Using credentials and DEFAULT profile from ~/.oci/config
Table is created
Data load is complete
D. Validating the Table
Navigate to Database in the cloud portal to find Tables.
Table is created with JSON datatype.
Let’s query the data from console and see the result!
Conclusion
Of course, you can use any other SDKs of your choice like Java, Go, etc. to perform the regular operations in Oracle NoSQL database cloud service.
Appendix:
Entire code snippets are given below
Want to discuss further? Join us in the Oracle Developer Public Slack!
