How to Use OpenAI / ChatGPT API with ZappySys ODBC Driver in BI / ETL Tools

[nayan]

Introduction

In this article, we will demonstrate how to call the OpenAI / ChatGPT API to process text, images, audio, or video using the ZappySys JSON Driver. This driver enables you to interact with REST APIs, like OpenAI, directly from any ODBC-compliant application, such as Power BI, Excel, or SQL Server.

Key Concepts Demonstrated:

• Constructing effective system/user prompts
• Parsing structured JSON responses
• Configuring the ZappySys JSON Driver for OpenAI API integration

Prerequisites

• Download and install the ZappySys ODBC PowerPack
• Obtain your OpenAI API Key (follow the steps below)

Steps

Step 1: How to Obtain Your OpenAI API Key

To interact with OpenAI models like ChatGPT, GPT-4, DALL·E, or Whisper, you’ll need an API key. Here’s how to obtain it:

1. Create an OpenAI Account: Visit https://platform.openai.com.
2. Sign Up: Register using your email, Google, Microsoft, or Apple account.
3. Verify Your Email: Complete the verification process.
4. Log Into the OpenAI Platform: After signing up, go to the OpenAI Dashboard.
5. Generate an API Key: Click your profile icon → “View API Keys” or visit API Keys.
6. Create a New Secret Key: Click Create new secret key.
7. Store the Key Securely: Copy and store the key, as it will not be displayed again.
8. Set Up Billing (if required): Add a payment method via the Billing Settings page. New users receive $5 in free credits for the first 3 months.
9. Set Usage Limits (optional): You can configure spending limits to manage costs.

Best Practices for API Key Security:

• Keep your API key private — treat it like a password.
• Use environment variables or secret vaults to store the key securely.
• Revoke keys if exposed or unused.
  
  

Step 2: Create a New ODBC DSN Using ZappySys JSON Driver

1. Open ODBC Data Source Administrator: Search for ODBC in the Windows Start menu and open the ODBC Data Source Administrator.
   

   

2. Create a New Data Source: In the User DSN or System DSN tab, click Add to create a new data source.
   

   

3. Select ZappySys JSON Driver: In the ODBC Data Source Setup window, select the ZappySys JSON Driver and click Continue.
   

   

Step 3: Configure ODBC Connection for OpenAI API Calls

1. Use the following API URL for the create chat completion:
   https://api.openai.com/v1/chat/completions

2. In the Connection Type field, select HTTP connection.

3. Set the HTTP Request Method to POST.

4. In the request body, you can use this example from the API documentation:

   { 
     "model": "gpt-4.1", 
     "messages": [ 
       { "role": "developer", "content": "You are a helpful assistant." }, 
       { "role": "user", "content": "Hello!" } 
     ] 
   }
   

5. In Content-Type, select JSON (application/json).

6. Click Configure HTTP Settings and set the authentication type to Static: Token/API Key. Paste the API key you obtained earlier.

7. Save the configuration and test the connection.

8. Proceed to the Preview Tab to check the response.
   

   

Step 4: Sample SQL Query to Call OpenAI API

Here’s a sample SQL query using the ZappySys JSON Driver to parse multiple addresses via OpenAI:

SELECT * FROM "$"
WITH(
	-- Basic connection info
	DataConnectionType='HTTP',
	CredentialType='Token',

	Src='https://api.openai.com/v1/chat/completions',
	RequestContentTypeCode='ApplicationJson',
	RequestMethod='POST',
	Header='cache-control: no-cache || Accept: */*',

	-- Extract main response
	Alias='a',
	Filter='$.choices[0].message',

	-- Request body
	RequestData='{
	"model": "gpt-4o-mini",
	"response_format": { "type": "json_object" },
	"messages": [
		{
			"role": "system",
			"content": "<<
			You are an address parsing and geocoding API. Parse and clean address using the most accurate address database.
			For each address in the provided list, parse and output the following fields: 
				raw_address, street_number, street_name, city, state, zip_code, country, 
				latitude, longitude, confidence_score, is_valid, google_search_query, 
				error_message, error_flag.
			If invalid, set is_valid=false and return error_message. Return as JSON under key 'parsed_addresses'.>>"
		},
		{
			"role": "user",
			"content": "<<
			Parse the following addresses:
			123 main street, Alpharetta, GA 300004
			---ADDRESS_END---
			123 main st, Alpha retta, USA
			---ADDRESS_END---
			2 satvanville, rysan near texas2 building, ghandhinagar
			---ADDRESS_END---
			main st, Alpha retta, USA
			---ADDRESS_END---
			BAD Address, XYZ Street, Invalid City, AB 99999
			---ADDRESS_END---"
		}
	],
	"max_tokens": 1000,
	"temperature": 0.0
}',

	-- Join parsed result
	JOIN1_Alias='b',
	JOIN1_Data='[$a.content$]',
	JOIN1_Filter='$.parsed_addresses',

	-- Metadata
	META='
		raw_address:string;
		google_search_query:string;
		street_number:string;
		street_name:string;
		city:string;
		state:string;
		zip_code:string;
		country:string;
		latitude:double;
		longitude:double;
		confidence_score:double;
		is_valid:string;
		error_message:string;
		error_flag:string;
		p_model:string;
		p_usage_total_tokens:int64;
		role:string;
		content:string;
	'
)

Conclusion

Integrating the ZappySys JSON ODBC Driver with the OpenAI API unlocks the power of AI-driven capabilities like address parsing and much more. By directly connecting ChatGPT to applications such as Power BI, Excel, or SQL Server, you can streamline complex workflows, including data enrichment, content generation, and intelligent parsing. This integration makes advanced GPT-4 capabilities accessible in BI and ETL environments, simplifying AI-driven tasks.

Visit our official page to start building smarter, faster, and more scalable solutions with ZappySys.

References

• JSON Driver
• JSON Driver documentation
• Blog articles
• ODBC PowerPack

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.