SOLUTION BRIEF

SQL Abstraction for NoSQL
Databases

SOLUTION BRIEF

SQL FOR NOSQL DATA | 1

SQL Abstraction for NoSQL
Databases
The growth of NoSQL continues to accelerate as the industry is increasingly forced to develop new
and more specialized data structures to deal with the explosion of application and device data. At thesame time, new data products for BI, Analytics, Reporting, Data Warehousing, AI, and Machine
Learning continue along a similar growth trajectory. Enabling interoperability between applications
and data sources, each with a unique interface and value proposition, is a tremendous challenge.
The world of relational databases grappled with the need for consistent interfaces decades ago, andlong since settled on standard means for communicating with data and application services. The
relational database driver standards like JDBC, ODBC and ADO.NET. Database vendors, developers,
and users have long relied on these common API standards, which has led to expansive ecosystems
of compatible tools and familiarity to users of nearly all skill levels.
While many products lack broad or deep NoSQL support, the majority of applications that consume
or generate data -- desktop tools, reporting servers, programming languages -- will implement robust
support for generic database access through one or more of these driver interfaces.
These universal driver interfaces speak a common language – ANSI SQL. Over the last 40 years, SQL
has established itself as the lingua franca of data access. While SQL does not marry perfectly withNoSQL structures, SQL-based drivers based on established data standards like ODBC, JDBC, and
ADO.NET provide a powerful and ubiquitous bridge between NoSQL and modern and legacy
applications alike.
The following solution brief highlights various techniques employed by CData Software(www.cdata.com) in providing SQL access to NoSQL data through their drivers. This paper
discusses a variety of mapping and flattening techniques, and continues with vendor examples thathighlight performance and usability differences between approaches.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 2

Mapping SQL to NoSQL
Due to the flexibility of NoSQL, it is common for data structures to be returned as JSON objects,
arrays, or any combination of hierarchical data. In order to work with these structures in common BI,
Reporting, and ETL tools, the data objects typically need to be transformed into a tabular data
format. The CData Drivers include several facilities for mapping or flattening these data structures tosimplify integration with standard tooling.
These capabilities include:•

Extended Projection:
Projection requesting exactly the data you want from your tables.

•

Horizontal Flattening:
Flattening drilling down into embedded data (sub-documents and arrays).

•

Vertical Flattening:
Flattening treating embedded arrays of sub-documents as separate tables.

•

Custom Schema Definitions:Definitions defining how the drivers view the NoSQL data.

•

ClientClient-Side JSON Functions:
Functions manipulating the data returned to perform client-side
aggregation and transformation.

No single capability is appropriate for all NoSQL data sets, and there are costs and benefits
associated with each approach. The techniques that users should employ to work with large or
deeply-nested structures will be different than working with datasets which are less “object based”.
Furthermore, other vendors approach abstracting NoSQL to SQL differently. After outlining these
NoSQL capabilities, this guide will outline other common approaches, including:•Presenting all data in a table definition:
definition exposing all sub-documents and embedded arrays
as columns within a table definition.

•

Creating parentparent-child relationships:
relationships separating sub-documents and embedded arrays as
distinct tables and build parent-child relationships between the tables, as appropriate.

Ultimately, the best solution is the one that offers the most flexibility when it comes to NoSQL datainterpretation, allowing users to tailor the solution to meet their any potential integration scenarios.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 3

Sample Document
To demonstrate the mapping and flattening capabilities it is useful to include example data. The
data structure below will be used in the examples that follow.

{"_id" : ObjectId("5780046cd5a397806c3dab38"),"address" : {"building" : "1007","coord" : [-73.856077, 40.848447],"street" : "Morris Park Ave","zipcode" : "10462"},"borough" : "Bronx","cuisine" : "Bakery","grades" : [{"date" : ISODate("2014-03-03T00:00:00Z"),"grade" : "A","score" : 2}, {"date" : ISODate("2013-09-11T00:00:00Z"),"grade" : "A","score" : 6}, {"date" : ISODate("2013-01-24T00:00:00Z"),"grade" : "A","score" : 10}, {"date" : ISODate("2011-11-23T00:00:00Z"),"grade" : "A","score" : 9}, {"date" : ISODate("2011-03-10T00:00:00Z"),"grade" : "B","score" : 14}],
"name" : "Morris Park Bake Shop","restaurant_id" : "30075445"}

