Introduction to Consumption APIs

Consumption APIs offer a powerful and flexible way to create custom API endpoints, allowing your applications to access and retrieve data from your OLAP database. These APIs act as the final layer in your Moose application, dynamically generating and executing SQL queries based on parameters received from incoming requests from your data consumers.

Core Concepts

File and Folder Conventions

Consumption APIs are defined as individual .ts.py files within the /apis folder of your Moose application. These files are automatically mapped to API endpoints based on their filenames.

Consumption API Definition

Each API file contains a route handler function that processes requests to its corresponding endpoint. This function is responsible for:

1. Parsing query parameters from the incoming request
2. Dynamically constructing SQL queries based on these parameters
3. Executing the queries against your database
4. Processing and formatting the results before sending the response

import { ConsumptionUtil } from "@514labs/moose-lib";
 
interface QueryParams {
limit: string;
minDailyActiveUsers: string;
}
 
export default async function handle(
{ limit = "10", minDailyActiveUsers = "0" }: QueryParams,
{ client, sql }: ConsumptionUtil,
) {
return client.query(
sql`SELECT 
        date,
        dailyActiveUsers
    FROM DailyActiveUsers
    WHERE dailyActiveUsers >= ${parseInt(minDailyActiveUsers)}
    LIMIT ${parseInt(limit)}`,
);
}
 

def run(client, params):
    minDailyActiveUsers = int(params.get('minDailyActiveUsers', [0])[0])
    limit = int(params.get('limit', [10])[0])
 
    return client.query(
        '''SELECT
            date,
            uniqMerge(dailyActiveUsers) as dailyActiveUsers
        FROM DailyActiveUsers
        GROUP BY date
        HAVING dailyActiveUsers >= {minDailyActiveUsers}
        ORDER BY date
        LIMIT {limit}''',
        {
            "minDailyActiveUsers": minDailyActiveUsers,
            "limit": limit
        }
    )
 

Query Parameters and Dynamic Data Retrieval

Query parameters play a crucial role in Consumption APIs, allowing for dynamic and flexible data retrieval. When defining your route handler function, you can specify the query parameters it accepts. These parameters are then passed to the function as arguments, mapped directly from the URL query parameters of the incoming HTTP GET request.

Orchestration

At the heart of Moose's Consumption APIs is an HTTP server that manages incoming GET requests. When a request is made to the /consumption/<endpoint-name> path, Moose automatically routes it to the corresponding handler file in your project's /apis directory, and executes the function defined within that file.