实战课堂-如何基于Cassandra建模

1 .- C* 陈江（恬泰） 2019.10 阿⾥里里云数据库部⾼高级专家 

2 .• why cassandra • 如何建模 • 关系型 • cassandra⽅方式 • 最佳实践 

3 .Why Cassandra SQL 

4 .Why Cassandra 

5 . Why Cassandra https://www.datastax.com/nosql-databases/benchmarks-cassandra-vs-mongodb-vs-hbase 

6 . Why Cassandra • 多语⾔言客户端⽀支持 http://cassandra.apache.org/doc/latest/getting_started/drivers.html 

7 . Why Cassandra Node1 Node1 Node1 node8 Node2 node3 node4 node7 node3 Node6 node4 Node2 Node2 Node5 

8 .关系型数据模型 deptId First Last • 5范式 1 Edgar Codd • Foreign 2 Raymond Boyce Keys • Joins id Dept 1 Engineering 2 Math 

9 .

10 .关系型数据模型 CREATE TABLE users ( id number(12) NOT NULL , firstname nvarchar2(25) NOT NULL , • Create entity lastname nvarchar2(25) NOT NULL, email nvarchar2(50) NOT NULL, table password nvarchar2(255) NOT NULL, created_date timestamp(6), PRIMARY KEY (id), • Add constraints CONSTRAINT email_uq UNIQUE (email) ); • Index fields CREATE TABLE videos ( id number(12), userid number(12) NOT NULL, name nvarchar2(255), • Foreign Key description nvarchar2(500), location nvarchar2(255), relationships location_type int, added_date timestamp, CONSTRAINT users_userid_fk FOREIGN KEY (userid) REFERENCES users (Id) ON DELETE CASCADE, PRIMARY KEY (id) ); 

11 .关系型数据模型 data Models Application 

12 .cassandra模型 Application Models data 

13 .基于cassandra建模-五步法 1. 创建应⽤用⼯工作流 2. 为查询建模 3. 建表 4. 正确设计主键 5. 使⽤用⾼高效数据类型 

14 .Killrvideo.com 

15 .1. build application workflow Users logs in Users selects video 

16 .Some application workflows in killrvideo tag video video video video video video 

17 .2. 

18 . workflows⽤到的查询 user id user email user Comments video video comment ( user Ratings video video ratings 

19 . workflows⽤到的查询 Video tag video tag video video video video id video video video 

20 . 3.建表 Entity Relationship user Comment tag Video video 

21 . 静态表 CREATE TABLE videos ( videoid uuid, userid uuid, name varchar, video description varchar, id video location text, location_type int, preview_thumbnails map<text,text>, tags set<varchar>, added_date timestamp, PRIMARY KEY (videoid) ); Partition key 

22 . 动态表 CREATE TABLE videos_by_tag ( tag text, videoid uuid, added_date timestamp, tag tag video name text, video preview_image_location text, tagged_date timestamp, PRIMARY KEY (tag, videoid) ); Partition key clustering 

23 . users功能 id user email user CREATE TABLE user_credentials ( CREATE TABLE users ( email text, userid uuid, password text, firstname text, userid uuid, lastname text, PRIMARY KEY (email) email text, ); created_date timestamp, PRIMARY KEY (userid) ); https://github.com/KillrVideo/killrvideo-data/blob/master/schema.cql 

24 .4. Get The Primary Key Right 

25 .Primary key relationship CREATE TABLE videos_by_tag ( tag text, videoid uuid, added_date timestamp, name text, preview_image_location text, tagged_date timestamp, PRIMARY KEY (tag, videoid) ); 

26 . IOT实战案例 • 字段：⻋车辆id, 采集时间，⻋车速， ⾥里里程， 总电压，总电 流， SOC，经度， 纬度。 • 需求1：根据⻋车辆id和时间范围，查找⼀一个⻋车⼀一段时间的数 据。 partition 100M partition Primary key(carid, timestamp) Primary key(carid, timestamp) …… 1 12 

27 . 5. Use Data Types Effectively CREATE TABLE videos ( 1 - Data Marshalling videoid uuid, userid uuid, 2 - Controlling Order name varchar, description varchar, location text, location_type int, preview_thumbnails map<text,text>, tags set<varchar>, added_date timestamp, PRIMARY KEY (videoid) ); 

28 . Controlling Order • 使⽤用clustering列列，注意控制⾏行行排序⽅方式 • 默认升序，可重写 INT VARCHAR DATE TIMESTAMP TIMEUUID 

29 .collections CREATE TABLE videos ( videoid uuid, userid uuid, name varchar, description varchar, location text, location_type int, preview_thumbnails map<text,text>, tags set<varchar>, added_date timestamp, PRIMARY KEY (videoid) );