关于使用 SQL 管理列表

Providers can use listings to share data products with accounts in any Snowflake region. To learn more about listings, see About sharing with listings.

供应商可以使用 SQL 命令创建和管理列表，并将其提供给特定使用者。要使用 SQL 共享列表，提供商需要完成以下任务：

Note

提供商不能提供付费、个性化的列表，或者有关私密数据交换的列表。

使用 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 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.

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

定义列表清单

To create a listing you must first create a listing manifest. Manifests are written in YAML (https://yaml.org/spec/ (https://yaml.org/spec/)), and include a prefix and required and optional fields.

例如，要创建一个包含列表术语的简单带标题列表，请定义一个清单，其类似于：

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

每个清单还包含其他部分，如：

auto_fulfillment: ...

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

简单的清单将包含：

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.

For additional examples and use cases associated with managing listings using SQL see Manage listings with SQL as a provider - examples.

使用 SQL 创建列表

To create a listing, you use the CREATE LISTING command, specifying a name and the listing details inline in 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. Additionally, listings can be described, shown, published and unpublished, and dropped.

Note

Creating a listing using SQL is conceptually similar to Share data or apps with specific consumers using a private listing. You should be familiar and comfortable with creating, viewing, and publishing listings using Snowsight and Provider Studio before creating listings using SQL. For more information, see Share data or apps with specific consumers using a private listing.

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;

Note

Listings are identified using the listing’s NAME. A listing NAME is the identifier used when initially creating the listing. In the example above, the listing name is MY1STLISTING. While title, subtitle and other listing characteristics can be altered, NAME cannot be altered by specifying a new name in yaml. Use ALTER LISTING … RENAME TO to rename a listing. Commands such as ALTER LISTING, SHOW LISTINGS, DESCRIBE LISTING, and DROP LISTING all use NAME to identify a listing. Listing NAME is not shown in Snowsight, which identifies listings by title.

For additional examples and use-cases associated with managing listings using SQL see Manage listings with SQL as a provider - examples.

使用 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 Publish a listing.

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

例如，要发布之前未发布的列表，请执行以下命令：

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;

For additional examples and use-cases associated with managing listings using SQL see Manage listings with SQL as a provider - examples.

使用 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.

要更改列表以包含更多字段，请执行类似以下所示的命令：

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 *"
$$;

For additional examples and use-cases associated with managing listings using SQL see Manage listings with SQL as a provider - examples.

使用 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.

要显示 MY1STLISTING 列表的详细信息，请执行类似以下所示的命令：

SHOW LISTINGS LIKE 'MY1STLISTING';

要显示您的角色可以访问的所有列表，请执行类似以下所示的命令：

SHOW LISTINGS;

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

DESC LISTING MY1STLISTING;

使用 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.

要取消发布列表，请执行类似以下所示的命令：

ALTER LISTING MY1STLISTING UNPUBLISH;

要删除列表，请执行类似以下所示的命令：

DROP LISTING IF EXISTS MY1STLISTING