research-article

Large-scale Predictive Analytics in Vertica: Fast Data Transfer, Distributed Model Creation, and In-database Prediction

Authors:

Jorge Martinez,

Indrajit RoyAuthors Info & Claims

SIGMOD '15: Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data

Pages 1657 - 1668

https://doi.org/10.1145/2723372.2742789

Published: 27 May 2015 Publication History

Abstract

A typical predictive analytics workflow will pre-process data in a database, transfer the resulting data to an external statistical tool such as R, create machine learning models in R, and then apply the model on newly arriving data. Today, this workflow is slow and cumbersome. Extracting data from databases, using ODBC connectors, can take hours on multi-gigabyte datasets. Building models on single-threaded R does not scale. Finally, it is nearly impossible to use R or other common tools, to apply models on terabytes of newly arriving data.

We solve all the above challenges by integrating HP Vertica with Distributed R, a distributed framework for R. This paper presents the design of a high performance data transfer mechanism, new data-structures in Distributed R to maintain data locality with database table segments, and extensions to Vertica for saving and deploying R models. Our experiments show that data transfers from Vertica are 6x faster than using ODBC connections. Even complex predictive analysis on 100s of gigabytes of database tables can complete in minutes, and is as fast as in-memory systems like Spark running directly on a distributed file system.

References

[1]

Apache Mahout. http://mahout.apache.org.

[2]

Comparing pattern mining on a billion records with HP Vertica and Hadoop. http://www.vertica.com/2013/04/08/comparing-pattern-mining-on-a-billion-records-with-hp-vertica-and-hadoop/.

[3]

HP Vertica Distributed R. https://www.vertica.com/distributedR.

[4]

MATLAB-parallel computing toolbox. http://www.mathworks.com/products/parallel-computing/.

[5]

