OmniSciDB  fe05a0c208
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
bench_update.py
Go to the documentation of this file.
1 import argparse
2 import sys
3 import pymapd
4 import pandas as pd
5 import numpy as np
6 
7 from pathlib import Path
8 
9 def getOptions(args=None):
10  parser = argparse.ArgumentParser(description='Basic benchmark for update queries')
11  parser.add_argument('-s','--host', help='OmniSci server address', default='localhost')
12  parser.add_argument('-p','--port', help='OmniSci server port', default='6273')
13  parser.add_argument('-d','--db', help='OmniSci database name', default='omnisci')
14  parser.add_argument('-u','--user', help='OmniSci user name', default='admin')
15  parser.add_argument('-w','--password', help='OmniSci password', default='HyperInteractive')
16  parser.add_argument('-r', '--num_rows', help='Number of rows to benchmark with', type=int, default=1_000_000)
17  parser.add_argument('-t', '--tag', help='Tag for test run')
18  return parser.parse_args(args)
19 
20 class OmniSciCon:
21  def __init__(self, user, pw, dbname, host):
22  self.con = pymapd.connect(user=user, password=pw, dbname=dbname, host=host)
23  self.cursor = self.con.cursor()
24 
25  def query(self, sql):
26  return self.cursor.execute(sql)
27 
28 def create_and_import_into_table(omnisci_con, table_name, file_path):
29  drop_sql = f"DROP TABLE IF EXISTS {table_name}"
30  create_sql = f"CREATE TABLE {table_name} (a INTEGER, b INTEGER, c INTEGER, d DOUBLE)"
31  import_sql = f"COPY {table_name} FROM '{file_path.absolute()}'"
32  omnisci_con.query(drop_sql)
33  omnisci_con.query(create_sql)
34  omnisci_con.query(import_sql)
35 
36 def gen_data(num_rows):
37  df = pd.DataFrame(np.random.randint(0, num_rows, size=(num_rows, 4)), columns=['a', 'b', 'c', 'd'])
38  df = df.astype(np.int32)
39  return df
40 
41 def bench_update_query(omnisci_con, table_name, tag):
42  query = f"UPDATE {table_name} SET a = a + 10, c = c + b, d = d * 2 WHERE MOD(b, 2) = 0"
43  query_times = []
44  for i in range(10):
45  result = omnisci_con.query(query)
46  query_times.append(result._result.execution_time_ms)
47 
48  print(f"Test tag: {tag}\nQuery: {query}\nRaw times(ms): {query_times}\n"
49  f"Avg: {np.average(query_times)}\nMin: {np.min(query_times)}\n"
50  f"Max: {np.max(query_times)}\nMedian: {np.median(query_times)}")
51 
52 def main(argv):
53  options = getOptions(argv)
54  omnisci_con = OmniSciCon(options.user, options.password, options.db, options.host)
55 
56  file_path = Path(f"{options.num_rows}_rand.csv")
57  if not file_path.exists():
58  df = gen_data(options.num_rows)
59  df.to_csv(file_path, index=False)
60 
61  table_name = "update_bench_test"
62  create_and_import_into_table(omnisci_con, table_name, file_path)
63 
64  bench_update_query(omnisci_con, table_name, options.tag)
65 
66 if __name__ == "__main__":
67  main(sys.argv[1:])
def create_and_import_into_table
Definition: bench_update.py:28
def bench_update_query
Definition: bench_update.py:41