Export data from Anylogic database

Question

I have been struggling with model data exportation using Anylogic.

Some context. When running a parameter variation experiment (several iterations and replicates) using parallelization, I haven't found a systematic way to collect the data to analyze them using Python or R. So, I want just to run experiments and save the output in a custom location (folder).

What I have tried so far:

• First, I tried the text file Anylogic features, but they didn't work with a parallel setup (e.g., not all rows were recorded)
• I tried using databases and then exporting the data to Excel. But I had the problem of Excel size limitation (about 1M rows). I am exploring several iterations and replicates so that Excel files wouldn't work.
• I have been trying to connect to the database using R and Python without success. Still, I would need some wrapper to convert the tables into a format I can use with R or Python. That would need to be done within Anylogic, so every experiment data are saved into a given folder.
  • Connect to the database using Python
  • Read the data and convert them to a format I can use independently, let's say a CSV file.
• For now, the only thing that has worked for me is to create as many CSV files as iteration and replicates I have in my experiment, so, if I have 10 iterations with 100 replicates each, I will get 1000 files CSV per dataset I want to collect.
• Another option would be to convert a database (query) into a csv file using Java. Before the experiment starts I clear all the databases. At the end of the experiment, I would like to save the data, and clear the databases, running this code:

try {
    ResultSet rs = selectResultSet("SELECT * from MODEL_PARAMETERS");
    CSVWriter csvWriter = new CSVWriter(new FileWriter("output/model_parameters.csv"), '\t');
    csvWriter.writeAll(rs, true);
    csvWriter.close();
    deleteFrom(model_parameters).execute();
} catch (IOException e)  {
    getEngine().pause();
    traceln("--> An Exception happened during initialization, continue? ...");
    e.printStackTrace();
}

I am getting this error:

The method writeAll(Iterable<String[]>, boolean) in the type CSVWriter is not applicable for the arguments (ResultSet, boolean)

The ResultSet is an interface:

https://help.anylogic.com/index.jsp?topic=%2Fcom.anylogic.help%2Fhtml%2Fjavadoc%2Fcom%2Fanylogic%2Fengine%2Fconnectivity%2FResultSet.html

The selectResultSet is:

selectResultSet
public ResultSet selectResultSet(java.lang.String sql,
                                 java.lang.Object... params)
Get the results as a result set object for the given sql and params
Parameters:
sql - string containing select query
params - array containing select query params
Returns:
ResultSet selected ResultSet

Any ideas on how to deal with this? Thanks!

Answer 1

To write dbase records to a csv, use this setup:

Create a "Text file" element and setup as below:

Then, use the code below when the Experiment ends, i.e. last iteration's last replication is done. Obviously adjust headers for your table structure:

File outputDirectory = new File("outputs");
outputDirectory.mkdir();
String outputFileNameWithExtension = outputDirectory.getPath()+File.separator+"output_operations.csv";

file.setFile(outputFileNameWithExtension, Mode.WRITE_APPEND);

// create header
file.println(        "unique_id"+","+"replication");

// Write data from dbase table                                
List<Tuple> rows = selectFrom(output_operations).list();

for (Tuple row : rows) {
        file.println(        row.get( output_operations.unique_id ) + "," + 
                                        row.get( output_operations.replication ));
}
file.close();

Answer 2

For now, the only thing that has worked for me is to create as many CSV files as iteration and replicates I have in my experiment, so, if I have 10 iterations with 100 replicates each, I will get 1000 files CSV per dataset I want to collect.

To make this work better, write your output data into the model dbase at runtime first, then export into 1 csv at the very end of all runs.

In your output dbase tables, have columns for "iteration" and "replication" numbers to ensure every record is absolutely unique (otherwise, some data is lost in parallel runs as you observed as well).

This works and is very reliable (and fast), it really boils down to ensuring that any dbase record done in any parallel run is written into a unique dbase record. You can use getCurrentReplication() and getCurrentIteration() at runtime to ensure data is writtne correctly...

Answer 3

This is working for me now, just a function with two parameters: query and filename

Here a more general explanation: https://sdaza.com/blog/2020/anylogic-database/