Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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