Error when defining UDF in cube 'sql' parameter
- 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**


**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
Assessment
This issue has not been assessed yet.