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).

Querying datashares

Accessing shared data in Amazon Redshift

You can discover shared data using standard SQL interfaces, JDBC or ODBC drivers, and the Data API. You can also query data with high performance from familiar business intelligence (BI) and analytic tools. You can perform queries by referring to the objects from other Amazon Redshift databases that are both local to and remote from your cluster that you have permissions to access.

You can do so simply by staying connected to local databases in your cluster. Then you can create consumer databases from datashares to consume shared data.

After you have done so, you can perform cross-database queries joining the datasets. You can query objects in consumer databases using the 3-part notation (consumer_database_name.schema_name.table_name). You can also query using external schema links to schemas in the consumer database. You can query both local data and data shared from other clusters within the same query. Such a query can reference objects from the current connected database and from other nonconnected databases, including consumer databases created from datashares.

Accessing metadata for datashares in Amazon Redshift

To help cluster administrators discover datashares, Amazon Redshift provides a set of metadata views to list datashares. These views list datashares created in your cluster and also those received from other clusters within the same account, from other accounts, or other Amazon Regions. These views display the following information:

Use SVV_DATASHARES to view a list of all datashares created in your cluster (outbound) and shared from others (inbound). For more information, see SVV_DATASHARES.

Use SVV_DATASHARE_CONSUMERS to view a list of data consumers. For more information, see SVV_DATASHARE_CONSUMERS.

Use SVV_DATASHARE_OBJECTS to view a list of objects in all datashares created in your cluster (outbound) and shared from others (inbound). For more information, see SVV_DATASHARE_OBJECTS.

Integrating Amazon Redshift data sharing with business intelligence tools

To integrate data sharing with business intelligence (BI) tools, we recommend that you use the Amazon Redshift JDBC or ODBC drivers.

Amazon Redshift JDBC and ODBC drivers support the GetCatalogs API operation in the drivers, which returns the list of all databases including those created from datashares. The drivers also support downstream operations, such as GetSchemas, GetTables, and so on, that return data from all the databases that GetCatalogs returns. The drivers provide this support even when the catalog isn't explicitly specified in the call. For more information about JDBC or ODBC drivers, see Configuring connections in Amazon Redshift in the Amazon Redshift Management Guide.

You can't connect to consumer databases created from datashares directly. Connect to local databases on your cluster. If you have a connection switching user interface in your tool, the list of databases should include only the local cluster databases. The list should exclude consumer databases created from datashares to provide the best experience. You can use an option in the SVV_REDSHIFT_DATABASES view to filter databases.