jKudQvpH

1drop keyspace datastax_demo;
2
3create keyspace if not exists datastax_demo WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1' };
4
5use datastax_demo;
6
7create table if not exists users(
8	user_id text PRIMARY KEY,
9	first_name text,
10	middle_name text,
11	last_name text,
12	dob timestamp,
13	street_address text,
14	zip_code text,
15	city_name text,
16	state_name text,
17	gender text,
18	phone_number text,
19	email text,
20	country_code text
21);
22
23create table if not exists user_interactions(
24	userid text,
25	id uuid,
26	clientid text,
27	correlationid text,
28	date timestamp,
29	details text,
30	event_type text,
31	user_agent text,
32	user_agent_filterd text,
33	reference text,
34	PRIMARY KEY (userid, date)
35) with clustering order by (date desc);
36
37create table if not exists user_points (
38	id text,
39	time timestamp,
40	balance int static,
41	balanceat timestamp static,
42	value int,
43	comment text,
44	PRIMARY KEY(id,time)
45)with clustering order by (time desc);
46
47create table if not exists latest_transactions(
48	cc_no text,
49	transaction_time timestamp,
50 	transaction_id text,
51 	user_id text,
52	location text,
53	items map<text, double>,
54	merchant text,
55	amount double,
56	status text,
57	notes text,
58	tags set<text>,
59	PRIMARY KEY (cc_no, transaction_time) 
60) WITH CLUSTERING ORDER BY (transaction_time desc)
61and caching = {'keys': 'NONE', 'rows_per_partition': '100000'};
62
63create index on latest_transactions(user_id);
64
65CREATE TABLE datastax_demo.user_start_end_visits (
66	user_id text, 
67	visit_id text, 
68	diff int, 
69	start timestamp, 
70	end timestamp, 
71	PRIMARY KEY (user_id, diff, visit_id)
72) with clustering order by (diff desc, visit_id asc);
73
74
75//
76CREATE OR REPLACE FUNCTION datastax_demo.filter_location_full (state set<frozen<tuple<text,timestamp,text, text,double, text, text>>>, 
77location text, filter text, transaction_id text, transaction_time timestamp, user_id text, amount double, merchant text, status text) 
78CALLED ON NULL INPUT 
79RETURNS set<frozen<tuple<text,timestamp,text, text, double, text, text>>>
80LANGUAGE java AS 	
81  'if (location.contains(filter)) {   
82  	com.datastax.driver.core.TupleType tupleType = com.datastax.driver.core.TupleType.of(com.datastax.driver.core.ProtocolVersion.NEWEST_SUPPORTED, 
83  	com.datastax.driver.core.CodecRegistry.DEFAULT_INSTANCE, 
84  	com.datastax.driver.core.DataType.text(), 
85  	com.datastax.driver.core.DataType.timestamp(), 
86  	com.datastax.driver.core.DataType.text(), 
87  	com.datastax.driver.core.DataType.text(), 
88  	com.datastax.driver.core.DataType.cdouble(), 
89  	com.datastax.driver.core.DataType.text(),
90  	com.datastax.driver.core.DataType.text()); 
91  	
92  	TupleValue v = tupleType.newValue(transaction_id, transaction_time, user_id, location, amount, merchant, status);  
93  	state.add(v); 
94  } return state;'; 
95
96//
97CREATE OR REPLACE AGGREGATE datastax_banking_iot.filter_location_full(text, text, text, timestamp, text, double, text, text) 
98SFUNC filter_location_full  STYPE set<frozen<tuple<text,timestamp,text, text, double, text, text>>>  INITCOND {};