This data originated from a MongoDB database, however the tools and techniques presented here
can be used with any supported NoSQL data source.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 4

Extended Projection
In SQL, projection generally refers to collecting a subset of columns from a table for use in an
operation. For example, issuing a SELECT query to pull a subset of columns from a table (SELECT
address, borough etc.). Extended projection is the process of extracting data from non-tabular or
hierarchical data sets.
Take the example of querying the included sample document from the root level. With NoSQL
structures the data represented in these top-level ‘columns’ may be object arrays or aggregates. The
default column list would include _id,
_id address,
address borough,borough cuisine,
cuisine grades,
grades name,name and restaurant_id.
restaurant_id The
address column is an example of an JSON object which would be returned as an aggregate withstandard projection.
Through extended projection, the CData Drivers allow users to drill down into the sub-documents andembedded arrays (using dot notation) without having to issue complex joins or subqueries. By
including _id,
_id address.street,address.street and grades.0 as columns in the projection, users can expose a more
atomic model of the data, accessing the traditionally exposed _id element as well as the street
element of the address sub-document and the entire first element in the embedded grades array.

SELECT[_id],[address.street],[grades.0]
FROM restaurants;

The driver returns the value for each field that contains data. If the field does not exist in a given
document, the driver returns NULL. This feature is useful whenever the structure of the data source is
known and when users can issue distinct SQL Queries.
However, BI and ETL tools do not typically offer direct query access, particularly for reporting andvisualization. In those instances, other flattening techniques should be used.

Horizontal Flattening
When a client does not have granular control of SQL Queries, flattening techniques should be used tomap NoSQL to a tabular representation. The CData Drivers handle this process though Connection
String settings.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 5

The Flatten Arrays and Flatten Objects connection properties allow users to control how objects andarray data is parsed in order to dynamically generate table schema for NoSQL data. These settings
configure how data is horizontally flattened, creating a single schema for all of the documents(including embedded data) in a given table.
Horizontal flattening can be very useful for smaller data sets or those without deeply-nested object
hierarchies. If the NoSQL data includes many sub-documents, large embedded arrays, or deeply
nested data, then horizontal flattening may not be the best solution. In these cases, horizontal
flattening may generate tables with too many columns, resulting in data sets that are overly granularand difficult to work with.
In the examples below, we display the expected results, based on various values for Flatten Arrays
and Flatten Objects, for the following query:

SELECT *FROM restaurants;

Default Behavior (FlattenObjects=False;)
Without any horizontal flattening, the Driver discovers seven columns for the table: _id,
_id address,
addressborough, cuisine,boroughcuisine grades,
grades name,name and restaurant_id.
restaurant_id Embedded data in the document is returned in a
raw, aggregate form.
_id

address

5780046…

