starlake-ai / StarBake

Apache License 2.0
2 stars 1 forks source link

Invalid SQL syntax: Table valued function cannot specify database name on transform using SL env Var #7

Closed zedach closed 2 weeks ago

zedach commented 2 weeks ago

Hi

using SL 1.3.0

python3 _scripts/dummy_data_generator.py OK ./starlake import OK ./starlake load OK ./starlake transform --name Customers.CustomerLifetimeValue KO

Exception in thread "main" org.apache.spark.sql.catalyst.parser.ParseException: 
[INVALID_SQL_SYNTAX.INVALID_TABLE_VALUED_FUNC_NAME] Invalid SQL syntax: Table valued function cannot specify database name: `starbake`.`Orders`.(line 17, pos 0)

== SQL ==
CREATE TABLE Customers.CustomerLifetimeValue USING parquet     AS With CustomerOrderSummary AS (
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(o.order_id) AS order_count,
SUM(o.products_total) AS total_spend,
AVG(o.products_total) AS average_spend_per_order
FROM
`starbake`.`Customers` c
LEFT JOIN (
SELECT
customer_id,
order_id,
SUM(product.price * product.quantity) AS products_total
FROM
starbake.Orders
^^^
(products) AS product
GROUP BY
customer_id,
order_id
) o
ON
c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name,
c.join_date
)
SELECT
COS.customer_id,
COS.first_name,
COS.last_name,
COS.order_count,
COS.total_spend,
COS.average_spend_per_order,
CASE
WHEN COS.order_count > 0 THEN COS.total_spend / COS.order_count
ELSE 0
END AS lifetime_value
FROM
CustomerOrderSummary COS;

the error seems to be due to non valorisation of CROSS_JOIN_UNNEST variable in

            FROM
                starbake.Orders
                    {{CROSS_JOIN_UNNEST}}(products) AS product

Expected

            FROM
                starbake.Orders
                    LATERAL VIEW EXPLODE(products) AS product

actual

            FROM
                starbake.Orders(products) AS product
hayssams commented 2 weeks ago

Happen when the sql file has now yaml file. Fixed and pushed as 1.3.1-SNAPSHOT