Skip to main content

Spark SQL

Use this when you need to mix multiple stores or scan hundreds of millions of rows. It can join Aster TSDB · PostgreSQL · Iceberg(RustFS) in a single SQL statement.

ItemValue
SQL gatewayKyuubi — 10000 (thrift) · 10099 (web UI)
Spark master7077, master UI 4440
MetastoreHive Metastore 9083
Iceberg catalogGravitino REST 19001
AccountPP_SPARK_USER / PP_SPARK_PASSWORD
There is a startup cost

Opening a single session takes several seconds. Do not send short queries here — for those, CQL or PostgreSQL is much faster.

Connecting​

beeline (via Kyuubi — recommended)​

docker exec -it plantpulse-datalake bash -c \
  '/opt/kopens/plantpulse-platform/plantpulse-analytics/spark/bin/beeline \
     -u "jdbc:hive2://127.0.0.1:10000/default" \
     -n "$PP_SPARK_USER" -p "$PP_SPARK_PASSWORD"'

This gives you session isolation and queueing. External SQL clients like DBeaver · Tableau also connect using the same JDBC URL.

spark-sql (direct — for debugging)​

docker exec -it plantpulse-datalake \
  /opt/kopens/plantpulse-platform/plantpulse-analytics/spark/bin/spark-sql \
  --master spark://127.0.0.1:7077

Examples​

Viewing catalogs and tables​

SHOW CATALOGS;
SHOW DATABASES;
SHOW TABLES IN default;
DESCRIBE TABLE default.tm_tag_point;

Large-scale aggregation — things CQL cannot do​

Aggregations that scan everything without a partition key are done here.

SELECT tag_id, count(*) AS points, avg(value) AS avg_value
FROM tm_tag_point
WHERE timestamp >= '2026-07-01' AND timestamp < '2026-08-01'
GROUP BY tag_id
ORDER BY points DESC
LIMIT 50;

Joining across stores​

View time series (Aster TSDB) and equipment metadata (PostgreSQL) in one query.

SELECT e.equipment_name,
       count(*)      AS points,
       avg(p.value)  AS avg_value
FROM tm_tag_point p
JOIN tm_equipment e ON e.equipment_id = p.equipment_id
WHERE p.timestamp >= '2026-07-01' AND p.timestamp < '2026-08-01'
GROUP BY e.equipment_name
ORDER BY points DESC;

Querying archives (Iceberg)​

Historical data that has moved to the cold tier remains as Iceberg tables.

-- 스냅샷 이력
SELECT * FROM iceberg.pp.tm_tag_point.snapshots ORDER BY committed_at DESC LIMIT 10;

-- 특정 시점의 모습 (타임 트래블)
SELECT count(*) FROM iceberg.pp.tm_tag_point
  TIMESTAMP AS OF '2026-07-01 00:00:00';

To check the status of archive jobs, see the cold_tier · archive_job lines in pd retention.

Things to know​

  • 🔴 Use Spark when aggregating tiered Aster TSDB tables without a partition key. CQL's full-scan aggregation does not merge chunks, so it returns incorrect values (tiered storage).
  • The master UI is on 4440 (not 8080).
  • PP_SPARK_PASSWORD is used for Kyuubi connection authentication, and hive-auth.properties creates it.
  • The archiver (plantpulse-warehouse-web) runs outside the data lake, in the platform image → warehouse module.

See also​