Trino ADB connector usage example

Konstantin Alpashkin

Contents

Prepare ADB cluster
Run the example

This article describes a step-by-step scenario that highlights major operations with an ADB cluster using the Trino connector.

Prepare ADB cluster

To run the example scenario, ensure that your ADB cluster is configured according to the following requirements:

The adb_trino_demo_user user with the CREATEEXTTABLE privileges and 123 password is created in the ADB cluster.

To add the test user, run psql on the ADB master host:

$ sudo su - gpadmin
$ psql adb

In the psql terminal, run the following query:

CREATE ROLE adb_trino_demo_user
WITH  CREATEEXTTABLE(protocol='gpfdist',type='readable')
      CREATEEXTTABLE(protocol='gpfdist',type='writable')
      LOGIN
      PASSWORD '123';

The adb_trino_demo_db database exists in the ADB cluster. To create the database, use the query:
CREATE DATABASE adb_trino_demo_db;

The adb_trino_demo_user user is allowed to connect to the adb_trino_demo_db database. For this purpose, you can add the following record to the pg_hba.conf file:

host    all  adb_trino_demo_user       0.0.0.0/0     md5

Note that it is not a production-ready configuration. Use it only for test needs. Instead of 0.0.0.0/0, you can specify the IP addresses (with a subnet number) of the ADH hosts with Trino service components.

TIP

You can modify the pg_hba.conf file via the ADCM web interface. To do this, fill in the Custom pg_hba section parameter on the Configuration tab of the ADB service in the ADB cluster. To apply the changes, click Save and run the Reconfigure & Restart service action.

The adb_trino_demo_db database contains the author test table (in the public schema). To create and populate the test table, run the following queries in psql.

First, connect to the test database:

\c adb_trino_demo_db

Then, run the queries:

CREATE TABLE public.author (id INT NOT NULL, name TEXT NOT NULL)
WITH (appendoptimized=true)
DISTRIBUTED BY(id);

INSERT INTO public.author(id, name) VALUES
(1,'Virginia Woolf'),
(2,'J.R.R. Tolkien'),
(3,'Harper Lee'),
(4,'J.D. Salinger'),
(5,'George Orwell'),
(6,'John Steinbeck'),
(7,'Margaret Mitchell'),
(8,'Alan Moore'),
(9,'Jack Kerouac'),
(10,'Ernest Hemingway');

The adb_trino_demo_user ADB user has sufficient permissions to work with the author table. To grant the read/write permissions, use the query:
GRANT SELECT, INSERT ON public.author TO adb_trino_demo_user;

Run the example

Create a new Trino catalog to work with ADB. For this, open the Trino service configuration in ADCM, enable Custom catalogs, and specify the following catalog properties:

catalog_name key value

adbcatalog

connector.name

adb

connection-url

jdbc:postgresql://10.92.40.79:5432/adb_trino_demo_db

Where:

10.92.40.79 is the sample IP address of the ADB master host. Replace it with your own value.
adb_trino_demo_db is the test ADB database to work with. The DB has to be created in advance, the creation steps are described in prerequisites.

connection-user

adb_trino_demo_user

connection-password

123

Save the configuration and restart the Trino service.

TIP
With dynamic catalog management, you can create/delete catalogs using CREATE/DELETE commands without having to restart the Trino service.
Connect to Trino using a JDBC client like DBeaver (or Trino CLI client). When connecting, provide Trino JDBC URL, for example:
jdbc:trino://ka-adh-2.ru-central1.internal:18188
The up-to-date JDBC connection string is available in ADCM (Clusters → <ADH_cluster> → Services → Trino → Info).
Once connected to Trino, list Trino catalogs using the command:
SHOW CATALOGS;
Verify that the test ADB catalog is available:
Catalog | ----------+ adbcatalog| system |
TIP
To analyze queries submitted by the Trino connector to ADB, use Trino UI.
Select the test catalog and the schema for further queries:
USE adbcatalog.public;

Read data from the test ADB table. The steps to create the test table are described in the prerequisites.

SELECT * FROM author;

The output:

id|name             |
--+-----------------+
 2|J.R.R. Tolkien   |
 1|Virginia Woolf   |
 6|John Steinbeck   |
 9|Jack Kerouac     |
 5|George Orwell    |
 3|Harper Lee       |
 7|Margaret Mitchell|
 8|Alan Moore       |
 4|J.D. Salinger    |
10|Ernest Hemingway |

Write data to the test table:

INSERT INTO author (id, name)
VALUES (11, 'L.N. Tolstoy');

Check the write operation results on the ADB side.
For this, run psql on the ADB master host:
$ sudo su - gpadmin $ psql adb_trino_demo_db
In the psql terminal, run the query:
SELECT * FROM public.author WHERE name LIKE '%Tolstoy%';
The output contains the record submitted to ADB by the Trino connector:
id | name ----+-------------- 11 | L.N. Tolstoy (1 row)

Create a new ADB table using the Trino connector. For this, run the following query in DBeaver:

CREATE TABLE public.transactions (
  txn_id integer,
  acc_id varchar,
  txn_value double,
  txn_date date
)
WITH (
  distributed_by = ARRAY['acc_id', 'txn_date'], (1)
  appendoptimized = true (2)
);

1	Specifies the hash distribution by several columns.
2	Sets the append-optimized table type.

For more information on setting table properties via the connector, see ADB table properties.

INSERT INTO public.transactions
(txn_id, acc_id, txn_value, txn_date)
VALUES
    (1, '1001', cast(20.00 as double), cast('2025-01-02' AS date)),
    (2, '1002', cast(110.50 as double), cast('2025-01-01' AS date)),
    (3, '1003', cast(23 as DOUBLE), cast('2025-01-01' AS date));

Check the newly created table using psql on the ADB master host.

\dt;

The new table is available:

                         List of relations
 Schema |     Name     | Type  |        Owner        |   Storage
--------+--------------+-------+---------------------+-------------
 public | author       | table | gpadmin             | append only
 public | transactions | table | adb_trino_demo_user | append only
(2 rows)

To delete the test ADB table, use the command:
DROP TABLE IF EXISTS public.transactions;
NOTE
The Trino connector can drop tables only if the ADB table owner is specified in the ADB catalog settings.

Found a mistake? Seleсt text and press Ctrl+Enter to report it