Snowflake has the user access control, it controls users’ access to database, schema, table objects through 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 to grant privilege of the database, schema, table to the role that external client (like Dremio) use in the connection to connect to Snowflake.
All Dremio versions
Use case Details
Summarize the use case for:
- 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 Snowflake then retrieve data from 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 name 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));
- In this example: We create a table name SAMPLEEMPLOYEE with the SQL create statement as below:
- Grant permission to role PUBLIC to access table name SAMPLEEMPLOYEE
Integrate Dremio to Snowflake
Retrieve data from Snowflake table
Case 1: Dremio connects to Snowflake success, but unable to see Snowflake database
Snowflake database not displace because the Snowflake user role PUBLIC that use in Dremio connects to Snowflake does not have a privilege to access Snowflake database.
Login to Snowflake database portal then grant a privilege to the Snowflake user role to the database that you want to access from client side (ex: Dremio).
After granted permission for user role to access database then the database name will display in Dremio
Case 2: Error when run select statement from Dremio to retrieve data from Snowflake table.
Even though the Snowflake user role in Dremio connection has privilege to access Snowflake database and schema, but it does not has access to Snowflake table.
Login to Snowflake database portal then grant the Snowflake user role that use in Dremio connection to have permission to access Snowflake table.
After issued the Snowflake user role PUBLIC to have access to table then query in Dremio run success.