Magpie offers both variables and secrets to substitute values into commands and scripts when they are executed.

Variables vs. Secrets

Variables and Secrets both offer a way to insert values into a script at runtime, but have differences that are summarized in the table below:

Setting and Using Variables

In the first example, we set a variable that is used as a table name. The table is called property_use_codes.

set table_var = 'property_use_codes'

The variable can then be called in any interpreter with the general substitution syntax of $#{variable:table_var} or using the variable shorthand notation, which consists of the variable name preceded by the $# prefix.

The variable could be used to create a table by substituting the key value string using the full substitution syntax:

create table {
  "name": "$#{variable:table_var}"
}

Or the exact same command can be run with the variable shorthand notation:

create table {
  "name": "$#{table_var}"
}

Next, you can substitute variables in a show statement with the variable:

show 8 from $#{table_var}

Which results in:

Other String Variables

When declaring string variables, different types of quotes can be used depending on your use case. For instance, if you need a multiline variable, you can declare your variable with """...""" quotes. Or, if your variable contains double quotes, you may contain the variable with a single quote.

Forced Type

By default, Magpie infers the type of the variable based on the content. However, variable types can be forced by adding as <variable type>. For example:

set date_cutoff = '2018-09-23' as date

Listing Variables

In order to see what variables are already set, Magpie allows users to list variables. This will show the name, type, and value of each variable in the Magpie context.

list variables

Which results in:

SQL Parameter Substitution

There is a second way that variables can be used in Magpie. The usage is determined by the variables prefix. The variable prefix $ can be used to substitute SQL parameters as opposed the variable prefix $#which performs a text substitution.

Say some variable is set date_cutoff = '2018-09-23'. One might use a SQL parameter substitution rather than a text substitution to run the query:

%sql
SELECT *
FROM transactions
WHERE timestamp >= $date_cutoff

Note that in this case, we did not force the type because Magpie’s SQL interpreter is able to cast the string value in the background. Using a $# prefix, in contrast, requires enclosing the variable in quotes, i.e., ... WHERE timestamp >= '$#date_cutoff'.

Setting Variables in Scripting Interpreters

Magpie variables can also be set within Python and Scala scripts. This can be useful when running Magpie job flows. Variables are set in the Magpie context and are associated with the session in which the variable is set. For example, let’s say that we want to substitute a file path to use later when saving a table:

%python
mc.variables.set('staging_path', '/staging/2dyNWv/')
mc.variables.get('staging_path') # returns "/staging/2dyNWv/"

In a separate task, we can then use the variable, staging_path, to set a path in a command. For example:

create table transactions from data source my_data_source
  at path '$#{variable:staging_path}';

Creating and Using Secrets

In this example, we demonstrate creating two Secrets to store the credentials to create a Snowflake Data Source.

First, an Organization Admin can create the Secrets. It is important to note that the name of your secret must be unique across your Organization.

Store the username using the shorthand syntax for creating secrets:

create secret snowflake_user with value "magpie";

Next store the password as a secret. Since we are including a description, it is necessary to use the longer-form JSON-Spec syntax:

create secret {
  "name": "snowflake_password",
  "description": "The Snowflake system account password for magpie",
  "value": "PL7cvi6ImTfGH22vUW9AM8oC"
};

Next, the Organization Admin can grant read access to users who have the developer role:

grant read on secret api_key to role developer;

The secret can now be substituted into any interpreter with the general substitution syntax of $#{secret:<secret_name>}. Below is a create data source example that uses the username and password secrets set above:

create data source {
  "name": "sample_snowflake_source",
  "sourceType": "snowflake",
  "hostname": "https://example.us-east-1.snowflakecomputing.com",
  
  "user": "$#{secret:snowflake_user}",
  "password": "$#{secret:snowflake_password}",
  
  "database": "ANALYTICS",
  "defaultSchema": "PUBLIC"
};

Sharing Secrets

When a secret is created, the Organization Admin that created the secret is granted read and write access to that secret. All Organization Admins are able to write a secret, but must be explicitly granted read permissions in order to use the value contained within a secret.

After a secret is created, read and write permissions can be delegated to other users or roles via the grant command. For example, to grant read access to a secret to all members assigned to a role called “analyst”, use these Magpie commands:

%magpie

create secret "my_secret" with value "my_secret_value";
grant read on secret my_secret to role analysts;

Listing Secrets

All Secrets in your organization can be enumerated by all users of your organization, but they cannot see more than the Secret’s name unless the user is an Organization Admin or Read/Write permissions have been granted to the user or a role attached to a user. Therefore, the result of the list secrets command does not imply the ability to read or write the value of a Secret.

list secrets;

Which results in:

Reading Secrets in Scripting Interpreters

Magpie secrets can also be set within Python and Scala scripts. This can be useful when running Magpie job flows. Secrets are defined in advance using the Magpie DSL and then can be read via the Magpie Context, provided that the user running the script has permissions to read the secret.

%magpie

create secret api_key with value "my_secret_key";

%python

mc.secrets.get('api_key') # returns "my_secret_key"

A Note on Special Characters in Variables and Secrets

When Magpie variables and secrets are substituted into a command, a literal substitution is performed. As a result, when substituting special characters into a string, they may need to be escaped twice. For example:

%magpie

set v = "\\\\"; // Sets the variable v to `\\`
print "$#v"; // Evaluates to """print "\\";"""
// Output: """\"""

The need to double escape special characters can be reduced by using “““triple quotes””” when setting a variable.

%magpie

set v = """\\""";
print "$#v"; // Evaluates to """print "\\";"""
// Output: """\"""

However, they cannot be used when using a variable, as variables are not replaced inside of triple quotes:

%magpie

set v = """\\""";
print """$#v""";
// Output: "$#v"