Week 1

ToDo

See the Action Items for CCTV project here.
See the Action Items for DeepVibe project here.

October 06, 2023

Implement a feature to drop ND frames from the training CSV files.
Organize the logs and training CSV files under a folder on the compute-msi workflow.
Working on the Action Items in the CCTV preprocessing pipeline.

October 05, 2023

Documenting CCTV training pipeline.
Work on the CCTV preprocessing pipeline AIs.
Report creation of WandB and firing a new run on the binary model with 10 epochs on MSI.

October 04, 2023

Play with OpenAI API to see how functions and other calls are used.

The prompt (or message in openai terms) has to be structured accounting the role , name (optional), and content attributes. Here is a good reference on constructing messages for openai chat endpoints.
- role: the role of the messenger (either system, user, or assistant)
- content: the content of the message (e.g., Write me a beautiful poem)

It's worth using the system role to setup the agent the way we want and then use the user role to send the SQL queries. An example of constructing a message for the ORSANCO use case:

The SQL query is loaded into the variable query

query = """SELECT [QryPublic Data Source].[DB ID], [QryPublic Data Source].SampleSite, [Metals Location].River, [Metals Location].[Mile Point], [QryPublic Data Source].Parameter, QryViolationUnion.Parameter, [Parameter Units].[Parameter Long Name], [QryPublic Data Source].[Parameter with Units], [QryPublic Data Source].Date, [Metals Event].[Flow (kcfs)], [QryPublic Data Source].[Sample Type], [QryPublic Data Source].RDL, [QryPublic Data Source].LF, First([QryPublic Data Source].PublicRounded) AS FirstOfPubRounded, [QryPublic Data Source].Detect, [QryPublic Data Source].[ND=1/2DL], [Metals Event].[Event Type], QryViolationUnion.WQS, QryViolationUnion.[Criteria Source]
FROM [Metals Location] INNER JOIN ((([Metals Event] INNER JOIN [QryPublic Data Source] ON [Metals Event].[Event ID] = [QryPublic Data Source].[Event ID]) INNER JOIN [Parameter Units] ON [QryPublic Data Source].Parameter = [Parameter Units].Parameter) LEFT JOIN QryViolationUnion ON ([QryPublic Data Source].[Sample Type] = QryViolationUnion.[Sample Type]) AND ([QryPublic Data Source].[DB ID] = QryViolationUnion.[DB ID]) AND ([QryPublic Data Source].Parameter = QryViolationUnion.Parameter)) ON [Metals Location].LocationID = [Metals Event].LocationID
WHERE ((([QryPublic Data Source].Date)>#5/1/2022# And ([QryPublic Data Source].Date)<#8/1/2022#) AND (([QryPublic Data Source].[Sample Type])="dissolved" Or ([QryPublic Data Source].[Sample Type])="total"))
GROUP BY [QryPublic Data Source].[DB ID], [QryPublic Data Source].SampleSite, [Metals Location].River, [Metals Location].[Mile Point], [QryPublic Data Source].Parameter, QryViolationUnion.Parameter, [Parameter Units].[Parameter Long Name], [QryPublic Data Source].[Parameter with Units], [QryPublic Data Source].Date, [Metals Event].[Flow (kcfs)], [QryPublic Data Source].[Sample Type], [QryPublic Data Source].RDL, [QryPublic Data Source].LF, [QryPublic Data Source].Detect, [QryPublic Data Source].[ND=1/2DL], [Metals Event].[Event Type], QryViolationUnion.WQS, QryViolationUnion.[Criteria Source], [Metals Event].[Event Type]
HAVING (((QryViolationUnion.Parameter) Is Not Null) AND (([Metals Event].[Event Type])="bimonthly"))
ORDER BY QryViolationUnion.Parameter, [QryPublic Data Source].[Parameter with Units], [QryPublic Data Source].Date, [QryPublic Data Source].[Sample Type];"""

Then compose the message in following structure.

response = openai.ChatCompletion.create(
   model=MODEL,
   messages=[
      {"role": "system", "content": "You are a helpful SQL master that tells the intent behind the given SQL query in brief"},
      {"role": "user", "content": query},
   ],
   max_tokens=500,
   temperature=0,
)
response["choices"][0]["message"]["content"]

Response:

'This SQL query retrieves data from multiple tables to generate a report on water quality measurements for a specific time period. The query joins the "Metals Location," "Metals Event," "QryPublic Data Source," and "Parameter Units" tables. It selects various columns from these tables, including the database ID, sample site, river, mile point, parameter, date, flow rate, sample type, RDL, LF, and other related information.\n\nThe query also performs a left join with the "QryViolationUnion" table to include violation information for the samples. It filters the results based on the date range, sample type (dissolved or total), and event type (bimonthly). The results are grouped by several columns and ordered by the parameter, parameter units, date, and sample type.\n\nOverall, this query aims to retrieve water quality data for a specific time period, including any violations, and organize it for reporting purposes.'

Also we are able to shape the result to have different sections according to our requirements utilizing function calls parameter in the OpenAI API. Here is the API reference for that.

note

The original usage of function calls is to integrate locally defined functions as part of the conversation with the chat model. But we can leverage the functionality for our use case.

