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 SHARE global privilege) to perform these tasks. For more details about the IMPORT SHARE 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:

Classic 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 |
| VIEW     | <DB>.AGGREGATES_EULA.AGGREGATE_1_v | 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:

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

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

  • A share can only be consumed once per account.

  • To see the objects that are being shared before creating a database, use the DESCRIBE SHARE command.

  • When a database is created from a share, only the role used to create the database can access objects in the database by default. For instructions on granting access to other roles, see Granting Privileges on a Shared Database (in this topic).

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 |
| VIEW     | SNOW_SALES.AGGREGATES_EULA.AGGREGATE_1_v | Thu, 15 Jun 2017 17:03:16 -0700 |
+----------+------------------------------------------+---------------------------------+

Granting Privileges on a Shared Database

The instructions to grant access to objects in a share differ depending on whether the provider segmented the objects in a share using database roles. This option associates different objects in the share with different database roles.

Note that a single share can include both objects that are accessible via database roles and objects that are not associated with a database role.

Option 1: Objects in a Share Not Associated with a Database Role

Allow users to access objects in a share by granting the IMPORTED PRIVILEGES privilege on a shared database to one or more roles in your account.

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:

Classic 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> 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;
    

Option 2: Objects in a Share Associated with a Database Role

Allow users to access objects in a share by granting the appropriate database role in the shared database to one or more roles in your account.

Step 1: Create a Database From the Share

Create a database from the share using CREATE DATABASE … FROM SHARE.

Executing this command requires a role with the global CREATE DATABASE and IMPORT SHARE privileges.

For example, create databases c1 from provider provider1 and share share1:

CREATE DATABASE c1 FROM SHARE provider1.share1;

Step 2: Grant Shared Database Roles to Your Account-level Roles

Grant shared database roles to roles in your account to allow users with those roles to access database objects in the share.

Use the role that you used to create the database from the share.

For example, grant database role c1.r1 to the analyst role in your account.

GRANT DATABASE ROLE c1.r1 TO ROLE analyst;

Creating Streams on Shared Views or Tables

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

The role used to execute the SQL statements in this section must have the required grants on the shared table or secure view. For information, see Granting Privileges on a Shared Database (in this topic).

  • To create streams on shared views:

    CREATE STREAM <name> ON VIEW <shared_db>.<schema>.<view>;
    

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

    CREATE STREAM aggregate_1_v_stream ON VIEW snow_sales.aggregates_eula.aggregate_1_v;
    
  • To create streams on shared tables:

    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

  • The data provider must enable change tracking on views or tables before you can create streams on these objects. If you cannot create streams on a desired shared object, contact the data provider to consider enabling change tracking on the object.

  • 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 is TRUE, the stream may be stale. In practice, reading from the stream may succeed for some time after the expected STALE_AFTER. However, the stream may become stale at any time during this period.

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;
Back to top