*[8. Updating the table data](#8-updating-the-table-data)
## 1. Setting your environment
In this section we'll look at how to get MonetDB working with hotmapper.
#### Important:
The MonetDB Apr2019 (11.33.3) version should be avoided, because it has a bug when inserting into a table with multiple constraints. It's already fixed in their nightly build so you should be fine using any superior version or the August2018 one.
#### Getting MonetDB
You can get your MonetDB installation following [their instructions](https://www.monetdb.org/Downloads).
Be sure to create and start your farm, if you didn't already, using:
~~~Bash
$ monetdbd create /path/to/my-dbfarm
$ monetdbd start /path/to/my-dbfarm
~~~
If you didn't set a systemctl command for your farm remember to start it every time you reboot your OS.
To create a new database, and remove it from maintenance mode(to avoid a very high memory usage):
~~~Bash
$ monetdb create hotmapper-demo
$ monetdb release hotmapper-demo
~~~
#### Getting HOTMapper dependencies
Let's create a new virtual environment and install the requirements.
Be sure that you have installed pip3 and python 3.5+.
Install python3-venv, if you don't have it, using the command bellow.
~~~Bash
$ sudo apt-get install python3-venv
~~~
Inside the hotmapper folder execute the following commands:
~~~Bash
$ python3 -m venv env
$ source env/bin/activate
$ pip install -r requirements.txt
~~~
## 2. Adjusting your HOTMapper Settings
Now that you have a working monetdb database it's needed to verify the HOTMapper settings to be sure it'll work with it.
First, using your favourite editor, open the file settings.py contained inside your HOTMapper folder.
Set the DATABASE variable to yours.
DATABASE = 'hotmapper_demo'
Verify the locations of your mapping protocols folder and table definitions. If you cloned the master repository it
should already be correct and the folders already exist.
MAPPING_PROTOCOLS_FOLDER = 'mapping_protocols'
TABLE_DEFINITIONS_FOLDER = 'table_definitions'
## 3. Creating your table definition
The HOTMapper tool uses two files to create a table, a json inside the table_definitions folder and a csv present on the
mapping_protocols folder. Both should have as name of the file: "table name + extension". Ex: table_test.json,
table_test.csv.
The table_definitions json has the objective of storing the table constraints and information about the source of the
table and it's description.
Let's create a json for a table_test:
* Go to the table_definitions folder and create a file named table_test.json.
* Open the file with your favourite editor and create the base object as shown bellow.
~~~json
{
"pairing_description":"A string with the description of what this table stores.",
"data_source":"A string with the source of the data.",
"pk":["A list with the primary key columns."],
"foreign_keys":["A list with the foreign key columns."]
}
~~~
* Let's fill the json with the information of table_test:
~~~json
{
"pairing_description":"Table with the graduation rates, which represent a percentage of students expected to graduate, used to learn the HOTMapper Tool",
"data_source":"Open data made by Sustainable Development Solutions Network",
"pk":["cod_country","year","gender","isced"],
"foreign_keys":[]
}
~~~
## 4. Creating your mapping protocol
The map protocol file is used to store the information about the table columns and to make the link between the data
from the csv and the database. This file is stored inside the mapping_protocols folder and uses as name the "table name
\+ .csv"
This csv must contain the following columns:
* Var. Lab -- An unique identifier for the column, this represents a specific column for the HOTMapper and shouldn't
be changed after table creation.
* Novo Rótulo -- A description of this column.
* Nome Banco -- The name of the column in the database.
* Tipo de Dado -- Type of the column in the database.
*\*[YEAR] - Eg.: 2010, 2011, ... -- A temporal identifier to help insertion of data from various years, which can have a different mapping
from the standard one. It has the name of the column in the data csv.
Let's create a mapping protocol for the table_test:
* First create a file named table_test.csv inside the mapping_protocols folder.
* Now, in the row number 1, create the following headers for the columns A-G: Var.Lab, Novo Rótulo, Nome Banco,
Tipo de Dado, 2016.
* If you have any doubt, inside your mapping_protocols folder should have a file named empty_map_protocol.csv
which you can use as a model.
For this example, we'll insert data with the graduation rates as percentage of students expected to graduate, the file
is EAD_GRAD_RATES_2016.csv which is inside the folder: hotmapper/open_data/.
Data taken from the Organisation for Economic Co-operation and Development. (https://stats.oecd.org/Index.aspx?datasetcode=EAG_GRAD_ENTR_RATES#) accessed in 2019-08-26
Let's create the columns.
* First, let's fill the protocol for the cod_country.
* In Var.Lab we'll use as an unique identifier: CODECOUNTRY
* Novo Rótulo: Code of the country
* Nome Banco: cod_country
* Tipo de Dado: VARCHAR(3)
* 2016: COUNTRY
* Now let's follow a similiar formula for the columns: Country, Year, Value, ISC11_LEVEL_CAT, SEX. You should have
