voltrondata / bft

The (B)ig (F)unction (T)axonomy is a detailed reference for common compute functions executed by different libraries, databases, and tools.

The B(ig) F(unction) T(axonomy)

The BFT aims to be a comprehensive catalogue of functions. Functions are the backbone of any compute system, but they are chronically under documented and often full of corner cases whose behavior differs in various systems. By documenting exhaustively documenting these corner cases we hope to make it possible for systems to fully describe their behaviors. This will make it easier to know what problems will be encountered switching between systems and, in some cases, make it possible to obtain the correct behavior through expression transformation or a precise application of function options.

Major Concepts

The following concepts describe the various sources of information that the BFT collects.

Substrait

The BFT is an extension of (or perhaps a supplement to) Substrait. We consider Substrait to be both a canonical source of function names as well as a format for encoding function calls, including options.

However, Substrait is terse and does not go into lengthy descriptions of function behavior. It does not provide examples, motivation, or classification of functions. As a result, the BFT starts with information from Substrait but then layers on additional information from its own sources.

Test Cases

The BFT includes a collection of test cases in the cases directory. These test cases explore different options, failure scenarios, or data types. These test cases are continuously verfied by running against different compute engines.

Supplemental Information

The BFT includes more extensive prose documentation for functions. It also can provide information on the motivation behind functions, practical use cases for functions, and a discussion of common properties that functions may have. This information is provided in the supplemental directory.

Dialects

Most compute systems are not capable of satisfying the full range of functions and options that are available. Many compute systems are not capable of picking different options. The information in dialects represents facts about individual compute engines that help us to understand how that system behaves. For example, we list which type classes a system supports and which options are implicitly specified when a function is called in that system.

Supported Dialects

BFT currently supports testing of the following dialects:

• cuDF
• Datafusion
• DuckDB
• Postgres
• SQLite
• Snowflake

Workflows

These workflows describe how this information is used.

Environment Setup

cd bft
conda create -n bft -c rapidsai -c conda-forge -c nvidia rapids=24.04 python=3.11 cuda-version=12.2
conda activate bft
pip install -r requirements.txt

Running Python Tests

Tests are located in the bft/tests folder and can be run with the following:

```
cd bft
pytest bft/tests/test_duckdb.py

bft/tests/test_duckdb.py ........sssssssssssssssssssssssss...............xxxx.......................xx....... [  9%]
.............................................xxxx............................................................ [ 22%]
...........xxxx.........x.x.x................................................................................ [ 35%]
....................x..x...........................xx..xx.....xx..xx.....xx..xx.............................. [ 48%]
............................................................................................................. [ 60%]
...sssssssssssssss.......................................................................ssssss.............. [ 73%]
......xx..xxx......x...............xxx..............sssss..........sssss........xxx.....................x.x.. [ 86%]
........sssss...........................ssssssssss....x..............sssss........xxx..ssss.....sssssssssss.. [ 98%]
...xx.x..                                                                                                     [100%]
==================================== 713 passed, 91 skipped, 52 xfailed in 2.56s ===================================
```

Local Dialect Testing

Testing the dialects locally will require different frameworks/libraries. Following steps mentions reference methods:

• cuDF
  For testing functions in cuDF, a GPU powered system having RAPIDS/cuDF needs to be installed. Instructions on RAPIDS installation can be found here

  Alternatively, a Google Colab notebook with GPU can be used for testing. RAPIDS provides a template notebook for installation and further usage.

  import cudf
  a = cudf.Series([1, 2, 3])
  a.pow(2)  # Power function
  

• Datafusion
  For testing with Datafusion, its CLI SQL console can be used. Installation instructions for various architectures can be found here.

  $ datafusion-cli
  > select power(2,3);
  

• DuckDB
  For testing with DuckDB, its CLI console or Python interface can be used. Installation instructions for various architectures and configuration can be found here.

  $ duckdb
  D select pow(1,2);
  

