search

viz-centric / flair-bi

Flair BI - Open source Business Intelligence (BI) & Analytics platform
https://www.vizcentric.com/product.html
Apache License 2.0
30 stars 22 forks source link

Redshift connection #814

Open fabiofilz opened 4 years ago

fabiofilz commented 4 years ago

Hi,

I cannot create a Redshift connector, because it return the error message "Invalid operation: function concat(information_schema.sql_identifier, "unknown", information_schema.sql_identifier) does not exist;"

Executing the query as the application does in the aws console, it returns the error: ERROR: function concat(information_schema.sql_identifier, "unknown", information_schema.sql_identifier) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.views WHERE table_schema NOT IN ('information_schema', 'pg_catalog') UNION ALL SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'pg_catalog') LIMIT 1;

If I change the function to use 2 parameters, it works.

SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME) FROM information_schema.views WHERE table_schema NOT IN ('information_schema', 'pg_catalog') UNION ALL SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME) FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'pg_catalog') LIMIT 1;

Could you check it, please?

Thank you. Kind regards, Fabio


flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:09.004  INFO 7 --- [t-executor-1984] c.f.e.config.grpc.JwtServerInterceptor   : Server interceptor grpc.health.v1.Health
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:11.762  INFO 7 --- [t-executor-1984] c.f.e.config.grpc.JwtServerInterceptor   : Server interceptor grpc.health.v1.Health
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:12.022  INFO 7 --- [t-executor-1984] c.f.e.config.grpc.JwtServerInterceptor   : Server interceptor messages.ConnectionService
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:12.036  INFO 7 --- [t-executor-1984] c.f.e.service.TestConnectionService      : Testing connection for com.fbi.engine.domain.Connection@3b
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:12.036  INFO 7 --- [t-executor-1984] com.fbi.engine.query.QueryServiceImpl    : Executing flair query SHOW TABLES LIMIT 1 with cache params CacheParams(writeToCache=false, readFromCache=false, cachePurgeAfterMinutes=0, refreshAfterTimesRead=0, refreshAfterMinutes=0) cache enabled true
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:12.036  INFO 7 --- [t-executor-1984] com.fbi.engine.query.QueryServiceImpl    : Interpreted Query: SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.views WHERE table_schema NOT IN ('information_schema', 'pg_catalog') UNION ALL SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'pg_catalog') LIMIT 1
flair-engine-668d5f4c4c-7mrbx flair-engine Hibernate: insert into query_audit_log (actor, connection_link_id, created_date, meta, query) values (?, ?, ?, ?, ?)
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:12.061 ERROR 7 --- [t-executor-1984] c.f.e.query.executor.SqlQueryExecutor    : Connection to database failed, stacktrace: [Amazon](500310) Invalid operation: function concat(information_schema.sql_identifier, "unknown", information_schema.sql_identifier) does not exist;
flair-engine-668d5f4c4c-7mrbx flair-engine 2020-09-21 07:37:12.061 ERROR 7 --- [t-executor-1984] com.fbi.engine.query.QueryServiceImpl    : Error executing statement SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.views WHERE table_schema NOT IN ('information_schema', 'pg_catalog') UNION ALL SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'pg_catalog') LIMIT 1
flair-engine-668d5f4c4c-7mrbx flair-engine
flair-engine-668d5f4c4c-7mrbx flair-engine com.project.bi.exceptions.ExecutionException: Database threw an exception
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.query.executor.SqlQueryExecutor.invokeQuery(SqlQueryExecutor.java:88)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.query.executor.SqlQueryExecutor.execute(SqlQueryExecutor.java:61)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.query.executor.SqlQueryExecutor.execute(SqlQueryExecutor.java:33)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.query.QueryServiceImpl.queryDatasource(QueryServiceImpl.java:105)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.query.QueryServiceImpl.queryAndPutToCache(QueryServiceImpl.java:64)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.query.QueryServiceImpl.executeQuery(QueryServiceImpl.java:44)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.service.TestConnectionService.testConnection(TestConnectionService.java:27)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.fbi.engine.service.AbstractConnectionGrpcService.testConnection(AbstractConnectionGrpcService.java:128)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.flair.bi.messages.ConnectionServiceGrpc$MethodHandlers.invoke(ConnectionServiceGrpc.java:715)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.stub.ServerCalls$UnaryServerCallHandler$UnaryServerCallListener.onHalfClose(ServerCalls.java:172)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.PartialForwardingServerCallListener.onHalfClose(PartialForwardingServerCallListener.java:35)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.ForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:23)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.ForwardingServerCallListener$SimpleForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:40)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.Contexts$ContextualizedServerCallListener.onHalfClose(Contexts.java:86)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.internal.ServerCallImpl$ServerStreamListenerImpl.halfClosed(ServerCallImpl.java:331)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.internal.ServerImpl$JumpToApplicationThreadServerStreamListener$1HalfClosed.runInContext(ServerImpl.java:808)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.internal.ContextRunnable.run(ContextRunnable.java:37)
flair-engine-668d5f4c4c-7mrbx flair-engine  at io.grpc.internal.SerializingExecutor.run(SerializingExecutor.java:123)
flair-engine-668d5f4c4c-7mrbx flair-engine  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
flair-engine-668d5f4c4c-7mrbx flair-engine  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
flair-engine-668d5f4c4c-7mrbx flair-engine  at java.base/java.lang.Thread.run(Thread.java:835)
flair-engine-668d5f4c4c-7mrbx flair-engine Caused by: java.sql.SQLException: [Amazon](500310) Invalid operation: function concat(information_schema.sql_identifier, "unknown", information_schema.sql_identifier) does not exist;
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.PGMessagingContext.moveThroughMetadata(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.PGMessagingContext.getNoData(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.client.PGClient.directExecuteExtraMetadataWithMessage(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at com.amazon.redshift.dataengine.PGQueryExecutor$CallableExecuteTask.call(Unknown Source)
flair-engine-668d5f4c4c-7mrbx flair-engine  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
flair-engine-668d5f4c4c-7mrbx flair-engine  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
flair-engine-668d5f4c4c-7mrbx flair-engine  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
flair-engine-668d5f4c4c-7mrbx flair-engine Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: function concat(information_schema.sql_identifier, "unknown", information_schema.sql_identifier) does not exist;
flair-engine-668d5f4c4c-7mrbx flair-engine  ... 13 common frames omitted