# Variables > For the complete documentation index, see [llms.txt](https://developers.thoughtspot.com/docs/llms.txt) Source: https://developers.thoughtspot.com/docs/variables # Variables Variables allow you to substitute values for specific properties of a metadata object and enable dynamic data propagation across Orgs in ThoughtSpot. Using variables, you can parameterize the properties of data connections, table references, and formulas per Org without changing the underlying data or creating duplicate objects. Variable values are assigned at runtime to ensure that customized configuration is available to users of different Orgs in ThoughtSpot. ## Overview ThoughtSpot provides predefined system variables such as `ts_username` and `ts_groups`, which can be used in formulas for data security. Additionally, you can configure variables programmatically via REST APIs, and later use these variables for the following purposes: - To [parameterize metadata object properties]({{navprefix}}/{{metadata-parameterization}}) and [publish artifacts across Orgs]({{navprefix}}/{{publishing-overview}}). - To implement [Row Level Security (RLS) rules]({{navprefix}}/{{rls-rules}}) using formula variables and customize data for a specific Org, Model, or user. You can also pass variable attributes as security entitlements in JWT tokens for [ABAC implementation]({{navprefix}}/{{abac-user-parameters}}#_abac_via_rls_with_formula_variables). - To update properties of a Connection or Table directly in the TML. ### Variable types ThoughtSpot supports creating the following types of variables via APIs: - `TABLE_MAPPING` Can be used for table mapping properties such as schema name, database name, and table name. - `CONNECTION_PROPERTY` Can be used for data connection properties such as `accountName`, `warehouse`, `user`, `password`, `role`, and so on. - `CONNECTION_PROPERTY_PER_PRINCIPAL` Can be used for modifying connection properties for a specific principal object, such as a user or user group. This means you can set different values for connection properties, such as `warehouse`, `role`, `user`, and `password`, depending on the user or group accessing the connection. The `CONNECTION_PROPERTY_PER_PRINCIPAL` variable does not allow parameterizing core connection properties such as `accountName`, `host`, or `port`. These properties must be derived from the connection configuration and cannot be set per user or user group. > **NOTE:** This feature is disabled by default. To enable this option, contact ThoughtSpot Support. - `FORMULA_VARIABLE` Formula variables allow you to parameterize logic in formulas and rules, and can be used in different contexts to dynamically populate values. Formula variables can be set for an Org, user, or Model and can be used in RLS rules with the `ts_var` function. The Variable API allows administrators to define formula variables for the `VARCHAR`, `BIGINT`, `INT`, `FLOAT`, `DOUBLE`, `BOOLEAN`, `DATE`, `DATE_TIME`, and `TIME` data types. ### APIs for Variable creation and management The following REST API endpoints are available for variable creation and management: - `POST /api/rest/2.0/template/variables/create` Allows [creating variables]({{navprefix}}/{{variables}}#_create_a_variable). - `POST /api/rest/2.0/template/variables/{identifier}/update` Allows [updating the variable name]({{navprefix}}/{{variables}}#_update_variable_name). - `POST /api/rest/2.0/template/variables/{identifier}/update-values` Allows [assigning values to a variable]({{navprefix}}/{{variables}}#_update_variable_values). - `POST /api/rest/2.0/template/variables/search` [Retrieves variables available in your Org context]({{navprefix}}/{{variables}}#_get_details_of_variables). - `POST /api/rest/2.0/template/variables/delete` [Deletes the variables]({{navprefix}}/{{variables}}#_delete_a_variable) specified in the API request. > **NOTE:** Variable APIs for creating, deleting, searching, and assigning values can only be used by the ThoughtSpot instance administrator. These APIs can only be used from the primary Org. ## Before you begin - Ensure that you have administration privileges to create and manage variables. If creating variables within a specific Org context, ensure your ThoughtSpot account has Org administration permissions or the `CAN_MANAGE_VARIABLES` (**Can manage variables**) privilege to create and manage variables. - Ensure that you have edit access to the metadata objects to which you want to assign variables. ## Create a variable To create a variable, send a `POST` request to the [/api/rest/2.0/template/variables/create]({{navprefix}}/restV2-playground?apiResourceId=http%2Fapi-endpoints%2Fvariable%2Fcreate-variable) API endpoint, with the following parameters in the request body. ### Request parameters In your `POST` request body, include the following parameters: | Parameter | Description | | --- | --- | | `type` | _String_. Type of the variable. The API supports the following types of variables: - `TABLE_MAPPING` To create variables for Table properties such as `databaseName`, `schemaName`, and `tableName`. - `CONNECTION_PROPERTY` To define variables for connection properties. This variable allows editing connection properties such as `accountName`, `warehouse`, `user`, `password`, `role`, and so on. - `CONNECTION_PROPERTY_PER_PRINCIPAL` To define variables for connection properties per user or user group. This variable allows modifying connection properties such as `warehouse`, `role`, `user`, and `password`. The `CONNECTION_PROPERTY_PER_PRINCIPAL` variable does not support modifying core connection properties such as `accountName`, `host`, or `port`. These properties must be derived from the connection configuration and cannot be set per user or user group. - `FORMULA_VARIABLE` For formula variables. For formula variables, you must specify the data type. | | `name` | _String_. Name of the variable. For example, `schema_var`. Note that the name must be unique across all Orgs within the instance. | | `is_sensitive` _Optional_ | _Boolean_. Indicates if the variable contains sensitive values such as passwords. | | `data_type` | _String_. Variable data type. It’s a required parameter for formula variables. The data type defined during variable creation determines the format of the variable values. For example, if you specify the `data_type` as `DATE`, the value for this variable must be in epoch format. Supported data types for formula variable values are: - `VARCHAR` String. For example, if you are creating variables for different regions, set the data type as `VARCHAR` to allow string values such as `EAST`, `WEST`, and so on. - `INT32` 32-bit integer. If you are creating a formula variable to specify a threshold for the quantity of the products purchased, or for columns such as customer or product IDs, set the type as `INT32` to allow numbers within the 32-bit range. For example, 100, 123456, 3005. - `INT64` 64-bit integer. For variable values that exceed the 32-bit range, set the data type as `INT64`. For example, transaction numbers, sales IDs, or discount values. - `DOUBLE` In ThoughtSpot, the DOUBLE data type is used for columns that require floating-point arithmetic or need to store numbers such as latitude, longitude, percentages, monetary values, margin threshold, measurements, and so on. - `DATE` Date in the epoch format. If you are creating a formula variable for a specific start date, set the data type as `DATE` and configure the date value in epoch format. For example, `1711933200` for 1 April 2024 (GMT). - `DATE_TIME` Date with time stamp. If you are creating a formula variable for specific timestamp, specify the data type as `DATE_TIME` and assign a timestamp in epoch format as variable value. For example, `1711933200000` for 1 April 2024 01:00:00 (GMT). > **NOTE:** The data\_type is required only for formula variables and is not supported for other variable types. | ### Example request The following example shows the request body for the table mapping variable type: ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/create' \ -H 'Accept: application/json' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "type": "TABLE_MAPPING", "name": "schema_var", "is_sensitive": true }' ``` The following example shows the request body for the formula variable creation: ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/create' \ -H 'Accept: application/json' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "type": "FORMULA_VARIABLE", "name": "region_var", "is_sensitive": false, "data_type": "VARCHAR" }' ``` The following example shows the request body for the connection property variable: ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/create' \ -H 'Accept: application/json' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "type": "CONNECTION_PROPERTY", "name": "accountNameVar", "is_sensitive": false }' ``` ### Example response If the API request is successful, ThoughtSpot returns the variable details in the response. ```JSON { "id": "3242b54c-69bc-4ff0-97cf-f99a2216b616", "name": "schema_var", "variable_type": "TABLE_MAPPING", "sensitive": true, "values": [] } ``` ```JSON { "id": "a033c855-dc49-4936-a82f-7fd29e57ab6b", "name": "country", "variable_type": "FORMULA_VARIABLE", "sensitive": false, "values": [] } ``` ```JSON { "id": "a033c855-dc49-4936-a82f-7fd29e57ab6b", "name": "accountNameVar", "variable_type": "CONNECTION_PROPERTY", "sensitive": false, "values": [] } ``` Note the variable ID and name for variable edits. The API returns an empty array for values because the values are not assigned to the variable. - To assign values, use the `/api/rest/2.0/template/variables/{identifier}/update-values` API endpoint. - To include formula variables in a JSON Web Token (JWT) token for [ABAC implementation]({{navprefix}}/{{abac_rls-variables}}), use the `/api/rest/2.0/auth/token/custom` API endpoint. > **NOTE:** In ABAC implementation, you can assign the variable values during token generation. Before configuring variable values for JWT generation, ensure that the variables are already available in ThoughtSpot and check whether their scope and values via a variable search API request. ## Update variable name To update the properties the name of variable, use the [/api/rest/2.0/template/variables/{identifier}/update]({{navprefix}}/restV2-playground?apiResourceId=http%2Fapi-endpoints%2Fvariable%2Fupdate-variable) API endpoint. In your API request, specify the variable ID in the `{identifier}` path parameter and the new name in the `POST` request body. ### Example request The following example shows the request body for updating the name of a variable: ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/3242b54c-69bc-4ff0-97cf-f99a2216b616/update' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "name": "demo_schema_var" }' ``` If the update operation is successful, the API returns a 204 response to indicate that the variable was updated successfully. ## Assign or update variable values To assign values to a variable, use the [/api/rest/2.0/template/variables/{identifier}/update-values]({{navprefix}}/restV2-playground?apiResourceId=http%2Fapi-endpoints%2Fvariable%2Fupdate-variable-values) API endpoint. For example, you can assign values for a formula variable and limit its scope to a specific Org, Model, or user context. Similarly, for the `CONNECTION_PROPERTY_PER_PRINCIPAL` variable type, you can define the principal type as user or user group, and specifiy the ID of the principal object. The API also allows you to edit, replace, or reset the values and scope assigned to a variable. > **NOTE:** The /api/rest/2.0/template/variables/{identifier}/update-values API endpoint replaces the legacy /api/rest/2.0/template/variables/update-values endpoint. The legacy endpoint is deprecated in 26.4.0.cl and later. ### Request parameters | Parameter | Description | | --- | --- | | `identifier` | _String_. ID of the variable to assign values. This is path parameter and must be passed in the request URL. | | `operation` | Specify the update operation type. The following values are available: - `ADD` Adds new values. Use this operation type to assign values to the variable. - `REMOVE` Removes the values assigned to the variable. For example, you can remove the values assigned to a variable configured for an Org. - `REPLACE` Replaces the existing attributes with new values. - `RESET` Resets all values at the variable level. For example, if a variable is assigned to multiple entities such as Org, user, or user group, the reset operation clears the values assigned to the variable for all entities. | | `variable_assignment` | _Array of variable values_. You can assign one or several set of values, and define the scope and constraints for each set of variable values. Assign values To assign variable values, specify the values in the `assigned_values` array. Define scope To set the scope for these variable values, define the following properties. These properties determine the entity level, such as Org, user, or user-group, for which the values will apply. - `org_identifier`: _Optional_. ID or name of the Org. For primary Org, specify `primaryOrg` or Org 0. Applicable to `TABLE_MAPPING`, `FORMULA_VARIABLE`, and `CONNECTION_PROPERTY` variable types. - `principal_type` and `principal_identifier`: _Optional_. Principal attributes such as user and user group. Applicable to the `CONNECTION_PROPERTY_PER_PRINCIPAL` variable type. - `model_identifier`: _Optional_. ID or name of the Model to which the variables configuration must be applied. Applicable to formula variables. - `priority`: _Optional_. Priority assigned to this value. If more than one entity matches the conditions during variable resolution, the system determines which entity’s value takes effect based on the value assigned to the `priority` parameter. For example, if a variable is configured for both the user and their group, the system determines which value to based on the assigned priority. Applicable to the `CONNECTION_PROPERTY_PER_PRINCIPAL` variable type. | | | | ### Example request The following sections show the examples for assigning values to different types of variables and configuring the Org scope. Note that the variable values and Org identifiers are case-sensitive. #### Table mapping variables The following example shows the request parameters to assign values to `TABLE_MAPPING` variables and set the variable scope to specific Orgs: ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/table_var/update-values' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "variable_assignment": [ { "assigned_values": [ "SALES_SCHEMA_A", ], "org_identifier": "Primary" }, { "assigned_values": [ "SALES_DB_A" ], "org_identifier": "Primary" }, { "assigned_values": [ "SALES_TABLE_A" ], "org_identifier": "Primary" } ], "operation": "ADD" }' ``` If the values are assigned successfully, the API returns 204 response code. You can use these variables in the Table TML or for [parameterizing Table properties]({{navprefix}}/{{metadata-parameterization}}). #### Connection property variables The following example shows the request parameters to assign values to the `CONNECTION_PROPERTY` variables and set the variable scope to a specific Org: ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/account_name_var/update-values' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "variable_assignment": [ { "assigned_values": [ "orgA_account" ], "org_identifier": "OrgA" }, { "assigned_values": [ "OrgB_account" ], "org_identifier": "OrgB" }, ], "operation": "ADD" }' ``` If the values are assigned successfully, the API returns 204 response code. You can use these variables in the Connection TML or for [parameterizing connection properties]({{navprefix}}/{{metadata-parameterization}}). #### Formula variables Formula variables assigned to Primary Org can be used across all Orgs. To avoid invalid data type errors, ensure that the values you assign to formula variables match the data type configured during variable creation. ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/country_var/update-values' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "variable_assignment": [ { "assigned_values": [ "Germany", "Switzerland" ], "org_identifier": "Primary" }, { "assigned_values": [ "Singapore", "Japan" ], "org_identifier": "OrgA" }, { "assigned_values": [ "Canada", "Mexico" ], "org_identifier": "OrgB" } ], "operation": "ADD" } ``` If the variable update is successful, the API returns 204 response code. you can use these variables in [RLS rules](https://docs.thoughtspot.com/cloud/latest/rls-variables-reference). You can also assign values and set the scope when creating a JWT for a given user. For more information, see [ABAC via RLS with variables]({{navprefix}}/{{abac_rls-variables}}). ## Get variables To get a list of variables or the details of a specific variable, send a `POST` request to the `[/api/rest/2.0/template/variables/search]({{navprefix}}/restV2-playground?apiResourceId=http%2Fapi-endpoints%2Fvariable%2Fsearch-variables)` API endpoint. To search for a variable, specify the following parameters in your API request: - variable details Details such as variable type, ID, and name pattern. For name pattern search, specify the partial name of the variable. For wildcard search, use `%`. - value scope Attributes such as Org ID, Model ID, and ID and type of Principal object, which determine the scope of the variable values. - output format for response content Specify one of the following values for output format: - `METADATA_ONLY` (default) Returns only the variable metadata - `METADATA_AND_VALUES` Returns the variable metadata along with the values assigned to each variable. ### Example request ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/search' \ -H 'Accept: application/json' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "record_offset": 0, "record_size": 10, "response_content": "METADATA", "value_scope": [ { "org_identifier": "primary" } ], "variable_details": [ { "identifier": "table_var", "type": "TABLE_MAPPING" }, { "identifier": "schema_var", "type": "TABLE_MAPPING" } ] }' ``` ### Example response If the request is successful, the API returns the variable data in the response: ```JSON [ { "id": "f658cfd5-fa6f-4c33-a12f-ea46fa799666", "name": "table_var", "variable_type": "TABLE_MAPPING", "sensitive": false, "values": null }, { "id": "7f52d7b1-38f1-4127-ad5a-a2a7f58064df", "name": "schema_var", "variable_type": "TABLE_MAPPING", "sensitive": false, "values": null } ] ``` ## Delete a variable To delete one or more variables, use the [/api/rest/2.0/template/variables/delete]({{navprefix}}/restV2-playground?apiResourceId=http%2Fapi-endpoints%2Fvariable%2Fdelete-variables) API endpoint. The API does not allow deleting formula variables if they are used in Models, RLS rules, user properties, or ABAC tokens. If the variable is used by an object in ThoughtSpot, make sure to remove the variable assignments and references before deleting it from ThoughtSpot. > **NOTE:** The legacy delete endpoint /api/rest/2.0/template/variables/{identifier}/delete is deprecated in 26.4.0.cl and later versions, and is replaced with the /api/rest/2.0/template/variables/delete endpoint. You can use the new endpoint to delete multiple variables in a single API request. ### Example request ```cURL curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/template/variables/delete' \ -H 'Content-Type: application/json' \ -H 'Authorization: Bearer {AUTH_TOKEN}' \ --data-raw '{ "identifiers": [ "table_var_1", "table_var_2", "table_var_3" ] } ``` If the API request is successful, ThoughtSpot returns a 204 response code. ## Additional resources - For more information about using formula variables in RLS rules, see [Formula variables](https://docs.thoughtspot.com/cloud/latest/rls-variables-reference). - For information about assigning security entitlements via ABAC tokens, see [ABAC via RLS with formula variables]({{navprefix}}/{{abac_rls-variables}}). - For information about RLS rules, see [RLS rules documentation](https://docs.thoughtspot.com/cloud/latest/security-rls).