Menu
Simba Technologies
Simba Technologies

Simba MongoDB ODBC Driver with SQL Connector 2.2.3
Installation and Configuration Guide

Virtual Tables

One advantage of the MongoDB design is the ability to store data that is denormalized into a fewer number of tables. However, the ODBC interface does not natively support accessing denormalized data types such as arrays and objects. By expanding the data contained within arrays and objects into virtual tables, the Simba MongoDB ODBC Driver allows you to directly interact with the data but leave the storage of the data in its denormalized form in MongoDB.

If any columns are mapped to a denormalized data type during the sampling process, then the driver creates the following tables and saves them as part of the schema definition:

  • A base table, which contains only the normal data from the real table.
  • A virtual table for each column of denormalized data, expanding the nested data.

Virtual tables refer to the data in the real table, enabling the driver to access the denormalized data. By querying the virtual tables, you can access the contents of arrays and objects via ODBC. When you write or modify data in a virtual table, the data in the real table in the MongoDB database is updated.

The base table and virtual tables appear as additional tables in the list of tables that exist in the database, and are named using the following conventions:

  • The base table uses the same name as the real table that it represents.
  • In the ODBC layer, the name of each virtual table is formed using the name of the real table that contains the array or object, an underscore (_), and the name of the array or object.
  • In the MongoDB layer, the name of each virtual table is formed using the name of the collection that the data comes from, a period (.), and then the name of the array or object followed by a set of closed square brackets ([]) for each hierarchy level in which the array or object is nested.
  • If a virtual table or column has the same name as an actual table or column in the database, then the driver appends _1 to the virtual table or column name as a suffix. The number increments as necessary until the name is unique.

For example, consider the example MongoDB table named CustomerTable shown below.

_id Customer Name Invoices Service Level Contacts Ratings

1111

ABC

[{invoice_id: ”123”, item: ”toaster”, price: ”456”, discount: ”0.2”},
{invoice_id: ”124”, item: ”oven”, price: ”1235”, discount: ”0.2”}]

Silver

[{type: primary, name: "John Johnson"},
{type: invoicing, name: "Jill Jilliamson"}]

[5,6]

2222

XYZ

[{invoice_id: ”135”, item: ”fridge”, price: ”12543”, discount: ”0.0”}]

Gold

[{type: primary, name: "Jane Doe"}]

[1,2]

CustomerTable has two columns that have an array of Objects in each cell, Invoices and Contacts, and one column that has an array of Scalar types, Ratings. Multiple virtual tables would be generated for this single source table. The first table is the base table, which is shown below.

_id Customer Name Service Level

1111

ABC

Silver

2222

XYZ

Gold

The base table contains all of the data of the original table, but the data from the arrays has been omitted and will be expanded in the virtual tables.

The following three tables show the virtual tables that represent the original arrays in the example.

_id Invoices_dim1_idx invoice_id item price discount

1111

0

123

toaster

456

0.2

1111

1

124

oven

1235

0.2

2222

0

135

fridge

12543

0.0

_id Contacts_dim1_idx type name

1111

0

primary

John Johnson

1111

1

invoicing

Jill Jilliamson

2222

0

primary

Jane Doe

_id Ratings_dim1_idx Ratings_value

1111

0

5

1111

1

6

2222

0

1

2222

1

2

Each of these tables contain the following:

  • A reference back to the original primary key column corresponding to the row of the original array (via the _id column)
  • An indication of the position of the data within the original array (using the Invoices_dim1_idx, Contacts_dim1_idx, and Ratings_dim1_idx columns)
  • The expanded data for each element within the array:
    • Invoices: invoice_id, item, price and discount
    • Contacts: type and name
    • Ratings: Ratings_value

You can select, insert, and update data in the base tables and virtual tables as if they were standard relational tables, and the driver will handle the storage details within MongoDB.

For example, to append 7 to the Ratings array in the CustomerTable where _id = 1111, execute the following statement:

INSERT INTO CustomerTable_Ratings (_id, Ratings_value) VALUES (1111, 7)

Some operations may be processed differently or not supported for certain types of data. For more information, see Write-back.