1Statistical Model Computation with UDFs

AbstractStatistical models are generally computed outside a DBMS due to their mathematical complexity. We introduce techniques to efciently compute fundamental statistical models inside a DBMS exploiting User-Dened-Functions (UDFs). We study the computation of linear regression, PCA, clustering and Naive Bayes. Two summary matrices on the data set are mathematically shown to be essential for all models: the linear sum of points and the quadratic sum of cross-products of points. We consider two layouts for the input data set: horizontal and vertical. We rst introduce efcient SQL queries to compute summary matrices and to score the data set. Based on the SQL framework, we introduce UDFs that work in a single table scan: aggregate UDFs to compute summary matrices for all models and a set of primitive scalar UDFs to score data sets. Experiments compare UDFs and SQL queries (running inside the DBMS) with C++ (analyzing exported les). In general, UDFs are faster than SQL queries and not much slower than C++. Considering export times, C++ is slower than UDFs and SQL queries. Statistical models based on precomputed summary matrices are computed in a few seconds. UDFs scale linearly and only require one table scan, highlighting their efciency. Index terms: DBMS; SQL; statistical model; UDF I