How to query data in Apache Hudi using StarRocks

[alberttwong]

Prerequisites
For this tutorial, you need to:

• Have Docker Desktop or podman container runtime installed.
• Have a MySQL client.
• A StarRocks or CelerData database.
• An Apache Hudi environment.
• Configure Apache Hudi.
• Create an external catalog and query the data.

Have Docker Desktop or podman container runtime installed

This is out of scope for the tutorial.

Have a MySQL client

This is out of scope for the tutorial.

A StarRocks or CelerData database cluster

This is out of scope for the tutorial.

An Apache Hudi environment

This is out of scope for the tutorial. However, we are using the Apache Hudi docker container for this tutorial.

Note

This article was written for Apache Hudi 0.13.1

Configure Apache Hudi

Follow the Hudi Docker Quickstart at https://hudi.apache.org/docs/docker_demo

Modify docker-compose_hadoop284_hive233_spark244.yml or docker-compose_hadoop284_hive233_spark244_mac_aarch64.yml to include starrocks in the hudi docker compose. Also you need to apply apache/hudi#8700 if they haven't merged it in yet to fix the docker networking issues.

  starrocks:
    image: starrocks/allin1-ubuntu
    hostname: starrocks-fe
    container_name: allin1-ubuntu
    ports:
      - 8030:8030
      - 8040:8040
      - 9030:9030

Do all the steps in the hudi docker compose quickstart. When you can do a show tables with beehive, you know that tables are ready and SR should be able to connect. It should look like this:

atwong@Alberts-MBP docker % docker exec -it adhoc-2 /bin/bash
root@adhoc-2:/opt# beeline -u jdbc:hive2://hiveserver:10000 \
>   --hiveconf hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat \
>   --hiveconf hive.stats.autogather=false
Connecting to jdbc:hive2://hiveserver:10000
23/05/13 02:21:33 INFO jdbc.Utils: Supplied authorities: hiveserver:10000
23/05/13 02:21:33 INFO jdbc.Utils: Resolved authority: hiveserver:10000
23/05/13 02:21:33 INFO jdbc.HiveConnection: Will try to open client transport with JDBC Uri: jdbc:hive2://hiveserver:10000
Connected to: Apache Hive (version 2.3.3)
Driver: Hive JDBC (version 1.2.1.spark2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1.spark2 by Apache Hive
0: jdbc:hive2://hiveserver:10000> show tables;
+---------------------+--+
|      tab_name       |
+---------------------+--+
| stock_ticks_cow     |
| stock_ticks_mor_ro  |
| stock_ticks_mor_rt  |
+---------------------+--+
3 rows selected (0.081 seconds)
0: jdbc:hive2://hiveserver:10000> select count(*) from stock_ticks_cow
0: jdbc:hive2://hiveserver:10000> ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
+------+--+
| _c0  |
+------+--+
| 197  |
+------+--+
1 row selected (3.117 seconds)

Create a external catalog and query the data.

Login to the SR container within the hudi docker compose

mysql -P9030 -h127.0.0.1 -uroot --prompt="StarRocks > "

then execute the sql commands to read from the COW tables.

Tip: It should be faster the second time you run the query due to StarRock's query caching is that enabled out of the box.

Note: The Hudi quickstart stores all the data in a HDFS container in the Docker Compose. Therefore we don't need the S3 location and credentials.

CREATE EXTERNAL CATALOG hudi_catalog_hms PROPERTIES (     "type" = "hudi",       "hive.metastore.uris" = "thrift://hivemetastore:9083" );
set catalog hudi_catalog_hms;
use default;
select count(*) from stock_ticks_cow;

Output should look similar to this:

StarRocks > select count(*) from stock_ticks_cow;
+----------+
| count(*) |
+----------+
|      197 |
+----------+
1 row in set (1.23 sec)

Run it again and it'll execute against the query cache.

StarRocks > select count(*) from stock_ticks_cow;
+----------+
| count(*) |
+----------+
|      197 |
+----------+
1 row in set (0.08 sec)

Issue (workaround provided): Hudi exception reading data. com.google.common.util.concurrent.UncheckedExecutionException #23374

Also I would recommend you try querying the MOR tables.

If you run into any issues, I would view the fe.log and be.logs

# bash
root@starrocks-fe:/data/deploy# ls
banner.txt  be.conf  entrypoint.sh  fe.conf  starrocks
root@starrocks-fe:/data/deploy# cd starrocks/
root@starrocks-fe:/data/deploy/starrocks# ls
LICENSE.txt  NOTICE.txt  apache_hdfs_broker  be  director  fe  feproxy  supervisor
root@starrocks-fe:/data/deploy/starrocks# cd fe
root@starrocks-fe:/data/deploy/starrocks/fe# ls
bin  conf  lib  log  meta  plugins  spark-dpp  temp_dir  webroot
root@starrocks-fe:/data/deploy/starrocks/fe# cd log
root@starrocks-fe:/data/deploy/starrocks/fe/log# tail -f fe.out 
    </Logger>
    <Logger name='audit.slow_query' level='INFO'/><Logger name='audit.query' level='INFO'/><Logger name='dump.query' level='INFO'/><Logger name='big_query.query' level='INFO'/>
  </Loggers>
</Configuration>
=====
[2023-09-14 16:34:40] notify new FE type transfer: UNKNOWN
[2023-09-14 16:34:40] notify new FE type transfer: LEADER
[2023-09-14 16:34:41] leader finished to replay journal, can write now.
Sep 14, 2023 4:34:46 PM com.baidu.jprotobuf.pbrpc.client.ProtobufRpcProxy proxy
INFO: Use global share channel pool to create protobuf RPC proxy with interface interface com.starrocks.rpc.PBackendService

[Gatsby-Lee]

thank you

[alberttwong]

https://dev.to/michaelmt66/build-an-open-source-lakehouse-with-minimun-code-effort-spark-hudi-dbt-hivemetastore-trino-1amg for another example. You can swap out trino for starrocks.

[alberttwong]

This doesn't work