Team I am running below simple snowflake query, however it take more than hours to execute. i could see Attempt 01 queued for long and then execute,

from flytekit import kwtypes, workflow
from flytekitplugins.snowflake import SnowflakeConfig, SnowflakeTask


snowflake_task_no_io = SnowflakeTask(
    name="sql.snowflake.no_io",
    inputs={},
    query_template="select * from TEST_DEV.IDENTITY.TEST_123 ;",
    output_schema_type=None,
    task_config=SnowflakeConfig(
        account="xxxxx.us-east-1",
        database="TEST_DEV",
        schema="IDENTITY",
        warehouse="DEMO_WH",
    ),
)


@workflow
def no_io_wf():
    return snowflake_task_no_io()

Hi Satish, thanks for your question. Could you confirm if by running the following command to check if your secrets have successfully been stored in the pod.

kubectl edit cm flyte-propeller-config

cc @glamorous-carpet-83516

this is what i coud see.

# Please edit the object below. Lines beginning with a '#' will be ignored,
# and an empty file will abort the edit. If an error occurs while saving this file will be
# reopened with the relevant failures.
#
apiVersion: v1
data:
  admin.yaml: |
    admin:
      clientId: 'flytepropeller'
      clientSecretLocation: /etc/secrets/client_secret
      endpoint: flyteadmin:81
      insecure: true
    event:
      capacity: 1000
      rate: 500
      type: admin
  cache.yaml: |
    cache:
      max_size_mbs: 1024
      target_gc_percent: 70
  catalog.yaml: |
    catalog-cache:
      endpoint: datacatalog:89
      insecure: true
      type: datacatalog
  copilot.yaml: |
    plugins:
      k8s:
        co-pilot:
          image: <http://cr.flyte.org/flyteorg/flytecopilot:v0.0.24|cr.flyte.org/flyteorg/flytecopilot:v0.0.24>
          name: flyte-copilot-
          start-timeout: 30s
  core.yaml: |
    manager:
      pod-application: flytepropeller
      pod-template-container-name: flytepropeller
      pod-template-name: flytepropeller-template
    propeller:
      downstream-eval-duration: 30s
      enable-admin-launcher: true
      gc-interval: 12h
      kube-client-config:
        burst: 25
        qps: 100
        timeout: 30s
      leader-election:
        enabled: true
        lease-duration: 15s
        lock-config-map:
          name: propeller-leader
          namespace: flyte
        renew-deadline: 10s
        retry-period: 2s
      limit-namespace: all
      max-workflow-retries: 50
      metadata-prefix: metadata/propeller
      metrics-prefix: flyte
      prof-port: 10254
      queue:
        batch-size: -1
        batching-interval: 2s
        queue:
          base-delay: 5s
          capacity: 1000
          max-delay: 120s
          rate: 100
          type: maxof
        sub-queue:
          capacity: 1000
          rate: 100
          type: bucket
        type: batch
      rawoutput-prefix: <s3://xxxxxxxxxx/>
      workers: 40
      workflow-reeval-duration: 30s
    webhook:
      certDir: /etc/webhook/certs
      serviceName: flyte-pod-webhook
  enabled_plugins.yaml: |
    tasks:
      task-plugins:
        default-for-task-types:
          container: container
          container_array: k8s-array
          sidecar: sidecar
          spark: spark
        enabled-plugins:
        - container
        - sidecar
        - k8s-array
        - snowflake
        - spark
  k8s.yaml: |
    plugins:
      k8s:
        default-cpus: 100m
        default-env-vars: []
        default-memory: 100Mi
  resource_manager.yaml: |
    propeller:
      resourcemanager:
        type: noop
  spark.yaml: |
    plugins:
      spark:
        logs:
          all-user:
            cloudwatch-enabled: true
            cloudwatch-template-uri: <https://console.aws.amazon.com/cloudwatch/home?region=us-east-1#logStream:group=/aws/vpcflowLogs/dev-max-ml-flyte;prefix=test;streamFilter=typeLogStreamPrefix>
          mixed:
            cloudwatch-enabled: true
            cloudwatch-template-uri: <https://console.aws.amazon.com/cloudwatch/home?region=us-east-1#logStream:group=/aws/vpcflowLogs/dev-max-ml-flyte;prefix=test;streamFilter=typeLogStreamPrefix>
          system:
            cloudwatch-enabled: true
            cloudwatch-template-uri: <https://console.aws.amazon.com/cloudwatch/home?region=us-east-1#logStream:group=/aws/vpcflowLogs/dev-max-ml-flyte;prefix=test;streamFilter=typeLogStreamPrefix>
        spark-config-default:
        - spark.hadoop.fs.s3a.aws.credentials.provider: com.amazonaws.auth.DefaultAWSCredentialsProviderChain
        - spark.hadoop.mapreduce.fileoutputcommitter.algorithm.version: "2"
        - spark.kubernetes.allocation.batch.size: "50"
        - spark.hadoop.fs.s3a.acl.default: BucketOwnerFullControl
        - spark.hadoop.fs.s3n.impl: org.apache.hadoop.fs.s3a.S3AFileSystem
        - spark.hadoop.fs.AbstractFileSystem.s3n.impl: org.apache.hadoop.fs.s3a.S3A
        - spark.hadoop.fs.s3.impl: org.apache.hadoop.fs.s3a.S3AFileSystem
        - spark.hadoop.fs.AbstractFileSystem.s3.impl: org.apache.hadoop.fs.s3a.S3A
        - spark.hadoop.fs.s3a.impl: org.apache.hadoop.fs.s3a.S3AFileSystem
        - spark.hadoop.fs.AbstractFileSystem.s3n.impl: org.apache.hadoop.fs.s3a.S3A
        - spark.hadoop.fs.s3.impl: org.apache.hadoop.fs.s3a.S3AFileSystem
        - spark.hadoop.fs.AbstractFileSystem.s3.impl: org.apache.hadoop.fs.s3a.S3A
        - spark.hadoop.fs.s3a.impl: org.apache.hadoop.fs.s3a.S3AFileSystem
        - spark.hadoop.fs.AbstractFileSystem.s3a.impl: org.apache.hadoop.fs.s3a.S3A
        - spark.hadoop.fs.s3a.multipart.threshold: "536870912"
        - spark.blacklist.enabled: "true"
        - spark.blacklist.timeout: 5m
        - spark.task.maxfailures: "8"
  storage.yaml: |
    storage:
      type: s3
      container: "xxxxxxxxxx"
      connection:
        auth-type: iam
        region: us-east-1
      enable-multicontainer: false
      limits:
        maxDownloadMBs: 10
  task_logs.yaml: |
    plugins:
      logs:
        cloudwatch-enabled: true
        cloudwatch-log-group: /aws/vpcflowLogs/dev-max-ml-flyte
        cloudwatch-region: us-east-1
        kubernetes-enabled: true
        templates:
        - displayName: logs_test
          templateUris:
          - <https://console.aws.amazon.com/cloudwatch/home?region=us-east-1#logEventViewer:group=/aws/vpcflowLogs/dev-max-ml-flyte;stream=var.log.containers.__-.log>
