# Trino Quickstart Info Prior to its rebranding in 2021, the Trino query engine was known as PrestoSQL or more simply Presto. Treasure Data supported several earlier versions of Presto. Newer versions of Presto are now supported under the Trino branding, and support for [Trino 423](/products/customer-data-platform/data-workbench/queries/trino/trino-423) has now been introduced. For the immediate future, you will see both Presto and Trino used in Treasure Data documentation. New functionality or new features will be identified as being Trino; however, documentation for existing functions and features that are common to both Presto and Trino will continue to be identified as Presto. [Trino](https://github.com/trinodb/trino) is an open-source, distributed SQL query engine. Trino can query data where it is stored, without needing to move data into a separate analytics system. Treasure Data has customized Trino to talk directly with our distributed columnar storage layer. ## Accessing Trino You can access Trino using the following: - TD Console - Trino JDBC/ODBC Driver - TD Toolbelt - REST API - Data Tank with Trino ### TD Console 1. Open the TD Console 2. Open the **Data Workbench** 3. Select **Queries** ![image](/assets/345090.de1e51b9284b079634025bc992c1cb036c6294932e248c711fa72f0469f6d2dd.89112c51.png) 4. Click **New Query** button ![image](/assets/345092.a637e42e590f7a9c6f56508896620c3f65844fcb5ba968466d5c5821bcd134b0.89112c51.png) 5. Select the query type of **Trino**, write a query on the editor and click "Run" button 6. After you run your query, you can check the query processing log ![image](/assets/345091.8e1477613a2b2185a7fe38eec14a9699bbeec53406e77ec95716a49c50c98757.0053d791.png) ### Trino Gateway The US Trino gateway resides at api-presto.treasuredata.com/. Additional endpoints can be found [here](/apis/endpoints/endpoints). You can use the TD toolbelt and drivers to connect to Treasure Data and issue queries. - [JDBC Driver for Trino](#about-the-jdbc-driver-for-trino) - can be used by a Java application to connect to, and issue queries to, the Trino query engine. - [ODBC Driver for Trino](#odbc-driver-for-trino) - can be used by Windows applications to connect to, and issue queries to, the Trino query engine. ### About the JDBC Driver for Trino The Trino JDBC connection has several limitations: - setAutoCommit(false) throws an exception because transactions are not supported. - trino-jdbc queries share the same Trino resources you have available for any other ad hoc or scheduled queries that you submit from the TD Console or through the REST API. - trino-jdbc queries are shown in your account job log, with the exception of the following standard entries: - Query result table - Query plan - Progress log - The query result is converted into JSON before being submitted back to separate tools for display. As such, very large result tables may take some time before being displayed. If you have latency problems, contact Support. - Certain BI tools issue “information_schema” requests that cause failed queries in our system. This may be an issue in your tool during first use. If you see any unexpected errors/bugs/logs that occur during beta use, contact Support. Our aim is to keep this API up as much as possible. Specifically, we expect to achieve a downtime of less than 1 hour total per month. **Setting up the JDBC Driver for Trino** 1. Download the [Trino jdbc driver](https://repo1.maven.org/maven2/io/trino/trino-jdbc/423/trino-jdbc-423.jar). - For using secure HTTPS (port 443) connection you need to use Trino 0.148 or later. 2. Use the following parameters to connect prestobase on development server: - Driver name: io.trino.jdbc.TrinoDriver (if you use TrinoSQL Driver, you may use io.prestosql.jdbc.PrestoDriver) - user: (Your TD API key) - password: (dummy string. This value will be ignored) - After Trino-jdbc 0.180, because of a [bug](https://github.com/prestodb/presto/issues/8566), a dummy string needs to be set to the password. **Connection URL examples** The connection URL needs to have the following format: `jdbc:trino://api-presto.treasuredata.com:443/td/(database name)?SSL=true` or `jdbc:presto://api-presto.treasuredata.com:443/td/(database name)?SSL=true` ``` jdbc:trino://api-presto.treasuredata.com:443/td?SSL=true or jdbc:trino://api-presto.treasuredata.com:443/td/sample_datasets?SSL=true ``` ``` jdbc:presto://api-presto.treasuredata.com:443/td?SSL=true or jdbc:presto://api-presto.treasuredata.com:443/td/sample_datasets?SSL=true ``` *Example Code* ```bash javac Sample.java java -cp .:trino-jdbc-0.359.jar Sample (your TD API key) ``` ``` time=1412351990, method=GET, path=/category/office time=1412351976, method=GET, path=/item/software/2265 time=1412351961, method=GET, path=/category/toys time=1412351947, method=GET, path=/item/electronics/3305 time=1412351932, method=GET, path=/category/software ``` *Sample.java File* ```java import java.sql.*; class Sample { public static void main(String[] args) { if(args.length != 1) { System.err.println("Provide your TD API key as an argument"); return; } String apikey = args[0]; try { Connection conn = DriverManager.getConnection("jdbc:trino://api-presto.treasuredata.com:443/td/sample_datasets?SSL=true", apikey, "dummy_password"); Statement stmt = conn.createStatement(); try { ResultSet rs = stmt.executeQuery("SELECT time, method, path from www_access limit 5"); while(rs.next()) { long time = rs.getLong(1); String method = rs.getString(2); String path = rs.getString(3); System.out.println(String.format("time=%s, method=%s, path=%s", time, method, path)); } } finally { stmt.close(); conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } ``` ### ODBC Driver for Trino The Presto ODBC driver will be deprecated in June 2025. There are no plans to provide updates to the ODBC driver in the future; consider using the OSS [Trino JDBC Drive](https://repo1.maven.org/maven2/io/trino/trino-jdbc/) Treasure Data provides a Trino API that can integrate directly with our Trino-specific ODBC driver. The installer for the driver is currently available for Windows 64-bit platform only. Downloads are available from your account or support representative. - Windows Treasure Data Trino ODBC Driver installer [TreasureDataPrestoODBC.msi](https://treasure-data.app.box.com/s/7i1dfqvxsczarsv210gglwvgwtb87i1c) - Tableau Datasource Customization File (.tdc) for Tableau users. [Treasure Data Trino ODBC Driver.tdc](https://treasure-data.box.com/s/lmoidymjdd28ijfiagjp4qwrilyh0un6) - Copy this file to My Documents\My Tableau Repositories\Data Sources folder. (This file controls Tableau to use Trino compatible SQL queries.) **Windows System Requirements** One of the following operating systems: - Windows Vista, 7, 8, or 10 - Windows Server 2008 or later - [Visual C++ Redistributable Packages for Visual Studio 2013](https://www.microsoft.com/en-ca/download/details.aspx?id=40784) installed. **Migrate from Treasure Data ODBC Driver for 32bit** Treasure Data ODBC Driver for 32bit is no longer supported. Replace it with Trino ODBC Driver according to the following instructions. The old driver is no longer supported. **Install the Driver on Windows (64-bit)** 1. Download the ODBC driver installer [TreasureDataTrinoODBC.msi](https://treasure-data.app.box.com/s/jka8w6kkdpr2kqmzomwvjxpw2n8ocrev). 2. Double-click on the installer package and follow the instructions. 3. Open the Start menu (or press the Windows key) and search/select ‘ODBC Data Source Administrator (64-bit)’. 4. Select Treasure Data Trino ODBC DSN, and select **Configure**. 5. Enter your Master TD API key into the User field. 6. Type the following values: - Authentication Type: No Authentication - User: YOUR MASTER API KEY - Host: https://api-presto.treasuredata.com - Port: 443 - Catalog: td ![image](/assets/presto-odbc1.a114edfb7b7abe7432d16c666e4f87ea54abce3a10645d828d3a5fc585e77c66.0053d791.png) ![image](/assets/presto-odbc2.ca0d9e6ca052229eae40d3b62821101e8dd629b5b7f82012f1bd405d1da46e1a.0053d791.png) 1. Select **Test**. 2. If successful, press **OK**. **Configure the Proxy Server** If you are connecting to a data source through a proxy server, you must provide connection information for the proxy server. 1. Open the ODBC Data Source Administrator where you created the DSN. Select the DSN, select **Configure**, and then select **Proxy Options**. 2. Check the **Use Proxy Server** box. 3. In the **Proxy Host** field, enter the hostname or IP address of the proxy server. 4. In the **Proxy Port** field, enter the TCP port number that the proxy server uses to listen for client connections. 5. In the **Proxy Username** field, enter your user name for accessing the proxy server. 6. In the **Proxy Password** field, enter the password of the user name. 7. (Optional) To save the proxy server in the Windows registry, select **Save Password (Encrypted)**. 8. Click **OK**. ### TD Toolbelt You can use the Toolbelt command-line (CLI) client to submit Trino queries. 1. Ensure that [TD Toolbelt](/tools/cli-and-sdks/quickstart) is installed. 2. Execute `td query` command like the following: ``` td query -w -T presto -d testdb "SELECT code, COUNT(1) FROM www_access GROUP BY code" ``` ### REST API You can use [POST /v3/job/issue/presto/:database](/apis/td_api_v3-public/jobs/createjob) endpoint to submit Trino queries. ### Data Tank with Trino Learn about the [Trino query processing flow](/products/customer-data-platform/data-workbench/queries/trino/data_tanks_using_presto#how-does-trino-query-processing-flow) that joins data from a TD table with data from a Data Tank table. ## Trino Query Runtime Limits and Query Hint Override By default, the Trino service limits query execution time to 6 hours. You can manage the Trino query execution time in several ways. You might want to limit some or all Trino queries to run for a shorter period. For example: - You can limit execution time for ad hoc queries by default, to avoid unexpectedly high Trino costs. - You can push users to choose Hive over Trino for large and long-running data pipeline jobs. ### Policies to Limit Query Execution Time Contact Support to request custom limits on the following types of queries: - Ad hoc queries from the Console, and queries submitted through ODBC and JDBC - Queries that run as scheduled queries or as steps in workflows - Queries explicitly assigned to any named resource pool The limits apply to queries created by any user. ### Limiting Execution Time of a Single Query with Query Hints If you want to override any other limits for a single query, you can apply the following query hint at the beginning of the query: ``` -- SET SESSION query_max_execution_time = '6h' SELECT COUNT(*) FROM ALL_EVENTS ; ``` If limits are set in your account, and you write a query with a hint, then the smallest limit between account, resource-group, query-hint, and global is used. Supported time measures are - h (hours) - m (minutes) - s (seconds) info All Trino queries are limited to 6 hours runtime, regardless of any account-level policy or query hint. If the limit specified in the query hint is longer than the configured limit, then the shorter configured limit still applies. For example, if a 1-hour limit is configured for ad hoc queries, then you can use the magic comment to limit a query to 5 minutes (5m) but not to increase the limit to 2 hours (2h). ## Scheduling Trino Queries You might encounter queries that take a very long time to finish. For example, suppose you query the IP addresses of your website visitors which you want to convert into countries and then make a daily summary of all visitors by each country. The query examines the data of IP addresses from the first visitor to the most recent. This query is processed over and over again for each query, resulting in a query that takes over an hour to complete. ## Stream Data Processing Stream data processing uses intermediate tables where only the *new* data (data that arrived since the last query) is processed and appended to the rest of the processed data, rather than processing the entire data every time. For example, you would create an intermediate table by processing all the data you have up to a certain point in time. Then you would schedule a query that processes the new data that arrived since the last query and append the data to the intermediate table. And, finally, you would query the intermediate table for the aggregated data. ### Creating Intermediate Tables The following example uses these elements to create the table: - Sample database: sample_db - Intermediate table: visitor_country - Raw data table: visitor_raw ```sql CREATE TABLE visitor_country AS SELECT TD_DATE_TRUNK('day', time, 'PST') AS time, userid, TD_IP_TO_COUNTRY_CODE(ip) country FROM visitor_raw WHERE TD_TIME_RANGE(time, null, '2017-07-01', 'PST') ``` ### Scheduling and Appending to the Intermediate Tables ```sql INSERT INTO visitor_country SELECT TD_DATE_TRUNK('day', time, 'PST') AS time, userid, TD_IP_TO_COUNTRY_CODE(ip) country FROM visitor_raw WHERE TD_TIME_RANGE(time, TD_TIME_ADD(TD_SCHEDULED_TIME(), ‘-1d’), TD_SCHEDULED_TIME(), ‘PST’); ``` ### Aggregating the Intermediate Tables ```sql SELECT country, approx_distinct(userid) num_users FROM vistitor_country GROUP BY 1 ``` ## Further Reading Review one or more of the following topics to learn more about using the Trino Query Engine: - [Writing Trino Queries](/products/customer-data-platform/data-workbench/queries/trino/writing_trino_queries) - provides examples of different types of Trino queries.