sfc-gh-vsekar/na_shareback.md

## na_shareback.md

      
    Raw
  

              na_shareback.md
            
          
    Native App : Shareback functionality

This markdown demonstrates the key implementation code for share-back data functionality offered via Snowflake Native App.
Doc: Request data sharing with app specifications
The approach demonstrated here as follows:

The Native App as part of its install, creates a database (app_shareback_db).
The app then creates a table (pos_transactions) in this database
The app then creates a share (app_shareback_share) and adds the table to this share.
The app creates a listing (app_shareback_share_listing) and attaches the share to the listing.

Manifest.yml

Ensure to reflect the privileges block for creating database(1), share(2) and listing(3) are requested, so that the  app requests in a consumer account.
# This is a manifest.yml file, a required component of creating a Snowflake Native App.
# This file defines properties required by the application package, including the location of the setup script and version definitions.
# Refer to https://docs.snowflake.com/en/developer-guide/native-apps/creating-manifest for a detailed understanding of this file.

manifest_version: 2

version:
  name: v1_0V
  label: v1_0V
  comment: The v1_0V version of a Snowflake Native App
  
artifacts:
  setup_script: scripts/setup.sql
  default_streamlit: code.home
  readme: README.md
  extension_code: true

configuration:
  trace_level: always
  log_level: debug

  # Ref: https://docs.snowflake.com/en/developer-guide/native-apps/event-definition#add-an-event-definition-to-the-manifest-file
  telemetry_event_definitions:
    - type: ERRORS_AND_WARNINGS
      sharing: MANDATORY
    - type: DEBUG_LOGS
      sharing: OPTIONAL
    - type: USAGE_LOGS
      sharing: MANDATORY

privileges:
  - CREATE DATABASE: # : 1
      description: "To create the database, where tables will be shared back to the provider"
  - CREATE SHARE: # : 2
      description: "Create share for sharing back the various assets from the app created database"
  - CREATE LISTING: # : 3
      description: "Create listing for sharing with the provider" 
Setup

In the Native App installation setup script, we will

create the resources
give appropriate grants to these resources

-- >> CREATE APPLICATION ROLES >> >>>>>>>>>>>>>>>>>>>>>>>>>>>
create application role if not exists app_admin;

-- This application role is meant to be used by users of the app
create application role if not exists app_public;

-- >> CREATE DATABASE AND ITS RESOURCES >>>>>>>>>>>>>>>>>>>>>>>>>>>
create database if not exists app_shareback_db 
      comment = 'Database for operational data for shareback app'
      ;

-- Ensure to grant the privileges otherwise the consumer will not be
-- able to access these resources
grant usage on database app_shareback_db
          to application role app_public;

grant all privileges on schema app_shareback_db.public
          to application role app_public;

create or alter table app_shareback_db.public.pos_transactions(
          pos_id string,
          pos_transaction_id string,
          item_sku string,
          quantity integer,
          price_per_quantity float,
          _updated_at timestamp default current_timestamp()
      );

grant all privileges on table app_shareback_db.public.pos_transactions 
        to application role app_public;

-- >> CREATE SHARE >>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create share if not exists app_shareback_share
    comment = 'Shareback of shareback app to provider';

grant usage on database app_shareback_db 
    to share app_shareback_share;

grant usage on schema app_shareback_db.public 
    to share app_shareback_share;
grant select on table app_shareback_db.public.pos_transactions 
    to share app_shareback_share;

grant select on all tables in schema app_shareback_db.public
    to share app_shareback_share;

-- >> CREATE LISTING >>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- We use a python based sproc to create the listing, as this gives a 
-- better flexibility.
create or replace procedure code.create_shareback_listing(org_name varchar, account_name varchar)
	returns variant
	language python
	runtime_version = '3.11'
	packages = ('snowflake-snowpark-python')
	imports = ('/python/create_shareback_listing.py')
	handler = 'create_shareback_listing.main'
;
call code.create_shareback_listing(current_organization_name(), current_account_name());

-- We need to set the target provider account.
alter application set specification app_shareback_appspec 
    type = listing 
    label = 'Share data with provider'
    description = 'Share data set to ACME App provider, for data enrichment.'
    listing = app_shareback_share_listing
    target_accounts = '<< PROVIDER SNOWFLAKE ACCOUNT >>'

    -- Optionally set the refresh schedule, for cross cloud/region scenario
    -- auto_fulfillment_refresh_schedule = '60 minute'
    ;
Script for creating listing

stored proc: code.create_shareback_listing

The following script will be called during the installation, it helps in creating the listing. The listing name is dynamic as ensure to contain the consumer account
information, as this will help the provider in differentiating the origination of the shared database.
import sys 
from snowflake.snowpark.session import Session

def main(p_session: Session ,org_name: str, account_name: str) -> str:
  sql_stmt = f'''
create  external listing if not exists app_shareback_share_listing 
share app_shareback_share as
$$
    title: "app_shareback_db {org_name}-{account_name}"
    description: "Shareback Listing from consumer for app_shareback_db"
    terms_of_service:
        type: "OFFLINE"
$$ 
publish=false 
review=false;'''
  p_session.sql(sql_stmt).collect();
  return sql_stmt
No results found