def get_tables():
ret = {}
logger = logging.getLogger("extract_schema")
logger.info("creating cursor")
cur = conn.cursor()
try:
cur.execute("show tables;")
for row in cur:
name = row[1]
logger.info("Found table %s", name)
cur2 = conn.cursor()
cur2.execute(f"describe table {name};")
col = []
for row2 in cur2:
cname = row2[0]
type = row2[1]
col.append((cname, type))
logger.debug("Found column %s (%s)", cname, type)
cur2.close()
ret[name] = col
finally:
cur.close()
return ret
def train(currency='USD'):
sql_command = ''' select date, rate_nb from exchange_rates
where currency_pair_id = (select id from currency_pairs where currency = '{}')
order by date;'''.format(currency)
data = pd.read_sql(sql_command, conn)
lendf = len(data)
train_df = data[:int(lendf * 0.995)]
test_df = data[int(lendf * 0.995):]
arima = ARIMAC(data, train_df, test_df, 'rate_nb')
arima.getBestParamsARIMA()
rmse, results = arima.modelTrain()
print(results)
print('Best order = {}'.format(arima.order))
print('RMSE = {}'.format(rmse))
cursor = conn.cursor()
cursor.execute('''insert into arima_params (p, d, q, currency, date, rmse)
values ({}, {}, {}, '{}', '{}', {}) on conflict do nothing'''
.format(arima.order[0], arima.order[1], arima.order[2],
currency, str(date.today()), rmse))
conn.commit()
cursor.close()
plt.figure(figsize=(14, 5))
plt.plot(data['date'], data['rate_nb'])
plt.plot(results['date'], results['predicted'])
plt.plot(results['date'], results['rate_nb'])
plt.show()
def dump_tokens():
c = conn.cursor()
res = c.execute("""
SELECT * FROM tokens;
""")
# 2D list of lists
return c.fetchall()
def main(scale, schema, runs, warm, end):
queries = load_queries()
logger.info("Loaded %d queries", len(queries))
db = "SNOWFLAKE_SAMPLE_DATA" if schema else "TCPH_SCHEMALESS"
schema_name = f"TPCH_SF{scale}"
schema_id = "SCHEMA" if schema else "SCHEMALESS"
with conn_vw(name = f'TEMPORARY_{schema_id}_SF{scale}', size='MEDIUM') as conn:
logger.info("Running on database %s", db)
cur = conn.cursor()
cur.execute(f"USE DATABASE {db};")
logger.info("Running on schema %s", db)
cur.execute(f"USE SCHEMA {schema_name};")
logger.info("Disabling result set cache!")
cur.execute("ALTER SESSION SET USE_CACHED_RESULT = FALSE;")
cur.close()
filename = f"results_{schema_id}_SF{scale}.csv"
filepath = os.path.join("results", filename)
logger.info("Writing results to %s", filepath)
with open(filepath, "w") as f:
header = ["Query"]+[f"Run {i+1}" for i in range(runs)] + ["Average", "Standard Deviation"]
write_row(f, header)
for q in queries[:end]:
timings, avg, std = time_query(q, conn, runs=runs, warmups=warm)
timings = list(timings)
timings += [avg / 1000.0, std / 1000.0]
timings = [f"{q.num}"] + [f"{x:.06f}" for x in timings]
write_row(f, timings)
def main(scale):
logger = logging.getLogger("create_schemaless")
tables = get_tables()
logger.info("Creating schemaless version of scale factor %d", scale)
schema_name = f"TPCH_SF{scale}"
with conn_vw(name='temporary2') as conn:
logger.info("Creating schema %s", schema_name)
cur = conn.cursor()
cur.execute("use database TCPH_SCHEMALESS;")
cur.execute(f"create or replace schema {schema_name}")
cur.execute(f"use schema {schema_name}")
logger.info("Creating stage TMP")
cur.execute(f"CREATE OR REPLACE STAGE TCPH_SCHEMALESS.{schema_name}.TMP")
logger.info(f"Creating export file format")
cur.execute(f"CREATE OR REPLACE FILE FORMAT TCPH_SCHEMALESS.{schema_name}.export TYPE = 'JSON' COMPRESSION = 'AUTO' ENABLE_OCTAL = FALSE ALLOW_DUPLICATE = FALSE STRIP_OUTER_ARRAY = FALSE STRIP_NULL_VALUES = TRUE IGNORE_UTF8_ERRORS = FALSE;")
for tbl, cols in tables.items():
table_name = f"{tbl}_RAW"
logger.info("Creating table %s", table_name)
cur.execute(f"CREATE OR REPLACE TABLE \"{table_name}\" (\"SRC\" VARIANT);")
stage_path = f"@TMP/{tbl}/"
logger.info("Copying data for table %s to temp files %s", tbl, stage_path)
cur.execute(f"copy into {stage_path} from (select object_construct(*) from \"SNOWFLAKE_SAMPLE_DATA\".\"{schema_name}\".\"{tbl}\") file_format = (format_name = export)")
logger.info("Copying data from temp path %s into actual table", stage_path)
cur.execute(f"copy into {table_name} from {stage_path} file_format = (format_name = export)")
logger.info("Deleting temp files from %s", stage_path)
cur.execute(f"rm {stage_path}")
cur.close()
def dump_tokens():
c = conn.cursor()
res = c.execute("""
SELECT * FROM tokens;
""")
# 2D list of lists
return c.fetchall()
def get_by_username(username):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM users WHERE userName= %s"
cur.execute(sql_string, (username, ))
info = cur.fetchone()
conn.close()
return info
def prune_temp_request():
""" delete temporary requests tables. Should be called at app startup """
c = conn.cursor()
res = c.execute("""
DELETE FROM temp_request(userid,reqsec)
""")
conn.commit()
return True
def get_email(email):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM users WHERE email= %s"
cur.execute(sql_string, (email, ))
info = cur.fetchone()
conn.close()
return info
def prune_temp_request():
""" delete temporary requests tables. Should be called at app startup """
c = conn.cursor()
res = c.execute("""
DELETE FROM temp_request(userid,reqsec)
""")
conn.commit()
return True
def get_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM users WHERE userID= %s"
cur.execute(sql_string, (user_id, ))
info = cur.fetchone()
conn.close()
return info[1]
def delete_review(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "DELETE FROM review WHERE placeID = %s"
cur.execute(sql_string, (place_id, ))
conn.commit()
cur.close()
conn.close()
def block_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "UPDATE users SET isBlock = 1 WHERE userID = %s "
cur.execute(sql_string, (user_id, ))
conn.commit()
cur.close()
conn.close()
def post_review(place_id, user_id, review, rating):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO review (placeID, userID, reviewDate, review,rating) VALUES(%s, %s,now(), %s, %s)"
cur.execute(sql_string, (place_id, user_id, review, rating))
conn.commit()
cur.close()
conn.close()
def approve_place(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "UPDATE places SET approved = 1 WHERE placeID = %s "
cur.execute(sql_string, (place_id, ))
conn.commit()
cur.close()
conn.close()
def signup(username, email, password):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO users (userName, email, password) VALUES(%s, %s, %s)"
cur.execute(sql_string, (username, email, password))
conn.commit()
cur.close()
conn.close()
def get_place_review(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM review WHERE placeID =%s ORDER BY reviewDate DESC"
cur.execute(sql_string, (place_id, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def get_review_by_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM review WHERE userID =%s ORDER BY reviewDate DESC"
cur.execute(sql_string, (user_id, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def get_to_approve():
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE approved = false ORDER BY postedDate DESC"
cur.execute(sql_string, )
info = cur.fetchall()
cur.close()
conn.close()
return info
def get_place_by_id(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE placeID = %s"
cur.execute(sql_string, (place_id, ))
info = cur.fetchone()
cur.close()
conn.close()
return info
def get_place_by_name(name):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE name = %s"
cur.execute(sql_string, (name, ))
info = cur.fetchone()
cur.close()
conn.close()
return info
def get_all_place_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT placeID,image_location FROM places WHERE userID =%s ORDER BY postedDate DESC"
cur.execute(sql_string, (user_id, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def run_query(q: query, conn):
#q.build_args()
sql = q.get_sql()
cur = conn.cursor()
start = time.time()
cur.execute(sql)
diff = time.time() - start
cur.close()
return diff
def new_place(user_id, name, address, website, phone, image_loc, description):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO places (userID, name, address, website, phone, image_location, description, postedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, now())"
cur.execute(
sql_string,
(user_id, name, address, website, phone, image_loc, description))
conn.commit()
cur.close()
conn.close()
def get_place_by_name1(name):
name = '%' + name + '%'
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE name LIKE %s"
cur.execute(sql_string, (name, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def main(scale, typed):
tables = get_tables()
cur = conn.cursor()
schema_name = f"TPCH_SF{scale}"
logger.info("Using schema %s", schema_name)
cur.execute(f"USE DATABASE TCPH_SCHEMALESS;")
cur.execute(f"USE SCHEMA {schema_name};")
for tbl, cols in tables.items():
col_sels = []
for col, type in cols:
type_str = ""
if typed:
type_str = f"::{type}"
col_sels.append(f"src:{col}{type_str} as {col}")
col_sel = ",\n\t".join(col_sels)
sql = f"""select
{col_sel}
from {tbl}_RAW"""
with open(os.path.join("views", f"{tbl}.sql"), "w") as f:
f.write(sql)
logger.info("Creating view %s with sql %s", tbl, sql)
cur.execute(f"CREATE OR REPLACE VIEW {schema_name}.{tbl} AS {sql}")
cur.close()
from connection import conn
import logging
import os
logger = logging.getLogger("extract_queries")
logger.info("creating cursor")
cur = conn.cursor()
try:
cur.execute("show views;")
for row in cur:
name = row[1]
text = row[7]
logger.info("Found view %s", name)
if "_" not in name:
# we only want raw queries!
# extract sql statement
as_idx = text.index("as ")
select_stmt = text[as_idx + 3:]
logger.debug("%s SQL: %s", name, select_stmt)
# save query to file
filename = f"{name}.sql"
with open(os.path.join("queries", filename), "w") as f:
f.write(select_stmt)
logger.info("saved sql to %s", filename)
finally:
cur.close()
def get_access_pair(userid):
""" return ( acc_key, acc_secret ) for provided userid or None """
c = conn.cursor()
c.execute(GET_ACCESS_PAIR, (userid, ))
return c.fetchone()
def delete_token(userid):
""" delete token with provided userid """
c = conn.cursor()
c.execute(DELETE_TOKEN, (userid, ))
conn.commit()
return c.rowcount == 1
def get_request_pair(userid):
""" return ( userid, req_secret ) pair for provided userid or None """
c = conn.cursor()
c.execute(GET_REQUEST_PAIR, (userid, ))
return c.fetchone()
def delete_unverified_request(userid):
""" Delete an user temporary request returns always True """
c = conn.cursor()
res = c.execute(DELETE_UNVERIFIED_REQUEST, (userid,))
conn.commit()
return True
def save_unverified_request( userid, req_secret ):
""" save unverified request keys """
c = conn.cursor()
res = c.execute(SAVE_UNVERIFIED_REQUEST, (userid, req_secret, userid))
conn.commit()
return True
def get_request_pair(userid):
""" return ( userid, req_secret ) pair for provided userid or None """
c = conn.cursor()
c.execute(GET_REQUEST_PAIR, (userid,))
return c.fetchone()
def delete_token(userid):
""" delete token with provided userid """
c = conn.cursor()
c.execute(DELETE_TOKEN, (userid,))
conn.commit()
return c.rowcount == 1
def get_access_pair(userid):
""" return ( acc_key, acc_secret ) for provided userid or None """
c = conn.cursor()
c.execute(GET_ACCESS_PAIR, (userid,))
return c.fetchone()
def save_access_tokens(userid, req_secret, acc_key, acc_secret):
c = conn.cursor()
c.execute(SAVE_ACCESS_TOKENS, (userid, req_secret, acc_key, acc_secret, userid))
conn.commit()
return c.rowcount == 1
def save_unverified_request(userid, req_secret):
""" save unverified request keys """
c = conn.cursor()
res = c.execute(SAVE_UNVERIFIED_REQUEST, (userid, req_secret, userid))
conn.commit()
return True
def delete_unverified_request(userid):
""" Delete an user temporary request returns always True """
c = conn.cursor()
res = c.execute(DELETE_UNVERIFIED_REQUEST, (userid, ))
conn.commit()
return True
