Connecting to Trino
Trino offers an SQL endpoint via a HTTP API. An open source JDBC driver does exist as well as a proprietary ODBC driver.
Multiple SQL clients can be used to connect to Trino as described below.
Prerequisites
The Trino server must be reachable from your local computer. It is typically secured via HTTPS with a certificated provided by a SecretClass.
As most setups use a self-signed certificate, that cannot be verified without the CA certificate, the easiest way is to disable the Trino cert validation as the following guide will do, however this is not secure!
To use certificate validation instead, you need to extract the ca.crt file from the the SecretClass and provide it to the Trino client.
Connect with trino-cli
Consult the official trino-cli docs for details on how to connect to a running Trino cluster.
The --insecure flag ignores the server TLS certificate and is required in this case, so your command looks something like:
$ java -jar ~/Downloads/trino-cli-403-executable.jar --server https://85.215.195.29:8443 --user admin --password --insecure
In case you are using OpenID connect, use --external-authentication instead of --password.
A browser window will be opened, which might require you to log in.
Please note that you still need to pass the --user argument because of this Trino issue.
|
Connect with DBeaver
DBeaver is a free multi-platform database tool for anyone who needs to work with databases. It is installed locally and can connect to Trino and offers a convenient UI to explore and work with Trino.
First of you need to click on the New Database Connection icon, select Other > Trino.
Afterwards you need to enter the Host and Port as well as Username and Password.
After entering the details you need to switch to the tab called Driver properties.
First action is to click on the SSL driver property and enter the value true.
Additionally you need to click on the green plus icon in the bottom left corner to add a User Property called SSLVerification.
Afterwards you need to set the value to NONE.
As the last step you can click on Finish and start using the Trino connection.
In case you are using OpenID connect, set the externalAuthentication property to true and don’t provide and username or password.
A browser window will be opened, which might require you to log in.
|
Connect with Python
For more information on how to connect to Trino from Python, have a look at the official trino-python-client.
A minimal example of making a connection and running a query looks like this:
import trino
def get_connection():
connection = trino.dbapi.connect(
host="trino-coordinator-default.default.svc.cluster.local",
http_scheme="https",
verify=False, # For best security you can also provide a path to the trino root ca: "/stackable/secrets/trino-ca-cert/ca.crt",
port=8443,
user="admin",
auth=trino.auth.BasicAuthentication("admin", "adminadmin"),
catalog="hive",
schema="staging",
)
return connection
def run_query(connection, query):
# print(f"[DEBUG] Executing query {query}")
cursor = connection.cursor()
cursor.execute(query)
return cursor.fetchall()
connection = get_connection()
run_query(connection, "show catalogs")In case you are using OpenID connect use the following connection definition:
connection = trino.dbapi.connect(
host="87.106.119.4", # You need to use an an externally reachable address, so that your browser can access it!
port=8443,
http_scheme="https",
verify=False, # For best security you can also provide a path to the trino root ca: "/stackable/secrets/trino-ca-cert/ca.crt",
auth=trino.auth.OAuth2Authentication(),
catalog="hive",
schema="staging",
)This snippet prints the following statement
Open the following URL in browser for the external authentication:
https://87.106.119.4:8443/oauth2/token/initiate/52787f3f7e18791121e3123eedcd797a06c0870548c007cd96e6e82899fbf830Click on the link, potentially log in and you should be authenticated.