-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathqueryprocessor.py
135 lines (108 loc) · 5.17 KB
/
queryprocessor.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
#!/usr/bin/python2.7
#
# This project done as part of CSE 512 Fall 2017
#
__author__ = "Prashant Gonarkar"
__version__ = "v0.1"
__email__ = "[email protected]"
import psycopg2
import os
import sys
RANGE_RATINGS_METADATA = 'rangeratingsmetadata'
ROUND_ROBIN_RATINGS_METADATA = 'roundrobinratingsmetadata'
RANGE_PARTITION_PREFIX = 'rangeratingspart'
ROUND_ROBIN_PARTITION_PREFIX = 'roundrobinratingspart'
RANGE_PARTITION_OUTPUT_NAME = 'RangeRatingsPart'
ROUND_ROBIN_PARTITION_OUTPUT_NAME = 'RoundRobinRatingsPart'
RANGE_QUERY_OUTPUT_FILE = 'RangeQueryOut.txt'
POINT_QUERY_OUTPUT_FILE = 'PointQueryOut.txt'
# Donot close the connection inside this file i.e. do not perform openconnection.close()
def RangeQuery(ratingsTableName, ratingMinValue, ratingMaxValue, openconnection):
#
# Range query on range partitions
try:
cur = openconnection.cursor()
# finding min boundary range of partition from metadata for given ratingMinValue
query = "select max(minrating) from {0} where minrating <= {1}".format(RANGE_RATINGS_METADATA,ratingMinValue)
cur.execute(query)
minpartboundary = cur.fetchone()[0]
# finding min boundary range of partition from metadata for given ratingMinValue
query = "select min(maxrating) from {0} where maxrating >= {1}".format(RANGE_RATINGS_METADATA,ratingMaxValue)
cur.execute(query)
maxpartboundary = cur.fetchone()[0]
# calculating the paratitions from metadata table where tuples of given ranges lies
query = "select partitionnum from {0} where maxrating >= {1} and maxrating <= {2}".format(RANGE_RATINGS_METADATA,minpartboundary,maxpartboundary)
cur.execute(query)
rows = cur.fetchall()
if os.path.exists(RANGE_QUERY_OUTPUT_FILE):
os.remove(RANGE_QUERY_OUTPUT_FILE)
for i in rows:
partitionname = RANGE_PARTITION_OUTPUT_NAME + repr(i[0])
query = "select * from {0} where rating >= {1} and rating <= {2}".format(partitionname, ratingMinValue, ratingMaxValue)
cur.execute(query)
rows2 = cur.fetchall()
with open(RANGE_QUERY_OUTPUT_FILE,'a+') as f:
for j in rows2:
f.write("%s," % partitionname)
f.write("%s," % str(j[0]))
f.write("%s," % str(j[1]))
f.write("%s\n" % str(j[2]))
#
# Range query on round robin paritions
# get no of round robin partitions
query = "select partitionnum from {0} ".format(ROUND_ROBIN_RATINGS_METADATA)
cur.execute(query)
rrpartitioncount = int(cur.fetchone()[0])
for i in range(rrpartitioncount):
partitionname = ROUND_ROBIN_PARTITION_OUTPUT_NAME + repr(i)
query = "select * from {0} where rating >= {1} and rating <= {2}".format(partitionname, ratingMinValue, ratingMaxValue)
cur.execute(query)
rows2 = cur.fetchall()
with open(RANGE_QUERY_OUTPUT_FILE,'a+') as f:
for j in rows2:
f.write("%s," % partitionname)
f.write("%s," % str(j[0]))
f.write("%s," % str(j[1]))
f.write("%s\n" % str(j[2]))
except Exception as ex:
print("Exception while processing RangeQuery: ",ex)
def PointQuery(ratingsTableName, ratingValue, openconnection):
# Point query for range partition
try:
cur = openconnection.cursor()
if ratingValue == 0:
rangepartitionnum = 0
else:
query = "select partitionnum from {0} where minrating < {1} and maxrating >= {1}".format(RANGE_RATINGS_METADATA,ratingValue)
cur.execute(query)
rangepartitionnum = cur.fetchone()[0]
partitionname = RANGE_PARTITION_OUTPUT_NAME + repr(rangepartitionnum)
query = "select * from {0} where rating = {1} ".format(partitionname, ratingValue )
cur.execute(query)
rows2 = cur.fetchall()
if os.path.exists(POINT_QUERY_OUTPUT_FILE):
os.remove(POINT_QUERY_OUTPUT_FILE)
with open(POINT_QUERY_OUTPUT_FILE,'a+') as f:
for j in rows2:
f.write("%s," % partitionname)
f.write("%s," % str(j[0]))
f.write("%s," % str(j[1]))
f.write("%s\n" % str(j[2]))
#
# Point query for round robin partition
query = "select partitionnum from {0} ".format(ROUND_ROBIN_RATINGS_METADATA)
cur.execute(query)
rrpartitioncount = int(cur.fetchone()[0])
for i in range(rrpartitioncount):
partitionname = ROUND_ROBIN_PARTITION_OUTPUT_NAME + repr(i)
query = "select * from {0} where rating = {1} ".format(partitionname, ratingValue )
cur.execute(query)
rows2 = cur.fetchall()
with open(POINT_QUERY_OUTPUT_FILE,'a+') as f:
for j in rows2:
f.write("%s," % partitionname)
f.write("%s," % str(j[0]))
f.write("%s," % str(j[1]))
f.write("%s\n" % str(j[2]))
except Exception as ex:
print("Exception while processing RangeQuery: ",ex)