<jupyter_start><jupyter_text>TPCH<jupyter_code>tpch = datasets.QueriesDataset('tpch:optimized')
tpch_sample = tpch.sample(n=500, strategy='bucketed')
v2_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v2/logs/three/version_0/checkpoints/epoch=37-step=1973453.ckpt'), 
    dataset=tpch, 
    encoder=v2.encoder, 
    model_cls=v2.model.GereltRegressor
)
v3_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v3/logs/three/version_0/checkpoints/epoch=56-step=2960180.ckpt'), 
    dataset=tpch, 
    encoder=v3.encoder, 
    model_cls=v3.model.GereltRegressor
)
v4_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v4/logs/three/version_0/checkpoints/epoch=38-step=2025386.ckpt'), 
    dataset=tpch, 
    encoder=v4.encoder, 
    model_cls=v4.model.GereltRegressor
)
v7_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v7/logs/three/version_1/checkpoints/epoch=30-step=1609922.ckpt'), 
    dataset=tpch, 
    encoder=v7.encoder, 
    model_cls=v7.model.GereltRegressor
)
v2_results = v2_estimater.evaluate(df=tpch_sample)
json.dumps(ge.evaluate_metrics(v2_results))
v3_results = v3_estimater.evaluate(df=tpch_sample)
json.dumps(ge.evaluate_metrics(v3_results))
v4_results = v4_estimater.evaluate(df=tpch_sample)
json.dumps(ge.evaluate_metrics(v4_results))
v7_results = v7_estimater.evaluate(df=tpch_sample)
json.dumps(ge.evaluate_metrics(v7_results))
ge.display_results(v3_results)
ge.display_results(v4_results)
ge.display_results(v7_results)<jupyter_output><empty_output><jupyter_text>Trained on THREE, Inference on TPCD<jupyter_code>tpcd = datasets.QueriesDataset('tpcd:optimized')
tpcd_sample = tpcd.sample(n=500, strategy='bucketed')
v2_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v2/logs/three/version_0/checkpoints/epoch=37-step=1973453.ckpt'), 
    dataset=tpcd, 
    encoder=v2.encoder, 
    model_cls=v2.model.GereltRegressor
)
v3_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v3/logs/three/version_0/checkpoints/epoch=56-step=2960180.ckpt'), 
    dataset=tpcd, 
    encoder=v3.encoder, 
    model_cls=v3.model.GereltRegressor
)
v4_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v4/logs/three/version_0/checkpoints/epoch=38-step=2025386.ckpt'), 
    dataset=tpcd, 
    encoder=v4.encoder, 
    model_cls=v4.model.GereltRegressor
)
v7_estimater = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v7/logs/three/version_1/checkpoints/epoch=30-step=1609922.ckpt'), 
    dataset=tpcd, 
    encoder=v7.encoder, 
    model_cls=v7.model.GereltRegressor
)
v2_results = v2_estimater.evaluate(df=tpcd_sample)
print(json.dumps(ge.evaluate_metrics(v2_results)))
v3_results = v3_estimater.evaluate(df=tpcd_sample)
print(json.dumps(ge.evaluate_metrics(v3_results)))
v4_results = v4_estimater.evaluate(df=tpcd_sample)
print(json.dumps(ge.evaluate_metrics(v4_results)))
v7_results = v7_estimater.evaluate(df=tpcd_sample)
print(json.dumps(ge.evaluate_metrics(v7_results)))<jupyter_output>4%|▍         | 19/467 [00:03<01:35,  4.67it/s]/usr/local/anaconda3/envs/py37/lib/python3.7/site-packages/numpy/core/fromnumeric.py:3622: RuntimeWarning: Degrees of freedom <= 0 for slice
  **kwargs)
/usr/local/anaconda3/envs/py37/lib/python3.7/site-packages/numpy/core/_methods.py:227: RuntimeWarning: invalid value encountered in double_scalars
  ret = ret.dtype.type(ret / rcount)
