Issue loading xml data into vertica table

Hi, I'm facing an issue loading xml data into vertica table using External Filter using the below command:


COPY cities FROM LOCAL ‘sample_data.xml’ WITH FILTER ExternalFilter(‘/path/to/xmlparser.py’);


 The sample_data.xml contains:

<data>

<record>

<city>Cambridge, MA</city>

<population>106038</population>

</record>

</data>

 xmlparser.py:

“”” xmlparser.py:

#!/usr/bin/env python

import sys, xml.etree.ElementTree

for record in xml.etree.ElementTree.fromstringlist(sys.stdin).getchildren():

keys = record.getchildren()

print ‘|’.join(key.text for key in keys)

“””

 The xml parser is working fine but while loading the data using COPY command, I am getting an error - ERROR 4856: Syntax error at or near "'xmlparser.py'" at character 69 Please advise.

 Please refer to this link for External Filter and python code(for xml parser). http://www.vertica.com/2012/11/14/how-to-parse-anything-into-vertica-using-externalfilter/

Comments

  • Hi Deepthi, 

    Can you try COPY command as below and see if it helps?

    COPY cities FROM LOCAL ‘sample_data.xml’ WITH FILTER ExternalFilter(cmd='python /path/to/xmlparser.py’); 

    Thanks


  • Hi!

    Do you installed External Filter from shell_load_package? 99,99% - its not installed.
    Install and everything will work.

    If it installed, so check quotes in query:
    • good single quote is apostrophe - <'> (U+0027 APOSTROPHE)
    • bad single quotes <‘> and <’> (U+2019 RIGHT SINGLE QUOTATION MARK)
  • Hi Pravesh,


    I tried using your approach but I'm still getting an error. Please see below:

    dbadmin=> COPY cities_xml FROM LOCAL 'sample1.xml' WITH FILTER ExternalFilter(cmd='python /home/dbadmin/xmlparser.py');

    ERROR 3399:  Failure in UDx RPC call InvokeProcessUDL(): Error calling processUDL() in User Defined Object [ExternalFilter] at [ProcessLaunchingPlugin.cpp:82], error code: 0, message: External process 'python /home/dbadmin/xmlparser.py' reported error: python: can't open file '/home/dbadmin/xmlparser.py': [Errno 13] Permission denied



  • Hi!

    Use in search: shell_load_package permission error.
    Same solved issue.

Leave a Comment

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