OmniOpt2-Logo ScaDS.ai-Logo

🗃️ Using the SQLite3 store

SQLite3 Usage in OmniOpt2

SQLite3 is an optional but always-enabled format for saving OmniOpt2 optimization results. By default, OmniOpt2 automatically writes all trial data and results into a local SQLite3 database file as an archival measure. Users who don't need it can simply ignore it without any impact on their workflow.

Key Points:

Benefits of Using SQLite3 for OmniOpt2 Results

Using SQLite3 to save OmniOpt2 optimization results unlocks powerful possibilities for data analysis and aggregation:

1. Structured Data Storage

2. Efficient Querying

3. Aggregation and Summarization

4. Cross-Experiment Comparison

5. Portability and Integration

6. Post-Processing and Reporting

In summary, SQLite3 provides a lightweight, yet powerful database option for archiving, analyzing, and aggregating OmniOpt2 hyperparameter optimization results, making it easier to extract insights and improve models.

How to access

Go to your run folder, and run sqlite3 database.db . This allows you to use SQLite3 to view the database.

Other DB-systems than SQLite3

You can add any other system that sqlalchemy supports by creating it's connect-string and passing it to OmniOpt2 with --db_url .
These include, but are not limited to postgresql://user:password@host:port/database , mysql+pymysql://user:password@host:port/database ... and so on.
It may be possible that, for certain databases, you need to install further plugins to the venv.
Using this option disables the saving into the SQLite3 database.

Tables and Meaning of Columns

experiment_v2
ColumnDescription
descriptionTextual description of the experiment
experiment_typeType or category of the experiment
idUnique experiment identifier
is_testFlag indicating if this is a test experiment
nameName of the experiment
propertiesJSON properties/configuration of the experiment
status_quo_nameName of the baseline or control setup
status_quo_parametersParameters of the baseline/control setup
time_createdTimestamp when the experiment was created
default_trial_typeDefault trial type associated with this experiment
default_data_typeDefault data type for trials in the experiment
auxiliary_experiments_by_purposeJSON mapping for auxiliary experiments grouped by purpose

generation_strategy
ColumnDescription
idUnique identifier for the generation strategy
nameName or label of the generation strategy
stepsConfiguration or steps defining the strategy
curr_indexCurrent index or step in the strategy sequence
experiment_idAssociated experiment identifier
nodesDetails of nodes used in the strategy (e.g. parallel generation nodes)
curr_node_nameName of the current node executing

trial_v2
ColumnDescription
abandoned_reasonReason why the trial was abandoned (if any)
failed_reasonReason why the trial failed (if any)
deployed_nameName of the deployed model/configuration
experiment_idIdentifier of the experiment this trial belongs to
idUnique trial identifier
indexIndex or sequence number of the trial
is_batchedFlag indicating if the trial is batched
lifecycle_stageCurrent lifecycle stage (e.g. running, completed)
num_arms_createdNumber of arms (parameter configurations) created for this trial
ttl_secondsTime to live for the trial, in seconds
run_metadataJSON metadata about the run
stop_metadataJSON metadata about the stop event
statusCurrent status (e.g. completed, running, failed)
status_quo_nameName of baseline status quo configuration
time_completedTimestamp when the trial completed
time_createdTimestamp when the trial was created
time_stagedTimestamp when the trial was staged/prepared
time_run_startedTimestamp when the trial run started
trial_typeType of trial (e.g. default, special)
generation_step_indexIndex of the generation step used
propertiesAdditional JSON properties for the trial

analysis_card
ColumnDescription
idUnique identifier of the analysis card
nameName of the analysis card
titleTitle text for the card
subtitleSubtitle text for the card
levelLevel or hierarchy depth of the card
dataframe_jsonJSON representing data in tabular format for analysis
blobBinary large object, e.g. charts or serialized data
blob_annotationAnnotation or metadata for the blob
time_createdTimestamp when the analysis card was created
experiment_idID of the experiment associated
attributesJSON of additional attributes or metadata
categoryCategory or grouping of the analysis card