{ "building" :"1007", "coord" :[-73.856077,
40.848447],"street" : …

boroughBronx

cuisinegrades

namerestaurant_id

Bakery[{"date" : ISODate("201403-03T00:00:00Z"),"grade" : "A", "score" : 2 },{ "date" : ISODate("201309-11T00:00:00Z"), …

Morris
Park
Bake
Shop

30075445

FlattenObjects=True;
If a user sets FlattenObjects to "True", the number of columns expands as the embedded address
sub-document is flattened. Without any changes to FlattenArrays
FlattenArrays,
Arrays any embedded arrays or arrays of
documents will be returned as aggregates:www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 6_idaddress.building

address.coordaddress.street

address.zipcodeborough…

5780046…

1007[-73.856077,
40.848447]

Morris Park Ave

10462

Bronx

…

FlattenArrays=2;
The FlattenArrays property determines how many items in an embedded array of sub-documents aretreated as individual columns. By setting FlattenArrays to "2", the driver extracts the first two items in
the embedded arrays of a document.
_idaddressborough

5780046…

{ "building" : "1007","coord" : [-73.856077,
40.848447], "street" :"Morris Park Ave","zipcode" : "10462" }Bronxcuisinegrades.0

grades.1…

Bakery{ "date" :ISODate("2014-0303T00:00:00Z"),"grade" : "A","score" : 2 }{ "date" :ISODate("2013-0911T00:00:00Z"),"grade" : "A","score" : 6 }…

FlattenArrays=1;FlattenObjects=True;
With FlattenArrays set to "1" and FlattenObjects set to “True” the driver will extract the first item in theembedded arrays of a document and flatten any embedded sub-documents.

_idaddress.
building

address.
coord.0

address.
streetaddress.
zipcode…

grades.
0.dategrades.
0.gradegrades.
0.score…

57800...

1007

73.856077

Morris Park
Ave

10462

…

2014-0303...

A

2

…

Any columns that are exposed from horizontal flattening are available for use in INSERT and
UPDATE statements as well, allowing you to add or update individual fields within sub-documents
and arrays. This approach works well for clients who know the structure of the data in advance andin data sets where the structure is not deeply nested.
Clearly, preconfiguring the driver prior to integration offers a richer experience when working withNoSQL data in tools and applications that expect a more traditional RDBMs Data Model.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 7

Vertical Flattening
NoSQL databases frequently contain an array (or arrays) of sub-documents. While it is possible todrill down into these sub-documents using horizontal flattening, that approach presents problems
when dealing with deeply nested data. Another approach for dealing with embedded arrays is to treat
them as separate tables of data. This process is known as vertical flattening and doing so helps tobuild a relational model between different 'types' of documents in a hierarchy.
Using the included sample document, users could retrieve an array of grades as a separate tableusing the following query:

SELECT*
FROM [restaurants.grades];This query would return the following data:
date

grade

score

2014-03-03T00:00:00ZA

2

2013-09-11T00:00:00Z

A

6

2013-01-24T00:00:00Z

A

10

2011-11-23T00:00:00ZA

9

2011-03-10T00:00:00Z

B

14

Users may also want to include information from the base restaurants table. This can beaccomplished with a join. Flattened arrays can be joined with the root document.
The CData Drivers expect that the left part of the join will be the array document that will be verticallyflattened. Set the SupportEnhancedSQL
upportEnhancedSQL connection property to false to join nested documents.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 8

For example:
SELECT[restaurants].[_id], [restaurants.grades].*
FROM[restaurants.grades]
JOIN[restaurants]
WHERE[restaurants].[name] = 'Morris Park Bake Shop' ;This query would return the following:

_id

date

grade

score

5780046cd5a397806c3dab38

2014-03-03T00:00:00ZA

25780046cd5a397806c3dab38

2013-09-11T00:00:00ZA

65780046cd5a397806c3dab38

2013-01-24T00:00:00ZA

105780046cd5a397806c3dab38

2011-11-23T00:00:00ZA

95780046cd5a397806c3dab38

2011-03-10T00:00:00ZB

14

Custom Schema Definitions
In order to create a relational facade on top of NoSQL data, a table schema must exist. The schema
can be created dynamically through flattening via the Connection properties, or by pre-defining aschema. A pre-defined schema is another option for drilling down into data when it is impossible tomaintain full control of the SQL queries constructed.
A Custom Schema gives users the ability to define the SQL structure that should be used whenaccessing the underlying NoSQL source. For example, using our sample data a user may want todefine a table with _id (as the primary key), name,name zipcode,
zipcode and latest_grade (the first entry in thegrades fields). The resulting schema would look like this:www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 9<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2"><rsb:info title="StaticRestaurants"
description="Custom Schema for the MongoDB restaurants data set."><!-- Column definitions --><attr name="id"xs:type="int32" iskey="true" other:bsonpath="$._id" /><attr name="name"xs:type="string" other:bsonpath="$.name"/><attr name="zipcode"
xs:type="string" other:bsonpath="$.address.state"/><attr name="latest_grade" xs:type="string" other:bsonpath="$.offices.grade"/></rsb:info><rsb:set attr="collection" value="companies" /></rsb:script>

Schema files are saved to disk alongside the driver and referenced through the connection
properties1. The driver will expose the defined tables based on the title attribute of rsb:info. Users canalso query the data explicitly by using the title as the table name in a SQL query:

SELECTid, latest_grade
FROM
StaticRestauraunts

By defining a schema, users gain granular control over data in ways not commonly supported in BI,
reporting, and ETL tools, facilitating the use of visualization, transformation, and extraction featuresof those same tools. Custom Schemas also allow users to define different views of the data stored ina single table enabling users to take full advantage of the NoSQL database structure where a given
table contains documents where relevant fields are differentiated by type definition.

Client-Side JSON Functions
NoSQL data structures are often represented as JSON structures. The CData Drivers support SQL
functions for extracting data from JSON structures. A few examples of the JSON functions arehighlighted below. For a complete list of supported functions reference the driver help
documentation.
The following example uses the included sample document to extract values contained in the'Students' table:

1

Other techniques for embedding schema definitions are also available, but are beyond the scope of this paper.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 10{
id: 123456,...,
grades: [{ "grade": "A", "score": 96 },{ "grade": "A", "score": 94 },{ "grade": "A", "score": 92 },{ "grade": "A", "score": 97 },{ "grade": "B", "score": 84 }
],
...
}

JSON_EXTRACT
The JSON_EXTRACT function can extract individual values from a JSON object. The following query
returns the values shown below based on the JSON path passed as the second argument to thefunction:
SELECT
JSON_EXTRACT(grades,'[0].grade') AS Grade,
JSON_EXTRACT(grades,'[0].score') AS Score
FROM Students;

This query returns the following data:
Grade

Score

A

96

JSON_SUM
The JSON_SUM function returns the sum of the numeric values of a JSON array within a JSON
object. The following query returns the total of the values specified by the JSON path passed as thesecond argument to the function:
SELECT
Name,
JSON_SUM(score,'[x].score') AS TotalScore
FROM Students;

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 11

DOCUMENTThe DOCUMENT function can be used to retrieve the entire document as a JSON string. See thefollowing query and its result as an example:
SELECT
DOCUMENT(*)
FROM Students;

The query above returns each document in the table as a single string. E.x:
DOCUMENT{ "_id" : ObjectId("5780046cd5a397806c3dab38"), "address" : { "building" : "1007", "coord" : [-73.856077,
40.848447], "street" : "Morris Park Ave", "zipcode" : "10462" }, "borough" : "Bronx", "cuisine" : "Bakery","grades" : [{ "date" : ISODate("2014-03-03T00:00:00Z"), "grade" : "A", "score" : 2 }, { "date" : ISODate("2013-0911T00:00:00Z"), "grade" : "A", "score" : 6 }, { "date" : ISODate("2013-01-24T00:00:00Z"), "grade" : "A", "score" :10 }, { "date" : ISODate("2011-11-23T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2011-0310T00:00:00Z"), "grade" : "B", "score" : 14 }], "name" : "Morris Park Bake Shop", "restaurant_id" : "30075445" }SQL to NoSQL Comparison
In order to choose the best approach for your integration, it is important to understand the various
approaches to modeling NoSQL through SQL. CData provides a high degree of granularity andcontrol when working with NoSQL data, however other vendors choose a different approach.
The following section will highlight the ways in which other popular vendors approach NoSQL
mapping in the following scenarios:•

Requesting All Table Data:
Data comparing the results of requesting all available data as a table(e.g. SELECT * FROM restaurants).

•

Embedded Arrays as Separate Tables:
Tables comparing the results of sending JOIN queries towork with a table and the embedded data array(s).

•Embedded Arrays & SubSub-Documents as Table
Table Elements:
Elements comparing the results of working
with embedded array(s) as elements within a table.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 12

Requesting All Table Data
The most common way to retrieve all of the data in a traditional relational database table is to submit
a SELECT * query. To compare techniques, we will display the results of a SELECT * ... queryfrom various ODBC Drivers using their default connection properties.
There are 3 distinct approaches used by the top industry vendors.
The Drivers from Vendor 1 (V1) parse the grades array and address objects into separate tables
linked by a parent-child relationship. The result of the select returns the following:
BOROUGHRESTAURANT_ID_ID

CUISINE

NAME

Bronx

30075445

5780046CD5A397806C3DAB38

BakeryMorris Park Bake
Shop

Brooklyn

30112340

5780046CD5A397806C3DAB39

Hamburgers

Wendy'S

Manhattan

30191841

5780046CD5A397806C3DAB3A

Irish

Dj Reynolds Pub And
Restaurant

Brooklyn40356018

5780046CD5A397806C3DAB3B

AmericanRiviera Caterer

The drivers from Vendor 2 (V2) similarly provide a subset of data when issuing a SELECT statement.
The V2 drivers parse the grades and address.coord arrays as separate linked tables. The result is:_idaddress_building

address_
streetaddress_
zipcodeboroughcuisinenamerestaurant_id57800…

1007

MorrisPark Ave

10462

Bronx

BakeryMorris Park
Bake Shop

30075445

57800…

469

Flatbush
Avenue11225

Brooklyn

Hamburgers

Wendy'S

30112340

57800…

351

West 57
Street10019

Manhattan

Irish

Dj Reynolds
Pub And …

30191841

57800…

2780

Stillwell
Avenue11224

Brooklyn

AmericanRivieraCaterer

40356018

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 13

While creating separate tables distinguishes the different hierarchies in a NoSQL database, it canhave significant performance and usability drawbacks when querying data from parent and child
tables. The CData Drivers expose the most data by default, flattening the address object and drilling
down to retrieve the fields available in the elements of the grades array.

_idaddress.bui…

address.
coord.0

address.
coord.1

address.
streetaddress.zip…

restaur
ant_idgrades.
0.dategrades.
0.grade…

57800…

1007-73.856…

40.8…

MorrisPark Ave

10462

30075445

201403-03…

A…

57800…

469-73.961…

40.6…

Flatbush
Avenue11225

30112340

201412-30…

A…

57800…

351-73.985…

40.7…

West 57
Street10019

30191841

201409-06…

A…

57800…

2780

73.982...

40.5…

Stillwell
Avenue11224

40356018

201406-10…

A…

Embedded Arrays as Separate Tables
In the sample data, each document in the restaurants table contains an array of embedded
documents in the grades element. This data represents the different grades a restaurant hasreceived over time. As this is a hierarchy, the embedded documents capture the relationships
between data by storing data in a single document structure.
By default, the drivers from Vendor 1 and 2 create a schema where the embedded documents are
only recognized as separate tables (referred to as virtual tables and child tables respectively). They
automatically create a table schema where the grades table shares a foreign key relationship withthe restaurants table. The CData Drivers offer an additional level of control. They maintain theembedded documents, as elements within the original document, yet still allow users to treat theembedded values as separate tables.
Regardless of how the schemas are defined, all of the drivers are able to perform JOIN queries toretrieve related data between the tables.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 14

Consider the following desired result-set:restaurant_id

date

grade

score

P_id

30075445

2014-03-03T00:00:00.000ZA2

568c37b748ddf53c5ed98932

30075445

2013-09-11T00:00:00.000ZA6568c37b748ddf53c5ed98932

30075445

2013-01-24T00:00:00.000ZA

10568c37b748ddf53c5ed98932

30075445

2011-11-23T00:00:00.000ZA

9568c37b748ddf53c5ed98932

30075445

2011-03-10T00:00:00.000Z

B

14568c37b748ddf53c5ed98932

Queries and Performance
The queries required by the drivers to retrieve each grade as a separate row are relatively similar.
Each driver uses an implicit JOIN to aggregate data. However, it is worth noting that the drivers from
Vendors 1 and 2 require the use of a WHERE clause to identify the relationship between the twotables.
The CData Drivers on the other hand use vertical flattening (where child arrays are recognized as
fields within the parent table, but can be treated as separate tables) to manage JOIN queries. The
drivers from V1 and V2 treat the grades array as a separate table by default, meaning that data fromboth tables are pulled into memory and the drivers perform the JOIN client-side.

DriverTime (seconds)

Query (returns all grades for approximately 10 million restaurants)

CData

252.9 (+35% - +59%)

SELECT [restaurants].[restaurant_id],[restaurants.grades].* FROM[restaurants.grades] JOIN [restaurants]

Vendor1

341.5

SELECT restaurants_grades.*, restaurants.restaurant_id
FROM restaurants_grades, restaurants
WHERE restaurants._ID = restaurants_grades.restaurants_id

Vendor2

401.2

SELECT restaurants_grades.*, restaurants.restaurant_id
FROM restaurants_grades, restaurants
WHERE restaurants._ID = restaurants_grades._id

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 15

Embedded Arrays & Sub-Documents as Table Elements
Document databases frequently contain embedded BSON/JSON objects and arrays as individual
elements. The CData drivers are the only drivers that are capable of easily handling this type ofstructure. Consider the following result-set:restaurant_id

grades.0.grade

grades.1.grade

grades.2.grade

grades.3.grade

grades.4.grade

123456780

A

A

A

A

A

123456781

B

B

B

B

B

123456782

C

C

C

C

C

With the CData drivers users can submit a free-form query to request the data as described above.
The drivers use dot-notation to interpret requests for individual array objects and fields within subdocuments.
For example:

SELECT[restaurant_id], [grades.0.grade], [grades.1.grade], [grades.2.grade], [grades.3.grade], [grades.4.grade]
FROM[restaurants]

Other drivers interpret arrays of documents as separate tables. This means that users must perform
JOIN queries in order to retrieve both restaurant and grades data. This interpretation means that
there is no simple way to retrieve the grades for a given restaurant in a single row.

www.cdata.com

SOLUTION BRIEF

SQL FOR NOSQL DATA | 16

Conclusion
The CData approach to standardizing NoSQL integration builds upon the ubiquitous support for andfamiliarity with ODBC, JDBC, and similar standards. By wrapping data access in standards-compliant
drivers, CData enables straightforward integration in any context where an RDBMS database would
be used.
As Big Data continues to usher in a new era of analytics and cognitive computing, users are tasked
with processing ever larger datasets. Data is now commonly aggregated from across multiple data
sources and processing is federated across multiple nodes. As a result, the impact of seemingly
small architectural differences can have a major impact on the performance of enterprise systems.
By offering granular control over NoSQL data interpretation, the CData Drivers exhibit a level of depth
and flexibility unmatched by other products. These distinctions are supported by performance
metrics executed when using the CData Drivers to process large amounts of NoSQL data.
While performance is beyond the scope of this paper, users should be cognizant of performance
characteristics of any driver planned for production. For a complete picture of the data interpretationand performance features that set the CData Drivers apart, please refer towww.cdata.com/tech/bigdata/
For more information about CData Software, please visit the company website at: www.cdata.com.

www.cdata.com

For a complete list of current NoSQL Drivers, go to: www.cdata.com/drivers/
CData Software (www.cdata.com) is a leading provider of data access and connectivity solutions. We specialize inthe development of Drivers and data access technologies for real-time access to on-line or on-premise applications,
databases, and Web APIs. Our drivers are universally accessible, providing access to data through established data
standards and application platforms such as ODBC, JDBC, ADO.NET, ODATA, SSIS, BizTalk, Excel, etc.
Our goal is to simplify the way you connect to data. We offer a straightforward approach to integration, with easyto-use, data providers, drivers, and tools accessible from any technology capable of interacting with major databasestandards. This approach to integration allows businesses to realize the tremendous benefits and costs savings ofintegration while reducing complexity and expense.

www.cdata.com