Freigeben über


Snowflake (Preview)

Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and more flexible than traditional offerings.

This connector is available in the following products and regions:

Service Class Regions
Logic Apps Standard All Logic Apps regions except the following:
     -   Azure Government regions
     -   Azure China regions
     -   US Department of Defense (DoD)
Power Automate Premium All Power Automate regions except the following:
     -   US Government (GCC)
     -   US Government (GCC High)
     -   China Cloud operated by 21Vianet
     -   US Department of Defense (DoD)
Power Apps Premium All Power Apps regions except the following:
     -   US Government (GCC)
     -   US Government (GCC High)
     -   China Cloud operated by 21Vianet
     -   US Department of Defense (DoD)
Contact
Name Snowflake
URL https://www.snowflake.com/support
Email support@snowflake.com
Connector Metadata
Publisher Snowflake Inc.
Website https://www.snowflake.com
Privacy policy https://www.snowflake.com/privacy-policy
Categories Data

Snowflake

Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and more flexible than traditional offerings.

This connector is available in the following products and regions:

Service Class Regions
Logic Apps Standard All Logic Apps regions except the following:
- Azure Government regions
- Azure China regions
- US Department of Defense (DoD)
Power Automate Premium All Power Automate regions except the following:
- US Government (GCC)
- US Government (GCC High)
- China Cloud operated by 21Vianet
- US Department of Defense (DoD)
Power Apps Premium All Power Apps regions except the following:
- US Government (GCC)
- US Government (GCC High)
- China Cloud operated by 21Vianet
- US Department of Defense (DoD)

Contact
Name Snowflake
URL https://www.snowflake.com/support
Email support@snowflake.com

Connector Metadata
Publisher Snowflake Inc.
Website https://www.snowflake.com
Privacy policy https://www.snowflake.com/privacy-policy
Categories Data

Snowflake connector is based on the Snowflake SQL REST API. Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and more flexible than traditional offerings. The connector uses the Snowflake REST API V2 to submit synchronous and asynchronous queries and retrieve corresponding results.

Prerequisites

  • Users must have Microsoft Entra ID for the external authentication.
  • Users must have a premium Power Apps license.
  • Users must have Snowflake account.

How to get your credentials

Set up Azure AD authentication for Snowflake by following these steps:

  1. In Step 1: Configure the OAuth Resource in Azure AD, follow steps 1-10 and define the scope as SESSION:ROLE-ANY by following these instructions.
  2. In Step 2: Create an OAuth Client in Azure AD, follow steps 1-13.
  3. Navigate to Authentication -> Platform configurations -> Add a platform -> Add "https://global.consent.azure-apim.net/redirect" -> Click Save. Ensure that the redirect URL is set in the Snowflake OAuth Client and not the Snowflake OAuth Resource.
  4. Go to the resource created in Step 1 and go to Expose an API -> Add a client application -> Add your APPLICATION_CLIENT_ID from earlier in step 3 above -> Click Save
  5. Follow Step 3: Collect Azure AD Information for Snowflake entirely.
  6. If you have already established a connection using the Snowflake certified connector, follow the steps in section Update existing security integration in Snowflake. If you are establishing a new connection, follow the steps in section Create new security integration in Snowflake. Make sure you've set your role as ACCOUNTADMIN before executing the query.

Create new security integration in Snowflake

  1. In Microsoft Azure, go to your Snowflake OAuth Resource app and click on Endpoints.

  2. To get the AZURE_AD_ISSUER in line 5, copy the link in the Federation metadata document field and open the link in a new tab. Copy the entityID link which should something look like this: https://sts.windows.net/90288a9b-97df-4c6d-b025-95713f21cef9/. Paste it into the query and make sure you have a / before the last quotation mark and that you keep the quotation marks.

  3. To get the Keys URL in line 6, copy the link in the OpenID Connect metadata document field and open the link in a new tab. Copy the jwks_uri which should look something like this: https://login.microsoftonline.com/90288a9b-97df-4c6d-b025-95713f21cef9/discovery/v2.0/keys. Paste it into the query and make sure you keep the quotation marks.

  4. Replace the Audience List URL in line 7 with Application ID URI from Step 1. Keep the quotation marks.

  5. If your Snowflake account uses the same email address as your Microsoft Azure account, then replace login_name in line 9 with email_address. If not, keep it as is and do not type in your login name. Keep the quotation marks.

