Data Consumers

This topic describes the tasks associated with creating databases from shares made available by data providers and then using the databases for queries and other operations.

You must use the ACCOUNTADMIN role (or a role granted the IMPORT SHARES global privilege) to perform these tasks. For more details about the IMPORT SHARES privilege, see Enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks.

Note

The tasks described in this topic do not apply to reader accounts. If you are using a reader account to consume shared data, you do not need to perform any of these tasks because they have already been completed by an administrator from the provider account.

In this Topic:

General Limitations for Shared Databases

Shared databases have the following limitations for consumers:

  • Shared databases are read-only. Users in a consumer account can view/query data, but cannot insert or update data, or create any objects in the database.

  • The following actions are not supported:

    • Creating a clone of a shared database or any schemas/tables in the database.

    • Time Travel for a shared database or any schemas/tables in the database.

    • Editing the comments for a shared database.

  • Shared databases and all the objects in the database cannot be forwarded (i.e. re-shared with other accounts).

Viewing Available Shares

You can view the shares that are available to consume in your account using either the web interface or SQL:

Web Interface

Click on Shares Shares tab, then click the Inbound toggle.

The page displays all the shares available to your account. The Database column displays the name of the database created from the share. If the column is empty, a database has not yet been created (in your account) from the share.

SQL

Execute a SHOW SHARES or DESCRIBE SHARE statement.

For example, using SQL:

SHOW SHARES;

+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+
| created_on                      | kind    | name                    | database_name         | to | owner | comment                                     |
|---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------|
| Thu, 15 Jun 2017 17:02:29 -0700 | INBOUND | XY12345.SALES_S         |                       |    |       |                                             |
| Thu, 15 Jun 2017 17:32:44 -0700 | INBOUND | XY12345.SALES_S2        |                       |    |       |                                             |
| Sat, 09 Jul 2016 19:18:09 -0700 | INBOUND | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |    |       | Sample data sets provided by Snowflake      |
+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+

The output shows:

  • Two shares, sales_s and sales_s2 are available. INBOUND in the kind column specifies that a data provider made the share available to your account to consume.

  • The name column displays the fully-qualified name of each share, in the form of provider_account.share_name (e.g. xy12345.sales_s).

  • If the database_name column is empty, a database has not been created yet (in your account) from the share.

The next example uses the DESCRIBE SHARE command to show the objects (database, schemas, and tables) that are in the sales_s share:

DESC SHARE xy12345.sales_s;

+----------+----------------------------------+---------------------------------+
| kind     | name                             | shared_on                       |
|----------+----------------------------------+---------------------------------|
| DATABASE | <DB>                             | Thu, 15 Jun 2017 17:03:16 -0700 |
| SCHEMA   | <DB>.AGGREGATES_EULA             | Thu, 15 Jun 2017 17:03:16 -0700 |
| TABLE    | <DB>.AGGREGATES_EULA.AGGREGATE_1 | Thu, 15 Jun 2017 17:03:16 -0700 |
+----------+----------------------------------+---------------------------------+

The share consists of one schema, aggregates_eula, with one table, aggregate_1. Note that each object name, including the database itself, is prefixed with <DB>. This indicates a database has not been created yet (in your account) from the share.

Creating a Database from a Share

You can create a database from a share using either the web interface or SQL:

Web Interface
  1. Click on Shares Shares tab, then click the Inbound toggle.

  2. Select a share from which a database has not yet been created (i.e. the Database column is empty).

  3. Click Create Database from Share.

  4. Follow the prompts provided in the dialog.

Note that, at creation time, you can choose to grant the IMPORTED PRIVILEGES privilege to one or more roles in the system or you can grant the privilege later.

SQL

Execute a CREATE DATABASE statement with the following data sharing-specific syntax:

CREATE DATABASE <name> FROM SHARE <provider_account>.<share_name>

Where provider_account is the name of the account that provided the share and share_name is the name of the share from which to create the database.

Note

SQL Examples

The following example creates a new database named snow_sales in your account from the sales_s share:

CREATE DATABASE snow_sales FROM SHARE xy12345.sales_s;

List the new snow_sales database:

SHOW DATABASES LIKE 'snow%';

+---------------------------------+-----------------------+------------+------------+-------------------------+--------------+---------+---------+----------------+
| created_on                      | name                  | is_default | is_current | origin                  | owner        | comment | options | retention_time |
|---------------------------------+-----------------------+------------+------------+-------------------------+--------------+---------+---------+----------------|
| Sun, 10 Jul 2016 23:28:50 -0700 | SNOWFLAKE_SAMPLE_DATA | N          | N          | SFC_SAMPLES.SAMPLE_DATA | ACCOUNTADMIN |         |         | 1              |
| Thu, 15 Jun 2017 18:30:08 -0700 | SNOW_SALES            | N          | Y          | xy12345.SALES_S         | ACCOUNTADMIN |         |         | 1              |
+---------------------------------+-----------------------+------------+------------+-------------------------+--------------+---------+---------+----------------+