MATLAB{the language of technical computing. http://www.mathworks.com/products/matlab.

[6]

Process containers. http://lwn.net/Articles/236038/.

[7]

Programmatic media buying. http://rocketfuel.com/.

[8]

The R project for statistical computing. http://www.r-project.org.

[9]

Revolution R enterprise scaler. http://www.revolutionanalytics.com/revolution-r-enterprise-scaler.

[10]

RHadoop and MapR. https://www.mapr.com/resources/rhadoop-and-mapr.

[11]

SAS analytics. http://www.sas.com.

[12]

SAS in-memory statistics for Hadoop. http://www.sas.com/en_us/software/sas-hadoop/in-memory-hadoop.html.

[13]

Teradata parallel transporter. http://www.teradata.com/tools-and-utilities/parallel-transporter/.

[14]

Teradata Warehouse Miner. http://www.teradata.com/products-and-services/teradata-warehouse-miner/.

[15]

S. Brin and L. Page. The anatomy of a large-scale hypertextual Web search engine. In WWW7, 1998.

Digital Library

[16]

Y. Bu, B. Howe, M. Balazinska, and M. D. Ernst. HaLoop: Efficient iterative data processing on large clusters. PVLDB, 3(1-2), 2010.

Digital Library

[17]

S. Das, Y. Sismanis, K. S. Beyer, R. Gemulla, P. J. Haas, and J. McPherson. Ricardo: Integrating R and Hadoop. In SIGMOD, 2010.

Digital Library

[18]

J. Dean and S. Ghemawat. MapReduce: Simplified data processing on large clusters. Commun. ACM, 51(1), 2008.

Digital Library

[19]

J. Ekanayake, H. Li, B. Zhang, T. Gunarathne, S.-H. Bae, J. Qiu, and G. Fox. Twister: A runtime for iterative MapReduce. In HPDC, 2010.

Digital Library

[20]

J. E. Gonzalez, Y. Low, H. Gu, D. Bickson, and C. Guestrin. PowerGraph: Distributed graph-parallel computation on natural graphs. In OSDI, 2012.

Digital Library

[21]

P. Gro'e, W. Lehner, T. Weichert, F. Färber, and W.-S. Li. Bridging two worlds with RICE integrating R into the SAP in-memory computing engine. PVLDB, 4(12), 2011.

[22]

J. M. Hellerstein, C. Ré, F. Schoppmann, D. Z. Wang, E. Fratkin, A. Gorajek, K. S. Ng, C. Welton, X. Feng, K. Li, and A. Kumar. The MADlib analytics library: Or MAD skills, the SQL. PVLDB, 5(12), 2012.

Digital Library

[23]

A. Lamb, M. Fuller, R. Varadarajan, N. Tran, B. Vandiver, L. Doshi, and C. Bear. The Vertica analytic database: C-store 7 years later. PVLDB, 5(12), 2012.

Digital Library

[24]

G. Malewicz, M. H. Austern, A. J. Bik, J. C. Dehnert, I. Horn, N. Leiser, and G. Czajkowski. Pregel: A system for large-scale graph processing. In SIGMOD, 2010.

Digital Library

[25]

E. Soroush, M. Balazinska, and D. Wang. ArrayStore: A storage manager for complex parallel array processing. In SIGMOD, 2011.

Digital Library

[26]

S. Venkataraman, E. Bodzsar, I. Roy, A. AuYoung, and R. S. Schreiber. Presto: Distributed machine learning and graph processing with sparse matrices. In EuroSys, 2013.

Digital Library

[27]

T. J. Ypma. Historical development of the Newton-Raphson method. SIAM Rev., 37(4), 1995.

Digital Library

[28]

M. Zaharia, M. Chowdhury, T. Das, A. Dave, J. Ma, M. McCauley, M. J. Franklin, S. Shenker, and I. Stoica. Resilient distributed datasets: a fault-tolerant abstraction for in-memory cluster computing. In NSDI, 2012.

Digital Library

Cited By

Wen X(2024)TensorTable: Extending PyTorch for mixed relational and linear algebra pipelinesBenchCouncil Transactions on Benchmarks, Standards and Evaluations10.1016/j.tbench.2024.1001614:1(100161)Online publication date: Mar-2024
https://doi.org/10.1016/j.tbench.2024.100161
He DNakandala SBanda DSen RSaur KPark KCurino CCamacho-Rodríguez JKaranasos KInterlandi M(2022)Query processing on tensor computation runtimesProceedings of the VLDB Endowment10.14778/3551793.355183315:11(2811-2825)Online publication date: 29-Sep-2022
https://dl.acm.org/doi/10.14778/3551793.3551833
Schüle MLang HSpringer MKemper ANeumann TGünnemann S(2022)Recursive SQL and GPU-support for in-database machine learningDistributed and Parallel Databases10.1007/s10619-022-07417-740:2-3(205-259)Online publication date: 9-Jul-2022
https://doi.org/10.1007/s10619-022-07417-7
Show More Cited By

Index Terms

Large-scale Predictive Analytics in Vertica: Fast Data Transfer, Distributed Model Creation, and In-database Prediction
1. Information systems
  1. Data management systems
    1. Database management system engines

Recommendations

Unified analytics platform for big data
WICSA/ECSA '12: Proceedings of the WICSA/ECSA 2012 Companion Volume

Greenplum is using Hadoop and several other open source tools in interesting ways as part of a big data architecture with their Greenplum Database (a scale-out MPP SQL database).
Large-scale multilevel streaming data analytics
CASCON '18: Proceedings of the 28th Annual International Conference on Computer Science and Software Engineering

There is a monumental shift happening in how data powers organizational and business operations. This shift is about moving away from traditional batch and real-time analytics to hybrid analytics involving both static and continuous data. Most analytics ...
Large-scale complex analytics on semi-structured datasets using asterixDB and spark

Large quantities of raw data are being generated by many different sources in different formats. Private and public sectors alike acclaim the valuable information and insights that can be mined from such data to better understand the dynamics of ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '15: Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data

May 2015

2110 pages

ISBN:9781450327589

DOI:10.1145/2723372

General Chair:
Timos Sellis
RMIT University, Australia
,
Program Chairs:
Susan B. Davidson
University of Pennsylvania, USA
,
Zack Ives
University of Pennsylvania, USA

Copyright © 2015 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected].

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 27 May 2015

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS'15

