Introduction to Pivotal GPText

Pivotal GPText enables processing mass quantities of raw text data (such as social media feeds or e-mail databases) into mission-critical information that guides business and project decisions. GPText joins the Greenplum Database massively parallel-processing database server with Apache SolrCloud enterprise search. GPText includes powerful text search as well as support for text analysis. GPText supports business decision making by offering:

  • Multiple kinds of data: GPText supports both semi-structured and unstructured data searches, which exponentially increases the kinds of information you can find.
  • Multiple document sources: GPText can index documents stored in Greenplum Database tables or documents retrieved from external stores, such as HTTP or FTP servers, Amazon S3, or Hadoop hdfs. Most document formats are recognized automatically.
  • Less schema dependence: GPText does not require static schemas to successfully locate information; schemas can change or be quite simple and still return targeted results.
  • Natural language text processing: GPText provides NLP capabilities with the integrated Apache OpenNLP toolkit.
  • Text analytics: You can use Apache MADlib in Greenplum Database for advanced machine learning, graph, statistics and analytics in Greenplum Database.

This chapter contains the following topics:

GPText System Architecture

GPText combines a Greenplum Database cluster with an Apache SolrCloud cluster. Greenplum Database segments and GPText nodes can be deployed on the same hosts or on different hosts with network connectivity.

The following figure shows the process architecture of the combined Greenplum Database and ApacheSolr clusters. The figure shows four cluster nodes with four Greenplum segments and four Solr instances deployed on each. An Apache ZooKeeper service manages the SolrCloud cluster. ZooKeeper nodes are deployed on three of the four hosts. Greenplum Database users access SolrCloud services via GPText user-defined functions installed in Greenplum databases and command-line utilities.

The figure omits the Greenplum master host, secondary master, and mirror segments for the Greenplum primary segments.

The Greenplum segments, Solr instances, and ZooKeeper nodes may all be deployed on separate hosts on the same network, depending on application and performance requirements.

The following sections describe how GPText integrates SolrCloud with Greenplum Database and how the two clusters work together to provide parallel text search capabilities in Greenplum Database and maintain high availability.

Greenplum Database Cluster

A Greenplum Database cluster is comprised of the following components:

  • A master database instance, executing on a dedicated host, conventionally named mdw. (Not illustrated)
  • A secondary master instance, on a host conventionally named smdw, acting as a warm standby for the master instance. (Not illustrated)
  • An array of database primary segment instances and mirrors deployed on segment hosts, by convention sdw1 through sdwn. A segment instance is an independent Postgres database server managing a portion of the distributed data. Each segment has a mirror (not illustrated) on another host in the cluster to provide uninterrupted service in case of a segment or segment host failure. The number of primary segments per host is determined by the hardware configuration—the number and type of processor cores, the amount of physical RAM, local storage capacity, and network capacity—as well as availability and performance requirements.

The Greenplum Database master instance, which stores no user data, coordinates the work of the segment instances. Database users log in to the master instance and submit SQL queries. The master instance creates a plan for executing the query, distributes the work to the segments, and gathers and returns the results to the user.

Apache SolrCloud

Apache Solr is a server providing access to Apache Lucene full-text indexes. Apache SolrCloud is a highly available, fault tolerant cluster of Apache Solr servers. The term GPText cluster is another way to refer to a SolrCloud cluster deployed by GPText for use with a Greenplum Database system.

A SolrCloud cluster is comprised of the following components:

  • An Apache ZooKeeper cluster to manage the SolrCloud cluster. SolrCloud uses ZooKeeper to manage server and index configurations and to coordinate the cluster’s activities. GPText can install a ZooKeeper cluster that is bound to the GPText cluster, or it can share an existing ZooKeeper cluster. If GPText installs the ZooKeeper cluster, it can be managed using GPText functions and utilities. The ZooKeeper cluster can be deployed on Greenplum Database cluster hosts or, for best performance, on separate hosts accessible to the Greenplum Database cluster.
  • Multiple SolrCloud server instances deployed on the Greenplum segment hosts or on other hosts on the same network. Each instance is a JVM process running Solr server. SolrCloud instances use local storage, which may be the same local storage volumes that store Greenplum Database data. The number of SolrCloud instances per host can be the same as the number of Greenplum primary segments per host, but this is not a requirement. The number of instances to execute per host is specified during GPText installation.

GPText provides document indexing and search capabilities for Greenplum Database with user-defined functions (UDFs) that access Solr APIs from within database queries.

GPText UDFs perform the following tasks:

  • create and manage GPText indexes
  • provide status information about indexes
  • insert documents into indexes from database tables or, for GPText external indexes, from documents stored outside of Greenplum Database
  • search indexes

