on - edited on by
DRay
This post describes methods of importing JSON data into Sisense ElastiCubes.
JSON is a file format consisting of arrays of key-value pairs wrapped in braces ("{}"). It is increasingly common with the popularity of NoSQL databases, and it is the typical output format of REST APIs (including Sisense's). More information about the JSON format and examples of its structure are available here:
Sisense does not have a native JSON connector, so customers will need to employ indirect methods to import JSON files into ElastiCubes.
Workflow 1
This method leverages Python to load your JSON data into MongoDB (a NoSQL database program). Once the data is inserted into MongoDB, it can be imported into Sisense using Sisense's MongoDB ODBC driver.
The process flow of data is outlined in the graphic below:
Steps
Step 1 – Install Python
Download and install Python 2.7 here:
Afterwards, install PyMongo using pip (Python's package manager):
C:\Python27>cd C:\python27\scriptsC:\Python27\Scripts>pip install pymongo
C:\Python27>cd C:\python27\scripts
C:\Python27\Scripts>pip install pymongo
C:\Python27>cd C:\python27\scripts
C:\Python27\Scripts>pip install pymongo
Step 2 – Change the settings in the script
The below script will parse a JSON file and insert it into a new database and collection in MongoDB. If you have installed Sisense, an instance of MongoDB already exists on your machine.
There are a configuration fields in the script that must be updated:
mongoServer: The server where the MongoDB instance is installed. Use "localhost" if you are running the script on the Sisense machine.
mongoPort: The port being used for the MongoDB instance. The default MongoDB port within Sisense is "27018".
mongoUser: The user type you are accessing the MongoDB with. "WriteUser" by default. "ReadUser" does not have sufficient permissions to insert the documents.
mongoPassword: The password used to access the MongoDB. It needs to be set using the Sisense REST API before running the script. Information on setting the password is here: https://docs.sisense.com/main/SisenseLinux/connecting-to-mongodb.htm
mongoDB: The name of the database you want to insert the data into. If the database does not exist, it will be created.
collectionName: The name of the collection you want to insert the data into. If the collection does not exist, it will be created.
importType: The type of import you want to achieve. "Insert" will append the JSON input as new documents in your collection. "Replace" will drop the entire collection and replace it.
sourceFile: The file path of the .json file you will be loading into MongoDB.
Step 3 – Run the script
Running the attached Python script can be done a number of ways:
1. Through the Windows Command prompt (navigate to Python.exe, then point to the .py file)
For example: cd C:\Python27\python.exe C:/MyScriptDirectory\json_to_mongo.py
2. Scheduled with a .bat file and Windows Task Scheduler
General information about this kind of process: https://community.sisense.com/t5/knowledge/schedule-sequential-elasticube-builds-using-windows-task/...
3. As a standalone script in your favorite IDE
Step 4 – Connect the ElastiCube to MongoDB
The following documentation page covers how to download the Sisense MongoDB ODBC driver, create a DSN, and import data from MongoDB: https://docs.sisense.com/main/SisenseLinux/connecting-to-mongodb.htm
Download:
# Imports
from pymongo import MongoClient
import datetime
import json
# MongoDB Settings
mongoServer = 'localhost'
mongoPort = '27018'
mongoUser = 'WriteUser'
mongoPassword = 'MyPassword' # https://documentation.sisense.com/accessing-sisense-application-database/
client = MongoClient(mongoServer + ':' + mongoPort,
username=mongoUser,
password=mongoPassword,
authMechanism='SCRAM-SHA-1')
mongoDB = 'MyDatabase' # Case Sensitive (if no DB exists, it will be created)
collectionName = 'MyCollection' # Case Sensitive (if no collection exists, it will be created)
importType = 'Replace' # Use 'Replace' to overwrite the existing collection; Insert to append data
# Source Info
sourceFile = open("C:\\JSONFiles\\MyFile.json", 'r') # Must be a JSON file
db = client[mongoDB]
collection = db[collectionName]
parsed = json.loads(sourceFile.read())
def main():
if importType == "Insert":
if len(parsed) > 1:
collection.insert_many(parsed)
if len(parsed) == 1:
collection.insert_one(parsed)
if importType == "Replace":
db.drop_collection(collectionName)
if len(parsed) > 1:
collection.insert_many(parsed)
if len(parsed) == 1:
collection.insert_one(parsed)
main()
print str(len(parsed)) + " records inserted into " + mongoDB + " database, " + collectionName + " collection"Workflow 2
This method uses a small Python script and the Pandas package to convert the JSON file to CSV. It outputs the CSV file to directory on the server, which Sisense can natively import.
Steps
Step 1 – Install Python
Download and install Python 2.7 here:
The script uses the Pandas package (https://pandas.pydata.org/) to convert the JSON object to CSV. You can use pip (Python's package manager) to install it:
Step 2 – Change the settings in the script
These are the configuration settings in the script that must be updated:
source_file: The path to the JSON file to be imported (for example, "C:\\JSONFiles\\MyFile.json")
output_directory: The path you want to export the CSV output to (for example, "C:\\Users\\Administrator\\Desktop")
csv_name: The name of the CSV file output (for example, "MyCSVFile.csv")
import_method: The type of import you want to achieve. "Insert" will append your data to the existing CSV file. "Replace" will overwrite the existing CSV file with your records.
Step 3 – Run the script
Running the attached Python script can be done a number of ways:
1. Through the Windows Command prompt (navigate to Python.exe, then point to the .py file)
For example: cd C:\Python27\python.exe C:/MyScriptDirectory\json_to_csv.py
2. Scheduled with a .bat file and Windows Task Scheduler
General information about this kind of process: https://community.sisense.com/t5/knowledge/schedule-sequential-elasticube-builds-using-windows-task/...
3. As a standalone script in your favorite IDE
Step 4 – Connect the ElastiCube to your CSV file
The following documentation page covers how to import CSV files into Sisense: https://docs.sisense.com/main/SisenseLinux/connecting-to-csv.htm
Download:
import pandas as pd
import os
# Configuration
source_file = "C:\\JSONFiles\\MyFile.json" # Path to the JSON file to be converted
output_directory = "C:\\CSVFiles\\" # Path to the directory to create the CSV file in
csv_name = "CSVOutput.csv" # Name of the CSV file to be created
import_method = 'Replace' # Use 'Replace' to overwrite the existing file; Use 'Insert' to append to the existing file
df_json = pd.read_json(source_file)
write_path = output_directory + '\\' + csv_name
if not os.path.exists(output_directory):
os.mkdir(output_directory)
def main():
if import_method == 'Replace':
df_json.to_csv(write_path, mode='w', index=False)
print str(len(df_json)) + " records written to " + write_path
if import_method == 'Insert':
if not os.path.exists(write_path):
df_json.to_csv(write_path, mode='a', index=False, header=True)
print str(len(df_json)) + " records written to " + write_path
else:
df_json.to_csv(write_path, mode='a', index=False, header=False)
print str(len(df_json)) + " records appended to " + write_path
main()General Notes and Caveats:
- Sisense introduced MongoDB authentication in version 6.7. Earlier versions of Sisense do not need the authentication parameters in the script
- MongoDB places some restrictions on special characters in keys ($.)
- JSON files can be nested, and their structure can be much more complex than traditional RDBMS tables. A more robust, custom-designed ETL script is likely required for large complex JSON structures.