CREATE SECURITY INTEGRATION <integration name>
type = external_oauth
enabled = true
external_oauth_type = azure
external_oauth_issuer = '<AZURE_AD_ISSUER>'     
external_oauth_jws_keys_url = '<Keys URL from section "How to get your credentials:step 6">'
 external_oauth_audience_list = ('<Application ID URI from registered resource app in Azure>')
external_oauth_token_user_mapping_claim = 'upn'
external_oauth_snowflake_user_mapping_attribute = 'login_name'
external_oauth_any_role_mode = 'ENABLE';

Update existing security integration in Snowflake

ALTER SECURITY INTEGRATION  <existing integration name>
set external_oauth_audience_list = ('<existing power bi audience list url>', '< Application ID URI from Step 1>')

Using the credentials

While creating the connection in power platform, use the credentials as shown in below snapshot.

  1. Client Id: Snowflake OAuth Client ID from registered Client app in Azure
  2. Client Secret: Snowflake OAuth Client secret from registered Client app in Azure
  3. Resource URL: Application ID URI from registered Resource app in Azure

Get started with your connector

  • Submit SQL Statement for Execution
  • Check the Status and Get Results
  • Cancel the Execution of a Statement

Known issues and limitations

Throttling Limits

Name Calls Renewal Period
API calls per connection 100 60 seconds

Actions

Action Description
Cancel the Execution of a Statement Cancel the Execution of a Statement
Check the Status and Get Results Check the Status of the Execution of a Statement and Get the Results
Convert result set rows from array to objects Convert result set rows from array to objects
Submit SQL Statement for Execution Submit a SQL Statement for Execution

Cancel the Execution of a Statement

Operation ID: Cancel

Cancel the Execution of a Statement

Parameters

