text-generation

Effortlessly Generate SQL Queries with Sqlcoder 70b Alpha

26 Apr 2025
Effortlessly Generate SQL Queries with Sqlcoder 70b Alpha

In today's data-driven world, efficiently retrieving and analyzing information is crucial for businesses. The Sqlcoder 70b Alpha service provides a powerful Cognitive Action designed to simplify the process of generating SQL queries. By leveraging advanced AI capabilities, developers can quickly create accurate SQL queries tailored to specific questions based on the provided database metadata. This not only accelerates development but also enhances the precision of data retrieval and analysis.

Imagine a scenario where a business analyst needs to compare sales figures from different regions. Manually crafting SQL queries can be time-consuming and prone to errors. With Sqlcoder 70b Alpha, developers can automate this process, allowing them to focus on deriving insights rather than writing complex SQL code. Common use cases include generating reports, performing data analysis, and building dynamic applications that require real-time data queries.

Prerequisites

To get started, you'll need an API key for the Cognitive Actions service and a general understanding of making API calls.

Generate SQL Query

The "Generate SQL Query" action is designed to construct SQL queries that answer specific questions based on the metadata of a given database. This action improves predictive accuracy through advanced parameters such as top-k filtering, nucleus sampling, and temperature control.

Input Requirements

To utilize this action, you will need to provide:

  • question: A string representing the question to be answered by the SQL query (e.g., "Do we get more sales from customers in New York compared to customers in San Francisco?").
  • tableMetadata: A string that defines the schema of the database, including tables, columns, and their relationships.
  • Additional optional parameters include:
    • topK: Number of highest probability tokens to consider for generating the output (default is 50).
    • topP: Probability threshold for nucleus sampling (default is 0.95).
    • doSample: A boolean indicating whether to use sampling (default is true).
    • temperature: A scalar to affect randomness in predictions (default is 0.7).
    • maxNewTokens: Maximum number of tokens to generate in the output (default is 512).
    • promptTemplate: A template for formatting the input prompt (defaults to a SQL generation task format).

Expected Output

The output will be a SQL query that can be executed against the specified database schema. The query will be constructed to directly address the provided question, ensuring relevance and accuracy.

Use Cases for this Action

  • Automated Reporting: Quickly generate SQL queries that compile sales data, customer insights, or inventory levels for regular reporting needs.
  • Data Analysis: Facilitate data exploration by allowing analysts to ask questions in natural language and receive structured queries as responses.
  • Dynamic Applications: Integrate this action into applications that require user-driven queries, enabling users to extract data without needing SQL knowledge.
import requests
import json

# Replace with your actual Cognitive Actions API key and endpoint
# Ensure your environment securely handles the API key
COGNITIVE_ACTIONS_API_KEY = "YOUR_COGNITIVE_ACTIONS_API_KEY"
# This endpoint URL is hypothetical and should be documented for users
COGNITIVE_ACTIONS_EXECUTE_URL = "https://api.cognitiveactions.com/actions/execute"

action_id = "1e0ef21f-fb52-42ed-8d4a-4b3eb0f4ded9" # Action ID for: Generate SQL Query

# Construct the exact input payload based on the action's requirements
# This example uses the predefined example_input for this action:
payload = {
  "topK": 50,
  "topP": 0.95,
  "doSample": false,
  "question": "Do we get more sales from customers in New York compared to customers in San Francisco? Give me the total sales for each city, and the difference between the two.",
  "temperature": 0,
  "maxNewTokens": 512,
  "tableMetadata": "CREATE TABLE products (\n  product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n  name VARCHAR(50), -- Name of the product\n  price DECIMAL(10,2), -- Price of each unit of the product\n  quantity INTEGER  -- Current quantity in stock\n);\n\nCREATE TABLE customers (\n   customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\n   name VARCHAR(50), -- Name of the customer\n   address VARCHAR(100) -- Mailing address of the customer\n);\n\nCREATE TABLE salespeople (\n  salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson \n  name VARCHAR(50), -- Name of the salesperson\n  region VARCHAR(50) -- Geographic sales region \n);\n\nCREATE TABLE sales (\n  sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\n  product_id INTEGER, -- ID of product sold\n  customer_id INTEGER,  -- ID of customer who made purchase\n  salesperson_id INTEGER, -- ID of salesperson who made the sale\n  sale_date DATE, -- Date the sale occurred \n  quantity INTEGER -- Quantity of product sold\n);\n\nCREATE TABLE product_suppliers (\n  supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\n  product_id INTEGER, -- Product ID supplied\n  supply_price DECIMAL(10,2) -- Unit price charged by supplier\n);\n\n-- sales.product_id can be joined with products.product_id\n-- sales.customer_id can be joined with customers.customer_id \n-- sales.salesperson_id can be joined with salespeople.salesperson_id\n-- product_suppliers.product_id can be joined with products.product_id",
  "promptTemplate": "### Task\nGenerate a SQL query to answer [QUESTION]{question}[/QUESTION]\n\n### Instructions\n- If you cannot answer the question with the available database schema, return 'I do not know'\n\n### Database Schema\nThe query will run on a database with the following schema:\n{table_metadata}\n\n### Answer\nGiven the database schema, here is the SQL query that answers [QUESTION]{question}[/QUESTION]\n[SQL]\n"
}

headers = {
    "Authorization": f"Bearer {COGNITIVE_ACTIONS_API_KEY}",
    "Content-Type": "application/json",
    # Add any other required headers for the Cognitive Actions API
}

# Prepare the request body for the hypothetical execution endpoint
request_body = {
    "action_id": action_id,
    "inputs": payload
}

print(f"--- Calling Cognitive Action: {action.name or action_id} ---")
print(f"Endpoint: {COGNITIVE_ACTIONS_EXECUTE_URL}")
print(f"Action ID: {action_id}")
print("Payload being sent:")
print(json.dumps(request_body, indent=2))
print("------------------------------------------------")

try:
    response = requests.post(
        COGNITIVE_ACTIONS_EXECUTE_URL,
        headers=headers,
        json=request_body
    )
    response.raise_for_status() # Raise an exception for bad status codes (4xx or 5xx)

    result = response.json()
    print("Action executed successfully. Result:")
    print(json.dumps(result, indent=2))

except requests.exceptions.RequestException as e:
    print(f"Error executing action {action_id}: {e}")
    if e.response is not None:
        print(f"Response status: {e.response.status_code}")
        try:
            print(f"Response body: {e.response.json()}")
        except json.JSONDecodeError:
            print(f"Response body (non-JSON): {e.response.text}")
    print("------------------------------------------------")

Conclusion

The Sqlcoder 70b Alpha service empowers developers to generate SQL queries effortlessly, significantly enhancing productivity and accuracy in data management tasks. By automating the SQL query generation process, developers can focus on deriving insights and building applications that respond to real-time data needs. To leverage this powerful action, start integrating it into your applications and watch how it transforms your data querying experience.

← Back to All Articles