E-MapReduce:Integrate Paimon with Trino

Prerequisites

A DataLake or Custom cluster with Trino and Paimon is created. Create a cluster.

Limitations

Paimon queries in Trino require EMR 3.46.0 or later, or EMR 5.12.0 or later.

Procedure

1. Specify the warehouse path.

   Paimon stores data and metadata in a file system (such as HDFS) or object storage (such as OSS-HDFS). The warehouse parameter defines the root path for this storage.

   1. Go to the Trino service configuration page.

      1. Log on to the E-MapReduce console.

      2. In the top navigation bar, select a region and resource group as needed.

      3. On the EMR on ECS page, find the target cluster and click Services in the Actions column.

      4. On the Services page, find the Trino service and click Configure.

   2. On the Configure page, find and click the paimon.properties tab.

   3. Set the warehouse parameter to the root path of your storage.

   4. Save the configuration.

      1. Click Save.

      2. In the dialog box, enter an Execution Reason and click Save.

2. (Optional) Specify the metastore type.

   Trino sets the metastore type automatically based on the services selected during cluster creation. To change it, modify the metastore parameter on the paimon.properties tab of the Trino service configuration page.

   Paimon supports three metastore types:

   • filesystem: metadata is stored only in a file system or object storage.

   • hive: metadata is synchronized to a specified Hive Metastore.

   • dlf: metadata is synchronized to Data Lake Formation (DLF).

3. Restart the Trino service.

   1. On the Trino service configuration page, choose More > Restart in the upper-right corner.

   2. In the dialog box, enter an Execution Reason and click OK.

   3. In the Confirm dialog box, click OK.

4. Query Paimon data.

   The following example writes data with Spark SQL using a filesystem catalog, then queries the data in Trino.

   1. Start Spark SQL.

      spark-sql --conf spark.sql.catalog.paimon=org.apache.paimon.spark.SparkCatalog --conf spark.sql.catalog.paimon.metastore=filesystem --conf spark.sql.catalog.paimon.warehouse=oss://<OSS-HDFS domain name>/warehouse

      Note

      • spark.sql.catalog.paimon: Defines a catalog named paimon.

      • spark.sql.catalog.paimon.metastore: Specifies the metastore type for the catalog. Setting the value to filesystem means that metadata is stored in the file system.

      • spark.sql.catalog.paimon.warehouse: Configures the location of the data warehouse. Replace <yourBucketName> with the name of your OSS bucket. For more information about how to create a bucket, see Create a bucket.

   2. Create a table and insert data in Spark SQL.

      -- Switch to the paimon catalog.
      USE paimon;
      
      -- Create and use a test database in the catalog.
      CREATE DATABASE test_db;
      USE test_db;
      
      -- Create a Paimon table.
      CREATE TABLE test_tbl (
          uuid int,
          name string,
          price double
      ) TBLPROPERTIES (
          'primary-key' = 'uuid'
      );
      
      -- Insert data into the Paimon table.
      INSERT INTO test_tbl VALUES (1, 'apple', 3.5), (2, 'banana', 4.0), (3, 'cherry', 20.5);

   3. Start the Trino CLI.

      trino --server master-1-1:9090 --catalog paimon --schema default --user hadoop

   4. Query the data you inserted.

      USE test_db;
      
      SELECT * FROM test_tbl;