Name Key Required Type Description
Instance Instance True String The uri of your Snowflake instance (without https://)
statement handle statementHandle True String statement handle
Request ID requestId String Request ID

Returns

Name Path Type Description
Code Code String Code
sqlState sqlState String sqlState
Message Message String Message
statementHandle statementHandle String statementHandle
statementStatusUrl statementStatusUrl String statementStatusUrl

Check the Status and Get Results

Operation ID: GetResults

Check the Status of the Execution of a Statement and Get the Results. While retrieving the results, the first partition always returns schema along with the data and further partitions returns only the data which may need to be converted using result set action.

Parameters

Name Key Required Type Description
Instance Instance True String The uri of your Snowflake instance
statement handle statementHandle True String statement handle
Request ID requestId String Request ID
Partition Partition Integer Partition

Returns

Name Path Type Description
Code Metadata.Code String
SqlState Metadata.SqlState String
Rows Metadata.Rows Integer Total number of rows
StatementHandle Metadata.StatementHandle String
CreatedOn Metadata.CreatedOn Integer Timestamp that specifies when the statement execution started. The timestamp is expressed in milliseconds since the epoch
StatementStatusUrl Metadata.StatementStatusUrl String
Format Metadata.Format string For v2 endpoints the only possible value for this field is jsonv2.
Partitions Partitions array of object Partition information
RowCount Partitions.RowCount integer Number of rows in the partition.
CompressedSize Partitions.CompressedSize integer the partition size before the decompression. This may or may not be present in the partitions. Uncompressed size would always be there.
UncompressedSize Partitions.UncompressedSize integer the partition size after the decompression
Data Data array of array Result set data.
Schema Schema array of string Result set schema

Convert result set rows from array to objects

Operation ID: Convert

Convert result set rows from array to objects

Parameters

Name Key Required Type Description
schema Schema string
Data Data string

Returns

Name Path Type Description
Data Data array of object Result set data.
Schema Schema array of object Result set schema

Submit SQL Statement for Execution

Operation ID: ExecuteSqlStatement

Submit a SQL Statement for Execution

Parameters

Name Key Required Type Description
Instance Instance True string The uri of your Snowflake instance
Request ID requestId string The ID of the Request
Asynchronous Async boolean Indicates if the query should be executed asynchronously
Nullable Nullable boolean If nullable is false, null values will be replaced by string
Statement Statement string The SQL statement to be executed - batches of statements not yet supported
Timeout timeout integer Number of seconds before timeout occurs
Database database string Database
Schema schema string Schema
Warehouse warehouse string Warehouse
Role Role string Role
Timezone timezone string Time zone to use when executing the statement.
query tag query_tag string Query tag that you want to associate with the SQL statement.
binary output format binary_output_format string Output format for binary values.
date output format date_output_format string Output format for DATE values.
time output format time_output_format string Output format for TIME values.
timestamp output format timestamp_output_format string Output format for TIMESTAMP values.
timestamp ltz output format timestamp_ltz_output_format string Output format for TIMESTAMP_LTZ values.
timestamp_ntz_output_format timestamp_ntz_output_format string Output format for TIMESTAMP_NTZ values.
timestamp tz output format timestamp_tz_output_format string Output format for TIMESTAMP_TZ values.
multi statement count multi_statement_count integer Number of statements to execute when using multi-statement capability. 0 implies variable number of statements. Negative numbers are not allowed.

Returns

Name Path Type Description
Code Metadata.Code String
SqlState Metadata.SqlState String
StatementHandle Metadata.StatementHandle String
CreatedOn Metadata.CreatedOn Integer Timestamp that specifies when the statement execution started. The timestamp is expressed in milliseconds since the epoch
StatementStatusUrl Metadata.StatementStatusUrl String
Format Metadata.Format String For v2 endpoints the only possible value for this field is jsonv2.
Partitions Partitions array of object Partition information
RowCount Partitions.RowCount Integer Number of rows in the partition.
CompressedSize Partitions.CompressedSize Integer the partition size before the decompression. This may or may not be present in the partitionInfo. Uncompressed size would always be there.
UncompressedSize Partitions.UncompressedSize Integer the partition size after the decompression
Data Data array of array Result set data.
Schema Schema array of string Results set schema

FAQ

  1. How to handle responses and partitions?
    Refer Snowflake SQL API documentation here
  2. How to handle partition 1 and above?
    Use "Convert result set rows from array to objects" action in the connector to transform the data.

Creating a connection

The connector supports the following authentication types:

Default Parameters for creating connection. All regions Not shareable

Default

Applicable: All regions

Parameters for creating connection.

This is not shareable connection. If the power app is shared with another user, another user will be prompted to create new connection explicitly.

Name Type Description Required
Client ID string Snowflake OAuth Client ID True
Client Secret securestring Snowflake OAuth Client Secret True
Resource URL string Snowflake OAuth Audience URL(Resource URL) True

Throttling Limits

Name Calls Renewal Period
API calls per connection 100 60 seconds

Actions

Cancel the Execution of a Statement

Cancel the Execution of a Statement

Check the Status and Get Results

Check the Status of the Execution of a Statement and Get the Results

Convert result set rows from array to objects

Convert result set rows from array to objects

Submit SQL Statement for Execution

Submit a SQL Statement for Execution

Cancel the Execution of a Statement

Cancel the Execution of a Statement

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

statement handle
statementHandle True string

statement handle

Request ID
requestId string

Request ID

Returns

Name Path Type Description
code
code string

code

sqlState
sqlState string

sqlState

message
message string

message

statementHandle
statementHandle string

statementHandle

statementStatusUrl
statementStatusUrl string

statementStatusUrl

Check the Status and Get Results

Check the Status of the Execution of a Statement and Get the Results

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

statement handle
statementHandle True string

statement handle

Request ID
requestId string

Request ID

partition
partition integer

partition

Returns

Name Path Type Description
Partitions
Partitions array of object

Partitions

RowCount
Partitions.RowCount integer

RowCount

UncompressedSize
Partitions.UncompressedSize integer

UncompressedSize

CompressedSize
Partitions.CompressedSize integer

CompressedSize

Schema
Schema array of object

Schema

name
Schema.name string

name

database
Schema.database string

database

schema
Schema.schema string

schema

table
Schema.table string

table

precision
Schema.precision integer

precision

scale
Schema.scale integer

scale

type
Schema.type string

type

nullable
Schema.nullable boolean

nullable

byteLength
Schema.byteLength integer

byteLength

collation
Schema.collation string

collation

length
Schema.length integer

length

Data
Data array of object

Data

ID
Data.ID integer

ID

FIRSTNAME
Data.FIRSTNAME string

FIRSTNAME

LASTNAME
Data.LASTNAME string

LASTNAME

GENDER
Data.GENDER string

GENDER

AGE
Data.AGE integer

AGE

EMAIL
Data.EMAIL string

EMAIL

PHONE
Data.PHONE string

PHONE

EDUCATION
Data.EDUCATION string

EDUCATION

Rows
Metadata.Rows integer

Rows

Format
Metadata.Format string

Format

Code
Metadata.Code string

Code

StatementStatusUrl
Metadata.StatementStatusUrl string

StatementStatusUrl

RequestId
Metadata.RequestId string

RequestId

SqlState
Metadata.SqlState string

SqlState

StatementHandle
Metadata.StatementHandle string

StatementHandle

CreatedOn
Metadata.CreatedOn string

CreatedOn

Convert result set rows from array to objects

Convert result set rows from array to objects

Parameters

Name Key Required Type Description
schema
schema string

schema

data
data string

data

Returns

Name Path Type Description
data
data array of object

data

Submit SQL Statement for Execution

Submit a SQL Statement for Execution

Parameters

Name Key Required Type Description
Instance
Instance True string

The uri of your Snowflake instance (without https://)

Request ID
requestId string

The ID of the Request

Asynchronous
async boolean

Indicates if the query should be executed asynchronously

nullable
nullable boolean

If nullable is false, null values will be replaced by string

statement
statement string

The SQL statement to be executed - batches of statements not yet supported

timeout
timeout integer

Number of seconds before timeout occurs

database
database string

database

schema
schema string

schema

warehouse
warehouse string

warehouse

role
role string

role

timezone
timezone string

Time zone to use when executing the statement.

query tag
query_tag string

Query tag that you want to associate with the SQL statement.

binary output format
binary_output_format string

Output format for binary values.

date output format
date_output_format string

Output format for DATE values.

time output format
time_output_format string

Output format for TIME values.

timestamp output format
timestamp_output_format string

Output format for TIMESTAMP values.

timestamp ltz output format
timestamp_ltz_output_format string

Output format for TIMESTAMP_LTZ values.

timestamp_ntz_output_format
timestamp_ntz_output_format string

Output format for TIMESTAMP_NTZ values.

timestamp tz output format
timestamp_tz_output_format string

Output format for TIMESTAMP_TZ values.

multi statement count
multi_statement_count integer

Number of statements to execute when using multi-statement capability. 0 implies variable number of statements. Negative numbers are not allowed.

Returns

Name Path Type Description
Partitions
Partitions array of object

Partitions

RowCount
Partitions.RowCount integer

RowCount

UncompressedSize
Partitions.UncompressedSize integer

UncompressedSize

CompressedSize
Partitions.CompressedSize integer

CompressedSize

Schema
Schema array of object

Schema

name
Schema.name string

name

database
Schema.database string

database

schema
Schema.schema string

schema

table
Schema.table string

table

nullable
Schema.nullable boolean

nullable

precision
Schema.precision integer

precision

scale
Schema.scale integer

scale

byteLength
Schema.byteLength integer

byteLength

collation
Schema.collation string

collation

length
Schema.length integer

length

type
Schema.type string

type

Data
Data array of

Data

Rows
Metadata.Rows integer

Rows

Format
Metadata.Format string

Format

Code
Metadata.Code string

Code

StatementStatusUrl
Metadata.StatementStatusUrl string

StatementStatusUrl

RequestId
Metadata.RequestId string

RequestId

SqlState
Metadata.SqlState string

SqlState

StatementHandle
Metadata.StatementHandle string

StatementHandle

CreatedOn
Metadata.CreatedOn string

CreatedOn