cube-js / cube-js/cube

Error when defining UDF in cube 'sql' parameter

Open
#6,780 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
SQL compilation error:
syntax error line 4 at position 7 unexpected 'CREATE'.

Cube is unable to create a UDF as part of the sql parameter. This was tested on Snowflake but may affect other data sources.

On Cube Cloud in SQL Runner, the same SQL returns a permissions error, but the same user can execute it from the Snowflake Console

**To Reproduce**
Steps to reproduce the behavior:
1. create a cube (see data model below) that includes a 'CREATE FUNCTION' command in the sql parameter.
2. In playground, add the measure and dimension and run

**Expected behavior**
A clear and concise description of what you expected to happen.

**Screenshots**
![image](https://github.com/cube-js/cube/assets/25996568/32b05908-562c-4e24-9cab-fd43bc118af9)

![image](https://github.com/cube-js/cube/assets/25996568/2bac615c-4e17-44ce-b687-5fc3cad38768)

**Minimally reproducible Cube Schema**
This example is from [Snowflakes UDF documentation](https://docs.snowflake.com/en/sql-reference/udf-overview#udf-example). It works in the Snowflake console, running as this same user.

```yaml
cubes:
- name: udf_test
sql: >
CREATE OR REPLACE FUNCTION addone(i int)
RETURNS INT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'addone_py'
as
$$
def addone_py(i):
return i+1
$$;
SELECT addone(3) as col1;

measures:
- name: count
type: count

dimensions:
- name: col1
sql: col1
type: number
```

**Version:**
0.33.31

**Additional Context:**
@igorlukanin noted there's a [restriction on the Snowflake side](https://docs.snowflake.com/en/developer-guide/node-js/nodejs-driver-execute#executing-a-batch-of-sql-statements-multi-statement-support) - alternative implementations may be:

- introducing an option to run multi-statement queries against Snowflake
- allowing to run an arbitrary set of SQL statements after a driver connects to the data source: all UDFs might as well go there

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.