CQL3入门与Cassandra 2.2及以上版本数据操作详解

需积分: 10 151 浏览量更新于2024-07-21 收藏 4.99MB PDF 举报

CQL for Cassandra 2.2及后续版本是Cassandra数据库的官方查询语言文档，发布于2015年12月17日。该文档详细介绍了Cassandra 2.2及更高版本中Cassandra Query Language (CQL)的使用方法和数据模型设计。CQL是Cassandra数据库的核心交互工具，用于执行查询、创建和管理数据。 **CQL数据模型介绍：** CQL3支持丰富的数据建模概念，包括简单的键值对存储、复合分区键和复合主键。用户可以使用它来定义表结构，如单个列族（ColumnFamily）的简单主键（仅包含一个或多个列名作为键），复合分区键（使用多个字段作为键的一部分），以及复合主键（涉及两个或更多键组件）。 **操作指南：** 文档首先介绍了如何启动CQL shell，如在Linux、Mac OS X和Windows系统上的启动步骤。对于初次使用者，创建和更新键空间是基础操作，包括设置复制因子，这对于确保数据分布和一致性至关重要。 **表的创建与修改：** 创建表时，可以指定不同的数据类型，例如使用COMPACT STORAGE选项来优化存储效率。当表结构发生冲突时，有专门的解决机制。此外，文档还涵盖了如何创建materialized view（预编译视图），这允许对数据进行聚合和分析，提升查询性能。 **高级数据类型和功能：** CQL3支持创建复杂的数据结构，如集合、映射（Map）和元组（Tuple）。用户定义类型（User-Defined Types, UDTs）允许自定义数据结构，而用户定义函数（UDFs）和用户定义聚合函数（UDA）则提供了自定义业务逻辑的能力。插入和更新数据的语法和最佳实践也在这一部分详述。 **实践应用：** 无论是初级开发者还是经验丰富的数据工程师，这份文档都能提供深入理解CQL3的重要工具。通过学习和掌握CQL3，用户可以有效地管理Cassandra集群中的数据，优化查询性能，并根据业务需求灵活设计数据模型。 Cassandra CQL3文档是理解和操作Cassandra数据库不可或缺的参考资料，涵盖从基础语法到高级特性的方方面面，有助于用户高效地构建和管理大规模分布式数据存储系统。

Using CQL

one column for the partition key breaks the data into chunks, or buckets. The data is still grouped, but

in smaller chunks. This method can be effective if a Cassandra cluster experiences hotspotting, or

congestion in writing data to one node repeatedly, because a partition is heavily writing. Cassandra is often

used for time series data, and hotspotting can be a real issue. Breaking incoming data into buckets by

year:month:day:hour, using four columns to route to a partition can decrease hotspots.

Data is retrieved using the partition key. Keep in mind that to retrieve data from the table, values for all

columns defined in the partition key have to be supplied, if secondary indexes are not used. The table

shown uses race_year and race_name in the primary key, as a composition partition key. To retrieve data,

both parameters must be identified.

To create a table having a composite partition key, use the following method:

• Insert the PRIMARY KEY keywords after the last column definition in the CREATE TABLE definition,

followed by the column names of the partition key. The column names for the composite partition key

are enclosed in double parentheses.

Cassandra stores an entire row of data on a node by partition key. If you have too much data in a partition

and want to spread the data over multiple nodes, use a composite partition key.

Using a composite partition key

Use a composite partition key to identify where data will be stored.

Use a composite partition key in your primary key to create columns that you can query to return

sorted results. This example creates a rank_by_year_and_name table storing the ranking and name of

cyclists who competed in races. The table uses race_year and race_name as the columns defining the

composition partition key of the primary key. The query discovers the ranking of cyclists who competed in

races by supplying year and race name values.

A composite partition key table can be created in two different ways, as shown.

Procedure

• Create the table rank_by_year_and_name in the cycling keyspace. Use race_year and race_name

for the composite partition key. The table definition shown has an additional column rank used in the

primary key. Before creating the table, set the keyspace with a USE statement. This example identifies

the primary key at the end of the table definition. Note the double parentheses around the first two

columns defined in the PRIMARY KEY.

cqlsh> USE cycling;

CREATE TABLE rank_by_year_and_name (

race_year int,

race_name text,

cyclist_name text,

rank int,

PRIMARY KEY ((race_year, race_name), rank)

);

Using CQL

• The keyspace name can be used to identify the keyspace in the CREATE TABLE statement instead of

the USE statement.

cqlsh> CREATE TABLE cycling.rank_by_year_and_name (

race_year int,

race_name text,

cyclist_name text,

rank int,

PRIMARY KEY ((race_year, race_name), rank)

);

Compound Primary Key

A compound primary key consists of a partition key that determines which node stores the data and of clustering column(s) which determine the order of the data on the partition.

