Snowflake Data Share

This is for:

Business Analyst System Administrator

The Data Share (platform-ca | platform-eu | platform-au) feature of the Coveo Administration Console provides a convenient way to retrieve your data if you already have a Snowflake account. You can use this feature to extract and integrate your data into your account and leverage it using your business intelligence (BI) tool of choice.

Example

Barca Sports stores the entirety of their data in their own Snowflake data lake. Meanwhile, they have a Coveo-powered search page, and they would like to retrieve the corresponding data and add it to the data lake. As a result, they use the Data Share feature, which accesses their data from Coveo’s UA database and shares it to their Snowflake account.

You can have multiple data shares active simultaneously. For example, if your company has multiple organizations in different regions and you separate your data based on regional compliance, you can create a data share for each region. To learn more about data sharing with Snowflake, see Working with shares.

Initiate a data share

To set up data sharing, you need to access the Coveo Administration Console and the Snowflake console.

  1. On the Data Share (platform-ca | platform-eu | platform-au) subtab of the Raw Data page, click Initiate data share.

  2. In the Initiate data share panel that opens, enter the Snowflake account locator. If you’re unsure about which account locator to enter, you can find the correct one by entering SELECT CURRENT_ACCOUNT() in the Snowflake console.

    Note

    The account locator is case-sensitive, therefore when entering it in the Initiate data share panel, be sure to use the same casing as displayed in the Snowflake console.
    Initiate data share | Coveo

  3. Click the dropdown list and select the region where your Snowflake account is hosted. If you’re unsure about which region to select, you can find the correct one by entering SELECT CURRENT_REGION() in the Snowflake console.

  4. Click Initiate.

Your Snowflake account appears in the Data Share subtab.

Data Share

The Status column indicates the status of the share:

  • If the status is Invalid, the Snowflake account locator you entered or the region you selected may be incorrect.

  • If the status is Pending, the data share is still in the process of synchronizing.

Note

A data share with a Snowflake account in the same region as one of the Snowflake Disaster Recovery (DR) sites takes more time to synchronize, therefore the share may be unavailable for up to one hour.

Retrieve the shares in Snowflake

Once the data share is initiated and its status is shown as Active in the Coveo Administration Console, you need to retrieve the shares in Snowflake and create a database for each one. This is possible through the Snowsight UI or by running SQL queries in a Snowflake worksheet.

Multiple data models may be available to you, such as the CORE model and the UA model. Each data model results in an individual share. The retrieval steps must be completed for each data model, therefore, you may need to follow the steps below more than once to retrieve each share in Snowflake.

With the Snowsight UI:

  1. Ensure that you’re using the ACCOUNTADMIN role. You can do this by clicking your username in the lower-left corner of the console and selecting Switch Role. Then, select ACCOUNTADMIN from the list of roles.

  2. Find the Coveo data share by navigating to Data Products > Private Sharing.

  3. On the Private Sharing page, under Direct Shares, you should see one or multiple shares.

    Direct shares | Coveo

  4. Click the down arrow next to the share you want to retrieve.

  5. In the Get Data panel that opens, enter the database name of your choice.

  6. Select a role from the dropdown list (for example, USERADMIN).

  7. Click Get Data. The database is now created.

  8. (Optional) Repeat the previous steps until you’ve retrieved all shares.

With SQL queries:

  1. Ensure that you’re using the ACCOUNTADMIN role. You can do this by clicking your username in the lower-left corner of the console and selecting Switch Role. Then, select ACCOUNTADMIN from the list of roles.

  2. Find the Coveo data shares by running the following query:

    SHOW shares;

    1. In the SQL results table, you should see one or multiple shares.

      Show shares | Coveo

      For each share, you need to create a database. To do so, first take note of the information under the owner_account and the name columns.

    2. Run the following query to create the database:

      CREATE DATABASE <NAME> FROM SHARE <PROVIDER_ACCOUNT>.<SHARE_NAME>

      Where you replace:

      • <NAME> with the database name of your choice.

      • <PROVIDER_ACCOUNT> with the information obtained from the owner_account column following the initial query.

      • <SHARE_NAME> with the information obtained from the name column following the initial query.

        For example, your organization is with Barca Sports, so you decide to name the database BARCA1. The information obtained from the owner_account and name columns are COVEODEV_AWS_US_EAST_1 and COVEO_CORE_MODEL_V001, respectively. Your query therefore would look like the following:

        CREATE DATABASE BARCA1 FROM SHARE COVEO.COVEODEV_AWS_US_EAST_1.COVEO_CORE_MODEL_V001

    3. Run the following statement to grant the database access to the user role:

      GRANT IMPORTED PRIVILEGES ON DATABASE <NAME> TO ROLE <ROLE>;

      Where you replace:

      • <NAME> with the database name that you just created.

      • <ROLE> with the user role to which you want to provide access (for example, USERADMIN).

  3. (Optional) Repeat the previous steps until you’ve retrieved all shares.

Upon completion, you can begin exploring your Coveo data on the Snowflake console.

Notes

  • When you’ve shared the data with a specific account, the users within that account can view the data but can’t edit it.

  • The column names in the Coveo data table are case sensitive. If the database is set to case insensitive, where QUOTED_IDENTIFIERS_IGNORE_CASE is set to TRUE, you won’t be able to run queries against the data table. Therefore, in cases where your database is case insensitive and you can’t revert the global configuration, we recommend altering your current session to make it case sensitive:

    -- Set the default behavior
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = false;

    For more information on the QUOTED_IDENTIFIERS_IGNORE_CASE parameter, see Impact of changing the parameter.

Remove data share

  1. In the Snowflake console, using the ACCOUNTADMIN role, run the following statement to remove the database:

    DROP DATABASE <NAME>;

    Where you replace <NAME> with the database you want to remove.

  2. On the Data Share (platform-ca | platform-eu | platform-au) subtab of the Raw Data page, click the account name from the Data Share list, and then click Revoke.

Regions and sites

Data sharing is currently supported for a limited number of regions. For information on sharing data in different regions, see Sharing Data With Data Consumers in a Different Region and Cloud Platform.

Region Sites

US

  • Main: US East (N. Virginia) - us-east-1

  • DR: US West (Oregon) - us-west-2

Canada

Main: Canada (Central) - ca-central-1

Europe

  • Main: EU (Ireland) - eu-west-1

  • DR: EU (Frankfurt) - eu-central-1

Asia

Main: Asia Pacific (Sydney) - ap-southeast-2

Required privileges

Privileges required to view or manage Data Share.

Action Service - Domain Required access level

Share data

Organization - Organization

View

Analytics - Snowflake management

Edit

Analytics - Administrate

Allowed

What’s next?

Create custom reports by building your own queries in the Snowflake console.