There are also GPText UDFs and command-line utilities to configure, monitor, and manage the SolrCloud cluster, and to manage replicas, SolrCloud’s high-availability mechanism. (More on replicas in the next section.)

Parallelism in GPText Indexing and Searching

SolrCloud distributes document indexes in slices called shards. Each shard is managed by a SolrCloud instance and ZooKeeper ensures that the shards are distributed evenly among the SolrCloud instances. The SolrCloud instances and Greenplum segments are not required to be on the same hosts.

With GPText, the default number of shards for an index is the number of Greenplum Database segments, so that each segment operates on an equal portion of the index. Optionally, a lesser number of shards can be specified when you create a GPText index, allowing indexing workloads to be scaled for performance requirements and resource usage.

High Availability for GPText Indexes

SolrCloud provides high availability by maintaining replicas of shards and providing automatic failover if a shard fails or becomes unavailable. One replica of each shard is the lead replica and any changes to it are applied to the other replicas. The replication factor, which determines the number of replicas to maintain for each shard, is set when the index is created. Replicas may also be added or dropped later using GPText UDFs or command-line utilities.

ZooKeeper determines the locations of shard replicas among the Solr nodes and hosts. When adding a replica using a GPText UDF or command-line utility, a new shard can be explicitly placed on a SolrCloud instance.

GPText Sample Use Case

Forensic financial analysts need to locate communications among corporate executives that point to financial malfeasance in their firm. The analysts use the following workflow:

  1. Load the email records into a Greenplum database.
  2. Create a Solr index of the email records.
  3. Run queries that look for text strings and their authors.
  4. Refine the queries until they pair a dummy company name with top three or four executives corresponding about suspect offshore financial transactions. With this data, the analysts can focus the investigation on specific individuals rather than the thousands of authors in the initial data sample.

GPText Workflow

GPText works with Greenplum Database and Apache SolrCloud to store and index big data for information retrieval (query) purposes. High-level workflows include data loading and indexing, and data querying.

This topic describes the following information:

Data Loading and Indexing Workflow

The following diagram shows the GPText workflow for loading and indexing data.

All client interaction with the system is through the Greenplum master instance.

  1. Load data into your Greenplum Database system.

    Create a database table to hold data and then add the data to the table. Greenplum provides parallel data loading utilities and protocols that help to transform and load external data in various formats and from various sources. For details, see the Greenplum Database Administrator Guide, at

    You can also create an external index for documents you retrieve from a web server, ftp server, Amazon S3, or hdfs. You can

  2. Create and configure an empty GPText index.

    Use the gptext.create_index() user-defined function (UDF) to create an empty GPText index for a database table. GPText stores configuration files for the index in ZooKeeper.

  3. Customize the index, if desired, by editing the index configuration files with the gptext-config command-line utility. You can customize the way document text is tokenized, filtered, and transformed before storing in the index and how query text is prepared to search the index.

  4. Populate the index with data from the database table or external data source.

    Use the gptext.index() or gptext.index_external() UDF to add data to the index. These UDFs work by dispatching SQL queries to execute on each Greenplum segment. The segments execute the queries and add the results to the index using Solr APIs.

  5. Commit changes to the index.

    Commit changes to the GPText index by calling the gptext.commit_index() UDF. Until the changes are committed, queries executed on the index cannot access any data added to the index with gptext.index(). If needed, uncommitted changes can be rolled back. SolrCloud replicates changes committed to the lead replica to the shards’ non-lead replicas.

Querying Data Workflow

The following diagram shows the high-level GPText query process workflow:

  1. A user submits a SQL query designed to search the indexed data.

    A GPText search query is a SQL SELECT statement on a GPText search UDF that contains full-text search expressions.

  2. The Greenplum master dispatches the query to the Greenplum Database segments.

  3. Each segment executes the query, using the Solr API to search its index shard.

    Solr analyzes and executes the search query on the lead replica for the shard.

  4. The Greenplum Database segments return the results of the search query to the Greenplum Database master.

  5. The Greenplum Database master aggregates the results from all segments and returns them to the client.

Text Analysis

GPText enables analysis of Solr indexes with Apache MADlib, an open source library for scalable in-database analytics. MADlib provides data-parallel implementations of mathematical, statistical, and machine learning methods for structured and unstructured data. You can use GPText to perform a variety of MADlib analyses.

Learn more about Apache MADlib at A gppkg package for MADlib is available on the Pivotal network at

The Apache OpenNLP toolkit provides advanced machine learning tools for tokenizing, recognizing, and tagging natural language text that you can enable for GPText indexin and searching. See Natural Language Processing with GPText Indexes for more information.