Training
Certification
Microsoft Certified: Azure Cosmos DB Developer Specialty - Certifications
Write efficient queries, create indexing policies, manage, and provision resources in the SQL API and SDK with Microsoft Azure Cosmos DB.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
APPLIES TO: Cassandra
Important
Materialized views in Azure Cosmos DB for Cassandra is currently in preview. You can enable this feature using the Azure portal. This preview of materialized views is provided without a service-level agreement. At this time, materialized views are not recommended for production workloads. Certain features of this preview may not be supported or may have constrained capabilities. For more information, see supplemental terms of use for Microsoft Azure previews.
Materialized views, when defined, help provide a means to efficiently query a base table (or container in Azure Cosmos DB) with filters that aren't primary keys. When users write to the base table, the materialized view is built automatically in the background. This view can have a different primary key for efficient lookups. The view will also only contain columns explicitly projected from the base table. This view will be a read-only table.
You can query a column store without specifying a partition key by using secondary indexes. However, the query won't be effective for columns with high or low cardinality. The query could scan through all data for a small result set. Such queries end up being expensive as they end up inadvertently executing as a cross-partition query.
With a materialized view, you can:
Materialized views have many benefits that include, but aren't limited to:
Create new API for Cassandra accounts by using the Azure CLI to enable the materialized views feature either with a native command or a REST API operation.
Sign in to the Azure portal.
Navigate to your API for Cassandra account.
In the resource menu, select Settings.
In the Settings section, select Materialized View for Cassandra API (Preview).
In the new dialog, select Enable to enable this feature for this account.
The API for Cassandra uses a materialized view builder compute layer to maintain the views.
You get the flexibility to configure the view builder's compute instances based on your latency and lag requirements to hydrate the views. From a technical stand point, this compute layer helps manage connections between partitions in a more efficient manner even when the data size is large and the number of partitions is high.
The compute containers are shared among all materialized views within an Azure Cosmos DB account. Each provisioned compute container spawns off multiple tasks that read the change feed from base table partitions and writes data to the target materialized view[s]. The compute container transforms the data per the materialized view definition for each materialized view in the account.
Create a materialized view builder to automatically transform data and write to a materialized view.
Sign in to the Azure portal.
Navigate to your API for Cassandra account.
In the resource menu, select Materialized Views Builder.
On the Materialized Views Builder page, configure the SKU and number of instances for the builder.
Note
This resource menu option and page will only appear when the Materialized Views feature is enabled for the account.
Select Save.
Once your account and Materialized View Builder is set up, you should be able to create Materialized views using CQLSH.
Note
If you do not already have the standalone CQLSH tool installed, see install the CQLSH Tool. You should also update your connection string in the tool.
Here are a few sample commands to create a materialized view:
First, create a keyspace name uprofile
.
CREATE KEYSPACE IF NOT EXISTS uprofile WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'datacenter1' : 1 };
Next, create a table named user
within the keyspace.
CREATE TABLE IF NOT EXISTS uprofile.USER (user_id INT PRIMARY KEY, user_name text, user_bcity text);
Now, create a materialized view named user_by_bcity
within the same keyspace. Specify, using a query, how data is projected into the view from the base table.
CREATE MATERIALIZED VIEW uprofile.user_by_bcity AS
SELECT
user_id,
user_name,
user_bcity
FROM
uprofile.USER
WHERE
user_id IS NOT NULL
AND user_bcity IS NOT NULL PRIMARY KEY (user_bcity, user_id);
Insert rows into the base table.
INSERT INTO
uprofile.USER (user_id, user_name, user_bcity)
VALUES
(
101, 'johnjoe', 'New York'
);
INSERT INTO
uprofile.USER (user_id, user_name, user_bcity)
VALUES
(
102, 'james', 'New York'
);
Query the materialized view.
SELECT * FROM user_by_bcity;
Observe the output from the materialized view.
user_bcity | user_id | user_name
------------+---------+-----------
New York | 101 | johnjoe
New York | 102 | james
(2 rows)
Optionally, you can also use the resource provider to create or update a materialized view.
There are a few limitations with the API for Cassandra's preview implementation of materialized views:
WHERE
clause, only IS NOT NULL
filters are currently allowed.ALTER TABLE ADD
operations aren't allowed on the base table’s schema. ALTER TABLE APP
is allowed only if none of the materialized views have selected *
in their definition.SELECT * FROM
or has the UDT in one of projected columns, UDT updates aren't permitted on the account.In addition to the above limitations, consider the following extra limitations:
enableMaterializedViews
is set to true on the account.enableMaterializedViews
on their restored account before creating the materialized views and builders again.Training
Certification
Microsoft Certified: Azure Cosmos DB Developer Specialty - Certifications
Write efficient queries, create indexing policies, manage, and provision resources in the SQL API and SDK with Microsoft Azure Cosmos DB.