About managing listings using SQL¶

Prerequisites for working with listings using SQL¶

Before you can create listings using SQL, you must do the following:

• Review and accept the Snowflake Provider and Consumer Terms of Service.

• Prepare the data for your listing. See Preparing data for a listing.

• Review the Provider Policies.

• Configure account privileges.

Sharing a listing using SQL¶

To share a listing with specific consumers using SQL, do the following:

1. Complete Prerequisites for working with listings using SQL.

2. Optionally, create a Provider Profile to offer listings.

   For more information about becoming a provider, see Becoming a provider of listings.

3. Define a listing manifest

4. Create a listing using SQL

5. Publish a listing using SQL

When you create a listing, you create it from the account that has the data or application package in it. The role that attaches a data product to a listing and publishes the listing must be the same role that created, and therefore owns, the application package or share. You cannot transfer the OWNERSHIP privilege for a share.

If you use a different role to create and manage the listing, grant the MODIFY privilege on the listing to the role that owns the application package or share. For example:

Share or application package owner role:

OWNERSHIP privilege on the share or application package. MODIFY privilege on the listing.

Listing owner role:

OWNERSHIP privilege on the listing.

Global CREATE DATA EXCHANGE LISTING privilege.

Within the provider account, you can use one of the following to create and manage listings:

ACCOUNTADMIN:

If you use the ACCOUNTADMIN role to create and manage listings, the ORGADMIN role must first delegate privileges to set up auto-fulfillment.

Custom role:

If you use a custom role, the ORGADMIN role must first delegate privileges to set up auto-fulfillment to the ACCOUNTADMIN role, which can then be used to grant the relevant privileges to the custom role.

Some privileges can only be granted using Snowsight, while others can be granted using SQL or Snowsight.

For more information about granting sharing privileges, see Granting Privileges to Other Roles.

Limitations of working with listings using SQL¶

This preview has the following limitations:

• You cannot offer paid, personalized listings, or listings on private data exchanges.

Define a listing manifest¶

To create a listing you must first create a listing manifest.

Manifests are written in YAML (https://yaml.org/spec/), and include a prefix and required and optional fields.

For example, to create a simple titled listing with listing terms, define a manifest similar to:

title: A title for the listing.
subtitle: An optional subtitle.
description: A general description.
profile: Provider profile reference.
listing_terms: ...
targets: ...

Each manifest then includes additional sections, such as:

auto_fulfillment: ...

And a number of optional fields, such as data_dictionary, business_needs, and more.

A simple manifest would include:

title: "MyFirstListing"
subtitle: "Example listing"
description: "This is my first listing!"
listing_terms:
  type: "OFFLINE"
targets:
   accounts: ["Org1.Account1"]

For more information, see Listing manifest reference.

Create a listing using SQL¶

To create a listing, use the CREATE LISTING command, specifying a name and a YAML manifest that describes the listing. Listings created using CREATE LISTING … are automatically published. After a listing is created, you can alter it using ALTER LISTING, which includes unpublish and publish support.

Note that unlike Snowsight, when you create a listing using SQL the associated share must already exist.

Additionally, listings can be described, shown, published and unpublished, and dropped.

Before you create your listing, ensure you have completed all prerequisites.

For example, if you want to create a DRAFT listing my1stlisting from share myshare with title “My first SQL listing”, execute the following command:

CREATE EXTERNAL LISTING my1stlisting
SHARE myshare AS
$$
 title: "My first SQL listing"
 description: "This is my first listing"
 listing_terms:
   type: "OFFLINE"
 targets:
   accounts: ["Org1.Account1"]
$$ PUBLISH=FALSE REVIEW=FALSE;

Publish a listing using SQL¶

You can publish and un-publish listings using ALTER LISTING … PUBLISH and ALTER LISTING … UNPUBLISH.

For more information about publishing listings using Snowsight, see Publishing a listing.

Note that listings can be automatically published when created using CREATE LISTING.

For example, to publish the previously unpublished listing, execute the following command:

ALTER LISTING MY1STLISTING PUBLISH;

Additionally, before a listing can be dropped, it must be un-published. To un-publish a previously published listing, execute a command similar to:

ALTER LISTING MY1STLISTING UNPUBLISH;

Expand the definition of a listing using SQL¶

The previous example did not include targets or usage examples. You can use the ALTER LISTING to alter a listing’s characteristics. In this example, we update an existing listing to add targets and example SQL. Note that the original YAML manifest is extended to include new content.

To alter a listing to include additional fields execute a command similar to:

ALTER LISTING MY1STLISTING AS
$$
   title: "My First SQL Listing"
   description: "This is my first listing"
   listing_terms:
     type: "OFFLINE"
   targets:
     accounts: ["Org1.Account1"]
   usage_examples:
     - title: "this is a test sql"
       description: "Simple example"
       query: "select *"
$$;

Examine listings using SQL¶

Much like tables and other SQL elements, listings can be described and shown. DESCRIBE LISTING takes a single listing name as a parameter and provides details about that listing. SHOW LISTINGS can provide information about a group of listings, using a LIKE filter, or all listings created by a given account if no filter is provided.

To show the details of the MY1STLISTING listing, execute a command similar to:

SHOW LISTINGS LIKE 'MY1STLISTING';

To show all listings that your role has access to, execute a command similar to:

SHOW LISTINGS;

To describe the listing MY1STLISTING, execute a command similar to:

DESC LISTING MY1STLISTING;

Drop listings using SQL¶

To remove a listing, you must first un-publish the listing. You should be familiar with removing listings using Snowsight before dropping listings using SQL. For more information about removing listings using Snowsight, see Removing listings as a provider.

To un-publish a listing, execute a command similar to:

ALTER LISTING MY1STLISTING UNPUBLISH;

To drop a listing, execute a command similar to:

DROP LISTING IF EXISTS MY1STLISTING