Understanding and configuring a schema map for OData Version 2
As described in Configuring data sources for OData Version 2 connectivity, you use the Hybrid Data Pipeline dashboard's Configure Schema editor to generate or edit a schema map. The schema map specifies the tables, or objects, and columns that will be accessible to OData clients for a particular data source definition. A schema map can only include tables from one schema. To expose tables from multiple schemas (in the same data store) or to expose multiple data stores in a single OData endpoint, you can create a data source group.
Hybrid Data Pipeline generates schema maps as a JSON string. When fetching data to satisfy requests, the Hybrid Data Pipeline OData service uses this schema to map a row in a table (or an object instance) to an entity, and to map the data in table columns (or object attributes) to entity properties. Progress recommends that you use the generated schema map. However, there are rare use cases that might require you to edit the JSON string. See JSON schema map syntax for a description of the syntax.
Primary and foreign keys
The schema map must specify how to uniquely identify a particular record. Many data store tables already have one or more primary key columns. The Configure Schema editor checks for a primary key in the tables you select, and identifies all tables that need to have a primary key defined. If a primary key is defined on a table, the OData service uses that primary key as the unique identifier and you cannot specify another. To expose tables that do not have a primary key, you must choose one or more columns to use as a virtual primary key. Hybrid Data Pipeline automatically adds related tables for selected foreign key columns.
Note: Although the Configure Schema editor lets you specify which tables and columns to expose to OData requests, it makes no change in the underlying data source. All columns of the data source are still available to SQL queries executed from the ODBC driver, JDBC driver, or the Hybrid Data Pipeline SQL Editor regardless of whether they are exposed through OData.
Entity names
In some cases, you might want to modify the names that the Configure Schema editor assigns to an entity.
By default, the Hybrid Data Pipeline OData service uses a plural form of the table name as the entity name. The schema generator automatically appends es to table names. For example, a data source table named Customers will become a Customerses entity. You might want to explicitly set the name to Customers.
If you are using a data source group, table names in the member data sources can conflict. Therefore, when you create a data source group, you must assign a unique prefix to each data source definition. When this is the case, it makes sense to use the same plural name for the tables in each schema map.
Queries must have the prefix appended to the plural entity name with an underscore separator. For example, two data sources in the same group might contain a Customer table. In the Configure Schema editor, you could assign the plural name Customers to the tables in both schemas. In the data source group, you could use a prefix such as east for one member and west for the other. Query requests to the east_Customers entity will then go to the first data source, and requests to west_Customers to the second.
By default, the Hybrid Data Pipeline OData service uses a plural form of the table name as the entity name. The schema generator automatically appends es to table names. For example, a data source table named Customers will become a Customerses entity. You might want to explicitly set the name to Customers.
