The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Work with XML data structure in Vertica

I create several tables (about 60 tables) to store my XML data structure (exactly IDMEF rfc4765), after inserting about 1 million data in tables (the tables are related to each other), executing queries become very slow.
For example a query with 6 joins (because of XML structure and related tables) executed in 13 seconds. (I have some queries with more than 6 joins now)
Would you have a suggestion about storing this type of data in Vertica?
(I use Vertica Analytic Database v9.2.0-0)

Answers

  • I'm confused here. Vertica isn't storing XML, and last I checked we don't even have a native parser for XML. That's not to say you can't load that stuff, you just have to parse it out somehow. I've done that myself before, and I assume you're doing something similar.

    Regardless of the source of your data, normalized tables aren't going to be very good for querying due to all the joins. If you want to scale that up, you'll want to consider some sort of flattened table structures. Flattened tables will likely help a lot here. You may want to stage them all with an ELT type process and then work to transform them into a more star schema state for reporting. Make sure you don't neglect good projection design as well.

  • edited August 18

    I don't store pure XML in Vertica. You are right. I parse my IDMEF XML with PHP and after that I insert data of each tag in it's table.
    For example for below XML we have 2 tables (category and data)

    table "category " has 1 column: type
    table "data " has 3 columns: value, address and category_id (relation with category table)

  • emorenoemoreno Employee

    Hi,
    If you already parse the data and transform it in a start schema, you can improve the projections in favor of the Joins. You could use DBD.
    Here you can find some shorts videos of Vertica some of vertica features such as working with projections and DBD.
    https://www.vertica.com/web-based-training/

    Eugenia

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.