• Postgres
  The Postgres CLI can be utilized for testing. Instructions for installation on various architectures through installers, packages, or source code can be found here.

  $ psql postgres
  postgres=# SELECT 2147483647::integer % 5;
  

• SQLite
  SQLite testing can be conducted on the CLI after its installation.

  $ sqlite3 data.db
  sqlite> select pow(2,4);
  

• Velox
  For testing with Velox, its Python interface can be installed via pip.

  import pyvelox.pyvelox as pv
  expr = pv.Expression.from_string("power(a,4)")
  a = pv.from_list([1, 3])
  eval = expr.evaluate({"a":a})
  

Snowflake
For testing with Snowflake, its Python interface can be installed via pip.

import snowflake.connector
conn = snowflake.connector.connect(
  user='user',
  password='password',
  account='account',
  warehouse='warehouse',
  database='database',
  schema='schema'
)
cur = conn.cursor()
cur.execute("SELECT POW(2,3)")

To run the tests, follow the below steps:

• Update bft/testers/snowflake/config.yaml file with snowflake user,account,db,schema etc.
• Set the password in the environment variable SNOWSQL_PWD

  export SNOWSQL_PWD=<your password> 
  

• Run the tests using the below command

  pytest bft/tests/test_snowflake.py
  

CI environment

We perform continuous integration on a variety of systems to verify the integrity of our dialect files and test cases. For each test case a dialect file should tell us how that system is expected to behave. We then run the test case against the system and verify the behavior is what we expected.

The BFT Site

All of this information is rolled up into a website that serves as reference documentation for these compute functions. This is intended for consumption by humans that wish to learn more details about specific functions.

The site is available here.

bft's Issues

Add support for testing the 0-arg version of count function

Current there is only support for testing the 1-arg version of the count function

Having expected results for approx_count_distinct seems strange...

Since there is no clear definition of the approximation algorithm of approx_count_distinct, it seems weird for BFT to include expected results. Any thoughts on how to deal with cases where the underlying algorithm is approximate by definition?

Add Snowflake dialect

I propose adding support for the Snowflake SQL dialect to BFT.

1. Add list of available snowflake functions
2. Add tests (disable by default)

We are currently working on this feedback and guidance are welcome.

SQLite returns None with trigonometric functions

While the On_domain_error option only provides ERROR and NAN as valid choices for trigonometric functions, few of them actually returns None or a null value.

Classify functions into categories

With all the functions now the BFT mentions in the Index page, it would be better to classify them into appropriate categories for a better user experience.

Add ability for backend engines to have "engine-specific behavior"

This came up in the following discussion: substrait-io/substrait#615 (comment)

For BFT / dialect testing I think we will need to add the ability for an engine to have an 
"engine-specific behavior"  which is defined as part of the dialect but not rooted in any 
substrait yaml. I think, for now, a minimal standard  of "at least 2 engines" will be a good guideline.

Gh-pages hosting seems to be disconnected

I'm not able to access https://voltrondata.github.io/bft/

Generate BFT pages with available information

Currently, the BFT will not generate pages for functions whose all the information is not available (options behaviour, dialect behaviour, etc.). Instead of this, it would be better to generate pages even with the incomplete correct information it has.

Add support to pass format string into functions (strftime, strptime, etc)

Equating floating point values

Some of the functions in the BFT result in floating point values. Currently, we check the expected value by just equating them over ==, i.e. if expected_result == actual_result. This fails in a lot of cases where the result is not the same due to the engine architecture and the function do not have a rounding option.

Unary function in Datafusion

Datafusion's negate function works either in place (-x) or attached (x.neg()). The BFT does not support such configuration of function with only one operand.

Add support for testing aggregate functions that take multiple args

string_agg

Functionality to map multiple dialect functions

Currently, the BFT assumes that every dialect has only a single implementation of a function. However, SQLite having two implementations for the modulus operator(a mod function and a % operator) contradicts it. Thus, the BFT shall have the option to map multiple functions to a single substrait function, i.e. it shall maintain a many-to-one relation.