abandoned_arm_v2
ColumnDescription
abandoned_reasonReason why the arm (parameter configuration) was abandoned
idUnique identifier of the abandoned arm
nameName or label of the abandoned arm
time_abandonedTimestamp when it was abandoned
trial_idIdentifier of the trial this arm belonged to

generator_run_v2
ColumnDescription
best_arm_nameName of the best arm (parameter set) from this generation run
best_arm_parametersJSON of parameters of the best arm
best_arm_predictionsJSON of predictions for the best arm
generator_run_typeType of the generator run (e.g. optimization algorithm)
idUnique identifier of the generator run
indexIndex of this generator run in the trial
model_predictionsJSON predictions produced by the model
time_createdTimestamp when the generator run was created
trial_idTrial ID associated with this generator run
weightWeight or importance assigned to this generator run
fit_timeTime spent fitting the model (seconds)
gen_timeTime spent generating arms (seconds)
model_keyKey identifying the model used
model_kwargsJSON of model-specific parameters
bridge_kwargsJSON of parameters related to bridging strategies
gen_metadataJSON metadata about the generation process

arm_v2
ColumnDescription
experiment_idID of the experiment this arm belongs to
idUnique identifier of the arm
nameName of the arm
parametersJSON of parameter values for this arm
trial_idID of the trial associated
time_createdTimestamp when the arm was created
time_removedTimestamp when the arm was removed (if any)
propertiesAdditional JSON properties for the arm

generation_step
ColumnDescription
idUnique identifier of the generation step
generation_strategy_idID of the generation strategy
indexIndex/order of the generation step in the strategy
nameName or label of the generation step
typeType of generation step (e.g. initial, iterative)
model_keyModel key used for this step
model_kwargsJSON of model parameters
bridge_kwargsJSON of bridging parameters
use_updateFlag if this step uses update mechanisms
minimum_trials_observedMinimum number of trials before this step can run
is_dedicatedFlag if step is dedicated to specific tasks
should_saveFlag indicating if results from this step should be saved
is_activeFlag indicating if this step is currently active

data_row
ColumnDescription
idUnique identifier of the data row
dataJSON or serialized data content
trial_idAssociated trial identifier
arm_nameName of the arm corresponding to this data row
time_createdTimestamp when the data row was created

Example data

This command was run:
./omniopt \
	--live_share \
	--send_anonymized_usage_stats \
	--partition alpha \
	--experiment_name ExampleDatabase \
	--mem_gb=4 \
	--time 60 \
	--worker_timeout=5 \
	--max_eval 4 \
	--num_parallel_jobs 2 \
	--gpus 0 \
	--run_program Li8udGVzdHMvb3B0aW1pemF0aW9uX2V4YW1wbGUgIC0taW50X3BhcmFtPSclKGludF9wYXJhbSknIC0tZmxvYXRfcGFyYW09JyUoZmxvYXRfcGFyYW0pJyAtLWNob2ljZV9wYXJhbT0nJShjaG9pY2VfcGFyYW0pJyAtLWludF9wYXJhbV90d289JyUoaW50X3BhcmFtX3R3byknIC0tbnJfcmVzdWx0cz0x \
	--parameter int_param range -100 10 int \
	--parameter float_param range -100 10 float \
	--parameter choice_param choice 1,2,4,8,16,hallo \
	--parameter int_param_two range -100 10 int \
	--num_random_steps 2 \
	--model BOTORCH_MODULAR \
	--auto_exclude_defective_hosts \
	--generate_all_jobs_at_once \
	--follow \
	--experiment_constraints MjAqaW50X3BhcmFtID49IDEwMAo= \
	--show_generate_time_table

And resulted in this database:

abandoned_arm_v2

No rows

analysis_card

