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.

  1. Navigate to Settings > Integrations > Servers & Services .
  2. Search for Snowflake.
  3. 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
  4. 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:

    1. Follow steps 1-4 in the instructions detailed in the Snowflake Computing documentation .
    2. Follow the instructions under the section titled Configure Demisto Credentials at this link .
    3. 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.