User's Guide: Scripts

1.0 Introduction

Welcome to the Fortanix Confidential Computing Manager - Scripts user guide. This guide describes how to create and manage the Scripts in Fortanix Confidential Computing Manager.

The Scripts are a set of user-defined instructions in SQL query or Python format for processing the input data within the Confidential Computing Manager environment. It displays a query interface to create, save the SQL queries and Python. The purpose of Script is to provide a standardized way to execute specific functions within the secure and controlled environment of Fortanix Confidential Computing Manager, ensuring that the sensitive data is handled with confidentiality and integrity.

Using the Scripts menu item in the Fortanix Confidential Computing Manager user interface (UI), you can add, review, and manage the scripts. It is important to note that after the scripts are added, they cannot be changed. If the modifications are necessary, you are required to create a new version of the Script for seamless, unique operations. Additionally, you can create SQL statements to JOIN data from different sources and AGGREGATE the data for Computing Manager use cases. Similarly, Python scripts can be used for executing the JOIN data queries.

2.0 Add A Script

Perform the following steps to seamlessly add a script to the Fortanix Confidential Computing Manager:

1. Click the Scripts menu item in the Confidential Computing Manager left navigation bar and click the + CREATE SCRIPT button to add a new script.

   

2. On the Create new script page, enter the following details:

   • Name: Enter a required name for your script in the provided field.

   • Description (Optional): Enter a valid brief description of your script.

   • Group: Select the required group name from the drop down menu to associate the script with that group. This helps in organizing and categorizing scripts for better management.

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

     NOTE

     Ensure the following:

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

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

     • Python Script: The script includes a method 'def process' to perform operations and return dataframe within the Python programming language.

3. Click the CREATE SCRIPT button 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

  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. Click the Scripts menu item in the Confidential Computing Manager left navigation bar.

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 the EDIT button to initiate the editing process.

   NOTE

   Alternatively, you can click the  overflow menu (three dots) for a script row and select the EDIT SCRIPT option 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 the UPDATE SCRIPT button 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.