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

介绍cassandra使用场景
如何基于cassandra建模
设计一个基于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) );