Connections
1. Overview
The Connections functionality in Mitra allows you to import data from CSV files or a JDBC connection, either to create new tables or to update existing tables in the system.
1.1. Use Cases for Connections
Connections can be used to build new tables or update existing ones.
Creating a New Table: You can create a table directly from a CSV import or by connecting to an external database. This is done in the table creation area located in the top-left corner of the screen. In this area, you can choose to create a table manually or by connecting through CSV files or a JDBC connection.
Before starting the creation process, it is necessary to define the type of ID the table will use, choosing from the options: Auto Increment, Numeric, or Text. This configuration is essential for defining how each row will be uniquely identified. Choosing the correct ID type ensures an organized structure and facilitates future interactions with the table, such as filtering and external data loads.
Updating an Existing Table: When you are inside a table, in the top-right corner, you can see the list of existing connections for that table and add a new connection. By creating a new connection, you can add new columns and rows to the existing table.
2. Connections for Single Key Tables
When creating a connection for a single key table, on the left side of the modal, you will see a preview of the data, which can come from a CSV file or a JDBC Query. On the right side is the summary, where the connection configuration is made.
2.1. Column Configuration
First Column (ID) and Second Column (Description): For single key tables where the ID is of type INT or VARCHAR, the first column will always be the ID and the second will be the description. For tables with an auto-incremental ID, the first column will always be the description, as the ID will be generated automatically.
Column Choices: For each column in your dataset, you can choose one of three options:
New Column: Add the column as a new one in the table. It can be Text, Numeric, Date, or a Foreign Key (FK).
Ignore: Do not include this column in the table.
FK Description: If you have columns functioning as foreign keys, such as “Requester ID” and “Requester Description,” you can define “Requester ID” as a new FK column, while “Requester Description” is configured as the description of that FK. Thus, the columns will be correctly associated, and for each new FK defined, a separate table will be automatically created containing the ID and the configured description. This allows each foreign key to have its own reference table, making data management and linking easier within the system.
Existing Column: Associate the column with an existing one in the table. Note: This option will only be available when editing an existing table, as when creating a new table, there will be no pre-existing columns.
2.2. Additional Options
Data Source: You can change the data source by clicking the “Data source” button, switching between CSV and SQL as needed.
Connection Scheduling: If you choose to import data from an external database via SQL, you can schedule the connection to run at specific times using a CRON code, enabling automations such as hourly or daily executions.
Row Addition Criteria: When configuring the connection for an existing table, you can define if the data load should add only new rows or if it can update existing rows. Note: This criterion will only be available when updating an existing table, as when creating a new table, all rows will be new.
3. Connections for Composite Key Tables
When creating a connection for a composite key table, the column configuration process differs from single key tables. In a composite key table, where multiple columns can act as keys, these keys can be in any position in the dataset. For each column, you can choose one of the following options:
New Column: If you define a column as new, you can choose if it will be a Foreign Key (FK). If it is an FK, you can create the FK record at the time of configuration or link it to an existing FK.
FK Description: Additionally, you can mark a column as the description of an FK, linking it to the corresponding FK.
Example: Suppose you have two columns, “Sales Manager ID” and “Sales Manager Description.” You can mark “Sales Manager ID” as a new FK column and “Sales Manager Description” as the description of that FK, linking it to the “Sales Manager ID.”
Ignore: This option allows you to discard columns that will not be used in the table.
Existing Column: Use this option if you want to map a column from the dataset to an existing column in the destination table. Note: This option will only be available when editing an existing table, as when creating a new table, there will be no pre-existing columns.
3.1. Time-Type Column Configuration
In composite key tables, only one time-type column (which includes day, month, quarter, or year) can be used. If your dataset includes more than one temporal column, you will need to select which one to use. Additionally, you must define the date granularity and the appropriate format for loading the data.
3.2. Numeric Column Configuration
Note: A numeric data type can be configured as a numeric attribute or a Foreign Key (FK). A numeric attribute is used when the value will be treated as something to be summed or calculated, such as "sales value" or "tuition value." An FK is used to represent a relationship, such as "customer code" or "product code," as marking it as an FK links this column to a target record, creating a reference between tables. Numeric columns will be treated as numeric attributes within your dataset. These columns represent the metrics of your dataset.
3.3. Additional Options
Data Source: You can change the data source by clicking the “DATA SOURCE” button, switching between CSV and SQL as needed.
Connection Scheduling: If you choose to import data via SQL, you can schedule the connection to run at specific times using a CRON code, enabling automations such as hourly or daily executions.
3.4. Additional Load Options
When configuring the connection, you also have the option to decide how the data will be loaded into the composite key table:
Clear All Data Before Loading: This option removes all data from the table before importing the new dataset.
Use One of the FKs as a Replacement Key: If you want to add new data without completely resetting the table, you can choose to use one of the FKs as a replacement key. In this case, you will select a specific FK within the composite key table. The system will only clear records that match the keys found in the new dataset before reloading data for those specific keys.
