Open Sourcing Querybook, Pinterest’s Collaborative Big Data Hub

8,027
Pinterest
Pinterest is a social bookmarking site where users collect and share photos of their favorite events, interests and hobbies. One of the fastest growing social networks online, Pinterest is the third-largest such network behind only Facebook and Twitter.

An efficient big data solution for an increasingly remote-working world.

By Charlie Gu | Tech Lead, Analytics Platform, Lena Ryoo | Software Engineer, Analytics Platform, and Justin Mejorada-Pier | Engineering Manager, Analytics Platform

With more than 300 billion Pins, Pinterest is powering an ever-growing and unique dataset that maps interests, ideas, and intent. As a data-driven company, Pinterest uses data insights and analysis to make product decisions and evaluations to improve the Pinner experience for more than 450 million monthly users. To continuously make these improvements, especially in an increasingly remote environment, it’s more important than ever for teams to be able to compose queries, create analyses, and collaborate with one another. Today we’re taking Querybook, our solution for more efficient and collaborative big data access, and open sourcing it for the community.

The common starting point for any analysis at Pinterest is an ad-hoc query that gets executed on a SparkSQL, Hive, Presto cluster, or any Sqlalchemy compatible engine. We built Querybook to provide a responsive and simple web UI for such analysis so data scientists, product managers, and engineers can discover the right data, compose their queries, and share their findings. In this post, we’ll discuss the motivation to build Querybook, its features, architecture, and our work to open source the project.

The Journey

The proposal to build Querybook started in 2017 as an intern project. During that time, we used a vendor-supplied web application as the query UI. There were often user complaints about that tool regarding its UI, speed & stability, lack of visualizations, as well as difficulty in sharing. Before long, we realized there was a great opportunity to build a better querying interface.

We started to interview data scientists and engineers about their workflows while scoping out technical details. Shortly, we realized most were organizing their queries outside of the official tool, and many used apps like Evernote. Although Jupyter had a notebook user experience, its requirement to use Python/R and the lack of table metadata integration deterred many users. Based on this finding, our team decided Querybook’s query interface would be a document where users can compose queries and write analyses all in one place with the power of collocated metadata and the simplicity of a note-taking app.

Released internally in March 2018, Querybook became the official solution to query big data at Pinterest. Nowadays, Querybook on average has 500 DAUs and 7k daily query runs. With an internal user rating of 8.1/10, it’s one of the highest-rated internal tools at Pinterest.

Feature Highlights

Figure 1. Querybook’s Doc UI

When a user first visits, they‘ll quickly notice its distinctive DataDoc interface. This is the primary place for users to query and analyze. Each DataDoc is composed of a list of cells which can be one of three types: text, query, or chart.

  • The text cell comes with built-in rich-text support for users to jot down their ideas or insights.
  • The query cell is used to compose and execute queries.
  • The chart cell is used to create visualizations based on execution results. Similar to Google Docs, when users are granted access to a DataDoc, they can collaborate with each other in real-time.

With the intuitive charting UI, users can easily turn a DataDoc into an illustrative dashboard. You can choose from different visualization options, such as time-series, pie-charts, scatter plots, and more. You can then connect your visualization to the results of any query on your DataDoc and post-process them with sorting and aggregation as needed. To automatically update these charts, you can use the scheduling options and select your desired cadence. The scheduler can notify users of success or failure. Combined with the templating option powered by Jinja, creating a live updating DataDoc is very quick.

Scheduling and visualization features aren’t intended to replace tools such as Airflow or Superset. Rather, these features provide users a simple and fast way to experiment with their queries and iterate on them. Often, Pinterest engineers use Querybook as the first step to compose queries before creating production-level workflows and dashboards.

Last but not least, Querybook comes with an automated query analytics system. Every query executed gets analyzed to extract metadata such as referenced tables and query runners. Querybook uses this information to automatically update its data schema and search ranking, as well as to show a table’s frequent users and query examples. The more queries, the more documented the tables become.

Architecture