No rows

arm_v2

generator_run_ididnameparametersweight
210_0{"int_param": 8, "float_param": -72.08981832489371, "int_param_two": -34, "choice_param": "8"}1
421_0{"int_param": 5, "float_param": -12.177476165816188, "int_param_two": -24, "choice_param": "16"}1
632_0{"int_param": 8, "float_param": -88.20812338130575, "int_param_two": -98, "choice_param": "8"}1
843_0{"int_param": 10, "float_param": -100.0, "int_param_two": -100, "choice_param": "8"}1

data_v2

iddata_jsondescriptionexperiment_idtime_createdtrial_indexgeneration_strategy_idstructure_metadata_json
1{"trial_index":{"0":0},"arm_name":{"0":"0_0"},"metric_name":{"0":"RESULT"},"mean":{"0":-74706.1208936512},"sem":{"0":null}}117502484527090{"df": {"__type": "DataFrame", "value": "{\"trial_index\":{\"0\":0},\"arm_name\":{\"0\":\"0_0\"},\"metric_name\":{\"0\":\"RESULT\"},\"mean\":{\"0\":-74706.1208936512},\"sem\":{\"0\":null}}"}, "description": null}
2{"trial_index":{"0":1},"arm_name":{"0":"1_0"},"metric_name":{"0":"RESULT"},"mean":{"0":-12658.7768742718},"sem":{"0":null}}117502484753641{"df": {"__type": "DataFrame", "value": "{\"trial_index\":{\"0\":1},\"arm_name\":{\"0\":\"1_0\"},\"metric_name\":{\"0\":\"RESULT\"},\"mean\":{\"0\":-12658.7768742718},\"sem\":{\"0\":null}}"}, "description": null}
3{"trial_index":{"0":2},"arm_name":{"0":"2_0"},"metric_name":{"0":"RESULT"},"mean":{"0":-192344.062642647},"sem":{"0":null}}117502485084272{"df": {"__type": "DataFrame", "value": "{\"trial_index\":{\"0\":2},\"arm_name\":{\"0\":\"2_0\"},\"metric_name\":{\"0\":\"RESULT\"},\"mean\":{\"0\":-192344.062642647},\"sem\":{\"0\":null}}"}, "description": null}
4{"trial_index":{"0":3},"arm_name":{"0":"3_0"},"metric_name":{"0":"RESULT"},"mean":{"0":-220010.92},"sem":{"0":null}}117502485517073{"df": {"__type": "DataFrame", "value": "{\"trial_index\":{\"0\":3},\"arm_name\":{\"0\":\"3_0\"},\"metric_name\":{\"0\":\"RESULT\"},\"mean\":{\"0\":-220010.92},\"sem\":{\"0\":null}}"}, "description": null}

experiment_v2

descriptionexperiment_typeidis_testnamepropertiesstatus_quo_namestatus_quo_parameterstime_createddefault_trial_typedefault_data_typeauxiliary_experiments_by_purpose
10ExampleDatabase{"immutable_search_space_and_opt_config": true}17502484291{}

generation_strategy