For a table with a compound primary key, Cassandra uses a partition key that is either simple or

composite. In addition, clustering column(s) are defined. Clustering is a storage engine process that sorts

data within each partition based on the definition of the clustering columns. Normally, columns are sorted

in ascending alphabetical order. Generally, a different grouping of data will benefit reads and writes better

than this simplistic choice.

Remember that data is distributed throughout the Cassandra cluster. An application can experience

high latency while retrieving data from a large partition if the entire partition must be read to gather a

small amount of data. On a physical node, when rows for a partition key are stored in order based on the

clustering columns, retrieval of rows is very efficient. Grouping data in tables using clustering columns is

the equivalent of JOINs in a relational database, but are much more performant because only one table is

accessed. This table uses category as the partition key and points as the clustering column. Notice that for

each category, the points are ordered in descending order.

Retrieving data from a partition is more versatile with clustering columns. For the example shown, a query

could retrieve all point values greater than 200 for the One-day-races.

To create a table having a compound primary key, use the following method:

• Insert the PRIMARY KEY keywords after the last column definition in the CREATE TABLE definition,

followed by the column name of the key. The column name is enclosed in parentheses.

Using a compound primary key

Use a compound primary key to create columns that you can query to return sorted results.

Use a compound primary key to create columns that you can query to return sorted results. If our pro

cycling example was designed in a relational database, you would create a cyclists table with a foreign

key to the races. In Cassandra, you denormalize the data because joins are not performant in a distributed

system. Later, other schema are shown that improve Cassandra performance. Collections and indexes are

two data modeling methods. This example creates a cyclist_category table storing a cyclist's last name,

ID, and points for each type of race category. The table uses category for the partition key and points

for a single clustering column. This table can be queried to retrieve a list of cyclists and their points in a

category, sorted by points.

Using CQL

A compound primary key table can be created in two different ways, as shown.

Procedure

• To create a table having a compound primary key, use two or more columns as the primary key. This

example uses an additional clause WITH CLUSTERING ORDER BY to order the points in descending

order. Ascending order is more efficient to store, but descending queries are faster due to the nature of

the storage engine.

cqlsh> USE cycling;

CREATE TABLE cyclist_category (

category text,

points int,

id UUID,

lastname text,

PRIMARY KEY (category, points)

) WITH CLUSTERING ORDER BY (points DESC);

• The keyspace name can be used to identify the keyspace in the CREATE TABLE statement instead of

the USE statement.

cqlsh> CREATE TABLE cyclist_category (

category text,

points int,

id UUID,

lastname text,

PRIMARY KEY (category, points)

) WITH CLUSTERING ORDER BY (points DESC);

Note: The combination of the category and points uniquely identifies a row in the cyclist_category

table. More than one row with the same category can exist as long as the rows contain different

pointsvalues. Consider the example again; is the data modeling for storing this data optimal? Under

what conditions might errors occur?

Creating a counter table

A counter is a special column for storing a number that is changed in increments.

Counters are useful for many data models. For example, a company may wish to keep track of the number

of web page views received on a company website. Scorekeeping applications could use a counter to keep

track of the number of games played online or the number of players who have joined a game. The table

shown uses id as the primary key and keeps track of the popularity of a cyclist based on thumbs up/down

clicks in the popularity field of a counter table.

Tracking count in a distributed database presents an interesting challenge. In Cassandra, the counter

value will be located in the Memtable, commit log, and one or more SSTables at any given moment.

Replication between nodes can cause consistency issues in certain edge cases. Cassandra counters were

redesigned in Cassandra 2.1 to alleviate some of the difficulties. Read "What’s New in Cassandra 2.1:

Better Implementation of Counters" to discover the improvements made in the counters.

A counter is a special column used to store a number that is changed in increments. A counter can be

decremented or incremented in integer values. Because counters are implemented differently from other

columns, counter columns can only be defined in dedicated tables. The counter data type is used to set

Using CQL

the data type. Do not assign this type to a column that serves as the primary key or partition key. Also, do

not use the counter type in a table that contains anything other than counter types and the primary key.

Explicitly, the only non-counter columns in a counter table must be part of the primary key.

In Cassandra 2.1 and later, you can configure many counter-related settings in the cassandra.yaml file.

Note: The replicate_on_write table property used by the Cassandra 2.0.x counter implementation has

been removed from Cassandra 2.1.

A counter column cannot be indexed or deleted.. To load data into a counter column, or to increase or

decrease the value of the counter, use the UPDATE command. Cassandra rejects USING TIMESTAMP or

USING TTL when updating a counter column.

To create a table having one or more counter columns, use this:

• Use CREATE TABLE to define the counter and non-counter columns. Use all non-counter columns as

