Trino connector: Hive catalog

Introduction

The Hive catalog allows Trino querying data stored in an Apache Hive data warehouse.

Requirements

The Hive connector requires a Hive metastore service (HMS), or a compatible implementation of the Hive metastore, such as AWS Glue.

Apache Hadoop HDFS 2.x supported.

Many distributed storage systems including HDFS, Amazon S3 or S3-compatible systems, Google Cloud Storage, Azure Storage, and IBM Cloud Object Storage can be queried with the Hive connector.

The coordinator and all workers must have network access to the Hive metastore and the storage system.

Hive metastore access with the Thrift protocol defaults to using port 9083.

Data files must be in a supported file format. Some file formats can be configured using file format configuration properties per catalog:

ORC
PARQUET
AVRO
RCFILE
SEQUENCEFILE
JSON
CSV
TEXTFILE

Schema operations

Create a schema

Create a schema
CREATE SCHEMA catalog.schema_name 

Table operations

Create table

The Gravitino Trino connector supports basic Hive table creation statements, such as defining fields, allowing null values, and adding comments. The Gravitino Trino connector supports CREATE TABLE AS SELECT.

CREATE OR REPLACE TABLE AS SELECT is not supported. Use DROP TABLE followed by CREATE TABLE AS SELECT as an alternative.

Create a table
CREATE TABLE catalog.schema_name.table_name
(
  name varchar,
  salary int
)

Alter table

Support for the following alter table operations:

Rename table
Add a column
Drop a column
Rename a column
Change a column type
Set a table property

Select

The Gravitino Trino connector supports most SELECT statements, allowing the execution of queries successfully. It doesn't support certain query optimizations, such as pushdown and pruning functionalities.

Update

UPDATE is only supported for transactional Hive tables with format ORC. UPDATE of partition or bucket columns is not supported.

Delete

DELETE applied to non-transactional tables is only supported if the table is partitioned and the WHERE clause matches entire partitions. Transactional Hive tables with ORC format support "row-by-row" deletion, in which the WHERE clause may match arbitrary sets of rows.

Merge

MERGE is only supported for ACID tables.

Schema and table properties

Set additional properties for tables and schemas in the Hive catalog using "WITH" keyword in the "CREATE" statement.

Create a schema with properties

Create a schema with an HDFS location
CREATE SCHEMA catalog.dbname
WITH (
  location = 'hdfs://{hdfs_host}:9000/user/hive/warehouse/dbname'
);

The following tables are the properties supported by the Hive schema:

Property	Description	Default Value	Required	Reserved
location	HDFS location for table storage	(none)	No	No

Reserved properties: A reserved property is one can't be set by users but can be read by users.

Create a table with properties

Create a table with properties
CREATE TABLE catalog.dbname.tablename
(
  name varchar,
  salary int
) WITH (
  format = 'TEXTFILE',
  KEY = 'VALUE',
  ...      
);

The following tables are the properties supported by the Hive table:

Property	Description	Default Value	Required	Reserved
format	Hive storage format for the table	TEXTFILE	No	No
location	HDFS location for table storage	(none)	No	No
input_format	The input format class for the table	org.apache.hadoop.mapred.TextInputFormat	No	No
output_format	The output format class for the table	org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat	No	No
serde_lib	The serde library class for the table	org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe	No	No
serde_name	Name of the serde	table name by default	No	No
partitioned_by	Partition columns for the table	(none)	No	No
bucketed_by	Bucket columns for the table	(none)	No	No
bucket_count	Number of buckets for the table	(none)	No	No
sorted_by	Sorted columns for the table	(none)	No	No

The following properties are automatically added and managed as reserved properties. Users are not allowed to set these properties.

Property	Description
total_size	Total size of the table
num_files	Number of files
external	Indicate whether it's an external table
table_type	The type of Hive table

Examples

Complete the following steps before you can use the Hive catalog in Trino through Gravitino:

Create a metalake and catalog in Gravitino. The examples assume the metalake name is test and the catalog name is hive_test.

Create the metalake and catalog
curl -X POST -H "Content-Type: application/json" \
-d '{
  "name": "test",
  "comment": "comment",
  "properties": {}
}' http://{gravitino_host}:8090/api/metalakes

curl -X POST \
-H "Content-Type: application/json" \
-d '{
  "name": "hive_test",
  "type": "RELATIONAL",
  "comment": "comment",
  "provider": "hive",
  "properties": {
    "metastore.uris": "thrift://{hive_host}:9083"
  }
}' http://{gravitino_host}:8090/api/metalakes/test/catalogs

For More information about the Hive catalog, refer to Hive catalog.

Set the value of configuration gravitino.metalake to the metalake you have created, named 'test', and start the Trino container.

Use the Trino CLI to connect to the Trino container and run a query.

List the catalogs
SHOW CATALOGS;

Example output
    Catalog
----------------
 gravitino
 jmx
 system
 hive_test
(4 rows)

Query 20231017_082503_00018_6nt3n, FINISHED, 1 node