idnamestepscurr_indexexperiment_idnodescurr_node_name
1SOBOL for 2 steps+BOTORCH_MODULAR for 2 steps[]-11[{"__type": "GenerationNode", "node_name": "SOBOL", "model_specs": [{"__type": "GeneratorSpec", "model_enum": {"__type": "Generators", "name": "SOBOL"}, "model_kwargs": {}, "model_gen_kwargs": {"model_gen_options": {"optimizer_kwargs": {"num_restarts": 20, "raw_samples": 1024}}, "fallback_to_sample_polytope": true, "normalize_y": true, "transform_inputs": true, "optimizer_kwargs": {"sequential": false}, "torch_device": "cpu", "random_seed": null, "check_duplicates": true, "deduplicate_strict": true, "enforce_num_arms": true, "warm_start_refitting": true, "jit_compile": true, "refit_on_cv": false, "fit_abandoned": false, "fit_out_of_design": false}}], "best_model_selector": null, "should_deduplicate": false, "transition_criteria": [{"threshold": 2, "only_in_statuses": null, "not_in_statuses": null, "transition_to": "BOTORCH_MODULAR", "block_transition_if_unmet": true, "block_gen_if_met": false, "use_all_trials_in_exp": false, "continue_trial_generation": false, "count_only_trials_with_data": true, "__type": "MaxTrials"}], "model_spec_to_gen_from": {"__type": "GeneratorSpec", "model_enum": {"__type": "Generators", "name": "SOBOL"}, "model_kwargs": {}, "model_gen_kwargs": {"model_gen_options": {"optimizer_kwargs": {"num_restarts": 20, "raw_samples": 1024}}, "fallback_to_sample_polytope": true, "normalize_y": true, "transform_inputs": true, "optimizer_kwargs": {"sequential": false}, "torch_device": "cpu", "random_seed": null, "check_duplicates": true, "deduplicate_strict": true, "enforce_num_arms": true, "warm_start_refitting": true, "jit_compile": true, "refit_on_cv": false, "fit_abandoned": false, "fit_out_of_design": false}}, "previous_node_name": null, "trial_type": null, "input_constructors": {}}, {"__type": "GenerationNode", "node_name": "BOTORCH_MODULAR", "model_specs": [{"__type": "GeneratorSpec", "model_enum": {"__type": "Generators", "name": "BOTORCH_MODULAR"}, "model_kwargs": {}, "model_gen_kwargs": {"model_gen_options": {"optimizer_kwargs": {"num_restarts": 20, "raw_samples": 1024}}, "fallback_to_sample_polytope": true, "normalize_y": true, "transform_inputs": true, "optimizer_kwargs": {"sequential": false}, "torch_device": "cpu", "random_seed": null, "check_duplicates": true, "deduplicate_strict": true, "enforce_num_arms": true, "warm_start_refitting": true, "jit_compile": true, "refit_on_cv": false, "fit_abandoned": false, "fit_out_of_design": false}}], "best_model_selector": null, "should_deduplicate": false, "transition_criteria": [{"threshold": 2, "only_in_statuses": null, "not_in_statuses": null, "transition_to": "BOTORCH_MODULAR", "block_transition_if_unmet": true, "block_gen_if_met": false, "use_all_trials_in_exp": false, "continue_trial_generation": false, "count_only_trials_with_data": true, "__type": "MaxTrials"}], "model_spec_to_gen_from": {"__type": "GeneratorSpec", "model_enum": {"__type": "Generators", "name": "BOTORCH_MODULAR"}, "model_kwargs": {}, "model_gen_kwargs": {"model_gen_options": {"optimizer_kwargs": {"num_restarts": 20, "raw_samples": 1024}}, "fallback_to_sample_polytope": true, "normalize_y": true, "transform_inputs": true, "optimizer_kwargs": {"sequential": false}, "torch_device": "cpu", "random_seed": null, "check_duplicates": true, "deduplicate_strict": true, "enforce_num_arms": true, "warm_start_refitting": true, "jit_compile": true, "refit_on_cv": false, "fit_abandoned": false, "fit_out_of_design": false}}, "previous_node_name": "BOTORCH_MODULAR", "trial_type": null, "input_constructors": {}}]BOTORCH_MODULAR

generator_run_v2

