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>
“”” xmlparser.py:
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/
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/
0
Comments
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
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:
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
Use in search: shell_load_package permission error.
Same solved issue.