4. Using METdbLoad

The METdbLoad module provides support for inserting MET output data (as .stat files) into a relational database (mysql, mariadb, or aurora).

4.1. Create Database

Before using the METdbLoad module, the database must exist and have the proper permissions (i.e. grant privileges to insert, delete, update, and index).

Data must be loaded into a database which has the prefix 'mv_', (e.g. mv_met_data). This database must be structured with the METviewer mv_mysql.sql schema:

cd [install]/metviewer
mysql -u[db_username] -p[db_password] -e'create database [db_name];'
mysql -u[db_username] -p[db_password] [db_name] < sql/mv_mysql.sql

Replace db_usenamer with the username
Replace db_passwd with the password
Replace db_name with the appropriate database name that begins with mv (e.g. mv_xyz )

Note

Use the mysql command line client (https://dev.mysql.com/doc/refman/en/mysql.html) to run the above instructions.

The mv_mysql.sql file is located in the METdataio/METdbLoad/sql/ directory.

4.2. Create an XML Load Specification Document

The XML load specification document contains information about the MET data, database connection information, and the location of data to be loaded. This is used by the METdbLoad script met_db_load.py to load the MET data. The XML load specification is validated against a schema to check that the file is valid. This validation is necessary to prevent extremely large payloads or recursive payloads that can compromise the loading of data. The elements in the XML load specification file must adhere to the order specified by the XML schema and conform to size and number of element limitations. Click to expand the example XML load specifications and the XML validation schema below.

The XML schema that is used to validate the XML specification file

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"
           xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <!-- Define special types used to impose size limitations on payload-->

    <xs:simpleType name="inserttype">
        <!-- only insert size of 1 -->
        <xs:restriction base="xs:positiveInteger">
            <xs:enumeration value="1"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="truefalsetype">
        <!--Checking for true or false values only (allow for case-insensitive) -->
        <xs:restriction base="xs:string">
            <xs:pattern value="((t|T)(r|R)(u|U)(e|E))|((f|F)(a|A)(l|L)(s|S)(e|E))"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="usernametype">
        <!--Limit the username to 32 characters (mysql maximum) -->
        <xs:restriction base="xs:string">
            <xs:pattern value="[\s\-_a-zA-z0-9]+"/>
            <xs:minLength value="3"/>
            <xs:maxLength value="32"/>
        </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="hostnametype">
        <!--Limit the hostname to 60 characters (mysql maximum) add 7 for port number -->
        <xs:restriction base="xs:string">
            <xs:pattern value="([.\-_a-zA-z0-9]+:\d+)"/>
            <xs:minLength value="3"/>
            <xs:maxLength value="67"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="dbnametype">
        <!--Limit the  database name to max 124 characters based on Stack Overflow  -->
        <xs:restriction base="xs:string">
            <xs:pattern value="[\-_a-zA-z0-9]+"/>
            <xs:minLength value="3"/>
            <xs:maxLength value="124"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="pwdtype">
        <!--Limit the password length  -->
        <xs:restriction base="xs:string">
            <xs:minLength value="3"/>
            <xs:maxLength value="30"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="foldertmpl">
        <!--Checking for folder template name, limit to max 4096 characters -->
        <xs:restriction base="xs:string">
            <!-- Allow _, #, -, / in path -->
            <!-- https://unix.stackexchange.com/questions/32795/what-is-the-maximum-allowed-filename-and-folder-size-with-ecryptfs -->
            <xs:pattern value=".+"/>
            <xs:minLength value="3"/>
            <xs:maxLength value="4096"/>

        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="mgmtsystype">
        <!--Checking for mariadb|aurora|mysql values in management_system-->
        <xs:restriction base="xs:string">
            <xs:enumeration value="mysql"/>
            <xs:enumeration value="aurora"/>
            <xs:enumeration value="mariadb"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="limitedstringtype">
        <!--Limit the database group and description  -->
        <xs:restriction base="xs:string">
            <xs:pattern value="[\s\-._,a-zA-z0-9]+"/>
            <xs:minLength value="1"/>
            <xs:maxLength value="300"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="linetype">
        <xs:restriction base="xs:string">
            <xs:minLength value="2"/>
            <xs:maxLength value="25"/>
            <xs:pattern value="[_a-zA-Z0-9]+"/>
        </xs:restriction>
    </xs:simpleType>


    <!-- COMPLEX TYPES -->
    <xs:element name="load_spec">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="connection">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="mgmtsystype" name="management_system" minOccurs="0"/>
                            <xs:element type="hostnametype" name="host"/>
                            <xs:element type="dbnametype" name="database"/>
                            <xs:element type="usernametype" name="user"/>
                            <xs:element type="pwdtype" name="password"/>
                            <xs:element type="truefalsetype" name="local_infile" minOccurs="0"/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>

                <xs:element name="date_list" minOccurs="0" maxOccurs="5">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="xs:int" name="start"/>
                            <xs:element type="xs:int" name="end"/>
                            <xs:element type="xs:int" name="inc"/>
                            <xs:element type="xs:string" name="format"/>
                        </xs:sequence>
                        <xs:attribute type="xs:string" name="name"/>
                    </xs:complexType>
                </xs:element>

                <!-- Header and other flags -->
                <xs:element type="truefalsetype" name="verbose"/>
                <xs:element type="xs:positiveInteger" name="insert_size"/>
                <xs:element type="truefalsetype" name="stat_header_db_check" minOccurs="0"/>
                <xs:element type="truefalsetype" name="mode_header_db_check" minOccurs="0"/>
                <xs:element type="truefalsetype" name="mtd_header_db_check" minOccurs="0"/>
                <xs:element type="truefalsetype" name="drop_indexes" minOccurs="0"/>
                <xs:element type="truefalsetype" name="apply_indexes" minOccurs="0"/>
                <xs:element type="truefalsetype" name="load_stat" minOccurs="0"/>
                <xs:element type="truefalsetype" name="load_mode" minOccurs="0"/>
                <xs:element type="truefalsetype" name="load_mtd" minOccurs="0"/>
                <xs:element type="truefalsetype" name="load_mpr" minOccurs="0"/>
                <xs:element type="truefalsetype" name="load_orank" minOccurs="0"/>
                <xs:element type="truefalsetype" name="force_dup_file" minOccurs="0"/>

                <xs:element type="limitedstringtype" name="group" minOccurs="0"/>
                <xs:element type="limitedstringtype" name="description" minOccurs="0"/>

                <xs:element name="load_files" minOccurs="0">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="xs:string" name="file" minOccurs="1" maxOccurs="200"/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>

                <xs:element type="foldertmpl" name="folder_tmpl" minOccurs="0"/>

                <xs:element name="load_val" minOccurs="0">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="field" minOccurs="0" maxOccurs="20">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="date_list" minOccurs="0" maxOccurs="5">
                                            <xs:complexType>
                                                <xs:simpleContent>
                                                    <xs:extension base="xs:string">
                                                        <xs:attribute type="xs:string" name="name"/>
                                                    </xs:extension>
                                                </xs:simpleContent>
                                            </xs:complexType>
                                        </xs:element>
                                        <xs:element type="xs:string" name="val" minOccurs="0" maxOccurs="100"/>
                                    </xs:sequence>
                                    <xs:attribute type="xs:string" name="name"/>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>

                <xs:element name="line_type" minOccurs="0" maxOccurs="1">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element type="linetype" name="val" maxOccurs="800" minOccurs="0"/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>

                <xs:element type="xs:string" name="load_xml" minOccurs="0"/>
                
                <xs:element type="xs:string" name="load_note" minOccurs="0"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

4.2.1. Create the XML Specification File

The XML load specification file contains the database connection information, data file location, and other instructions for loading the data into the database.

Create the XML load specification file by copying the example specification file METdataio/METdbLoad/test/Examples/example_load_specification.xml file to the workspace location.

Do not save this XML specification file where it can be read by anyone who should not have access to this information, as this file contains the database password.

cp $METDATAIO_HOME/METdbLoad/test/Examples/example_load_specification.xml  path-to-your-dir/load_specification.xml

$METDATAIO_HOME is the path to the location of the cloned or forked METdataio source code.
Replace the path-to-your-dir with the actual path to where this file will be saved.

Change directory to the location where the example_load_specification.xml file was copied.

cd path-to-your-dir/load_specification.xml

Replace path-to-your-dir with the full path where the XML specification file will be saved.

Make the necessary edits to the required elements and delete any optional, unused/irrelevant elements, based on the explanation below (click to expand). Remember to update the username and password that is applicable to the database.

Note

The order of the elements in the XML load specification file is crucial. DO NOT modify the order of the described elements (below).

4.2.2. Load Data

Now the MET data can be loaded in the database using the met_db_load.py script in the path-to-METdataio-source/METdbLoad/ush directory. The path-to-METdataio-source is the directory where the METdataio source code is saved.

Note

Only data files with the .stat extension will be loaded

cd /path-to-METdataio-source/METdataio/METdbLoad/ush

python met_db_load.py /path-to/load_specification.xml

If logging is desired, redirect output to a log file (via &> command):

python met_db_load.py /path-to/load_specification.xml  &> log/your_logname.log &

Replace path-to-METdataio-source to the path to the location of the cloned or forked METdataio source code.
Replace the path-to with the location where the load_specification.xml XML load specification file was saved.

Refer to the section Create the XML Specification File and expand the drop-down instructions “The following is an explanation of the required and optional elements and any limitations” for details on what is expected in the XML load specification file.

4.3. Troubleshooting

Error:	ERROR: Caught class com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry ‘CT07-NMM-LIN-R2-0-2005-07-15 12:00:00-2005-07-15 12:00:00-0-2005’ for key 2
Solution:	This error is caused by trying to insert a stat_header record into the database when an identical one already exists. If identical stat_header information is present in more than one stat file, set the <stat_header_db_check> value to true. This setting will reduce performance, because the stat_header table is checked for duplicate stat_header each time a row is inserted. However, if a stat_header row already exists in the table with the insert information, then the existing record will be used instead of trying to insert a duplicate.
Error:	ERROR:root: (1049, “Unknown database ‘mv_test’”) in run_sql Error when connecting to database
Solution:	This error is caused when attempting to load data into a database that does not exist. You will need to create the database, set up the appropriate privileges as outlined above, and load the schema using the mv_mysql.sql file.
Error:	/full-path-to/xyz.xml is not valid and may contain a recursive payload or an excessively large payload
Solution:	This error is typically encountered when one of the following conditions exist as a result of failing the XML validation step: the order of the elements in the XML load specification file is inconsistent with the order expected the XML load specification file is missing one or more mandatory elements one or more elements has exceeded size limits specified in the XML schema there are additional XML elements that are not expected Refer to the section Create the XML Load Specification File to verify that the XML load specification file is correct.