Example of Virtual Tables vs. Columnar Views
Note:
- This feature is not supported for Couchbase and DocumentDB.
- The following example uses arrays from MongoDB. Columnar views are supported for all forms of array data structures, so the concepts in this example are also applicable to other array data structures such as lists, maps, and sets from DynamoDB.
Because arrays are not natively supported in standard ODBC and JDBC applications, the Schema Editor renormalizes arrays by mapping them to a supported format.
Array data can be renormalized into virtual tables or columnar views. In virtual tables, each row contains one array element. In columnar views, each column contains one array element. These columns are not created in the database. Rather, they are generated at query time based on the data structure specified in the schema definition.
The following example shows a comparison between a virtual table of arrays and a columnar view of the same arrays.
Consider the following array data in a collection named testscores:
{
"grades": [
{ "course": "BIOL100", "grade": "A", "score": 96 },
{ "course": "CHEM100", "grade": "A", "score": 88 },
{ "course": "PHYS100", "grade": "B", "score": 75 },
{ "course": "MATH100", "grade": "B", "score": 72 },
{ "course": "ENGL100", "grade": "C", "score": 60 }
],
"name": "John Doe",
"student_id": "11875445"
}
{
"grades": [
{ "course": "BIOL100", "grade": "A", "score": 86},
{ "course": "CHEM100", "grade": "B", "score": 78},
{ "course": "PHYS100", "grade": "A", "score": 85},
{ "course": "MATH100", "grade": "A", "score": 90}
],
"name": "Jane Doe",
"student _id": "11812340"
}
By default, your Schema Editor connector renormalizes arrays into virtual tables. The following two tables show the base table and virtual table that would be created in the schema definition.
| _id | name | student_id |
|---|---|---|
|
1111 |
John Doe |
11875445 |
|
2222 |
Jane Doe |
11812340 |
| _id | testscores_grades_dim1_idx | course | grade | score |
|---|---|---|---|---|
|
1111 |
0 |
BIOL100 |
A |
96 |
|
1111 |
1 |
CHEM100 |
A |
88 |
|
1111 |
2 |
PHYS100 |
B |
75 |
|
1111 |
3 |
MATH100 |
B |
72 |
|
1111 |
4 |
ENGL100 |
C |
60 |
|
2222 |
0 |
BIO100 |
A |
86 |
|
2222 |
1 |
CHEM100 |
B |
78 |
|
2222 |
2 |
PHYS100 |
A |
85 |
|
2222 |
3 |
MATH100 |
A |
90 |
To create a columnar view of the score array, you would select the score column, click Move to Parent, and then specify 5 as the number of elements to move. The Schema Editor creates columns in the base table containing up to 5 elements from each score array. The following table shows what the base table looks like after the columnar view is created. The virtual table remains unchanged.
| _id | name | student_id | score_0 | score_1 | score_2 | score_3 | score_4 |
|---|---|---|---|---|---|---|---|
|
1111 |
John Doe |
11875445 |
96 |
88 |
75 |
72 |
60 |
|
2222 |
Jane Doe |
11812340 |
86 |
78 |
85 |
90 |
|