Athena Iceberg Tables parsing issue

[matthias-Q]

Hi,
I want to parse a SQL Statement that creates an Iceberg table on Athena:

create table if not exists tmp.mytable (
    name string
)
location 's3://bucket/tmp/mytable/'
tblproperties (
  'table_type'='iceberg',
  'format'='parquet'
);

running

stmts = sqlglot.parse(sql, read=sqlglot.Dialects.ATHENA)
stmts[0].sql()

returns:

CREATE TABLE IF NOT EXISTS tmp.mytable 
    (name TEXT) 
LOCATION 's3://bucket/tmp/mytable/' 
WITH (
    table_type='iceberg', 
    FORMAT='parquet'
)

Unfortunately, the syntax in Athena is different for Iceberg Tables and Hive-style tables.

The parsed statement should look like this:

CREATE TABLE IF NOT EXISTS tmp.mytable 
    (name STRING) 
LOCATION 's3://bucket/tmp/mytable/' 
TBLPROPERTIES (
    'table_type'='iceberg', 
    'FORMAT'='parquet'
)

Instead of WITH -> TBLPROPERTIES
The keys in the this block are wrapped in upper quotes and iceberg has slightly different data types. In this case STRING instead of TEXT

https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-supported-data-types.html
https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.html

[matthias-Q]

I have tested this and is does not seem to have made a difference.

In [14]: sqlglot.__version__
Out[14]: '22.4.0'

Input SQL:

create table if not exists tmp.mytable (
    name string
)
location 's3://bucket/tmp/mytable/'
tblproperties (
  'table_type'='iceberg',
  'format'='parquet'
);

Output:

In [13]: sqlglot.parse(sql, read=sqlglot.Dialects.ATHENA)[0].sql()
Out[13]: "CREATE TABLE IF NOT EXISTS tmp.mytable (name TEXT) LOCATION 's3://bucket/tmp/mytable/' WITH (table_type='iceberg', FORMAT='parquet')"

[georgesittas]

You need to specify the output dialect:

>>> sql = """
... create table if not exists tmp.mytable (
...     name string
... )
... location 's3://bucket/tmp/mytable/'
... tblproperties (
...   'table_type'='iceberg',
...   'format'='parquet'
... );
... """
>>>
>>> import sqlglot
>>>
>>> sqlglot.parse_one(sql, dialect="athena").sql(dialect="athena")  # or just sqlglot.transpile(sql, read="athena")[0]
"CREATE TABLE IF NOT EXISTS tmp.mytable (name STRING) LOCATION 's3://bucket/tmp/mytable/' TBLPROPERTIES ('table_type'='iceberg', 'FORMAT'='parquet')"

[matthias-Q]

Ah, thanks. I missed that!