Skip to main content

Data Query Overview

The data lake has multiple storage systems, and the query language depends on what you are asking about. This section summarizes how to use each of the three.

What you want to ask aboutStorageLanguageDocumentation
Metadata such as equipment, tags, or usersPostgreSQLSQLPostgreSQL
Time series such as tag valuesAster TSDBCQLCQL
Join and bulk analysis across multiple storage systemsSpark (Kyuubi)Spark SQLSpark SQL
One-line rule for choosing

If it's one tag over one period, use CQL. For equipment lists or configurations, use PostgreSQL. If you need to mix multiple storage systems or scan hundreds of millions of rows, use Spark SQL.

The three are different in nature​

PostgreSQLAster TSDB (CQL)Spark SQL
Good atJoins, constraints, transactionsTime-range queries within a single partitionBulk scans, cross-storage joins
Not good atTime series with hundreds of millions of rowsArbitrary joins, queries without a partition keyShort responses (startup cost in seconds)
Response timeMillisecondsMillisecondsSeconds to minutes
Where it attaches5432904210000 (Kyuubi)

🔴 Do not use CQL as if it were SQL. Aster TSDB is a storage system that works by "finding via partition key and scanning in clustering order." If you apply conditions without a partition key, it will search all nodes, and in that case Aster TSDB requires ALLOW FILTERING — and that requirement is a warning, not permission. Send such queries to Spark SQL.

How to connect — two approaches​

① From the console — Execute SQL in a browser. Accounts, permissions, and auditing are enforced.

The Data Gateway Console is that interface. It is the normal path for a person to connect to a node that is in production.

② Inside the node — Connect directly via shell. This is for operations and debugging.

docker exec -it plantpulse-datalake pd node psql   # PostgreSQL
docker exec -it plantpulse-datalake pd node cql    # Cassandra (cqlsh)
Connecting directly to a production node is a last resort

Connecting directly bypasses the gateway's permissions and auditing. Use the console for queries, and use the shell only for tasks that cannot be done from the console.

See also​