Snowflake
Use the Snowflake integration to query and update your Snowflake database.
Configure Snowflake on Demisto
Several parameters are explained in greater detail in the Detailed Instructions section.
- Navigate to Settings > Integrations > Servers & Services .
- Search for Snowflake.
-
Click
Add instance
to create and configure a new integration instance.
- Name : a textual name for the integration instance.
- Username
- Account - See Detailed Description section.
- Region (only if you are not US West)
- Authenticator - See Detailed Description section.
- Default warehouse to use
- Default database to use
- Default schema to use
- Default role to use
- Use system proxy settings
- Trust server certificate (insecure)
- Fetch incidents
- Fetch query to retrieve new incidents. This field is mandatory when ‘Fetches incidents’ is set to true.
- First fetch timestamp (
- The name of the field/column that contains the datetime object or timestamp for the data being fetched (case sensitive). This field is mandatory when ‘Fetches incidents’ is set to true.
- The name of the field/column in the fetched data from which the name for the demisto incident will be assigned (case sensitive)
- The maximum number of rows to be returned by a fetch
- Incident type
- Click Test to validate the URLs, token, and connection.
Detailed Instructions
Additional information for configuring the integration instance.
Integration Parameters
-
Account
The name of the Snowflake account to connect to without the domain name: snowflakecomputing.com. For example, mycompany.snowflakecomputing.com, enter “mycompany”. For more information, see the Snowflake Computing documentation . -
Authenticator
(Optional) Use this parameter to log in to your Snowflake account using Okta. For the ‘Username’ parameter, enter your ‘<okta_login_name>’. For the ‘Password’ parameter, enter your ‘<okta_password>’. The value entered here should be ‘https://<okta_account_name>.okta.com/’ where all the values between the less than and greater than symbols are replaced with the actual information specific to your Okta account. -
Credentials
To use Key Pair authentication, follow these instructions:- Follow steps 1-4 in the instructions detailed in the Snowflake Computing documentation .
- Follow the instructions under the section titled Configure Demisto Credentials at this link .
- Use the credentials you configured. Refer to the two images at the bottom of the section titled Configure an External Credentials Vault .
Commands
You can execute these commands from the Demisto CLI, as part of an automation, or in a playbook. After you successfully execute a command, a DBot message appears in the War Room with the command details.
1. Run a query in Snowflake
Executes a SELECT query and retrieve the data.
Base Command
snowflake-query
Input
| Argument Name | Description | Required |
|---|---|---|
| query | The query to execute. | Required |
| warehouse | The warehouse to use for the query. If not specified, the default will be used. | Optional |
| database | The database to use for the query. If not specified, the default will be used. | Optional |
| schema | The schema to use for the query. If not specified, the default will be used. | Optional |
| role | The role to use for the query. If not specified, the default will be used. | Optional |
| limit | The number of rows to retrieve. | Optional |
| columns | A CSV list of columns to display in the specified order, for example: “Name, ID, Timestamp” | Optional |
Context Output
| Path | Type | Description |
|---|---|---|
| Snowflake.Query | String | The query used to fetch results from the database. |
| Snowflake.Result | Unknown | Results from querying the database. |
| Snowflake.Database | String | The name of the database object. |
| Snowflake.Schema | String | The name of the schema object. |
Command Example
snowflake-query warehouse=demo_wh database=demo_db schema=public query="select * from test"
Context Example
{
"Snowflake": {
"Query": "select * from test",
"Schema": "public",
"Result": [
{
"TS": "2018-09-11 00:00:00.000000",
"ID": 1,
"NAME": "b"
},
{
"TS": "2018-10-12 00:00:00.000000",
"ID": 2,
"NAME": "kuku"
},
{
"TS": "2018-10-12 00:00:00.000000",
"ID": 3,
"NAME": "kiki"
},
{
"TS": "2018-10-12 00:00:00.000000",
"ID": 4,
"NAME": "kaka"
},
{
"TS": "2018-10-12 00:00:00.000000",
"ID": 5,
"NAME": "kuku"
},
{
"TS": "2019-03-26 11:14:18.574000",
"ID": 8,
"NAME": "blah"
},
{
"TS": "2019-03-26 11:16:16.773000",
"ID": 8,
"NAME": "new"
},
{
"TS": "2019-03-26 11:30:42.479000",
"ID": 9,
"NAME": "nBw4QhFcGJ"
},
{
"TS": "2019-03-14 00:00:00.000000",
"ID": 10,
"NAME": "UPDATing"
},
{
"TS": "2019-03-19 00:00:00.000000",
"ID": 11,
"NAME": "TESTING IT OUT again"
},
{
"TS": "2019-03-28 05:32:13.355000",
"ID": 13,
"NAME": "New Alert"
},
{
"TS": "2019-03-28 06:09:26.153000",
"ID": 14,
"NAME": "SHOULD FETCH THIS NEW"
},
{
"TS": "2019-03-28 08:46:50.311000",
"ID": 15,
"NAME": "Perth"
},
{
"TS": "2019-03-28 06:19:06.271000",
"ID": 16,
"NAME": "Edinburgh"
},
{
"TS": "2019-03-28 06:19:14.059000",
"ID": 17,
"NAME": "York"
},
{
"TS": "2019-03-28 06:20:27.126000",
"ID": 18,
"NAME": "Persimmon"
},
{
"TS": "2019-03-28 06:28:31.001000",
"ID": 19,
"NAME": "Langdon"
},
{
"TS": "2019-03-28 11:53:41.416000",
"ID": 20,
"NAME": "London"
}
],
"Database": "demo_db"
}
}
Human Readable Output
select * from test
| ID | NAME | TS |
|---|---|---|
| 1 | b | 2018-09-11 00:00:00.000000 |
| 2 | kuku | 2018-10-12 00:00:00.000000 |
| 3 | kiki | 2018-10-12 00:00:00.000000 |
| 4 | kaka | 2018-10-12 00:00:00.000000 |
| 5 | kuku | 2018-10-12 00:00:00.000000 |
| 8 | blah | 2019-03-26 11:14:18.574000 |
| 8 | new | 2019-03-26 11:16:16.773000 |
| 9 | nBw4QhFcGJ | 2019-03-26 11:30:42.479000 |
| 10 | UPDATing | 2019-03-14 00:00:00.000000 |
| 11 | TESTING IT OUT again | 2019-03-19 00:00:00.000000 |
| 13 | New Alert | 2019-03-28 05:32:13.355000 |
| 14 | SHOULD FETCH THIS NEW | 2019-03-28 06:09:26.153000 |
| 15 | Perth | 2019-03-28 08:46:50.311000 |
| 16 | Edinburgh | 2019-03-28 06:19:06.271000 |
| 17 | York | 2019-03-28 06:19:14.059000 |
| 18 | Persimmon | 2019-03-28 06:20:27.126000 |
| 19 | Langdon | 2019-03-28 06:28:31.001000 |
| 20 | London | 2019-03-28 11:53:41.416000 |
2. Make a DML change in the database
Makes a DML change in the database.
Base Command
snowflake-update
Input
| Argument Name | Description | Required |
|---|---|---|
| db_operation | The command to execute. | Required |
| warehouse | The warehouse to use for the query. If not specified, the default will be used. | Optional |
| database | The database to use for the query. If not specified, the default will be used. | Optional |
| schema | The schema to use for the query. If not specified, the default will be used. | Optional |
| role | The role to use for the query. If not specified, the default will be used. | Optional |
Context Output
There is no context output for this command.
Command Example
snowflake-update warehouse=demo_wh database=demo_db schema=public db_operation="update test set NAME='Persimmon' where ID=18"
Human Readable Output
Operation executed successfully.