Create database, schema, table then grant permission to Snowflake user role for client (Dremio) to access. - Snowflake - All version – Dremio Support

Summary

This is a step by step set of instruction to create database, schema, table then grant permission to Snowflake user role for client (Dremio) to access.

Reported Issue

Requests for assistance in creating database, schema, table with Snowflake

Overview

Snowflake has user access control. It controls users’ access to database, schema, and table objects through the assignment of privileges to roles. Any external clients connect to Snowflake with a specific user role and want to access Snowflake’s database, schema, or table; it requires granting the privilege of the database, schema, or table to the role that external client (like Dremio) uses in the connection to connect to Snowflake.

Relevant Versions, Tools, and Integrations

All Dremio versions

Steps to Resolve

the following steps will be taken:

Create Snowflake warehouse
Create Snowflake database
Create Snowflake schema
Create Snowflake table
Grant privilege user role to access warehouse, database, schema, and table
Display the privilege of the database and schema that just created
Connect Dremio to Snowflak, then retrieve data from the Snowflake table

Create Snowflake warehouse

Click on Admin on the left
Click on Warehouses
Click on + Warehouse button on the right
Enter the warehouse name

Grant permission to the role name PUBLIC to access the warehouse name KB_WH

Create Snowflake database

Click on Data on the left
Click on Databases
Then click on the “+ Database” on the top right
Enter database name (ex: KB_DB1)
Click Create button to create the database name KB_DB1

Grant permission to role name PUBLIC to access the database name KB_DB1

Create Snowflake schema

Click on the database name KB_DB1 that just created
Click on the “+ Schema” button on the top right
Enter the schema name (ex: KB_SC1)
Click Create button to create the schema name KB_SC1

Grant permission to the role name PUBLIC to access schema name KB_SC1

Create Snowflake table

Click on the schema name that you want to create a table under it (ex: KB_SC1)
Click on the dropdown Create button on the top right
Then click on Table
Choose the table type that you want to create
The SQL editor window will display, enter the create table SQL statement to create table.
- - In this example, We create a table named SAMPLEEMPLOYEE with the SQL create statement as below:
    CREATE OR REPLACE TABLE KB_DB1.KB_SC1. SAMPLEEMPLOYEE (
    EMP_FIRSTNAME VARCHAR (16777216),
    EMP_LASTNAME VARCHAR (16777216),
    EMP_AGE NUMBER (38, 0));

Grant permission to role PUBLIC to access table name SAMPLEEMPLOYEE

Integrate Dremio to Snowflake

Retrieve data from the Snowflake table

Common Challenges

Case 1: Dremio connects to Snowflake successfully, but is unable to see the Snowflake database

Cause:

The Snowflake database is not displayed because the Snowflake user role PUBLIC that Dremio uses to connect to Snowflake does not have the privilege to access the Snowflake database.

Solution:

Login to the Snowflake database portal, then grant the Snowflake user role privileges to the database that you want to access from the client side (e.g., Dremio).

After granting permission for a user role to access the database then the database name will display in Dremio

Case 2: Error when running a SELECT statement from Dremio to retrieve data from a Snowflake table.

Cause:

Although the Snowflake user role in the Dremio connection has privileges to access the Snowflake database and schema, it does not have access to the Snowflake table.

Solution:

Login to the Snowflake database portal, then grant the Snowflake user role used in the Dremio connection permission to access the Snowflake table.

After issuing the Snowflake user role PUBLIC to have access to the table, the query in Dremio ran successfully.

Next steps

Additional resources

https://docs.snowflake.com/en/sql-reference/snowflake-db-roles