are there an option in SQL to not use double quotation marks in a SQL

[l1t1]

When I run SQL on a parquet file's column name has both Upper and Lower letter, I had to use double quotation marks for them.

from datafusion import SessionContext
ctx = SessionContext()
ctx.register_parquet('a', 'a.parquet')
df = ctx.sql('select * from a')
df
DataFrame()
+------+-----+
| User | Age |
+------+-----+
| Tom  | 12  |
| Jack | 30  |
+------+-----+
df = ctx.sql('select "Age" from a')
df
DataFrame()
+-----+
| Age |
+-----+
| 12  |
| 30  |
+-----+

if I don't use double quotation marks, it reports following error.

>>> df = ctx.sql('select Age from a')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
Exception: DataFusion error: SchemaError(FieldNotFound { field: Column { relation: None, name: "age" }, valid_fields: [Column { relation: Some(Bare { table: "a" }), name: "User" }, Column { relation: Some(Bare { table: "a" }), name: "Age" }
] }, Some(""))

sometimes, it's diffcult to write a sql when there are many those fieldnames.

[alamb]

I think you can set datafusion.sql_parser.enable_ident_normalization to false (docs here https://arrow.apache.org/datafusion/user-guide/configs.html)

set datafusion.sql_parser.enable_ident_normalization = false;

[l1t1]

thanks, could you tell me how to set it in python CLI?

[alamb]

Perhaps you could use https://arrow.apache.org/datafusion-python/generated/datafusion.SessionConfig.html#datafusion.SessionConfig.set

[l1t1]

seems no effect after setting

>>> import datafusion
>>> sc=datafusion.SessionConfig(config_options=None)
>>> sc.set("datafusion.sql_parser.enable_ident_normalization" , "false")
<datafusion.SessionConfig object at 0x3000c15ec0>
>>> from datafusion import SessionContext
>>> ctx = SessionContext()

>>> df = ctx.sql('create table x as select 1 "A"')
>>> df2 = ctx.sql('select * from x')
>>> df2
DataFrame()
+---+
| A |
+---+
| 1 |
+---+
>>> df2 = ctx.sql('select a from x')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
Exception: DataFusion error: SchemaError(FieldNotFound { field: Column { relation: None, name: "a" }, valid_fields: [Column { relation: Some(Bare { table: "x" }), name: "A" }] }, Some(""))

[alamb]

I think you may have to set the configuration option on the SessionContext (I don't see sc used anywhere -- can you pass it to the constructor of SessionContext ?)

[l1t1]

passed sc, still not work

>>> sc.set("datafusion.sql_parser.enable_ident_normalization" , "false")
<datafusion.SessionConfig object at 0x300078bf10>
>>> ctx = SessionContext(sc)
>>> df = ctx.sql('create table x as select 1 "A"')
>>> df2 = ctx.sql('select a from x')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
Exception: DataFusion error: SchemaError(FieldNotFound { field: Column { relation: None, name: "a" }, valid_fields: [Column { relation: Some(Bare { table: "x" }), name: "A" }] }, Some(""))
>>>

[alamb]

So in your example above, when you set datafusion.sql_parser.enable_ident_normalization to false, the query

select a from x

Means "select the column named (lowercase) a from x"

However, the previous statement create table x as select 1 "A" creates a column named (uppercase) A

So if you run a query like select A from x (use the non normalized column name A) it works fine

Python 3.11.11 (main, Dec  3 2024, 17:20:40) [Clang 16.0.0 (clang-1600.0.26.4)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import datafusion
>>> from datafusion import SessionContext
>>> sc=datafusion.SessionConfig(config_options=None)
>>> sc.set("datafusion.sql_parser.enable_ident_normalization" , "false")
<datafusion.context.SessionConfig object at 0x102664e90>
>>> ctx = SessionContext(sc)
>>> df = ctx.sql('create table x as select 1 "A"')
>>> df2 = ctx.sql('select A from x')
>>> df2
DataFrame()
+---+
| A |
+---+
| 1 |
+---+

This is with this datafusion version:

>>> datafusion.__version__
'43.1.0'

Thank you to @lmwnshn for pointing this out.

[alamb]

BTW here is a less verbose way of doing the same thing (directly execute set datafusion.sql_parser.enable_ident_normalization='false'):

Python 3.11.11 (main, Dec  3 2024, 17:20:40) [Clang 16.0.0 (clang-1600.0.26.4)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from datafusion import SessionContext
>>> ctx = SessionContext()
>>> df = ctx.sql("set datafusion.sql_parser.enable_ident_normalization='false'")
>>> df = ctx.sql('create table x as select 1 "A"')
>>> df2 = ctx.sql('select A from x')
>>> df2
DataFrame()
+---+
| A |
+---+
| 1 |
+---+