search

shzlw / poli

An easy-to-use BI server built for SQL lovers. Power data analysis in SQL and gain faster business insights.
https://shzlw.github.io/poli
MIT License
1.97k stars 331 forks source link

Ability to add search_path for Redshift queries #20

Closed fosstrack closed 5 years ago

fosstrack commented 5 years ago

Was able to connect to AWS Redshift using a Postgres compatible JDBC driver (https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html)

However, my data is partitioned into multiple schemas. Need to select the schema before running the query. e.g.

set search_path to mySchema;
select count(*) from myTable;

The error I get is: Internal Server Error: PreparedStatementCallback; bad SQL grammar [set search_path to xyzz; select * from....

Please suggest a way to do this either while creating the query or when setting up the data source (I can then create multiple datasources - one database per schema.)

shzlw commented 5 years ago

I am not familiar with Redshift and I need to take a look. Since your DB is PostgreSQL, have you tried?

select count(*) from mySchema.myTable;
fosstrack commented 5 years ago

Thanks. Yes. That will work. I was wondering if there is any way to SET variables applicable to the query or to the session before running it to avoid inserting schema.myTable.

shzlw commented 5 years ago

I will try handle your use case in a more generic way: if there are multiple statements, each of them will be executed in order in the same session and the last statement will return query result.

shzlw commented 5 years ago

v0.9.1 is now released which now supports multiple SQL statements in the query editor. Make sure you set poli.allow-multiple-query-statements=true to enable this feature.