MessageGears Enhances Direct Connection to data by interfacing with Google BigQuery API

May 21, 2020
Craig Pohan

The MessageGears marketing console is an enterprise Java application that connects directly to Customer data sources to allow our users to segment users and personalize messages. Traditionally, we have leveraged JDBC (Java Database Connectivity) drivers to standardize connectivity to dozens of traditional and modern databases, which work well across nearly any standard data source and give us the flexibility to connect to dozens of customer data stores.

Today, however, MessageGears now fully integrates with the Google BigQuery API to support faster and more efficient data transfer to power our Segment, Message, and Engage products.

MessageGears uses live Customer data to Segment, Message, and Engage with CRM-based recipients. Our customers generate thousands of segments and deliver personalized messages with rich contextual data to billions of recipients every month. During the process, large amounts of data is extracted from customer data stores. Our experience has shown leveraging JDBC drivers will get the job done, but not with great efficiency and they typically don’t follow preferred design patterns.

Java database connectivity has been a standard for the past 20 years, and every emerging data store offers a JDBC driver to support adoption, even if there are limitations. Modern Cloud data stores (Snowflake, Google BigQuery, among others) are designed to “break the mold” of traditional database engines and offer methods to load, explore, manipulate, and extract the ever-growing size of data large enterprises store today.

Typically, full capabilities for modern cloud data stores are provided via direct APIs. Additionally, cloud data store providers offer preferred design patterns for increased performance on large operations. However, to fit into the data store market, these vendors also provide JDBC drivers to ease integration and increase usage. Basic JDBC drivers will offer standard connectivity and basic SQL (structured query language) support.

MessageGears has and always will embrace modern data store technologies.  With such commitment, we’ve integrated Google BigQuery using Google native API patterns. Our goal was to speed up counting, extracting and loading large amounts of data.  Our results exceeded expectations and empowered our customers to manipulate tens of millions of rows of data in record time.

The key power to a native BigQuery integration is leveraging the true nature of a massively parallel processing (MPP) database. Google BigQuery stores and processes data on thousands of servers in a very distributed manner. Typical JDBC drivers will require the data store to funnel all results back through a single connection, thereby making something that starts off parallel back into a serial process. Further JDBC drivers funnel data through a generic data store connection that isn’t configured specifically for the data store vendor and is rarely recommended for large data extracts. Our observations showed that, as result sets grew in width (more columns returned), the overall query time grew linearly regardless of data size.

When using the native BigQuery API integration, MessageGears is enabling Google to execute commands in the most efficient manner. Further, data can be extracted from BigQuery using the native Google Cloud Storage hand-off.  The net result is a 16X improvement for large data extracts.

Benchmarks using the JDBC connector showed 50 million recipient record sets with 200 attributes taking over 3 hours to extract for processing to deliver personalized messages. After converting to the native integration, our team observed the exact same extract taking 12 minutes to perform the same operation. The dramatic speed improvement provides our customers more real-time personalization information and nimble UI operations to count and segment audiences for improved marketing.

Giving our users a best-in-class product is always our primary concern, and integrating newer technologies within our product is just one way we deliver value to our customers. Have a data store you’d like to see us integrate with? Let us know @Messagegears.

About the Author

Craig Pohan

Craig is the Chief Technology Officer at MessageGears responsible for all product, engineering and operations teams. Craig has a 20-year background delivering advertising and marketing technology solutions to enterprise clients. Craig spent 15 years competitively racing sailboats along the Eastern US and now lives in Atlanta with his wife and kids.