ssh-keygen -t rsa -P ""
 
 
It will ask you if you want to name the file it creates id_rsa. Just press ENTER and accept the default name.
 
Now run the following commands. They should each run without error.

 
 
cp .ssh/id_rsa.pub .ssh/authorized_keys
 
sudo chmod 777 /usr/local/asterixdb/clusters/local
 
sudo chmod 666 /usr/local/asterixdb/clusters/local/*.xml
 
sudo chmod 666 /usr/local/asterixdb/clusters/demo/*.xml
 
sudo chmod 666 /usr/local/asterixdb/conf/managix-conf.xml
 
managix configure
 
managix validate
 
managix create -n my_asterix -c /usr/local/asterixdb/clusters/local/local.xml
 
 
That last command may take some time to complete. After it runs, you should see Status: ACTIVE, which tells you that AsterixDB is now running in your Linux virtual machine.
 
Finally, click on the browser window icon at the bottom of the screen. And navigate to the URL http://127.0.0.1:19001. You should see a page like this one where you can type in queries.
 

 
 
SELECT VALUE ds FROM Metadata.`Dataset` ds;
 
 
This query will print out information about all of the datasets that already exist in your AsterixDB instance. You will add your own datasets below.
 
If you later shut down and restart VMware, you can use the following commands to restart AsterixDB:

 
 
managix stop -n my_asterix
 
managix start -n my_asterix
 
Option 2 (Advanced)
 
Another way to get access to a Linux machine is to create one in Azure. Start by following these instructions (https://docs.microsoft.com/en-us/azure/virtual-machines/linux/quick-create-portal) to create a linux machine that you can connect to using SSH. (See the linked instructions on that page, or click here (https://docs.microsoft.com/en-us/azure/virtual-machines/linux/ssh-from-windows), to see how to install SSH on a Windows machine. On a Mac, SSH is already available in the Terminal program.)
 
 
Once you have connected to your Linux machine using SSH, you can follow these instructions (https://ci.apache.org/projects/asterixdb/install.html#Section1SingleMachineAsterixDBInstallation) to install AsterixDB on the Linux machine.

 
 
cd Downloads/asterix-server-0.9.0-binary-assembly samples/local/bin/start-sample-cluster.sh
 
Now, navigate in your browser to http://localhost:19001, and you should see a web page where you can type in SQL++ queries.
 
If you want to test that it works, you can try running the following query:

 
 
SELECT VALUE ds FROM Metadata.`Dataset` ds;
 
 
This query will print out information about all of the datasets that already exist in your AsterixDB instance. You will add your own datasets below.
 
When you want to stop your AsterixDB cluster, run this command:

 
 
samples/local/bin/stop-sample-cluster.sh
 
 
Loading Data
 
Now that you have AsterixDB running, we need to load some data. Start by downloading the data in the browser on your Linux machine. Navigate to this page (hw5.html) in that browser and click the link at the top of the page to download the additional files.
 
The browser will store the hw5.tar.gz file in the Downloads subdirectory. Run this command to unpack the archive:

 
tar xfz Downloads/hw5.tar.gz
 
 
If you run the ls command, you will now see four files ending with .adm that store the data we will use in this assignment. (Recall that ADM is an extension of JSON that supports additional types of data.)
 
Create a new dataverse "hw5", which includes a dataset "world", and load the mondial.adm data into that dataset using the following commands:

 
USE hw5;
 
SELECT x.mondial.country FROM world x;
 
 
2. Return each country, one by one (see the difference?):

 
USE hw5;
 
SELECT y as country FROM world x, x.mondial.country y;
 
 
3. Return just their codes and their names, orderd alphabetically. (Note that -car_code is not a legal field name since it includes a -, which means minus, so we must enclose the name in back quotes `…`.)

 
USE hw5;
 
SELECT y.`-car_code` as code, y.name as name
 
FROM world x, x.mondial.country y
 
ORDER BY y.name;
 
 
4. Here is a query that will produce an error because some provinces have a single city while others have lists of cities:

USE hw5;
 
SELECT z.name as province_name, u.name as city_name
 
FROM world x, x.mondial.country y, y.province z, z.city u
 
WHERE y.name = 'Hungary';
 
 
5. Try this version that fixes all cities to be lists:

 
USE hw5;
 
SELECT z.name as province_name, u.name as city_name FROM world x, x.mondial.country y, y.province z,
CASE WHEN is_array(z.city) THEN z.city ELSE [z.city] END u WHERE y.name = 'Hungary';

USE hw5;
 
SELECT z.name as province_name, (SELECT u.name FROM cities u)
 
FROM world x, x.mondial.country y, y.province z
 
LET cities = CASE WHEN z.city is missing THEN []  
WHEN is_array(z.city) THEN z.city
 
ELSE [z.city] END
 
WHERE y.name = 'Hungary';
 
 
Problems [24 points]  
Give a SQL++ command to perform each of the following queries. A correct answer for each question is worth 2 points.
 
1. Retrieve all the names of all cities located in Peru, sorted alphabetically. [30 rows]  
2. For each country return its name, its population, and the number of religions, sorted alphabetically by country. [238 rows]  
3. For each religion return the number of countries where it occurs. Order them in decreasing number of countries. [37 rows]  
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 x to a float or to an int.) [262 rows]  
5. Compute the list of all mountains, their heights, and the countries where they are located. Here you will join the "mountain" collection with the "country" collection, on the country code. You should return a list consisting of the mountain name, its height, the country code, and country name, in descending order of the height. [272 rows]  
6. Compute a list of countries with all their mountains. This is similar to the previous problem, but now you will group the mountains for each country. Your query should return a list where each element consists of the country code, country name, and a list of mountain names and heights. Order the countries by the number of mountains they contain. [238 rows]  
7. 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 its list of bordering seas, in decreasing order of the number of seas. [74 rows]  
8. Return all landlocked countries. A country is landlocked if it borders no sea. Order your answers in decreasing order of the country's area. (Note: this should be an easy query to derive from the previous one.) [45 rows]  
9. Find all pairs of countries that share both a mountain and a sea. Your query should return a list of pairs of country names. Avoid including a country with itself, like in (France,France). Also avoid