4 of 18

Semi-Structured Data

A data format which does not obey the typical formal structure of data (such as relational data) but also has ‘tags’ to enforce hierarchies (tree structured)

5 of 18

Why Semi-Structured?

Real-world data is often semistructured, with a flexible schema

JSON
Protobuf
XML

Of course, we can convert this into a relational model, but the overhead costs of doing so may be too high due to the complexity of the data!

Hence, we use a system to query over semistructured data directly

6 of 18

JavaScript Object Notation

Why JSON?

Easy for humans to read and write and for machines to parse and generate. (Win-Win!)
Based on familiar syntax derived from JavaScript, C, Java

Built on two structures:

A collection of name/value pairs

Can be an object, record, struct, dictionary, hash table, keyed list, associative array...

An ordered list of values

In most languages, this is realized as an array, vector, list, or sequence.

Takeaways:

Think of it as an array of ordered name value pairs, or an array of objects in the form of a tree
.adm files in your homework stand for Asterix Data Model: very similar format to JSON files

7 of 18

AsterixDB

A scalable, open source Big Data Management System ( BDMS)

Used to query over semi-structured data!

We will use SQL++ in AsterixDB to query over data in our HW

Dataverse stands for “data universe”, and is used in a similar vein to the word database in relational DBMS’

9 of 18

SQL++

The query language used in AsterixDB to query semi-structured data.
You will note that it is strikingly similar to SQL!

(Because SQL is the industry standard)
And, because it was designed to be just like SQL but parsed for processing JSON data

Has keywords to handle collections of data (i.e. non-flat data)�

Why design SQL++ like SQL? Why not some other query language?

People are used to specifying data through SQL syntax
SQL-like language enforces idea of physical data independence

10 of 18

Output with Structure

If we wanted the name and a list of orders:

SELECT P.name, P.orders

FROM Person P;

{“name”: “Dan”, “orders”: [ { “date”: 1997,

“product”: “Furby”} ]}

{“name”: “Alvin”, “orders”: [ {date”: 2000,

“product”: “Furby”},

{date”: 2012,

“product”: “Magic8”} ]}

11 of 18

(Implicit) Unnesting

If we wanted the name and each product separately:

SELECT P.name, O.product as product

FROM Person P, P.orders O;

{“name”: “Dan”, “product”: “Furby”}

{“name”: “Alvin”, “product”: “Furby”}

{“name”: “Alvin”, “product”: “Magic8”}

12 of 18

Nesting (Method #1)

If we wanted the name and all products as a list:

SELECT P.name, products

FROM Person P

LET products = (SELECT O.product FROM P.order O);

{“name”: “Dan”, “products”: [“Furby”] }

{“name”: “Alvin”, “products”: [“Furby”, “Magic8”]}

13 of 18

Nesting (Method #2)

If we wanted the name and all products as a list:

SELECT P.name, (SELECT O.product FROM P.order O)� AS products

FROM Person P;

{“name”: “Dan”, “products”: [“Furby”] }

{“name”: “Alvin”, “products”: [“Furby”, “Magic8”]}

14 of 18

Compare!

If we want name and all products as a list:

SELECT P.name, products

FROM Person P

LET products = (SELECT O.product

FROM P.order O);

{“name”: “Dan”, “product”: [“Furby”] }

{“name”: “Alvin”, “product”: [“Furby”, “Magic8”]}

If we want name and each product separately:

SELECT P.name, O.product as product

FROM Person P, P.orders O;

{“name”: “Dan”, “product”: “Furby”}

{“name”: “Alvin”, “product”: “Furby”}

{“name”: “Alvin”, “product”: “Magic8”}

15 of 18

Cases

SELECT y.name, z.`-continent` as continent

FROM geo.world x, x.mondial.country y,

case when is_array(y.encompassed)

then y.encompassed

else [y.encompassed] end z

ORDER BY y.name;

“Find the encompassing continents for each country”

16 of 18

Useful Syntax for HW7

is_array( … ) -----> checks if value is an array
split(s, d) -----> splits string s on delimiter d
[ … ] -----> explicitly construct array
(CASE WHEN … THEN … ELSE … END) -----> combine with “is_array(…)”
MISSING -----> reserved keyword like “NULL”
` … ` -----> backtick needed for accessing keys with names containing “-”

17 of 18

Mondial SQL++ Example

SELECT y.`-car_code` as code,

y.name as name

FROM geo.world x, x.mondial.country y