Javascript & SQL within database management system

Javascript & SQL
within database management system

Clusterpoint — Building and Growing Database-as-a-Service : A Case Study
Clusterpoint database
2-in-1: DBaaS & on-premises database
Document oriented
Sharded + replicated
Schema less
ACID transactions
Cloud enabled (EU & US sites)

Evolution of Computing Infrastructure
History
1970 1995 2010 20152005
Google discovers that all of web
does not ﬁt in a relational database
Jeﬀ Dean, Sanjay Ghemaway et al
publish papers on MapReduce
and BigTable
Key-Value Store
MapReduce
Mainframes Clusters of Commodity Hardware
Commercially Viable
Technologies are split
between: data storage
and computing
will they merge
?

Clusterpoint — Building and Growing Database-as-a-Service : A Case StudyClusterpoint — Building and Growing Database-as-a-Service : A Case Study

Clusterpoint — Running JavaScript Inside the Database
db.runCommand({
mapreduce: "DenormAggCollection",
query: {
filter1: { '$in': [ 'A', 'B' ] },
filter2: 'C',
filter3: { '$gt': 123 }
},
map: function() { emit(
{ d1: this.Dim1, d2: this.Dim2 },
{ msum: this.measure1, recs: 1, mmin: this.measure1,
mmax: this.measure2 < 100 ? this.measure2 : 0 }
);},
reduce: function(key, vals) {
var ret = { msum: 0, recs: 0, mmin: 0, mmax: 0 };
for(var i = 0; i < vals.length; i++) {
ret.msum += vals[i].msum;
ret.recs += vals[i].recs;
if(vals[i].mmin < ret.mmin) ret.mmin = vals[i].mmin;
if((vals[i].mmax < 100) && (vals[i].mmax > ret.mmax))
ret.mmax = vals[i].mmax;
}
return ret;
},
finalize: function(key, val) {
val.mavg = val.msum / val.recs;
return val;
},
out: 'result1',
verbose: true
});
db.result1.
find({ mmin: { '$gt': 0 } }).
sort({ recs: -1 }).
skip(4).
limit(8);
SELECT
Dim1, Dim2,
SUM(Measure1) AS MSum,
COUNT(*) AS RecordCount,
AVG(Measure2) AS MAvg,
MIN(Measure1) AS MMin
MAX(CASE
WHEN Measure2 < 100
THEN Measure2
END) AS MMax
FROM DenormAggTable
WHERE (Filter1 IN (’A’,’B’))
AND (Filter2 = ‘C’)
AND (Filter3 > 123)
GROUP BY Dim1, Dim2
HAVING (MMin > 0)
ORDER BY RecordCount DESC
LIMIT 4, 8
1
2
3
4
5
1
7
6
1
2
3
4
5
Grouped dimension columns are pulled
out as keys in the map function,
reducing the size of the working set.
Measures must be manually aggregated.
Aggregates depending on record counts
must wait until finalization.
Measures can use procedural logic.
Filters have an ORM/ActiveRecord-
looking style.
6 Aggregate filtering must be applied to
the result set, not in the map/reduce.
7 Ascending: 1; Descending: -1
Revision4,Created2010-03-06
RickOsborne,rickosborne.org
mySQL MongoDB

+

Technology top 2015 (StackOverﬂow)

SQL JavaScript
flexible to express
queries
executes in parallel
static
hard to define
expressions
bad with custom
routines
hard to express queries
difficult to execute in
parallel
dynamic
easy to define
expressions
great with custom
routines

Javascript - V8
Too good to be used only in browsers
• Chrome
• Node.js
• MongoDB
• Google BigQuery UDF

Javascript - V8
Produces machine code (IA-32, x64, ARM)

Javascript - V8
Performance - Problem
Compute the 25,000th prime

Javascript - V8
Performance - Algorithm
For x = 1 to inﬁnity: if x not divisible by any
member of an initially empty list of primes, add
x to the list until we have 25,000

Javascript - V8
Performance - Contenders

Javascript - V8
Performance - Results (only 17% slower)

Javascript - V8
Data Binding
C++ callback V8
if (name == “John”)
{
return “Male”;
}
Accessor 
(C++ callback)
string * get_value(string &ﬁeld_name)
{
if (ﬁeld_name == “name”)
return new string(“John”);
else
return new string(“Unknown”);
}

JS/SQL
Language structure
SQL-like structure
Arbitrary JavaScript in any clause of the SELECT or
UPDATE statement.
Joins, indexing nested documents
+

SELECT * FROM product
+Clusterpoint — Building and Growing Database-as-a-Service : A Case Study

JS/SQL
Insert statement
INSERT INTO product JSON VALUE {
"name": "Schwinn S29 Full Suspension Mountain Bike",
"image_url": "schwinn_s29.jpeg",
"description": "...",
"color": ["black","red"],
"order_price": 211.16,
"price": 259.16,
"packaging": {
"height": 23,
"width": 25,
"depth": 12,
"weight": 54
},
"availability": "In Stock"
}

JS/SQL
Update statement
+
UPDATE product[“id_123”] SET
{
color.push_back(“orange”);
packaging.volume = packaging.height * packaging.width * packaging.depth;
}

JS/SQL
Price buckets

JS/SQL
Grouping/Aggregation
function PriceBucket(price) {
var boundaries = [0, 1, 5, 10, 50, 100, 200, 500, 1000];
for (var i = 1; i < boundaries.length; i++) {
if (price >= boundaries[i - 1] && price < boundaries[i])
return boundaries[i - 1].toString() + " to " + boundaries[i].toString();
}
return "above " + boundaries[boundaries.length - 1].toString();
}
SELECT PriceBucket(price), COUNT()
FROM product
GROUP BY PriceBucket(price);

JS/SQL
Javascript & SQL in action
SELECT PriceBucket(price), COUNT()
FROM product
GROUP BY PriceBucket(price);
doc1 John
AccessorField (price) store JS V8
…. PriceBucket(price) ….doc2 George
doc3 Marry

JS/SQL
Joins
INSERT INTO product["34A40855"] JSON VALUE {
name: "Schwinn S29 Full Suspension Mountain Bike",
price: 259.16
};
INSERT INTO order JSON VALUE {
product_key: "34A40855",
delivery_address: "My Office"
};
SELECT delivery_address, product[product_key].price
FROM order
WHERE product[product_key].price > 20
+

Sign up
Where:
http://cloud.clusterpoint.com
How Much?
“Zero, none”
10GB free FOREVER !!!
Any Docs?
http://docs.clusterpoint.com
Few ﬁelds and you are in

Thank you!

Javascript & SQL within database management system

More Related Content

What's hot

Similar to Javascript & SQL within database management system

Recently uploaded

Javascript & SQL within database management system