Cassandra uses the structure to build tables, query | August more challenging

preface

List store, your strength is your weakness, and your weakness is still your list.

Environment to prepare

Cassandra is already built by default

Create key space

Example:

CREATE KEYSPACE one WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

CREATE KEYSPACE two WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 3} AND durable_writes = false;
Copy the code

The CREATE KEYSPACE statement has two properties: replication and durable_WRITES and NetworkTopologyStrategy

Two strategies: SimpleStrategy and NetworkTopologyStrategy

The simple difference between the two policies is that the single data center uses the simple policy and the multi-data center uses the network topology policy. In this case, the simple policy is to use the key space as one

Create table and partition

Create table device_sensor_smoke (unique_id text, time_id timeuuid, # temperature double, primary key (unique_id, (time_id, event_id)) #Copy the code

There are three ways to select a primary key

1.  unique_id               text   PRIMARY KEY, 
2.  primary key (unique_id, time_id, event_id)
3.  primary key (unique_id, time_id), event_id)
Copy the code

If you have a primary key(a), you will find no clustering columns. If you have a primary key(a), you will find no clustering columns
Primary key (a, b,c) primary key (a, b,c
Primary key (a, b), c)compositePartition key) event_id is the cluster column

In a table, CQL defines the concept of partitioning. A partition is simply a set of rows that share the same partition key value. Note that if the partitioning key consists of multiple columns, the rows belong to the same partition and only they have the same value for all these partitioning key columns.

For example, to define the table definition and content:

CREATE TABLE t (
    a int,
    b int,
    c int,
    d int,
    PRIMARY KEY ((a, b), c, d)
);

SELECT * FROM t;
   a | b | c | d
  ---+---+---+---
   0 | 0 | 0 | 0    // row 1
   0 | 0 | 1 | 1    // row 2
   0 | 1 | 2 | 2    // row 3
   0 | 1 | 3 | 3    // row 4
   1 | 1 | 4 | 4    // row 5
Copy the code

Row1 and Row2 are in the same partition. This explains why our item table uses unique_id as the first primary key, because unique_id has a lot of duplications and Cassandra will query in order according to the unique_id.

CREATE TABLE t (
    a int,
    b int,
    c int,
    PRIMARY KEY (a, b, c)
);

SELECT * FROM t;
   a | b | c
  ---+---+---
   0 | 0 | 4     // row 1
   0 | 1 | 9     // row 2
   0 | 2 | 2     // row 3
   0 | 3 | 3     // row 4
Copy the code

Because of this feature, we will have a large number of duplicate unique_id as the first digit of the primary key, which corresponds to the a in the table above.

So the data in our table will be ordered if we use unique values such as time_id. As the first primary key. Each individual value would be an extent, and the final result would be out of order.

The query

As we’ve seen from the above constructor statement, CQL has strict rules. This feature is also reflected in the query statement that creates the following tables

CREATE TABLE posts (
    userid text,
    blog_title text,
    posted_at timestamp,
    entry_title text,
    content text,
    category int,
    PRIMARY KEY (userid, blog_title, posted_at)
)
Copy the code

No data is inserted into the table, then do the following query

SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND blog_title='John''s Blog'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'
Copy the code

I get (I didn’t insert any data)

 entry_title | content
-------------+---------

(0 rows)
Copy the code

Make the following query after that

SELECT entry_title, Content FROM posts WHERE userid = 'John doe' AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31 InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "posted_at" cannot be restricted as preceding column "blog_title" is not restricted"Copy the code

If you’re familiar with SQL, you don’t see anything wrong with the second query at first glance, and that’s if the first query succeeds. However, we find that the primary key structure is (userID, blog_title, posted_at), the first query order is the same as the primary key, and the second query is only userid, posted_at.

Userid and posted_at are not contiguous on the primary key, so an error will be reported. Cassandra is allowed to “filter”, but also consider spatial continuity.

select * from posts ; Select * from entry_title where entry_title =" CQL ";Copy the code

The above two can be passed.

but

Select * from entry_title =" CQL "and category = 1;Copy the code

This query will be rejected because entry_title and category are not contiguous, so Cassandra has no guarantee that a large amount of data will be scanned even if few conditions are met.

But it can be enforced with ALLOW FILTERING, as can the invalidated queries above. So in order for these two queries to work, you need to write something like this.

