Snowflake External Function

SeekWell’s Snowflake integration lets you send data from Snowflake into Google Sheets, Salesforce, Intercom and any other destination we support from your existing Snowflake Worksheets.

Create integration

CREATE OR REPLACE api integration seekwell
  api_provider = aws_api_gateway
  api_aws_role_arn = 'arn:aws:iam::145125969827:role/snowflake-external-lambda-functions'
  enabled = true
  api_allowed_prefixes = ('https://yjq9k9mqh3.execute-api.us-east-1.amazonaws.com/snowflake-external-function-stage')
;

Where:

  • aws_api_gateway is the Snowflake api_provider.

  • api_aws_role_arn is SeekWell’s ARN for our API.

  • api_allowed_prefixes is SeekWell’s API endpoint.
    Please note, we’d prefer to use a custom domain here, but Snowflake requires the default domain.

Snowflake external functions and API integrations do not support AWS custom domains. To access an Amazon API Gateway from Snowflake, use the default URL generated by AWS.

More information on External Functions can be found here.

Verify integration

DESCRIBE integration seekwell;

Create seekwell function

Open the web app and select command / control + k, then type “API” to generate an API key. 
CREATE OR REPLACE external function public.seekwell(x object)
  returns variant
    headers = (
      'seekwell-api-key' = 'INSERT_YOUR_API_KEY'
  )
  api_integration = seekwell
  as 'https://yjq9k9mqh3.execute-api.us-east-1.amazonaws.com/snowflake-external-function-stage/seekwell'
;

Grant roles access to function

grant all privileges on function public.seekwell(object) to role MY_ROLE;

Send data to Google Sheets

send data to google sheets

Required parameters

type

'sheets'

ss_id

The spreadsheet ID (long unique ID in the Sheet URL)

sheet

The sheet / tab name within the spreadsheet (for example, "Sheet1")

cell

The cell to start rows in (for example, "A1")

with to_sheets as (
	SELECT
	u.*,
	OBJECT_CONSTRUCT(
	    'type', 'sheets',
	    'ss_id', 'INSERT_YOUR_SPREADSHEET',
	    'sheet', 'users',
	    'cell', 'a1',
	    'title', 'User Report'
	) as seekwell
	FROM public.users AS u
	LIMIT 100
)
select
to_sheets.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sheets.*)
)::string as seekwell_result
from to_sheets as to_sheets

Note that you can use SQL in the OBJECT_CONSTRUCT, for example:

OBJECT_CONSTRUCT(
    'sheet', (select concat('run_on_', replace(current_date()::string, '-'))),
    'code', current_statement(),
    'columns', array_construct(
        'ENGLISH_NAME', 'HEADLINE_CATEGORY', 'EMOJI', 'HEADLINE_DESCRIPTION'
    )
    ) as seekwell
     'sheet', (select concat('run_on_', replace(current_date()::string, '-'))),

Send data to Salesforce

send data to salesforce

Our Salesforce destination (type = 'sfdc') takes the following parameters:

type

'sfdc'

object

for example, Account, Contact, Opportunity, etc.

operation

for example, insert, update, upsert, etc.

Simply alias your column name in Snowflake as the field name in Salesforce. Custom fields will end in __c. The field names can be found in the Object Manager in Setup.

with to_sfdc as (
    select
    u.email as "Email",
    sc.id as "Id",
    u.channel as "Channel__c",
    OBJECT_CONSTRUCT(
        'type', 'sfdc',
        'object', 'Contact',
        'operation', 'update'
    ) as seekwell
    from public.users as u
    join salesforce.contacts as sc on u.email = sc.email
)

select
to_sfdc.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sfdc.*)
)::string as seekwell_result
from to_sfdc as to_sfdc

Schedule Tasks

CREATE TASK mytask_hour
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
SELECT
u.*,
OBJECT_CONSTRUCT(
    'type', 'sheets',
    'ss_id', 'INSERT_YOUR_SPREADSHEET',
    'sheet', 'users',
    'cell', 'a1',
    'title', 'User Report',
) as seekwell
FROM public.users AS u
LIMIT 100;
Make sure you alias your destination OBJECT_CONSTRUCT as seekwell.

Ordering columns

If the order of your columns matters (for example, in Sheets) you can use the columns parameter to specify the order.

Column names are case-sensitive. By default, Snowflake uses upper-case, so you should use upper-case in the column array unless you are explicitly lower-casing your columns in the SELECT statement. 
OBJECT_CONSTRUCT(
	...
	'columns', array_construct(
        'DO', 'RE', 'MI'
)

Using SQL in your "seekwell" metadata

You can use SQL in the OBJECT_CONSTRUCT call to make the parameters dynamic, for example, if you wanted to change the Sheet name based on the current date you could write a query like the following:

OBJECT_CONSTRUCT(
		...
    'title', 'Weather Report',
		'sheet', (select concat('run_on_', replace(current_date()::string, '-'))),
    'code', current_statement(),
    ...
) as seekwell
Was this page helpful?
×