R Example: SalesTaxCalculator
The SalesTaxCalculator
scalar function reads a float and a varchar from a table, an item's price and the state abbreviation. It then uses the state abbreviation to find the sales tax rate from a list and calculates the item's price including the state's sales tax, returning the total cost of the item.
You can find more UDx examples in the VerticaGithub repository, https://github.com/vertica/UDx-Examples.
Load the Function and Library
Create the library and the function.
=> CREATE OR REPLACE LIBRARY rLib AS 'sales_tax_calculator.R' LANGUAGE 'R'; CREATE LIBRARY => CREATE OR REPLACE FUNCTION SalesTaxCalculator AS LANGUAGE 'R' NAME 'SalesTaxCalculatorFactory' LIBRARY rLib FENCED; CREATE FUNCTION
Querying Data with the Function
The following query shows how you can run a query with the UDSF.
=> SELECT item, state_abbreviation, price, SalesTaxCalculator(price, state_abbreviation) AS Price_With_Sales_Tax FROM inventory; item | state_abbreviation | price | Price_With_Sales_Tax -------------+--------------------+-------+--------------------- Scarf | AZ | 6.88 | 7.53016 Software | MA | 88.31 | 96.655295 Soccer Ball | MS | 12.55 | 13.735975 Beads | LA | 0.99 | 1.083555 Baseball | TN | 42.42 | 46.42869 Cheese | WI | 20.77 | 22.732765 Coffee Mug | MA | 8.99 | 9.839555 Shoes | TN | 23.99 | 26.257055 (8 rows)
UDSF R Code
SalesTaxCalculator <- function(input.data.frame) { # Not a complete list of states in the USA, but enough to get the idea. state.sales.tax <- list(ma = 0.0625, az = 0.087, la = 0.0891, tn = 0.0945, wi = 0.0543, ms = 0.0707) for ( state_abbreviation in input.data.frame[, 2] ) { # Ensure state abbreviations are lowercase. lower_state <- tolower(state_abbreviation) # Check if the state is in our state.sales.tax list. if (is.null(state.sales.tax[[lower_state]])) { stop("State is not in our small sample!") } else { sales.tax.rate <- state.sales.tax[[lower_state]] item.price <- input.data.frame[, 1] # Calculate the price including sales tax. price.with.sales.tax <- (item.price) + (item.price * sales.tax.rate) } } return(price.with.sales.tax) } SalesTaxCalculatorFactory <- function() { list(name = SalesTaxCalculator, udxtype = c("scalar"), intype = c("float", "varchar"), outtype = c("float")) }