VerticaPy

VerticaPyLab is a Docker-based JupyterLab environment designed to provide a GUI-based platform for using Vertica and VerticaPy. It eliminates the need for manual installation and dependency management by using two containers: one for the Vertica database and one for JupyterLab containing VerticaPy and its dependencies.

Key features include:

• QueryProfiler: Investigate and identify reasons for slow query performance.
• Graphics and visualizations: Create plots using Grafana, Matplotlib, Plotly, and other libraries.
• Run vsql: Execute the Vertica vsql editor directly from the interface.
• Seamless data ingestion: Effortlessly load and process massive datasets from Vertica.
• Interactive tutorials: Access VerticaPy lessons to learn core concepts.

VerticaPy provides an end-to-end analytical workflow integrated with Vertica. The core capabilities include:

• Core Data Structures: Working with vDataFrame and vDataColumn for data manipulation.
• Data Ingestion: Streamlining ETL processes to ingest data from databases, spreadsheets, and external sources into Vertica.
• Data Exploration: Using interactive visualizations, charts, and graphs to uncover patterns.
• Data Preparation: Cleaning, reshaping, and enriching datasets through pre-processing tools.
• Machine Learning: Performing predictive analysis, including model training and evaluation.
• Full Stack Integration: Combining data processing, analysis, and visualization, including support for User Defined Functions (UDFs) and GeoPandas.
• Performance Optimization: Using the QueryProfiler tool to identify bottlenecks and optimize query performance.

VerticaPy is a Python library designed for data science projects where data is stored in a Vertica database. It provides scikit-learn-like functionality but operates by bringing the logic to the data (in-database processing) rather than moving data to an in-memory Python process. This approach leverages Vertica's speed and built-in analytics/ML features.

Key Advantages:

• Easy Data Exploration
• Fast Data Preparation
• In-Database Machine Learning
• Easy Model Evaluation
• Easy Model Deployment
• Flexibility to use either Python or SQL

VerticaPy is designed for seamless analytical exploration on Vertica databases. To begin using the library, you should follow these primary workflows:

1. Setup and Connection: Configure your workspace and establish a connection to your Vertica database to ensure an optimal environment for data journeys.
2. Data Manipulation with Virtual DataFrames: Use the vDataFrame module to store and manipulate data. The Virtual DataFrame is a core abstraction designed to optimize data manipulation processes and elevate analytical capabilities.
3. Workflow Optimization: Follow established best practices for data management and visualization to maximize the efficiency of your analytical workflows.

This benchmark evaluates the performance of Vertica's Linear Regression algorithm against Apache Spark. The comparison focuses on speed, accuracy, and scalability across different data sizes and optimization methods.

Test Configuration

• Vertica Version: 8.0.1
• Spark Version: 2.02
• Convergence Criteria (both): max iter = 100, e = 10^-6

Dataset

The benchmark uses an artificial dataset generated from a Linear Regression model with added noise.

VerticaPy provides a comprehensive suite of tools for end-to-end data science workflows, ranging from data manipulation to advanced machine learning. The full-stack capabilities include:

• Complex Data Handling: Using VMaps to work with intricate data structures.
• Data Access: Seamlessly querying external databases via DBLink integration.
• Spatial Analytics: Integrating with GeoPandas for Geo DataFrame and map visualization.
• Predictive Modeling: Implementing techniques like Linear regression and XGBoost (including exporting models to JSON).
• Time-Series Analysis: Utilizing slices, interpolation, and joins for temporal data.
• Model Evaluation: Using Train/Test splits for dataset partitioning.
• Extensibility: Creating User-defined functions (UDFs) and leveraging Magic methods within the vDataFrame for enhanced manipulation.

VerticaPy provides an extensive selection of plotting graphics for data visualization and business insight extraction. The chart gallery is categorized into different types such as Basic, Machine Learning, and Geo-spatial charts.

Most charts in VerticaPy support multiple visualization engines and interfaces:

• Available Engines: Plotly, Matplotlib, and Highcharts.
• Available Interfaces: Python and SQL.

Common chart types include:

• Basic Charts: Area, Bar, Boxplot, Candlestick, Contour, Correlation Matrix, Density, Histogram, Line, Pie, Pivot, Range, Scatter, and Spider charts.

For detailed technical guidance on how to implement these graphics, refer to the VerticaPy chart gallery guide.

This benchmark compares the performance of the Vertica ARIMA algorithm against the Python statsmodels library. The comparison evaluates three key metrics across varying dataset sizes (from 10K to 100M rows):

1. Training Run Time: How long it takes to train the model.
2. Prediction Run Time: How long it takes to generate predictions.
3. Mean Squared Error (MSE): The accuracy of the model. For Vertica, the MSE is retrieved from the summary table using the GET_MODEL_SUMMARY command.

Benchmark Configuration:

• Parameters: p = 5, d = 1, q = 1
• Dataset: Artificially created using a Linear model with random noise.
• Vertica Version: 23.3.0-5
• Python Statsmodels Version: 0.14.0