pypika icon indicating copy to clipboard operation
pypika copied to clipboard

SnowflakeQuery Table alias should not be quoted

Open srausser opened this issue 2 years ago • 0 comments

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

srausser avatar Jun 08 '22 00:06 srausser