While running this simple query in Dremio:
SELECT 'NBC Universal – NBC News' Data
We got the following error message
Failed to encode 'NBC Universal – NBC News' in character set 'ISO-8859-1'
We tried setting saffron settings for character set with the following docker command:
docker run -p 9047:9047 -p 31010:31010 -p 45678:45678 -e DREMIO_JAVA_SERVER_EXTRA_OPTS="-Dcalcite.default.charset=UTF-16LE -Dcalcite.default.nationalcharset=UTF-16LE -Dcalcite.default.collation.name=UTF-16LE$en_US" dremio/dremio-oss
Dremio version is 4.1
Is there any config to make Dremio default charset to be UTF-8 or UTF16 ?
We were looking at this thread for how to configure character set in Dremio.
Exception details from docker logs :
com.dremio.common.exceptions.UserException: Failed to encode 'NBC Universal – NBC News' in character set 'ISO-8859-1' at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:776) ~[dremio-common-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.planner.sql.SqlExceptionHelper.coerceException(SqlExceptionHelper.java:126) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan(NormalHandler.java:60) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan(HandlerToExec.java:70) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.work.foreman.AttemptManager.plan(AttemptManager.java:415) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.work.foreman.AttemptManager.lambda$run$0(AttemptManager.java:324) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.service.commandpool.CommandWrapper.run(CommandWrapper.java:62) ~[dremio-services-commandpool-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_232] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_232] at java.lang.Thread.run(Thread.java:748) [na:1.8.0_232] Caused by: org.apache.calcite.runtime.CalciteException: Failed to encode 'NBC Universal – NBC News' in character set 'ISO-8859-1' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_232] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_232] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_232] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_232] at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.util.NlsString.(NlsString.java:81) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.rex.RexBuilder.makeLiteral(RexBuilder.java:887) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.rex.RexBuilder.makeVarCharLiteral(RexBuilder.java:1108) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertLiteral(SqlNodeToRexConverterImpl.java:120) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4639) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:3988) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql.SqlLiteral.accept(SqlLiteral.java:533) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4552) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.StandardConvertletTable$14.convertCall(StandardConvertletTable.java:292) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4650) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:3988) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4552) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectList(SqlToRelConverter.java:3831) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:619) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3056) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:555) ~[calcite-core-1.16.0-201909031936280585-3f76472.jar:1.16.0-201909031936280585-3f76472] at com.dremio.exec.planner.sql.SqlConverter.toConvertibleRelRoot(SqlConverter.java:320) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.planner.sql.handlers.PrelTransformer.toConvertibleRelRoot(PrelTransformer.java:777) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377] at com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToRelRoot(PrelTransformer.java:862) ~[dremio-sabot-kernel-4.1.3-202001022113020736-53142377.jar:4.1.3-202001022113020736-53142377]
The issue was that $ in the calcite config has to be escaped, and also since Dremio (4.1) is still using calcite 1.16 , saffron config needs to be saffron.default instead of calcite.default.
docker run -p 9047:9047 -e DREMIO_JAVA_SERVER_EXTRA_OPTS="-Dsaffron.default.charset=UTF-16LE -Dsaffron.default.nationalcharset=UTF-16LE -Dsaffron.default.collation.name=UTF-16LE\$en_US" dremio/dremio-oss:4.1.7
For direct fix in Dremio config files, dremio-env file needs to updated with this:
# Extra Java options - server only (dremio command)
#
DREMIO_JAVA_SERVER_EXTRA_OPTS="-Dsaffron.default.charset=UTF-16LE -Dsaffron.default.nationalcharset=UTF-16LE -Dsaffron.default.collation.name=UTF-16LE\$en_US"