Query Lambdas

Query Lambdas are named, parameterized SQL queries stored in Rockset that can be executed from a dedicated REST endpoint. Using Query Lambdas, you can save your SQL queries as separate resources in Rockset and manage them successfully through development and production.

Why Query Lambdas?

We recommend using Query Lambdas to build applications backed by Rockset as opposed to querying with raw SQL directly from application code.

📘

See our release blog for the full story on how Query Lambdas can increase developer velocity in your application development.

Version Control SQL Queries

Each Query Lambda maintains a version history and programmatic executions of that Lambda must provide a version. Any update to a Query Lambda automatically creates a new version. Versioning allows you to build and test changes without affecting production queries. Each Query Lambda - Version pair is immutable, so once specified in application code the underlying query will never change without the application source code itself changing.

Learn more about enforcing version control with Query Lambdas here.

Manage the SQL Development Lifecycle

Working with raw SQL is dangerous and tedious enough as-is, especially when embedding it into application code. Avoid SQL injection security risks and potential schema exposure by saving your queries and turning them into REST endpoints. Once a Query Lambda is created, a REST endpoint is automatically generated which you can then query directly with your query parameters as HTTP parameters. Collaborating on SQL queries is now as easy as sharing a link, while your application code remains unchanged.

Monitor Individual Query Metrics

Without custom implementation work application-side, there is no way to understand how a particular query is performing. Now that your SQL queries are saved as abstracted resources in Rockset, however, you can monitor and track their performance as they are executed in production. Rockset automatically maintains several statistics and performance metrics for each of your Query Lambdas.

Learn more about performance monitoring and errors with Query Lambdas here.

Share Query Executions Externally

By enabling public access to a Query Lambda, you can share the ability to both execute a query and view its results publicly, including with those not in your Rockset organization. This also enables you to programmatically execute queries from your application without an API key or other authentication. Anyone with the URL to your public Query Lambda will be able to execute it at any time and view its complete JSON response in real-time, including the SQL query results, query execution metrics, and Collections queried.

Creating Query Lambdas

Query Lambdas can be created and updated in the Query Lambdas tab of the Rockset Console or by using the Rockset API directly. Our official client libraries also natively support creating Query Lambdas. See library-specific documentation for relevant syntax.

Each Query Lambda is tied to a specific query text and parameter set. You can set default values for query parameters (making them optional during executions of your Query Lambda), or you can make them mandatory for each execution (failing to pass along will result in an error).

For example, let's say we wanted to write a query that pulls all the events over a variable amount of days. We can represent days as a parameter of type int. The query will look something like this:

SELECT
    *
FROM
    commons._events
WHERE 
    EXTRACT(DATE from _event_time) >= CURRENT_DATE - DAYS(:days)

This parameterized SQL query can be saved as a Query Lambda called dailyReportsLambda, and then execute it through a REST endpoint from anywhere (given an API key):

curl --request POST \
  --url https://$ROCKSET_SERVER/v1/orgs/self/ws/commons/lambdas/dailyReportsLambda/versions/{version} \
  -H 'Authorization: ApiKey ...' -H 'Content-Type: application/json'
  -d '{
    "parameters": [
      {
        "name": "days",
        "value": 1
      }
    ]
  }'

📘

Did you know you can parameterize field names and collections? Check out our Special Functions for implementation instructions.

Below is an example of how you can parameterize the above Query Lambda using our special functions TABLE and FIELD:

SQL

SELECT 
    * 
FROM 
    TABLE(['commons', '_events']) 
WHERE 
    EXTRACT(DATE from FIELD('_event_time')) >= CURRENT_DATE - DAYS(:days)

Using Query Lambdas with Workspaces

Like collections, each Query Lambda is associated with a particular Workspace (by default, they will be created in the commons workspace). You can set this workspace at the time of creation.

🚧

Query Lambdas in one workspace are not limited to querying collections in that same workspace.

Any Query Lambda in any workspace can query any number of collections in any number of workspaces within your Rockset organization.

Executing Query Lambdas

Executing a Query Lambda is as easy as hitting its dedicated REST endpoint, which is automatically generated on creation (and each update). Each Query Lambda will generate a REST endpoint with the following format:

https://$ROCKSET_SERVER/v1/orgs/self/ws/{workspace}/lambdas/{queryLambda}/versions/{version}

You can also find this URL by navigating to the Query Lambdas tab of the Rockset Console and then selecting your newly created Query Lambda. There, you can find both the dedicated REST endpoint as well as instructions on how to execute the Query Lambda.

Execute the Query Lambda by hitting its dedicated REST endpoint with a POST request directly, passing in any query parameters as REST parameters as necessary.

Publicly Sharing Query Lambdas

By default Query Lambdas can only be viewed by users in your Rockset organization, but you have the option to allow users to publicly share Query Lambdas by generating a unique link that executes the query using an HTTP POST endpoint.

