以使用者身份通过 SQL 管理列表 – 示例

以下是使用者可以通过 SQL 命令以编程方式完成的常见任务示例：

显示可用列表

显示运行命令的使用者可用的列表。有关 SHOW AVAILABLE LISTINGS 命令的更多信息，请参阅 SHOW AVAILABLE LISTINGS。

描述

备注

显示可用列表。

IS_SHARED_WITH_ME = TRUE 用于仅显示与运行命令的使用者私下共享的列表。IS_IMPORTED = TRUE 用于仅显示导入的列表。

 
SHOW AVAILABLE LISTINGS

描述可用列表

After running SHOW AVAILABLE LISTINGS to identify the available listings and the global listing names, a consumer can run DESCRIBE AVAILABLE LISTING to return descriptions of the columns in the listings that are available to them. For more information about the DESCRIBE AVAILABLE LISTING command, see DESCRIBE AVAILABLE LISTING.

描述

备注

描述列表列。

listing_global_name 用于识别要描述的特定全局列表。当 is_ready_for_import 列为 TRUE 时，数据已存在于该区域中，使用者可以立即导入数据。

 
DESCRIBE AVAILABLE LISTING < listing_global_name >

请求列表并自动轮询可用性

After running SHOW AVAILABLE LISTINGS to identify the available listings, a consumer can use the SYSTEM$REQUEST_LISTING_AND_WAIT stored procedure to request a listing and automatically poll for availability. A consumer can also use this stored procedure when the is_ready_for_import column is FALSE. For more information about the SYSTEM$REQUEST_LISTING_AND_WAIT stored procedure, see SYSTEM$REQUEST_LISTING_AND_WAIT.

描述

备注

请求特定列表并轮询可用性。

<timeout_mins> 指定列表履行等待期（以分钟为单位）。默认值为 240 分钟或 4 小时。

当请求的列表变得可用或已经可用时，将返回消息 Success: Listing <listing_global_name> is ready to be imported。

如果超过超时时间，则返回消息 Error: Timed out waiting for the listing to be available after <timeout_mins> min(s)。

要请求列表而不等待列表履行，输入 0（零）即可返回 <timeout_mins> value. When the value is 0, the message Success:Listing <listing_global_name> requested successfully, but not waiting to confirm fulfillment。

 
CALL SYSTEM$REQUEST_LISTING_AND_WAIT( ' <listing_global_name> ' [ , <timeout_mins>. ] );

从列表中创建数据库

After requesting a listing, a consumer can use the CREATE DATABASE ... FROM LISTING ... command to create a database from a listing. For more information about the CREATE DATABASE ... FROM LISTING ... command, see CREATE DATABASE ... FROM LISTING ....

描述

备注

从列表中创建数据库。

<name> 指定数据库标识符。对于您的账户必须是唯一的。标识符必须以字母字符开头，且不能包含空格或特殊字符，除非整个标识符字符串放在双引号内。例如 "My object"。放在双引号内的标识符也区分大小写。

 
CREATE DATABASE <name> FROM LISTING '<listing_global_name>';

端到端示例

以下示例显示如何使用上述 SQL 命令以使用者身份管理列表。该示例假设使用者已有权访问 COVID-19 数据命名 GZ1MXZFTF1 的列表，并且该列表在使用者所在区域可用。该示例还假设使用者已获授 sysadmin 角色，这是从列表创建数据库所必需的角色。

-- Switch to sysadmin role
USE ROLE sysadmin;

-- Show available listings with a filter for shared listings
-- Note that you can optionally filter for private shared listings using IS_SHARED_WITH_ME = TRUE
-- The example assumes that the response returns a listing with a listing_global_name of GZ1MXZFTF1
SHOW AVAILABLE LISTINGS;

-- Get the global name and title of listings and filter on the title
SELECT "global_name", "title"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  WHERE "is_imported" = false
    AND "title" LIKE '%COVID-19%';

-- Request the listing returned in `SHOW AVAILABLE LISTINGS` and wait for completion
CALL SYSTEM$REQUEST_LISTING_AND_WAIT('GZ1MXZFTF1');

-- Accept legal terms for the listing. Email verification is required to create the database from listing GZ1MXZFTF1
CALL SYSTEM$ACCEPT_LEGAL_TERMS('DATA_EXCHANGE_LISTING', 'GZ1MXZFTF1');

-- Create database from the listing
CREATE DATABASE test_california_covid_import
  FROM LISTING 'GZ1MXZFTF1';

-- Use the new database
USE DATABASE test_california_covid_import;

-- Query the 'COVID.CASES' table and limit the results to 100 rows
SELECT * FROM COVID.CASES LIMIT 100;