SafetyCulture to Databricks

This project is a data integration tool that collects information from the SafetyCulture API v1.0 and inserts it into Databricks Delta tables. It is designed to automate the extraction, transformation, and loading (ETL) of SafetyCulture data for analytics and reporting in Databricks.

Prerequisites

For Users

• Windows 64-bit OS
• PowerShell or CMD (Command Prompt) to run the application
• Access to SafetyCulture API and token
• Databricks workspace and credentials
• No .NET installation required (if using the provided self-contained executable)

How to create tokens and find IDs

• SafetyCulture Personal Access Token:
  1. Log in to the SafetyCulture web app.
  2. Click your name in the lower-left corner.
  3. Select "Profile settings" from the pop-up menu.
  4. Click "API tokens" in the side menu.
  5. Click "Create a new token".
  6. Give the token a name and click "Create token".
  7. Copy the token and store it in appsettings.json.
• Databricks Personal Access Token:
  1. Log in to Databricks workspace.
  2. Click your user icon (top right) and select "User Settings".
  3. Go to "Access Tokens" and click "Generate New Token".
  4. Copy the generated token and use it in appsettings.json.
• Databricks SQL Warehouse ID:
  1. In Databricks workspace, go to "SQL" from the sidebar.
  2. Click on "SQL Warehouses".
  3. Select the warehouse you want to use.
  4. Copy the "Warehouse ID" from the warehouse details page and use it in appsettings.json.

For Developers

• Additionally, pre-install .NET 8 SDK (required to build or publish the application)

Configuration: appsettings.json

Before running the application, you must create and configure an appsettings.json file. This file contains all necessary settings for SafetyCulture and Databricks integration.

Where to place

• Place appsettings.json in the same directory as SafetyCultureToDatabricks.exe (the published executable).
• Users can edit this file at any time to update configuration without modifying the application.

Example appsettings.json

{
    "SafetyCulture": {
      "BaseUrl": "https://api.safetyculture.io",
      "TokenSecret": "your-safetyculture-token"
    },
    "Parquet": {
      "InspectionsFileName": "sc_inspections.parquet",
      "InspectionItemsFileName": "sc_inspection_items.parquet",
      "SitesFileName": "sc_sites.parquet",
      "ActionsFileName": "sc_actions.parquet",
      "ActionTimelineItemsFileName": "sc_action_timeline_items.parquet",
      "ActionAssigneesFileName": "sc_action_assignees.parquet",
      "Users": "sc_users.parquet",
      "GroupUsersFileName": "sc_group_users.parquet",
      "SiteMembersFileName": "sc_site_members.parquet",
      "TemplatesFileName": "sc_templates.parquet",
      "TemplatePermissionsFileName": "sc_template_permissions.parquet"
    },
    "Databricks": {
      "Host": "https://your-databricks-instance.databricks.azure.com",
      "PersonalAccessToken": "your-databricks-token",
      "VolumePath": "/Volumes/your_catalog/your_schema/your_volume",
      "DatabaseName": "your_catalog.your_schema",
      "WarehouseId": "your_warehouse_id"
    },
    "Logging": {
      "LogLevel": {
        "Default": "Information",
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Information"
      },
      "Console": {
        "IncludeScopes": false
      }
    }
  }

Usage

1. Edit appsettings.json with your SafetyCulture and Databricks configuration.
2. The application connects to SafetyCulture Server, collects data, and saves them as Parquet files in Temp folders before uploading to Databricks.
3. Run the application from PowerShell or CMD:

   .\\SafetyCultureToDatabricks.exe
   

Features

• Connects to SafetyCulture API v1.0 to retrieve inspection, action, user, site, template, and other data.
• Parses and transforms JSON responses into structured models.
• Writes data to Databricks Delta tables using Parquet format for efficient storage and querying.
• Supports incremental data extraction and merging.
• Modular architecture with separate extractors, parsers, jobs, and utilities.

Project Structure

• Extraction/ — Extractors for different SafetyCulture entities (Inspections, Actions, Users, etc.).
• Parsers/ — JSON parsers for SafetyCulture API responses.
• Jobs/ — Job classes orchestrating extraction, transformation, and loading for each entity.
• Models/ — Data models representing SafetyCulture entities.
• Services/ — Service classes for API communication.
• Utils/ — Utility classes for configuration, Parquet writing, Databricks uploading, and SQL execution.
• Program.cs — Main entry point for running jobs.
• appsettings.json — Configuration file for API keys, Databricks connection, and other settings.

Prerequisites

• .NET 8.0 or later
• Access to SafetyCulture API v1.0 (API token required)
• Databricks workspace with Delta Lake enabled

Getting Started

1. Clone the repository.
2. Update appsettings.json with your SafetyCulture API token and Databricks connection details.
3. Build the project:

   dotnet build
   

4. Run the desired job or the main program:

   dotnet run
   

How to Build and Publish (Self-Contained, Win-x64)

1. Restore dependencies

   dotnet restore
   

2. Publish a self-contained release build for Windows 64-bit

   dotnet publish -c Release -r win-x64 --self-contained true /p:PublishSingleFile=true /p:IncludeNativeLibrariesForSelfExtract=true -o publish
   

   • Output will be in the publish folder.
3. Create a ZIP file of the release

   Compress-Archive -Path publish\\* -DestinationPath SafetyCultureToDatabricks-win-x64.zip
   

How to add another table later

1. Create its model.
2. Create its extractor returning parquet path.
3. Add another BuildXJob(...) similar to BuildInspectionsJob and append to the jobs array.

License

See LICENSE for details.