E-Commerce Data Model
Download the dataset
mkdir -p /home/hadoop/AdithyaJ/Cassandra/Datasets
cd /home/hadoop/AdithyaJ/Cassandra/Datasets
wget http://dbs.uni-leipzig.de/file/Amazon-GoogleProducts.zip
unzip Amazon-GoogleProducts.zip
wget http://dbs.uni-leipzig.de/file/Abt-Buy.zip
unzip Abt-Buy.zip
Import the data
cd /home/hadoop/AdithyaJ/Cassandra/Datasets
cqlsh
CREATE KEYSPACE admatic WITH replication = {'class' : 'NetworkTopologyStrategy', 'dc1' : 1, 'dc2' : 1};
USE admatic;
CREATE TABLE amazon (
id text PRIMARY KEY,
title text,
description text,
manufacturer text,
price double
);
COPY amazon (id, title, description, manufacturer, price) FROM 'Amazon.csv';
Processed: 1364 rows; Rate: 817 rows/s; Avg. rate: 1456 rows/s
1364 rows imported from 1 files in 0.937 seconds (0 skipped).
Q1 Find products by price
SELECT id,title,manufacturer,price FROM amazon WHERE price = 49.95;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
The only way Cassandra can execute this query is by retrieving all the rows from the table amazon and then by filtering out the ones which do not have the requested value for the price column.
If your table contains for example a 1 million rows and 95% of them have the requested value for the time1 column, the query will still be relatively efficient and you should use ALLOW FILTERING.
On the other hand, if your table contains 1 million rows and only 2 rows contain the requested value for the time1 column, your query is extremely inefficient. Cassandra will load 999, 998 rows for nothing. If the query is often used, it is probably better to add an index on the price column.
Create an index for price
CREATE INDEX price_idx ON amazon (price);
Q1 Find products by price
SELECT id,title,manufacturer,price FROM amazon WHERE price = 49.95;
id | title | manufacturer | price
------------+--------------------------------------------------------+-----------------------+-------
b000ezp0n8 | delorme topo usa mapping software 6.0 west region | delorme | 49.95
b000lu89yi | system care professional | avanquest | 49.95
b000s604de | art text | belight software | 49.95
b000bhq5jw | microsoft sql server developer edition 2005 cd/dvd | microsoft software | 49.95
b000q6zk3a | microsoft student with encarta premium 2008 dvd | microsoft | 49.95
b000nknb62 | pc cillin internet sec 2007 lics large box 3u | trend micro | 49.95
b000o39yge | macdrive 7 | mediafour corporation | 49.95
b000b6vlh4 | reference success deluxe 2006 | topics entertainment | 49.95
b000i5wcp8 | american idol extreme music creator | sony media | 49.95
b000eiau3u | microsoft office & windows training professional | topics entertainment | 49.95
b000in6trc | spy sweeper w/antivirus family edition | webroot software | 49.95
b000cditbc | ae mappoint 2006 cd | microsoft corp | 49.95
b000gcbov6 | the movies (mac) | feral interactive | 49.95
b000nkfee4 | finale songwriter 2007 | finale | 49.95
b000saufbg | partition commander 10 | avanquest | 49.95
b000hed3fi | the print shop 22 deluxe sb cs by the print shop | encore software | 49.95
b00070j14y | super solvers - spellbound! ! - school edition for mac | learning company | 49.95
b000ezmxz6 | delorme topo usa mapping software 6.0 east region | delorme | 49.95
b000ik9s00 | vcomm system suite professional 7 | avanquest | 49.95
b000cszg2m | gen-x media made easy vol. 3 | gen-x | 49.95
(20 rows)
Q2 Find products by manufacturer
SELECT token(id),id,title,manufacturer,price FROM amazon WHERE manufacturer = 'microsoft';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
CREATE INDEX manufacturer_idx ON amazon (manufacturer);
SELECT token(id),id,title,manufacturer,price FROM amazon WHERE manufacturer = 'microsoft';
system.token(id) | id | title | manufacturer | price
----------------------+------------+-------------------------------------------------------------------------------------+--------------+----------
-8818665322887908242 | b000hcxklc | microsoft outlook 2007 | microsoft | 109.95
-8715633220437458341 | b000barbaq | microsoft visual studio team suite 2005 with msdn premium (cd & dvd) | microsoft | 10949
-8684181460069117216 | b0002nt5ho | microsoft virtual pc for mac 7.0 with windows xp home | microsoft | 219.99
-8376342511109441745 | b000bawkpm | microsoft visual studio team edition for software developers 2005 with msdn premium | microsoft | 5479
-8336513571715137501 | b0000ca2ix | mcsa/mcse self-paced training kit (exams 70-292 & 70-296) - upgrading your certi | microsoft | 70
-7984771122522275384 | b000m7uqjg | windows vista business spanish full version [dvd] | microsoft | 329.95
-7969744930844261617 | b00022pti4 | microsoft windows xp professional full version with sp2 | microsoft | 299.99
-4615340255121702735 | b000q6zk3a | microsoft student with encarta premium 2008 dvd | microsoft | 49.95
-4557375610380639604 | b000hcxkks | microsoft office small business 2007 full version | microsoft | 449.95
-4537752846481144826 | b000mfgx7w | microsoft windows vista business 32-bit for system builders | microsoft | 179.99
-4275971327442174464 | b000cc09uw | microsoft mappoint 2006 standard edition | microsoft | 299
-4076243686318432050 | b0001wn16m | microsoft office student and teacher edition 2004 macintosh | microsoft | 149.99
-3921631172128499112 | b000jywfq4 | softgrid cal for ts 4.1 english mlp 20 user cal | microsoft | 0
-3815444987423882877 | b0000c6fjm | microsoft office frontpage 2003 ( academic version ) ( windows ) | microsoft | 159.98
-3666324185405076871 | b000hcz8gm | microsoft visio standard 2007 version upgrade | microsoft | 129.95
-2079248264659588153 | b0001wn1fs | microsoft word 2004 (mac) | microsoft | 229.99
-2079154390880765845 | b0000cg4gt | small business server 2003 20u cal addpak user | microsoft | 0
-1713685351042804902 | b000ayktam | microsoft digital image suite 2006 plus pinnacle studio 10.0 | microsoft | 99.95
-1546763913058753596 | b000a76zt8 | microsoft works suite 2006 | microsoft | 99
-1146477399578103488 | b000hcvr5s | microsoft word 2007 version upgrade | microsoft | 109.95
-569039483801840237 | b000fa5ens | mom standard ops mgmt lic 2005 english mlp 5 oml | microsoft | 0
-566266855295471955 | b000hcvr1m | microsoft office excel 2007 | microsoft | 229.95
2050390938317034500 | b000gcj6mk | microsoft flight simulator x deluxe dvd | microsoft | 49.95
2698610662295347770 | b000hctyto | microsoft windows vista ultimate upgrade [dvd] | microsoft | 259.95
3153553172992448681 | b000m2yc32 | microsoft onenote 2007 upgrade | microsoft | 79.95
3187621094616539027 | b000hcz8ey | microsoft onenote 2007 | microsoft | 99.95
3655332182345107796 | b000jiojvo | exchange svr ent 2007 x64 eng dvd 25 clt | microsoft | 11318.25
3911709599859484683 | b00004nhn7 | services for netware 5.0 | microsoft | 149
4218983710022588114 | b000hcz9ac | microsoft windows vista home basic upgrade [dvd] | microsoft | 99.95
4256672475209658373 | b00002jvfk | microsoft entertainment pack ce | microsoft | 49.5
4667552977245312724 | b000hctyte | microsoft windows vista ultimate full version [dvd] | microsoft | 399.95
6915264727826988404 | b000bhl1r8 | microsoft sql server standard edition 2005 64 bit cd/dvd 5 client | microsoft | 1849
7433747869765935575 | b000gh2pa0 | microsoft windows small business server premium 2003 r2 upgrade cd/dvd 5 client | microsoft | 709
7700821018469795566 | b0001wn0sq | microsoft word 2004 upgrade (mac) | microsoft | 109.99
7798835662794443601 | b0001wn0m2 | microsoft office 2004 standard (mac) | microsoft | 399.99
8588545889297815327 | b00022ptru | microsoft windows xp home edition full version with sp2 | microsoft | 199
8597349641973689324 | b00008mnxz | microsoft windows terminal server 2003 client additional license for users- 5 user | microsoft | 669
8743923524629772112 | b0001wn18u | microsoft excel 2004 (mac) | microsoft | 229.99
9058523046735377448 | b000i3g30k | money home & business 2007 win32 eng na mini box us only cd | microsoft | 139.43
(39 rows)
Q3 Find product by price and manufacturer
SELECT id,title,manufacturer,price FROM amazon WHERE price = 49.95 AND manufacturer = 'sony media';
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
CREATE TABLE amazon_by_price_manufacturer (
id text,
title text,
description text,
manufacturer text,
price double,
PRIMARY KEY (price, manufacturer)
);
COPY amazon_by_price_manufacturer (id, title, description, manufacturer, price) FROM 'Amazon.csv';
SELECT id,title,manufacturer,price FROM amazon_by_price_manufacturer WHERE price = 49.95 AND manufacturer = 'sony media';
id | title | manufacturer | price
------------+-------------------------------------+--------------+-------
b000i5wcp8 | american idol extreme music creator | sony media | 49.95
(1 rows)