tech jogging Like other installations and setups, Trino formerly PrestoSQL can contain steps which cause difficulties. How many times you were stuck with something? In mostly cases, it was a trivial issue but you spent countless time to solve it. It's better to have a cheat sheet for discovering those issues before encountering them. The list of pitfalls is based on Starburst open-source distribution.
History
Trino formerly PrestoSQL was originated in 2012 year as PrestoDB open-source project in Facebook. PrestoSQL was started in 2019 by the PrestoDB founders. Facebook forced to rebrand PrestoSQL into Trino in 2020. One of the successful commercial distribution based on Trino is Starburst. Starburst includes both open-source and commercial products.
Disable swap on each node
Trino assumes that swap is not used. Swap can dramatically impact on performance and stability of a Trino cluster. If swap is on, memory consumption will be close to 100% and, as a result, Trino cluster will be slow and many queries will fail.
The typical error messages are.
Error type 1.
io.prestosql.spi.PrestoException: Query 20200720_132906_00038_4smph has not been accessed since 2020-07-20T09:42:25.080-04:00: currentTime 2020-07-20T09:52:25.447-04:00
Error type 2.
io.prestosql.spi.PrestoTransportException: Encountered too many errors talking to a worker node. The node may have crashed or be under too much load. This is probably a transient issue, so please retry your query in a few minutes.
The current swappiness setting can be received.
cat /proc/sys/vm/swappiness
Turn off swappiness temporary.
sudo sysctl vm.swappiness=0
Turn off swappiness permanently changing vm.swappiness=0 setting in the file below.
sudo nano /etc/sysctl.conf
Swap memory information.
free -m
Java 11 installation
OpenJDK 11 can be used. Java 11 does not have JRE dedicated folder.
sudo yum install java-11-openjdk-devel
OpenJDK JRE folder is /usr/lib/jvm/jre-11. It points to the same location as JDK one.
SQL Server connector overwhelms SQL Server
When data is written to SQL Server, Trino tries to do it as fast as possible. It will utilize all workers to push data to SQL Server. As a result, it opens a lot of connections at least one per worker and SQL Server can crash. Wideness of an exported table impacts on it as well. The more columns is in your table, the more chances to encounter the issue can be. Also, the number of records in a destination table contributes to the issue.
The error message is
io.prestosql.spi.PrestoException: There is insufficient system memory in resource pool 'default' to run this query.
at io.prestosql.plugin.jdbc.JdbcPageSink.appendPage(JdbcPageSink.java:117)
at io.prestosql.operator.TableWriterOperator.addInput(TableWriterOperator.java:257)
at io.prestosql.operator.Driver.processInternal(Driver.java:384)
at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
at io.prestosql.operator.Driver.processFor(Driver.java:276)
at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1076)
at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
at io.prestosql.$gen.Presto_348_e____20210219_123137_2.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: There is insufficient system memory in resource pool 'default' to run this query.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2766)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2641)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2056)
at io.prestosql.plugin.jdbc.JdbcPageSink.appendPage(JdbcPageSink.java:109)
... 12 more
To solve the issue, RAM of SQL Server should be pumped up. You can try to increase SQL Server memory until the issue is gone. For example, if you export a table with 100 columns and your record count is some hundred million records, RAM can be set up to 96GB with 90GB dedicated to SQL Server.
Permissions for /tmp folder if Hive connector used
/tmp folder has to have the permissions in case of using Hive connector.
ls -ld /tmp
drwxrwxrwx. 13 root root 4096 Jul 30 15:08 /tmp
Trino copies Hive connector files in /tmp folder during Trino server starup.
The location of the temporary folder can be changes with -Djava.io.tmpdir property in jvm.config file.
If /tmp folder is not granted emough permissions, Trino server will not start.
server.log error message when Hive connector is being loaded.
2021-07-30T14:09:22.395-0400 INFO main io.trino.metadata.StaticCatalogStore -- Loading catalog /etc/starburst/catalog/hive_connector.properties --
...
2021-07-30T14:09:23.815-0400 ERROR main io.trino.server.Server null
java.lang.ExceptionInInitializerError
at io.trino.plugin.hive.HdfsEnvironment$$FastClassByGuice$$e99ee3bd.newInstance(<generated>)
at com.google.inject.internal.DefaultConstructionProxyFactory$FastClassProxy.newInstance(DefaultConstructionProxyFactory.java:89)
at com.google.inject.internal.ConstructorInjector.provision(ConstructorInjector.java:114)
at com.google.inject.internal.ConstructorInjector.access$000(ConstructorInjector.java:32)
at com.google.inject.internal.ConstructorInjector$1.call(ConstructorInjector.java:98)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:112)
at io.airlift.bootstrap.LifeCycleModule.provision(LifeCycleModule.java:54)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:120)
at com.google.inject.internal.ProvisionListenerStackCallback.provision(ProvisionListenerStackCallback.java:66)
at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:93)
at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:306)
at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
at com.google.inject.internal.SingletonScope$1.get(SingletonScope.java:168)
at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:39)
at com.google.inject.internal.SingleParameterInjector.inject(SingleParameterInjector.java:42)
at com.google.inject.internal.SingleParameterInjector.getAll(SingleParameterInjector.java:65)
at com.google.inject.internal.ConstructorInjector.provision(ConstructorInjector.java:113)
at com.google.inject.internal.ConstructorInjector.access$000(ConstructorInjector.java:32)
at com.google.inject.internal.ConstructorInjector$1.call(ConstructorInjector.java:98)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:112)
at io.airlift.bootstrap.LifeCycleModule.provision(LifeCycleModule.java:54)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:120)
at com.google.inject.internal.ProvisionListenerStackCallback.provision(ProvisionListenerStackCallback.java:66)
at com.google.inject.internal.ConstructorInjector.construct(ConstructorInjector.java:93)
at com.google.inject.internal.ConstructorBindingImpl$Factory.get(ConstructorBindingImpl.java:306)
at com.google.inject.internal.FactoryProxy.get(FactoryProxy.java:62)
at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
at com.google.inject.internal.SingletonScope$1.get(SingletonScope.java:168)
at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:39)
at com.google.inject.internal.InternalInjectorCreator.loadEagerSingletons(InternalInjectorCreator.java:213)
at com.google.inject.internal.InternalInjectorCreator.injectDynamically(InternalInjectorCreator.java:184)
at com.google.inject.internal.InternalInjectorCreator.build(InternalInjectorCreator.java:111)
at com.google.inject.Guice.createInjector(Guice.java:87)
at io.airlift.bootstrap.Bootstrap.initialize(Bootstrap.java:276)
at io.trino.plugin.hive.InternalHiveConnectorFactory.createConnector(InternalHiveConnectorFactory.java:117)
at io.trino.plugin.hive.InternalHiveConnectorFactory.createConnector(InternalHiveConnectorFactory.java:77)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at io.trino.plugin.hive.HiveConnectorFactory.create(HiveConnectorFactory.java:63)
at io.trino.connector.ConnectorManager.createConnector(ConnectorManager.java:359)
at io.trino.connector.ConnectorManager.createCatalog(ConnectorManager.java:216)
at io.trino.connector.ConnectorManager.createCatalog(ConnectorManager.java:208)
at io.trino.connector.ConnectorManager.createCatalog(ConnectorManager.java:194)
at io.trino.metadata.StaticCatalogStore.loadCatalog(StaticCatalogStore.java:88)
at io.trino.metadata.StaticCatalogStore.loadCatalogs(StaticCatalogStore.java:68)
at io.trino.server.Server.doStart(Server.java:119)
at io.trino.server.Server.lambda$start$0(Server.java:73)
at io.trino.$gen.Trino_354_e____20210730_180904_1.run(Unknown Source)
at io.trino.server.Server.start(Server.java:73)
at com.starburstdata.presto.StarburstTrinoServer.main(StarburstTrinoServer.java:50)
Caused by: java.lang.RuntimeException: failed to load Hadoop native library
at io.trino.hadoop.HadoopNative.requireHadoopNative(HadoopNative.java:59)
at io.trino.plugin.hive.HdfsEnvironment.<clinit>(HdfsEnvironment.java:39)
... 52 more
Caused by: java.io.IOException: Permission denied
at java.base/java.io.UnixFileSystem.createFileExclusively(Native Method)
at java.base/java.io.File.createTempFile(File.java:2129)
at java.base/java.io.File.createTempFile(File.java:2175)
at io.trino.hadoop.HadoopNative.loadLibrary(HadoopNative.java:92)
at io.trino.hadoop.HadoopNative.requireHadoopNative(HadoopNative.java:47)
... 53 more
Error "FATAL: remaining connection slots are reserved for non-replication superuser connections"
This issue might be caused by Event Logger with Postgres database as a backend when a Trino cluster is not stopped during shutting down Linux computers. In that case, Postgres connections are not closed and after Trino start, enough connections can't be allocated.
To solve it, restart your Trino cluster. It will release Postgres connections after stopping the cluster.
The issue was encountered in Starburst Enterprise edition 360-e.2.
The error message found in the server log.
2021-10-02T10:47:21.873-0400 INFO main io.trino.eventlistener.EventListenerManager -- Loading event listener etc/event-listener.properties --
2021-10-02T10:47:22.424-0400 INFO main Bootstrap PROPERTY DEFAULT RUNTIME DESCRIPTION
2021-10-02T10:47:22.424-0400 INFO main Bootstrap jdbc.connection-pool.idle-timeout 10.00m 10.00m Maximum amount of time a connection can sit idle in the pool
2021-10-02T10:47:22.424-0400 INFO main Bootstrap jdbc.password [REDACTED] [REDACTED] Password of the user connecting to the database
2021-10-02T10:47:22.424-0400 INFO main Bootstrap jdbc.url ---- jdbc:postgresql://sample.com:5432/event_logger URL of the database; for MySQL, include sessionVariables=sql_mode=ANSI
2021-10-02T10:47:22.424-0400 INFO main Bootstrap jdbc.user ---- sample_user User connecting to the database
2021-10-02T10:47:22.424-0400 INFO main Bootstrap jdbc.connection-pool.max-size 10 10 Maximum number of connections in the pool
2021-10-02T10:47:22.424-0400 INFO main Bootstrap jdbc.connection-pool.min-size 1 1 Minimum number of connections in the pool
2021-10-02T10:47:22.500-0400 INFO main com.zaxxer.hikari.HikariDataSource HikariPool-1 - Starting...
2021-10-02T10:47:22.583-0400 ERROR main com.zaxxer.hikari.pool.HikariPool HikariPool-1 - Exception during pool initialization.
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.Driver$ConnectThread.getResult(Driver.java:410)
at org.postgresql.Driver.connect(Driver.java:268)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:98)
at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:83)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
at com.starburstdata.presto.eventlogger.api.db.PersistenceModule.createDatasource(PersistenceModule.java:77)
at com.starburstdata.presto.eventlogger.api.db.PersistenceModule$$FastClassByGuice$$670968.GUICE$TRAMPOLINE(<generated>)
at com.starburstdata.presto.eventlogger.api.db.PersistenceModule$$FastClassByGuice$$670968.apply(<generated>)
at com.google.inject.internal.ProviderMethod$FastClassProviderMethod.doProvision(ProviderMethod.java:260)
at com.google.inject.internal.ProviderMethod.doProvision(ProviderMethod.java:171)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory.provision(InternalProviderInstanceBindingImpl.java:185)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory.access$300(InternalProviderInstanceBindingImpl.java:139)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory$1.call(InternalProviderInstanceBindingImpl.java:169)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:109)
at io.airlift.bootstrap.LifeCycleModule.provision(LifeCycleModule.java:54)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:117)
at com.google.inject.internal.ProvisionListenerStackCallback.provision(ProvisionListenerStackCallback.java:66)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory.get(InternalProviderInstanceBindingImpl.java:164)
at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
at com.google.inject.internal.SingletonScope$1.get(SingletonScope.java:169)
at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:45)
at com.google.inject.internal.InternalInjectorCreator.loadEagerSingletons(InternalInjectorCreator.java:213)
at com.google.inject.internal.InternalInjectorCreator.injectDynamically(InternalInjectorCreator.java:186)
at com.google.inject.internal.InternalInjectorCreator.build(InternalInjectorCreator.java:113)
at com.google.inject.Guice.createInjector(Guice.java:87)
at io.airlift.bootstrap.Bootstrap.initialize(Bootstrap.java:275)
at com.starburstdata.presto.eventlogger.QueryLoggerEventListenerFactory.create(QueryLoggerEventListenerFactory.java:40)
at com.starburstdata.presto.license.LicenceCheckingEventListenerFactory.create(LicenceCheckingEventListenerFactory.java:50)
at io.trino.eventlistener.EventListenerManager.createEventListener(EventListenerManager.java:117)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
at java.base/java.util.Collections$2.tryAdvance(Collections.java:4747)
at java.base/java.util.Collections$2.forEachRemaining(Collections.java:4755)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
at io.trino.eventlistener.EventListenerManager.configuredEventListeners(EventListenerManager.java:100)
at io.trino.eventlistener.EventListenerManager.loadEventListeners(EventListenerManager.java:85)
at io.trino.server.Server.doStart(Server.java:134)
at io.trino.server.Server.lambda$start$0(Server.java:77)
at io.trino.$gen.Trino_360_e_2____20211002_144649_1.run(Unknown Source)
at io.trino.server.Server.start(Server.java:77)
at com.starburstdata.presto.StarburstTrinoServer.main(StarburstTrinoServer.java:40)
2021-10-02T10:47:22.584-0400 ERROR main com.starburstdata.presto.eventlogger.api.db.PersistenceModule Failed to create connection pool; query event logger will be disabled
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: remaining connection slots are reserved for non-replication superuser connections
at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:597)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:576)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
at com.starburstdata.presto.eventlogger.api.db.PersistenceModule.createDatasource(PersistenceModule.java:77)
at com.starburstdata.presto.eventlogger.api.db.PersistenceModule$$FastClassByGuice$$670968.GUICE$TRAMPOLINE(<generated>)
at com.starburstdata.presto.eventlogger.api.db.PersistenceModule$$FastClassByGuice$$670968.apply(<generated>)
at com.google.inject.internal.ProviderMethod$FastClassProviderMethod.doProvision(ProviderMethod.java:260)
at com.google.inject.internal.ProviderMethod.doProvision(ProviderMethod.java:171)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory.provision(InternalProviderInstanceBindingImpl.java:185)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory.access$300(InternalProviderInstanceBindingImpl.java:139)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory$1.call(InternalProviderInstanceBindingImpl.java:169)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:109)
at io.airlift.bootstrap.LifeCycleModule.provision(LifeCycleModule.java:54)
at com.google.inject.internal.ProvisionListenerStackCallback$Provision.provision(ProvisionListenerStackCallback.java:117)
at com.google.inject.internal.ProvisionListenerStackCallback.provision(ProvisionListenerStackCallback.java:66)
at com.google.inject.internal.InternalProviderInstanceBindingImpl$CyclicFactory.get(InternalProviderInstanceBindingImpl.java:164)
at com.google.inject.internal.ProviderToInternalFactoryAdapter.get(ProviderToInternalFactoryAdapter.java:40)
at com.google.inject.internal.SingletonScope$1.get(SingletonScope.java:169)
at com.google.inject.internal.InternalFactoryToProviderAdapter.get(InternalFactoryToProviderAdapter.java:45)
at com.google.inject.internal.InternalInjectorCreator.loadEagerSingletons(InternalInjectorCreator.java:213)
at com.google.inject.internal.InternalInjectorCreator.injectDynamically(InternalInjectorCreator.java:186)
at com.google.inject.internal.InternalInjectorCreator.build(InternalInjectorCreator.java:113)
at com.google.inject.Guice.createInjector(Guice.java:87)
at io.airlift.bootstrap.Bootstrap.initialize(Bootstrap.java:275)
at com.starburstdata.presto.eventlogger.QueryLoggerEventListenerFactory.create(QueryLoggerEventListenerFactory.java:40)
at com.starburstdata.presto.license.LicenceCheckingEventListenerFactory.create(LicenceCheckingEventListenerFactory.java:50)
at io.trino.eventlistener.EventListenerManager.createEventListener(EventListenerManager.java:117)
at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
at java.base/java.util.Collections$2.tryAdvance(Collections.java:4747)
at java.base/java.util.Collections$2.forEachRemaining(Collections.java:4755)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
at io.trino.eventlistener.EventListenerManager.configuredEventListeners(EventListenerManager.java:100)
at io.trino.eventlistener.EventListenerManager.loadEventListeners(EventListenerManager.java:85)
at io.trino.server.Server.doStart(Server.java:134)
at io.trino.server.Server.lambda$start$0(Server.java:77)
at io.trino.$gen.Trino_360_e_2____20211002_144649_1.run(Unknown Source)
at io.trino.server.Server.start(Server.java:77)
at com.starburstdata.presto.StarburstTrinoServer.main(StarburstTrinoServer.java:40)
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
at org.postgresql.Driver$ConnectThread.getResult(Driver.java:410)
at org.postgresql.Driver.connect(Driver.java:268)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:98)
at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:83)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
... 41 more
Expected a string or numeric value for field 'field_name' of type VARCHAR: [value1, value2] [ArrayList]
The error is encountered when Elasticsearch connector is used. The connector can handle only simple data types but Elasticsearch includes list of values in a field.
The message is.
SQL Error [58]: Query failed (#20211105_183725_88248_kz74f): Expected a string or numeric value for field 'field_name' of type VARCHAR: [value1, value2, value3] [ArrayList]
To fix the issue, a command should be run to notify Trino about those fields in the _meta section of the index mapping. Replace those place holders with yours values: elastic.sample.com:9200, index_name, and field_name.
curl --request PUT \
--url elastic.sample.com:9200/index_name/doc/_mapping \
--header 'content-type: application/json' \
--data '
{
"_meta": {
"presto":{
"field_name":{
"isArray":true
}
}
}
}'
The testing can be done running any of those SQL statements.
SELECT field_name FROM elastic_catalog."default".index_name WHERE field_name IS NOT NULL;
SELECT DISTINCT field_name FROM elastic_catalog."default".index_name;
Trino documentation reference is Array types.
Comments
comments powered by Disqus