OpenAI
Working with OpenAI in Pixeltable¶
Pixeltable unifies data and computation into a table interface. In the Pixeltable Basics tutorial, we saw how OpenAI API calls can be incorporated into Pixeltable workflows. In this tutorial, we'll go into more depth on OpenAI integration. You'll need an OpenAI API key to run this demo.
Prerequisites¶
- An OpenAI account with an API key (https://openai.com/index/openai-api/)
Important Notes¶
- OpenAI usage may incur costs based on your OpenAI plan.
- Be mindful of sensitive data and consider security measures when integrating with external services.
In [ ]:
%pip install -q pixeltable openai
In [1]:
import pixeltable as pxt
pxt.create_dir('demo', ignore_errors=True)
Connected to Pixeltable database at: postgresql://postgres:@/pixeltable?host=/Users/asiegel/.pixeltable/pgdata Created directory `demo`.
Creating the Table¶
First, we'll create a table and populate it with some sample data.
In [2]:
pxt.drop_table('demo.openai', ignore_errors=True)
t = pxt.create_table('demo.openai', {'id': pxt.IntType(), 'input': pxt.StringType()})
Created table `openai`.
In [3]:
# text from https://en.wikipedia.org/wiki/Global_financial_crisis_in_September_2008
wikipedia_text = '''On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers.
The significance of the Lehman Brothers bankruptcy is disputed with some assigning it a pivotal role in the unfolding of subsequent events.
The principals involved, Ben Bernanke and Henry Paulson, dispute this view, citing a volume of toxic assets at Lehman which made a rescue impossible.[16][17] Immediately following the bankruptcy, JPMorgan Chase provided the broker dealer unit of Lehman Brothers with $138 billion to "settle securities transactions with customers of Lehman and its clearance parties" according to a statement made in a New York City Bankruptcy court filing.[18]
The same day, the sale of Merrill Lynch to Bank of America was announced.[19] The beginning of the week was marked by extreme instability in global stock markets, with dramatic drops in market values on Monday, September 15, and Wednesday, September 17.
On September 16, the large insurer American International Group (AIG), a significant participant in the credit default swaps markets, suffered a liquidity crisis following the downgrade of its credit rating.
The Federal Reserve, at AIG's request, and after AIG had shown that it could not find lenders willing to save it from insolvency, created a credit facility for up to US$85 billion in exchange for a 79.9% equity interest, and the right to suspend dividends to previously issued common and preferred stock.[20]'''
sample_inputs = wikipedia_text.split('\n')
In [4]:
# Insert a single sample row into the table
t.insert(id=0, input=sample_inputs[0])
t.show()
Inserting rows into `openai`: 1 rows [00:00, 253.49 rows/s] Inserted 1 row with 0 errors.
Out[4]:
| id | input |
|---|---|
| 0 | On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. |
Making OpenAI API calls¶
Calling OpenAI API endpoints involves constructing a message object, which we can express in Pixeltable by adding a new computed column.
In [5]:
prompt = "For the following sentence, extract all company names from the text."
msgs = [
{ "role": "system", "content": prompt },
{ "role": "user", "content": t.input }
]
t.add_column(input_msgs=msgs)
Computing cells: 100%|███████████████████████████████████████████| 1/1 [00:00<00:00, 104.58 cells/s] Added 1 column value with 0 errors.
Out[5]:
UpdateStatus(num_rows=1, num_computed_values=1, num_excs=0, updated_cols=[], cols_with_excs=[])
Unlike the values of theinput column, which users provide, the t.input_msgs column is computed automatically from the t.input column values:
In [6]:
t.show()
Out[6]:
| id | input | input_msgs |
|---|---|---|
| 0 | On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. | [{'role': 'system', 'content': 'For the following sentence, extract all company names from the text.'}, {'role': 'user', 'content': 'On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers.'}] |
In Pixeltable, OpenAI API calls are exposed as Pixeltable functions, which can be used to create computed columns. You'll need to enter your OpenAI API key now.
In [7]:
import os
import getpass
if 'OPENAI_API_KEY' not in os.environ:
os.environ['OPENAI_API_KEY'] = getpass.getpass('OpenAI API Key:')
Now we can run the prompt against our input data using OpenAI's chat_completions API.
In [8]:
from pixeltable.functions import openai
t['chat_output'] = openai.chat_completions(model='gpt-3.5-turbo', messages=t.input_msgs)
t.show()
Computing cells: 100%|████████████████████████████████████████████| 1/1 [00:00<00:00, 1.90 cells/s] Added 1 column value with 0 errors.
Out[8]:
| id | input | input_msgs | chat_output |
|---|---|---|---|
| 0 | On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. | [{'role': 'system', 'content': 'For the following sentence, extract all company names from the text.'}, {'role': 'user', 'content': 'On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers.'}] | {'id': 'chatcmpl-9OttExT1O3SKUhLDIS4F6KYq0BuXa', 'model': 'gpt-3.5-turbo-0125', 'usage': {'total_tokens': 69, 'prompt_tokens': 61, 'completion_tokens': 8}, 'object': 'chat.completion', 'choices': [{'index': 0, 'message': {'role': 'assistant', 'content': 'Lehman Brothers, Federal Reserve Bank', 'tool_calls': None, 'function_call': None}, 'logprobs': None, 'finish_reason': 'stop'}], 'created': 1715721740, 'system_fingerprint': None} |
The output of the OpenAI API calls are generally complex JSON structures, which require some navigation to extract the response. We can express this as JSON path expressions and create another computed column:
In [9]:
t['response'] = t.chat_output.choices[0].message.content
t.show()
Computing cells: 100%|███████████████████████████████████████████| 1/1 [00:00<00:00, 237.18 cells/s] Added 1 column value with 0 errors.
Out[9]:
| id | input | input_msgs | chat_output | response |
|---|---|---|---|---|
| 0 | On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. | [{'role': 'system', 'content': 'For the following sentence, extract all company names from the text.'}, {'role': 'user', 'content': 'On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers.'}] | {'id': 'chatcmpl-9OttExT1O3SKUhLDIS4F6KYq0BuXa', 'model': 'gpt-3.5-turbo-0125', 'usage': {'total_tokens': 69, 'prompt_tokens': 61, 'completion_tokens': 8}, 'object': 'chat.completion', 'choices': [{'index': 0, 'message': {'role': 'assistant', 'content': 'Lehman Brothers, Federal Reserve Bank', 'tool_calls': None, 'function_call': None}, 'logprobs': None, 'finish_reason': 'stop'}], 'created': 1715721740, 'system_fingerprint': None} | Lehman Brothers, Federal Reserve Bank |
Let's run a query to look only at the input and output:
In [10]:
t.select(t.input, t.response).show()
Out[10]:
| input | response |
|---|---|
| On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. | Lehman Brothers, Federal Reserve Bank |
Once we have defined these computed columns, much like with a spreadsheet, newly inserted t.input values trigger computation of all derived columns, such as t.response. Let's insert the rest of our data and see how this works.
In [11]:
t.insert({'id': i, 'input': sample_inputs[i]} for i in range(1, len(sample_inputs)))
Computing cells: 100%|██████████████████████████████████████████| 15/15 [00:02<00:00, 5.08 cells/s] Inserting rows into `openai`: 5 rows [00:00, 581.33 rows/s] Computing cells: 100%|██████████████████████████████████████████| 15/15 [00:02<00:00, 5.05 cells/s] Inserted 5 rows with 0 errors.
Out[11]:
UpdateStatus(num_rows=5, num_computed_values=15, num_excs=0, updated_cols=[], cols_with_excs=[])
In [12]:
t.select(t.input, t.response).show()
Out[12]:
| input | response |
|---|---|
| On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. | Lehman Brothers, Federal Reserve Bank |
| The significance of the Lehman Brothers bankruptcy is disputed with some assigning it a pivotal role in the unfolding of subsequent events. | Lehman Brothers |
| The principals involved, Ben Bernanke and Henry Paulson, dispute this view, citing a volume of toxic assets at Lehman which made a rescue impossible.[16][17] Immediately following the bankruptcy, JPMorgan Chase provided the broker dealer unit of Lehman Brothers with $138 billion to "settle securities transactions with customers of Lehman and its clearance parties" according to a statement made in a New York City Bankruptcy court filing.[18] | - Lehman Brothers\n- JPMorgan Chase |
| The same day, the sale of Merrill Lynch to Bank of America was announced.[19] The beginning of the week was marked by extreme instability in global stock markets, with dramatic drops in market values on Monday, September 15, and Wednesday, September 17. | - Merrill Lynch\n- Bank of America |
| On September 16, the large insurer American International Group (AIG), a significant participant in the credit default swaps markets, suffered a liquidity crisis following the downgrade of its credit rating. | - American International Group (AIG) |
| The Federal Reserve, at AIG's request, and after AIG had shown that it could not find lenders willing to save it from insolvency, created a credit facility for up to US$85 billion in exchange for a 79.9% equity interest, and the right to suspend dividends to previously issued common and preferred stock.[20] | - The Federal Reserve\n- AIG |
Adding Ground Truth Data¶
Now let's see how Pixeltable can be used to evaluate a model against ground truth data. We'll start by manually populating a ground_truth column in our table.
In [13]:
t['ground_truth'] = pxt.StringType(nullable=True)
ground_truth = [
'Lehman Brothers',
'Lehman Brothers',
'JP Morgan Chase, Lehman Brothers',
'Merill Lynch, Bank of America',
'American International Group',
'American International Group',
]
for i, gt in enumerate(ground_truth):
t.update({'ground_truth': gt}, where=(t.id == i))
Added 6 column values with 0 errors. Inserting rows into `openai`: 1 rows [00:00, 680.78 rows/s] Inserting rows into `openai`: 1 rows [00:00, 1479.47 rows/s] Inserting rows into `openai`: 1 rows [00:00, 1845.27 rows/s] Inserting rows into `openai`: 1 rows [00:00, 1883.39 rows/s] Inserting rows into `openai`: 1 rows [00:00, 1889.33 rows/s] Inserting rows into `openai`: 1 rows [00:00, 2525.17 rows/s]
And this is what we have so far:
In [14]:
t.select(t.input, t.response, t.ground_truth).show()
Out[14]:
| input | response | ground_truth |
|---|---|---|
| On Sunday, September 14, it was announced that Lehman Brothers would file for bankruptcy after the Federal Reserve Bank declined to participate in creating a financial support facility for Lehman Brothers. | Lehman Brothers, Federal Reserve Bank | Lehman Brothers |
| The significance of the Lehman Brothers bankruptcy is disputed with some assigning it a pivotal role in the unfolding of subsequent events. | Lehman Brothers | Lehman Brothers |
| The principals involved, Ben Bernanke and Henry Paulson, dispute this view, citing a volume of toxic assets at Lehman which made a rescue impossible.[16][17] Immediately following the bankruptcy, JPMorgan Chase provided the broker dealer unit of Lehman Brothers with $138 billion to "settle securities transactions with customers of Lehman and its clearance parties" according to a statement made in a New York City Bankruptcy court filing.[18] | - Lehman Brothers\n- JPMorgan Chase | JP Morgan Chase, Lehman Brothers |
| The same day, the sale of Merrill Lynch to Bank of America was announced.[19] The beginning of the week was marked by extreme instability in global stock markets, with dramatic drops in market values on Monday, September 15, and Wednesday, September 17. | - Merrill Lynch\n- Bank of America | Merill Lynch, Bank of America |
| The Federal Reserve, at AIG's request, and after AIG had shown that it could not find lenders willing to save it from insolvency, created a credit facility for up to US$85 billion in exchange for a 79.9% equity interest, and the right to suspend dividends to previously issued common and preferred stock.[20] | - The Federal Reserve\n- AIG | American International Group |
| On September 16, the large insurer American International Group (AIG), a significant participant in the credit default swaps markets, suffered a liquidity crisis following the downgrade of its credit rating. | - American International Group (AIG) | American International Group |
Evaluation¶
Now that we have some ground truth available, we can carry out basic evaluations of the GPT outputs, in this case by asking ChatGPT to decide whether the two are equivalent.
To start with, we'll create an evaluation prompt. In this case, the prompt requires some bespoke string substitution, so it's easiest to do using a UDF. (See the Pixeltable Basics tutorial and the UDFs in Pixeltable guide for more details on UDFs.)
In [15]:
system_prompt = '''
Compare the following listA and listB of entities, and check if they contain the same entities.
Return a JSON object with the following format:
{"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise}
'''
@pxt.udf
def eval_prompt(listA: str, listB: str) -> list[dict]:
return [
{'role': 'system', 'content': system_prompt},
{'role': 'user', 'content': f'listA: "{listA}" \n listB: "{listB}"'}
]
t['eval_prompt'] = eval_prompt(t.response, t.ground_truth)
Computing cells: 100%|███████████████████████████████████████████| 6/6 [00:00<00:00, 592.42 cells/s] Added 6 column values with 0 errors.
In [16]:
t.select(t.eval_prompt).show()
Out[16]:
| eval_prompt |
|---|
| [{'role': 'system', 'content': ' Compare the following listA and listB of entities, and check if they contain the same entities. Return a JSON object with the following format: {"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise} '}, {'role': 'user', 'content': 'listA: "Lehman Brothers, Federal Reserve Bank" listB: "Lehman Brothers"'}] |
| [{'role': 'system', 'content': ' Compare the following listA and listB of entities, and check if they contain the same entities. Return a JSON object with the following format: {"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise} '}, {'role': 'user', 'content': 'listA: "- Merrill Lynch - Bank of America" listB: "Merill Lynch, Bank of America"'}] |
| [{'role': 'system', 'content': ' Compare the following listA and listB of entities, and check if they contain the same entities. Return a JSON object with the following format: {"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise} '}, {'role': 'user', 'content': 'listA: "- The Federal Reserve - AIG" listB: "American International Group"'}] |
| [{'role': 'system', 'content': ' Compare the following listA and listB of entities, and check if they contain the same entities. Return a JSON object with the following format: {"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise} '}, {'role': 'user', 'content': 'listA: "Lehman Brothers" listB: "Lehman Brothers"'}] |
| [{'role': 'system', 'content': ' Compare the following listA and listB of entities, and check if they contain the same entities. Return a JSON object with the following format: {"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise} '}, {'role': 'user', 'content': 'listA: "- Lehman Brothers - JPMorgan Chase" listB: "JP Morgan Chase, Lehman Brothers"'}] |
| [{'role': 'system', 'content': ' Compare the following listA and listB of entities, and check if they contain the same entities. Return a JSON object with the following format: {"reasoning": explaining your reasoning, "decision": 1 if the lists matched, 0 otherwise} '}, {'role': 'user', 'content': 'listA: "- American International Group (AIG)" listB: "American International Group"'}] |
The actual evaluation happens in another computed column. We can use OpenAI's handy response_format parameter to enforce that the output is properly formed JSON.
In [17]:
t['eval'] = openai.chat_completions(model='gpt-3.5-turbo', messages=t.eval_prompt, response_format={'type': 'json_object'})
t['eval_output'] = t.eval.choices[0].message.content
Computing cells: 100%|████████████████████████████████████████████| 6/6 [00:07<00:00, 1.33s/ cells] Added 6 column values with 0 errors. Computing cells: 100%|███████████████████████████████████████████| 6/6 [00:00<00:00, 913.53 cells/s] Added 6 column values with 0 errors.
Let's take a look:
In [18]:
t.select(t.response, t.ground_truth, t.eval_output).show()
Out[18]:
| response | ground_truth | eval_output |
|---|---|---|
| Lehman Brothers, Federal Reserve Bank | Lehman Brothers | {"reasoning": "listA contains an additional entity 'Federal Reserve Bank' that listB does not have.", "decision": 0} |
| - The Federal Reserve\n- AIG | American International Group | {"reasoning": "The entity 'AIG' is present in both lists, as 'AIG' in listA and 'American International Group' in listB. Therefore, the lists match.", "decision": 1} |
| - Lehman Brothers\n- JPMorgan Chase | JP Morgan Chase, Lehman Brothers | {\n "reasoning": "The entities in listA are 'Lehman Brothers' and 'JPMorgan Chase', while the entities in listB are 'JP Morgan Chase' and 'Lehman Brothers'. The only difference is in the spelling and formatting of 'JPMorgan Chase'. Since the entities are the same but in different formats, we can consider them as matching.",\n "decision": 1\n} |
| - Merrill Lynch\n- Bank of America | Merill Lynch, Bank of America | {\n "reasoning": "The two lists do not contain the exact same entities as listA has additional hyphens and listB has a comma instead of a hyphen.",\n "decision": 0\n} |
| Lehman Brothers | Lehman Brothers | {\n "reasoning": "Both listA and listB contain the same entity 'Lehman Brothers'.",\n "decision": 1\n} |
| - American International Group (AIG) | American International Group | {\n"reasoning": "The entities in listA and listB are very similar, with listA including the abbreviation 'AIG' at the end. This difference does not change the core entity being referred to, which is 'American International Group'. Therefore, the lists contain the same entity.",\n"decision": 1\n} |
Finally, it's time to pull the decision out of the JSON structs returned by OpenAI. There's just one complication: the chat_completions responses are strings, not JSON structs. We can resolve this with Pixeltable's handy apply method, which turns any Python function into a Pixeltable function. In this case, we'll apply the Python function json.loads to parse the string into a JSON struct.
In [19]:
import json
t['eval_decision'] = t.eval_output.apply(json.loads).decision
Computing cells: 100%|███████████████████████████████████████████| 6/6 [00:00<00:00, 662.17 cells/s] Added 6 column values with 0 errors.
In [20]:
t.select(t.response, t.ground_truth, t.eval_output, t.eval_decision).show()
Out[20]:
| response | ground_truth | eval_output | eval_decision |
|---|---|---|---|
| Lehman Brothers, Federal Reserve Bank | Lehman Brothers | {"reasoning": "listA contains an additional entity 'Federal Reserve Bank' that listB does not have.", "decision": 0} | 0 |
| - The Federal Reserve\n- AIG | American International Group | {"reasoning": "The entity 'AIG' is present in both lists, as 'AIG' in listA and 'American International Group' in listB. Therefore, the lists match.", "decision": 1} | 1 |
| - Lehman Brothers\n- JPMorgan Chase | JP Morgan Chase, Lehman Brothers | {\n "reasoning": "The entities in listA are 'Lehman Brothers' and 'JPMorgan Chase', while the entities in listB are 'JP Morgan Chase' and 'Lehman Brothers'. The only difference is in the spelling and formatting of 'JPMorgan Chase'. Since the entities are the same but in different formats, we can consider them as matching.",\n "decision": 1\n} | 1 |
| - Merrill Lynch\n- Bank of America | Merill Lynch, Bank of America | {\n "reasoning": "The two lists do not contain the exact same entities as listA has additional hyphens and listB has a comma instead of a hyphen.",\n "decision": 0\n} | 0 |
| Lehman Brothers | Lehman Brothers | {\n "reasoning": "Both listA and listB contain the same entity 'Lehman Brothers'.",\n "decision": 1\n} | 1 |
| - American International Group (AIG) | American International Group | {\n"reasoning": "The entities in listA and listB are very similar, with listA including the abbreviation 'AIG' at the end. This difference does not change the core entity being referred to, which is 'American International Group'. Therefore, the lists contain the same entity.",\n"decision": 1\n} | 1 |
Updated about 1 month ago