pypika
pypika copied to clipboard
SnowflakeQuery Table alias should not be quoted
from pypika import Database, Schema, Table
from pypika.dialects import SnowflakeQuery
ArrayCompact = CustomFunction("ARRAY_COMPACT", ["COLUMN"])
ANALYTICS_DB = Database("ANALYTICS")
BATCH_ETL_SCHEMA = Schema("BATCH_ETL", parent=ANALYTICS_DB)
t1 = Table("t1", schema=BATCH_ETL_SCHEMA)
t2 = Table("t2", schema=BATCH_ETL_SCHEMA)
o = t1.as_("o")
l = t2.as_("l")
q = (
SnowflakeQuery.from_(o)
.left_join(l)
.on(o.LOCATION_UUID_FIRST == l.UUID)
.select(
o.PERMALINK.as_("permalink"),
ArrayCompact(o.CATEGORIES___PERMALINK).as_("category_permalinks"),
)
)
str(q)
results in alias with quotes
SELECT
o.PERMALINK "permalink",
ARRAY_COMPACT(o.CATEGORIES___PERMALINK) "category_permalinks"
FROM ANALYTICS.BATCH_ETL.t1 "o"
LEFT JOIN ANALYTICS.BATCH_ETL.t2 "l" ON
o.LOCATION_UUID_FIRST = l.UUID
which snowflake errors out with
SQL compilation error: error line 6 at position 4 invalid identifier 'O.LOCATION_UUID_FIRST'
correct with alias not having quotes
SELECT
o.PERMALINK "permalink",
ARRAY_COMPACT(o.CATEGORIES___PERMALINK) "category_permalinks"
FROM ANALYTICS.BATCH_ETL.t1 o
LEFT JOIN ANALYTICS.BATCH_ETL.t2 l ON
o.LOCATION_UUID_FIRST = l.UUID