Possible Issue when Transpiling from Spark to Athena?

[aguynamedryan]

Hello! I'm new to sqlglot and it is amazing.

I'm attempting to transpile a set of SQL queries written in Spark into Athena. I'm running into an issue where the file format isn't getting quoted during transpilation:

import sqlglot
translated = sqlglot.transpile(
    'CREATE TABLE `db`.`tab` USING parquet AS SELECT 1 AS `col`',
    read="spark",
    write="athena",
    pretty=False
)[0]
print(translated)

The current output is: CREATE TABLE "db"."tab" WITH (format=parquet) AS SELECT 1 AS "col"

The desired output is parquet wrapped in single quotes to represent a string literal: CREATE TABLE "db"."tab" WITH (format='parquet') AS SELECT 1 AS "col"

Is this something sqlglot is supposed to handle?

[georgesittas]

Hiya 👋

Is this a syntax error in Athena? What about Presto and Trino that Athena's based on? If it's indeed invalid, it should be a low lift to fix this, by overriding the generation logic for FileFormatProperty and producing the string literal syntax you've shown.

Is this something sqlglot is supposed to handle?

DDL and DML statements are usually not as highly prioritized, but yes, SQLGlot aims to transpile as much as possible.

Since this isn't in scope for the core team, I'm going to mark as resolved, but we're happy to accept contributions if you're down to tackle this yourself!

[aguynamedryan]

Thanks for the quick response!

I only have access to Athena and it is a syntax error for Athena. I can't speak for Presto/Trino. I've submitted a pull request to fix the issue: #5125