<jupyter_start><jupyter_text>merge<jupyter_code>ds_aug = QueriesDataset("imdb:exec_time_aug")
df_aug = ds_aug.load()
df_merge = df.append(df_aug)
df_merge = df_merge.query('runtime > 0.1')
df_merge.runtime.describe()
df_merge = df_merge.drop_duplicates()
ds_aug.df = df_merge
ds_aug.df = ds_aug.groom()
df_merge['bucket'] = df_merge.runtime.apply(np.log2).apply(np.round).apply(lambda x: max(0, x)).apply(lambda x: min(x,8)).astype(int)
plt.hist(df_merge['bucket'])
df_merge.groupby('bucket').count()
ds_aug.df = ds_aug.df.query('nodes < 125')
import os
ds_aug.df.to_csv(os.path.join(ds_aug.input_path, 'aug_01.csv'), header=False, index=False, columns=['query', 'runtime'])
ds_aug.input_path<jupyter_output><empty_output><jupyter_text>----<jupyter_code>df = df.query('runtime > 0.1 ').query('runtime < 500')
df['bucket'] = df.runtime.apply(np.log2).apply(np.round).apply(lambda x: max(0, x)).apply(lambda x: min(x,8)).astype(int)
plt.hist(df['bucket'])
df.groupby('bucket').count()
ds.df = df
df = ds.groom()
len(ds.df), len(df)
ds.input_path
ds.df = df
import os
ds.df.to_csv(os.path.join(ds.input_path, 'clean_00.csv'), header=False, index=False, columns=['query', 'runtime'])
df.groupby('nodes').count()
plt.hist(ds.df['nodes'])<jupyter_output><empty_output><jupyter_text>___ augment---<jupyter_code>mean_bucket = int(ds.df[ds.df['bucket'] > 0].groupby('bucket').count().mean().runtime)
sample_size = mean_bucket
sample_size
sample = df[df['bucket'] > 4]
len(sample)
buckets = sample.runtime.apply(np.log2).apply(lambda x: min(x, 8)).apply(lambda x: max(0, x))
plt.hist(buckets)
import os
batch_size = 300
_df = sample
for i in range(0,len(_df), batch_size):
    aug_df = ds.augment(_df[i:i+batch_size])
    f_name = os.path.join(ds.input_path, f'aug_{batch_size}_{i}:{i + len(aug_df)}')
    print(i, len(aug_df), f_name)
    aug_df.to_csv(f_name, header=False, index=False, columns=['query', 'runtime'])
len(aug_df)
ds.df.to_csv(os.path.join(ds.input_path, 'clean.csv'), header=False, index=False, columns=['query', 'runtime'])
buckets = aug_df.runtime.apply(np.log2).apply(lambda x: min(x, 8)).apply(lambda x: max(0, x))
plt.hist(buckets)

ds.save()
df['partition'] = df.runtime.apply(np.log2).apply(lambda x: min(x, 8)).apply(lambda x: max(0, x))
slow_df = df[df['partition'] > 4]
len(slow_df)
import os
os.getcwd()
with open('/Users/danieldubovski/projects/deep_query_optimization/dqo/localhost_imdb.qcp', 'w+') as dcp_file:
    for idx, row in slow_df.iterrows():
        dcp_file.write(f"{row['query']}\n")
    
from dqo.relational import SQLParser
from tqdm import tqdm
queries = []

for idx, row in tqdm(ds.df.iterrows(), total=ds.df.shape[0]):
    queries.append(SQLParser.to_query(row['query']))
db
from collections import defaultdict, Counter
tables = defaultdict(int)
for query in queries:
    c = Counter([r.name for r in query._relations])
    for k, v in c.items():
        tables[k] += v
    
tables_df = pd.DataFrame(tables.items(), columns=['table', 'count'])
tables_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19000331016219796, 'fraction_numerical': 0.012247600132406488, 'mean_word_length': 6.093896713615023, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 18, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '460913', 'license': 'MIT', 'n_tokens_mistral': 1230, 'n_tokens_neox': 1195, 'n_words': 202, 'path': 'dqo/datasets/augment.ipynb', 'repo_name': 'danield137/deep_query_optimzation', 'size': '3021'}