-
Notifications
You must be signed in to change notification settings - Fork 0
/
excercise.cql
142 lines (100 loc) · 5.04 KB
/
excercise.cql
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
## Create keyspace catalog
CREATE KEYSPACE catalog WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'} AND durable_writes = true;
## To list keyspaces:
DESCRIBE keyspaces;
## To list all columnfamilies in catalog keyspace:
USE catalog;
describe columnfamilies;
## To create columnfamily product
CREATE COLUMNFAMILY product (
productId varchar,
title TEXT,
brand varchar,
publisher varchar,
length int,
breadth int,
height int,
PRIMARY KEY (productId) );
##Check for data
Select * from product;
#Insert data
insert into product(productid, title, publisher) values('LATHE2', 'Strong lathe', 'Kiosk');
## To add column modelid of type text
ALTER COLUMNFAMILY product add modelid text;
## To describe column family product
DESCRIBE product;
## to describe keyspace catalog
DESCRIBE KEYSPACE catalog;
## To add few rows to product column family
insert into product(productid, brand, modelid) values('LATHE1', 'Mitsbushi', 'M100');
insert into product(productid, title, breadth, length) values('MOTOR1', 'AC motor', 22, 36);
insert into product(productid, title, breadth, length) values('MOTOR2', 'DC Motor', 22, 36);
## To show column family data
select * from product;
## To show row for a productid with EQ
select * from product where productid = 'LATHE1';
## To show row for a productid with IN
select productid, title, modelid from product where productid IN ('LATHE1', 'MOTOR1');
## To add column with data type SET
ALTER COLUMNFAMILY product ADD keyfeatures set<TEXT>;
##To insert product with set data type
insert into product(productid, title, brand, keyfeatures) values('Nut1', 'Threaded Nut', 'Tej', {'detachable', 'colorful'});
## To add column with data type MAP
ALTER COLUMNFAMILY product ADD camera map<TEXT, TEXT>;
##Dropping a column
ALTER COLUMNFAMILY product DROP camera;
## To add column with data type MAP
ALTER COLUMNFAMILY product ADD properties map<TEXT, TEXT>;
##To insert product with map data type
insert into product(productid, title, brand, properties) values('Cement', 'Sets quickly', 'Ambuja', {'Strength':'Standard autoclave test passed', 'Setting Time':'650 minutes'});
## To add column with data type LIST
ALTER COLUMNFAMILY product ADD service_type LIST<TEXT>;
##To insert product with list data type
insert into product(productid, title, brand, service_type) values('SOFA1', 'Urban Living Derby Sofa', 'Urban Living', ['Needs to call seller', 'Service Engineer will Come to the Site ']);
-----------------------------Partition Key Restrictions------------------------------
#Allow Filtering issue because partition key not included
select productid from product where publisher = 'Kiosk';
## To create columnfamily skus
CREATE TABLE skus (
sellerId varchar,
productId varchar,
skuId varchar,
title text,
listingId varchar,
isListingCreated boolean,
timeofskucreation text,
PRIMARY KEY((sellerId,skuId), timeofskucreation, productId)
);
## To load data from file skus.csv into skus Column Family
COPY skus(sellerid, productid, skuid, title,listingid, islistingcreated,timeofskucreation) FROM 'seller.csv' ;
## To show data of skus
select * from skus;
##Won t work unless All partition keys included
select * from skus where sellerid= 'Decor';
##All partition keys included
select * from skus where sellerid= 'Decor' and skuid='DECORSKU';
## To allow filtering
select * from skus where productid > 'SOFA1' ALLOW FILTERING;
##Won t work unless All partition keys are needed even with in
select * from skus where sellerid in ('Decor');
##All partition keys included
select * from skus where sellerid in ('Decor') and skuid in ('DECORSKU');
##Cannot use >,>=,<,<= operator directly in partition key
select * from skus where sellerid > 'Chroma' and skuid > 'ChromaSKU1';
##OrderBy Not supported without resticted partition columns
select * from skus order by sellerid;
## To do range query using > on token(partition key)
select * from skus where token(sellerid,skuid) >= token('Chroma', 'ChromaSKU1') ;
--------------------------Clustering Key Restrictions----------------------------------
##Require 1 to n-1 clustering keys
select * from skus where sellerid = 'Fab' and skuid in ('FABSKU', 'FABSKU1') and productid = 'SOFA1';
##Whereas giving only the first clustering column works
select * from skus where sellerid = 'Fab' and skuid in ('FABSKU', 'FABSKU1') and timeofskucreation = '2015-12-11 12:21';
## To query successfully with clustering keys with EQ on skus
select * from skus where sellerid = 'Fab' and skuid in ('FABSKU', 'FABSKU1') and timeofskucreation = '2015-12-11 12:21' and productid = 'SOFA1';
## To query successfully with clustering keys with IN on skus
select * from skus where sellerid in ('Decor', 'Fab') and skuid in ('FABSKU', 'DECORSKU') and timeofskucreation IN ('2015-12-11 12:21', '2016-07-01 22:30') AND
productid in ('SOFA1', 'SOFA2');
## To query by restricting the clustering columns with range operator
select * from skus where sellerid in ('Decor', 'Fab') and skuid in ('FABSKU', 'DECORSKU') and timeofskucreation >= '2015-08-01 12:21' AND timeofskucreation
<= '2016-07-01 22:30';