Run Now with Custom Parameters

Steven Johnson -

A little under a year ago we released the ability to run a Fleet via webhook and send custom parameters. This feature has allowed our users to scale up their use cases and spend more time providing ROI to their stakeholders.

The biggest feedback that we received on the feature was to be able to provide parameters for our native low-code Blueprints at runtime. That feature was not available until today. With Shipyard's new Trigger Fleet Runs API endpoint, you can now take a single Fleet and send a request with JSON that can override any environment variable of your choosing including Blueprint Variables.

To show off the new API endpoint, I want to use a popular use case as a demo. In this blog post, I will use our new Trigger Fleet Runs API endpoint to start a Fleet that will download a query from Snowflake as a CSV and email it to a stakeholder.

If you'd rather watch this walk-through versus read, check out the video below:

Building a Starting Fleet

To start the process, we will need to create a Fleet as a template where we can send parameters at runtime. Our Fleet will contain two Vessels. One Vessel is built using the Snowflake Blueprint to Store Query Results as CSV. The second Vessel is built using the Email Blueprint to Send a Message with a File.

For the inputs, I am including my authentication information in the Vessels since I do not plan to change that information at run time. However, the information about the query and email will be changed at runtime, so I am including dummy data for those fields. You can see the full template Fleet setup that we are starting with below. 

name: Template Fleet for API
_id: f6345faf-6340-46f3-bea9-6cac07d2eafa
vessels:
    Download Snowflake Query:
        _id: 7cefdb94-23cc-4731-9567-9e45267fbb9c
        source:
            blueprint: Snowflake - Store Query Results as CSV
            inputs:
                SNOWFLAKE_ACCOUNT: '#{SNOWFLAKE_DEMO.SNOWFLAKE_ACCOUNT}'
                SNOWFLAKE_DATABASE: SNOWFLAKE_SAMPLE_DATA
                SNOWFLAKE_DESTINATION_FILE_NAME: template.csv
                SNOWFLAKE_DESTINATION_FOLDER_NAME: null
                SNOWFLAKE_FILE_HEADER: true
                SNOWFLAKE_PASSWORD: SHIPYARD_HIDDEN
                SNOWFLAKE_QUERY: fake query
                SNOWFLAKE_SCHEMA: PUBLIC
                SNOWFLAKE_USERNAME: '#{SNOWFLAKE_DEMO.SNOWFLAKE_USERNAME}'
                SNOWFLAKE_WAREHOUSE: TEST_WH
            type: BLUEPRINT
        guardrails:
            retry_count: 1
            retry_wait: 0s
            runtime_cutoff: 4h0m0s
            exclude_exit_code_ranges:
                - 200-206
        notifications:
            emails:
            after_error: true
            after_on_demand: false
    Email Snowflake Data:
        _id: 2a569aa3-366a-4edd-8a53-23e73e2941c0
        source:
            blueprint: Email - Send Message with File
            inputs:
                EMAIL_BCC: null
                EMAIL_CC: null
                EMAIL_INCLUDE_SHIPYARD_FOOTER: true
                EMAIL_MESSAGE: Attached
                EMAIL_PASSWORD: SHIPYARD_HIDDEN
                EMAIL_SEND_METHOD: tls
                EMAIL_SENDER_ADDRESS: ${EMAIL_USERNAME}
                EMAIL_SENDER_NAME: null
                EMAIL_SMTP_HOST: '#{DEMO_EMAIL_CREDS.EMAIL_SMTP_HOST}'
                EMAIL_SMTP_PORT: '#{DEMO_EMAIL_CREDS.EMAIL_SMTP_PORT}'
                EMAIL_SOURCE_FILE_NAME: template.csv
                EMAIL_SOURCE_FILE_NAME_MATCH_TYPE: exact_match
                EMAIL_SOURCE_FOLDER_NAME: null
                EMAIL_SUBJECT: Snowflake Query
                EMAIL_TO: template@shipyardapp.com
                EMAIL_USERNAME: '#{DEMO_EMAIL_CREDS.EMAIL_USERNAME}'
            type: BLUEPRINT
        guardrails:
            retry_count: 1
            retry_wait: 0s
            runtime_cutoff: 4h0m0s
        notifications:
            emails:
            after_error: true
            after_on_demand: false
connections:
    Download Snowflake Query:
        Email Snowflake Data: SUCCESS
notifications:
    emails:
        - steven.johnson@shipyardapp.com
    after_error: true
    after_on_demand: false

Choosing Fields to Change at Runtime

In the YAML above, you can see the two Vessels named Download Snowflake Query and Email Snowflake Data along with the variables for each Vessel below them. Using the new API endpoint, we are allowed to change any of those variables at runtime.

The variables that we are going to change in this demo are:

Download Snowflake Query:
  • SNOWFLAKE_DESTINATION_FILE_NAME

large_manu_4.csv

  • SNOWFLAKE_QUERY
select * 
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1000"."PART"
where P_MFGR = 'Manufacturer#4' and P_SIZE = 50
limit 100
Email Snowflake Data
  • EMAIL_SOURCE_FILE_NAME

large_manu_4.csv

  • EMAIL_TO

steven.johnson@shipyardapp.com

Run the Fleet with the API

Using the example code in the Shipyard documentation, we can now run our template Fleet from earlier with the custom variables from above. The custom variables will need to be input into a dictionary:

json_data = {
  "vessel_overrides": [
    {
      "name": "Download Snowflake Query",
      "environment_variable_overrides": {
        "SNOWFLAKE_DESTINATION_FILE_NAME": "large_manu_4.csv",
        "SNOWFLAKE_QUERY": '''
        select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1000"."PART" where P_MFGR = 'Manufacturer#4' and P_SIZE = 50 limit 100
        '''
      }
    },
    {
      "name": "Email Snowflake Data",
      "environment_variable_overrides": {
        "EMAIL_SOURCE_FILE_NAME": "large_manu_4.csv",
        "EMAIL_TO": "steven.johnson@shipyardapp.com"
      }
    }
  ]
}

The json_data variable will now be sent to the Shipyard API. This will start the fleet with the custom variables replacing the template variables from above. The code below will accomplish that. All you will need to do is input your Shipyard API key, organization, project, and Fleet IDs:

import requests

headers = {
    'Accept': 'application/json',
    'X-Shipyard-API-Key': 'YOUR_API_KEY',
    'Content-Type': 'application/json',
}



response = requests.post(
    'https://api.app.shipyardapp.com/orgs/<YOUR_ORG_ID>/projects/<YOUR_PROJECT_ID/fleets/<YOUR_FLEET_ID>/fleetruns',
    headers=headers,
    json=json_data,
)

The Fleet runs with the variables that are provided, and the data is emailed over. With this setup, we can continue to update the variables from above to run the same Fleet but with different variables. Say goodbye to having to duplicate a Fleet over and over again to send different reports to different stakeholders.