SVV_DATASHARES - Amazon Redshift
Table columnsUsage notesSample query

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 AWS 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