In this example, the origin column indicates the fully-qualified name of the share from which the database was created.

Similarly, the output of SHOW SHARES and DESC SHARE now includes the name of the database that was created from the share:

SHOW SHARES;

+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+
| created_on                      | kind    | name                    | database_name         | to | owner | comment                                     |
|---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------|
| Thu, 15 Jun 2017 17:02:29 -0700 | INBOUND | xy12345.SALES_S         | SNOW_SALES            |    |       |                                             |
| Thu, 15 Jun 2017 17:32:44 -0700 | INBOUND | xy12345.SALES_S2        |                       |    |       |                                             |
| Sat, 09 Jul 2016 19:18:09 -0700 | INBOUND | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |    |       | Sample data sets provided by Snowflake      |
+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+

DESC SHARE xy12345.sales_s;

+----------+----------------------------------------+---------------------------------+
| kind     | name                                   | shared_on                       |
|----------+----------------------------------------+---------------------------------|
| DATABASE | SNOW_SALES                             | Thu, 15 Jun 2017 17:03:16 -0700 |
| SCHEMA   | SNOW_SALES.AGGREGATES_EULA             | Thu, 15 Jun 2017 17:03:16 -0700 |
| TABLE    | SNOW_SALES.AGGREGATES_EULA.AGGREGATE_1 | Thu, 15 Jun 2017 17:03:16 -0700 |
+----------+----------------------------------------+---------------------------------+

Creating Table Streams on Shared Tables

Note

Support for table streams on shared tables is provided as a preview feature.

Creating table streams on shared tables enables you to track data manipulation language (DML) changes made in those tables. This functionality is similar to creating and using streams on “local” tables (i.e. in the same account as the stream).

To create table streams on shared tables:

  1. Using a role that has the IMPORTED PRIVILEGES privilege, create a table stream on a shared table:

    CREATE STREAM <name> ON TABLE <shared_db>.<schema>.<table>;
    

    For example, create a table stream on the shared aggregate_1 table in the snow_sales.aggregates_eula database and schema:

    CREATE STREAM aggregate_1_stream ON TABLE snow_sales.aggregates_eula.aggregate_1;
    

    For more information on creating streams, see CREATE STREAM.

Note

To avoid allowing a stream to become stale, consume the stream records within a transaction during the retention period for the table. Contact the data provider to determine the data retention period for the table.

To determine whether a stream has become stale, execute the DESCRIBE STREAM or SHOW STREAMS command. In the command output, when the STALE column value for the stream is TRUE, the stream is stale.

Granting Privileges on a Shared Database

Only the roles used to create a database from a share can use the database initially. However, access can be granted to other roles through a special data sharing-specific privilege named IMPORTED PRIVILEGES.

A role can grant IMPORTED PRIVILEGES on an imported database only when it either:

  • Owns the imported database (i.e. has the OWNERSHIP privilege on the database).

  • Was granted the MANAGE GRANTS global privilege.

Assigning IMPORTED PRIVILEGES to Other Roles

You can assign this role to other roles using either the web interface or SQL:

Web Interface

Click on Databases Databases tab » <shared_db_name>.

In the side panel, click on Grant Privileges and assign the privilege to one or more roles.

SQL

Execute a GRANT <privileges> … TO ROLE statement.

To see the roles that have USAGE privileges on a shared database, use the web interface or the SHOW GRANTS command.

SQL Examples

  1. Role r1 creates database snow_sales from share xy12345.sales_s:

    use role r1;
    create database snow_sales from share xy12345.sales_s;
    
  2. Role r1 grants IMPORTED PRIVILEGES on database snow_sales to role r2:

    grant imported privileges on database snow_sales to role r2;
    
  3. Since r2 does not have the OWNERSHIP privilege on the database, to be able to perform either of the following grant or revoke operations, role r2 must hold the MANAGE GRANTS privilege on the account:

    use role r2;
    grant imported privileges on database snow_sales to role r3;
    revoke imported privileges on database snow_sales from role r3;
    

Querying a Shared Database

Querying a shared database is the same as querying any other database in your account.

For example:

USE ROLE r1;

USE DATABASE snow_sales;

SELECT * FROM aggregates_1;