Skip to content

Snowflake Connect: AI on January 27

Unlock the full potential of data and AI with Snowflake’s latest innovations.

register now
Snowflake for DevelopersGuidesBuild a Custom API in Java on AWS

Build a Custom API in Java on AWS

Transformation
Brad Culberson
fork repo

Overview

Many builders want to share some of the data stored in Snowflake over an http API. Modern mobile and web applications often want to retrieve that data through http APIs. This tutorial will go through how to build, deploy, and host a custom API Powered by Snowflake.

This API consists of reporting endpoints from data stored in Snowflake. Serverless Framework was used to build and deploy the application for simplicity of operation and ease of scaling.

After completing this guide, you will have built, tested, and deployed a custom API built with Java, Serverless Framework, and AWS Lambda/API Gateway.

The dataset is the same as used in the Building a Data Application guide.

Prerequisites

  • Privileges necessary to create a user, database, and warehouse in Snowflake
  • Privileges necessary to create an API Gateway and Lambda in Amazon Web Services
  • Ability to install and run software on your computer
  • Basic experience using git and editing yml
  • Intermediate knowledge of Java

What You’ll Learn

  • How to configure and build a custom API Powered by Snowflake
  • How to deploy a custom API on AWS serverless technologies
  • How to test and monitor the API

What You’ll Need

What You’ll Build

  • API Powered by Snowflake

Downloading the Code

The code used in this guide is hosted in github. You can download the code as a ZIP from GitHub or use the following git command to clone the repository.

git clone https://github.com/Snowflake-Labs/sfguide-snowflake-java-api.git

After downloading you will have a folder sfguide-snowflake-java containing all the code needed for the API. Open the folder in VSCode to review the project.

The Handler class contains the handleRequest method which has all the entrypoints for the API endpoints. The handleRequest method executes the correct sql and builds the response for the endpoints. The monthlyPreparedStatement method builds the statement needed to execute on Snowflake to provide data needed for the endpoint which pulls the trips completed aggregated by month. Review the code and the SQL needed to retrieve the data from Snowflake and serialize it to JSON for the response. This endpoint also takes 2 optional query string parameters start_range and end_range.

public ApiGatewayResponse handleRequest(Map<String, Object> input, Context context) {		
    String path = (String) input.get("path");
    Map<String, String> queryStringParameters = (Map<String, String>) input.get("queryStringParameters");		
    try {			
        PreparedStatement stat;
        switch(path) {
            case "/trips/monthly":
                Connection conn = Connector.connect();
                stat = monthlyPreparedStatement(queryStringParameters, conn);
                break;
            case "/trips/day_of_week":
                conn = Connector.connect();
                stat = dayOfWeekPreparedStatement(queryStringParameters, conn);
                break;
            case "/trips/temperature":
                conn = Connector.connect();
                stat = temperaturePreparedStatement(queryStringParameters, conn);
                break;
            default:
                return handleDefault();
        }

        long start_time = System.nanoTime();
        ResultSet rs = stat.executeQuery();
        long time_ms = (System.nanoTime() - start_time) / 1000000;
        ArrayList<Object[]> results = new ArrayList<Object[]>();
        while (rs.next()) {
            results.add(new Object[]{rs.getObject(1), rs.getObject(2)});
        }

        return ApiGatewayResponse.builder()
                .setStatusCode(200)
                .setObjectBody(new Response(results, time_ms))
                .setHeaders(HEADERS)
                .build();		
    } catch (Exception e) {
        LOG.error(e);
        return ApiGatewayResponse.builder()
        .setStatusCode(500)
        .build();
    }
}

private PreparedStatement monthlyPreparedStatement(Map<String, String> queryStringParameters, Connection conn) throws Exception {
    PreparedStatement stat;
    if (queryStringParameters != null && queryStringParameters.get("start_range") != null && queryStringParameters.get("end_range") != null) {
        String sql = "select COUNT(*) as trip_count, MONTHNAME(starttime) as month from demo.trips where starttime between ? and ? group by MONTH(starttime), MONTHNAME(starttime) order by MONTH(starttime);";	
        stat = conn.prepareStatement(sql);
        stat.setString(1, queryStringParameters.get("start_range"));
        stat.setString(2, queryStringParameters.get("end_range"));
        return stat;
    }
    String sql = "select COUNT(*) as trip_count, MONTHNAME(starttime) as month from demo.trips group by MONTH(starttime), MONTHNAME(starttime) order by MONTH(starttime);";
    stat = conn.prepareStatement(sql);
    return stat;
}