Figure 2. Overview of Querybook’s architecture

To understand how Querybook works, we’ll walk through the process of composing and executing a query.

  1. The first step is to create a DataDoc and write the query in a cell. While the user types, the user’s query gets streamed to the server via Socket.IO.
  2. The server then pushes the delta to all users reading that DataDoc via Redis. At the same time, the server would save the updated DataDoc in the database and create an async job for the worker to update the DataDoc content in ElasticSearch. This allows the DataDoc to be searched later.
  3. Once the query is written, the user can execute the query by clicking the run button. The server would then create a record in the database and insert a query job into the Redis task queue. The worker receives the task and sends the query to the query engine (Presto, Hive, SparkSQL, or any Sqlalchemy compatible engine). While the query is running, the worker pushes live updates to the UI via Socket.IO.
  4. When the execution is completed, the worker loads the query result and uploads it in batches to a configurable storage service (e.g. S3). Finally, the browser gets notified of the query completion and makes a request to the server to load the query result and display it to the user.

For brevity, this section only focused on one user flow of Querybook. However, all the infrastructure used is covered. Querybook allows some of it to be customized. For example, you can choose to upload execution results to either S3, Google Cloud Storage, or a local file. In addition, MySQL can also be swapped with any Sqlalchemy-compatible database such as Postgres.

The Path To Open Source

After noticing the success that Querybook had internally, we decided to open source it. One challenge we bumped into was how to make it generic while preserving some of the Pinterest-specific integrations. For this, we decided to have a two-layer organization through a plugin system and to add an Admin UI.

The Admin UI lets companies configure Querybook’s query engines, table metadata ingestion, and access permissions from a single friendly interface. Previously, these configurations were done inside configuration files and required a code change as well as a deployment to be reflected. With this new UI, admins can make live Querybook changes without going through code or config files.

Figure 3. The Admin UI

The plugin system integrates Querybook with the internal systems at Pinterest by utilizing Python’s importlib. With the plugin system, developers can configure auth, customize query engines, and implement exporters to internal sites. Customized behaviors provided by the plugin system allow Querybook to be optimized for the user’s workflow at Pinterest while ensuring the open-source is generic for the public.

You can check out more of Querybook’s features and its documentation on Querybook.org, and you can reach us at querybook@pinterest.com.

Acknowledgments: We want to thank the following engineers that have made contributions to Querybook: Lauren Mitchell, Langston Dziko, Mohak Nahta, and Franklin Shiao. And to Chunyan Wang, Dave Burgess, and David Chaiken for their critical advice and support.

Pinterest
Pinterest is a social bookmarking site where users collect and share photos of their favorite events, interests and hobbies. One of the fastest growing social networks online, Pinterest is the third-largest such network behind only Facebook and Twitter.
Tools mentioned in article
Open jobs at Pinterest
Engineer Manager, Content Knowledge S...
San Francisco, CA, US

About Pinterest:  

Millions of people across the world come to Pinterest to find new ideas every day. It’s where they get inspiration, dream about new possibilities and plan for what matters most. Our mission is to help those people find their inspiration and create a life they love. In your role, you’ll be challenged to take on work that upholds this mission and pushes Pinterest forward. You’ll grow as a person and leader in your field, all the while helping Pinners make their lives better in the positive corner of the internet.

Pinterest helps people Discover and Do the things they love. We have more than 450M monthly active users who actively curate an ecosystem of more than 100B Pins on more than 1B boards, creating a rich human curated graph of immense value. 

Technically, we are building out an internet scale personalized recommendation engine in 22+ languages, which requires a deep understanding of the users and content on our platform. As engineer manager on the Content Knowledge Signal team, you’ll work on building 20+ content understanding signals based on Pinterest Knowledge Graph, which will make measurably positive impact on hundreds of millions of users with improved recommendation and featurization breakthroughs on almost all Pinterest product surfaces (Discovery, Shopping, Growth, Ads, etc). 

