<jupyter_start><jupyter_text>For Flights dataset with the following query tempalte:SELECT unique_carrier, AF(dep_delay) FROM flightsWHERE distance BETWEEN 1000 AND 1200 AND origin_state_abr='LA'  GROUP BY unique_carrier<jupyter_code>from dbestclient.executor.executor import SqlExecutor<jupyter_output><empty_output><jupyter_text>Parameter Initialization<jupyter_code>sqlExecutor = SqlExecutor()
sqlExecutor.execute("set n_mdn_layer_node_reg=10")          # 
sqlExecutor.execute("set n_mdn_layer_node_density=15")      # 
sqlExecutor.execute("set n_jobs=1")                         # 
sqlExecutor.execute("set n_hidden_layer=1")                 # 
sqlExecutor.execute("set n_epoch=20")                       # 
sqlExecutor.execute("set n_gaussians_reg=8")                # 
sqlExecutor.execute("set n_gaussians_density=10")           # 
sqlExecutor.execute("set csv_split_char=','")
sqlExecutor.execute("set table_header=" +
                      "'year_date,unique_carrier,origin,origin_state_abr,dest,dest_state_abr,dep_delay,taxi_out,taxi_in,arr_delay,air_time,distance'")<jupyter_output>Local mode is on, as no slaves are provided.
start loading pre-existing models.
Loaded 4 models. time cost  0.284639 s
OK, n_mdn_layer_node_reg is updated.
OK, n_mdn_layer_node_density is updated.
OK, n_jobs is updated.
OK, n_hidden_layer is updated.
OK, n_epoch is updated.
OK, n_gaussians_reg is updated.
OK, n_gaussians_density is updated.
OK, csv_split_char is updated.
OK, table_header is updated.<jupyter_text>Model Creation<jupyter_code># sqlExecutor.execute("drop table template5")  # drop the model
sqlExecutor.execute(
    "create table template5(dep_delay real, distance real,origin_state_abr categorical) from '/home/quincy/Documents/workspace/data/flights/flight_1m.csv' GROUP BY unique_carrier method uniform size 0.001 ")<jupyter_output>Start creating model template5
The given table is treated as a uniform sample, and it is obtained with sampling rate 0.001
Reading data file...
get frequency info from data....
fit MdnQueryEngineXCategoricalOneModel...
training density...
finish training embedding.
< Epoch 0
< Epoch 1
< Epoch 2
< Epoch 3
< Epoch 4
< Epoch 5
< Epoch 6
< Epoch 7
< Epoch 8
< Epoch 9
< Epoch 10
< Epoch 11
< Epoch 12
< Epoch 13
< Epoch 14
< Epoch 15
< Epoch 16
< Epoch 17
< Epoch 18
< Epoch 19
finish mdn training...
training regression...
finish training embedding.
embedding inference...
start normalizing data...
transform data from MDN training...
finish transforming data from MDN training...
< Epoch 0
< Epoch 1
< Epoch 2
< Epoch 3
< Epoch 4
< Epoch 5
< Epoch 6
< Epoch 7
< Epoch 8
< Epoch 9
< Epoch 10
< Epoch 11
< Epoch 12
< Epoch 13
< Epoch 14
< Epoch 15
< Epoch 16
< Epoch 17
< Epoch 18
< Epoch 19
Finish regression training.
time cost: 430s.
------------------------<jupyter_text>Query Serving<jupyter_code>predictions = sqlExecutor.execute(
            "select unique_carrier, avg(dep_delay)  from template5 where   1000  <=distance<= 1200 and  origin_state_abr='LA'  group by unique_carrier"
        )

# sqlExecutor.execute("drop table template5")<jupyter_output>OK
        0           1
    9E,LA 1206.414211
    AA,LA 1206.447441
    AQ,LA 1206.316881
    AS,LA 1206.448448
    B6,LA 1206.397555
    CO,LA 1206.387241
    DH,LA 1206.572376
    DL,LA 1206.398092
    EV,LA 1206.344478
    F9,LA 1206.571358
    FL,LA 1206.474904
    HA,LA 1206.531414
    HP,LA 1206.337871
    MQ,LA 1206.488969
    NK,LA 1206.678322
    NW,LA 1206.411467
