tencent cloud

Stream Compute Service

DocumentaçãoStream Compute ServiceStream Lake Engine SetatsJob Development GuideDeveloping Spark JobsSpark Batch Queries

Spark Batch Queries

Download
Modo Foco
Tamanho da Fonte
Última atualização: 2026-07-03 15:10:13

Overview

Setats supports batch data queries through Spark. Users can directly access the latest snapshot data in Setats tables using Spark SQL for scenarios such as offline analysis, detailed sampling, aggregation statistics, and result verification.

Prerequisites

You have created a Setats cluster and completed the Warehouse configuration.
You have obtained the Setats Spark Connector Jar package.
You have confirmed that the Spark cluster can access the underlying storage of the Warehouse, as well as the Hive Metastore (if using Hive Catalog).
The required Jar package has been deployed to the local directory of the Spark node or to an HDFS-accessible path.
If you are using EMR Spark 3.3 + Hive Catalog + COS acceleration bucket, prepare the following in advance:
setats-spark-bundle-<spark-version>_<scala-version>-<connector-version>.jar
Hive Metastore available address
Warehouse path, for example, cosn://<bucket>/warehouse/

Preparing the Environment

Logging In to a Spark Node

Using EMR Spark 3.3 as an example, log in to the node where Spark resides and confirm that the Connector Jar has been uploaded to the local directory or HDFS.
If the Jar is stored in a local directory, you can reference it directly via --jars. If it is stored in HDFS, you can also reference the corresponding path in the startup parameters.

Starting Spark SQL

The following example uses Hive Catalog:
sh /usr/local/service/spark/bin/spark-sql \\
  --jars /usr/local/service/spark/setats-spark-bundle-3.3_2.12-<connector-version>.jar \\
  --conf spark.sql.setats.force.read.from.service=true \\
  --conf spark.sql.setats.force.read.service.data=true \\
  --conf spark.sql.session.timeZone='UTC' \\
  --conf spark.driver.cores=4 \\
  --conf spark.driver.memory=4g \\
  --conf spark.executor.memory=4g \\
  --conf spark.executor.cores=4 \\
  --conf spark.sql.extensions=com.tencent.oceanus.spark.extensions.SetatsSparkSessionExtensions \\
  --conf spark.sql.catalog.setats=com.tencent.oceanus.spark.SparkCatalog \\
  --conf spark.sql.catalog.setats.type=hive \\
  --conf spark.sql.catalog.setats.warehouse=cosn://<bucket>/warehouse/ \\
  --conf spark.sql.catalog.setats.uri=thrift://<metastore-host-1>:7004,thrift://<metastore-host-2>:7004
If you use a Hadoop Catalog, you can change spark.sql.catalog.setats.type to hadoop and omit the uri based on the actual situation.

Spark Catalog Parameter Descriptions

Parameter
Description
spark.sql.setats.force.read.from.service
Enable metadata read Setats service.
spark.sql.setats.force.read.service.data
Enable in-memory data read Setats service.
spark.sql.session.timeZone
Spark SQL session time zone. Set it to UTC for time travel queries to avoid time zone offset when reading historical snapshots by timestamp.
spark.sql.extensions
Setats Spark Session extension class: com.tencent.oceanus.spark.extensions.SetatsSparkSessionExtensions
spark.sql.catalog.setats
Setats Spark Catalog implementation class: com.tencent.oceanus.spark.SparkCatalog
spark.sql.catalog.setats.type
Catalog type, such as hive or hadoop.
spark.sql.catalog.setats.warehouse
Setats Warehouse address
spark.sql.catalog.setats.uri
Hive Metastore address, required only for Hive Catalog

Query Example

Basic queries:
SELECT *
FROM `setats`.`testdb`.`demo_setats_table1`;
Querying a specified partition:
SELECT *
FROM `setats`.`testdb`.`demo_setats_table1`
WHERE dt = '20260319';
Aggregation queries:
SELECT
  dt,
  COUNT(*) AS cnt
FROM `setats`.`testdb`.`demo_setats_table1`
GROUP BY dt;
Query the synchronization result table example:
SELECT *
FROM `setats`.`testdb`.`mysql_user_behavior_sink`;

System Tables

Spark batch queries support accessing Setats system tables to view metadata information such as table snapshots, manifests, data files, and index files. The system tables currently supported are as follows:
System Tables
Description
snapshots
Query table snapshot history
manifests
Query Manifest file information
manifests_detail
Query Manifest detail information
files
Query data file information for the current snapshot
files_with_dv
Query data file information with deletion vectors
index_files
Query index file information
bucket_manifests
Query association information between Bucket and Manifest
For example, you can view the snapshot history of a target table through system tables:
SELECT *
FROM `setats`.`testdb`.`demo_setats_table1`.`snapshots`;

Querying Partition Information

Currently, when querying partition information in Spark SQL, you need to use the SHOW PARTITIONS statement:
SHOW PARTITIONS `setats`.`testdb`.`demo_setats_table1`;

Time Travel

Setats supports time travel queries based on historical snapshots in Spark, which is applicable to scenarios such as historical data review, result verification, and problem diagnosis.
If you need to query historical data by snapshot version, it is recommended that you first confirm the target snapshot through the snapshots system table and then perform a time travel query.
For example:
SELECT *
FROM `setats`.`testdb`.`demo_setats_table1`
VERSION AS OF <snapshot_id>;
If you need to query historical data by timestamp, you can access historical snapshots using the timestamp method:
SELECT *
FROM `setats`.`testdb`.`demo_setats_table1`
TIMESTAMP AS OF '<yyyy-MM-dd HH:mm:ss>';

Table Name Format

The format for accessing Setats tables in Spark is:
`<catalog_name>`.`<database>`.`<table_name>`
For example: setats.testdb.demo_setats_table1
Anterior: Flink Lookup JoinPróximo: Doris External Table Query

Ajuda e Suporte

Esta página foi útil?

comentários