Public SQL Endpoints in Rockset

October 21, 2022

,

Introduction

Making use of real-time data for analytics is a deeply collaborative project. We’ve helped data engineers, data architects, engineering leaders, ML teams, and product managers connect the dots between various systems to deliver on Rockset’s promise of fast queries on fresh data. Not only are we collaborating with customers on analytics projects, we use our own product daily and collaborate across teams internally. While it’s easy to invite coworkers or colleagues to an organization within Rockset, we wanted a dead-simple and delightful method for sharing queries and their results with anyone, and we’re launching a beta feature to accomplish exactly that.

Public Query Lambdas

Rockset’s Query Lambdas are named, parameterized SQL queries that can be executed from a dedicated REST endpoint. They allow users to save SQL queries as separate resources for use in production applications. Query Lambdas are one of our more popular features - with an API key they can be executed from a Curl command, the command line, Node, or Python, making them an ideal way to integrate queries into software.

We’ve seen the usage of Query Lambdas evolve in interesting and unpredictable ways. Because users were leveraging Query Lambdas to collaborate and iterate on queries, we added versioning to make it easy to build and test changes. Because sending code snippets is so common, we added “copy internal link” to allow members of the same organization to easily pass SQL around. And today, we’re making sharing even easier by launching something called a Public Query Lambda (PQL).

What Can They Do?

The simplest description of a Public Query Lambda is an endpoint that can deliver query results to anyone, directly from a browser, with no API key and no required membership to a Rockset organization. Public Query Lambdas are used to execute queries on live data and return the results in a formatted JSON object. We’ve published one publicly, here, using one of our sample datasets. Check it out!

results

To create your own Public Query Lambda, save a query as a Query Lambda and then navigate to the Query Lambda tab using the left nav. Click on your Query Lambda and select "Make Public".

make_public

From here, you can copy a link to the endpoint, which, whenever hit, will execute the query and return results.

public_ql

Why did we build this? Well, we have a few ideas for how Public Query Lambdas might be used.

Sharing query results and query performance within an organization

We often see Rockset usage expand across organizations within a company. However, new teams considering the product frequently want to see it in action before poking around in the Rockset console. Sending a Public Query Lambda is an incredibly quick method for sharing performance characteristics and demonstrating the ability to perform SQL JOIN operations on deeply-nested JSON - on real data - in a way that stakeholders are likely to understand quickly.

Limited 3rd-party access

Another interesting feature of Public Query Lambdas is that 3rd parties can leverage them to provide access to data from a particular collection and allow users to do whatever they like with it. So far, this blog has focused on the visual representation of query results from within a browser, however, you can also execute a PQL from the command line or within an application. We see this potentially being useful for collaborations between public and private institutions (e.g. the Centers for Disease Control and Prevention sharing data with the broader analytics community without having to upload CSVs or update Google Sheets files.)

Limitations

In order to prevent misuse or accidental use of Public Query Lambas, we’re limiting its availability to Rockset admins for now. Additionally, we’re limiting Public Query Lambdas to 5 queries per second to avoid excessive compute consumption. Our implementation does not yet support parameterized queries.

Moving Forward

If Public Query Lambdas prove popular, we have a number of improvements we’d like to implement, including support for parameters, granular role-based access controls, social sharing, and more. Additionally, we’re considering allowing users of Public Query Lambdas to modify their SQL.

Our hope, however, is to once again be surprised at our users’ novel and creative application of this feature. That’s where you come in - do Public Query Lambdas sound useful to you? Is there something you’d like to see us extend or change? Love this feature and already sending Public Query Lambdas to your teammates? We’d love to hear from you. Get in touch at dwyer@rockset.com. Brand new to Rockset? Get $300 in free trial credits by signing up here.