OH (1),LA 1206.414877
    OO,LA 1206.303849
    TW,LA 1206.444148
    TZ,LA 1206.627812
    UA,LA 1206.383681
    US,LA 1206.429333
    VX,LA 1206.332781
    WN,LA 1206.444382
    XE,LA 1206.422698
    YV,LA 1206.444927
Time cost: 0.0306s.
------------------------<jupyter_text>HIVE querySELECT unique_carrier, COUNT(dep_delay),SUM(dep_delay), AVG(dep_delay)  FROM flights WHERE distance BETWEEN 1000 AND 1200  AND origin_state_abr='LA'   GROUP BY unique_carrier<jupyter_code>Time Cost: 498s
hive> SELECT unique_carrier, COUNT(dep_delay),SUM(dep_delay), AVG(dep_delay)  FROM flights WHERE distance BETWEEN 1000 AND 1200  AND origin_state_abr='LA'   GROUP BY unique_carrier;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hduser_20210310231215_fd3ca82b-edd9-4606-bddf-6e91da2bea6b
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 224
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1586257888433_0314, Tracking URL = http://master:8088/proxy/application_1586257888433_0314/
Kill Command = /opt/hadoop292/bin/hadoop job  -kill job_1586257888433_0314
Hadoop job information for Stage-1: number of mappers: 214; number of reducers: 224
2021-03-10 23:12:41,836 Stage-1 map = 0%,  reduce = 0%
2021-03-10 23:20:33,745 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 6466.4 sec
MapReduce Total cumulative CPU time: 0 days 1 hours 47 minutes 46 seconds 400 msec
Ended Job = job_1586257888433_0314
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 214  Reduce: 224   Cumulative CPU: 6466.4 sec   HDFS Read: 57224216019 HDFS Write: 23598 SUCCESS
Total MapReduce CPU Time Spent: 0 days 1 hours 47 minutes 46 seconds 400 msec
OK
DH                                                                                                  	3971	4788137.399999998	1205.7762276504652
DL                                                                                                  	95975	1.1575895680000003E8	1206.1365647303987
EV                                                                                                  	34035	4.1062881099999994E7	1206.4898222418096
F9                                                                                                  	5916	7132196.000000003	1205.5774171737664
TW                                                                                                  	10982	1.32409268E7	1205.6935712984885
MQ                                                                                                  	39003	4.703943819999997E7	1206.0466682050092
FL                                                                                                  	14608	1.7622019300000004E7	1206.326622398686
TZ                                                                                                  	1237	1490529.2999999993	1204.9549717057391
UA                                                                                                  	72344	8.725239720000003E7	1206.076484573704
NK                                                                                                  	1478	1782778.2999999993	1206.2099458728007
US                                                                                                  	63588	7.669369760000001E7	1206.1033150908977
NW                                                                                                  	41632	5.020781880000002E7	1205.9910357417375
9E                                                                                                  	7720	9318013.800000008	1206.9966062176177
HA                                                                                                  	5108	6161658.099999998	1206.2760571652307
AA                                                                                                  	84448	1.0186515010000005E8	1206.247040782494
OO                                                                                                  	45830	5.527949410000002E7	1206.185775692778
VX                                                                                                  	1763	2126155.8000000026	1205.9874078275682
HP                                                                                                  	13056	1.5747752600000003E7	1206.1697763480395
AQ                                                                                                  	815	984427.1000000001	1207.8860122699389
AS                                                                                                  	19721	2.3785714200000014E7	1206.110957862178
B6                                                                                                  	15646	1.8878680100000016E7	1206.613837402532
WN                                                                                                  	133469	1.6097666200000003E8	1206.0977605286623
XE                                                                                                  	20138	2.4297131199999988E7	1206.531492700367
CO                                                                                                  	33622	4.0555577299999975E7	1206.2214413181837
OH (1)                                                                                              	10144	1.2236402000000004E7	1206.2699132492116
YV                                                                                                  	9692	1.1692630299999997E7	1206.4207903425502
Time taken: 498.802 seconds, Fetched: 26 row(s)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.060799319727891155, 'fraction_numerical': 0.17261904761904762, 'mean_word_length': 1.4483476450689565, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 14, 'word_list_counts': {'cursed_substrings.json': 104, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '451679', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 3677, 'n_tokens_neox': 2619, 'n_words': 685, 'path': 'demo/flights_query_template_5.ipynb', 'repo_name': 'qingzma/DBEstClient', 'size': '9408'}