CacheConfiguration cfg = new CacheConfiguration<>().setSqlEscapeAll(true) This will make sql case sensitive
发送自 Windows 10 版邮件应用 发件人: [email protected] 发送时间: 2019年12月28日 14:20 收件人: [email protected] 主题: Ignite - SQL Column names Case not preserved. Hi Ignite users I have a simple table created in ignite as below. CREATE TABLE acc ( "accId" VARCHAR PRIMARY KEY, "accCol1" VARCHAR, "accCol2" INT, "accCol3" VARCHAR, "accCol4" BOOLEAN ); On the above table from sqlLine client a simple query like below is also failing without recognising the case of the column. 0: jdbc:ignite:thin://ignite-service-pramesh2> SELECT * FROM acc WHERE accCol1 is null; Error: Failed to parse query. Column "ACCCOL1" not found; SQL statement: SELECT * FROM acc WHERE accCol1 is null; [42122-197] (state=42000,code=0) java.sql.SQLException: Failed to parse query. Column "ACCCOL1" not found; SQL statement: SELECT * FROM acc where accCol1 is null; [42122-197] at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:751) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:210) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:473) at sqlline.Commands.execute(Commands.java:823) at sqlline.Commands.sql(Commands.java:733) at sqlline.SqlLine.dispatch(SqlLine.java:795) at sqlline.SqlLine.begin(SqlLine.java:668) at sqlline.SqlLine.start(SqlLine.java:373) at sqlline.SqlLine.main(SqlLine.java:265) I know that by default h2 has upper case as the column names. Is there a way I could preserve the case of the column names in Ignite? I have this similar problem with Ignite Spark also. I have a spark dataframe with similar cols with similar cols. StructField(accCol1,StringType,true), StructField(accCol2,IntegerType,true), StructField(accCol3,StringType,true), StructField(accCol4,BooleanType,true), StructField(accId,StringType,true) I am trying to find the difference between this dataframe and dataframe read from ignite using spark except operator. val datasetDF = spark.read.parquet("hdfs://***") val igniteDF = spark.read .format(FORMAT_IGNITE) .option(OPTION_TABLE, "acc") .option(IgniteDataFrameSettings.OPTION_CONFIG_FILE, "ignite-client.xml") .load() datasetDF.except(igniteDF) But this operation fails with below exception. Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to parse query. Column "ACCCOL1" not found; SQL statement: SELECT accCol1, CAST(accCol2 AS VARCHAR) AS accCol2, accCol3, accCol4, accId FROM acc [42122-197] at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2653) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.parseAndSplit(IgniteH2Indexing.java:2356) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:2196) at org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2128) at org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2123) at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2693) at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2137) at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:685) ... 21 more Caused by: org.h2.jdbc.JdbcSQLException: Column "ACCOUNTAGEBUCKET" not found; SQL statement: SELECT accountAgeBucket, CAST(accountBirthYear AS VARCHAR) AS accountBirthYear, accountGender, accountIsMaid, accountConsumerId FROM ACCOUNT [42122-197] at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:150) at org.h2.command.dml.Select.prepare(Select.java:858) at org.h2.command.Parser.prepareCommand(Parser.java:283) at org.h2.engine.Session.prepareLocal(Session.java:611) at org.h2.engine.Session.prepareCommand(Session.java:549) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247) at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:694) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepare0(IgniteH2Indexing.java:539) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:509) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:476) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2635) I tried these individually I can read then. Just except operator fails. For some reason the query that is replaced with UPPER case column names while executing DAG. I tried to recreate the table with all Upper Col Names and rename the column names in parquet to upper case and everything works very well. But I cannot have upper case column names on my project. Has anybody faced this issue before? Thanks Praneeth -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
