Exporting Subscription Data

MATRIXX provides a Java utility, data_export.jar, which uses checkpoint files to transform the database contents and MATRIXX Event Files (MEFs) to comma-separated value (CSV) files. The data_export.jar file also creates SQL files for creating RDBMS tables and loading the data from the CSV files.

Creating RDBMS tables is a multi-step process that includes:
  • If necessary, running the create_checkpoint.py script on a publishing pod to create a new checkpoint of the in-memory database.
  • Running the Java utility data_export.jar to create CSV files, which can be used to load data into a relational database.
  • Running SQL scripts to create RDBMS tables and load the data.

The data_export.jar Utility

The data_export.jar utility takes MEFs and static checkpoints of the following databases as input:

  • Pricing
  • Balance set
  • Subscriber

The data_export.jar utility uses a configuration file to determine which MTX objects and fields to export. The utility also uses the configuration file to control the transformation of objects in the checkpoint and MEF files to SQL tables in the exported CSV files. The configuration file is a Java properties file with a YAML file extension and its entries are stated in "name:value" format. Sample YAML files are provided under the /opt/mtx/data directory, or you can create a custom configuration file.

If you have custom MATRIXX Data Containers (MDC) that extend MATRIXX MDCs, you can use the add_custom_field_to_export.py script to create a custom YAML configuration file that includes your custom fields. For more information, see the discussion about adding custom fields to export.

Note: Array and list elements can be exported if the elements in the array or list are of simple types (not structs). The array or list is exported as a string with a comma as a delimiter between the elements.

Output Files

Based on the configurations in the YAML file, the data_export.jar utility will generate the following files:

  • The extracted database content in one or more CSV files.
  • The create_tables.sql script, which creates new SQL tables for the exported data.
    Note: The properties files and the CREATE TABLE statements in the create_tables.sql script provide descriptions of the fields in the output CSV files.
  • The load_tables.sql script, which loads the exported CSV data into the SQL tables.
Note: The in-progress output CSV files have a temporary file extension,.tmp. The file extension is changed to .csv when the output file is complete.

Database Export and SQL Loading Process

The following steps describe the process for exporting subscription data or event data to CSV files and loading that data into new SQL tables:
  1. Select or create a configuration YAML file to use with the data_export.jar utility based on the type of MTX objects you want to export (subscriber, event, or both).
    Tip: If you have custom MDCs that extend MATRIXX MDCs, you can use the add_custom_field_to_export.py script to create a new YAML configuration file that includes your custom fields to use as input for the data_export.jar utility.
  2. Use the configuration YAML file to run the data_export.jar utility and export your data.
  3. Execute the create_tables.sql script generated by the data_export.jar utility to create new SQL tables.
  4. Execute the load_tables.sql script generated by the data_export.jar utility to load the exported CSV data into the new SQL tables.