From database to CSV using Anylogic
When running a parameter variation experiment, that is, simulating over several iterations and replicates using parallelization, we usually need to collect a huge amount of data and have them in a format that then we can process using Python or R.
The best way to do this in Anylogic would be using a database and then export, read, or connect to the database to process simulation results (although, see the section update below). We can do that easily in Anylogic. Every time an experiment finishes, we can export the data (from a database) to an Excel file manually.
The issue with Excel files is, on the one hand, they are Excel files, and on the other, they are not suitable for big data (more than 1 million rows). We can create a function to save all the simulation tables into an Excel file as our experiment finishes. However, we will still have the limit-of-rows limitation (check the Anylogic file linked below for a function to create Excel files from a database).
Here I follow a different approach by exporting an Anylogic database table to a CSV file within Java. The general setup using Anylogic PLE 8.6 would be:
- Create the databases you need for your experiment, and be sure you add the columns iteration and replicate.
- Create a function to save the data of your simulation runs (e.g., agent’s status, age, etc.)
- Define a parameter variation experiment.
- Define a variable to specify where to save the data (i.e., path).
- Write code in the experiment Java Actions section so that to save data every time you run an experiment.
- Import functions in the advanced Java section of the experiment.
Databases
I define two tables (data1
and data2
). Each agent saves its data at a given rate. After 5 years, the simulation will finish.
The tables include a column with the experiment iteration and replicate, in addition to the agent’s index, time, and a random value from a normal distribution.
From DB to CSV
The key function to export the data to a CSV file is f_SQLToCSV
. It uses two arguments, a SQL query (query
) and the path to an output file (filename
). For instance, we can write:
You can use any query for your data, giving you a lot of flexibility on what to export to a CSV file. The f_SQLToCSV
method is:
The next step would be to create an experiment and complete the Java actions accordingly. First, we clear our tables.
Then, we collect information on the iteration and replicate of the simulation run:
Finally, at the end of the experiment, we save the data and clear the tables again:
The method f_exportTables
is just a function that goes through each table and export them to a CSV file. v_tables
is string array with the name of the tables I want to export {"data1", "data2"}
:
Remember to import some functions in the imports section
:
From there, we can create additional functions to select the tables to be exported. For more details, download the Anylogic File here.
Update
When running several replicates of my simulation, saving the information in a database didn’t work as expected. My simulation just crashed, and I was not able to keep the data. I finally decided to follow my previous approach: create many CSV files – one per iteration and replicate – and read them using an R or Python function. I know you end up with a lot of CSV files, but at least the simulation doesn’t crash, and you can recover the output of your simulation as it goes.