Python Example: currency_convert
The currency_convert
scalar function reads two values from a table, a currency and a value. It then converts the item's value to USD, returning a single float result.
You can find more UDx examples in the VerticaGithub repository, https://github.com/vertica/UDx-Examples.
UDSF Python Code
import vertica_sdk import decimal rates2USD = {'USD': 1.000, 'EUR': 0.89977, 'GBP': 0.68452, 'INR': 67.0345, 'AUD': 1.39187, 'CAD': 1.30335, 'ZAR': 15.7181, 'XXX': -1.0000} class currency_convert(vertica_sdk.ScalarFunction): """Converts a money column to another currency Returns a value in USD. """ def __init__(self): pass def setup(self, server_interface, col_types): pass def processBlock(self, server_interface, block_reader, block_writer): while(True): currency = block_reader.getString(0) try: rate = decimal.Decimal(rates2USD[currency]) except KeyError: server_interface.log("ERROR: {} not in dictionary.".format(currency)) # Scalar functions always need a value to move forward to the # next input row. Therefore, we need to assign it a value to # move beyond the error. currency = 'XXX' rate = decimal.Decimal(rates2USD[currency]) starting_value = block_reader.getNumeric(1) converted_value = decimal.Decimal(starting_value / rate) block_writer.setNumeric(converted_value) block_writer.next() if not block_reader.next(): break def destroy(self, server_interface, col_types): pass class currency_convert_factory(vertica_sdk.ScalarFunctionFactory): def createScalarFunction(self, srv): return currency_convert() def getPrototype(self, srv_interface, arg_types, return_type): arg_types.addVarchar() arg_types.addNumeric() return_type.addNumeric() def getReturnType(self, srv_interface, arg_types, return_type): return_type.addNumeric(9,4)
Load the Function and Library
Create the library and the function.
=> CREATE LIBRARY pylib AS '/home/dbadmin/python_udx/currency_convert/currency_convert.py' LANGUAGE 'Python'; CREATE LIBRARY => CREATE FUNCTION currency_convert AS LANGUAGE 'Python' NAME 'currency_convert_factory' LIBRARY pylib fenced; CREATE FUNCTION
Querying Data with the Function
The following query shows how you can run a query with the UDSF.
=> SELECT product, currency_convert(currency, value) AS cost_in_usd FROM items; product | cost_in_usd --------------+------------- Shoes | 133.4008 Soccer Ball | 110.2817 Coffee | 13.5190 Surfboard | 176.2593 Hockey Stick | 76.7177 Car | 17000.0000 Software | 10.4424 Hamburger | 7.5000 Fish | 130.4272 Cattle | 269.2367 (10 rows)