Scripts

1.0 Introduction

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

Scripts are user-defined instructions written in SQL query or Python that process input data within the Fortanix CCM runtime environment. It Scripts feature provides a query interface to create and save SQL queries and Python scripts. The purpose of Scripts is to 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, and manage scripts. After a script is created, it cannot be modified. If changes are required, you must create a new version of the script to maintain uniqueness.

Scripts support:

• 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 left navigation panel, click the Scripts menu item, and then click + ADD SCRIPT to create a new script.

   

2. On the Script page, do the following:

   • Name: Enter a required name for your script.

   • Description (Optional): Add a brief description to provide additional context if needed.

   • Group: Select the required group name from the drop down menu to associate the script with that group.

   • Select query language: Click the appropriate radio button to select the query language as SQL, SQL Aggregate or Python for your script. Use the provided text area to enter the relevant SQL commands or Python code. For more information about supported SQL queries, refer to Section 2.1: Real Time Examples.

     NOTE

     Ensure the following:

     • SQL Script: The script must include the SQL keyword 'SELECT' to retrieve specific data from the database.

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

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

3. Click SAVE to initiate the script creation process.

The new script is now created successfully.

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
"""

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 modify the details of the scripts:

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

2. Locate and click the script that you wish to edit from the list of available scripts. A new screen opens where you can review and edit the selected group and script queries.

3. Click EDIT to initiate the editing process.

   NOTE

   Alternatively, you can click the  overflow menu (three dots) for a script row and select EDIT SCRIPT to modify the script.

4. Update the configuration as needed.

   NOTE

   These modifications are applied in a new version of script rather than altering the existing one.

5. After you have made the necessary edits, click UPDATE SCRIPT to save your changes and create a new version of the script.

The script is now updated successfully.

NOTE

You cannot delete a script.

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 tab:

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

  • Created at: Shows the timestamp indicating when the script was initially created.

  • Changed by: Indicates the user responsible for the most recent changes to the script.

  • Changed at: Displays the timestamp of the last modification to the script.

  • Group: Specifies the group to which the script belongs.

  • Query language: Specifies the language used in the script, such as SQL, SQL Aggregate or Python.

• Version History tab:

  • Changed by: Lists the users who made changes to the script across different versions.

  • Created at: Displays the timestamps corresponding to the creation of each version.

  • Content: Displays the content of the modified script.