Trino Queries not considering UI selected schema in query and failing with syntax error

Is the issue already present in https://github.com/cloudera/hue/issues or discussed in the forum https://discourse.gethue.com? Nope Describe the bug: Below error when not specifying schema name even that is selected from UI TrinoUserError(type=USER_ERROR, name=MISSING_SCHEMA_NAME, message="line 1:15: Schema must be specified when session schema is not set", query_id=20221215_173151_00229_mmkqb) Steps to reproduce it? select the schema from UI and then run any query on any table without specifying it in table. It should take default as UI selected but not working as expected Select * from

Hue version or source? (e.g. open source 4.5, CDH 5.16, CDP 1.0...). System info (e.g. OS, Browser...). 4.10

sbbagal13 commented 1 year ago

Anyone facing similar issue ? do we have any workaround for this ?

sbbagal13 commented 1 year ago

@Harshg999 any suggestion this issue?

Harshg999 commented 1 year ago

Hi @sbbagal13, thanks for reporting this. Will take a look on this issue. For the time being, adding this issue in the roadmap pipeline.

github-actions[bot] commented 1 year ago

This issue is stale because it has been open 30 days with no activity and is not labeled "Prevent stale". Remove "stale" label or comment or this will be closed in 10 days.

github-actions[bot] commented 1 year ago

This issue is stale because it has been open 30 days with no activity and is not labeled "Prevent stale". Remove "stale" label or comment or this will be closed in 10 days.

sbbagal13 commented 1 year ago

@bjornalm could you keep this issue open

Harshg999 commented 1 year ago

Hey @sbbagal13, thanks for reporting this issue. We have created an internal roadmap item to improve Trino with SQLAlchemy as a whole and this will be scoped and pulled in subsequent releases. Stay tuned!

agl29 commented 9 months ago

Hello @sbbagal13, We've made some change to the Hue code to better support Trino. Could you please try the latest version of Hue and let us know if you encounter the same issue? Your feedback is valuable. Thank you for reporting the issue.

2416210017 commented 8 months ago

Hello @sbbagal13, We've made some change to the Hue code to better support Trino. Could you please try the latest version of Hue and let us know if you encounter the same issue? Your feedback is valuable. Thank you for reporting the issue.

I found that there are several issues with Hue's support for trino connectors:

Users in trino must authorize all catalogs, otherwise an error message will appear: Query 20240319:031005_22463_1sm8u failed: Access Denied: Cannot select from columns [schemaName] in table or view schema (I have observed that in trino, execute: SHOW SCHEMAS From catalog; list all databases.)
Configure account passwords and LDAP trino. Only one user can be specified in the hue configuration file, and cannot impersonate a user or configure "auth_username": "${User}", "auth_password": "${PASSWORD}" to fill in the username and password. This will result in an error message when logging in with different hue users: io. trino. spi. security AccessiDeniedException: Access Denied: User usr_data_collect cannot impersonate user hive

Harshg999 commented 8 months ago

@2416210017 Thanks for pointing this out. Can you share what Hue config are you setting into Hue for enabling Trino editor? Also share the config the default_user config value under [desktop].

Also any specific Trino side configurations you've added or is it a vanilla setup?

2416210017 commented 8 months ago

@2416210017 Thanks for pointing this out. Can you share what Hue config are you setting into Hue for enabling Trino editor? Also share the config the default_user config value under [desktop].

Also any specific Trino side configurations you've added or is it a vanilla setup?

Thank you for your reply! Of course, the following are the configuration files for [desktop] and [trino] in hue

[desktop]
secret_key=9XOhLUeIAvipV!&o
http_host=0.0.0.0
http_port=8887
time_zone=Asia/Shanghai
app_blacklist=pig,zookeeper,hbase,oozie,indexer,jobbrowser,rdbms,jobsub,sqoop,metastore
django_debug_mode=false
http_500_debug_mode=false
cherrypy_server_threads=50
default_site_encoding=utf
collect_usage=false
enable_prometheus=true
[[django_admins]]
[[custom]]
[[auth]]
backend=desktop.auth.backend.LdapBackend
idle_session_timeout=28800
[[[jwt]]]
[[ldap]]
ldap_url=ldap://192.168.17.100:389
ldap_username_pattern="uid=<username>,ou=People,dc=cc,dc=com"
use_start_tls=false
search_bind_authentication=false
create_users_on_login=true
base_dn="ou=People,dc=cc,dc=com"
bind_dn="cn=Manager,dc=cc,dc=com"
bind_password="xxx@cc.com"

[[interpreters]]
[[[trino]]]
name=Trino
interface=trino
options='{"url": "https://trino.cc.com:443", "auth_username": "hive", "auth_password": "xxxxxxxxxxxx"}'

In addition, our trino is enabled for LDAP authentication and HTTPS. The configuration file is as follows:

cat /data1/trino-server/etc/password-authenticator.properties
password-authenticator.name=ldap
ldap.url=ldap://192.168.17.100:389
ldap.user-bind-pattern=uid=${USER},ou=People,dc=cc,dc=com
ldap.user-base-dn=dc=cc,dc=com
ldap.allow-insecure=true

agl29 commented 8 months ago

Hi @2416210017 , Could you also provide the config.properties file so that we can replicate the same behaviour in our environment?

2416210017 commented 8 months ago

Hi @2416210017 , Could you also provide the config.properties file so that we can replicate the same behaviour in our environment?

Of course not a problem, the following are the correspondingand nginx.conf (the frontend uses nginx to enable SSL proxy for the backend's trino server) in the corresponding trino.

# cat config.properties

discovery.uri=https://trino.cc.com
http-server.http.port=8060
coordinator=true
node-scheduler.include-coordinator=true
internal-communication.shared-secret=lhoOHWfehuKgkXnu4E6Gq5L5K4i8iMPlHFw
query.max-memory=100GB
query.max-memory-per-node=50GB
http-server.authentication.type=PASSWORD
http-server.process-forwarded=true
query.max-length=1000000000
query.max-history=5000
exchange.max-buffer-size=64MB
query.max-concurrent-queries=30
task.client.timeout=60s
query.client.timeout=20m

# cat nginx.conf

    server {
        listen      443 ssl;
        server_name trino.cc.com;

    ssl_certificate  /etc/nginx/conf.d/ssl/xxx.crt;
    ssl_certificate_key /etc/nginx/conf.d/ssl/xxx.key;
    ssl_session_timeout 5m;
    ssl_ciphers ECDHE-RSA-AES128-GCM-SHA256:ECDHE:ECDH:AES:HIGH:!NULL:!aNULL:!MD5:!ADH:!RC4;
    ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
    ssl_prefer_server_ciphers on;

    access_log  /var/log/nginx/access_443.log  proxy;
    proxy_read_timeout 3600s;

        location / {
            proxy_set_header Host $host;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_redirect    off;
            proxy_set_header X-Forwarded-Proto $scheme;

            proxy_pass   http://192.168.17.86:8060;
        }
    }

cloudera / hue

Trino Queries not considering UI selected schema in query and failing with syntax error #3128