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

[sbbagal13]

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]

[sbbagal13]

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

[sbbagal13]

@Harshg999 any suggestion this issue?

[Harshg999]

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]

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]

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]

@bjornalm could you keep this issue open

[Harshg999]

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]

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]

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:

1. 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.)
2. 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]

@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]

@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="[email protected]"

[[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]

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

[2416210017]

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 corresponding<config. properties>and 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;
        }
    }