Mapping Protocols

Updated with v1.1.

Mapping Protocols is a csv file used to map the columns of a data csv to a database. On it you can choose a name for the column, it's type and do a direct mapping of a column in the data csv or execute CASEs and basic SQL functions.

This file needs to have the same name of the table you want to create/edit + '.csv', be separated by ',' (comma) and stored inside the MAPPING_PROTOCOLS_FOLDER by default hotmapper/mapping_protocols, if you want to store in another place you can change this on the settings.py file.

For any changes on this file, after the creation of the table you're editing, you'll need to execute the 'remap' command.

The format of the mapping protocol is of a simple csv file with 5 columns:

Var.Lab

An unique identifier for the column, this represents a specific column for the HOTMapper.

If this value is changed after the creation of the table, it'll be considered that the column was deleted and another as created on it's place, so be careful when doing any modifications on this column. If you want to change the name of the column in the database, the value you need to change is the one presents on the 'Nome Banco' column.

Novo Rótulo

This column is used to store a description of what the database column stores. It's very useful to write a short description so other peoples can understand the mapping better.

Nome Banco

The name of the column in the database.

Changes in this column after the table creation will be considered a rename if the value in the Var.Lab column doesn't change.

Tipo de Dado

Type of the column in the database. Can't be changed by HOTMapper after table creation.

*[YEAR] - Eg.: 2010, 2011, ...

Here's the column where the connection between the database and the column csv or other tables is done.

The name of this column is a temporal identifier, since the mapping can vary from an year to another, you can make various of those columns. The name that you put here is the one you need to pass as a parameter during the execution of a Hotmapper command.

You can fill the data here in three ways:

• Directly Mapping:

  If you put a column present in the header of the data csv an one for one mapping will be done (each row you contain exactly the same data from the csv base.).

  You can also put a column from another table by writing (~other_table_name.column). if there's an year column or a foreign key column, both will be used as WHERE clauses.

• Case Mapping:

  You can also do CASEs here, by putting the '~'(tilde) as the first character.

  The case can have as variables:

  • Columns from the header(data csv),
  • Columns from the current table (the name of the column in the database),
  • Columns from another table if there's an year column or a foreign key column, both will be used as WHERE clauses during the execution of the CASE. The column name of the other table needs to be preceded by the other table name (Eg.: other_table.collum).

  The format to execute a case mapping is: ~CASE WHEN "var" THEN "X" WHEN... ELSE "Y" END.

• Simple functions:

  HOTMapper can also execute some functions from SQL that have only one parameter, in the same syntax as SUM(column). As with the CASE, it's needed to be preceded by a ''(tilde).

  Differently of all the other methods of mapping, this function will not be executed during an insertion or update. To execute it is needed to run the run_aggregations command.

Bellow you can see an example of a filled mapping protocol, this file comes with hotmapper, inside of hotmapper/mapping_protocols and named example_mapping_protocols.csv. There's also an empty mapping protocol to be used as based named empty_map_protocol.csv