You can also review the other endpoints in Handler.java to see how simple it is to host multiple endpoints.

Building the Application

This project uses Maven to manage dependencies and to perform the build. To build the project, run maven:

mvn package

The build artifact should be created under ./target/snowflake-java-api.jar which will be used later.

Configuring the Application

The Handler class requires environment variables for configuration. These environment variables will be set for the lambda in AWS by the Serverless Framework automatically.

Copy the serverless-template.yml to serverless.yml. Update the serverless.yml to have your Snowflake account in both places that have (SNOWFLAKE_ACCOUNT and SNOWFLAKE_PRIVATE_KEY). This Snowflake account must be the same one used for the Building a Data Application guide as we will be using the same database and user. If you haven't completed the first 4 steps of that guide, do so before continuing.

Modify the region in the serverless.yml (line 17) to the same region as your credentials.

aws configure get region

This project expects the private key to be stored in SSM. To upload the private key, run the following replacing with the same Snowflake account used in serverless.yml:

aws ssm put-parameter --name <ACCOUNT>.DATA_APPS_DEMO --type "String" --value "`cat ~/.ssh/snowflake_demo_key`"

Verify the private key was uploaded correctly:

aws ssm get-parameter --name <ACCOUNT>.DATA_APPS_DEMO

Configuring, Packaging, and Testing

Serverless login will open a browser to authenticate the serverless tool. When running serverless, it will ask you what org to add it to. You can choose the default or any org you have setup in serverless.com. You can also keep the original snowflake-java-api name for the application or give it a new name. When asked to deploy the application, choose No.

serverless login
serverless

After this is complete, Serverless Framework is configured for use.

Deploying the API

Now that the application and configuration is complete, you can deploy it to AWS by running the following command:

serverless deploy

After the completion of deployment, serverless info will give you the URI where your API is now hosted.

serverless info

You can now do the same tests you did locally on the now publicly available API. Replace the server uri with your API location.

curl "http://<DEPLOYMENT>.execute-api.<REGION>/dev/trips/monthly" | jq
curl "http://<DEPLOYMENT>.execute-api.<REGION>/dev/trips/day_of_week" | jq
curl "http://<DEPLOYMENT>.execute-api.<REGION>/dev/trips/temperature" | jq

To test the query string parameters you can use the following:

curl "http://<DEPLOYMENT>.execute-api.<REGION>/dev/trips/monthly?start_range=2013-06-01&end_range=2013-07-31" | jq

Your api is now available for use by your mobile and web applications.

Monitoring your API with Serverless Framework

To monitor your API you can login to the AWS console and view the metrics under Services, Lambda, Applications, Monitoring and your logs under Services, Lambda, Functions, snowflake-java-api-dev-api.

Cleanup

If you are done with this exercise you can remove all aws resources by having Serverless Framework cleanup.

serverless remove

Conclusion

You've successfully built, tested, and deployed a custom API on AWS Powered by Snowflake. The serverless stack from AWS is a great way to quickly and easily build a powerful API with little operational overhead. It's also very cost effective for most uses.

Right now this is a public API and is accessible to anyone on the internet. If you have a need to authenticate your users you should check out Amazon Cognito. You can also use custom authorizers in lambda or AWS IAM to restrict access.

To get more comfortable with this solution, implement new endpoints pointing to the sample dataset provided or other datasets.

We will be posting more guides in the future on building custom APIs, Powered by Snowflake, in other languages and other cloud providers. Please check back for more guides.

Code for this project is available at https://github.com/Snowflake-Labs/sfguide-snowflake-java-api.

What we've covered

  • How to build, configure and package a custom API Powered by Snowflake
  • How to test the API locally
  • How to deploy a custom API on AWS serverless technologies
  • How to test and monitor the API in AWS
Updated 2025-12-20

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances