Homework 7 | JSON and NoSQL

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Homework 7 | JSON and NoSQL

3.   Start an instance of AsterixDB using the start-sample-cluster.sh (or .bat if you are on Windows) located in the opt/local/bin folder.

a.   Once you are in the opt/local/bin folder, you can start an instance by typing in ./start-sample-cluster.sh (or .bat if you are on Windows) in the terminal

4.  When your AsterixDB instance is running you can enter the query interface by visiting 127.0.0.1:19001 in your favorite web browser. It may take a few minutes before you can access the page, while the instance starts up.

5.   Download the geographical data from the link in the resources above. The data are JSON data files; you can inspect them using your favorite text editor.

6.   Create a dataverse of Mondial data. Copy and paste the text below in the Query box of  the web interface. Edit the to be the location of the mondial.adm file on your computer. The format of this line depends on your operating system.

127.0.0.1 is the ip address of the computer you are running this on, and what follows is the directory path.

FOR WINDOWS: your path will look like the example in the code below, since Windows paths start with the name of the drive like C:

FOR MAC: your path will most likely look something like this:

“path”=“127.0.0.1:///Users/XXX/Desktop/hw7/data/mondial.adm” (Then don’t forget the (“format”=”adm”) part as shown below)

i.      Tip: Go to your mondial.adm file in your Finder > right click  > hold option > click “Copy “” as Pathname > paste this path in after

“path”=”127.0.0.1:// (so it looks like the path above, should be a total of three slashes “/”)

7.    Then press Run

DROP DATAVERSE geo IF EXISTS;

CREATE DATAVERSE geo;

CREATE TYPE geo.worldType AS {auto_id:uuid };

CREATE DATASET geo.world(worldType)

PRIMARY KEY auto_id AUTOGENERATED;

LOAD DATASET geo.world USING localfs

(("path"="127.0.0.1:///Users/suciu/asterixdb/hw7/mondial.adm"),("format"="a

dm"));

/* Edit the absolute path above to point to your copy of mondial.adm. */

/* You may need to use '\' instead of '/' in a path for Windows. e.g.,

127.0.0.1://C:\\XXX\\hw7\\mondial.adm. */

8.  Alternatively, you can use the terminal to run queries rather than the web interface. After you have started Asterix, put your query in a file (say q1.sqlp), then execute the query    by typing the following command in terminal (notice that the port number here is 19002):

curl -v --data-urlencode "statement=`cat q1.sqlp`" --data pretty=true

http://localhost:19002/query/service

This will print the output on the screen. If there is too much output, you can save it to a file

curl -v --data-urlencode "statement=`cat q1.sqlp`" --data pretty=true

http://localhost:19002/query/service     > output.txt

You can now view output.txt using your favorite text editor.

9.   To reference the geodatabase, use the statement USE geo; before each of your queries  to declare the geo namespace. Alternatively, prefix every dataset with geo. Try this query to see if things are running correctly:

SELECT y.`-car_code` as code, y.name as name

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

ORDER BY y.name;

10. For practice, run, examine, modify these queries. They contain useful templates for the questions on the homework: make sure you understand them.

-- return the set of countries

SELECT x.mondial.country FROM geo.world x;

-- return each country, one by one (see the difference?)

SELECT y as country FROM geo.world x, x.mondial.country y;

-- return just their codes, and their names, alphabetically

-- notice that -car_code is not a legal field name, so we enclose in ` ...

`

SELECT y.`-car_code` as code, y.name as name

FROM geo.world x, x.mondial.country y order by y.name;

-- this query will NOT run...

SELECT z.name as province_name, u.name as city_name

FROM geo.world x, x.mondial.country y, y.province z, z.city u

WHERE  y.name='Hungary';

-- ...because some provinces have a single city, others have a list of

cities; fix it:

[Result Size: 238 rows of {"num_religions":..., "country":..., "population":...}]

3.   For each religion return the number of countries where it occurs; order them in a decreasing number of countries.

Name your output attributes religion, num_countries.

[Result size: 37 of {"religion':..., "num_countries":...}]

4.   For each ethnic group, return the number of countries where it occurs, as well as the total population world-wide of that group. Hint: you need to multiply the ethnicity’s

percentage with the country’s population. Use the functions float(x) and/or int(x) to convert a string to a float or to an int.

Name your output attributes ethnic_group, num_countries, total_population. You can leave your final total_population as a float if you like.

[Result Size: 262 of {"ethnic_group":..., "num_countries":..., "total_population":...}]

5.   Find all countries bordering two or more seas. Here you need to join the "sea" collection with the "country" collection. For each country in your list, return its code, its name, and  the list of bordering seas, in decreasing order of the number of seas.

Name your output attributes country_code, country_name, seas. The attribute seas should be a list of objects, each with the attribute sea.

[Result Size: 74 rows of {"country_code":..., "country_name":..., "seas": [{"sea":...}, {"sea":...}, ...]}]

Submission Instructions

Write your answers in a file for each question: q1.sqlp, … , q5.sqlp to Gradescope.