how to select value from "storage integration" in snowflake?

user_4675712 0
Viewed 404

I have created storage integration by executing below command

create or replace storage integration stager 
       type = external_stage
       storage_provider = s3
       enabled = true
       storage_aws_role_arn = 'arn:aws:iam::24545426:role/test'
       storage_allowed_locations = ('s3://testb/')

after that I have executed DESC INTEGRATION stager

i got the result like

enter image description here

now I want select STORAGE_AWS_EXTERNAL_ID and property value of the stager .

how to select STORAGE_AWS_EXTERNAL_ID and property value using query or python?

snowflake-schema snowflake-cloud-data-platform python-3.x
1 Answers

You can use RESULT_SCAN function to process the outcome of another query (using SQL):

https://docs.snowflake.com/en/sql-reference/functions/result_scan.html

https://docs.snowflake.com/en/sql-reference/functions/result_scan.html#examples-using-describe-and-show-commands

For python:

import snowflake.connector

ctx = snowflake.connector.connect(
      ...
) 


cs = ctx.cursor()

try:
    sql = "DESC INTEGRATION stager"
    cs.execute( sql )
    for (c_property, c_type, c_value, c_default) in cs:
        if c_property == "STORAGE_AWS_EXTERNAL_ID":
            print('{0}, {1}'.format(c_property, c_value))


finally:
    cs.close()

This is the result of the above Python script:

STORAGE_AWS_EXTERNAL_ID, WX65722_SFCRole=2_GpY+ZF0b41Nu3d2ZDFYPfCUbBxk=
user_125509650
Related