How to create a databricks database with read only access on top of an existing database

user_6339438 0
Viewed 1489

I will use this image to visualize my question: enter image description here

Databricks1 creates a database (and tables) in Databricks and stores its data in the storage account. In Databricks2 I want to read the data: Databricks2 only has read permissions. I can read directly on the raw delta files, but I would like to create a database and table that is visualized as well in the Databricks UI. I thought it would work the following way: 

CREATE DATABASE IF NOT EXISTS datastore_panels
LOCATION '/mnt/readOnlyTraining/tmp/panels/';

But this gives a permission error, though the tmp/panels database is already in place.

Is there a way to create a database/table from existing resources on top of delta with read only access?

delta-lake databricks
2 Answers

I have found the solution. I wasted quite some time on this and I never encountered someone with the same question.

The solution is actually simple, but you need to know. I have a Service Principal with read access to my storage account.

Create the database like this (don't mention the location):

CREATE DATABASE IF NOT EXISTS datastore_panels

Create the table (use location, but do not set tableproperties or partitioning: it will read this from the delta table metadata): 

CREATE TABLE IF NOT EXISTS datastore_panels.customer_data
USING delta
LOCATION '/mnt/readOnlyTraining/delta/customer-data/'

For those that want to understand the question better, this is what I tried before 

   %sql
CREATE TABLE IF NOT EXISTS datastore_panels.production_bazeilles_press_shopfloor (
      reg_id INT,
      year INT,
      timestamp_utc TIMESTAMP,
      unit STRING,
      value DECIMAL (18,8),
      descr_total STRING,
      descr01 STRING,
      descr02 STRING,
      descr03 STRING,
      descr04 STRING,
      descr05 STRING,
      descr06 STRING,
      descr07 STRING,
      descr08 STRING,
      descr09 STRING,
      descr10 STRING
)
USING delta
PARTITIONED BY (year)
LOCATION '/mnt/blob/panels/production/bazeilles/press/shopfloor'
TBLPROPERTIES ('delta.deletedFileRetentionDuration' = "interval 60 days",
              'delta.autoOptimize.optimizeWrite' = 'true'
)

This doesn't work because spark will in the end only read, but due to the columns specified, tblproperties and partitionedby spark needs to log to the storage account with only read access that someone tries to change these properties. This logging step is not possible so it gives a "no permission" error back.

user_63394380

We had a similar situation and we have created a service principal with "Storage Read Access" on ADLS and mounted the databricks 1 location with databricks 2 with read only service principal. However, we need to additionally run an automated script to auto create the tables in databricks 2 based on new mount location.

Although, we are stuck with one problem, what about if different groups want to access same tables in same databricks account (databricks 1): 1 group with read and write whereas other with group with read only. And we don't want another service principal because we want give table names to the groups instead of table location. Similar to database ACL in databases to manage privileges.

user_41249690
Related