# Dynamic Data Functions {% hint style="info" %} Dynamic Data Functions are currently in development. This documentation is for feedback and development purposes. {% endhint %} ## Overview There are certain types of calculations that are difficult to perform in SQL, particularly recursive calculations that depend upon the result of prior calculations, or that depend on external data. Dynamic Data Functions (DDF) allow you to perform these operations within ChartSQL for advanced use cases. DDFs are like advanced excel formulas. Your ChartSQL queries can return functions which are executed before the data is rendered. DDFs are particularly useful for complex, iterative, and conditionally dependent calculations often found in financial modeling, simulations, and analytics ## Quick Start You enable Dynamic Data Functions by adding the directive `@ddf: true` to your script. This tells ChartSQL Studio to check each cell in your result for DDFs to be evaluated. A DDF is any cell that returns a string like `=function(){ //code }` ```sql -- @ddf: true SELECT 'James' as first_name, 'Smith' as last_name, '=function(row){ return row.first_name & " " & row.last_name }' as full_name ``` ## DDFs vs Common Table Expressions (CTEs) and Recursive CTEs SQL Common Table Expressions (CTE) and Recursive CTEs are the native way to perform complex hierarchical and self-referential calculations, however DDFs can do more than CTEs: * **Dynamic Execution**: DDFs can execute additional runtime logic, manipulate data dynamically, and even fetch or integrate live data during execution. * **Inline Functionality**: With DDFs, additional logic (like calling external APIs, conditional recursion) can be executed inline without needing additional stored procedures or scripts. * **Direct Data Manipulation**: DDFs allow on-the-fly data manipulation (like adding rows based on runtime conditions), which is not possible directly within SQL or its CTEs. ## Evaluation Order DDF cells are always evaluated from top to bottom and left to right. ## Limitations ### Post Processing Only Dynamic Data Functions are processed by ChartSQL after the data is returned, and so you cannot wrap the result in an outer query or process it further with SQL. ### ChartSQL Studio Desktop Only Dynamic Data Functions have full access to the ChartSQL Studio Server process, and so for security purposes, are not available on ChartSQL Studio Cloud, and cannot be published to DashSQL. ## Execution Context For each DDF invocation, your DDF can access variables about the invocation. You can use these variables to access data and perform dynamic calculations. In the quick start example, the DDF makes use of the `row` context variable, which returns a structure (hashmap) of the currently executing row. It uses this data to concatenate the first name and last name into the full name. ``` =function(row){ return row.first_name & " " & row.last_name } ``` ### Context Variables Reference | Name | Description | | ----- | ----------------------------------------------------------------------------------------------------------------------------------------------- | | rid | The current integer index of the executing row. Starting at 1. | | cid | The current integer index of the executing column. Starting at 1. | | field | The name of the currently executing column field. | | data | A reference to the query data. | | prev | The result of the previous calculation. | | row | The data for the currently executing row. | | func | The string function of the currently executing DDF. Use this to reappend the function onto new rows if creating dynamically generating queries. | ## Examples #### You will typically use DDFs for the following situations: 1. **Recursive calculations.** Complex recursive calculations are straightforward and computationally efficient as DDFs. DDFs naturally accommodate recursion without complex SQL constructs. 2. **Integration with real-time data** from external APIs. DDFs allow ChartSQL to embed external functionality directly into queries, enhancing flexibility and reducing the latency you might face in a traditional ETL process that periodically updates a data warehouse. ### Fibonacci Series Consider this complex SQL query to generate a Fibonacci Series (for each row, add the previous two rows). This is a complicated query, hard to read and debug. These kinds of recursive queries are difficult to do in SQL when the business logic gets more complex. ```sql -- MySQL 8.0 Fibonacci function WITH RECURSIVE Fibonacci (n, fib_n, next_fib_n) AS ( -- Initial subquery: defines the starting point of the recursion. SELECT 1 AS n, 0 AS fib_n, -- F(0) 1 AS next_fib_n -- F(1) UNION ALL -- Recursive subquery: defines the recursive step. SELECT n + 1, next_fib_n AS fib_n, fib_n + next_fib_n AS next_fib_n FROM Fibonacci WHERE n < 10 -- Specifies how many Fibonacci numbers to generate. ) -- Final SELECT to output the result SELECT n, fib_n FROM Fibonacci; ``` However with DDFs, this is more understandably accomplished: ```sql -- @ddf: true SELECT 0 as num UNION ALL SELECT 1 as num UNION ALL SELECT '=function( rid, data, func ){ // Calculate the next value in the series var result = data["num"][rid - 1] + data["num"][rid - 2]; // Add a new row to the end of the query using the same function // which achieves the recursion if(data.recordCount < 10){ data.addRow({num: func}); } return result; }' as num ``` ### Compound Interest Rate ```sql -- @ddf: true SELECT 1 as year, 10000 as starting_amount, -- Starting amount 0.05 as interest_rate, '=function(rid, data, func, row){ var result = row.starting_amount + row.starting_amount * row.interest_rate; if (row.year < 10) { // Recurse for 10 years data.addRow({ year: row.year + 1, starting_amount: result, interest_rate: row.interest_rate, final_amount: func }); } return result; }' as final_amount ``` The equivalent Recursive CTE (MySQL 8.0) ```sql WITH RECURSIVE CompoundInterest AS ( -- Initial subquery: defines the starting point of the recursion. SELECT 1 AS year, 10000 AS starting_amount, 0.05 AS interest_rate, 10500 AS final_amount -- Initial final_amount equals the starting amount UNION ALL -- Recursive subquery: defines the recursive step. SELECT year + 1 AS year, final_amount AS starting_amount, -- Previous year's final_amount becomes this year's starting_amount 0.05 AS interest_rate, -- Assuming the interest rate remains constant final_amount + final_amount * 0.05 AS final_amount -- Calculate the new final_amount FROM CompoundInterest WHERE year < 10 -- Continue recursion until year reaches 10 ) -- Final SELECT to output the result SELECT * FROM CompoundInterest; ```