Information Integration

Chapter 2. Federated Databases

(5: NoSQL-MongoDB, JSON format)
SIA & SDBIS
 NoSQL: MongoDB
JSON Document Data Source Access

Web External Data Sources Integration

Federated Database Systems. Concepts
(Review)
● Federation: “disparate data(bases) integration into a unified logical
structure”.
● Federated MDBS
○ MDBS: Multidatabase System: “A distributed DBMS in which each site maintains complete
autonomy”
○ F-MDBS: “... is a cross between distributed DBMS and centralized DBMS; it is a distributed
system for global users and a centralized system for local users”.
● Federated Database: “The sources are independent, but one source can call
on others to supply information.”
Data-based perspective [1, 10..14] (virtual database)
(Review)
Case Study (Review)
● Data source Access Model
● Data Sources ○ External Tables
○ SQL: Oracle DB Database ○ Remote Views
12c/18c/19c/21c, ■ SQL Remote Views
○ SQL: PostgreSQL 9/10/12, ■ XML Remote Views
○ CSV/XLSx: Local FileSystem, Virtual ■ JSON Remote Views
File System (FTP) ■ REST Remote Views
○ XML: Local FileSystem or ○ Local Tables (ETL)
(Web)REST Data Services ● Integration Model
○ JSON: Local FileSystem or ○ Consolidation Views
(Web)REST Data Services ○ Analytical Views
○ NoSQL: MongoDB: JSON Web
Access ● Web Model:
○ ORDS REST Views,
○ APEX Reports and Charts
5
2.2 Architecture and components FDB

● 2.2.1 Federated Database System Concept

● 2.2.2 Data Source Model and Access Components
● 2.2.3 Integration and Analytical Model
● 2.2.4 Integration Web Model

6
2.2.2 Data Source Model and Access Components
● SQL Data Source Access Components
● CSV, XLS Data Source Access Components
● XML, JSON Data Source Access Components
● MongoDB Data Source Access Components

7
(1) NoSQL Data Source Access Components for
MongoDB
● Integration Strategy: REST access to MongoDB collections
● Integration Components
○ REST Service for MongoDB
■ RESTHeart
○ Integration Schema Access Components
■ UTL_HTTP to open and read web data streams.
■ PL/SQL Function to encapsulate UTL_HTTP-API calls
○ SQL View from REST-JSON Remote View
■ querying JSON View as a REST-Resource (data ingestion)
● Using UTL_HTTP PL/SQL library;
● Model matching JSON-to-SQL using JSON_TABLE function.
NoSQL Data Source Access
Prepare RESTHeart service
● Install MongoDB server, running on localhost:27017
○ Use a MongoDB client, e.g. Studio 3T
○ Create a database (e.g. mds) and a collection (e.g. Locations)
● Setup RESTHeart for MongoDB
○ Download and install RESTHeart
○ Configuration (to be included in run command with RHO variable or in config file):
■ Connect to local mongodb)
● /mclient/connection-string->"mongodb://localhost:27017"
■ Map REST.URL endpoint to mongodb database/collection
● /mongo/mongo-mounts -> [ { "what": "/restheart", "where": "/" }, { "what":
"/mds", "where": "/mds" }]
NoSQL Data Source Access
Prepare RESTHeart service
● Setup RESTHeart for MongoDB
○ Run on existing MongoDB
RHO='/mclient/connection-string->"mongodb://localhost:27017";/mongo/mongo-mounts -> [ { "what":
"/restheart", "where": "/" }, { "what": "/mds", "where": "/mds" }];' java -jar restheart.jar

○ Connect: http://localhost:8080/ping or http://localhost:8080/mds/Locations

curl http://admin:secret@localhost:8080/mds/Locations
curl http://admin:secret@localhost:8080/mds/DepartamentsCities
Integration Schema Access Components
PL/SQL Access Function of RESTHeart Service
● UTL_HTTP Call including username/password security string encoded:
CREATE OR REPLACE FUNCTION get_restheart_data_media(pURL VARCHAR2, pUserPass VARCHAR2)
RETURN clob IS
l_req utl_http.req;
l_resp utl_http.resp;
l_buffer clob;
begin
l_req := utl_http.begin_request(pURL);
utl_http.set_header(l_req, 'Authorization', 'Basic ' ||
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_i18n.string_to_raw(pUserPass, 'AL32UTF8'))));
l_resp := utl_http.get_response(l_req);
UTL_HTTP.READ_TEXT(l_resp, l_buffer);
UTL_HTTP.end_response(l_resp);
return l_buffer;
end;
/
--curl http://admin:secret@localhost:8080/mds/Locations
Integration Schema Access Components
SQL Query on PL/SQL Access Function
● UTL_HTTP wrapper function call
○ including username/password security string,
○ with JSON_TABLE function to implement model matching:
with rest_doc as
(select get_restheart_data_media('http://host.docker.internal:8080/mds/Locations',
'admin:secret') doc from dual)
SELECT idDepartament , departamentName, departamentCode, countryName
FROM JSON_TABLE( (select doc from rest_doc) , '$.departaments.departament[*]'
COLUMNS ( idDepartament PATH '$.idDepartament'
, departamentName PATH '$.departamentName'
, departamentCode PATH '$.departamentCode'
, countryName PATH '$.countryName'
)
);
Integration Schema Access Components
Remote MongoDB Collection View
CREATE OR REPLACE VIEW departaments_cities_all_view AS
with json as
(select get_restheart_data_media('http://host.docker.internal:8080/mds/Locations',
'admin:secret') doc from dual)
SELECT * FROM JSON_TABLE( (select doc from json) , '$.departaments.departament[*]'
COLUMNS (
idDepartament PATH '$.idDepartament'
, departamentName PATH '$.departamentName'
, departamentCode PATH '$.departamentCode'
, countryName PATH '$.countryName'
, nested PATH '$.cities.city[*]'
columns (
idCity path '$.idCity' null on error
, cityName path '$.cityName' null on error
, postalCode path '$.postalCode' null on error
)
) );
CASE STUDY: ORCL FDB NoSQL
Mongo Document DB

14
Case Study: MongoDB Remote Views
● SQL Script Example:
○ Data Source definition:
■ 16_DS_MongoDB_JSON_Locations.js
○ Remove Views Definitions:
■ 28_AM_JSON_MongoDB_View.sql
● Links:
○ Fotache M. (2023b), Polyglot Persistence and Big Data, Available on GitHub:
(https://github.com/marinfotache/Polyglot-Persistence-and-Big-Data )
○ RESTHeart for MongoDB
■ Configuration
● https://github.com/SoftInstigate/restheart/tree/master/examples/example-conf-files
■ Run standalone
● https://restheart.org/docs/default-configuration#standalone-configuration
EXTRA
Prepare MongoDB service

● Install MongoDB server

○ Install on MacOS host
○ Install as Docker Container from hub
■ docker pull mongo
■ docker run -p 27017:27017 --name mongodb-6.0 -d mongo:6.0
■ docker exec -it mongodb-6.0 mongosh "mongodb://localhost:27017"
● Connect to MongoDB
○ mongosh "mongodb://localhost:27017"
○ Studio 3T Free
○ JDBC Connect
■ JDBC Driver for maven (JDBC Driver Project)