In this assignment we will experiment with SenseiDB and its Browse Query Language (BQL).
Sample outputs are below:
To use Sensei, login using SSH to gpu1.ddl.ok.ubc.ca with your Novell account. Type /srv/sensei/bin/sensei-shell.sh localhost 50020 to get the Sensei shell to enter and run BQL queries.
You can also open your browser of choice and go to http://gpu1.ddl.ok.ubc.ca:50020/ to see the companion web-console if you prefer to use it to interact with the sensei cluster.
How many of the following "features" are supported by SenseiDB?
A. 0 B. 1 C. 3 D. 5 E. 6
Answer:
C. 3Note: There are 4 potential supported features, as you can TECHNICALLY insert/delete through JSON.
Given the following schema:
<table uid="id" delete-field="" skip-field="">
<column name="gid" type="int" />
<column name="gname" type="string" />
<column name="pubname" type="string" />
<column name="releasedate" type="date" />
<column name="maxplayers" type="int" />
<column name="rating" type="float" />
<column name="genre" type="string" multi="true" delimiter=","/>
</table>
And the associated sample table:
+--------+-----------+---------+-------------+--------------+----------+----------+ | gid | gname | pubname | releasedate | maxplayers | rating | genre | +--------+-----------+---------+-------------+--------------+----------+----------+ | 1.0 | Wii Sports| Nintendo| 2006-11-19 | 4 | 3.5 | sports, | | | | | | | | family | | 2 | Mario Kart| Nintendo| 2008-04-10 | 4 | 4.2 | racing, | | | | | | | | family, | | | | | | | | action | | 3 | Wii Fit | Nintendo| 2008-12-01 | 2 | -1.2 | exercise,| | | | | | | | health, | | | | | | | | family, | | | | | | | |2008-12-01| +--------+-----------+---------+-------------+--------------+----------+----------+
How many violations of the schema are present in the sample table and what are they?
Answer:
There are 3 violations in total: - [ 1.0 in gid column ] -> GID IS TYPE INT NOT FLOAT - [ -1.2 in rating column ] -> NEGATIVE NUMBERS ARE NOT SUPPORTED - [ 2008-12-01 in genre column ] -> GENRE MULTI MUST ALL HAVE SAME TYPE (STRING NOT DATE)
Write a JSON statement that could be used to add a game data event into a SenseiDB table using the schema presented in Task 2. Don't actually add the new game, just write the JSON statement.
Answer:
{ "type" : "add", "data":
{
"gid" : "501",
"gname" : "Dance Dance Revolution",
"pubname" : "Konami",
"releasedate" : "1998-11-21",
"maxplayers" : "2",
"rating" : "4.2",
"genre" : "health,music,dance"
}
}
Write a BQL query that will list all the game records. There should be 459 records printed.
Answer:
SELECT * LIMIT 459;
Write 3 different BQL queries that list all of the games with a rating of either 0, 1, 3, or 4 (i.e., not equal to 2 or 5). There should be 371 records printed.
Answer:
SELECT gname, rating WHERE rating <= 1 OR rating = 3 OR rating = 4 LIMIT 459; SELECT gname, rating WHERE rating <> 2 AND rating <> 5 LIMIT 459; SELECT gname, rating WHERE rating BETWEEN 0 AND 4 AND rating <> 2 LIMIT 459; SELECT gname, rating WHERE rating BETWEEN 0 AND 1 OR rating BETWEEN 3 AND 4 LIMIT 459; Note: using 'SELECT gname, rating WHERE rating = 0' will return no results!
Write a BQL query that returns the two newest games from each publisher. Note that some publishers will only have one game.
Answer:
SELECT gname, pubname, releasedate GROUP BY pubname TOP 2 ORDER BY releasedate DESC LIMIT 43;
Write a BQL query that returns a list of games and their respective genres organized into groups for each publisher. The games' descriptions must match the following conditions: containing both the words "ipsum" and "et" but not containing either of the words "congue" or "sit". You must also find how many hits were counted for each genre.
Answer:
SELECT gname, genre, pubname WHERE description CONTAINS ALL ("ipsum", "et") EXCEPT ("congue", "sit") GROUP BY pubname BROWSE BY genre;
Write a BQL query to find the top 10 Puzzle or Dance games using a custom relevance model, which gives higher rating to games that are of the genre Puzzle (with boost value 50) or games that have the name Mortal Kombat (with boost value 100).
Answer:
SELECT * WHERE genre IN ('Puzzle','Dance')
USING RELEVANCE MODEL my_model (favorite_genre:'Puzzle', favorite_name:'Mortal Kombat')
DEFINED AS (String favorite_genre, String favorite_name)
BEGIN
float boost = 0.0;
if (genre.contains(favorite_genre))
boost += 50;
if (favorite_name.equals(gname))
boost += 100;
return rating + boost;
END
ORDER BY RELEVANCE LIMIT 10;
Modify your existing game center website from Lab 1 to use SenseiDB to display a list of all of the games instead of your previous database. As well, you need to display the facet information for Publisher Name, Rating, Genre, and Maximum Number of Players.
Your page doesn't have to do anything fancy, it can just be a simple page that displays the list of all games and facet information in a readable format.
Refer to http://senseidb.com/clients.html for the client APIs to interact with sensei. Hint: the Rest/JSON Client API via HTTP POST is probably the easiest to implement.
You can view a live example at http://cs-suse-5.ok.ubc.ca/44907087/tomcat/gameCenter/senseiList.php.
The mark breakdown is as follows:
Answer:
Submit a single text file with answers to the written questions and a single zip file all your source code using Connect or by email.
Home