Here is an example on using function calls in the ORSANCO scenario:

Define a function prototype in json format as required by openai API.

functions = [
{
   "name": "get_key_information",
   "description": "Get the key information from the given SQL",
   "parameters": {
         "type": "object",
         "properties": {
            "summary": {
               "type": "string",
               "description": "Summary of the most possible intent behind the given SQL query",
            },
            "key_attributes_in_SQL": {"type": "string", 
                              "description": "List of key attributes used in the SQL"},
         },
         "required": ["summary", "key_attributes_in_SQL"],
   },
   }
]

The chat endpoint call should include the function prototype we created.

response = openai.ChatCompletion.create(
   model="gpt-3.5-turbo-0613",
   messages= [{"role": "system", "content": "You are a helpful SQL master that tells the intent behind the given SQL query in brief"},
      {"role": "user", "content": query},],
   functions=functions,
   function_call="auto"
)

We receive the result as a json formatted string, hence we load it into a json object.

import pprint
response_json = json.loads(response_message["function_call"]["arguments"])
pprint.pprint(response_json)

result:

{
   'summary': "This SQL query retrieves data from multiple tables and joins them based on specific conditions. It includes various attributes such as DB ID, SampleSite, River, Mile Point, Parameter, Date, Flow, Sample Type, RDL, LF, PublicRounded, Detect, ND=1/2DL, Event Type, WQS, and Criteria Source. The query filters the results based on the date range and sample type, and groups the data by several attributes. It also includes a condition to exclude null values for QryViolationUnion.Parameter and restricts the event type to 'bimonthly'. The final result is ordered by QryViolationUnion.Parameter, Parameter with Units, Date, and Sample Type.",
   'key_attributes_in_SQL': '[QryPublic Data Source].[DB ID], [QryPublic Data Source].SampleSite, [Metals Location].River, [Metals Location].[Mile Point], [QryPublic Data Source].Parameter, QryViolationUnion.Parameter, [Parameter Units].[Parameter Long Name], [QryPublic Data Source].[Parameter with Units], [QryPublic Data Source].Date, [Metals Event].[Flow (kcfs)], [QryPublic Data Source].[Sample Type], [QryPublic Data Source].RDL, [QryPublic Data Source].LF, First([QryPublic Data Source].PublicRounded), [QryPublic Data Source].Detect, [QryPublic Data Source].[ND=1/2DL], [Metals Event].[Event Type], QryViolationUnion.WQS, QryViolationUnion.[Criteria Source]'
}

October 03, 2023

My weekly notes clean up and refactoring for chronological output.
Two stage model runs are complete. Check how we combine them and generate the accuracy metrics on a test set.
1. Warning message is thrown in the logs repeatively cluttering the run log for second model training. Fix it.
```
/home/gqc/mambaforge/envs/compute-msi/lib/python3.10/site-packages/sklearn/metrics/_classification.py:1757: UndefinedMetricWarning: F-score is ill-defined and being set to 0.0 in labels with no true nor predicted samples. Use `zero_division` parameter to control this behavior.
```
  This warning can be turned off via a parameter. First check with Vannary and Sudhir to see if this is the expected behavior.
2. Running a test set on the two stage models.
  1. Wandb link is here
  2. Figure out the test set running flow based on Q&A. Document the findings in the Readme file of the compute-msi repo.
  3. Had to fix some directory paths in the metric calculation script to point to the correct models.
3. Refactoring the pipeline is required as generating two model prediction results require one model to be specified in the settings.py and another through the cli. Should stick to one way of doing things. But will keep the current flow of events to match Vannary's records so it will be easier to cross-check. Adding this as a todo under the project.
4. Naming of wandb projects: (Adding these to the README as well). I think we can refactor the names. But will keep it as is for now to make them consistent with the Vannary's run logs.
  1. cctv-sd1-two-stage-model: Test set results over the ensemble of two-stage models
  2. cctv-sd1-defect-multilabel: Stage 2 model training of two-stage ensemble
  3. cctv-sd1-singlelabel: Stage 1 model (binary model) training of two-stage ensemble
  4. cctv-sd1-multilabel: Multi-label model training

October 02, 2023

I checked Vannary's 3rd notebook in colab and it has new updates. And according to Vannary's email she will develop this further based on the Monday's meeting with Dr. Lence.
1. If we want to discard all other models except lightGBM, I can modify the latest nb for that purpose. The older lightGBM only training notebook vs. the lightGBM training in the latest have few known changes in the input features. There might be more differences too.
2. Sudhir: Please modify your notebook to include the multimodel support from Vannary's code. Notebook can be found here
Review the AI3 notes and questions and adding my input.
Send a reminder to AgPoint.
Fixing nvidia-smi: no GPU drivers found issue
1. The sudo apt purge command to remove the existing GPU libs failed due to a dependency issue (might be a cyclic dependency)
2. sudo dpkg --remove --force-all <package_name> on all the libs causing the dependency issues fixed the problem with purging.
3. Installed the nvidia driver version 535 from ppa database.

Week 1

ToDo​

October 06, 2023​

October 05, 2023​

October 04, 2023​

October 03, 2023​

October 02, 2023​