Snowflake SQL Table Update

Provides an efficient way for executing bulk insert/update on a Snowflake database without having to hand-write INSERT or UPDATE statements.

This Node may be affected by Windows Regional Settings.

Revision History

1.0.0.0 Initial release
1.0.0.1 Improved error handling on Connection Secrets

Properties

Connection

Type: Connection Input

Url
Type: String
The URL for your Snowflake acount. Can be retrieved by viewing account details in the Snowflake application.

AccountIdentifier
Type: String
The account identifier for your Snowflake acount. Can be retrieved by viewing account details in the Snowflake application.

UserName
Type: String
The user name for the account you want to log in as. Can be retrieved by viewing account details in the Snowflake application, or under Users in the Admin section.

Database
Type: String
The name of the database you want to connect to.

Schema optional
Type: String
The name of a schema you want to limit the connection to. This is optional.

ClientSecrets
Type: Password
The OAuth client secrets JSON object is used to authenticate the login. See Activate OAuth section for more details on how to get this.

Role optional
Type: String
The role you want to use for the connection. This is not required as the user name's role will be used if no other role supplied here.

AccessToken
Type: Password
OAuth Access Token

RefreshToken
Type: Password
OAuth Refresh Token

Schema

Type: [String
The name of the schema you want to affect with your update. Only required if no schema specified on the connection.

Table

Type: Json Input
A JSON representation of the data to be updated or inserted (see Remarks)

KeyFields

Type: String Input
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the Table property. It is not necessary to supply key fields if AllowUpdate is false.

AllowInsert

Type: Boolean Input
Indicates whether inserts are allowed into the target table.

AllowUpdate

Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).

RowsAffected

Type: Int32 Output
Returns the number of rows affected by the insert/update operation.

#Remarks

The JSON supplied should look like the following:

{
	"TableName": [
		{
			"code": "123",
			"description": "first item",
			"amount": 100
		},
		{
			"code": "456",
			"description": "second item",
			"amount": 85
		}
	]
}

When AllowUpdate is true, the fields specified in KeyFields will be matched from TableXml to the target table. All KeyFields supplied must exist in both TableXml and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.

For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)), the KeyFields property could be set to code if it was necessary to update based on this value rather than the internal integer id field.

If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.

When a column is an identity column, and specific values needs to be inserted, a custom field 'IdentityInsert' with value 'true' could be added to the node.

For tables and columns with spaces, the xml tag name will have to be escaped to make it valid xml. A space should be escaped as _0x0020_

A full list of characters can be found here: Unicode UCS-2 Code Chart (columbia.edu)

Activate OAuth

You will need to activate OAuth on your Snowflake account to be able to use this connector. To activate OAuth you must create a security integration.
To do this you must navigate to the SQL Worksheet (accessed by clicking on the + in the left menu) and create a new SQL Worksheet.

You will then have to run the following command in that worksheet

CREATE OR REPLACE SECURITY INTEGRATION 
"Flowgear"
TYPE = OAUTH
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = "CONFIDENTIAL"
OAUTH_REDIRECT_URI = "https://app.flowgear.net/r/auth"
OAUTH_ISSUE_REFRESH_TOKENS = true
ENABLED = true;

This will create a security integration on your account that can then be used to create a connection. You will note that the creation does not supply you with the client secrets needed. To get those you must run a separate command.

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'Flowgear' )

You can then copy the value returned from that as is and paste it in the ClientSecrets. The object will be parsed to get the client id/secret needed for OAuth.

By default, Snowflake prevents the ACCOUNTADMIN, ORGADMIN, GLOBALORGADMIN, and SECURITYADMIN roles from authenticating. To allow these roles to authenticate you will need to run the follow command.

ALTER ACCOUNT SET OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = false;

Troubleshooting

The most common error that occurs is "Failed to convert parameter value from a String to a Double" (or other numeric type). This occurs due to the Regional Settings in place on the machine on which the DropPoint is running. For example, if the decimal separator is set to a comma, only numbers containing a comma decimal separator will be parsed correctly.

To resolve these problems, first determine exactly which field or fields are not being converted correctly by progressively reducing your JSON payload until it contains only the problem fields. Next, review the regional settings at the DropPoint.

If it is not possible to change Regional Settings on the machine, the numeric fields need to be formatted in a way that conforms to the Regional Settings.

See Also

MySQL Table Update
ODBC Table Update
OLEDB Table Update
Microsoft SQL Table Update