Insert Data into a Vertica Database

To insert records into a Vertica database, use the Vertica output engine.

The Vertica output engine uses an XSL template to transform the XML produced by Media Server into a format, such as a CSV file, that can be inserted into the database. It then connects to the database using ODBC and inserts the information using a COPY query:

COPY <table>
FROM LOCAL '<local_file>'
DELIMITER '<delimiter>'
ENCLOSED BY '<quote>'
ESCAPE AS '<escape>'

where:

<table> is the Vertica database table to copy data into. This is read from the TrackMapping configuration parameter.

<delimiter>, <quote>, and <escape> are replaced by values from the corresponding configuration parameters.

To insert records into a Vertica database

  1. Create a new configuration to send to Media Server with the process action, or open an existing configuration that you want to modify.

  2. In the [Session] section, add a new task by setting the EngineN parameter. You can give the task any name, for example:

    [Session]
    Engine0=VerticaOutput
  3. Create a new configuration section to contain the task settings, and set the following parameters:

    Type The output engine to use. Set this parameter to vertica.
    TrackMapping The tracks that you want to output, mapped to Vertica database tables.
    OdbcConnectionString The ODBC connection string to use to connect to the database. For information about how to connect to a Vertica database, refer to the Vertica documentation.
    XMLOutputPath The path to the directory to use for temporary files and saved output.
    XSLTemplate

    The XSL template to use to transform records from analysis engines to a format that can be inserted into the database (such as a CSV file).

    OutputInterval (Optional) The interval between inserting batches of records into the database. The default interval is 60 seconds.

    For example:

    [VerticaOutput]
    Type=vertica
    TrackMapping0=FaceRecog.Result : face_recognition
    TrackMapping1=Ocr.Result : ocr
    OdbcConnectionString=DSN=mydb
    XMLOutputPath=./tmp
    XSLTemplate=toCSV.xsl
    OutputInterval=120s

    For more information about the parameters that you can set to configure a Vertica output task, refer to the Media Server Reference.

  4. Save and close the configuration file. Micro Focus recommends that you save your configuration files in the location specified by the ConfigDirectory parameter.