Installing and Configuring the VAST Connector for Trino

Installing the VAST Trino Connector for the VAST Database

VAST provides a Trino server and VAST Connector for Trino in a docker image.

Download the image for your version of VAST Cluster from https://hub.docker.com/r/vastdataorg/trino-vast. Follow the instructions there to configure the image on your client.

Compatibility of Trino versions with VAST Cluster

Prerequisites for Trino

These are the specific prerequisites if you are using Trino as the query engine for the VAST Database:

• A Trino cluster must be up and running

  Tip:      

  Consult VAST Support if you need help with installing a Trino cluster.

• A low-latency, high-bandwidth network connection between the Trino nodes and the VAST cluster.

Configuring Trino

vast.properties

The Trino image includes a VAST configuration file, vast.properties, which looks like this:

connector.name=vast
endpoint={VAST_ENDPOINT}
region=us-east-1
access_key_id={VAST_ACCESS_KEY_ID}
secret_access_key={VAST_SECRET_ACCESS_KEY}

# Preliminary tuning parameters
num_of_splits=64
num_of_subsplits=10
vast.http-client.request-timeout=60m
vast.http-client.idle-timeout=60m

data_endpoints={VAST_DATA_ENDPOINTS}

vast.http-client.request-timeout=100h
vast.http-client.idle-timeout=290s
vast.http-client.max-connections=100
vast.http-client.max-connections-per-server=50

Over-the-wire Compression of Query Results

You can configure the connector to compress query results as they are sent to the client, using zstandard compression.

To do this, add this line to the vast.properties file:

vast.compression=zstd

jvm.config

Add these lines to the jvm.config file:

--add-opens=java.base/java.nio=ALL-UNNAMED
--add-opens=java.base/java.lang=ALL-UNNAMED
--add-exports=java.base/sun.nio.ch=ALL-UNNAMED

Verifying the Trino Environment

Run the following commands from your Trino client to confirm that the connector is correctly set up:

1. List Trino catalogs:

   trino> SHOW CATALOGS;

   The output should include a catalog named vast:

    Catalog
   ---------
    <...>
    vast
    <...>

2. List schemas in the vast catalog:

   trino> SHOW SCHEMAS FROM vast;

   The output should include the database and the schema you created when configuring the VAST cluster:

          Schema
   --------------------
     <...>
    vastdb/schema1
     <...>

3. List tables in schema vastdb/schema1:

   trino> SHOW TABLES FROM vast."vastdb/schema1";

   If you did not create any tables in the schema, the command completes but the output shows no tables.

4. (Optional) Set vastdb/schema1 as the default namespace for this session. This allows you to omit specifying the catalog and schema in the following steps.

   trino> USE vast."vastdb/schema1";

5. Create a table in schema vastdb/schema1:

   trino:vastdb/schema1> CREATE TABLE cats (weight BIGINT, name VARCHAR, attitude REAL, date DATE, time TIMESTAMP);

6. List columns in the newly created table:

   trino:vastdb/schema1> DESCRIBE cats;

   The output should show the columns:

     Column  |     Type     | Extra | Comment
   ----------+--------------+-------+---------
    weight   | bigint       |       |
    name     | varchar      |       |
    attitude | real         |       |
    date     | date         |       |
    time     | timestamp(3) |       |
   (5 rows)

7. Add data to the newly created table:

   trino:vastdb/schema1> INSERT INTO cats (weight, name, attitude, date, time) VALUES (14, 'jeff', 2.5, date('2022-12-07'), now());

8. Run a transaction:

   trino:vastdb/schema1> START TRANSACTION;
   INSERT INTO cats (
     weight, name, attitude, date, time
   ) VALUES (
     12, 'happy', 18.7, date('2022-12-02'), now()
   );
   INSERT INTO cats (
     weight, name, attitude, date, time
   ) VALUES (
     8, 'joy', 19.2, date('2022-12-02'), now()
   );
   INSERT INTO cats (
     weight, name, attitude, date, time
   ) VALUES (
     11, 'chester', 13.2, date('2022-12-02'), now()
   );
   COMMIT;

9. List table rows:

   trino:vastdb/schema1> SELECT * FROM cats;

   The output should contain the following rows:

    weight |  name   | attitude |    date    |          time
   --------+---------+----------+------------+-------------------------
        14 | jeff    |      2.5 | 2022-12-07 | 2022-12-08 19:50:51.471
        12 | happy   |     18.7 | 2022-12-02 | 2022-12-08 19:55:35.325
         8 | joy     |     19.2 | 2022-12-02 | 2022-12-08 19:55:36.390
        11 | chester |     13.2 | 2022-12-02 | 2022-12-08 19:55:37.180
   (4 rows)

10. Verify that you can view a query execution plan:

    trino:vastdb/schema1> EXPLAIN SELECT * FROM cats WHERE weight > 10 AND attitude < 10;

Trino Client Supported Features

Creating Database Views

You can create database views using the Trino client when connected to a VAST Database. Views allow you to save queries and re-use them in other queries.

Database views are stored queries that present a part of a VAST Database. They are created and managed using the client application and stored in a table in the database itself. They can be used in other queries on the database.

CREATE VIEW vast."mybucket/myschema".myview
    COMMENT "view comment"
         AS (SELECT col1, col2
               FROM vast."mybucket/myschema".mytable
              WHERE col1 > 0)

SHOW TABLES FROM vast."mybucket/myschema" LIKE '%_view%'

SHOW CREATE VIEW vast."mybucket/myschema".myview

ALTER VIEW vast."mybucket/myschema".myview
        AS (SELECT col1, col2
              FROM vast."mybucket/myschema".mytable
             WHERE col1 > 0 AND col2 > 0)

  SELECT *
    FROM vast."mybucket/myschema".myview
   WHERE v_col2 > 5
ORDER BY v_col1

ALTER VIEW vast."mybucket/myschema".myview RENAME TO vast."mybucket/myschema".mynewview

Limitations

• View properties are not supported

• Nested data types are not supported

• User-defined column names and comments are lost if the schema of the query changes when redefining a view