kjuUMmXx

1drop keyspace if exists warehouse;
2CREATE KEYSPACE Warehouse WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};
3USE Warehouse;
4
5CREATE TABLE warehouse ( 
6    W_ID INT, 
7    W_NAME VARCHAR, 
8    W_STREET_1 VARCHAR, 
9    W_STREET_2 VARCHAR, 
10    W_CITY VARCHAR, 
11    W_STATE VARCHAR, 
12    W_ZIP VARCHAR,
13    W_TAX DECIMAL,
14    PRIMARY KEY(W_ID)
15);
16
17CREATE TABLE warehouse_cnts (
18    W_ID INT,
19    W_YTD counter,
20    PRIMARY KEY (W_ID)
21);
22
23CREATE TABLE district ( 
24    D_W_ID INT, 
25    D_ID INT, 
26    D_NAME VARCHAR, 
27    D_STREET_1 VARCHAR, 
28    D_STREET_2 VARCHAR, 
29    D_CITY VARCHAR, 
30    D_STATE VARCHAR, 
31    D_ZIP VARCHAR,
32    D_TAX DECIMAL,
33    PRIMARY KEY( (D_ID), D_W_ID )
34);
35
36CREATE TABLE district_cnts (
37    D_W_ID INT,
38    D_ID INT,
39    D_YTD COUNTER,
40    D_NEXT_O_ID COUNTER,
41    PRIMARY KEY( (D_W_ID), D_ID )
42);
43
44CREATE TABLE customer ( 
45    C_W_ID INT, 
46    C_D_ID INT, 
47    C_ID INT, 
48    C_FIRST VARCHAR, 
49    C_MIDDLE VARCHAR, 
50    C_LAST VARCHAR, 
51    C_STREET_1 VARCHAR, 
52    C_STREET_2 VARCHAR, 
53    C_CITY VARCHAR, 
54    C_STATE VARCHAR, 
55    C_ZIP VARCHAR,
56    C_PHONE VARCHAR,
57    C_SINCE TIMESTAMP,
58    C_CREDIT VARCHAR,
59    C_CREDIT_LIM DECIMAL,
60    C_DISCOUNT DECIMAL,
61    C_DATA VARCHAR,
62    PRIMARY KEY( (C_D_ID), C_W_ID, C_ID)
63);
64
65CREATE TABLE customer_top_ten (
66    C_W_ID INT,
67    C_D_ID INT,
68    C_ID INT,
69    C_BALANCE BIGINT,
70    PRIMARY KEY( (C_D_ID), C_W_ID, C_ID)
71);
72
73CREATE TABLE customer_cnts (
74    C_W_ID INT,
75    C_D_ID INT,
76    C_ID INT,
77    C_BALANCE COUNTER,
78    C_YTD_PAYMENT COUNTER,
79    C_PAYMENT_CNT COUNTER,
80    C_DELIVERY_CNT COUNTER,
81    PRIMARY KEY( (C_D_ID), C_W_ID, C_ID)
82);
83
84CREATE TABLE orders ( 
85    O_W_ID INT, 
86    O_D_ID INT, 
87    O_ID INT, 
88    O_C_ID INT, 
89    O_CARRIER_ID INT, 
90    O_OL_CNT DECIMAL, 
91    O_ALL_LOCAL DECIMAL, 
92    O_ENTRY_D TIMESTAMP,
93    PRIMARY KEY((O_D_ID, O_W_ID), O_ID)
94) WITH CLUSTERING ORDER BY (O_ID asc);
95
96CREATE MATERIALIZED VIEW order_by_customer 
97as select O_W_ID, O_D_ID, O_ID, O_C_ID
98from orders
99where O_W_ID is not null
100and O_D_ID is not null
101and O_ID is not null
102and O_C_ID is not null
103primary key ((O_W_ID, O_D_ID, O_C_ID), O_ID);
104
105CREATE TABLE item ( 
106    I_ID INT, 
107    I_NAME VARCHAR, 
108    I_PRICE DECIMAL, 
109    I_IM_ID INT, 
110    I_DATA VARCHAR,
111    PRIMARY KEY(I_ID)
112);
113
114CREATE TABLE order_line ( 
115    OL_W_ID INT, 
116    OL_D_ID INT, 
117    OL_O_ID INT, 
118    OL_NUMBER INT, 
119    OL_I_ID INT, 
120    OL_DELIVERY_D TIMESTAMP, 
121    OL_AMOUNT DECIMAL, 
122    OL_SUPPLY_W_ID INT, 
123    OL_QUANTITY DECIMAL, 
124    OL_DIST_INFO VARCHAR,
125    PRIMARY KEY((OL_D_ID, OL_W_ID) , OL_O_ID, OL_NUMBER)
126);
127
128
129CREATE MATERIALIZED VIEW order_line_by_item 
130as select OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER, OL_I_ID
131from order_line
132where OL_W_ID is not null
133and OL_D_ID is not null
134and OL_O_ID is not null
135and OL_NUMBER is not null
136and OL_I_ID is not null
137primary key ((OL_D_ID, OL_I_ID), OL_W_ID, OL_NUMBER, OL_O_ID);
138
139CREATE TABLE stock (
140    S_W_ID INT,
141    S_I_ID INT,
142    S_DIST_01 VARCHAR,
143    S_DIST_02 VARCHAR,
144    S_DIST_03 VARCHAR,
145    S_DIST_04 VARCHAR,
146    S_DIST_05 VARCHAR,
147    S_DIST_06 VARCHAR,
148    S_DIST_07 VARCHAR,
149    S_DIST_08 VARCHAR,
150    S_DIST_09 VARCHAR,
151    S_DIST_10 VARCHAR,
152    S_DATA VARCHAR,
153    PRIMARY KEY((S_W_ID), S_I_ID)
154);
155
156CREATE TABLE stock_cnts (
157    S_W_ID INT, 
158    S_I_ID INT,
159    S_QUANTITY COUNTER, 
160    S_YTD COUNTER, 
161    S_ORDER_CNT COUNTER,
162    S_REMOTE_CNT COUNTER,
163    PRIMARY KEY((S_W_ID), S_I_ID)
164);