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 Granting CREATE SHARE and IMPORT SHARE to Other Roles.

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

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.

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.

For example, using SQL:

GRANT IMPORTED PRIVILEGES ON DATABASE snow_sales TO ROLE sysadmin;

USE ROLE sysadmin;

GRANT IMPORTED PRIVILEGES ON DATABASE snow_sales TO ROLE public;

This example illustrates how to grant all imported privileges on the database to the SYSADMIN role and the SYSADMIN role then forwards the grant to the PUBLIC role.

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

SHOW GRANTS ON DATABASE snow_sales;

+---------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------+
| created_on                      | privilege | granted_on | name       | granted_to | grantee_name  | grant_option | granted_by    |
|---------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------|
| Thu, 15 Jun 2017 18:30:09 -0700 | OWNERSHIP | DATABASE   | SNOW_SALES | ROLE       | ACCOUNTADMIN  | true         | ACCOUNTADMIN  |
| Thu, 15 Jun 2017 18:36:46 -0700 | USAGE     | DATABASE   | SNOW_SALES | ROLE       | PUBLIC        | false        | SECURITYADMIN |
| Thu, 15 Jun 2017 18:30:09 -0700 | USAGE     | DATABASE   | SNOW_SALES | ROLE       | SECURITYADMIN | true         | ACCOUNTADMIN  |
+---------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------+

SHOW GRANTS ON SCHEMA snow_sales.aggregates_eula;

+---------------------------------+-----------+------------+----------------------------+------------+---------------+--------------+---------------+
| created_on                      | privilege | granted_on | name                       | granted_to | grantee_name  | grant_option | granted_by    |
|---------------------------------+-----------+------------+----------------------------+------------+---------------+--------------+---------------|
| Thu, 15 Jun 2017 16:41:40 -0700 | OWNERSHIP | SCHEMA     | SNOW_SALES.AGGREGATES_EULA |            |               | true         |               |
| Thu, 15 Jun 2017 18:30:09 -0700 | USAGE     | SCHEMA     | SNOW_SALES.AGGREGATES_EULA | ROLE       | ACCOUNTADMIN  | false        | ACCOUNTADMIN  |
| Thu, 15 Jun 2017 18:36:46 -0700 | USAGE     | SCHEMA     | SNOW_SALES.AGGREGATES_EULA | ROLE       | PUBLIC        | false        | SECURITYADMIN |
| Thu, 15 Jun 2017 18:30:09 -0700 | USAGE     | SCHEMA     | SNOW_SALES.AGGREGATES_EULA | ROLE       | SECURITYADMIN | true         | ACCOUNTADMIN  |
+---------------------------------+-----------+------------+----------------------------+------------+---------------+--------------+---------------+

SHOW GRANTS OF SHARE xy12345.sales_s;

+---------------------------------+-------+------------+---------------+---------------+
| created_on                      | share | granted_to | grantee_name  | granted_by    |
|---------------------------------+-------+------------+---------------+---------------|
| Thu, 15 Jun 2017 18:30:09 -0700 |       | ROLE       | ACCOUNTADMIN  | ACCOUNTADMIN  |
| Thu, 15 Jun 2017 18:36:46 -0700 |       | ROLE       | PUBLIC        | SECURITYADMIN |
| Thu, 15 Jun 2017 18:36:46 -0700 |       | ROLE       | SECURITYADMIN | ACCOUNTADMIN  |
+---------------------------------+-------+------------+---------------+---------------+

Querying a Shared Database

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

For example:

USE ROLE public;

USE DATABASE snow_sales;

SELECT * FROM aggregates_1;