Sponsor:

SIGMOD

SIGMOD/PODS'15: International Conference on Management of Data

May 31 - June 4, 2015

Victoria, Melbourne, Australia

Acceptance Rates

SIGMOD '15 Paper Acceptance Rate 106 of 415 submissions, 26%;

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

11
Total Citations
View Citations
757
Total Downloads

Downloads (Last 12 months)27
Downloads (Last 6 weeks)3

Reflects downloads up to 28 Feb 2025

Other Metrics

View Author Metrics

Citations

Cited By

Wen X(2024)TensorTable: Extending PyTorch for mixed relational and linear algebra pipelinesBenchCouncil Transactions on Benchmarks, Standards and Evaluations10.1016/j.tbench.2024.1001614:1(100161)Online publication date: Mar-2024
https://doi.org/10.1016/j.tbench.2024.100161
He DNakandala SBanda DSen RSaur KPark KCurino CCamacho-Rodríguez JKaranasos KInterlandi M(2022)Query processing on tensor computation runtimesProceedings of the VLDB Endowment10.14778/3551793.355183315:11(2811-2825)Online publication date: 29-Sep-2022
https://dl.acm.org/doi/10.14778/3551793.3551833
Schüle MLang HSpringer MKemper ANeumann TGünnemann S(2022)Recursive SQL and GPU-support for in-database machine learningDistributed and Parallel Databases10.1007/s10619-022-07417-740:2-3(205-259)Online publication date: 9-Jul-2022
https://doi.org/10.1007/s10619-022-07417-7
Zhang ZWu WJiang JYu LCui BZhang C(2020)C olumnSGD: A Column-oriented Framework for Distributed Stochastic Gradient Descent2020 IEEE 36th International Conference on Data Engineering (ICDE)10.1109/ICDE48307.2020.00134(1513-1524)Online publication date: Apr-2020
https://doi.org/10.1109/ICDE48307.2020.00134
Bachinger FKronberger G(2019)Concept for a Technical Infrastructure for Management of Predictive Models in Industrial ApplicationsComputer Aided Systems Theory – EUROCAST 201910.1007/978-3-030-45093-9_32(263-270)Online publication date: 17-Feb-2019
https://dl.acm.org/doi/10.1007/978-3-030-45093-9_32
Kumar ABoehm MYang JChirkova RYang JSuciu D(2017)Data Management in Machine LearningProceedings of the 2017 ACM International Conference on Management of Data10.1145/3035918.3054775(1717-1722)Online publication date: 9-May-2017
https://dl.acm.org/doi/10.1145/3035918.3054775
Jiang JCui BZhang CYu LChirkova RYang JSuciu D(2017)Heterogeneity-aware Distributed Parameter ServersProceedings of the 2017 ACM International Conference on Management of Data10.1145/3035918.3035933(463-478)Online publication date: 9-May-2017
https://dl.acm.org/doi/10.1145/3035918.3035933
Ma EGupta VHsu MRoy I(2016)dmapplyProceedings of the VLDB Endowment10.14778/3007263.30072689:13(1293-1304)Online publication date: 1-Sep-2016
https://dl.acm.org/doi/10.14778/3007263.3007268
LeFevre JLiu RInigo CPaz LMa ECastellanos MHsu MÖzcan FKoutrika GMadden S(2016)Building the Enterprise Fabric for Big Data with Vertica and Spark IntegrationProceedings of the 2016 International Conference on Management of Data10.1145/2882903.2903744(63-75)Online publication date: 26-Jun-2016
https://dl.acm.org/doi/10.1145/2882903.2903744
Venkataraman SYang ZLiu DLiang EFalaki HMeng XXin RGhodsi AFranklin MStoica IZaharia MÖzcan FKoutrika GMadden S(2016)SparkRProceedings of the 2016 International Conference on Management of Data10.1145/2882903.2903740(1099-1104)Online publication date: 26-Jun-2016
https://dl.acm.org/doi/10.1145/2882903.2903740
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Figures

Tables

Media

View Table of Conten