Skip to main content

Mappings

This section is about Timbr SQL Data Definition Language (DDL) queries to create and edit mappings.

Create or replace mapping​

Create or replace a concept mapping

Required information for creating or replacing a concept mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {mapping_name} - The name of the mapping you want to create
  • {target_concept} - The name of the concept associated with the mapping
  • {table_column} - The table column to be mapped (can also be a calculated column)
  • {property_of_concept} - The property of the column associated with the table column (or calculated column)
  • {source_database} - The source database from which the mapping will occur
  • {source_table} - The source table from which the mapping will occur
CREATE OR REPLACE MAPPING `{mapping_name}` INTO (`{target_concept}`) [USING {datasource_name}] [WITHOUT PARSING]
    AS 
        SELECT `{table_column}` AS `{property_of_concept}`
        FROM {source_database}.{source_table};

{} - Mandatory

[] - Optional

Optional arguments in create mapping

The USING <datasource_name> optional arguement in the SQL statement refers to which datasource will be used for the mapping.

If the WITHOUT PARSING optional argument is set, Timbr will not parse the SQL statement of the mapping and it will push it down to the datasource as-is.

Child concept mapping

In cases where the concept being mapped to has one or more sub-concepts (children), or if you intend to create sub-concepts, be aware that you don't need to set another mapping in the hierarchy for the sub-concepts since they inherit the mapping of the upper-level (parent) concept automatically. Setting a mapping for both upper level concepts as well as their children concepts, will result in a duplication of the data.

For more information and examples read here.

Create or replace many-to-many mapping​

Create or replace a many-to-many mapping to a concept. Many to many mappings are usually created between two concepts, or a concept and itself, where the mapping goes through an EAV table (Entity-Attribute-Value table) which stores the information about the relationship between two tables.

Required information for creating or replacing a many-to-many mapping to a concept (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {mapping_name} - The name of the mapping you want to create
  • {relationship_name} - The many-to-many relationship name you want to create as it will appear in this concept queries and metadata
  • {relationship_property} - The property you want to add to the relationship that will appear in this concept queries and metadata
  • {property type} - The type of property being added to the relationship
  • {eav_column1} - The table column from the EAV table to be mapped to the source concept
  • {source_concept} - The name of the source concept associated with the many-to-many relationship
  • {source_concept_property} - The property from the source concept to be related to the property of the target concept
  • {inverse_relationship_name} - The many-to-many relationship name as it will appear from the target concept context
  • {eav_column2} - The table column from the EAV table to be mapped to the target concept
  • {target_concept} - The target concept to which the many-to-many relationship points to
  • {target_concept_property} - The property in the target concept which is related to the property in the source concept
  • {eav_database} - The source database from which the mapping will occur
  • {eav_table} - The source table from which the mapping will occur
CREATE OR REPLACE MAPPING `{mapping_name}` ({relationship_property} {property_type},
    CONSTRAINT `{relationship_name}`
        FOREIGN KEY (`{eav_column1}`)
        REFERENCES `{source_concept}` (`{source_concept_property}`), 
    CONSTRAINT `{inverse_relationship_name}`
        FOREIGN KEY (`{eav_column2}`)
        REFERENCES `{target_concept}` (`{target_concept_property}`)) [USING {datasource_name}] [WITHOUT PARSING]
    AS 
        SELECT `{eav_column1}` AS `{source_concept_property}`,
            `{eav_column2}` AS `{target_concept_property}`
        FROM `{eav_database}`.`{eav_table}`;

{} - Mandatory

[] - Optional

Optional arguments in create mapping

The USING <datasource_name> optional arguement in the SQL statement refers to which datasource will be used for the mapping.

If the WITHOUT PARSING optional argument is set, Timbr will not parse the SQL statement of the mapping and it will push it down to the datasource as-is.

Querying a relationship property

In order to query a relationship property in the SQL editor or from any other endpoint, users must use the dtimbr schema which exposes relationships. Once loading a concept's metadata, the relationship properties will appear with "_" (underscore) before the property name indicating that this is a property of the many-to-many relationship and not the property of the target concept which is identified with a dot before the property name.

The syntax of a relationship property appears as follows:

{relationship_name}[{target_concept}]_{target_concept_property}

Create or replace multi-value mapping​

Create or replace a multi-value mapping

Required information for creating or replacing a multi-value mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {mapping_name} - The name of the mapping you want to create
  • {added_property} - The property to be added from the multi-value table
  • {added_property_type} - The data type of the property to be added from the multi-value table
  • {mv_table_column} - The related column of the table to be mapped from the mediary property of the concept
  • {target_concept} - The name of the concept associated with the mapping
  • {mediary_property} - The related property of the concept to be mapped to the mediary table column
  • {added_column} - The table column (can also be a calculated column) to be mapped to the added property
  • {mv_database} - The source database from which the mapping will occur
  • {mv_table} - The source table from which the mapping will occur
CREATE OR REPLACE MAPPING `{mapping_name}` (`{added_property}` `{added_property_type}`, 
    FOREIGN KEY (`{mv_table_column}`) 
    REFERENCES `{target_concept}`(`{mediary_property}`)) [USING {datasource_name}] [WITHOUT PARSING]
    AS 
        SELECT `{mv_table_column}` AS `{mediary_property}`,
            `{added_column}` AS `{added_property}` 
        FROM `{mv_database}`.`{mv_table}`;

{} - Mandatory

[] - Optional

Optional arguments in create mapping

The USING <datasource_name> optional arguement in the SQL statement refers to which datasource will be used for the mapping.

If the WITHOUT PARSING optional argument is set, Timbr will not parse the SQL statement of the mapping and it will push it down to the datasource as-is.

Show SQL create mapping statement​

Required information for show create mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {mapping_name} - The name of the mapping you want to show a create statement for. 
SHOW CREATE MAPPING `{mapping_name}`

Remove a mapping​

Removes a mapping from an ontology.

Required information for removing a mapping from the ontology (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {mapping_name} - The name of the mapping you want to remove
DROP MAPPING `{mapping_name}`;