UC Data Interaction App

A Streamlit application that provides a visual interface for viewing and editing Unity Catalog managed tables. Users can select tables from a dropdown, search by ID, and edit any column (except primary keys) directly through an intuitive web interface.

Demo

Video Walkthrough

Watch the complete demo showing table selection, data editing, and real-time updates in action.

Features

• Multi-table Support: Select from multiple tables using a dropdown menu
• Smart Column Detection: Automatically configures appropriate input widgets based on column data types
• Flexible Editing: Edit any column except primary key (ID) columns
• Search & Filter: Search records by ID with built-in filtering
• Real-time Updates: Changes are immediately reflected in the database
• Secure Authentication: Uses Databricks OAuth for user authentication

Architecture

This app uses a dual authentication model:

• Data Reading: Uses the authenticated user's token to query tables (respects user-level permissions)
• Data Updates: Uses the app's service principal to execute UPDATE statements (requires proper service principal permissions)

Prerequisites

1. Databricks Setup

• Unity Catalog enabled workspace
• SQL Warehouse configured and running
• App deployed as a Databricks App with service principal authentication

2. Service Principal Permissions

CRITICAL: The app's service principal must have the following permissions on all tables you want to edit.

Finding Your App's Service Principal

To find your app's service principal:

1. Navigate to your Databricks App in the workspace
2. Go to the app settings/configuration page
3. Look for the "Service Principal" section
4. Copy the service principal name/ID for use in the GRANT statements below

Required Permissions

The service principal needs these permissions (can be granted via Databricks UI or SQL):

Step 1: Catalog and Schema USE permissions

-- Grant USE permission on catalog (required first)
GRANT USE CATALOG ON CATALOG catalog_name TO SERVICE_PRINCIPAL 'your-app-service-principal';

-- Grant USE permission on schema (required before table permissions)
GRANT USE SCHEMA ON SCHEMA catalog_name.schema_name TO SERVICE_PRINCIPAL 'your-app-service-principal';

Step 2: Table-level permissions

-- Grant SELECT permission (for reading data)
GRANT SELECT ON TABLE catalog_name.schema_name.table_name TO SERVICE_PRINCIPAL 'your-app-service-principal';

-- Grant UPDATE permission (for editing data)  
GRANT UPDATE ON TABLE catalog_name.schema_name.table_name TO SERVICE_PRINCIPAL 'your-app-service-principal';

UI-Based Permission Management

Recommended: Use the Databricks UI for easier permission management:

1. Catalog Explorer → Navigate to your catalog/schema/table
2. Permissions Tab → Click "Grant Permissions"
3. Principal Type → Select "Service Principal"
4. Principal Name → Enter your app's service principal name
5. Permissions → Select "USE CATALOG", "USE SCHEMA", "SELECT", and "UPDATE" as needed

Example for Default Tables

-- For pedroz_demo_catalog
GRANT USE CATALOG ON CATALOG pedroz_demo_catalog TO SERVICE_PRINCIPAL 'your-app-service-principal';
GRANT USE SCHEMA ON SCHEMA pedroz_demo_catalog.default TO SERVICE_PRINCIPAL 'your-app-service-principal';

-- For approval_table
GRANT SELECT ON TABLE pedroz_demo_catalog.default.approval_table TO SERVICE_PRINCIPAL 'your-app-service-principal';
GRANT UPDATE ON TABLE pedroz_demo_catalog.default.approval_table TO SERVICE_PRINCIPAL 'your-app-service-principal';

-- For users_table
GRANT SELECT ON TABLE pedroz_demo_catalog.default.users_table TO SERVICE_PRINCIPAL 'your-app-service-principal';
GRANT UPDATE ON TABLE pedroz_demo_catalog.default.users_table TO SERVICE_PRINCIPAL 'your-app-service-principal';

3. User Permissions

Users must have SELECT permissions on the tables they want to view:

GRANT SELECT ON TABLE catalog_name.schema_name.table_name TO USER 'user@example.com';

Environment Configuration

The app requires the following environment variables (configured in app.yaml):

• DATABRICKS_WAREHOUSE_ID: SQL Warehouse ID for query execution
• DATABRICKS_HOST: Databricks workspace URL
• Service principal credentials (configured automatically by Databricks Apps)

Usage

1. Table Selection

• Use the dropdown at the top to select which table to view/edit
• Default table: pedroz_demo_catalog.default.approval_table

2. Data Loading

• Click "Filter" to load data (displays up to 1000 rows)
• Use "Search by ID" to find specific records
• Click the refresh button (🔄) to reload data

3. Editing Data

• ID columns: Read-only (primary keys cannot be modified)
• Boolean columns: Checkboxes for true/false values
• Numeric columns: Number input fields
• Text columns: Text input fields
• Edit multiple rows and columns simultaneously

4. Saving Changes

• Click "Make Update" to save all changes
• Updates are executed using the app's service principal
• Success confirmation with visual feedback (balloons animation)
• Data automatically refreshes after updates

Table Requirements

Tables must have:

• An id column as the primary key
• Proper Unity Catalog table structure
• Appropriate permissions configured for both users and the service principal

Security Notes

• User Authentication: Reading data respects individual user permissions
• Service Principal Updates: All UPDATE operations use the app's service principal credentials
• SQL Injection Protection: All user inputs are properly escaped
• Primary Key Protection: ID columns cannot be modified through the interface

Troubleshooting

Common Issues

1. "No user token available for updates"

   • Ensure you're logged into Databricks and the app has proper OAuth configuration

2. "Error loading data: Permission denied"

   • Check that your user account has SELECT permissions on the table
   • Verify the service principal has the required permissions

3. "Error updating table"

   • Ensure the service principal has UPDATE permissions on the table
   • Check that you're not trying to modify an ID column

Permission Check Queries

-- Check service principal permissions
SHOW GRANTS ON TABLE catalog_name.schema_name.table_name;

-- Check user permissions  
SHOW GRANTS ON TABLE catalog_name.schema_name.table_name FOR USER 'user@example.com';

Development

To run locally:

1. Install dependencies: pip install -r requirements.txt
2. Configure environment variables in app.yaml
3. Run: streamlit run app.py

Support

For issues related to permissions or app functionality, contact your Databricks administrator.