I'm trying to create some reusable functions within my SQLX file and I'm finding that if I use CREATE TEMP FUNCTION statements, that it fails when I try to execute the job.

Take this very basic example:

config {
  type: "table",
  schema: "debug",
  name: "test"
}

CREATE TEMP FUNCTION addition(a INT64, b INT64)
RETURNS INT64
AS (
    a + b
);

CREATE TEMP FUNCTION multiply(a INT64, b INT64)
RETURNS INT64
AS (
    a * b
);

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)

SELECT
    x,
    y,
    addition(x, y) AS added,
    multiply(x, y) as multiplied
FROM numbers

Running it is fine:

However when I execute the job, it immediately fails with the error: "Syntax error: Unexpected keyword CREATE at [31:1]":

Am I shotting SQLX correct here? Any ideas how I can use multiple TEMP UDFs within Dataform SQLX files?

I know I can just convert the TEMP UDFs to non-TEMP, e.g. CREATE OR REPLACE FUNCTION in a separate SQLX file each, but that's kind of a drag -- I had hoped to contain everything in one SQLX file.

Another idea is that I can convert the TEMP UDFs to Javascript which outputs the UDF SQL code when invoked within SQLX. So I'm not really blocked, I just hoped to do a more cleaner SQL way of things.

I'm trying to create some reusable functions within my SQLX file and I'm finding that if I use CREATE TEMP FUNCTION statements, that it fails when I try to execute the job.

Take this very basic example:

config {
  type: "table",
  schema: "debug",
  name: "test"
}

CREATE TEMP FUNCTION addition(a INT64, b INT64)
RETURNS INT64
AS (
    a + b
);

CREATE TEMP FUNCTION multiply(a INT64, b INT64)
RETURNS INT64
AS (
    a * b
);

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)

SELECT
    x,
    y,
    addition(x, y) AS added,
    multiply(x, y) as multiplied
FROM numbers

Running it is fine:

However when I execute the job, it immediately fails with the error: "Syntax error: Unexpected keyword CREATE at [31:1]":

Am I shotting SQLX correct here? Any ideas how I can use multiple TEMP UDFs within Dataform SQLX files?

I know I can just convert the TEMP UDFs to non-TEMP, e.g. CREATE OR REPLACE FUNCTION in a separate SQLX file each, but that's kind of a drag -- I had hoped to contain everything in one SQLX file.

Another idea is that I can convert the TEMP UDFs to Javascript which outputs the UDF SQL code when invoked within SQLX. So I'm not really blocked, I just hoped to do a more cleaner SQL way of things.

• google-cloud-platform
• google-bigquery
• dataform

1 Answer 1

Well, I remembered in SQLX there are pre_operations { ... } so I experimented with this:

config {
  type: "table",
  schema: "debug",
  name: "test"
}

pre_operations {
  CREATE TEMP FUNCTION addition(a INT64, b INT64)
  RETURNS INT64
  AS (
      a + b
  );
---
  CREATE TEMP FUNCTION multiply(a INT64, b INT64)
  RETURNS INT64
  AS (
      a * b
  );
}

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)

SELECT
    x,
    y,
    addition(x, y) AS added,
    multiply(x, y) as multiplied
FROM numbers

This works well when the job is executed, however it doesn't work when pressing "Run":