-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathfaketickets.py
168 lines (142 loc) · 7.22 KB
/
faketickets.py
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
##########################################
#
# Romain Anselin - DataStax - 2022
# File: faketickets.py
# Function: manage specific faketickets
# related queries
#
##########################################
import random
from datetime import datetime, timedelta
from cassandra import ConsistencyLevel
from cassandra.query import SimpleStatement
from cassandra.query import PreparedStatement
import helper
### Fake_tickets
def create_table_fake_tickets(session, ksname, tblname):
session.execute("CREATE TABLE IF NOT EXISTS " + ksname + "." + tblname + """(
id int,
ownedby text,
ticket int,
time timestamp,
updatedat timestamp,
notes text,
PRIMARY KEY (id)
);
""")
### Sample inserts
def insert_bind_fake_tickets(session, ksname, tblname):
ticket_insert = "INSERT INTO " + ksname + "." + tblname + "(id, ownedby, ticket, time, notes) VALUES (?, ?, ?, ?, ?) ;"
ticketins_prep = session.prepare(ticket_insert)
ticketins_prep.consistency_level = ConsistencyLevel.LOCAL_QUORUM
return ticketins_prep
def insert_bind_fake_tickets_ttl(session, ksname, tblname):
ttl = 86400
ticket_insert = "INSERT INTO " + ksname + "." + tblname + "(id, ownedby, ticket, time, notes) VALUES (?, ?, ?, ?, ?) USING TTL " + ttl + " ;"
ticketins_prep = session.prepare(ticket_insert)
ticketins_prep.consistency_level = ConsistencyLevel.LOCAL_QUORUM
return ticketins_prep
def insert_bind_fake_tickets_lwt(session, ksname, tblname):
ticket_insert = "INSERT INTO " + ksname + "." + tblname + "(id, ownedby, ticket, time, notes) VALUES (?, ?, ?, ?, ?) IF NOT EXISTS ;"
ticketins_prep = session.prepare(ticket_insert)
ticketins_prep.consistency_level = ConsistencyLevel.LOCAL_QUORUM
ticketins_prep.serial_consistency_level = ConsistencyLevel.LOCAL_SERIAL
return ticketins_prep
### Materizalized view
def create_mv_fake_tickets(session, ksname, tblname):
mvname='mv_fake_tickets'
session.execute("CREATE MATERIALIZED VIEW IF NOT EXISTS " + ksname + "." + mvname + """(
AS select ownedby, time, id, notes, ticket
FROM """ + ksname + "." + tblname + """
WHERE ownedby is not null PRIMARY KEY (ownedby, id) ;
""")
### SELECT and COUNT
def select_bind_fake_tickets(session, ksname, tblname):
ticket_select = """
SELECT id, ownedby, ticket, time, notes
FROM """ + ksname + "." + tblname + """
WHERE id = ?
"""
ticketsel_prep = session.prepare(ticket_select)
ticketsel_prep.consistency_level=ConsistencyLevel.QUORUM
return ticketsel_prep
def select_count_bind_fake_tickets(session, ksname, tblname):
ticket_sel_count = """
SELECT count(*)
FROM """ + ksname + "." + tblname + """
WHERE id = ?
"""
ticketselcnt_prep = session.prepare(ticket_sel_count)
return ticketselcnt_prep
# Create SAI
def create_sai_fake_tickets(session, ksname, tblname):
idx = "ownedby"
# !!! TIL: Originally, index called dept_sai_idx hardcoded. It will not create the index as it exists for another table, but it will not error out either !!!
try:
session.execute("CREATE CUSTOM INDEX IF NOT EXISTS " + tblname + "_sai_idx ON " + ksname + "." + tblname + "(" + idx + ") USING 'StorageAttachedIndex'")
except:
print ("ERROR: Unable to create index:\n" "CREATE CUSTOM INDEX IF NOT EXISTS " + tblname + "_sai_idx ON " + ksname + "." + tblname + "(" + idx + ") USING 'StorageAttachedIndex'")
def create_solr_fake_tickets(session, ksname, tblname):
print("Creating Solr Index on %s.%s" % (ksname, tblname))
session.execute("CREATE SEARCH INDEX IF NOT EXISTS ON " + ksname + "." + tblname + " WITH COLUMNS ownedby, updatedat, time, notes")
def fake_tickets_workflow(session, ksname, startval, numrec):
### Values implementation
tblname = "fake_tickets"
endval = startval + numrec
owner = [ "Romain", "Ryan", "Bettina", "Navaneetha", "Rachan", "Ivan", "Alberto", "Peter", "Pav", "Alkesh", "Uzoma", "Calum", "Cordell" ]
power10 = [1,10,100,1000,10000,100000,1000000,10000000]
# Create table if not exists
create_table_fake_tickets(session, ksname, tblname)
create_sai_fake_tickets(session, ksname, tblname)
# Insert Bind
ticketins_prep = insert_bind_fake_tickets(session, ksname, tblname)
# Select and count binds
ticketsel_prep = select_bind_fake_tickets(session, ksname, tblname)
ticketselcnt_prep = select_count_bind_fake_tickets(session, ksname, tblname)
# min_time = datetime.now()
min_time = helper.current_milli_time()
for i in range (startval, endval):
# timenow = datetime.now()
timenow = helper.current_milli_time()
pickowner = str(random.choice(owner))
pickticket = random.randint(0,100000)
if i%2 == 0:
datastr = 'Python writing data with value ' + str(i) + ' for ' + pickowner + ' at ' + str(helper.fromts(timenow))
else:
datastr = '%s Odd entries have a different data string for record %s for %s' % (str(helper.fromts(timenow)), str(i), pickowner)
### Bound statement
tickins_bind = ticketins_prep.bind((i, pickowner, pickticket, timenow, datastr))
# print(str(tickins_bind))
session.execute(tickins_bind)
# Show time for every 10k records
if i%10000 == 0:
print( "Written " + str(i) + " records so far, time now " + str(helper.dtn()) )
# Read every power of 10 the content
# if (helper.isPower10(i,10)):
if i in power10:
print( "Record " + str(i) + " reading now " + str( helper.dtn() ) )
startselread = helper.dtn()
### READ SCENARIO
ticksel_bind = ticketsel_prep.bind([i])
selrows = session.execute(ticksel_bind)
for row in selrows:
print(row.id, row.ownedby, row.ticket, row.time, row.notes)
### COUNT SCENARIO
tickselcnt_bind = ticketselcnt_prep.bind([i])
cntrows = session.execute(tickselcnt_bind)
for row in selrows:
print("Number of rows: " + str(row.count))
print( "Read execution finished: " + str( helper.dtn() - startselread ) )
# saiSimplestmt = SimpleStatement( "SELECT id, ownedby, ticket, time, notes FROM " + ksname + "." + tblname + " WHERE time >= %s AND time <= %s;" % (min_time, timenow) ,
# consistency_level=ConsistencyLevel.QUORUM)
# startsairead = helper.dtn()
# sai_rows = session.execute(saiSimplestmt)
# for sai_row in sai_rows:
# print(sai_row.id, sai_row.ownedby, sai_row.ticket, sai_row.time, sai_row.notes)
# print( "SAI READ " + str(i) + " FINISHED " + str( helper.dtn() - startsairead ) )
# saicntSimplestmt = SimpleStatement( "select count(*) FROM " + ksname + "." + tblname + " WHERE my_id = %s AND time >= %s AND time <= %s;" % (i, min_time, timenow) ,
# consistency_level=ConsistencyLevel.QUORUM)
# saicnt_rows = session.execute(saicntSimplestmt)
# for saicnt_row in saicnt_rows:
# print("Number of SAI rows: " + str(sai_row.count))
# saicnt_rows = session.execute(saicntSimplestmt)