Issue loading xml data into vertica table

Deepthi_1

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/

[Deleted User]

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

id10t

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)
  

Deepthi_1

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

id10t

Hi!

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