Trino

Knowi natively integrates with Trino, allowing you to query and visualize data from multiple sources through Trino's distributed SQL engine. You can transform and visualize Trino data with drag-and-drop visualizations, combine data with other sources, and automate reporting directly in Knowi.

Overview

There are two main approaches for integrating with your Trino cluster:

• UI-based connection: Connect Knowi to Trino through a direct connection.

• Cloud9Agent: Use the Cloud9Agent to integrate with Trino clusters running on your internal network.

With either method, you can issue native Trino SQL queries across one or more catalogs, create datasets for visualizations, blend results with SQL or NoSQL sources, automate queries, and share dashboards.

 

UI-Based Approach

Connecting

1. Log in to Knowi and navigate to Menu → Data Sources.

2. Click + New Datasource.

3. Select Trino under Data Warehouses.

4. Configure the connection:

5. Click Test Connection to validate.

6. Click Save to store the datasource.

Note: For internal clusters, use the Cloud9Agent method.

 

Querying Data

Visual Builder

1. Go to Queries and create a new query.

2. Select your Trino datasource.

3. Use Visual Builder to:

   • Browse tables from the schema explorer

   • Drag and drop fields

   • Apply filters, groupings, and joins

   • Automatically generate SQL

Query Editor

Switch to Query Editor for full SQL control. Example queries:

-- Query across multiple catalogs
SELECT 
    c.custkey,
    c.name AS customer_name,
    o.orderdate,
    o.totalprice
FROM tpch.sf1.customer c
JOIN tpch.sf1.orders o ON c.custkey = o.custkey
WHERE o.orderdate > DATE '2024-01-01'
ORDER BY o.totalprice DESC
LIMIT 100;

-- Cross-catalog query
SELECT 
    m.user_id,
    m.username,
    p.total_orders,
    p.last_order_date
FROM mysql.users.members m
JOIN postgresql.analytics.purchase_summary p 
     ON m.user_id = p.customer_id
WHERE p.total_orders > 10;

AI Assistant Features

• Generate Query: Write your request in natural language, AI builds the SQL.

• Explain Query: Get plain-language explanations of SQL.

• Find Issues: AI scans for optimizations or errors.

• Syntax Help: Get help with Trino-specific SQL.

Data Execution Strategy

• Direct Query: Run queries in real time.

• Cached Data: Store results for faster dashboard performance.

 

Advanced Features

Session Properties

Example of query tuning:

SET SESSION distributed_join = true;
SET SESSION query_max_memory = '8GB';

SELECT * FROM large_table JOIN another_table;

 

Catalog and Schema Navigation

• Browse catalogs in the schema explorer.

• Navigate schemas and tables.

• View column metadata.

• Right-click tables for sample queries.

 

Cloud9Agent Approach

For clusters on internal networks, configure the Cloud9Agent.

Configuration

Update the agent.properties file:

# Trino Datasource Configuration
datasource1.name=Internal Trino Cluster
datasource1.type=TRINO
datasource1.host=trino-coordinator.internal
datasource1.port=8080
datasource1.catalog=hive
datasource1.schema=analytics
datasource1.username=admin
datasource1.password=your_password
datasource1.useSSL=true
datasource1.properties=SSL=true&SSLVerification=NONE

 

Best Practices

1. Specify Schema for improved performance.

2. Push Down Filters to reduce transferred data.

3. Leverage Trino’s Features such as distributed joins.

4. Monitor Performance using Trino’s Web UI.

 

Troubleshooting

• Connection Timeout: Ensure Trino is accessible to Knowi or Cloud9Agent.

• Authentication Failed: Verify credentials and config.

• Catalog Not Found: Confirm catalog exists and is registered.

• SSL/TLS Errors: Check SSL settings and certificates.

For more details, refer to Trino documentation or contact Knowi support.