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 100Email 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.
