SVV_DATASHARES - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SVV_DATASHARES

Use SVV_DATASHARES to view a list of datashares created on the cluster, and datashares shared with the cluster.

SVV_DATASHARES is visible to the following users:

  • Superusers

  • Datashare owners

  • Users with ALTER or USAGE permissions on a datashare

Other users can't see any rows. For information on the ALTER and USAGE permissions, see GRANT.

Table columns

Column name Data type Description
share_name varchar(128) The name of a datashare.
share_id integer The ID of the datashare.
share_owner integer The owner of the datashare.
source_database varchar(128) The source database for this datashare.
consumer_database varchar(128) The consumer database that is created from this datashare.
share_type varchar(8) The type of the datashare. Possible values are INBOUND and OUTBOUND.
createdate timestamp without time zone The date when datashare was created.
is_publicaccessible boolean The property that specifies whether a datashare can be shared to a publicly accessible cluster.
share_acl varchar(256) The string that defines the permissions for the specified user or user group for the datashare.
producer_account varchar(16) The ID for the datashare producer account.
producer_namespace varchar(64) The unique cluster identifier for the datashare producer cluster.
managed_by varchar(64) The property that specifies the Amazon service that manages the datashare.

Usage notes

Retrieving additional metadata – Using the integer returned in the share_owner column, you can join with usesysid in SVL_USER_INFO to get data about the datashare owner. This includes the name and additional properties.

Sample query

The following example returns the output for SVV_DATASHARES.

SELECT share_owner, source_database, share_type, is_publicaccessible FROM svv_datashares WHERE share_name LIKE 'tickit_datashare%' AND source_database = 'dev'; share_owner | source_database | share_type | is_publicaccessible --------------+-----------------+-------------+---------------------- 100 | dev | OUTBOUND | True (1 rows)

The following example returns the output for SVV_DATASHARES for outbound datashares.

SELECT share_name, share_owner, btrim(source_database), btrim(consumer_database), share_type, is_publicaccessible, share_acl, btrim(producer_account), btrim(producer_namespace), btrim(managed_by) FROM svv_datashares WHERE share_type = 'OUTBOUND'; share_name | share_owner | source_database | consumer_database | share_type | is_publicaccessible | share_acl | producer_account| producer_namespace | managed_by ----------------+-------------+-----------------+-------------------+------------+---------------------+-----------+-----------------+--------------------------------------+------------ salesshare | 1 | dev | | OUTBOUND | True | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d | marketingshare | 1 | dev | | OUTBOUND | True | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d |

The following example returns the output for SVV_DATASHARES for inbound datashares.

SELECT share_name, share_owner, btrim(source_database), btrim(consumer_database), share_type, is_publicaccessible, share_acl, btrim(producer_account), btrim(producer_namespace), btrim(managed_by) FROM svv_datashares WHERE share_type = 'INBOUND'; share_name | share_owner | source_database | consumer_database | share_type | is_publicaccessible | share_acl | producer_account | producer_namespace | managed_by ----------------+-------------+-----------------+-------------------+------------+---------------------+-----------+------------------+--------------------------------------+------------ salesshare | | | | INBOUND | False | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d | marketingshare | | | | INBOUND | False | | 123456789012 | 13b8833d-17c6-4f16-8fe4-1a018f5ed00d | ADX