The gravitino catalog is a catalog defined By Trino catalog configuration. The hive_test catalog is the catalog created by you in Gravitino. Other catalogs are regular user-configured Trino catalogs.

Create tables and schemas

Create a new schema named database_01 in hive_test catalog.

Create a schema
CREATE SCHEMA hive_test.database_01;

Create a schema with an HDFS location
CREATE SCHEMA hive_test.database_01 WITH (
  location = 'hdfs://{hdfs_host}:9000/user/hive/warehouse/database_01'
);

Create a new table named table_01 in schema hive_test.database_01 and stored in a TEXTFILE format, partitioning by salary, bucket by name and sorted by salary.

Create a partitioned, bucketed table
CREATE TABLE  hive_test.database_01.table_01
(
name varchar,
salary int,
month int    
)
WITH (
  format = 'TEXTFILE',
  partitioned_by = ARRAY['month'],
  bucketed_by = ARRAY['name'],
  bucket_count = 2,
  sorted_by = ARRAY['salary']  
);

Write data

Insert a row into table_01
INSERT INTO hive_test.database_01.table_01 (name, salary, month) VALUES ('ice', 12, 22);

Insert rows from a select
INSERT INTO hive_test.database_01.table_01 (name, salary, month) SELECT * FROM hive_test.database_01.table_01;

Delete an entire partition
DELETE FROM hive_test.database_01.table_01 WHERE month=22;

If an ACID table is defined in the schema hive_test.database_01 as follows:

Create an ACID table in Hive
CREATE TABLE database_01.test_acid
(
    id INT,
    name STRING,
    salary INT
)
CLUSTERED BY (id) INTO 4 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

Update rows in test_acid
UPDATE hive_test.database_01.test_acid SET name='bob' WHERE id=1;

Delete rows from test_acid
DELETE FROM hive_test.database_01.test_acid WHERE id=1;

Merge into test_acid
MERGE INTO hive_test.database_01.test_acid t USING hive_test.database_01.table_01 s
    ON (t.name = s.name)
    WHEN MATCHED AND s.name = 'bob'
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE
            SET salary = s.salary + t.salary
    WHEN NOT MATCHED
        THEN INSERT (id, name, salary)
              VALUES (3, s.name, s.salary);

Query data

Query table_01
SELECT * FROM hive_test.database_01.table_01;

Modify a table

Add the age column
ALTER TABLE hive_test.database_01.table_01 ADD COLUMN age int;

Drop the age column
ALTER TABLE hive_test.database_01.table_01 DROP COLUMN age;

Rename table_01 to table_02
ALTER TABLE hive_test.database_01.table_01 RENAME TO hive_test.database_01.table_02;

DROP

Drop a schema
DROP SCHEMA hive_test.database_01;

Drop a table
DROP TABLE hive_test.database_01.table_01;

HDFS config and permissions

For basic setups, the Apache Gravitino Trino connector configures the HDFS client using catalog configurations. It supports configuring the HDFS client with hdfs-site.xml and core-site.xml files via the trino.bypass.hive.config.resources setting in the catalog configurations.

Before running any Insert statements for Hive tables in Trino, you must check that the user Trino is using to access HDFS has access to the Hive warehouse directory. Override this username by setting the HADOOP_USER_NAME system property in the Trino JVM config, replacing hdfs_user with the appropriate username:

etc/jvm.config

-DHADOOP_USER_NAME=hdfs_user

S3

When using AWS S3 within the Hive catalog, users need to configure the Trino Hive connector's AWS S3-related properties in the catalog's properties. Refer to the documentation of Hive connector with Amazon S3.

Create the catalog from the Trino CLI.

Create a Hive catalog with S3
call gravitino.system.create_catalog(
  'gt_hive',
  'hive',
  map(
    array['metastore.uris',
        'trino.bypass.hive.s3.aws-access-key', 'trino.bypass.hive.s3.aws-secret-key', 'trino.bypass.hive.s3.region'
    ],
    array['thrift://hive:9083', '{aws_access_key}', '{aws_secret_key}', '{region}']
  )
);

The settings for trino.bypass.hive.s3.aws-access-key, trino.bypass.hive.s3.aws-secret-key and trino.bypass.hive.s3.region are required by the Apache Gravitino Trino connector.

Once the Hive catalog is successfully created, users can create schemas and tables on S3.

Create a schema and table on S3
CREATE SCHEMA gt_hive.gt_db02
WITH (location = 's3a://trino-test/dw/gt_db02');

CREATE TABLE gt_hive.gt_db02.tb01 (
    name varchar,
    salary int
);

The location specifies the AWS S3 storage path.

After running the command, the tables are ready for data reading and writing operations on AWS S3.

Ensure the Hive Metastore service used by the Hive catalog supports AWS S3.

Introduction​

Requirements​

Schema operations​

Create a schema​

Table operations​

Create table​

Alter table​

Select​

Update​

Delete​

Merge​

Schema and table properties​

Create a schema with properties​

Create a table with properties​

Examples​

Create tables and schemas​

Write data​

Query data​

Modify a table​

DROP​

HDFS config and permissions​

S3​