Skip to content

Trino User Guide#

Start Trino CLI#

  1. Log in to your Trino server where it is installed.

  2. Start Trino.

    ./trino --user $user --catalog $catalog_name
    

Note

For Hive catalogs in Trino server, by default, the privacera_hive service is used for access management. For all other catalogs, privacera_trino service is used.

Verify Trino Integration#

By running some sample queries. you can verify whether Trino has been properly integrated with Privacera and ready to use.

Prerequisite#

It is assumed that Trino server is configured with Privacera Trino plugin and Redshift catalog.

Create Policies#

  1. Create a policy as shown below for displaying metadata related to Redshift catalog.

  2. For a few catalogs such as PostgreSQL, Redshift, Hive and so on, metadata is stored in an information_schema table. So, create a policy to select on the schema as shown below.

Run Queries#

Create Schema#

  1. Query to create a new schema.

    CREATE SCHEMA customer_schema;
    

    The following output is displayed. You will be denied to create a schema since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> create schema custome_schema;
    Query 20210618_070819_00008_7pje8 failed: Access Denied: Cannot create schema custome_schema
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to create a new schema.

    CREATE SCHEMA customer_schema;
    

    The following output is displayed. Now, you will be able to create the schema.

    trino> create schema custome_schema;
    CREATE SCHEMA
    

    To verify if the schema has been created, run the following command.

    trino> show schemas;
       Schema       
    --------------------
    customer_schema
    

Create Table#

  1. Query to create a new table.

        CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    

    The following output is displayed. You will be denied to create a table since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    Query 20210618_071950_00011_7pje8 failed: Access Denied: Cannot create table customer_table
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Create permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to create a new table.

    CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    

    The following output is displayed. Now, you will be able to create the table.

    trino> CREATE TABLE IF NOT EXISTS customer_schema.customer_table ( id int, person_name varchar ,ssn varchar, country varchar, account_id varchar);
    CREATE TABLE
    

Insert Data in Table#

  1. Query to insert data in the table.

    insert into customer_schema.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    

    The following output is displayed. You will be denied to insert data since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> insert into customer_schema.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    Query 20210618_080707_00033_7pje8 failed: Access Denied: Cannot insert into table customer_table
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Insert permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to insert multiple data in the table.

    insert into customer_schema.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    insert into customer_schema.customer_table values (2, 'Genne','242-92-5772','US','661-338-6787');
    insert into customer_schema.customer_table values (3, 'Edward','251-91-5542','US','231-338-5789');
    insert into customer_schema.customer_table values (4, 'Pearlene','217-97-5522','UK','708-471-6810');
    insert into customer_schema.customer_table values (5, 'James','263-95-5530','US','209-626-9041');
    insert into customer_schema.customer_table values (6, 'Nancy','281-98-5531','US','303-239-4282');
    

    The following output is displayed. Now, you will be able to insert data in the table.

    trino> insert into customer_schema_t.customer_table values (1, 'Nancy','208-95-5535','UK','856-232-9702');
    INSERT: 1 row
    Query 20210618_080500_00031_7pje8, FINISHED, 1 node
    Splits: 35 total, 35 done (100.00%)
    4.01 [0 rows, 0B] [0 rows/s, 0B/s] 
    

Select Data in Table#

  1. Query to select data in the table.

    select * from customer_schema.customer_table;
    

    The following output is displayed. You will be denied to select data since the Trino service you just installed does not have the necessary permissions. To verify, you can check Privacera Portal Audits page.

    trino> select * from customer_schema.customer_table;
    Query 20210618_082114_00034_7pje8 failed: Access Denied: Cannot select from columns [country, account_id, person_name, id, ssn] in table or view customer_table
    
  2. Permissions in Access Management.

    In Privacera Portal, create a policy with Select permissions for your Trino user under privacera_trino service as shown below.

  3. Rerun the query to select data in the table.

    SELECT * FROM customer_schema.customer_data;
    

    The following output is displayed. Now, you will be able to insert data in the table.

    trino:customer_schema> select * from customer_schema.customer_table;
    id | person_name |     ssn     | country |  account_id  
    ----+-------------+-------------+---------+--------------
    1 | Nancy       | 208-95-5535 | UK      | 856-232-9702 
    2 | Genne       | 242-92-5772 | US      | 661-338-6787 
    3 | Edward      | 251-91-5542 | US      | 231-338-5789 
    4 | Pearlene    | 217-97-5522 | UK      | 708-471-6810 
    5 | James       | 263-95-5530 | US      | 209-626-9041 
    6 | Nancy       | 281-98-5531 | US      | 303-239-4282 
    (6 rows)
    
    Query 20210615_083840_00045_4sqq5, FINISHED, 1 node
    Splits: 17 total, 17 done (100.00%)
    0.40 [6 rows, 0B] [15 rows/s, 0B/s]
    

Row-level Filtering#

Trino supports row-level filtering at a table-level.

  1. Create a row-level policy on the table created above to display rows only with US country.

  2. Query the table to display rows only with the country US.

    trino:customer_schema> select * from customer_table;
    

    The following output will be displayed.

    trino:customer_schema> select * from customer_table;
    id | person_name |     ssn     | country |  account_id  
    ----+-------------+-------------+---------+--------------
    2 | Genne       | 242-92-5772 | US      | 661-338-6787 
    3 | Edward      | 251-91-5542 | US      | 231-338-5789 
    5 | James       | 263-95-5530 | US      | 209-626-9041 
    6 | Nancy       | 281-98-5531 | US      | 303-239-4282 
    (4 rows)
    

Column-level Masking#

Trino supports column-level masking at a table-level.

  1. Create a column-masking policy on the table created above to show only the last 4 digits of the Social Security Number (SSN).

  2. Query the table to show only the last 4 digits of the Social Security Number (SSN).

    trino:customer_schema> select * from customer_table;
    

    The following output will be displayed.

    trino:customer_schema> select * from customer_table;
    id | person_name |     ssn     | country |  account_id  
    ----+-------------+-------------+---------+--------------
    1 | Nancy       | XXXXXXX5535 | UK      | 856-232-9702 
    2 | Genne       | XXXXXXX5772 | US      | 661-338-6787 
    3 | Edward      | XXXXXXX5542 | US      | 231-338-5789 
    4 | Pearlene    | XXXXXXX5522 | UK      | 708-471-6810 
    5 | James       | XXXXXXX5530 | US      | 209-626-9041 
    6 | Nancy       | XXXXXXX5531 | US      | 303-239-4282 
    (6 rows)
    
    Query 20210615_085728_00055_4sqq5, FINISHED, 1 node
    Splits: 17 total, 17 done (100.00%)
    0.77 [6 rows, 0B] [7 rows/s, 0B/s]
    

Note

To know more about Trino refer - Trino SQL Document


Last update: July 23, 2021