The developers of ScyllaDB are working hard so that ScyllaDB will not only have unparalleled performance (see our benchmarks) and reliability, but also have the features that our users want or expect for compatibility with the latest version of Apache Cassandra.
The latest of these new features is Materialized Views, which will be an experimental feature in the upcoming ScyllaDB release 2.0. Because this feature is experimental, users are invited to try it in non-production environments. The initial implementation has limitations which are discussed at the end of this blog and will be addressed in later versions of ScyllaDB.
The Materialized Views feature was first introduced in Apache Cassandra 3.0. Material Views automated the tedious and inefficient chore where an application maintains several tables with the same data organized differently. As usual, the ScyllaDB version is compatible – in features and CQL syntax – with the Apache Cassandra version. In this post, we shortly introduce Materialized Views and how to use them in ScyllaDB followed by a summary of their limitations in this release.
What are Materialized Views?
In Apache Cassandra and ScyllaDB, data is divided into partitions which can be found by a partition key. Sometimes the application needs to find a partition or partitions by the value of another column. Doing this efficiently without scanning all of the partitions requires indexing.
Materialized Views is one of the three indexing options available in Apache Cassandra 3.0. The other two are “Secondary Index” and “SASI” (Sstable-Attached Secondary Index). The biggest drawback of the other two indexing methods is their non-scalability: They are local indexes, i.e., a node indexes the data it contains, so when a search is expected to yield just one (or few) partitions, we need to send it to all the nodes because we cannot know which one might hold the data. Materialized Views are different because they are not a local index but rather a global one. The index is a ScyllaDB table which is distributed to the different nodes using the normal table distribution mechanisms and scalable just like normal tables. Actually Materialized Views are more than just an index, they allow you to fetch some of the columns of the retrieved partition and not just its key.
People have been using Materialized Views, also calling them denormalization, for years even before it was added to Apache Cassandra (and now to ScyllaDB) as a server-side feature. In those days, the application maintained two or more views and two or more separate tables with the same data but under a different partition key. Every time the application wanted to write data, it needed to write to both tables, and reads were done directly (and efficiently) from the desired table. However, ensuring any level of consistency between the data in the two or more views required complex and slow application logic.
ScyllaDB’s Materialized Views feature moves this complexity out of the application and into the servers. The implementation is faster (fewer round trips to the applications) and more reliable. This approach makes it much easier for applications to begin using multiple views into their data. The application just declares the additional views (we’ll see how below), new view tables get created, and on every update to the original table (known as the “base table”), the additional view tables get automatically updated as well.
Be warned that Materialized Views are real tables, and does take up storage space.
Reads from a materialized view are just as fast as regular reads from a table and just as scalable. But as expected, updates to a table with Materialized Views are slower than normal updates since these updates need to update both the original table and the materialized view as well as to ensure the consistency of both updates. However, doing those in the application without server help would have been even slower.
Moreover, ScyllaDB’s superior performance often makes it acceptable for the user to use advanced but slower features like Materialized Views. This helps to improve the application’s data consistency and speed up its development.
Example
To enable experimental features in ScyllaDB including Materialized Views, add the “experimental: true” setting to the scylla.yaml configuration file, or using the “–experimental 1” option for ScyllaDB in Docker.
Let’s look at a table of buildings: the key is the building’s unique name and additional columns are each building’s city, year it was built, and its height in meters:
CREATE TABLE buildings ( name text, city text, built int, meters int, PRIMARY KEY (name) );
Now from this “base table”, let’s ask ScyllaDB to automatically maintain a second table which is a materialized view for finding buildings by the city. The new table will have the city as the partition key. The city cannot be the entire key for each record (a building) because we can have multiple buildings in the same city. Therefore we will have (city, name) as the primary key of the new materialized-view table: city is the partition key, and name is a clustering key:
CREATE MATERIALIZED VIEW building_by_city AS SELECT * FROM buildings WHERE city IS NOT NULL PRIMARY KEY(city, name);
And a second view which selects only some of the columns in the original table:
CREATE MATERIALIZED VIEW building_by_city2 AS SELECT meters FROM buildings WHERE city IS NOT NULL PRIMARY KEY(city, name);
Let’s insert a few famous buildings into this list. Each of these buildings was the tallest building in the world at the time it was built:
INSERT INTO buildings (name, city, built, meters) VALUES ('Burj Khalifa', 'Dubai', 2010, 828); INSERT INTO buildings (name, city, built, meters) VALUES ('Shanghai World Financial Center', 'Shanghai', 2008, 487); INSERT INTO buildings (name, city, built, meters) VALUES ('Taipei 101', 'Taipei', 2004, 449); INSERT INTO buildings (name, city, built, meters) VALUES ('Sears Tower', 'Chicago', 1974, 442); INSERT INTO buildings (name, city, built, meters) VALUES ('World Trade Center', 'New York City', 1972, 417); INSERT INTO buildings (name, city, built, meters) VALUES ('Empire State Building', 'New York City', 1931, 381); INSERT INTO buildings (name, city, built, meters) VALUES ('Chrysler Building', 'New York City', 1930, 283);
The table now looks like this:
SELECT * FROM buildings; name | built | city | meters --------------------------------+-------+---------------+-------- Chrysler Building | 1930 | New York City | 283 Burj Khalifa | 2010 | Dubai | 828 World Trade Center | 1972 | New York City | 417 Shanghai World Financial Center | 2008 | Shanghai | 487 Sears Tower | 1974 | Chicago | 442 Empire State Building | 1931 | New York City | 381 Taipei 101 | 2004 | Taipei | 449
One might observe in this example that the data (city, built) or just (built) could also have served as a key because it uniquely determines a building in this data set. However, ScyllaDB has no way to guarantee that this remains as more data is added. For example, one might add another building built in 1930 to the list (we’ll even do this below). The only key which is guaranteed to be unique and remain unique as additional data is added is the original table’s key. As a rule in a materialized view, all the components of the original primary key of the table MUST also appear in the materialized view’s key. This is why we added the name into the view’s key in this example.
The “WHERE city IS NOT NULL“ filter in the snippet above ensures that if a building includes a null value for city, it will not be added to the view table because doing so would be illegal (a key component either partition or clustering key cannot be null). Adding this filter is mandatory. If you don’t, you will not be able to create the materialized view:
CREATE MATERIALIZED VIEW building_by_city AS SELECT * FROM buildings PRIMARY KEY(city, name); InvalidRequest: code=2200 [Invalid query] message="Primary key column 'city' is required to be filtered by 'IS NOT NULL'"
As expected, the new materialized view looks like this:
SELECT * FROM building_by_city; city | name | built | meters ---------------+---------------------------------+-------+-------- New York City | Chrysler Building | 1930 | 283 New York City | Empire State Building | 1931 | 381 New York City | World Trade Center | 1972 | 417 Shanghai | Shanghai World Financial Center | 2008 | 487 Chicago | Sears Tower | 1974 | 442 Dubai | Burj Khalifa | 2010 | 828 Taipei | Taipei 101 | 2004 | 449
This view contains all the columns from the base table because of the “SELECT * ” command we used to define the view. We can also look at the second view which selected only some of the columns in the original table:
SELECT * FROM building_by_city2; city | name | meters ---------------+---------------------------------+-------- New York City | Chrysler Building | 283 New York City | Empire State Building | 381 New York City | World Trade Center | 417 Shanghai | Shanghai World Financial Center | 487 Chicago | Sears Tower | 442 Dubai | Burj Khalifa | 828 Taipei | Taipei 101 | 449
Unsurprisingly, the new view contains the key columns (city and name) but the only additional column selected is meters. The built column in the base table is not copied to the new view because it was not selected.
Although internally each materialized view is a separate table, a user is not allowed to modify a view directly:
DELETE FROM building_by_city WHERE city='Taipei'; InvalidRequest: code=2200 [Invalid query] message="Cannot directly modify a materialized view"
Limitations of the experimental release
In ScyllaDB 2.0, the Materialized Views support is experimental, with some pieces still missing:
- Currently, only new data written after the creation of a view will appear in that view. If you add a view to a table with existing data, a background process to copy that data to the new view is not yet started. The related “nodetool viewbuildstatus” command is also not supported.
- Repairs do not yet handle MV information.
- A “local batchlog” was not yet implemented. This means that if a node is rebooted in the middle of a materialized view update, some of the view table updates may be lost despite the client receiving a “success” response (the view-table write happens asynchronously just like in Apache Cassandra).
- Local locking was not yet implemented. This means that two performing concurrent updates to the same column in the base table is not reliable and may result in two view rows in the view table while in the base table there is only one value.
- While writes to tables with Materialized Views are ongoing, the cluster’s topology cannot be changed (i.e., nodes should not be added or removed).
We plan to complete those missing pieces by the next release. Meanwhile, users are invited to experiment with Materialized Views in ScyllaDB when 2.0 releases, but not to use it in production.
Apache®, Apache Cassandra®, are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.
