Beyond Text-to-SQL: Harnessing the Power of Function Calling for Time Series Data Insights

In today's data-driven world, time series data has become the lifeblood of countless enterprises. From monitoring application performance and IoT sensor streams to analyzing financial markets and user behavior, the ability to extract timely insights from time-stamped data is paramount. As this data deluge grows, the need for intuitive and efficient query interfaces becomes increasingly critical. Natural language interfaces, powered by Large Language Models (LLMs), offer a promising avenue to democratize data access and empower users of all technical backgrounds to ask questions and get answers directly from their data.

While Text-to-SQL has emerged as a popular approach for natural language database querying, its application to complex time series data, especially within the context of star schemas, presents significant hurdles. Translating nuanced time series questions into accurate and performant SQL queries can quickly become a maze of intricate joins and specialized aggregations. 

In this blog post, we'll explore the limitations of Text-to-SQL for time series data and introduce a more powerful and flexible alternative: Function Calling.

We will delve into three distinct function calling approaches we've been experimenting with, demonstrating how this paradigm shift can unlock a new level of expressiveness and efficiency in querying your time series data, ultimately leading to faster insights and better business decisions.

The Challenge: Querying Time Series Star Schemas with Natural Language

Time series databases, often structured in star schemas for optimized analytical processing, present unique challenges when accessed via natural language interfaces. Let's examine some key pain points where Text-to-SQL can fall short:

Challenge 1: The Complexity of Joins in Text-to-SQL for Star Schemas

Imagine you want to answer the question: "What is the average CPU utilization for servers in the 'US-West' region over the last hour?"In a star schema, your metric data (CPU utilization) might reside in a fact table (metrics_hourly), while region information is stored in a dimension table (region_dim) and device details in device_dim. A Text-to-SQL approach would need to generate a complex SQL query involving joins across these tables.

For instance, the generated SQL might look something like this:

SELECT 
      AVG(mh.metric_value)
FROM 
     metrics_hourly mh

     JOIN device_dim dd ON mh.device_id = dd.id

     JOIN region_dim rd ON dd.region_id = rd.id

WHERE 
   mh.metric_id = (SELECT id FROM metric_dim WHERE value = 'CPU Utilization')

  AND rd.value = 'US-West'

  AND mh.time >= NOW() - INTERVAL '1 hour';

While achievable, crafting this multi-table join correctly from natural language is a complex task for an LLM. Subtle variations in phrasing (“CPU Utilization” and “US-West” may not be saved as it is), misunderstandings of table relationships, or errors in join conditions can easily lead to incorrect SQL and inaccurate results.

Challenge 2: Missing Specialized Time Series Aggregations

Time series databases like TimescaleDB, InfluxDB, and others often offer specialized aggregation functions optimized for time-based data. For example, TimescaleDB provides functions like time_bucket() for efficient time-based grouping, first(), last() for retrieving values at specific time points, and interpolation functions for handling missing data.

If you ask:

Show me the hourly average CPU utilization, time-bucketed by 1-hour intervals for server 'Server-001' today

A naive Text-to-SQL approach might generate standard SQL aggregations that are less efficient and don't fully leverage the power of the underlying time series database. Ideally, we'd want the LLM to understand the time-bucketed nature of the query and utilize TimescaleDB's time_bucket() function:

SELECT 
       time_bucket('1 hour', time) AS hourly_interval,
       AVG(metric_value) 
FROM 
       metrics_hourly

WHERE 
       metric_id = (SELECT id FROM metric_dim WHERE value = 'CPU Utilization')

       AND device_id = (SELECT id FROM device_dim WHERE value = 'Server-001')

       AND time >= date_trunc('day', NOW())

GROUP BY hourly_interval

ORDER BY hourly_interval;

Text-to-SQL models often struggle to consistently generate and utilize these database-specific time series functions, potentially leading to less performant and less feature-rich queries.

Challenge 3: Limitations in Expressiveness for Nuanced Time Series Queries

Natural language often allows for more nuanced and context-rich queries than easily translated into standard SQL.

Consider a question like:

Analyze the trend of network latency KPI for our European regions over the past week, highlighting any significant increases.

While Text-to-SQL can handle basic aggregations and filtering, expressing concepts like "trend analysis," "significant increases," or combining KPIs and dimensions in complex analytical requests can push its boundaries. Capturing the full intent and translating it into SQL that accurately reflects the desired analysis becomes increasingly challenging.

Our Solution: Function Calling Approaches for Time Series Data

To overcome these limitations and unlock the full potential of natural language interaction with time series data, we've explored Function Calling. This approach shifts the paradigm from directly generating SQL to enabling the LLM to understand the intent of the query and call pre-defined functions (APIs) tailored to your specific database schema and time series querying needs.

We've experimented with three distinct Function Calling strategies, each offering a different balance of flexibility, expressiveness, and efficiency:

Approach 1: Filtered Table + Code Execution - Extreme Flexibility, Deferred Analysis

In this approach, the Function Call is designed to be relatively simple. The LLM identifies the core parameters of the query – time range, metrics/KPIs, dimensions, and granularity – and calls a function that retrieves a filtered table from the database based on these parameters. This table, often in CSV or JSON format, is then passed to a separate code execution environment, such as Python or a serverless function.