kind: ConfigMap
kind: ConfigMap
metadata:
  annotations:
    <http://meta.helm.sh/release-name|meta.helm.sh/release-name>: max-ml-flyte
    <http://meta.helm.sh/release-namespace|meta.helm.sh/release-namespace>: flyte
  creationTimestamp: "2022-09-14T11:33:04Z"
  labels:
    <http://app.kubernetes.io/instance|app.kubernetes.io/instance>: max-ml-flyte
    <http://app.kubernetes.io/managed-by|app.kubernetes.io/managed-by>: Helm
    <http://app.kubernetes.io/name|app.kubernetes.io/name>: flyteadmin
    <http://helm.sh/chart|helm.sh/chart>: flyte-core-v0.1.10
  name: flyte-propeller-config
  namespace: flyte
  resourceVersion: "57800317"
  uid: xxxxxxxxxxxxxx

The secrets should not be in config map, but k8s secrets

@freezing-airport-6809 yes. i followed as per the documentation mentioned in above link

@mammoth-lion-71167 let me look, I think the name of the secret might be a problem- cc @glamorous-carpet-83516

@mammoth-lion-71167 Your flytepropeller pod yaml should look like below

...
        volumeMounts:
        - mountPath: /etc/flyte/config
          name: config-volume
        - mountPath: /etc/secrets/
          name: snowflake-auth
...
      volumes:
      - configMap:
          defaultMode: 420
          name: flyte-propeller-config
        name: config-volume
      - name: snowflake-auth
        secret:
          defaultMode: 420
          secretName: mysecret

@glamorous-carpet-83516 sorry for delay in my response. we are using the helm chart shared by the flyte. https://github.com/flyteorg/flyte/tree/master/charts/flyte-core

it looks like an access issue. Perhaps the token expired? Try regenerating the jwt token, and update the secret again.

let me check on that

it looks like snowflake updated their endpoint to /api/v2/statements, but we used

/api/statements

in propeller. https://github.com/flyteorg/flyteplugins/blob/16370216a6fc34412870ff1e12ca2a44ffbf1986/go/tasks/plugins/webapi/snowflake/plugin.go#L212

@glamorous-carpet-83516 thanks kevin. i will try and let you know

oh, sorry. Maybe there are some other issues in snowflake plugin. let me test it in my dev cluster, then I’ll get back to you

Hmm, this should break our end to end tests - cc @high-accountant-32689

@glamorous-carpet-83516 would you be able to share the dockerfile file for flytepropeller you have used to build?

any recommendation for snowflake 404 error code?

There is another endpoint I forgot to update to v2. I just updated all the endpoint to v2, now it’s working for me. feel free to try this image pingsutw/flytepropeller:fcfce5e490382297e88e8f8c5ca02084acf6a4d8

@glamorous-carpet-83516 no luck for me. this time i am getting 400 error code

E0928 11:34:01.227719       1 workers.go:102] error syncing 'cloudops-max-flyte-demo-development/fa396b5b859f64dc2a26': failed at Node[n0]. RuntimeExecutionError: failed during plugin execution, caused by: failed to execute handle for plugin [snowflake]: [SystemError] unknown execution phase [400]

could you send a post request to snowflake server? just want to make sure your jwt token is valid

@glamorous-carpet-83516 thanks kevin. i try. can you clarify, Are there any benefits of going with flyte snowflake plugin written in python + go language instead of executing snowflake query directly using python snowflake connector module?

So backend plugins can decouple form user code. You can rev the version, update code etc without updating user libraries. Also backend plugins do not run in a pod, they run in the engine very efficient. Last point backend plugins can recover from pod failures, as state is stored independently That being said you should use python plugins to write new plugins quickly and use backend plugins when efficiency etc is a bottleneck We are working to make it easier to write backend plugins, potentially in other languages @mammoth-lion-71167

@glamorous-carpet-83516 i suspect 400 issue be due to triple double quotes i added in query_template like """ sql query""" to escape special characters. after removing, it is working fine. job got succeeded.

@mammoth-lion-71167 sorry Kevin has been out. He will back and should be able to reply

@mammoth-lion-71167 sorry, seems like snowflake plugin doesn’t handle those special characters. trying to fix it.