best_arm_namebest_arm_parametersbest_arm_predictionsgenerator_run_typeidindexmodel_predictionstime_createdtrial_idweightfit_timegen_timemodel_keymodel_kwargsbridge_kwargsgen_metadatamodel_state_after_gengeneration_strategy_idgeneration_step_indexcandidate_metadata_by_arm_signaturegeneration_node_name
1017502484381SOBOL
217502484360.00421076006023210.13509030098794Sobol{"deduplicate": true, "seed": null, "init_position": 0, "scramble": true, "generated_points": null, "fallback_to_sample_polytope": false}{"transforms": [{"__type": "Type[Transform]", "transform_type": "RemoveFixed"}, {"__type": "Type[Transform]", "transform_type": "OrderedChoiceToIntegerRange"}, {"__type": "Type[Transform]", "transform_type": "OneHot"}, {"__type": "Type[Transform]", "transform_type": "IntToFloat"}, {"__type": "Type[Transform]", "transform_type": "Log"}, {"__type": "Type[Transform]", "transform_type": "Logit"}, {"__type": "Type[Transform]", "transform_type": "UnitX"}], "transform_configs": null, "optimization_config": null, "fit_tracking_metrics": true, "fit_on_init": true, "data_loader_config": null, "fit_out_of_design": null, "fit_abandoned": null}{"model_fit_quality": null, "model_std_quality": null, "model_fit_generalization": null, "model_std_generalization": null}{"seed": 47048, "init_position": 8}1SOBOL
3017502484602SOBOL
417502484590.00941983307711780.11721514101373Sobol{"deduplicate": true, "seed": 47048, "init_position": 8, "scramble": true, "generated_points": null, "fallback_to_sample_polytope": false}{"transforms": [{"__type": "Type[Transform]", "transform_type": "RemoveFixed"}, {"__type": "Type[Transform]", "transform_type": "OrderedChoiceToIntegerRange"}, {"__type": "Type[Transform]", "transform_type": "OneHot"}, {"__type": "Type[Transform]", "transform_type": "IntToFloat"}, {"__type": "Type[Transform]", "transform_type": "Log"}, {"__type": "Type[Transform]", "transform_type": "Logit"}, {"__type": "Type[Transform]", "transform_type": "UnitX"}], "transform_configs": null, "optimization_config": null, "fit_tracking_metrics": true, "fit_on_init": true, "data_loader_config": null, "fit_out_of_design": null, "fit_abandoned": null}{"model_fit_quality": null, "model_std_quality": null, "model_fit_generalization": null, "model_std_generalization": null}{"seed": 47048, "init_position": 25}1SOBOL
5017502484933BOTORCH_MODULAR
0_0{"int_param": 8, "float_param": -72.08981832489371, "int_param_two": -34, "choice_param": "8"}[{"RESULT": -74494.25151311475}, {"RESULT": {"RESULT": 12842340.085245028}}]6[{"RESULT": [-62901.523349967945]}, {"RESULT": {"RESULT": [1191651556.7625513]}}]17502484910.429976123967218.9819894910324BoTorch{"surrogate_spec": null, "surrogate_specs": null, "surrogate": null, "acquisition_class": null, "acquisition_options": null, "botorch_acqf_class": null, "refit_on_cv": false, "warm_start_refit": true}{"transforms": [{"__type": "Type[Transform]", "transform_type": "RemoveFixed"}, {"__type": "Type[Transform]", "transform_type": "OrderedChoiceToIntegerRange"}, {"__type": "Type[Transform]", "transform_type": "OneHot"}, {"__type": "Type[Transform]", "transform_type": "LogIntToFloat"}, {"__type": "Type[Transform]", "transform_type": "Log"}, {"__type": "Type[Transform]", "transform_type": "Logit"}, {"__type": "Type[Transform]", "transform_type": "IVW"}, {"__type": "Type[Transform]", "transform_type": "Derelativize"}, {"__type": "Type[Transform]", "transform_type": "BilogY"}, {"__type": "Type[Transform]", "transform_type": "StandardizeY"}], "transform_configs": null, "optimization_config": null, "expand_model_space": true, "fit_tracking_metrics": true, "fit_on_init": true, "default_model_gen_options": null, "torch_device": null, "data_loader_config": null, "fit_out_of_design": null, "fit_abandoned": null, "fit_only_completed_map_metrics": null}{"expected_acquisition_value": -1.6128382490077362, "metric_to_model_config_name": {"RESULT": "default"}, "model_fit_quality": -1.9825202713510057, "model_std_quality": 2.6328766687688195e-06, "model_fit_generalization": -0.046714185559622035, "model_std_generalization": 1.381924629723967}{}1BOTORCH_MODULAR
7017502485364BOTORCH_MODULAR
2_0{"int_param": 8, "float_param": -88.20812338130575, "int_param_two": -98, "choice_param": "8"}[{"RESULT": -191602.3426462933}, {"RESULT": {"RESULT": 55566319.81362155}}]8[{"RESULT": [-191250.6763321454]}, {"RESULT": {"RESULT": [306116211.6571691]}}]17502485340.5731158400303718.510107692971BoTorch{"surrogate_spec": null, "surrogate_specs": null, "surrogate": null, "acquisition_class": null, "acquisition_options": null, "botorch_acqf_class": null, "refit_on_cv": false, "warm_start_refit": true}{"transforms": [{"__type": "Type[Transform]", "transform_type": "RemoveFixed"}, {"__type": "Type[Transform]", "transform_type": "OrderedChoiceToIntegerRange"}, {"__type": "Type[Transform]", "transform_type": "OneHot"}, {"__type": "Type[Transform]", "transform_type": "LogIntToFloat"}, {"__type": "Type[Transform]", "transform_type": "Log"}, {"__type": "Type[Transform]", "transform_type": "Logit"}, {"__type": "Type[Transform]", "transform_type": "IVW"}, {"__type": "Type[Transform]", "transform_type": "Derelativize"}, {"__type": "Type[Transform]", "transform_type": "BilogY"}, {"__type": "Type[Transform]", "transform_type": "StandardizeY"}], "transform_configs": null, "optimization_config": null, "expand_model_space": true, "fit_tracking_metrics": true, "fit_on_init": true, "default_model_gen_options": null, "torch_device": null, "data_loader_config": null, "fit_out_of_design": null, "fit_abandoned": null, "fit_only_completed_map_metrics": null}{"expected_acquisition_value": -2.697145778036146, "metric_to_model_config_name": {"RESULT": "default"}, "model_fit_quality": -1.5654917725281385, "model_std_quality": 1.0960946446370698e-06, "model_fit_generalization": -0.06274517616608666, "model_std_generalization": 1.1614062085206702}{}1BOTORCH_MODULAR

