When you create a topic in a relational database, you define the data structure on the publication repository where publications publish data to and subscriptions consume data from. The topic structure can consist of multiple tables.
Cloud Integration Hub generates the tables in the publication repository where it retains the data that is published for the topic. Cloud Integration Hub uses the data structure for the publications and subscriptions that are associated with the topic.
For example: Create a Sales topic that represents sales data. Applications from all the stores in the organization publish sales data to the Sales topic. The accounting application subscribes to the Sales topic and consumes published sales data from all stores, or, if a filter is applied, from specific stores.
Before you define publications and subscriptions for the data that is published and consumed in Cloud Integration Hub, you need to define the canonical structure that will hold the data that is published to Cloud Integration Hub in the Cloud Integration Hub publication repository. You define the canonical structure when you define the topic. You can define multiple topics that represent different source data sets.
Create topic tables in a relational database
You can use the following methods to create topic tables in a relational database:
•Create a table from a connection. Use this method when the structure of a table in the data domain that the topic represents exists in a connection object. You can use relational, flat file, JDBC_IC, JDBCV2, and Salesforce connections to create topic tables. The Add Table From Connection dialog box shows up to 200 tables. You can also search for an object from a connection.
•Create a table from a flat file. Use this method when the structure of a table in the data domain that the topic represents exists in a flat file.
•Create a new table. Use this method to define the structure manually if the structure of the table does not exist in a compatible file.
You can use more than one method to create tables in a single topic. For example, create two tables from a flat file, create three tables from a metadata file, and create a new table.
Using metadata files to create topic tables in a relational database
You can load a metadata file to Cloud Integration Hub and create a topic table in a relational database that is based on the structure of the file.
When you use a metadata file to create a topic table, you can define table attributes in the file before you load it to Cloud Integration Hub. For example, define column data type and precision, or define a column as a filter accelerator that is not encrypted.
You can use JSON, XML, XLS, and XLSX metadata files to create topic tables.
The metadata file must contain the following fields, and must not contain any other fields:
columnName
Mandatory. Name of the table column. The name must begin with an alphabetic character or underscore and can contain only alphanumeric characters or underscores.
filterAccelerator
Optional. Indicates that the column will be used in subscription queries and requires performance-related handling by Cloud Integration Hub. Use this indicator with topics that you plan to use for unbound subscriptions. By default, false.
When you use filter accelerators, consider the following guidelines:
- Filter accelerators slow down the writing of publication data to the Cloud Integration Hub publication repository.
- Filter accelerators have no impact on subscriptions that do not use filters.
- You can enable encryption for a new topic data on a hosted Cloud Integration Hub publication repository. To use a column as a filter accelerator, you must define the value of the column's encryption field to false.
datatype
Optional. Data type of the field. By default, string.
The file can contain fields of the following data types:
- String
- Decimal
- Double
- Int32
- Int64
- Date_time
- Text
precision
Optional. Applies to data types that support precision. The default precision value depends on the data type of the field:
- String: 255
- Decimal: 15
- Text: 10000
scale
Optional. Applies to data types that support data scaling. The default scale value depends on the data type of the field:
- Decimal: 0. If the decimal field isn't encrypted and the published value is less than the topic decimal scale value, Cloud Integration Hub adds zeros to the decimal value. For example, if you define a scale of 4 for a decimal of 15.4, Cloud Integration Hub publishes and consumes the data with a decimal of 15.4000.
- Date_time: 0. For example, if you define a scale of 2 for a date_time of 2022-02-04 00:40:23.946, Cloud Integration Hub rounds the date_time to 2022-02-04 00:40:23.950. Applies to a hosted publication repository.
- All other data types: empty
encryption
Optional. Applies to a hosted publication repository.
required
Optional. Applies to a relational topic table. If this option is set to true, ensure that the data is published to the required fields. If the published data doesn't meet the required mandatory fields criteria, the event fails.
If a file doesn't contain all the mandatory fields, loading the file to Cloud Integration Hub fails.
If a file contains identical rows, Cloud Integration Hub adds only the first row to the topic table.
When you edit the structure of a topic with associated publications or subscriptions in a relational database, it might affect the associated publications and subscriptions. Topic structure changes might also impact the data in the publication repository and sometimes cause data loss.
Based on the nature of the update, you might have to edit the associated publications and subscriptions to align with the updated topic structure. Topic updates require that no publication or subscription events be in Processing or Delayed status. Search for any publication or subscription events that are in Processing status on the Events page and change their status to Discarded before you update them. For more information, see Discarding all events in Processing status.
The following table describes the effects of topic structure updates on data in the publication repository and the resulting optional or required changes to the associated publications and subscriptions.
Optional/Required Changes to Associated Publications and Subscriptions
Add table
Table added
Optional: To publish and to consume the additional table, edit the mapping to include the additional table.
If you do not update the mapping, data in the table will not be published to the publication repository and subscribers will not receive it.
Delete table
Table deleted, including data that was published to the table
Remove references to the table from the mapping of publications and from the mapping and the filter of subscriptions.
Add column
Column added
Optional: To publish and to consume the additional column, edit the mapping to include the additional column.
If you do not update the mapping, data in the column will not be published to the publication repository and subscribers will not receive it.
Delete column
Column deleted, including data that was published to the column
Remove references to the column from the mapping of publications and from the mapping and the filter of subscriptions.
Rename column
Column deleted, including data that was published to the column, and another column created with new name
Remove references to the changed column from the mapping of publications and from the mapping and the filter of subscriptions.
Optional: To publish or to consume the column that is created with a new name, edit the mapping to include the new column.
If you do not update the mapping, data in the new column will not be published to the publication repository and subscribers will not receive it.
Change column data type
Column deleted, including data that was published to the column, and another column created with new data type
Remove references to the changed column from the mapping of publications and from the mapping and the filter of subscriptions.
Optional: To publish or to consume the column that is created with the new data type, edit the mapping to include the new column.
If you do not update the mapping, data in the new column will not be published to the publication repository and subscribers will not receive it.
Increase column precision, scale unchanged
Column updated
Open the publication or the subscription page for all associated publications and subscriptions. You do not need to edit any of the publication or subscription settings.
Increase column precision, increase scale by a lower value than the precision increase or by the same value as the precision increase
Column updated
Open the publication or the subscription page for all associated publications and subscriptions. You do not need to edit any of the publication or subscription settings.
Any other precision or scale updates
Column deleted, including data that was published to the column, and another column created with updated precision or scale
Remove references to the changed column from the mapping of publications and from the mapping and the filter of subscriptions.
Optional: To publish or to consume the column that is created with the new precision or the new scale, edit the mapping to include the new column.
If you do not update the mapping, data in the new column will not be published to the publication repository and subscribers will not receive it.
1 Deleting columns in the publication repository might take a long time, based on the number of rows in the table.