Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

createView

Creates a non-materialized view from the result of the specified aggregation pipeline. Views in SingleStore are non-writable and they act as read-only collections (tables). Refer to CREATE VIEW for more information.

This command is an extension of the MongoDB® createView command.

Syntax

db.createView (
<view_name>,
<view_source>,
<aggregation_pipeline, { $view: { json:true, inferred:true }}>,
<collation>
)

Arguments

  • view_name: Name of the view.

  • view_source: Name of the source collection or view.

  • aggregation_pipeline: An array that contains the aggregation pipelines.

  • $view: {json:true, inferred:true}: Creates structures (columns) that are optimal for SQL queries and improves the performance of SQL queries that reference the view.

    • json: Converts the documents or array fields added to the view to JSON type instead of BSON type.

    • inferred: Infers the data type of each field added to the view and creates a top-level column with the inferred type in the view.

  • collation (Optional): The default collation for the view.

Example

The following example creates a view using the $view: {json:true, inferred:true} option and then uses the view in SQL statements.

Create a view via the MongoDB® endpoint. The following commands create a collection and then use the collection as the source to create a view.

db.exampleCollection.insertMany( [
{ _id: 1, Code: "xv1f", Qty: 45 },
{ _id: 2, Code: "nm3w", Qty: 30 },
{ _id: 3, Code: "qoma", Qty: 20 },
{ _id: 4, Code: "hr3k", Qty: 15 } ] )
db.createView(
"exampleView",
"exampleCollection",
[{ $match: { Qty: { $gt: 25 } } }, {$view:{json:true, inferred:true}}]
)
db.exampleView.find()
[ { _id: 1, Code: 'xv1f', Qty: 45 },
  { _id: 2, Code: 'nm3w', Qty: 30 } ]
db.exampleView.find({Qty:{$gt:40}})
[ { _id: 1, Code: 'xv1f', Qty: 45 } ]

Run the following commands via the SQL endpoint.

DESC exampleView;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| _more_1  | bson       | YES  |     | NULL    |       |
| $_id     | longblob   | YES  |     | NULL    |       |
| _id      | bson       | NO   |     | NULL    |       |
| doc      | JSON       | YES  |     | NULL    |       |
| doc.Code | longtext   | YES  |     | NULL    |       |
| doc.Qty  | bigint(21) | YES  |     | NULL    |       |
| doc._id  | bigint(21) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+

This command shows that a top-level column is added for each field in the view.

The following command queries the view using SQL:

SELECT `doc._id` '_id', `doc.Code` 'Code', `doc.Qty` 'Qty'
FROM exampleView
WHERE `doc.Qty` > 40;
+-----+------+-----+
| _id | Code | Qty |
+-----+------+-----+
|   1 | xv1f |  45 |
+-----+------+-----+

The following SQL statement shows information on how this query is executed:

EXPLAIN SELECT `doc._id` '_id', `doc.Code` 'Code', `doc.Qty` 'Qty'
FROM exampleView
WHERE `doc.Qty` > 40;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Gather partitions:all alias:remote_0 parallelism_level:segment                                                                                                    |
| Project [BSON_EXTRACT_BIGINT(exampleView._id) AS _id, BSON_EXTRACT_STRING(exampleView._more,'Code') AS Code, BSON_EXTRACT_BIGINT(exampleView._more,'Qty') AS Qty] |
| ColumnStoreFilter [BSON_EXTRACT_BIGINT(exampleView._more,'Qty') > 40 AND BSON_EXTRACT_BIGINT(exampleView._more,'Qty') > 25]                                       |
| ColumnStoreScan dbTest.exampleCollection AS exampleView, SORT KEY __UNORDERED () table_type:sharded_columnstore                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Last modified:

Was this article helpful?