100%|██████████| 467/467 [01:36<00:00,  4.84it/s]
  0%|          | 0/467 [00:00<?, ?it/s]<jupyter_text>NUMBERS VS STRINGS<jupyter_code>qe = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v2/logs/three_opt_default_adam/version_1/checkpoints/epoch=60.ckpt'), 
    dataset=imdb, 
    encoder=v2.encoder, 
    model_cls=v2.model.GereltRegressor
)
r = qe.evaluate(df=datasets.QueriesDataset('imdb:only_strings').load())
json.dumps(ge.evaluate_metrics(r))
qe = ge.QueryEstimater(
    os.path.join(os.getcwd(), 'v2/logs/three_opt_default_adam/version_1/checkpoints/epoch=60.ckpt'), 
    dataset=imdb, 
    encoder=v2.encoder, 
    model_cls=v2.model.GereltRegressor
)
r = qe.evaluate(df=datasets.QueriesDataset('imdb:only_numbers').load())
json.dumps(ge.evaluate_metrics(r))<jupyter_output>2021-01-25 15:50:22,596 | dqo.datasets | INFO | Reading data from /Users/danieldubovski/projects/deep_query_optimization/dqo/datasets/imdb/execution/only_numbers/runtimes/data_part_00.csv [1/1]
2021-01-25 15:50:22,601 | dqo.datasets | INFO | Loaded 124 rows from /Users/danieldubovski/projects/deep_query_optimization/dqo/datasets/imdb/execution/only_numbers/runtimes/data_part_00.csv [1/1]
100%|██████████| 124/124 [00:07<00:00, 16.15it/s]
/usr/local/anaconda3/envs/py37/lib/python3.7/site-packages/sklearn/metrics/_classification.py:1814: UserWarning: y_pred contains classes not in y_true
  warnings.warn('y_pred contains classes not in y_true')<jupyter_text>RANDOM QUERIES<jupyter_code>v1_results.predict_query('select MIN(id) from aka_title')
v1_results.predict_query('select MIN(id) from aka_title, cast_info where aka_title.movie_id = cast_info.movie_id and cast_info.note like "%a%"')
v1_results.predict_query('select MIN(id) from aka_title, cast_info where aka_title.movie_id = cast_info.movie_id and aka_title.movie_id > 10')
v1_results.predict_query('select MIN(id) from aka_title, cast_info where aka_title.movie_id = cast_info.movie_id')
v1_results.predict_query('select MIN(id) from cast_info')
# overfitting
import re

epoch_num_regex = re.compile("epoch=(\d.*)-")
cp_path = os.path.join(os.getcwd(), 'v7/logs/three/version_1/checkpoints/')
cps = [os.path.join(cp_path, f) for f in os.listdir(cp_path)]
cps = sorted(cps, key= lambda s: int(epoch_num_regex.search(s)[1]))

v = v7
for cp in tqdm(cps):
    qe = ge.QueryEstimater(cp, dataset=tpcd, encoder=v.encoder, model_cls=v.model.GereltRegressor)
    r = qe.evaluate(df=tpcd_sample)
    print(int(epoch_num_regex.search(cp)[1]), json.dumps(ge.evaluate_metrics(r)))
cp_path = os.path.join(os.getcwd(), 'v4/logs/three/version_0/checkpoints/')
cps = [os.path.join(cp_path, f) for f in os.listdir(cp_path)]
cps = sorted(cps, key= lambda s: int(epoch_num_regex.search(s)[1]))

v = v4
for cp in tqdm(cps):
    qe = ge.QueryEstimater(cp, dataset=tpcd, encoder=v.encoder, model_cls=v.model.GereltRegressor)
    r = qe.evaluate(df=tpcd_sample)
    print(int(epoch_num_regex.search(cp)[1]), json.dumps(ge.evaluate_metrics(r)))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14656392514546313, 'fraction_numerical': 0.049221575719452744, 'mean_word_length': 5.905537459283388, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 16, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '593406', 'license': 'MIT', 'n_tokens_mistral': 2825, 'n_tokens_neox': 2591, 'n_words': 289, 'path': 'dqo/estimator/gerelt/evaluate_three.ipynb', 'repo_name': 'danield137/deep_query_optimzation', 'size': '6359'}