Need help transpiling teradata query to snowflake

[Ashay4u]

Hi

I have a teradata query similar to the one shown below, but sqlglot is unable to parse this. it shows error like missing ")" when in actuality its not required.

select strt_dt (format "YYYY/MM") (varchar(20)) as yrmnth from ABC

The code looks like below

import sqlglot

sql = """select strt_dt (format "YYYY/MM") (varchar(20)) as yrmnth from ABC;"""
new_query = sqlglot.transpile(sql, read='teradata', write='snowflake', pretty=True)[0]
print(new_query)

Is there anything i am doing wrong? Is there a way to fix/work-around this ?

[tobymao]

i'm unfamiliar with this syntax and have never seen it before. cc @treysp

sqlglot has not implemented this syntax from teradata is my guess. so the workaround would be to rewrite your query into a form that is more standard, perhaps with casts

[Ashay4u]

This too results in a parsing error.

import sqlglot

sql = """create volatile table temp
as(
    select
current_date as curr_cal_dt
FROM ABC
)with data primary index (curr_cal_dt) on commit preserve rows;
"""
# sqlglot.transpile(sql, read="teradata", write="sql")[0]
try:
    new_query = sqlglot.transpile(sql, read='teradata', write='snowflake', pretty=True)[0]
    print(new_query)
except sqlglot.errors.ParseError as error:
    print(error.errors)

[georgesittas]

The problem is probably related to the on commit preserve rows property, which can be handled like with no data (look at parser.py:1071). Feel free to make a PR for this, otherwise it'd be better to file an issue.

[treysp]

I'm working on this in #1219

[Ashay4u]

When i try to transpile a Teradata query to snowflake as follows, it maintains the MULTISET VOLATILE Keywords which is clearly not snowflake compatible. Can someone please help with me these kinds of scenarios?

import sqlglot

sql = """create multiset volatile table cts as 
SELECT XYZ FROM ABC;
"""
try:
    new_query = sqlglot.transpile(sql, read='teradata', write='snowflake', pretty=True)[0]
    print(new_query)
except sqlglot.errors.ParseError as error:
    print(error.errors)

output

CREATE MULTISET VOLATILE TABLE cts AS
SELECT
  XYZ
FROM ABC

[georgesittas]

You'd need to override the create_sql method in Snowflake to filter out args that shouldn't be printed -- or transpile it to something that's equivalent in Snowflake.

The Create expression in particular is a bit annoying to deal with, because most of its syntax is common across dialects and so we've decided to bundle various dialect-specific features in the base method, instead of overriding it..

[Ashay4u]

@georgesittas Thanks for you suggestion. I plan to override the create_sql method from generator.py: 494 in snowflake.py. Can you help me understand the steps to achieve that?
Also if you can help me understand how the TRANSFORMS dict from snowflake.py:221 is used in the code, it would be helpful. I am relatively new to this type of implementation method, hence the request.

[georgesittas]

Hey 👋

Trey made a related PR yesterday that you can look at (see the discussion too): #1211

Also if you can help me understand how the TRANSFORMS dict from snowflake.py:221 is used in the code, it would be helpful. I am relatively new to this type of implementation method, hence the request.

The TRANSFORMS dictionary provides a way of overriding the SQL generation logic for a specific expression type. For example, you can see the following entry in MySQL's TRANSFORMS:

        ...
        exp.DayOfWeek: rename_func("DAYOFWEEK"),
        ...

This says that the DayOfWeek expression (which corresponds to a SQL function) will be renamed to DAYOFWEEK(....), instead of the default DAY_OF_WEEK that SQLGlot uses.

Another way to do this could be to explicitly override the dayofweek_sql method inside the Generator class of a dialect. There are several examples you can look at if you navigate the dialects.

[treysp]

I'm working on this in #1219. It handles MULTISET, but VOLATILE will take more discussion.