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.