Private Results

Sundeck provides the ability to route query results directly to the client, ensuring no result data is sent through Sundeck. To accomplish this, Sundeck provides a Private Results Helper, which can be set up with a few simple steps.

Private Results Helper

The Sundeck private results helper leverages AWS API Gateway to bypass the use of Sundeck systems for results return. There are several ways to set this up:

Bash script (See below)
Cloud Formation Template (coming soon)
Terraform (coming soon)
Manually via AWS Console or CLI

Configuring Private Results Helper

Create a new API Gateway using below script
Copy the API Gateway URL https://${API_ID}.execute-api.${REGION}.amazonaws.com/prod
Set the API Gateway URL in the Sundeck UI on Account Settings page or via the sql command ALTER SUNDECK ACCOUNT SET PRIVATE_RESULTS_HELPER_URL = 'https_url';

Bash script to set up using AWS CLI

Click to expand

  #!/usr/bin/env bash

  # This script creates a Sundeck private results endpoint using AWS API Gateway, a relatively inexpensive zero-maintenance service.
  # Fill in your account id below. (this includes both account name and potentially region identifier).
  SNOWFLAKE_URL=${SNOWFLAKE_URL:-"https://<YOUR-ACCOUNT-ID>.snowflakecomputing.com"}

  REGION=${REGION:-"us-west-2"}
  ENDPOINT_NAME=${ENDPOINT_NAME:-"${USER}-private-results-endpoint-name"}

  setup() {
    API_ID=$(aws apigateway create-rest-api --name "$ENDPOINT_NAME" --region "$REGION" --endpoint-configuration types=REGIONAL --description 'Sundeck Results Helper' --output json | jq -r -e '.id')

    ROOT_RESOURCE_ID=$(aws apigateway get-resources --rest-api-id "$API_ID" --query "items[?path=='/'].id" --output text --region "$REGION")
    QUERIES_RESOURCE_ID=$(aws apigateway create-resource --rest-api-id "$API_ID" --parent-id "$ROOT_RESOURCE_ID" --path-part queries --region "$REGION" --output json | jq -r -e '.id')
    V1_RESOURCE_ID=$(aws apigateway create-resource --rest-api-id "$API_ID" --parent-id "$QUERIES_RESOURCE_ID" --path-part v1 --region "$REGION" --output json | jq -r -e '.id')
    QUERY_REQUEST_RESOURCE_ID=$(aws apigateway create-resource --rest-api-id "$API_ID" --parent-id "$V1_RESOURCE_ID" --path-part query-request --region "$REGION" --output json | jq -r -e '.id')

    aws apigateway put-method --rest-api-id "$API_ID" --resource-id "$QUERY_REQUEST_RESOURCE_ID" --http-method POST --authorization-type NONE --region "$REGION" || { fail "Failed to create POST method for /queries/v1/query-request"; exit 1; }
    aws apigateway put-integration --rest-api-id "$API_ID" --resource-id "$QUERY_REQUEST_RESOURCE_ID" --http-method POST --type HTTP --passthrough-behavior NEVER --integration-http-method POST --request-templates "$POST_REQUEST_TEMPLATE_STRING" --uri "${SNOWFLAKE_URL}/queries/v1/query-request" --region "$REGION" || { fail "Failed to create POST integration for /queries/v1/query-request"; exit 1; }
    aws apigateway put-method-response --rest-api-id "$API_ID" --resource-id "$QUERY_REQUEST_RESOURCE_ID" --http-method POST --status-code 200 --region "$REGION" || { fail "Failed to create POST method response for /queries/v1/query-request"; exit 1; }
    aws apigateway put-integration-response --rest-api-id "$API_ID" --resource-id "$QUERY_REQUEST_RESOURCE_ID" --http-method POST --status-code 200 --response-templates "$POST_RESPONSE_TEMPLATE_STRING" --region "$REGION" || { fail "Failed to create POST integration response for /queries/v1/query-request"; exit 1; }

    QUERIES_PLUS_RESOURCE_ID=$(aws apigateway create-resource --rest-api-id "$API_ID" --parent-id "$ROOT_RESOURCE_ID" --path-part '{queries+}' --region "$REGION" --output json | jq -r -e '.id')
    aws apigateway put-method --rest-api-id "$API_ID" --resource-id "$QUERIES_PLUS_RESOURCE_ID" --http-method GET --request-parameters "$GET_REQUEST_PARAMETERS" --authorization-type NONE --region "$REGION" || { fail "Failed to create GET method for /{queries+}"; exit 1; }
    aws apigateway put-integration --rest-api-id "$API_ID" --resource-id "$QUERIES_PLUS_RESOURCE_ID" --http-method GET --type HTTP --passthrough-behavior NEVER --integration-http-method GET --request-parameters "$GET_INTEGRATION_REQUEST_PARAMETERS" --request-templates "$GET_REQUEST_TEMPLATE_STRING" --uri "${SNOWFLAKE_URL}/{queries}" --region "$REGION" || { fail "Failed to create GET integration for /{queries+}"; exit 1; }
    aws apigateway put-method-response --rest-api-id "$API_ID" --resource-id "$QUERIES_PLUS_RESOURCE_ID" --http-method GET --status-code 200 --region "$REGION" || { fail "Failed to create GET method response for /{queries+}"; exit 1; }
    aws apigateway put-integration-response --rest-api-id "$API_ID" --resource-id "$QUERIES_PLUS_RESOURCE_ID" --http-method GET --status-code 200 --response-templates "$GET_RESPONSE_TEMPLATE_STRING" --region "$REGION" || { fail "Failed to create GET integration response for /{queries+}"; exit 1; }

    aws apigateway create-deployment --rest-api-id "$API_ID" --stage-name "prod" --region "$REGION" || { fail "Failed to deploy API"; exit 1; }
    echo "API Gateway URL: https://${API_ID}.execute-api.${REGION}.amazonaws.com/prod"
  }

  ########
  ## Construct Arguments for setup
  read -r -d '' post_request_template << 'EOF'
  #set($queryParams = ["clientStartTime", "requestId", "request_guid", "retryCounter"])

  #foreach($paramName in $queryParams)
    #set($result = "")
    #set($result = $input.params($paramName))
    #if($result != "")
      #set($context.requestOverride.querystring[$paramName]= $result)
    #end
  #end
  
  #set($headerParams = ["Authorization", "Accept", "User-Agent", "Content-Type"])
  #foreach($paramName in $headerParams)
    #set($result = "")
    #set($result = $input.params($paramName))
    #if($result != "")
      #set($context.requestOverride.header[$paramName] = $result)
    #end
  #end

  $input.json("$")
  EOF


  read -r -d '' post_response_template << 'EOF'
  #foreach($toStrip in ['rowset', 'rowsetBase64', 'chunks', 'chunkHeaders', 'encryptionMaterial', 'presignedUrls', 'stageInfo'])
    #set($removed = $input.path('$.data').remove($toStrip))
  #end
  $input.json('$')
  EOF

  read -r -d '' get_request_template << 'EOF'
  #set($result = "")
  #set($result = $input.params('auth'))
  #if($result != "")
    #set($context.requestOverride.header.Authorization = $result)
  #else
    #set($context.requestOverride.header.Authorization = $input.params('Authorization'))
  #end

  #set($headerParams = ["Accept", "User-Agent", "Content-Type", "x-sundeck-stripdata"])
  #foreach($paramName in $headerParams)
    #set($result = "")
    #set($result = $input.params($paramName))
    #if($result != "")
      #set($context.requestOverride.header[$paramName] = $result)
    #end
  #end
  EOF

  read -r -d '' get_response_template << 'EOF'
  #set($stripData = $input.params('x-sundeck-stripdata'))

  #if($stripData != "")
    #foreach($toStrip in ['rowset', 'rowsetBase64', 'chunks', 'chunkHeaders', 'encryptionMaterial', 'presignedUrls', 'stageInfo'])
      #set($removed = $input.path('$.data').remove($toStrip))
    #end
  #end
  $input.json('$')
  EOF
  
  # replace all " with \" and all new lines with \n
  POST_REQUEST_TEMPLATE_STRING=$(printf '{"application/json": "%s" }' "$(echo "${post_request_template}" | sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/\\n/g' -e 's/"/\\"/g')")
  POST_RESPONSE_TEMPLATE_STRING=$(printf '{"application/json": "%s" }' "$(echo "${post_response_template}" | sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/\\n/g' -e 's/"/\\"/g')")
  GET_REQUEST_TEMPLATE_STRING=$(printf '{"application/json": "%s" }' "$(echo "${get_request_template}" | sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/\\n/g' -e 's/"/\\"/g')")
  GET_RESPONSE_TEMPLATE_STRING=$(printf '{"application/json": "%s" }' "$(echo "${get_response_template}" | sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/\\n/g' -e 's/"/\\"/g')")
  
  GET_REQUEST_PARAMETERS="method.request.path.queries=false"
  GET_INTEGRATION_REQUEST_PARAMETERS="integration.request.path.queries=method.request.path.queries"

  setup