Function Call (Simplified Example):

{

  "function_name": "get_timeseries_table",

  "arguments": {

    "time_range": "last_hour",

    "metrics": ["CPU Utilization"],

    "region": "US-West",

    "granularity": "hourly"

  }

}

Advantages:

• Unmatched Flexibility: The code execution environment allows for arbitrary post-processing of the retrieved data. Users can perform complex statistical analysis, create custom visualizations, integrate with external systems, and more.

• Simplified Function Definition: The function call itself remains focused on data retrieval, keeping the function definition relatively simple and manageable.

Disadvantages:

• Potential Inefficiency: Transferring potentially large filtered tables can be inefficient, especially for high-volume time series data.

• Security Considerations: Introducing a code execution environment requires robust security measures.

Best Suited For: Advanced data exploration, highly customized analysis by data scientists, and scenarios where the post-processing needs are unpredictable and diverse.

Approach 2: Comprehensive Function Call (Structured Query Dictionaries) - Maximum Query Expressiveness

This approach aims for maximum expressiveness within the Function Call itself. We define a highly structured function call, using lists of dictionaries to represent complex query components – table selection, column filters, aggregations, groupings, and even joins. The LLM's task is to construct these structured query dictionaries based on the natural language request.

Function Call (Example Structure):


[

  {

    "table": "metrics_hourly",

    "columns": ["time", "device_name", "metric_value"],

    "filters": [

      {"column": "metric_name", "operator": "=", "value": "CPU Utilization"},

      {"column": "region_name", "operator": "=", "value": "US-West"}

    ],

    "aggregations": [{"column": "metric_value", "type": "AVG"}],

    "groupby": "device_name"

  }

]

Advantages:

• Unparalleled Expressiveness: Allows for highly complex analytical queries to be represented directly within the function call structure, offering fine-grained control over query construction.

• Potentially Efficient Query Execution: If the backend is designed to parse these structured dictionaries, it can translate them into optimized database queries without unnecessary data transfer.

Disadvantages:

• Function Definition Complexity: Designing and managing function definitions that can capture all possible query components becomes significantly more complex.

• LLM Challenge: Requires a highly sophisticated LLM to reliably generate these complex nested structures, increasing the risk of errors.

Best Suited For: Scenarios requiring very intricate analytical queries, systems with robust backend query processing capabilities, and minimizing reliance on external code execution.

Approach 3: Intent-Based Function Calling - Balancing Expressiveness and User-Friendliness

Our preferred approach strikes a balance between expressiveness and user-friendliness: Intent-Based Function Calling. Here, we categorize common user intents for time series data querying (e.g., "get metric value," "aggregate by region," "trend analysis"). For each intent, we define a specific Function Call with parameters tailored to that intent.

Example Intent: "Get Average Metric Value Grouped by Region"

Function Definition:

{

  "function_name": "get_avg_metric_by_region",

  "description": "Get the average value of a specified metric, grouped by region.",

  "parameters": {

    "type": "object",

    "properties": {

      "metric_id": {

        "type": "string",

        "description": "The ID or name of the metric to query."

      },

      "time_range": {

        "type": "string",

        "description": "The time range for the query (e.g., 'last hour', 'today')."

      }

    },

    "required": ["metric_id", "time_range"]

  }

}

Advantages:

• Balanced Approach: Offers a good level of expressiveness for common time series queries while maintaining relative simplicity in function definitions and LLM interaction.

• Intent Clarity: Intent classification makes the system more robust and easier to understand.

• User-Friendly Interface: Intents often align with how users naturally formulate their data questions, leading to a more intuitive user experience.

• Efficient Querying: Avoids retrieving entire tables and focuses on retrieving only the necessary data based on the identified intent and parameters.

Disadvantages:

• Less Flexible than Approach 1 for Custom Post-Processing: Limited in allowing users to perform completely arbitrary analysis beyond the predefined intents.

• Less Expressive than Approach 2 for Extremely Complex Queries: May not handle the most intricate analytical queries as comprehensively as Approach 2.

Best Suited For: General-purpose time series data access, business intelligence dashboards, operational monitoring, and scenarios where user-friendliness, efficiency, and manageability are key priorities.

Benefits of Function Calling for Time Series Data

In summary, Function Calling offers a compelling alternative to Text-to-SQL for unlocking the full potential of natural language interaction with time series data, especially within star schema environments. By adopting a Function Calling approach, enterprises can achieve:

• Enhanced Query Expressiveness: Capture more nuanced time series analysis requests beyond basic SQL translations.

• Increased Flexibility: Tailor function definitions to specific database schemas and time series functionalities.

• Improved Query Efficiency: Optimize data retrieval by focusing on intent and parameters rather than generating potentially complex and inefficient SQL.

• Greater User Friendliness: Create more intuitive and accessible natural language interfaces for users of varying technical skill levels.

• Future-Proofing for Advanced Analytics: Function Calling provides a foundation for incorporating more advanced time series analytics and machine learning workflows in the future.