Configuring Scripts via the Civis API

Create scripts in SQL, Python, R or whatever language you want. You can query, modify, import, and export data. You can use the Civis API to orchestrate a workflow; creating and launching other processes, creating reports, etc.

SQL scripts will run on the Redshift cluster specified by the remoteHostId. Container scripts run in whatever docker image you supply. Python scripts will run in our datascience-python image and R scripts will run in the datascience-r image.

User Context 

Who will the script execute as:

• runner - The resources available to the script are those of the script runner. This is the default.
• author - The resources available to the script are those of the last runner of the script backing the template. If you publish a script with the user context of author, template runners will be calling the script with any credentials you supply and use your API key.

Parameters

Parameters can be added to a script so that it can be run with arguments and possibly published as Templates.

Parameter Names

Parameter names must start and end with a number or English letter. Only numbers, letters, and underscores are allowed. Parameters may NOT be named PATH, HOME, HOSTNAME, PWD, CIVIS_API_KEY, DATABASE, RUNNER_USER, or RUNNER_USER_ID.

Parameter Types

Valid parameter types are the following: string, multi_line_string, integer, float, bool, file, database, credential_aws, credential_redshift, and credential_custom

Parameter Variables

Here are the ways to use parameters within the different script types. The variables available to the script vary based on the parameter type.

SQL Scripts

If the parameter is named "foo", the following variables are available by wrapping them in double curly braces, e.g., SELECT COUNT(*) FROM some_schema.some_tablename WHERE id = {{foo.literal}}. Note: the valid parameter type to be used through the API is the name listed but lowercased, unless noted by parentheses.

Python, R, and Container Scripts

For these script types, parameter names get turned into environment variables. All environment variables will always be strings, however their types will be validated and you will be able to be cast them to the parameter type. Environment variables are made available under both the name you enter, and the name in all uppercase letters.

The following environment variables are made available for a parameter "foo". Note: the valid parameter type to be used through the API is the name listed but lowercased unless noted by parentheses.

Fixed Parameters

This feature is currently only available via the API. If you would like to define a parameter that holds a predetermined value that is not editable by custom scripts users, you can use a fixed parameter. Fixed parameters are most useful for affixing credentials and databases to your script. The values for fixed parameters are placed in environment variables in the same way arguments are. To use a fixed parameter, set the value attribute to the value you would like to use.

The example code below shows how to create/replace fixed parameters on an existing Python script. Note that when you use a database parameter, json.dumps() is required for the value.

import civis
client = civis.APIClient()

params = []
params.append({'name': 'NUM_DOGS', 'type': 'integer'})  # a normal integer cred
params.append({'name': 'PRIVATE_CUSTOM_CRED', 'type': 'credential_custom', 'value': my_secret_custom_cred_id})  # a fixed custom credential
params.append({'name': 'PRIVATE_POSTGIS_DB', 'type': 'database', 'value': json.dumps({'database': my_secret_database_id, 'credential': my_secret_cred_id})})
# now params is [
#    {'name': 'NUM_DOGS', 'type': 'integer'},
#    {'name': 'PRIVATE_CUSTOM_CRED', 'type': 'credential_custom', 'value': 6142},
#    {'name': 'PRIVATE_POSTGIS_DB', 'type': 'database', 'value': '{"database": 989, "credential": 1504}'}
# ]

client.scripts.patch_python3(job_id, params=params)

Note: Fixed files must be readable by the author of a template.

Custom Drop-Down Parameters

These are parameters that appear in the UI as dropdown lists of pre-selected choices. For example, you could create a custom drop-down parameter for 'Month' and have it appear as a list of the twelve months. Right now these are also only configurable via the API. 

The example below shows how to create them: 

import civis
client = civis.APIClient()

client.scripts.patch_python3(job_id, params=[
  {
    "name": "month",
    "required": False,
    "type": "integer",
    "allowedValues": [
      { "label": "January", "value": "1" },
      { "label": "February", "value": "2" },
      { "label": "March", "value": "3" },
      { "label": "April", "value": "4" },
      { "label": "May", "value": "5" },
      { "label": "June", "value": "6" },
      { "label": "July", "value": "7" },
      { "label": "August", "value": "8" },
      { "label": "September", "value": "9" },
      { "label": "October", "value": "10" },
      { "label": "November", "value": "11" },
      { "label": "December", "value": "12" }
    ]
  }
])

In the Platform UI, the parameter appears as follows: 

Required Resource Parameters

The resources allocated to a script can be set through parameters named REQUIRED_CPU, REQUIRED_MEMORY or REQUIRED_DISK_SPACE. If any of these parameters are present and arguments are supplied, the given values will be used to allocate resources. Otherwise, resources will be set based on the values in the script's requiredResources attribute. Note that parameters must be of the correct types (Integer for REQUIRED_CPU and REQUIRED_MEMORY and Float for REQUIRED_DISK_SPACE) in order to have an effect.

Arguments

Arguments can be added to any script that has parameters, or any custom script whose template has parameters. Valid arguments for each of the parameter types are listed below.

String - Any string value. Any "numbers" used will just be treated as the string representation of that number.

Multi-line String - Any string value. Presents the argument for the parameter as a multi-line text box.

Integer - Any integer value. Float values are not allowed.

Float - Any float value.

Boolean - Either true or false.

Redshift, Custom, or AWS Credential - Any integer value. Use the ID of the credential in Civis Platform, which can be found via the Credentials endpoint.

Database - A dictionary containing the keys 'database' and 'credential'. The values for these keys are the id of the database and the id of the database credential repspectively.

File - Any integer value. Use the id of the file in the Civis platform. Must be readable by the runner of the job. The file must not be expired when adding as an argument and the job will fail if the file is expired at runtime.

Default Values

When creating a parameter, you can specify a default value for the parameter. This is the value for the argument that will be used if no argument is supplied. Defaults cannot be set on required parameters since by definition, an argument must be supplied for them.

If a parameter is 1) optional, 2) has no default, and 3) no argument is supplied for it, the following will happen:

For SQL Scripts, an argument will still be set since valid SQL must be produced. Strings will be the empty string. Integers and Floats will be 0. Booleans will be false. Credential's values (.id, .username, .password, .access_key_id, and .secret_access_key) will be the empty string.

For Python, R, and Container Scripts, there will be no environment variable created.

Templates and Custom Scripts

To share your script so that others may use it, publish your script as a Template. Others may then reference your template and plug in their own arguments for your parameters by creating a Custom Script.