DbSchema Database Designer

DbSchema | Cassandra - How to Alter a Materialized View?



Table of Contents

  1. Introduction
  2. Prerequisites
  3. Restrictions on Altering a Materialized View
  4. Altering Materialized View in cqlsh
  5. Altering Materialized View in DbSchema
  6. Conclusion
  7. References

Introduction

Cassandra is a highly scalable and distributed NoSQL database known for its ability to handle massive amounts of data across multiple commodity servers. Materialized views in Cassandra provide denormalized views of data to support efficient querying. They are precomputed and stored physically on disk, making query execution faster.

In this article, we will explore how to alter materialized views in Cassandra using both __cqlsh (Cassandra Query Language shell) and __DbSchema. We’ll discuss the prerequisites, restrictions, and various aspects of altering materialized views such as modifying table properties, compression, compaction, and caching.

Prerequisites

Before proceeding with altering materialized views, ensure that you have the following prerequisites:

  • Knowledge of __Cassandra data modeling and the concept of __materialized views.

To know more about materialized view refer to Cassandra-How to create a materialized view?

  • Access to a running Cassandra cluster with appropriate privileges.
  • Familiarity with __cqlsh (Cassandra Query Language shell) or __DbSchema, depending on your preferred method.

Restrictions on Altering a Materialized View

There are a few restrictions to keep in mind when altering a materialized view in Cassandra:

  • You cannot __change the primary key or partition key` of a materialized view.
  • You cannot __add or remove columns` from a materialized view.
  • You cannot __change the clustering order` of a materialized view.

Altering Materialized View in cqlsh

  1. Open __cqlsh` and connect to your Cassandra cluster.
  2. Switch to the keyspace containing the materialized view using the __USE` statement. For example,
USE keyspace_name;
  1. Use the __ALTER MATERIALIZED VIEW` statement to modify the materialized view’s properties. Here’s the general syntax:
ALTER MATERIALIZED VIEW keyspace_name.view_name
WITH table_options = {
  'property1': 'value1',
  'property2': 'value2',
  ...
};

Replace __keyspace_name with the name of the keyspace where the materialized view is defined, and __view_name with the name of the materialized view you want to alter.

  1. Specify the desired table options within the __WITH table_options section. You can modify properties such as __caching, __compression, __compaction, and more.

  2. Execute the ALTER statement to apply the changes to the materialized view.

Modifying Table Properties

To alter the table properties of a materialized view, you can use the __ALTER MATERIALIZED VIEW statement in cqlsh or the corresponding feature in DbSchema. The table properties include options such as __bloom_filter_fp_chance, __caching, __comment, __compaction, __compression, and more.

Here’s an example of altering table properties in cqlsh:

ALTER MATERIALIZED VIEW keyspace_name.view_name
WITH table_options = {
  'caching': {
    'keys': 'ALL',
    'rows_per_partition': '100'
  },
  'compression': {
    'sstable_compression': 'DeflateCompressor',
    'chunk_length_in_kb': '64'
  }
};

In the above example, we __modify` the caching options to cache all keys and set the number of rows per partition to 100.

Additionally, we __change the compression to use the DeflateCompressor with a chunk length of __64 KB.

Modifying Compression and Compaction

Compression and compaction settings can significantly impact the storage and performance of materialized views in Cassandra.

Compression is the process of reducing the size of data on disk. Cassandra supports various compression algorithms such as LZ4Compressor, DeflateCompressor, and SnappyCompressor. You can modify the compression options as part of altering a materialized view.

Compaction refers to the process of merging and organizing data on disk to improve read and write performance. Cassandra provides different compaction strategies such as SizeTieredCompactionStrategy, DateTieredCompactionStrategy, and TimeWindowCompactionStrategy. You can also modify the compaction options while altering a materialized view.

Comparison Table B/w Compression and Compaction

Here’s a comparison table between compression and compaction in Cassandra:

Compression Compaction
__Definition` A technique used to reduce the size of stored data The process of merging and organizing SSTables
__Purpose` Reduce disk space usage and improve read performance Maintain data integrity and ensure efficient reads
__Operation` Compresses data before writing to disk Merges and consolidates SSTables on disk
Data Size Reduces the size of stored data on disk Does not directly affect the size of stored data
CPU Usage Increases CPU usage for compressing and decompressing Moderate CPU usage for compaction operations
Read Speed Decompression is required before reading data Read speed is unaffected by compaction
Write Speed Compression overhead can impact write speed Write speed can be affected during compaction
Configurability Compression algorithms and ratios can be configured Compaction strategies and thresholds can be set
Granularity Can be applied at the table, column family, or column level Operates at the SSTable level
Examples Snappy, LZ4, Deflate, and Gzip SizeTieredCompactionStrategy, LeveledCompactionStrategy, TimeWindowCompactionStrategy

Changing Caching

Caching in Cassandra helps improve read performance by storing frequently accessed data in memory. You can modify the caching options of a materialized view to control the caching behavior.

The caching options include keys, rows_per_partition, and save_period.

  • The keys option determines which keys to cache (e.g., ALL, NONE, or a specific number of keys).
  • The rows_per_partition option specifies the number of rows per partition to cache.
  • The save_period option defines the duration to save cached rows.

Altering Materialized View in DbSchema

  1. Open DbSchema and connect to your Cassandra database.
  2. Locate the keyspace containing the materialized view in the schema explorer.
  3. Expand the keyspace, and you should see the materialized views listed.
  4. Right-click on the materialized view you want to alter and select “Alter Table” or a similar option.
  5. In the Alter Table dialog, you can modify various properties of the materialized view, such as table options, compression, compaction, and caching.
  6. Make the desired changes to the properties and click the “Save” or “Apply” button to save the alterations.
  7. DbSchema will generate the appropriate ALTER statement and execute it against the Cassandra database to modify the materialized view.

Alter Materialized View and Visually Manage Cassandra using DbSchema

DbSchema is a Cassandra client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Alter Materialized View

  • Start the application and connect to the Cassandra database.
  • Located keyspace and right-click on the materialized view.
  • Select alter table option and make changing.

Conclusion

Altering materialized views in Cassandra allows you to modify table properties, compression, compaction, and caching options to optimize performance and storage. However, there are certain restrictions, such as not being able to change the primary key or add/remove columns.

In this article, we covered the process of altering materialized views using cqlsh and DbSchema, along with the prerequisites and restrictions. We discussed modifying table properties, compression, compaction, and caching options to fine-tune the behavior of materialized views.

Remember to carefully plan and test any changes to materialized views in a production environment to ensure the desired performance improvements.

References

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.