Python Example: validate_url
The validate_url
scalar function reads a string from a table, a URL. It then validates if the URL is responsive, returning a status code or a string indicating the attempt failed.
You can find more UDx examples in the VerticaGithub repository, https://github.com/vertica/UDx-Examples.
UDSF Python Code
import vertica_sdk import urllib.request import time class validate_url(vertica_sdk.ScalarFunction): """Validates HTTP requests. Returns the status code of a webpage. Pages that cannot be accessed return "Failed to load page." """ def __init__(self): pass def setup(self, server_interface, col_types): pass def processBlock(self, server_interface, arg_reader, res_writer): # Writes a string to the UDx log file. server_interface.log("Validating webpage accessibility - UDx") while(True): url = arg_reader.getString(0) try: status = urllib.request.urlopen(url).getcode() # Avoid overwhelming web servers -- be nice. time.sleep(2) except (ValueError, urllib.error.HTTPError, urllib.error.URLError): status = 'Failed to load page' res_writer.setString(str(status)) res_writer.next() if not arg_reader.next(): # Stop processing when there are no more input rows. break def destroy(self, server_interface, col_types): pass class validate_url_factory(vertica_sdk.ScalarFunctionFactory): def createScalarFunction(self, srv): return validate_url() def getPrototype(self, srv_interface, arg_types, return_type): arg_types.addVarchar() return_type.addChar() def getReturnType(self, srv_interface, arg_types, return_type): return_type.addChar(20)
Load the Function and Library
Create the library and the function.
=> CREATE OR REPLACE LIBRARY pylib AS 'webpage_tester/validate_url.py' LANGUAGE 'Python'; => CREATE OR REPLACE FUNCTION validate_url AS LANGUAGE 'Python' NAME 'validate_url_factory' LIBRARY pylib fenced;
Querying Data with the Function
The following query shows how you can run a query with the UDSF.
=> SELECT url, validate_url(url) AS url_status FROM webpages; url | url_status ----------------------------------------------+---------------------- http://my.vertica.com/documentation/vertica/ | 200 http://www.google.com/ | 200 http://www.mass.gov.com/ | Failed to load page http://www.espn.com | 200 http://blah.blah.blah.blah | Failed to load page http://www.hpe.com/ | 200 (6 rows)