What you'll do:

  • Manage a horizontal team of talented and dedicated ML engineers to build the foundational content understanding and engagement features of our contents to be used across all Pinterest ecosystems
  • Utilize state of the art algorithms/industry best practice to build and improve content understanding signals 
  • Partner with other engineering teams and sales & marketing team to discover future opportunities to improve content recommendation on Pinterest
  • Hire new engineers to grow the team
  • Build ML models using text and visual information of a pin, identify the most relevant set of text annotations for that pin. These sets of highly relevant annotations are among the most important features used in more than 30 use cases within Pinterest, including key ranking models of Homefeed, Search and Ads.
  • Build ML models using text and images of the products, to understand their product categories (bags, shoes, shirts, etc) and their attributes (brand, color, style, etc). They are used to greatly improve relevance for product recommendation on major shopping surfaces. 
  • Build ML models to understand search queries, then use them, together with Pin level signals, to boost search relevance. 
  • Build graph based embedding as well as explicit annotation to represent the specialties of our native content creators, to improve creator and native content recommendation.
  • Build highly efficient and expandable data pipelines to understand engagement data at various entity levels. Such engagement signals are the major feature of the ranking models for our three main Discovery surfaces. 
  •  

What we're looking for:

  • 2+ years of industrial experience in ML team’s EM or TL for one or multiple of the following use cases with large scale: ads targeting, search and discovery, growth, content/user understanding
  • Hands-on experience working with ML algorithm development and productization.  
  • Experience working with PMs and XFN partners on E2E systems and moving business metrics

#TG1

Our Commitment to Diversity:

At Pinterest, our mission is to bring everyone the inspiration to create a life they love—and that includes our employees. We’re taking on the most exciting challenges of our working lives, and we succeed with a team that represents an inclusive and diverse set of identities and backgrounds.

Software Engineer, Machine Learning P...
San Francisco, CA, US

About Pinterest:  

Millions of people across the world come to Pinterest to find new ideas every day. It’s where they get inspiration, dream about new possibilities and plan for what matters most. Our mission is to help those people find their inspiration and create a life they love. In your role, you’ll be challenged to take on work that upholds this mission and pushes Pinterest forward. You’ll grow as a person and leader in your field, all the while helping Pinners make their lives better in the positive corner of the internet.

We are seeking a senior software engineer to build and boost Pinterest’s machine learning training and serving platforms and infrastructure. The candidate will work with different teams to design, build and improve our ML systems, including the model training computation platform, serving systems and model deployment systems.

What you'll do:

  • Design and build solutions to make the model training, serving and deployment process more efficient, more reliable, and less error-prone by human mistakes.
  • Design and build long term solutions to boost the model iteration velocity for machine learning engineers and data scientists.
  • Work extensively with ML engineers across Pinterest to understand their requirements, pain points, and build generalized solutions. Also work with partner teams to drive projects requiring cross-team coordination. 
  • Provide technical guidance and coaching to other junior engineers in the team.

What we're looking for:

  • Hands-on experience developing large-scale machine learning models in production, or experience working on the systems supporting onboarding large-scale machine learning models.
  • Ability to drive cross-team projects; Ability to understand our internal customers (ML practitioners), their common usage patterns and pain points.
  • Flexibility to work across different areas: tool building, model optimization, infrastructure optimization, large scale data processing pipelines, etc.
  • 5+ years of professional experience in software engineering.
  • Fluency in Python and either Java or Scala (Fluency in C++ for the MLS role).
  • Past tech lead experience is preferred, but not required. (Not necessary for the MLS role).

#LI-GB2

Our Commitment to Diversity:

At Pinterest, our mission is to bring everyone the inspiration to create a life they love—and that includes our employees. We’re taking on the most exciting challenges of our working lives, and we succeed with a team that represents an inclusive and diverse set of identities and backgrounds.

Engineering Manager, Ads Engagement M...
San Francisco, CA, US; Palo Alto, CA, US

About Pinterest:  

