Error: Unable to connect to Snowflake

[3goats]

Hi,

I'm looking at you excellent blog post and trying to get the demo working, however I'm getting the following error:

Error: Unable to connect to Snowflake: Request to Snowflake failed.
      at /Users/cbourne/development/starsnow_request/node_modules/serverless-snowflake-external-function-plugin/lib/snowflake.js:165:23
      at Object.callback (/Users/cbourne/development/starsnow_request/node_modules/snowflake-sdk/lib/connection/connection.js:121:13)
      at Timeout.requestCallback (/Users/cbourne/development/starsnow_request/node_modules/snowflake-sdk/lib/services/sf.js:1133:23)
      at Object.callback (/Users/cbourne/development/starsnow_request/node_modules/snowflake-sdk/lib/services/sf.js:639:37)
      at Request.<anonymous> (/Users/cbourne/development/starsnow_request/node_modules/snowflake-sdk/lib/http/base.js:107:19)
      at Request._callback (/Users/cbourne/development/starsnow_request/node_modules/lodash/lodash.js:10076:25)
      at Request.requestRetryReply [as reply] (/Users/cbourne/development/starsnow_request/node_modules/requestretry/index.js:115:19)
      at Request.<anonymous> (/Users/cbourne/development/starsnow_request/node_modules/requestretry/index.js:156:10)
      at processTicksAndRejections (node:internal/process/task_queues:93:5)

Could you clarify exactly what these environment variables are:

set -x SNOWFLAKE_ACCOUNT (I set this to my snowflake organisation )
set -x SNOWFLAKE_USERNAME (I set this to my snowflake username)
set -x SNOWFLAKE_DATABASE (I set this to a simple test database)
set -x SNOWFLAKE_PASSWORD (I set this to my snowflake password)
set -x SNOWFLAKE_ROLE SYSADMIN
set -x SNOWFLAKE_SCHEMA TEST

[tfoldi]

Yes, the environment variables looks fine. You can check the exact names or hardcode the values to the config file here:

https://github.com/starschema/starsnow_request/blob/master/serverless.yml#L21

The error you have is a generic timeout issue. Can you connect to other websites from your terminal? Are you in a locked-down corporate network that requires a proxy server?

[3goats]

"The error you have is a generic timeout issue. Can you connect to other websites from your terminal? Are you in a locked-down corporate network that requires a proxy server?"

Yes no problems with this, no not on a corporate network and no proxy servers involved.

[tfoldi]

can you confirm, that

1. you can access your instance with snowsql from the same terminal?
2. your account name should be its name only (not the URL):

Name assigned to your Snowflake account. If
                                  you are not on us-west-2 or AWS deployement,
                                  append the region and platform to the end,
                                  e.g., <account>.<region> or
                                  <account>.<region>.<platform>

[3goats]

OK thanks - think thats fixed it.

[3goats]

This is what I have now:

serverless deploy
Serverless: Running "serverless" installed locally (in service node_modules)
Serverless: Packaging service...
Serverless: Excluding development dependencies...
Snowflake: Adding permission to Snowflake for invoking API Gateway
Serverless: Uploading CloudFormation file to S3...
Serverless: Uploading artifacts...
Serverless: Uploading service starsnow-request.zip file to S3 (40.7 MB)...
Serverless: Validating template...
Serverless: Updating Stack...
Serverless: Checking Stack update progress...
....................
Serverless: Stack update finished...
Service Information
service: starsnow-request
stage: dev
region: us-west-2
stack: starsnow-request-dev
resources: 17
api keys:
  None
endpoints:
  POST - https://gveznagqq8.execute-api.us-west-2.amazonaws.com/dev/starsnow_request
  POST - https://gveznagqq8.execute-api.us-west-2.amazonaws.com/dev/starsnow_request_get
functions:
  starsnow_request: starsnow-request-dev-starsnow_request
  starsnow_request_get: starsnow-request-dev-starsnow_request_get
layers:
  None
Snowflake: Deploying Snowflake API and Functions
Serverless: Removing old service artifacts from S3...
Snowflake: Integration STARSNOW_REQUEST_DEV successfully created.
Snowflake: Created or replaced external function STARSNOW_REQUEST_GET
Snowflake: Created or replaced external function STARSNOW_REQUEST

However, when I run this from the example:

select key, value:"4. close"::float
from TABLE (FLATTEN(
        input => STARSNOW_REQUEST('https://www.alphavantage.co/query',
                                  object_construct('method', 'get',
                                                   'params', object_construct(
                                                           'function', 'TIME_SERIES_DAILY_ADJUSTED',
                                                           'symbol', 'SNOW',
                                                           'apikey', '<your_alphavantage_key>'))
            ): data:"Time Series (Daily)"));

I get an unkown function error SQL compilation error: Unknown function STARSNOW_REQUEST

My Snowflake config looks like this:

Organization	ZNMLXVI
Edition	Standard
Cloud	Google Cloud Platform
Region	Europe West 2 (London)

Does my instance need to be running on AWS for this to work ?

[tfoldi]

I guess you should, as far as I know, external functions are only supported on AWS and Azure instances

Still strange, that it does not give you an error message when deploying your functions. Did you use the same user and current role as configured in serverless when you checked the function?

[tfoldi]

just a note, I will test this tomorrow on my GCP instance (for the first time) and let you know the results