Virtual Tables
You can configure the Informatica MongoDB ODBC driver to create virtual tables in the schema if the collection contains arrays.
Virtual tables depict the renormalized view of a MongoDB collection. You can import virtual tables as an ODBC data object and create mappings.
To configure virtual table creation, open the Informatica MongoDB ODBC Driver DSN. In the Schema Definition dialog box, click Virtual Table Options.
If you enable virtual table creation, the driver creates the following virtual tables:
- Main virtual table
- The main virtual table contains all the data from the original MongoDB collection except the data in the arrays. The driver replaces the cells that contain arrays with the number of arrays in the cell.
- The main virtual table use the following naming convention by default: <original collection name>_vt_main
- The columns that contain arrays use the following naming convention by default: Number of <original column name>
- Virtual table for array columns
- The driver creates a virtual table for each column that contain arrays.
- The virtual table for an array column uses the following naming convention by default:<original collection name>_vt_<original column name>
- Each virtual table has a key column that references back to the primary key column in the original collection. The key column uses the following naming convention by default: <original collection name>.<primary key column name>.
- The virtual table has an index column that shows the position of the data within the original array. The index column uses the following naming convention by default: <original column name>.index
- Other columns in the virtual table represent the elements in the array and are named after the array element. If the array is of scalar type, the data column uses the following naming convention by default:<original column name>.value
Note: You cannot use a DD_DELETE strategy in an Update Strategy transformation to delete rows from a virtual table. You also cannot use the MongoDB ODBC driver to add an array element to an existing array index because of a limitation from the C API used by the MongoDB driver.
Virtual Table Options
Configure the virtual table options to create virtual tables for a collection that contains arrays.
The following table describes the virtual table options in the Informatica MongoDB ODBC driver:
Property | Description |
|---|
Enable Virtual Table Detection | The driver creates virtual tables if the collection contains arrays. Default is disabled. |
Virtual Main Table Suffix | The suffix for the main virtual table. Default is main. |
Virtual Key Column Separator | The separator for the key columns in a virtual table. The virtual key column separator must be consistent across connections used in a mapping. For example, if one connection uses the period (.) as the virtual key column separator and another connection in the same mapping uses the underscore (_) as the separator, the mapping fails. You must use the underscore (_) as the virtual key column separator. |
Virtual Table Name Separator | The separator in the virtual table name. Default is _vt_. Note: If tables in the MongoDB database and virtual tables have the same names, metadata import might be corrupted. To avoid importing corrupted metadata, do not use table names that contain the virtual table separator in the MongoDB database. |
Virtual Table Index Column Suffix | The suffix for the virtual table index column. Default is index. |
Hide Real Table if Virtual Tables Created | Hide the real tables if the corresponding virtual tables are created. Default is disabled. |
Show Array Counts In Virtual Main Table | The virtual tables contain columns that show the array count. Default is disabled. |
Virtual Table Array Count Prefix | The prefix for the virtual table array count column. Default is Number of. |
Enable Any Match Columns Detection | The driver filters the data and selects rows where a value in a top-level array matches a specified expression and then returns the results as columns in a virtual table. |
Any Match Table Name Prefix | The prefix for naming the array column in an any match virtual table. |
Any Match Column Separator | The separator for naming the columns in an any match virtual table. |
Virtual Tables - An Example
The collection CustomerTable contains arrays. You want to create virtual tables from the arrays and import the virtual tables as data objects in Informatica Cloud.
The following table shows the schema of CustomerTable collection:
id | Customer Name | Invoices | Service Level | Contacts | Ratings |
|---|
1111 | John | [{invoice_id=123,item=toaster, price=456,discount=0.2}, {invoice_id=124,item=oven, price=12345, discount=0.3}] | Silver | [{type=primary,name="John Johnson"}, {type=invoicing,name="Jane Johnson"}] | [7,8] |
2222 | Jane | [{invoice_id=125,item=blender, price=7456,discount=0.5}, | Gold | [{type=primary,name="Jane Johnson"} | [5,6] |
If you enable virtual table detection, the driver creates the following virtual tables:
- CustomerTable_vt_main
- The following table shows the schema of CustomerTable_vt_main virtual table:
id | Customer Name | Number of Invoices | Service Level | Number of Contacts | Number of Ratings |
|---|
1111 | John | 2 | Silver | 2 | 2 |
2222 | Jane | 1 | Gold | 1 | 2 |
- CustomerTable_vt_Invoices
- The following table shows the schema of CustomerTable_vt_Invoices virtual table:
CustomerTable.id | Invoices_index | invoice_id | item | price | discount |
|---|
1111 | 1 | 123 | toaster | 456 | 0.2 |
1111 | 2 | 124 | oven | 12345 | 0.3 |
2222 | 1 | 125 | blender | 7456 | 0.5 |
- CustomerTable_vt_Contacts
- The following table shows the schema of CustomerTable_vt_Contacts virtual table:
CustomerTable.id | Contacts_index | type | name |
|---|
1111 | 1 | primary | John Johnson |
1111 | 2 | invoicing | Jane Johnson |
2222 | 1 | primary | Jane Johnson |
- CustomerTable_vt_Ratings
- The following table shows the schema of CustomerTable_vt_Ratings virtual table:
CustomerTable.id | Ratings_index | Ratings_value |
|---|
1111 | 1 | 7 |
1111 | 2 | 8 |
2222 | 1 | 5 |
2222 | 2 | 6 |