To make a Query Lambda publicly accessible, navigate to the Query Lambdas tab of the Rockset Console and select the appropriate Query Lambda. Click the “Make Public” button located in the upper right corner, which will allow you to enable public access. Once enabled, you will be given a unique Public Access Link and a unique Public Access Endpoint that can be shared with others outside of your Rockset organization and without an API Key. You can view these links anytime by returning to this page and clicking "Manage Public Access".

Managing Public Access

By default, Query Lambdas can only be viewed and executed by authenticated users in your Rockset account. You can enable public access to the Query Lambda to generate a unique URL which allows anyone with the link to execute your Query Lambda and view its response without any authentication.

To enable public access, navigate to the Query Lambdas tab of the Rockset Console and select the Query Lambda you would like to share publicly. Click the "Make Public" button located in the top-right corner, and then toggle "Enable Public Access" in the pop-up modal.

Once enabled, you will be given two unique URLs which can be shared publicly for accessing this Query Lambda:

1. The Public Access Link contains a simple UI which allows others to execute the Query Lambda and view its formatted response inside their browser. This is most useful for human access by other team members or the public.
2. The Public Access Endpoint is an authenticated REST API endpoint which will trigger the execution of your Query Lambda and return its response via an HTTP POST request. This is most useful for programmatic access by applications.

Scheduled Query Lambdas

You can schedule your query lambdas for automatic execution and configure certain actions to be taken based on the results of the executed query lambda.

Navigate to the details page for the query lambda that you are interested in scheduling and then visit the Scheduled Queries tab. You will be able to see all of the scheduled executions that you have set up for any versions of this query lambda.

Click "Create a New Schedule" to create a new scheduled query lambda. First, choose the version of this query lambda that is to be executed. You will then need to specify the execution schedule. This should be provided in either UNIX cron format or Quartz cron format (see note below). You will also need to provide a Rockset API key that has the necessary permissions required to execute your query lambda.

🚧

UNIX vs Quartz cron format

The Rockset API Create Scheduled Lambda endpoint will continue to accept either the UNIX or Quartz cron format for the schedule, but unix crons will always be converted to an equivalent quartz cron by our servers.

Configuring a webhook is optional. Specify the URL of your webhook and provide a value to be used for the authorization header if needed. The webhook payload must be in JSON format and will be sent to your webhook as the body of the request (this can also be left out). There are special character sequences which you can use within this payload:

• "{{QUERY_RESULTS}}" will be replaced by the results of the query execution.
• "{{QUERY_ID}}" will be replaced by the ID of the of the query that was triggered.

🚧

A request will be made to the webhook only if there is at least one doc in the results of the executed query lambda.

Example

If you have an unauthenticated webhook set up at the URL https://xxxxxx.execute-api.us-west-2.amazonaws.com/dev/webhookand you would like to schedule a Query Lambda to execute every day and send the query ID and results to this webhook, you would use a configuration like this:

After each scheduled execution of the Query Lambda, a POST request will be sent to that URL. In this case, the body of the request would look something like {"query_id": "asdfasdf-asdfasdf", "query_results": "[{\"field\": \"value\"}]"}.

📘

Check out this blog for 5 cool use cases for Scheduled Query Lambdas.

System Parameters

When the system runs your scheduled query lambdas, it will always add certain parameters to the execution requests. You can choose to use these parameters or ignore them. These parameters are tied to each individual scheduling of a QL. If you create a new schedule for a QL that has already been running, they will not share system parameter state.

The list of system parameters that are currently available is as follows:

• _last_scheduled_execution_time (int) - This corresponds to the last time that this scheduled query lambda began execution. Unix timestamp in milliseconds. Defaults to 0 for the first execution.
• _last_scheduled_completion_time (int) - This corresponds to the last time that this scheduled query lambda ended execution. Unix timestamp in milliseconds. Defaults to 0 for the first execution.

Limitations

There are certain restrictions on scheduled query lambdas:

• The minimum duration between each execution is 1 second. However, there may be up to a 10 second delay between the scheduled and actual execution times.
• If the last query triggered by a scheduled query lambda has not completed execution by the time the next execution would be triggered, another query will not be run untiil the previous query has completed.
• There is a size limit on the size of the query results that scheduled query lambdas can handle. Currently, that size limit is 1MB. It is recommended that you write your query in such a way that it minimizes the result size.
• Each organization is limited in the number of scheduled query lambdas that they can have at any one time. Contact Rockset Support if you are interested in increasing this limit.

There are also restrictions on webhooks:

• The webhook must accept POST requests. Other HTTP methods are currently not supported.
• Only authentication methods that make use of the Authorization header are supported.
• Custom headers and cookies are not supported.
• The content-type header of the request will always be application/json.