Scripts

1.0 Introduction

This guide describes how to create and manage Scripts in Fortanix Confidential Computing Manager (CCM).

Scripts are user-defined instructions written in SQL or Python that process input data within the Fortanix CCM runtime environment. The Scripts feature provides an interface to create, save, and manage SQL queries and Python scripts. Scripts provide a standardized mechanism to execute specific functions within the secure and controlled Fortanix CCM environment, ensuring that sensitive data is handled with confidentiality and integrity.

Using the Scripts menu item in the Fortanix CCM user interface (UI), you can add, view, edit, and manage scripts. When a script is updated, Fortanix CCM creates a new version of the script instead of modifying the existing version.

Scripts support the following:

• SQL statements to JOIN data from multiple sources

• SQL operations to AGGREGATE data for CCM use cases

• Python scripts to execute data processing logic, including JOIN operations

2.0 Create a Script

Perform the following steps to create a script in Fortanix CCM:

1. In the CCM UI left navigation panel, click Scripts, and then click ADD SCRIPT to create a new script.

   

2. In the Create Script form:

   1. Script Name: Enter a name for your script.

   2. Description (Optional): Enter a description for the script.

   3. Group: Select the required Fortanix Armor IAM group name from the drop down menu.

   4. Select query language: Select the required query language as SQL, SQL Aggregate, or Python. Enter the SQL query or Python script in the script editor. For more information about supported SQL queries, refer to Section 2.1: Real Time Examples.

      NOTE

      Ensure the following:

      • SQL: The script must include the SELECT statement to retrieve data from the dataset.

      • SQL Aggregate: The script must include SQL aggregate functions such as AVG, MIN, MAX, COUNT, or SUM to perform calculations or analysis on grouped data.

      • Python: The script must define the def process method to perform the required operations and return a DataFrame.

3. Click ADD SCIPT to initiate the script creation process.

2.1 Real Time Examples

2.1.1 SQL Script 

Table Name: Patient_Medication_Encounter_SQL

Query Language: SQL Join Query Script (Join Query)

SELECT DISTINCT "patients"."patients-Id",
    "patients".GENDER,
    "patients".RACE,
    encounters.ENCOUNTERCLASS,
    conditions.CODE as condition_code
FROM "patients"
    JOIN medications ON medications."patients-Id" = "patients"."patients-Id"
    JOIN conditions ON conditions."patients-Id" = "patients"."patients-Id"
    JOIN encounters ON encounters."patients-Id" = "patients"."patients-Id"
WHERE medications.CODE = '860975'
    AND medications.STOP = '';

2.1.2 Aggregate SQL Script 1: Patient Condition with Gender Female

This script focuses and helps us identify and analyze conditions specific to women's health.

• Name: Patient_Condition_Gender_F

• Description: Filters patient conditions for females.

• Group: CCM Default

• Query Language: SQL

  SELECT COUNT(DISTINCT "patients-Id")
  FROM joined_data
  WHERE ENCOUNTERCLASS = 'ambulatory'
  AND GENDER='F'; 

2.1.3 Aggregate SQL Script 2: Patient Condition with Gender Male

This script is similar to the previous script, but targets on the health conditions that predominantly affecting men.

• Name: Patient_Condition_Gender_M

• Description: Filters patient conditions for males.

• Group: CCM Default

• Query Language: SQL

  SELECT COUNT(DISTINCT "patients-Id")
  FROM joined_data
  WHERE ENCOUNTERCLASS = 'ambulatory'
  AND GENDER='M';

2.1.4 Aggregate SQL Script 3: Patient Condition with Diabetic Retinopathy

This script targets patients diagnosed with diabetic retinopathy, a complication of diabetes. It helps us understand the prevalence and management of this condition within our patient population.

• Name: Patients_Diabetic_Retinopathy

• Description: Identifies patients diagnosed with diabetic retinopathy.

• Group: CCM Default

• Query Language: SQL

  SELECT COUNT(DISTINCT "patients-Id")
  FROM joined_data
  WHERE condition_code = '422034002';

2.1.5 Python Script

Table Name: Patient_Medication_Encounter_Python

Query Language: Python

import pandas as pd
from typing import Dict

REQUIREMENTS = """
pandas==3.0.2
"""

def process(data_frames: Dict[str, pd.DataFrame]) - pd.DataFrame:
    airline_df = data_frames["airline_customers"]
    media_df = data_frames["media_customers"]

    # Join DataFrames on EMAIL
    merged_df = airline_df.merge(media_df, on='EMAIL', how='inner')

    # Create desired column names with prefixes
    merged_df.rename(columns={'PHONE_x': 'Airline_Phone', 'PHONE_y': 'Media_Phone',
                               'ZIP': 'Airline_ZIP', 'PRODUCT': 'Airline_Product',
                               'SLS_DATE_x': 'Airline_Sales_Date', 'SLS_DATE_y': 'Media_Sales_Date'},
                     inplace=True)

    # Select desired columns
    result_df = merged_df[['EMAIL', 'Airline_Phone', 'Zone', 'Airline_ZIP',
                           'Airline_Product', 'Airline_Sales_Date', 'SALES_DLR',
                           'Media_Phone', 'STATUS', 'AGE_BAND', 'Media_Sales_Date',
                           'CAMPAIGN', 'SEC_VIEW', 'COST']]

    # Display the result
    return result_df

3.0 Edit the Scripts

Perform the following steps to edit a script:

1. In the CCM UI left navigation panel, click the Scripts menu item.

2. From the list of available scripts, select the script that you want to edit. The script details page opens, where you can review the script details and content.

3. Click EDIT to modify the script.

   NOTE

   Alternatively, click the overflow menu (three dots) for a script row and select Edit Script.

4. Update the script configuration as required.

   NOTE

   The changes are saved as a new version of the script instead of modifying the existing version.

5. Click UPDATE SCRIPT to save the changes and create a new script version.

NOTE

Scripts cannot be deleted.

4.0 Scripts Details Overview

Click the required script to view the insights into general details and version history, enabling effective management and understanding of your scripts.

• General Properties:

  • Name: Displays the name of the script.

  • Script type: Specifies the script language, such as SQL, SQL Aggregate, or Python.

  • Group: Specifies the Fortanix Armor IAM group to which the script belongs.

  • Created at: Displays the timestamp when the script was created.

  • Created by: Displays the user who created the script.

  • Last updated at: Displays the timestamp of the most recent update to the script.

• Script:

  • Version: Displays the available script versions. Select a version from the drop down menu to view the corresponding script content.