Millions of people across the world come to Pinterest to find new ideas every day. It’s where they get inspiration, dream about new possibilities and plan for what matters most. Our mission is to help those people find their inspiration and create a life they love. In your role, you’ll be challenged to take on work that upholds this mission and pushes Pinterest forward. You’ll grow as a person and leader in your field, all the while helping Pinners make their lives better in the positive corner of the internet.

Pinterest is one of the fastest growing online ad platforms, and our success depends on mining rich user interest data that helps us connect users with highly relevant advertisers/products. We’re looking for an Engineering Manager with experience in machine learning, data mining, and information retrieval to lead a team that develops new data-driven techniques to show the most engaging and relevant promoted content to the users. You’ll be leading a world-class ML team that is growing quickly and laying the foundation for Pinterest’s business success.

What you’ll do:

  • Manage and grow the engineering team, providing technical vision and long-term roadmap
  • Design features and build large-scale machine learning models to improve ads engagement prediction
  • Effectively collaborate and partner with several cross functional teams to build the next generation of ads engagement models
  • Mentor and grow ML engineers to allow them to become experts in modeling/engagement prediction 

What we’re looking for:

  • Degree in Computer Science, Statistics or related field
  • Industry experience building production machine learning systems at scale, data mining, search, recommendations, and/or natural language processing
  • 1+ years of experience leading projects/ teams either as TL/ TLM/ EM
  • Cross-functional collaborator and strong communicator
  • Experience with ads domain is a big plus

#LI-SM4

Our Commitment to Diversity:

At Pinterest, our mission is to bring everyone the inspiration to create a life they love—and that includes our employees. We’re taking on the most exciting challenges of our working lives, and we succeed with a team that represents an inclusive and diverse set of identities and backgrounds.

Engineering Manager, Ads Marketplace
San Francisco, CA, US; Palo Alto, CA, US; Seattle, WA, US

About Pinterest:  

Millions of people across the world come to Pinterest to find new ideas every day. It’s where they get inspiration, dream about new possibilities and plan for what matters most. Our mission is to help those people find their inspiration and create a life they love. In your role, you’ll be challenged to take on work that upholds this mission and pushes Pinterest forward. You’ll grow as a person and leader in your field, all the while helping Pinners make their lives better in the positive corner of the internet.

Within the Ads Quality team, we try to connect the dots between the aspirations of Pinners and the products offered by our partners. In this role, you will lead a team of engineers that is responsible for monetizing our Shopping and Creator surfaces. Using your strong analytical skill sets, a thorough understanding of auction mechanisms, and experience in managing an engineering team, you will advance the state of the art in Marketplace design and yield management.

What you’ll do:

  • Manage a team of engineers with a background in ML, backend development, economics, and data science to:
    • Monetize new surfaces effectively and responsibly 
    • Interface with our Product and Organic teams to understand requirements and build solutions that cater to our advertisers and users
    • Build models to enable scalable solutions for ad allocation, eligibility and pricing on the new surfaces
    • Hold a high standard for engineering excellence by building robust and future proof systems with an appreciation for simplicity and elegance
    • Identify gaps and opportunities as we expand and execute on closing those gaps effectively and in a timely manner
  • Work closely with Product on planning roadmap, set technical direction and deliver values
  • Coach and mentor team members and help them develop their career path and achieve their career goals

What we’re looking for:

  • Degree in Computer Science, Statistics, or related field
  • 2+ years of management experience
  • 5+ years of relevant experience
  • Background in computational advertising, econometrics, shopping
  • Strong industry experience in machine learning
  • Experience with ads domain is a big plus
  • Cross-functional collaborator and strong communicator

#LI-SM4

Our Commitment to Diversity:

At Pinterest, our mission is to bring everyone the inspiration to create a life they love—and that includes our employees. We’re taking on the most exciting challenges of our working lives, and we succeed with a team that represents an inclusive and diverse set of identities and backgrounds.

Verified by
Security Software Engineer
Tech Lead, Big Data Platform
Software Engineer
Talent Brand Manager
Sourcer
Software Engineer
You may also like