select * from entry_title ="How to use CQL"and  category  = 1  ALLOW FILTERING  ;

SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND posted_at > = '2012-01-01' AND posted_at < '2012-01-31'  ALLOW FILTERING ;
Copy the code

The sorting

In fact, any Cassandra query, the final result is ordered, the default is the same as when the table was created (if not specified, the default is ascending, refer to the above t table).

But if there is a special ordering requirement it can be met.

That said, Cassandra supports custom ordering, but with some limitations

Create the following table

create table teacher(
    id int,
    address text,
    name text,
    age int,
    height int,
    primary key(id,address,name)
)WITH CLUSTERING ORDER BY(address DESC, name ASC);
Copy the code

Insert the following data

Insert into the teacher (id, address, name, age, height) values (1, 'guangdong', 'lixiao, 32172); Insert into the teacher (id, address, name, age, height) values (1, 'guangxi', 'linzexu, 68178); Insert into the teacher (id, address, name, age, height) values (1, "guangxi", "lihao", 25178); Insert into the teacher (id, address, name, age, height) values (2, 'guangxi', 'lixiaolong, 32172); Insert into the teacher (id, address, name, age, height) values (2, 'guangdong', 'lixiao, 32172); Insert into the teacher (id, address, name, age, height) values (2, 'guangxi', 'linzexu, 68178); Insert into the teacher (id, address, name, age, height) values (2, "guangxi", "lihao", 25178); Insert into the teacher (id, address, name, age, height) values (2, 'guangxi', 'NND, 32172);Copy the code

From the above table building and query routines, we also know that all the limitations must come from hot men.

So we directly on the statement, to feel the column storage in what monster.

Correct example 1:

SELECT * FROM teacher WHERE id=1 ORDER BY address ASC;
SELECT * FROM teacher WHERE id=1 ORDER BY address ASC, name ASC;
SELECT * FROM teacher WHERE id=1 AND address='guangxi' ORDER BY address ASC;
SELECT * FROM teacher WHERE id=1 AND address='guangxi' ORDER BY address ASC, name ASC;
SELECT * FROM teacher WHERE id=1 ORDER BY address DESC;
SELECT * FROM teacher WHERE id=1 ORDER BY address DESC, name DESC;
SELECT * FROM teacher WHERE id=1 AND address='guangxi' ORDER BY address DESC;
SELECT * FROM teacher WHERE id=1 AND address='guangxi' ORDER BY address DESC, name DESC;
Copy the code

Correct example 2:

SELECT * FROM tt WHERE id=1 ORDER BY address DESC;
SELECT * FROM tt WHERE id=1 ORDER BY address DESC, name ASC;
SELECT * FROM tt WHERE id=1 AND address='guangxi' ORDER BY address DESC;
SELECT * FROM tt WHERE id=1 AND address='guangxi' ORDER BY address DESC, name ASC;
SELECT * FROM tt WHERE id=1 ORDER BY address ASC;
SELECT * FROM tt WHERE id=1 ORDER BY address ASC, name DESC;
SELECT * FROM tt WHERE id=1 AND address='guangxi' ORDER BY address ASC;
SELECT * FROM tt WHERE id=1 AND address='guangxi' ORDER BY address ASC, name DESC;
Copy the code

Error example display

SELECT * FROM teacher ORDER BY address DESC; SELECT * FROM teacher WHERE id=1 ORDER BY name DESC; SELECT * FROM teacher WHERE id=1 ORDER BY address DESC, name ASC; SELECT * FROM teacher WHERE age=1 ORDER BY address DESC; SELECT * FROM teacher WHERE age=1; SELECT * FROM tt WHERE id=1 ORDER BY address DESC, name DESC; Address DESC, name ASC, address DESC, name ASC, address DESCCopy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Cassandra uses the structure to build tables, query | August more challenging

preface

Environment to prepare

Create key space

Create table and partition

The query

The sorting

Recommended reading/references for this article

Cassandra uses the structure to build tables, query | August more challenging

preface

Environment to prepare

Create key space

Create table and partition

The query

The sorting

Recommended reading/references for this article

Related Posts

Dynamic programming chapter 5:01 knapsack problem and complete knapsack problem

Centos7.5 Docker Installation Offline and Container Running OCI Runtime CREATE Failed Locate and resolve the problem

ConcurrentHashMap no problem