-
Notifications
You must be signed in to change notification settings - Fork 6
/
Copy path02-03_-_Data_Modeling_Cassandra-Land_Project_PART_2.studio-nb.tar
524 lines (458 loc) · 100 KB
/
02-03_-_Data_Modeling_Cassandra-Land_Project_PART_2.studio-nb.tar
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
notebook.bin 0100644 0000000 0000000 00000164403 13577206362 012142 0 ustar 00 0000000 0000000 json_notebook_v1 {"1":"690faf93-25d2-40f7-81db-b22a77f7b05b","10":"418ed742-cd61-4df8-abd8-07bc56a62e8d","11":"02-03 - Data Modeling: Cassandra-Land Project PART 2","12":{"1":1576864861,"2":744000000},"13":{"1":1576864896,"2":918000000},"14":false,"15":[{"1":"1b050bf7-d225-465d-a9fd-6f3a7286b4c6","10":4,"11":"<center><img src=\"//datastaxtraining.s3.amazonaws.com/developer-day/developer-day-studio-header.png\" width=\"800\"></center>\n\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n# <center><span style=\"color:navy\">Data Modeling: Cassandra-Land Project Part 2</span></center>","12":"markdown","13":{"1":"ddde6084-9ddb-4c2e-a087-57734e1d434d","10":{"9":"<p><center><img src=\" //datastaxtraining.s3.amazonaws.com/developer-day/developer-day-studio-header.png\" width=\"800\"></center></p>\n<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h1><center><span style=\"color:navy\">Data Modeling: Cassandra-Land Project Part 2</span></center></h1>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"eeffd3f7-c276-4789-b222-9cf1aa4625ac","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 1: Create the ride_list_by_location Table</span></center>\n<center></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n\nOur app needs a list of rides in the park that we can present to the user so the user can schedule rides.\nFor our current app, since there aren't too many rides in a theme park, the entire list of rides will easily fit into a single partition.\nBut what if we were dealing with billions of rides?\nIn that case, it would be necessary to divide the rides up in some way so that they don't all end up in one huge partition.\nWe'll use the ride location for this purpose.\nIn our example, all the rides will be in a single location, but we'll use this field to position us for future growth - we are certain that Cassandra-Land will catch on and every city in the world will need their own theme park.\n\nWe want to create a table that looks as follows:\n\nNote that `location` is the partition key.\n\nWe also need to use the ride_id as a clustering column to create uniqueness.\nOtherwise, rides in the same location will cause upserts.\nSee if you can formulate the CQL in the following cell to create this table.\n\n<span style=\"color:blue\">Hint:</span> The primary key will have `location` as the partition key and `ride_id` as a clustering column, so the primary key clause will look like `PRIMARY KEY((location), ride_id)`.","12":"markdown","13":{"1":"63ae7a58-0c5d-4b11-9685-6c9a427b2bb9","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 1: Create the ride_list_by_location Table</span></center></h3>\n<p><center><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/use_caseScheduleRide.jpg\" alt=\"Schedule Ride use case\" /></center>\n<br /><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<p>Our app needs a list of rides in the park that we can present to the user so the user can schedule rides.\n<br />For our current app, since there aren't too many rides in a theme park, the entire list of rides will easily fit into a single partition.\n<br />But what if we were dealing with billions of rides?\n<br />In that case, it would be necessary to divide the rides up in some way so that they don't all end up in one huge partition.\n<br />We'll use the ride location for this purpose.\n<br />In our example, all the rides will be in a single location, but we'll use this field to position us for future growth - we are certain that Cassandra-Land will catch on and every city in the world will need their own theme park.</p>\n<p>We want to create a table that looks as follows:\n<br /><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_list_by_location.jpg\" alt=\"ride-list_by_location\" />\n<br />Note that <code>location</code> is the partition key.</p>\n<p>We also need to use the ride_id as a clustering column to create uniqueness.\n<br />Otherwise, rides in the same location will cause upserts.\n<br />See if you can formulate the CQL in the following cell to create this table.</p>\n<p><span style=\"color:blue\">Hint:</span> The primary key will have <code>location</code> as the partition key and <code>ride_id</code> as a clustering column, so the primary key clause will look like <code>PRIMARY KEY((location), ride_id)</code>.</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"b66f8d06-0920-4a80-a025-ba018a57673f","11":"// Create the ride_list_by_location table here:\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"40d6f114-14c2-4b8b-9fc5-6154ad061996","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nCREATE TABLE cassandra_land.ride_list_by_location (\n location text,\n ride_id UUID,\n ride_name text,\n capacity int,\n PRIMARY KEY((location), ride_id)\n);\n```\n</details>","12":"markdown","13":{"1":"9c9f6d34-5ffa-4fc8-ae65-946b3309c793","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>CREATE TABLE cassandra_land.ride_list_by_location (\n location text,\n ride_id UUID,\n ride_name text,\n capacity int,\n PRIMARY KEY((location), ride_id)\n);\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"ddaac652-ce82-47de-845e-75a2d1d438c4","10":4,"11":"Let's make sure we created the table like we expected.\nUse the SCHEMA view in the top right hand corner to review the ride_list_by_location table. \nOnce you've done that we'll insert some rides in the table.\n\nExecute the following cell to insert the rides:","12":"markdown","13":{"1":"4f4e8d9c-52f5-4568-ad73-3db017ef60aa","10":{"9":"<p>Let's make sure we created the table like we expected.\n<br />Use the SCHEMA view in the top right hand corner to review the ride_list_by_location table.\n<br />Once you've done that we'll insert some rides in the table.</p>\n<p>Execute the following cell to insert the rides:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"726f6f2e-3fda-4dbf-9b06-93d44c1ba09f","11":"INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 'Fast Response Rollercoaster', 50);\nINSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 95aac1f5-c69c-444e-9645-b3e1ae913837, 'Always Up Ferris Wheel', 100);\nINSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop', 25);\nINSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain', 50);\nINSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All', 1000);\n\n// Note the use of pre-generated UUID values above. This is done here to make the rest of the exercises \n// more streamlined and easier to troubleshoot. In practice you would generate a UUID using\n// UUID() as you see in the statement below.\nINSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', UUID(), 'Single Rider Slingshot', 1);","12":"cql","16":true,"17":false,"18":{},"23":187,"25":"CL.ONE"},{"1":"9775e03c-4f74-4e01-8a37-7a72ced4ad6f","10":4,"11":"As the last part of this step, let's formulate the query we will use in the app to get the list of rides for the user.\nWe want to get all the rides in NYC.\nTry to create the query in the next cell:","12":"markdown","13":{"1":"e9f86a20-7cb4-4919-9198-34332796219e","10":{"9":"<p>As the last part of this step, let's formulate the query we will use in the app to get the list of rides for the user.\n<br />We want to get all the rides in NYC.\n<br />Try to create the query in the next cell:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"9588667b-b078-49cb-86d6-2ff2b09ee08f","11":"// Create a query to get all rides in NYC:\n","12":"cql","16":true,"17":false,"18":{},"22":312,"25":"CL.ONE"},{"1":"4356edf2-8572-4ae6-9165-ecc9d62fa8c0","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nSELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';\n```\n</details>","12":"markdown","13":{"1":"98299154-6b58-4035-9411-fc28fd20dfbb","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>SELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"34f9ef1a-d87f-4d3b-9529-0224468c8344","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 2: Create the Last Three Tables</span></center>\n<center></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n\nIn order to complete the _Schedule Ride_ use-case, the user will need to schedule a ride.\nThe _View Schedule_ and _Alert Rider_ use-cases both rely on the data generated by this use-case.\nWhen a user schedules a ride, the app will need to make entries in _three_ different tables - the `ride_count_by_time_and_ride`, the `ride_instances_by_user_id` and the `ride_instances_by_start_time` tables.\n\nThe `ride_count_by_time_and_ride` table keeps track of how many riders are scheduled to ride a specific ride at a specific time.\nFor this count we will use an `int` and not a `counter`.\nCounters do not prevent concurrent updates, so they may be slightly off - which is fine for things like counting \"likes\", etc.\nBut if we need an exact count, we will need to use an `int` with lightweight transactions.\nAlso, note that for this table we need to use both a start time and a ride ID to uniquely identify the count.\nSo we will use a composite partition key for this table.\n\nOnce we are certain there is room on the ride to schedule another rider, we need to update two tables.\nWe can use a `BATCH` to do this, but we are getting ahead of ourselves.\n\nLet's start by creating the tables.\nThe `ride_count_by_time_and_ride` table looks like this:\n![ride-ride_instances_by_user_id](https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_count_by_time_and_ride.jpg \"ride_instances_by_user_id\" )\nNote that the partition key is both the `start_time` and the `ride_id`.\n\nThe `ride_instances_by_user_id` table looks like this:\n![ride-ride_instances_by_user_id](https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_instances_by_user_id.jpg \"ride_instances_by_user_id\" )\nNote the partition key is `user_id`, and `start_time` is a clustering column that makes the row unique.\n\nAnd `ride_instances_by_start_time` looks like this:\n![ride-ride_instances_by_start_time](https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_instance_by_start_time.jpg \"ride_instances_by_start_time\" )\nHere, `start_time` is the partition key and `ride_id` and `user_id` are clustering columns that makes the row unique.\n\nSee if you can create these tables in the following cell (Don't forget to use the schema viewer to check your work):","12":"markdown","13":{"1":"1ecb38f2-5fe2-43a3-8732-bb9c3436cb12","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 2: Create the Last Three Tables</span></center></h3>\n<p><center><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/use_caseScheduleRide.jpg\" alt=\"Schedule Ride use case\" /></center>\n<br /><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<p>In order to complete the <em>Schedule Ride</em> use-case, the user will need to schedule a ride.\n<br />The <em>View Schedule</em> and <em>Alert Rider</em> use-cases both rely on the data generated by this use-case.\n<br />When a user schedules a ride, the app will need to make entries in <em>three</em> different tables - the <code>ride_count_by_time_and_ride</code>, the <code>ride_instances_by_user_id</code> and the <code>ride_instances_by_start_time</code> tables.</p>\n<p>The <code>ride_count_by_time_and_ride</code> table keeps track of how many riders are scheduled to ride a specific ride at a specific time.\n<br />For this count we will use an <code>int</code> and not a <code>counter</code>.\n<br />Counters do not prevent concurrent updates, so they may be slightly off - which is fine for things like counting “likes”, etc.\n<br />But if we need an exact count, we will need to use an <code>int</code> with lightweight transactions.\n<br />Also, note that for this table we need to use both a start time and a ride ID to uniquely identify the count.\n<br />So we will use a composite partition key for this table.</p>\n<p>Once we are certain there is room on the ride to schedule another rider, we need to update two tables.\n<br />We can use a <code>BATCH</code> to do this, but we are getting ahead of ourselves.</p>\n<p>Let's start by creating the tables.\n<br />The <code>ride_count_by_time_and_ride</code> table looks like this:\n<br /><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_count_by_time_and_ride.jpg\" alt=\"ride-ride_instances_by_user_id\" title=\"ride_instances_by_user_id\" />\n<br />Note that the partition key is both the <code>start_time</code> and the <code>ride_id</code>.</p>\n<p>The <code>ride_instances_by_user_id</code> table looks like this:\n<br /><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_instances_by_user_id.jpg\" alt=\"ride-ride_instances_by_user_id\" title=\"ride_instances_by_user_id\" />\n<br />Note the partition key is <code>user_id</code>, and <code>start_time</code> is a clustering column that makes the row unique.</p>\n<p>And <code>ride_instances_by_start_time</code> looks like this:\n<br /><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/ride_instance_by_start_time.jpg\" alt=\"ride-ride_instances_by_start_time\" title=\"ride_instances_by_start_time\" />\n<br />Here, <code>start_time</code> is the partition key and <code>ride_id</code> and <code>user_id</code> are clustering columns that makes the row unique.</p>\n<p>See if you can create these tables in the following cell (Don't forget to use the schema viewer to check your work):</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"230f07d6-6945-47aa-9841-d71f29c7d845","11":"// Create the ride_count_by_time_and_ride table here:\n\n// Create the ride_instances_by_user_id table here:\n\n// Create the ride_instances_by_start_time table here:\n\n// Again, once you've created the tables use the SCHEMA view in the top right hand corner for review\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"e6384ac4-56f6-4641-a472-eac0580460c5","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solutions.</span></i></summary>\n```\n// How to: Create the ride_count_by_time_and_ride table:\nCREATE TABLE cassandra_land.ride_count_by_time_and_ride (\n start_time timestamp,\n ride_id UUID,\n rider_count int,\n PRIMARY KEY(start_time, ride_id)\n);\n\n// How to: Create the ride_instances_by_user_id table:\nCREATE TABLE cassandra_land.ride_instances_by_user_id (\n user_id UUID,\n start_time timestamp,\n ride_id UUID,\n ride_name text,\n PRIMARY KEY((user_id), start_time)\n);\n\n// How to: Create the ride_instances_by_start_time table:\nCREATE TABLE cassandra_land.ride_instances_by_start_time (\n start_time timestamp,\n ride_id UUID,\n ride_name text,\n user_id UUID,\n phone_number text,\n PRIMARY KEY((start_time), ride_id, user_id)\n);\n```\n</details>","12":"markdown","13":{"1":"7e628c49-0d4c-4b62-9261-2258c4e0bc87","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solutions.</span></i></summary></p>\n<pre><code>// How to: Create the ride_count_by_time_and_ride table:\nCREATE TABLE cassandra_land.ride_count_by_time_and_ride (\n start_time timestamp,\n ride_id UUID,\n rider_count int,\n PRIMARY KEY(start_time, ride_id)\n);\n\n// How to: Create the ride_instances_by_user_id table:\nCREATE TABLE cassandra_land.ride_instances_by_user_id (\n user_id UUID,\n start_time timestamp,\n ride_id UUID,\n ride_name text,\n PRIMARY KEY((user_id), start_time)\n);\n\n// How to: Create the ride_instances_by_start_time table:\nCREATE TABLE cassandra_land.ride_instances_by_start_time (\n start_time timestamp,\n ride_id UUID,\n ride_name text,\n user_id UUID,\n phone_number text,\n PRIMARY KEY((start_time), ride_id, user_id)\n);\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"779053f1-7398-4a8f-8465-752b68dfebfb","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 3: Implement the Schedule Ride use case</span></center>\n<center></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )","12":"markdown","13":{"1":"cca2e9b4-7a77-4aed-9c27-8384bcc39a3e","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 3: Implement the Schedule Ride use case</span></center></h3>\n<p><center><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/use_caseScheduleRide.jpg\" alt=\"Schedule Ride use case\" /></center>\n<br /><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"d944daf5-59e0-456e-88d7-172bbc934311","10":4,"11":"Now let's look at how we would schedule a ride.\n\nThe first step is to make sure the ride has room for another rider by comparing the `rider_count` to the `capacity`.\n\nWe already have the `capacity` from our previous query where we got all the rides at NYC, but for the sake of practice, let's write a query to get just the `capacity` field from the `ride_list_by_location` table for “Always Up Ferris Wheel”.\nExecute the next cell to **get** and **copy** the ride_id for ride “Always Up Ferris Wheel”:\n","12":"markdown","13":{"1":"942f2614-2811-4864-8f33-8a2e76bed176","10":{"9":"<p>Now let's look at how we would schedule a ride.</p>\n<p>The first step is to make sure the ride has room for another rider by comparing the <code>rider_count</code> to the <code>capacity</code>.</p>\n<p>We already have the <code>capacity</code> from our previous query where we got all the rides at NYC, but for the sake of practice, let's write a query to get just the <code>capacity</code> field from the <code>ride_list_by_location</code> table for “Always Up Ferris Wheel”.\n<br />Execute the next cell to <strong>get</strong> and <strong>copy</strong> the ride_id for ride “Always Up Ferris Wheel”:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"6a7e7cba-dbe9-4cde-a062-34fb9fd3d990","11":"SELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"091012a7-bdd1-4df0-aa75-f8a580c150f5","10":4,"11":"Now that you have the ride_id for “Always Up Ferris Wheel” use that to create the query below:","12":"markdown","13":{"1":"a125313e-c571-4a7b-bf16-0fbc869b18f0","10":{"9":"<p>Now that you have the ride_id for “Always Up Ferris Wheel” use that to create the query below:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"e3e4d9ec-1384-4060-bde0-cee25dc31031","11":"// Write a SELECT statement that retrieves the capacity for the \"Always Up Ferris Wheel\" here:\n","12":"cql","16":true,"17":false,"18":{},"22":88,"25":"CL.ONE"},{"1":"b2cac781-90ec-413b-9c17-e08077d5d14f","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nSELECT capacity FROM cassandra_land.ride_list_by_location \nWHERE location = 'NYC' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837;\n```\n</details>","12":"markdown","13":{"1":"8448cb70-d0ab-43c2-bbff-519496874ef6","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>SELECT capacity FROM cassandra_land.ride_list_by_location \nWHERE location = 'NYC' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837;\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"9ba435e5-d095-4072-af68-b54776505b78","10":4,"11":"Next, we need to query the `rider_count` for the desired ride and time from the `ride_count_by_time_and_ride` table.\nSee if you can write a query to get this count for the “Always Up Ferris Wheel” at 10AM on January 1, 2019.\nYou will specify the start time as `'2019-01-01T10:00:00'`. **You will use the same ride_id as you did in the previous query.**\n\nNote that we have not inserted any rows in the table yet, so your query will not return anything.\nThat's OK, just keep following along.","12":"markdown","13":{"1":"6695aff6-86b3-4fdb-acc3-185e8631b570","10":{"9":"<p>Next, we need to query the <code>rider_count</code> for the desired ride and time from the <code>ride_count_by_time_and_ride</code> table.\n<br />See if you can write a query to get this count for the “Always Up Ferris Wheel” at 10AM on January 1, 2019.\n<br />You will specify the start time as <code>'2019-01-01T10:00:00'</code>. <strong>You will use the same ride_id as you did in the previous query.</strong></p>\n<p>Note that we have not inserted any rows in the table yet, so your query will not return anything.\n<br />That's OK, just keep following along.</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"23":125,"25":"CL.ONE"},{"1":"139b9f08-2dc8-4f3a-82b8-bdfab180c59a","11":"// Write the SELECT statement to get the rider_count from ride_count_by_time_and_ride, \n// given a start time of '2019-01-01T10:00:00' and the ride_id associated with \"Always Up Ferris Wheel\".\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"fbace06f-f9f7-406d-9d74-a6a165e575fc","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nSELECT rider_count FROM cassandra_land.ride_count_by_time_and_ride \nWHERE start_time = '2019-01-01T10:00:00' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837;\n```\n</details>","12":"markdown","13":{"1":"17a636a8-e0af-478d-8406-49ea01ee8732","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>SELECT rider_count FROM cassandra_land.ride_count_by_time_and_ride \nWHERE start_time = '2019-01-01T10:00:00' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837;\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"2e4d6f5a-e8d5-4727-9b7f-5e121f7bc1dc","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 3a: Update rider_count Using Lightweight Transactions</span></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )","12":"markdown","13":{"1":"9cb20aca-abcd-4fea-a82b-e24ea0dbd3e9","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 3a: Update rider_count Using Lightweight Transactions</span></center></h3>\n<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"8fafbbe0-766b-4b9a-b015-319c31b04a8d","10":4,"11":"What we are trying to do is check to see if the `rider_count` is less than the capacity so we don't overbook the ride.\nThe SELECT query in the previous cell didn't return any results because no riders have been scheduled for that ride/time yet.\n\nIf no riders have been scheduled, we need to create a row for our first rider.\nHowever, if the row _does_ exist, we need to increment the `rider_count` - but only if the `rider_count` is less than capacity.\n\nIn either case, we need to do this in a safe way so we don't have problems with concurrent updates.\n\nTo make sure we insert safely, we can use the `IF NOT EXISTS` clause with the `INSERT` statement and set the `rider_count` to 1.\nIf we use the `IF NOT EXISTS` clause and the insert fails, we know that between the time we queried the count and when we tried to do the insert, somebody else did the insert first.\nIn this case we need to move to doing an update.\nLet's write an `INSERT` statement with the `IF NOT EXISTS` clause to see how it works.\nThe clause follows the `VALUES()` section of the statement:","12":"markdown","13":{"1":"e8cc1e34-7f89-47ff-ad35-483edcbd3fe7","10":{"9":"<p>What we are trying to do is check to see if the <code>rider_count</code> is less than the capacity so we don't overbook the ride.\n<br />The SELECT query in the previous cell didn't return any results because no riders have been scheduled for that ride/time yet.</p>\n<p>If no riders have been scheduled, we need to create a row for our first rider.\n<br />However, if the row <em>does</em> exist, we need to increment the <code>rider_count</code> - but only if the <code>rider_count</code> is less than capacity.</p>\n<p>In either case, we need to do this in a safe way so we don't have problems with concurrent updates.</p>\n<p>To make sure we insert safely, we can use the <code>IF NOT EXISTS</code> clause with the <code>INSERT</code> statement and set the <code>rider_count</code> to 1.\n<br />If we use the <code>IF NOT EXISTS</code> clause and the insert fails, we know that between the time we queried the count and when we tried to do the insert, somebody else did the insert first.\n<br />In this case we need to move to doing an update.\n<br />Let's write an <code>INSERT</code> statement with the <code>IF NOT EXISTS</code> clause to see how it works.\n<br />The clause follows the <code>VALUES()</code> section of the statement:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"78b4ad3d-c5cc-4aeb-9dd3-bdc84431a073","11":"// Write an INSERT statement (using a LWT) to create a record for cassandra_land.ride_count_by_time_and_ride\n// with the a start time of '2019-01-01T10:00:00' and the ride_id associated with the \"Always Up Ferris Wheel\".\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"1a8c677b-1218-4876-892f-32de0d41dd6a","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nINSERT INTO cassandra_land.ride_count_by_time_and_ride (start_time, ride_id, rider_count) \n VALUES('2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 1)\nIF NOT EXISTS;\n```\n</details>","12":"markdown","13":{"1":"24483534-cdae-479a-a867-6afcc831bf2e","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>INSERT INTO cassandra_land.ride_count_by_time_and_ride (start_time, ride_id, rider_count) \n VALUES('2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 1)\nIF NOT EXISTS;\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"14ad02e6-4ac3-4d9d-8d1f-d1925ff229bc","10":4,"11":"To simulate a concurrent write problem, run the exact same query again and see what happens:","12":"markdown","13":{"1":"a735f811-ded9-4eb4-b461-bc2c98d35d3d","10":{"9":"<p>To simulate a concurrent write problem, run the exact same query again and see what happens:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"14d8b8d3-6763-4a33-9f52-ee4239b6aa8d","11":"// Again, write an INSERT statement (using a LWT) to create a record for cassandra_land.ride_count_by_time_and_ride\n// with the a start time of '2019-01-01T10:00:00' and the ride_id associated with the Always Up Ferris Wheel.\n// (you can just copy and paste the previous query and run it here):\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"12a01e95-7154-4cf0-870c-0a5a3a6d7309","10":4,"11":"Notice that the results of the second attempt show `[applied]` as false - meaning that the insert did not occur (due to the `IF NOT EXISTS`) clause.\n\nIn your app, if the query was successful, you would be done incrementing the count.\nIf not, you would proceed to the update loop using lightweight transactions, which we will now describe.\n\nThe way lightweight transactions work is that we only perform an update if the row is in a specified state.\nIn this example, the specified state is that `rider_count` is a value we have previously read (which is also less than the capacity of the ride).\nIn terms of pseudocode, the logic looks like this:\n<pre>\nLet current rider count be the current value from the row\nLet signed-up be false\nWhile current rider count is less than capacity AND not signed-up...\n Attempt to update the rider count with a lightweight transaction\n If the update was successful,\n Set signed-up to true\n Else let current rider count be the current value from the row\nReturn signed-up\n</pre>\n\nUsing programming language logic and the queries you have already performed, it should be clear how to build this update loop into your app.\nHowever, the line:\n<pre>\n...\n<b>Attempt to update the rider count with a lightweight transaction</b>\n...\n</pre>\nneeds further clarification.\n\nThis line performs an `UPDATE` statement using the `IF <condition>` clause.\nit has this form:\n```\nUPDATE <keyspace name>.<table name>\n SET <assignment>\n WHERE <row specification>\n IF <condition>\n```\nThe `assignment` would be something like `rider_count = 2` and the condition would be `rider_count = 1`.\nIn the next cell, update the `rider_count` for the row with `start_time` = `'2019-01-01T10:00:00'` for \"Always Up Ferris Wheel\":","12":"markdown","13":{"1":"687a7fdc-bdb8-4f93-9881-bf8f3eaa2d2f","10":{"9":"<p>Notice that the results of the second attempt show <code>[applied]</code> as false - meaning that the insert did not occur (due to the <code>IF NOT EXISTS</code>) clause.</p>\n<p>In your app, if the query was successful, you would be done incrementing the count.\n<br />If not, you would proceed to the update loop using lightweight transactions, which we will now describe.</p>\n<p>The way lightweight transactions work is that we only perform an update if the row is in a specified state.\n<br />In this example, the specified state is that <code>rider_count</code> is a value we have previously read (which is also less than the capacity of the ride).\n<br />In terms of pseudocode, the logic looks like this:</p>\n<pre>\nLet current rider count be the current value from the row\nLet signed-up be false\nWhile current rider count is less than capacity AND not signed-up...\n Attempt to update the rider count with a lightweight transaction\n If the update was successful,\n Set signed-up to true\n Else let current rider count be the current value from the row\nReturn signed-up\n</pre>\n<p>Using programming language logic and the queries you have already performed, it should be clear how to build this update loop into your app.\n<br />However, the line:</p>\n<pre>\n...\n<b>Attempt to update the rider count with a lightweight transaction</b>\n...\n</pre>\n<p>needs further clarification.</p>\n<p>This line performs an <code>UPDATE</code> statement using the <code>IF <condition></code> clause.\n<br />it has this form:</p>\n<pre><code>UPDATE <keyspace name>.<table name>\n SET <assignment>\n WHERE <row specification>\n IF <condition>\n</code></pre>\n<p>The <code>assignment</code> would be something like <code>rider_count = 2</code> and the condition would be <code>rider_count = 1</code>.\n<br />In the next cell, update the <code>rider_count</code> for the row with <code>start_time</code> = <code>'2019-01-01T10:00:00'</code> for “Always Up Ferris Wheel”:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"075b7e5b-7c23-4230-81f8-4b4dfe1a7647","11":"// Write the UPDATE statement to increment rider_count on ride_count_by_time_and_ride (using a lightweight transaction) here:\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"c163fa8c-4b17-4ee5-92ad-64e0954dc459","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nUPDATE cassandra_land.ride_count_by_time_and_ride SET rider_count = 2 \nWHERE start_time = '2019-01-01T10:00:00' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837\nIF rider_count = 1;\n```\n</details>","12":"markdown","13":{"1":"b7b09417-9256-49ab-ba48-8719f054e518","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>UPDATE cassandra_land.ride_count_by_time_and_ride SET rider_count = 2 \nWHERE start_time = '2019-01-01T10:00:00' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837\nIF rider_count = 1;\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"c94cec5d-7ad4-479a-bce1-35fa95a94338","10":4,"11":"Notice, if you run the update a second time, the statement fails because the condition is no longer true.\n\nYou will also notice that the `rider_count` is now 2.\nStrictly speaking, this is not correct since we are only scheduling one rider.\nThe reason the count is 2 instead of 1 is that we inserted the row and also incremented the count.\nNormally we would do one or the other.\n\n","12":"markdown","13":{"1":"3a04eff5-3ebd-4334-b868-c35474b4e865","10":{"9":"<p>Notice, if you run the update a second time, the statement fails because the condition is no longer true.</p>\n<p>You will also notice that the <code>rider_count</code> is now 2.\n<br />Strictly speaking, this is not correct since we are only scheduling one rider.\n<br />The reason the count is 2 instead of 1 is that we inserted the row and also incremented the count.\n<br />Normally we would do one or the other.</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"31fbf024-668b-4bb2-958e-2550e27e1795","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 3b: Update Multiple Tables Using Batches</span></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n\nThe final step to scheduling a rider is to add rows to the two other tables (`ride_instances_by_start_time` and `ride_instances_by_user_id`).\nRemember, these tables support the other two use-cases.\nWe will use a `BATCH` to insert into these tables, because we want a level of atomicity - i.e., we want both inserts to succeed or neither to succeed.\nThe `BATCH` works by placing both `INSERT` statements inside a `BEGIN BATCH` and `APPLY BATCH` statements.\nTry it out in the next cell by filling in the necessary fields and executing the cell.\nThen verify the contents of the tables in the two following cells:","12":"markdown","13":{"1":"ec653661-ed83-48a2-a174-a0d653a59c0a","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 3b: Update Multiple Tables Using Batches</span></center></h3>\n<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<p>The final step to scheduling a rider is to add rows to the two other tables (<code>ride_instances_by_start_time</code> and <code>ride_instances_by_user_id</code>).\n<br />Remember, these tables support the other two use-cases.\n<br />We will use a <code>BATCH</code> to insert into these tables, because we want a level of atomicity - i.e., we want both inserts to succeed or neither to succeed.\n<br />The <code>BATCH</code> works by placing both <code>INSERT</code> statements inside a <code>BEGIN BATCH</code> and <code>APPLY BATCH</code> statements.\n<br />Try it out in the next cell by filling in the necessary fields and executing the cell.\n<br />Then verify the contents of the tables in the two following cells:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"c62c11b7-21e1-4780-b597-a4dca1b59a94","11":"// Were going to need the user_id for the user with phone number 4088675309\n// in the next set of queries so lets pull that data for reference now.\nSELECT * FROM cassandra_land.users_by_phone_number WHERE phone_number = '4088675309';","12":"cql","16":true,"17":false,"18":{},"22":39,"25":"CL.ONE"},{"1":"019707f2-2e9b-481e-9af2-dead034e9536","11":"BEGIN BATCH\n\n// Here's the insert for ride_instances_by_start_time\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 'Always Up Ferris Wheel', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');\n \n// Here's the insert for the ride_instances_by_user_id\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 'Always Up Ferris Wheel');\n\nAPPLY BATCH;","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"567d3b7b-8dec-4697-85f7-763340950ab5","11":"// Verify the contents of ride_instances_by_start_time by executing this cell\nSELECT * FROM cassandra_land.ride_instances_by_start_time;","12":"cql","16":true,"17":false,"18":{},"22":110,"25":"CL.ONE"},{"1":"0daa0dde-58a5-4367-8273-29c48bfed007","11":"// Verify the contents of ride_instances_by_user_id by executing this cell\nSELECT * FROM cassandra_land.ride_instances_by_user_id;","12":"cql","16":true,"17":false,"18":{},"22":3,"25":"CL.ONE"},{"1":"db1406ee-e418-4ea7-ae0c-cc7ed494a263","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 4: The \"View Schedule\" Use-case</span></center>\n<center></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n\nIn the previous step, we created the tables we need for the final two use-cases.\nLet's put some more data in these tables and then do a query to view the schedule for a user.\n\nExecute the next cell:","12":"markdown","13":{"1":"7aca4bf4-91f6-4a69-b8e4-562cb5273321","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 4: The “View Schedule” Use-case</span></center></h3>\n<p><center><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/use_caseViewSchedule.jpg\" alt=\"Schedule Ride use case\" /></center>\n<br /><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<p>In the previous step, we created the tables we need for the final two use-cases.\n<br />Let's put some more data in these tables and then do a query to view the schedule for a user.</p>\n<p>Execute the next cell:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"e89b1025-0a42-4a83-87cc-68be3abb4693","11":"// Schedule the user (4088675309) to ride Performance Mountain\n// We assume in this CQL that the rider_count row does not exist\nINSERT INTO cassandra_land.ride_count_by_time_and_ride \n (start_time, ride_id, rider_count) \n VALUES('2019-01-01T12:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 1)\nIF NOT EXISTS;\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T12:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T12:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain');\nAPPLY BATCH;\n\n// Schedule the user (4088675309) to ride Relational Scaling Death Drop\n// We assume in this CQL that the rider_count row does not exist\nINSERT INTO cassandra_land.ride_count_by_time_and_ride \n (start_time, ride_id, rider_count) \n VALUES('2019-01-01T13:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 1)\nIF NOT EXISTS;\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T13:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T13:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop');\nAPPLY BATCH;\n\n// Schedule the user (4088675309) to ride It's a Huge World After All\n// We assume in this CQL that the rider_count row does not exist\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T14:00:00', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T14:00:00', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T14:00:00', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All');\nAPPLY BATCH;\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"2f9956b5-dfe2-42e3-abf3-72e733f168d9","10":4,"11":"Now you do it. Use the next 2 cells to get references for both ride and user IDs. Then fill in the missing values in the **INSERT** statements below.","12":"markdown","13":{"1":"c8f2e856-132e-48ca-abdf-e152f8e67900","10":{"9":"<p>Now you do it. Use the next 2 cells to get references for both ride and user IDs. Then fill in the missing values in the <strong>INSERT</strong> statements below.</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"d78f2cb3-a225-4a0c-8ea7-fd1fb92f1053","11":"// Just for reference list out the current rides by location NYC\n// and note the ride_id UUIDs\nSELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';","12":"cql","16":true,"17":false,"18":{},"22":311,"25":"CL.ONE"},{"1":"6b77a12c-ae76-465b-9c50-741805098ece","11":"SELECT * FROM cassandra_land.users_by_phone_number WHERE phone_number = '4088675309';","12":"cql","16":true,"17":false,"18":{},"22":99,"25":"CL.ONE"},{"1":"440fee1a-e287-4d27-9234-a368acd37994","10":4,"11":"The next cell has the operations to simulate a user scheduling rides.\nGo through the CQL in the next cell and replace the ride IDs and the user IDs as necessary, then execute the cell:","12":"markdown","13":{"1":"66c48fe4-aa93-469f-9bd6-1b565e232d16","10":{"9":"<p>The next cell has the operations to simulate a user scheduling rides.\n<br />Go through the CQL in the next cell and replace the ride IDs and the user IDs as necessary, then execute the cell:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"a7bd6897-1a78-4325-b432-d09f1b17144c","11":"// Schedule the user (4088675309) to ride Fast Response Rollercoaster\n// We assume in this CQL that the rider_count row does not exist\nINSERT INTO cassandra_land.ride_count_by_time_and_ride \n (start_time, ride_id, rider_count) \n VALUES('2019-01-01T11:00:00', RIDE_ID_FOR_FAST_RESPONSE_ROLLERCOASTER_GOES_HERE, 1)\nIF NOT EXISTS;\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T11:00:00', RIDE_ID_FOR_FAST_RESPONSE_ROLLERCOASTER_GOES_HERE, 'Fast Response Rollercoaster', USER_ID_FOR_4088675309_GOES_HERE, '4088675309');\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (USER_ID_FOR_4088675309_GOES_HERE, '2019-01-01T11:00:00', RIDE_ID_FOR_FAST_RESPONSE_ROLLERCOASTER_GOES_HERE, 'Fast Response Rollercoaster');\nAPPLY BATCH;","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"a4fe95f4-a2c2-49c9-93e3-7e3ff5be2cf1","10":4,"11":"Now let's write the query to get the ride schedule for the user with phone number 4088675309.\nYou will want to query the `ride_instances_by_user_id` table for all the ride instances for the specified user.\nCan you write that query in the next cell?","12":"markdown","13":{"1":"3a4246ee-ea13-4f0e-9eca-d42da94bbf10","10":{"9":"<p>Now let's write the query to get the ride schedule for the user with phone number 4088675309.\n<br />You will want to query the <code>ride_instances_by_user_id</code> table for all the ride instances for the specified user.\n<br />Can you write that query in the next cell?</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"9fca9e63-1581-4fea-938c-5d0968ac28bf","11":"// Write the query here to get all the scheduled rides for the user with phone number 4088675309:\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"14b83c99-c652-4e8e-935c-7d2564e10096","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nSELECT * FROM cassandra_land.ride_instances_by_user_id where user_id = 24fa0298-ccbb-41f7-8137-7c2d91cc4e09;\n\n```\n</details>","12":"markdown","13":{"1":"acf7d3b1-c735-41a5-9bc9-4173cb9c84db","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>SELECT * FROM cassandra_land.ride_instances_by_user_id where user_id = 24fa0298-ccbb-41f7-8137-7c2d91cc4e09;\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"8b42faaf-9e26-4b17-bf70-5454f58a02b2","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n### <center><span style=\"color:navy\">Step 5: Alert Rider Use-case</span></center>\n<center></center>\n![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n\nIn this final use-case, we want to notify all riders that have scheduled rides for that hour.\nBefore we write the query, let's add some more riders for the 10AM time slot (this will make the query results a bit more interesting).\nExecute the next cell:","12":"markdown","13":{"1":"a7329d29-942c-4b9c-a7ff-f314722a5649","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h3><center><span style=\"color:navy\">Step 5: Alert Rider Use-case</span></center></h3>\n<p><center><img src=\"https://s3.amazonaws.com/datastaxtraining/solution-days/cassandra-intro/data-modeling/use_caseRideAlert.jpg\" alt=\"Schedule Ride use case\" /></center>\n<br /><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<p>In this final use-case, we want to notify all riders that have scheduled rides for that hour.\n<br />Before we write the query, let's add some more riders for the 10AM time slot (this will make the query results a bit more interesting).\n<br />Execute the next cell:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"ebf8a53f-3c64-4d01-a146-abffaca2ec27","11":"// Schedule the user (8017124927) to ride Fast Response Rollercoaster **/\nINSERT INTO cassandra_land.ride_count_by_time_and_ride \n (start_time, ride_id, rider_count)\n VALUES ('2019-01-01T10:00:00', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 1)\nIF NOT EXISTS;\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T10:00:00', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 'Fast Response Rollercoaster', 7558d1e9-544b-4e81-960f-8957e372760d, '8017124927');\n \nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (7558d1e9-544b-4e81-960f-8957e372760d, '2019-01-01T10:00:00', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 'Fast Response Rollercoaster');\nAPPLY BATCH;\n// END\n\n// Schedule the user (9999999999) to ride Performance Mountain\nINSERT INTO cassandra_land.ride_count_by_time_and_ride \n (start_time, ride_id, rider_count)\n VALUES ('2019-01-01T10:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 1)\nIF NOT EXISTS;\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T10:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain', 154a857e-4b40-4d8d-9646-c4bf7632999f, '9999999999');\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (154a857e-4b40-4d8d-9646-c4bf7632999f, '2019-01-01T10:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain');\nAPPLY BATCH;\n// END\n\n// Schedule the user (1234567890) to ride Relational Scaling Death Drop\nINSERT INTO cassandra_land.ride_count_by_time_and_ride \n (start_time, ride_id, rider_count)\n VALUES ('2019-01-01T10:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 1)\n IF NOT EXISTS;\n\nBEGIN BATCH\nINSERT INTO cassandra_land.ride_instances_by_start_time \n (start_time, ride_id, ride_name, user_id, phone_number)\n VALUES ('2019-01-01T10:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop', d0acbb79-64c7-43a2-aaf2-0ef8fceb94d4, '1234567890');\nINSERT INTO cassandra_land.ride_instances_by_user_id \n (user_id, start_time, ride_id, ride_name)\n VALUES (d0acbb79-64c7-43a2-aaf2-0ef8fceb94d4, '2019-01-01T10:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop');\nAPPLY BATCH;\n// END","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"f3707717-d0a8-45f1-8a17-7975b224df9b","10":4,"11":"Finally, let's write the query that gets the list of all users scheduled to ride at 10am.\nWe'll query the `ride_instances_by_start_time` table.\nSee if you can write this final query in the cell below:","12":"markdown","13":{"1":"72cd3a8b-2ae8-4734-a4fd-fc3884b61546","10":{"9":"<p>Finally, let's write the query that gets the list of all users scheduled to ride at 10am.\n<br />We'll query the <code>ride_instances_by_start_time</code> table.\n<br />See if you can write this final query in the cell below:</p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"65f506a2-c493-4659-8c98-a90d0305261c","11":"// Query the ride_instances_by_start_time table for all the riders at 10AM January 1, 2019:\n","12":"cql","16":true,"17":false,"25":"CL.ONE"},{"1":"d95a9315-3daa-4a74-9992-2b1399a19379","10":4,"11":"<details>\n<summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary>\n```\nSELECT * FROM cassandra_land.ride_instances_by_start_time WHERE start_time = '2019-01-01T10:00:00';\n```\n</details>","12":"markdown","13":{"1":"36d2f1f7-e854-4d4a-b6fa-b7705e1acc7e","10":{"9":"<p><details>\n<br /><summary><i><span style=\"color:blue\">Click here for the solution.</span></i></summary></p>\n<pre><code>SELECT * FROM cassandra_land.ride_instances_by_start_time WHERE start_time = '2019-01-01T10:00:00';\n</code></pre>\n<p></details></p>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"},{"1":"3c9943e7-f2a3-4b31-84cb-35da447b6a7b","10":4,"11":"![line](https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png \"line\" )\n# <center><span style=\"color:navy\">Congratulations!!!!!</span></center>\n#### You have completed the _Cassandra-Land Project_ notebook!\n\nWOW! You have worked through the entire project and it was challenging!\nYou have created the data model and all the CQL for this app!\nAs a result, you have learned the following:\n* How to analyze use-cases to derive a data model\n* How to denormalize to maintain performance\n* How to use lightweight transactions\n* How to leverage batch operations\n\nYou are well on your way to becoming a Cassandra expert.\nWant to continue your journey?\nJoins us at DataStax Academy where there's even more good stuff!\nThese courses are <span style=\"color:green\">FREE</span>, and they are a great place to start:\n* [DS201: DataStax Enterprise Foundations of Apache Cassandra™](http://academy.datastax.com/resources/ds201-foundations-apache-cassandra \"DS201: DataStax Enterprise Foundations of Apache Cassandra™\")\n* [DS210: DataStax Enterprise Operations with Apache Cassandra™](https://academy.datastax.com/resources/ds210-datastax-enterprise-operations-apache-cassandra \"DS210: DataStax Enterprise Operations with Apache Cassandra™\")\n* [DS220: DataStax Enterprise 6 Practical Application Data Modeling with Apache Cassandra™](https://academy.datastax.com/resources/ds220 \"DS220: DataStax Enterprise 6 Practical Application Data Modeling with Apache Cassandra™\")","12":"markdown","13":{"1":"1423e155-07fe-40e4-8001-e5133d8c6693","10":{"9":"<p><img src=\"https://datastaxtraining.s3.amazonaws.com/developer-day/lineblue.png\" alt=\"line\" title=\"line\" /></p>\n<h1><center><span style=\"color:navy\">Congratulations!!!!!</span></center></h1>\n<h4>You have completed the <em>Cassandra-Land Project</em> notebook!</h4>\n<p>WOW! You have worked through the entire project and it was challenging!\n<br />You have created the data model and all the CQL for this app!\n<br />As a result, you have learned the following:</p>\n<ul>\n<li>How to analyze use-cases to derive a data model</li>\n<li>How to denormalize to maintain performance</li>\n<li>How to use lightweight transactions</li>\n<li>How to leverage batch operations</li>\n</ul>\n<p>You are well on your way to becoming a Cassandra expert.\n<br />Want to continue your journey?\n<br />Joins us at DataStax Academy where there's even more good stuff!\n<br />These courses are <span style=\"color:green\">FREE</span>, and they are a great place to start:</p>\n<ul>\n<li><a href=\"http://academy.datastax.com/resources/ds201-foundations-apache-cassandra\" title=\"DS201: DataStax Enterprise Foundations of Apache Cassandra™\">DS201: DataStax Enterprise Foundations of Apache Cassandra™</a></li>\n<li><a href=\"https://academy.datastax.com/resources/ds210-datastax-enterprise-operations-apache-cassandra\" title=\"DS210: DataStax Enterprise Operations with Apache Cassandra™\">DS210: DataStax Enterprise Operations with Apache Cassandra™</a></li>\n<li><a href=\"https://academy.datastax.com/resources/ds220\" title=\"DS220: DataStax Enterprise 6 Practical Application Data Modeling with Apache Cassandra™\">DS220: DataStax Enterprise 6 Practical Application Data Modeling with Apache Cassandra™</a></li>\n</ul>\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"25":"CL.ONE"}],"16":{"1":{}},"17":""} code.txt 0100644 0000000 0000000 00000077203 13577206362 011304 0 ustar 00 0000000 0000000 --------------------NOTEBOOK_02-03 - Data Modeling: Cassandra-Land Project PART 2--------------------
--------------------CELL_MARKDOWN_1--------------------
<center><img src="//datastaxtraining.s3.amazonaws.com/developer-day/developer-day-studio-header.png" width="800"></center>
# <center><span style="color:navy">Data Modeling: Cassandra-Land Project Part 2</span></center>
--------------------CELL_MARKDOWN_2--------------------
### <center><span style="color:navy">Step 1: Create the ride_list_by_location Table</span></center>
<center></center>
Our app needs a list of rides in the park that we can present to the user so the user can schedule rides.
For our current app, since there aren't too many rides in a theme park, the entire list of rides will easily fit into a single partition.
But what if we were dealing with billions of rides?
In that case, it would be necessary to divide the rides up in some way so that they don't all end up in one huge partition.
We'll use the ride location for this purpose.
In our example, all the rides will be in a single location, but we'll use this field to position us for future growth - we are certain that Cassandra-Land will catch on and every city in the world will need their own theme park.
We want to create a table that looks as follows:
Note that `location` is the partition key.
We also need to use the ride_id as a clustering column to create uniqueness.
Otherwise, rides in the same location will cause upserts.
See if you can formulate the CQL in the following cell to create this table.
<span style="color:blue">Hint:</span> The primary key will have `location` as the partition key and `ride_id` as a clustering column, so the primary key clause will look like `PRIMARY KEY((location), ride_id)`.
--------------------CELL_CQL_3--------------------
// Create the ride_list_by_location table here:
--------------------CELL_MARKDOWN_4--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
CREATE TABLE cassandra_land.ride_list_by_location (
location text,
ride_id UUID,
ride_name text,
capacity int,
PRIMARY KEY((location), ride_id)
);
```
</details>
--------------------CELL_MARKDOWN_5--------------------
Let's make sure we created the table like we expected.
Use the SCHEMA view in the top right hand corner to review the ride_list_by_location table.
Once you've done that we'll insert some rides in the table.
Execute the following cell to insert the rides:
--------------------CELL_CQL_6--------------------
INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 'Fast Response Rollercoaster', 50);
INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 95aac1f5-c69c-444e-9645-b3e1ae913837, 'Always Up Ferris Wheel', 100);
INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop', 25);
INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain', 50);
INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All', 1000);
// Note the use of pre-generated UUID values above. This is done here to make the rest of the exercises
// more streamlined and easier to troubleshoot. In practice you would generate a UUID using
// UUID() as you see in the statement below.
INSERT INTO cassandra_land.ride_list_by_location (location, ride_id, ride_name, capacity) VALUES('NYC', UUID(), 'Single Rider Slingshot', 1);
--------------------CELL_MARKDOWN_7--------------------
As the last part of this step, let's formulate the query we will use in the app to get the list of rides for the user.
We want to get all the rides in NYC.
Try to create the query in the next cell:
--------------------CELL_CQL_8--------------------
// Create a query to get all rides in NYC:
--------------------CELL_MARKDOWN_9--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
SELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';
```
</details>
--------------------CELL_MARKDOWN_10--------------------
### <center><span style="color:navy">Step 2: Create the Last Three Tables</span></center>
<center></center>
In order to complete the _Schedule Ride_ use-case, the user will need to schedule a ride.
The _View Schedule_ and _Alert Rider_ use-cases both rely on the data generated by this use-case.
When a user schedules a ride, the app will need to make entries in _three_ different tables - the `ride_count_by_time_and_ride`, the `ride_instances_by_user_id` and the `ride_instances_by_start_time` tables.
The `ride_count_by_time_and_ride` table keeps track of how many riders are scheduled to ride a specific ride at a specific time.
For this count we will use an `int` and not a `counter`.
Counters do not prevent concurrent updates, so they may be slightly off - which is fine for things like counting "likes", etc.
But if we need an exact count, we will need to use an `int` with lightweight transactions.
Also, note that for this table we need to use both a start time and a ride ID to uniquely identify the count.
So we will use a composite partition key for this table.
Once we are certain there is room on the ride to schedule another rider, we need to update two tables.
We can use a `BATCH` to do this, but we are getting ahead of ourselves.
Let's start by creating the tables.
The `ride_count_by_time_and_ride` table looks like this:
Note that the partition key is both the `start_time` and the `ride_id`.
The `ride_instances_by_user_id` table looks like this:
Note the partition key is `user_id`, and `start_time` is a clustering column that makes the row unique.
And `ride_instances_by_start_time` looks like this:
Here, `start_time` is the partition key and `ride_id` and `user_id` are clustering columns that makes the row unique.
See if you can create these tables in the following cell (Don't forget to use the schema viewer to check your work):
--------------------CELL_CQL_11--------------------
// Create the ride_count_by_time_and_ride table here:
// Create the ride_instances_by_user_id table here:
// Create the ride_instances_by_start_time table here:
// Again, once you've created the tables use the SCHEMA view in the top right hand corner for review
--------------------CELL_MARKDOWN_12--------------------
<details>
<summary><i><span style="color:blue">Click here for the solutions.</span></i></summary>
```
// How to: Create the ride_count_by_time_and_ride table:
CREATE TABLE cassandra_land.ride_count_by_time_and_ride (
start_time timestamp,
ride_id UUID,
rider_count int,
PRIMARY KEY(start_time, ride_id)
);
// How to: Create the ride_instances_by_user_id table:
CREATE TABLE cassandra_land.ride_instances_by_user_id (
user_id UUID,
start_time timestamp,
ride_id UUID,
ride_name text,
PRIMARY KEY((user_id), start_time)
);
// How to: Create the ride_instances_by_start_time table:
CREATE TABLE cassandra_land.ride_instances_by_start_time (
start_time timestamp,
ride_id UUID,
ride_name text,
user_id UUID,
phone_number text,
PRIMARY KEY((start_time), ride_id, user_id)
);
```
</details>
--------------------CELL_MARKDOWN_13--------------------
### <center><span style="color:navy">Step 3: Implement the Schedule Ride use case</span></center>
<center></center>
--------------------CELL_MARKDOWN_14--------------------
Now let's look at how we would schedule a ride.
The first step is to make sure the ride has room for another rider by comparing the `rider_count` to the `capacity`.
We already have the `capacity` from our previous query where we got all the rides at NYC, but for the sake of practice, let's write a query to get just the `capacity` field from the `ride_list_by_location` table for “Always Up Ferris Wheel”.
Execute the next cell to **get** and **copy** the ride_id for ride “Always Up Ferris Wheel”:
--------------------CELL_CQL_15--------------------
SELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';
--------------------CELL_MARKDOWN_16--------------------
Now that you have the ride_id for “Always Up Ferris Wheel” use that to create the query below:
--------------------CELL_CQL_17--------------------
// Write a SELECT statement that retrieves the capacity for the "Always Up Ferris Wheel" here:
--------------------CELL_MARKDOWN_18--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
SELECT capacity FROM cassandra_land.ride_list_by_location
WHERE location = 'NYC' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837;
```
</details>
--------------------CELL_MARKDOWN_19--------------------
Next, we need to query the `rider_count` for the desired ride and time from the `ride_count_by_time_and_ride` table.
See if you can write a query to get this count for the “Always Up Ferris Wheel” at 10AM on January 1, 2019.
You will specify the start time as `'2019-01-01T10:00:00'`. **You will use the same ride_id as you did in the previous query.**
Note that we have not inserted any rows in the table yet, so your query will not return anything.
That's OK, just keep following along.
--------------------CELL_CQL_20--------------------
// Write the SELECT statement to get the rider_count from ride_count_by_time_and_ride,
// given a start time of '2019-01-01T10:00:00' and the ride_id associated with "Always Up Ferris Wheel".
--------------------CELL_MARKDOWN_21--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
SELECT rider_count FROM cassandra_land.ride_count_by_time_and_ride
WHERE start_time = '2019-01-01T10:00:00' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837;
```
</details>
--------------------CELL_MARKDOWN_22--------------------
### <center><span style="color:navy">Step 3a: Update rider_count Using Lightweight Transactions</span></center>
--------------------CELL_MARKDOWN_23--------------------
What we are trying to do is check to see if the `rider_count` is less than the capacity so we don't overbook the ride.
The SELECT query in the previous cell didn't return any results because no riders have been scheduled for that ride/time yet.
If no riders have been scheduled, we need to create a row for our first rider.
However, if the row _does_ exist, we need to increment the `rider_count` - but only if the `rider_count` is less than capacity.
In either case, we need to do this in a safe way so we don't have problems with concurrent updates.
To make sure we insert safely, we can use the `IF NOT EXISTS` clause with the `INSERT` statement and set the `rider_count` to 1.
If we use the `IF NOT EXISTS` clause and the insert fails, we know that between the time we queried the count and when we tried to do the insert, somebody else did the insert first.
In this case we need to move to doing an update.
Let's write an `INSERT` statement with the `IF NOT EXISTS` clause to see how it works.
The clause follows the `VALUES()` section of the statement:
--------------------CELL_CQL_24--------------------
// Write an INSERT statement (using a LWT) to create a record for cassandra_land.ride_count_by_time_and_ride
// with the a start time of '2019-01-01T10:00:00' and the ride_id associated with the "Always Up Ferris Wheel".
--------------------CELL_MARKDOWN_25--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
INSERT INTO cassandra_land.ride_count_by_time_and_ride (start_time, ride_id, rider_count)
VALUES('2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 1)
IF NOT EXISTS;
```
</details>
--------------------CELL_MARKDOWN_26--------------------
To simulate a concurrent write problem, run the exact same query again and see what happens:
--------------------CELL_CQL_27--------------------
// Again, write an INSERT statement (using a LWT) to create a record for cassandra_land.ride_count_by_time_and_ride
// with the a start time of '2019-01-01T10:00:00' and the ride_id associated with the Always Up Ferris Wheel.
// (you can just copy and paste the previous query and run it here):
--------------------CELL_MARKDOWN_28--------------------
Notice that the results of the second attempt show `[applied]` as false - meaning that the insert did not occur (due to the `IF NOT EXISTS`) clause.
In your app, if the query was successful, you would be done incrementing the count.
If not, you would proceed to the update loop using lightweight transactions, which we will now describe.
The way lightweight transactions work is that we only perform an update if the row is in a specified state.
In this example, the specified state is that `rider_count` is a value we have previously read (which is also less than the capacity of the ride).
In terms of pseudocode, the logic looks like this:
<pre>
Let current rider count be the current value from the row
Let signed-up be false
While current rider count is less than capacity AND not signed-up...
Attempt to update the rider count with a lightweight transaction
If the update was successful,
Set signed-up to true
Else let current rider count be the current value from the row
Return signed-up
</pre>
Using programming language logic and the queries you have already performed, it should be clear how to build this update loop into your app.
However, the line:
<pre>
...
<b>Attempt to update the rider count with a lightweight transaction</b>
...
</pre>
needs further clarification.
This line performs an `UPDATE` statement using the `IF <condition>` clause.
it has this form:
```
UPDATE <keyspace name>.<table name>
SET <assignment>
WHERE <row specification>
IF <condition>
```
The `assignment` would be something like `rider_count = 2` and the condition would be `rider_count = 1`.
In the next cell, update the `rider_count` for the row with `start_time` = `'2019-01-01T10:00:00'` for "Always Up Ferris Wheel":
--------------------CELL_CQL_29--------------------
// Write the UPDATE statement to increment rider_count on ride_count_by_time_and_ride (using a lightweight transaction) here:
--------------------CELL_MARKDOWN_30--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
UPDATE cassandra_land.ride_count_by_time_and_ride SET rider_count = 2
WHERE start_time = '2019-01-01T10:00:00' AND ride_id = 95aac1f5-c69c-444e-9645-b3e1ae913837
IF rider_count = 1;
```
</details>
--------------------CELL_MARKDOWN_31--------------------
Notice, if you run the update a second time, the statement fails because the condition is no longer true.
You will also notice that the `rider_count` is now 2.
Strictly speaking, this is not correct since we are only scheduling one rider.
The reason the count is 2 instead of 1 is that we inserted the row and also incremented the count.
Normally we would do one or the other.
--------------------CELL_MARKDOWN_32--------------------
### <center><span style="color:navy">Step 3b: Update Multiple Tables Using Batches</span></center>
The final step to scheduling a rider is to add rows to the two other tables (`ride_instances_by_start_time` and `ride_instances_by_user_id`).
Remember, these tables support the other two use-cases.
We will use a `BATCH` to insert into these tables, because we want a level of atomicity - i.e., we want both inserts to succeed or neither to succeed.
The `BATCH` works by placing both `INSERT` statements inside a `BEGIN BATCH` and `APPLY BATCH` statements.
Try it out in the next cell by filling in the necessary fields and executing the cell.
Then verify the contents of the tables in the two following cells:
--------------------CELL_CQL_33--------------------
// Were going to need the user_id for the user with phone number 4088675309
// in the next set of queries so lets pull that data for reference now.
SELECT * FROM cassandra_land.users_by_phone_number WHERE phone_number = '4088675309';
--------------------CELL_CQL_34--------------------
BEGIN BATCH
// Here's the insert for ride_instances_by_start_time
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 'Always Up Ferris Wheel', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');
// Here's the insert for the ride_instances_by_user_id
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T10:00:00', 95aac1f5-c69c-444e-9645-b3e1ae913837, 'Always Up Ferris Wheel');
APPLY BATCH;
--------------------CELL_CQL_35--------------------
// Verify the contents of ride_instances_by_start_time by executing this cell
SELECT * FROM cassandra_land.ride_instances_by_start_time;
--------------------CELL_CQL_36--------------------
// Verify the contents of ride_instances_by_user_id by executing this cell
SELECT * FROM cassandra_land.ride_instances_by_user_id;
--------------------CELL_MARKDOWN_37--------------------
### <center><span style="color:navy">Step 4: The "View Schedule" Use-case</span></center>
<center></center>
In the previous step, we created the tables we need for the final two use-cases.
Let's put some more data in these tables and then do a query to view the schedule for a user.
Execute the next cell:
--------------------CELL_CQL_38--------------------
// Schedule the user (4088675309) to ride Performance Mountain
// We assume in this CQL that the rider_count row does not exist
INSERT INTO cassandra_land.ride_count_by_time_and_ride
(start_time, ride_id, rider_count)
VALUES('2019-01-01T12:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 1)
IF NOT EXISTS;
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T12:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T12:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain');
APPLY BATCH;
// Schedule the user (4088675309) to ride Relational Scaling Death Drop
// We assume in this CQL that the rider_count row does not exist
INSERT INTO cassandra_land.ride_count_by_time_and_ride
(start_time, ride_id, rider_count)
VALUES('2019-01-01T13:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 1)
IF NOT EXISTS;
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T13:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T13:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop');
APPLY BATCH;
// Schedule the user (4088675309) to ride It's a Huge World After All
// We assume in this CQL that the rider_count row does not exist
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T14:00:00', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T14:00:00', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All', 24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '4088675309');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (24fa0298-ccbb-41f7-8137-7c2d91cc4e09, '2019-01-01T14:00:00', 7e1f2049-2fe3-42c5-a0f4-c7d093423c79, 'It''s a Huge World After All');
APPLY BATCH;
--------------------CELL_MARKDOWN_39--------------------
Now you do it. Use the next 2 cells to get references for both ride and user IDs. Then fill in the missing values in the **INSERT** statements below.
--------------------CELL_CQL_40--------------------
// Just for reference list out the current rides by location NYC
// and note the ride_id UUIDs
SELECT * FROM cassandra_land.ride_list_by_location WHERE location = 'NYC';
--------------------CELL_CQL_41--------------------
SELECT * FROM cassandra_land.users_by_phone_number WHERE phone_number = '4088675309';
--------------------CELL_MARKDOWN_42--------------------
The next cell has the operations to simulate a user scheduling rides.
Go through the CQL in the next cell and replace the ride IDs and the user IDs as necessary, then execute the cell:
--------------------CELL_CQL_43--------------------
// Schedule the user (4088675309) to ride Fast Response Rollercoaster
// We assume in this CQL that the rider_count row does not exist
INSERT INTO cassandra_land.ride_count_by_time_and_ride
(start_time, ride_id, rider_count)
VALUES('2019-01-01T11:00:00', RIDE_ID_FOR_FAST_RESPONSE_ROLLERCOASTER_GOES_HERE, 1)
IF NOT EXISTS;
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T11:00:00', RIDE_ID_FOR_FAST_RESPONSE_ROLLERCOASTER_GOES_HERE, 'Fast Response Rollercoaster', USER_ID_FOR_4088675309_GOES_HERE, '4088675309');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (USER_ID_FOR_4088675309_GOES_HERE, '2019-01-01T11:00:00', RIDE_ID_FOR_FAST_RESPONSE_ROLLERCOASTER_GOES_HERE, 'Fast Response Rollercoaster');
APPLY BATCH;
--------------------CELL_MARKDOWN_44--------------------
Now let's write the query to get the ride schedule for the user with phone number 4088675309.
You will want to query the `ride_instances_by_user_id` table for all the ride instances for the specified user.
Can you write that query in the next cell?
--------------------CELL_CQL_45--------------------
// Write the query here to get all the scheduled rides for the user with phone number 4088675309:
--------------------CELL_MARKDOWN_46--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
SELECT * FROM cassandra_land.ride_instances_by_user_id where user_id = 24fa0298-ccbb-41f7-8137-7c2d91cc4e09;
```
</details>
--------------------CELL_MARKDOWN_47--------------------
### <center><span style="color:navy">Step 5: Alert Rider Use-case</span></center>
<center></center>
In this final use-case, we want to notify all riders that have scheduled rides for that hour.
Before we write the query, let's add some more riders for the 10AM time slot (this will make the query results a bit more interesting).
Execute the next cell:
--------------------CELL_CQL_48--------------------
// Schedule the user (8017124927) to ride Fast Response Rollercoaster **/
INSERT INTO cassandra_land.ride_count_by_time_and_ride
(start_time, ride_id, rider_count)
VALUES ('2019-01-01T10:00:00', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 1)
IF NOT EXISTS;
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T10:00:00', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 'Fast Response Rollercoaster', 7558d1e9-544b-4e81-960f-8957e372760d, '8017124927');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (7558d1e9-544b-4e81-960f-8957e372760d, '2019-01-01T10:00:00', 27eb0940-30a7-40b1-9f1f-823a3cc01693, 'Fast Response Rollercoaster');
APPLY BATCH;
// END
// Schedule the user (9999999999) to ride Performance Mountain
INSERT INTO cassandra_land.ride_count_by_time_and_ride
(start_time, ride_id, rider_count)
VALUES ('2019-01-01T10:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 1)
IF NOT EXISTS;
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T10:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain', 154a857e-4b40-4d8d-9646-c4bf7632999f, '9999999999');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (154a857e-4b40-4d8d-9646-c4bf7632999f, '2019-01-01T10:00:00', 800933d1-9211-4c60-a26a-e115b3a2e1b6, 'Performance Mountain');
APPLY BATCH;
// END
// Schedule the user (1234567890) to ride Relational Scaling Death Drop
INSERT INTO cassandra_land.ride_count_by_time_and_ride
(start_time, ride_id, rider_count)
VALUES ('2019-01-01T10:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 1)
IF NOT EXISTS;
BEGIN BATCH
INSERT INTO cassandra_land.ride_instances_by_start_time
(start_time, ride_id, ride_name, user_id, phone_number)
VALUES ('2019-01-01T10:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop', d0acbb79-64c7-43a2-aaf2-0ef8fceb94d4, '1234567890');
INSERT INTO cassandra_land.ride_instances_by_user_id
(user_id, start_time, ride_id, ride_name)
VALUES (d0acbb79-64c7-43a2-aaf2-0ef8fceb94d4, '2019-01-01T10:00:00', ce3bfbf5-90a3-40bb-b371-04fb50aed97f, 'Relational Scaling Death Drop');
APPLY BATCH;
// END
--------------------CELL_MARKDOWN_49--------------------
Finally, let's write the query that gets the list of all users scheduled to ride at 10am.
We'll query the `ride_instances_by_start_time` table.
See if you can write this final query in the cell below:
--------------------CELL_CQL_50--------------------
// Query the ride_instances_by_start_time table for all the riders at 10AM January 1, 2019:
--------------------CELL_MARKDOWN_51--------------------
<details>
<summary><i><span style="color:blue">Click here for the solution.</span></i></summary>
```
SELECT * FROM cassandra_land.ride_instances_by_start_time WHERE start_time = '2019-01-01T10:00:00';
```
</details>
--------------------CELL_MARKDOWN_52--------------------
# <center><span style="color:navy">Congratulations!!!!!</span></center>
#### You have completed the _Cassandra-Land Project_ notebook!
WOW! You have worked through the entire project and it was challenging!
You have created the data model and all the CQL for this app!
As a result, you have learned the following:
* How to analyze use-cases to derive a data model
* How to denormalize to maintain performance
* How to use lightweight transactions
* How to leverage batch operations
You are well on your way to becoming a Cassandra expert.
Want to continue your journey?
Joins us at DataStax Academy where there's even more good stuff!
These courses are <span style="color:green">FREE</span>, and they are a great place to start:
* [DS201: DataStax Enterprise Foundations of Apache Cassandra™](http://academy.datastax.com/resources/ds201-foundations-apache-cassandra "DS201: DataStax Enterprise Foundations of Apache Cassandra™")
* [DS210: DataStax Enterprise Operations with Apache Cassandra™](https://academy.datastax.com/resources/ds210-datastax-enterprise-operations-apache-cassandra "DS210: DataStax Enterprise Operations with Apache Cassandra™")
* [DS220: DataStax Enterprise 6 Practical Application Data Modeling with Apache Cassandra™](https://academy.datastax.com/resources/ds220 "DS220: DataStax Enterprise 6 Practical Application Data Modeling with Apache Cassandra™")
versions-info.txt 0100644 0000000 0000000 00000000026 13577206362 013160 0 ustar 00 0000000 0000000 Studio Version: 6.7.0