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
|
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:
|
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