Consume imported data

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 Enable 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 imported data, you do not need to perform any of these tasks because they have already been completed by an administrator from the provider account.

General limitations for imported databases

Imported databases have the following limitations for consumers:

  • Imported 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 an imported database or any schemas/tables in the database.

    • Time Travel for an imported database or any schemas/tables in the database.

    • Editing the comments for an imported database.

  • Imported databases and all the objects in the database cannot be re-shared with (imported by) other accounts.

  • Imported databases cannot be replicated.

Viewing available shares

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

Snowsight:

Select Data Products » Private Sharing, then select Shared With You. You can view the following:

  • Privately Shared Listings that have been shared with you. You can also view Data exchange listings that you have access to.

  • Direct shares that have been shared with you. Depending on the share status, shares are grouped into two sections:

    • Direct shares that are ready to get (i.e. a database has not been created from the share).

    • Direct shares that have been imported into a database and are ready to query.

To view Snowflake Marketplace listings that have been imported to a database and are ready to query, in the navigation menu, select Data Products » Marketplace. For more information, see Exploring Listings.

Classic Console:

Select Shares Shares tab, then select 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;
Copy

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 name of each share, in the form of share_name (e.g. SALE_S).

  • The owner_account column displays the account name that provided each share, in the form of orgname.account_name.

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

+-------------------------------+----------+----------------------+---------------+-----------------------+------------------+--------------+----------------------------------------+---------------------+
| created_on                    | kind     | owner_account        | name          | database_name         | to               | owner        | comment                                | listing_global_name |                  |
|-------------------------------+----------+----------------------+---------------+-----------------------+------------------+--------------+----------------------------------------|---------------------|
| 2017-07-09 19:18:09.821 -0700 | INBOUND  | SNOW.XY12345         | SALES_S2      | UPDATED_SALES_DB      |                  |              | Transformed and updated sales data     |                     |
| 2017-06-15 17:02:29.625 -0700 | OUTBOUND | SNOW.MY_TEST_ACCOUNT | SALES_S       | SALES_DB              | XY12345, YZ23456 | ACCOUNTADMIN |                                        |                     |
+-------------------------------+----------+----------------------+---------------+-----------------------+------------------+--------------+----------------------------------------+---------------------+

The following 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 |
+----------+------------------------------------+---------------------------------+
Copy

The share consists of one schema, aggregates_eula, with one table, aggregate_1. 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 the web interface or SQL:

Snowsight:

  1. Sign in to Snowsight.

  2. Select Data Products » Private Sharing.

  3. Select the Shared with You tab.

  4. In the Ready to Get section, select the share that you want to create a database for.

  5. Set a database name and the roles that are permitted to access the database.

  6. Select Get Data.

Classic Console:

  1. Select Shares Shares tab, then select the Inbound toggle.

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

  3. Select 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>
Copy

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 imported 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 an Imported 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;
Copy

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

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 includes the name of the database that was created from the share:

SHOW SHARES;
Copy
+-------------------------------+----------+----------------------+---------------+-----------------------+------------------+--------------+----------------------------------------+---------------------+
| created_on                    | kind     | owner_account        | name          | database_name         | to               | owner        | comment                                | listing_global_name |
|-------------------------------+----------+----------------------+---------------+-----------------------+------------------+--------------+----------------------------------------|---------------------|
| 2017-07-09 19:18:09.821 -0700 | INBOUND  | SNOW.XY12345         | SALES_S2      | UPDATED_SALES_DB      |                  |              | Transformed and updated sales data     |                     |
| 2017-06-15 17:02:29.625 -0700 | OUTBOUND | SNOW.MY_TEST_ACCOUNT | SALES_S       | SALES_DB              | XY12345, YZ23456 | ACCOUNTADMIN |                                        |                     |
+-------------------------------+----------+----------------------+---------------+-----------------------+------------------+--------------+----------------------------------------+---------------------+

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

Granting privileges on an imported 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 an imported 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 Console:

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 an imported 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;
    Copy

  2. Role r1 grants IMPORTED PRIVILEGES on database snow_sales to role r2:

    grant imported privileges on database snow_sales to role r2;
    Copy

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

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 imported 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;
Copy

Step 2: Grant database roles to your account-level roles

Grant 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, see the database roles available, then grant database role c1.r1 to the analyst role in your account:

SHOW DATABASE ROLES in DATABASE c1;
GRANT DATABASE ROLE c1.r1 TO ROLE analyst;
Copy

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 an imported database (in this topic).

  • To create streams on shared views:

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

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

  • To create streams on shared tables:

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

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

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 an imported database

Querying an imported 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;
Copy
Language: English