metric_v2

experiment_idgenerator_run_ididlower_is_betterintentmetric_typenamepropertiesminimizeopboundrelativetrial_typecanonical_namescalarized_objective_idscalarized_objective_weightscalarized_outcome_constraint_idscalarized_outcome_constraint_weight
111objective0RESULT{"name": "RESULT", "lower_is_better": true, "properties": {}}1

parameter_constraint_v2

boundconstraint_dictexperiment_ididgenerator_run_idtype
-100{"int_param": -20.0}110

parameter_v2

domain_typeexperiment_ididgenerator_run_idnameparameter_typeis_fidelitytarget_valuedigitslog_scalelowerupperchoice_valuesis_orderedis_taskdependentsfixed_value
111int_param100-10010
112float_param200-10010
213choice_param30["1", "2", "4", "8", "16", "hallo"]00
114int_param_two100-10010

runner

No rows

trial_v2

abandoned_reasonfailed_reasondeployed_nameexperiment_ididindexis_batchedlifecycle_stagenum_arms_createdttl_secondsrun_metadatastop_metadatastatusstatus_quo_nametime_completedtime_createdtime_stagedtime_run_startedtrial_typegeneration_step_indexproperties
11001{}{}3175024845217502484381750248438{}
12101{}{}3175024847517502484601750248460{}
13201{}{}3175024850817502484931750248493{}
14301{}{}3175024855217502485361750248536{}