part of the PRIMARY KEY definition.

Using a counter

A counter is a special column for storing a number that is changed in increments.

To load data into a counter column, or to increase or decrease the value of the counter, use the UPDATE

command. Cassandra rejects USING TIMESTAMP or USING TTL in the command to update a counter

column.

Procedure

• Create a table for the counter column.

cqlsh> USE cycling;

CREATE TABLE popular_count (

id UUID PRIMARY KEY,

popularity counter

);

• Loading data into a counter column is different than other tables. The data is updated rather than

inserted.

UPDATE cycling.popular_count

SET popularity = popularity + 1

WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

• Take a look at the counter value and note that popularity has a value of 1.

SELECT * FROM cycling.popular_count;

• Additional increments or decrements will change the value of the counter column.

Create table with COMPACT STORAGE

Create table using legacy (Thrift) storage engine format.

When you create a table using compound primary keys, for every piece of data stored, the column name

needs to be stored along with it. Instead of each non-primary key column being stored such that each

column corresponds to one column on disk, an entire row is stored in a single column on disk. If you need

to conserve disk space, use the WITH COMPACT STORAGE directive that stores data in the legacy

(Thrift) storage engine format.

CREATE TABLE sblocks (

block_id uuid,

subblock_id uuid,

data blob,

PRIMARY KEY (block_id, subblock_id)

Using CQL

)

WITH COMPACT STORAGE;

Using the compact storage directive prevents you from defining more than one column that is not part of

a compound primary key. A compact table using a primary key that is not compound can have multiple

columns that are not part of the primary key.

A compact table that uses a compound primary key must define at least one clustering column. Columns

cannot be added nor removed after creation of a compact table. Unless you specify WITH COMPACT

STORAGE, CQL creates a table with non-compact storage.

Collections and static columns cannot be used with COMPACT STORAGE tables.

Table schema collision fix

How to fix schema collision problems.

Dynamic schema creation or updates can cause schema collision resulting in errors.

Procedure

1. Run a rolling restart on all nodes to ensure schema matches. Run nodetool describecluster on

all nodes. Check that there is only one schema version.

2. On each node, check the data directory, looking for two directories for the table in question. If there is

only one directory, go on to the next node. If there are two or more directories, the old table directory

before update and a new table directory for after the update, continue.

3. Identify which cf_id (column family ID) is the newest table ID in system.schema_columnfamilies.

The column family ID is fifth column in the results.

$ cqlsh -e "SELECT * FROM system.schema_column_families" |grep <tablename>

4. Move the data from the older table to the newer table's directory and remove the old directory. Repeat

this step as necessary.

5. Run nodetool refresh.

Creating a materialized view

How to create CQL materialized views.

In Cassandra 3.0 and later, a materialized view is a table that is built from another table's data with a new

primary key specified. In Cassandra, queries are optimized by primary key definition and often there is a

table per query. If a new query is desired, a new table is created. Previously, these additional tables were

created and updated manually in the client application. Materialized views update and delete values when

the original table is updated and deleted.

Whereas secondary indexes which are suited for low cardinality data, materialized views are suited for high

cardinality data. Secondary indexes on high cardinality data require all nodes in a cluster to be queried,

causing high read latency. With materialized views, the data is arranged serially based on the new primary

key in a new table. Materialized views will cause hotspots if low cardinality data is inserted.

To create a materialized view, certain requirements must be met.

• The columns of the original table's primary key must be part of the materialized view's primary key.

• Only one new column may be added to the materialized view's primary key.

The following table is the original, or base, table for the materialized views that will be built.

CREATE TABLE cyclist_mv (cid UUID PRIMARY KEY, name text, age int, birthday

date, country text);

剩余171页未读，继续阅读

oracle_liuchao

粉丝: 0
资源: 66

CQL3入门与Cassandra 2.2及以上版本数据操作详解

cdrs：以Rust语言编写的Cassandra DB本机客户端。 在https：github.comAlexPikalovcdrstreev.1.x上找到1.x版本。寻找异步版本吗？ -检查WIP https：github.comAlexPikalovcdrs-async

cassandra-rs —绑定到DataStax C / C ++客户端-Rust开发

log-service-wrust:使用Rust将日志服务存储到Cassandra DB中

cassandra如何通过cql查询表大小

Cassandra 4.x 版本怎么启动cassandra

windows上Cassandra 4.x 版本怎么启动cassandra

Cassandra 4.x 文件夹中没有bat文件，如何启动cassandra

python调用CQL

cassandra安装

cassandra数据库连接工具

最新资源

cdrs：以Rust语言编写的Cassandra DB本机客户端。在https：github.comAlexPikalovcdrstreev.1.x上找到1.x版本。寻找异步版本吗？ -检查WIP https：github.comAlexPikalovcdrs-async