Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 37 ยท Table API and SQL

Flink SQL Queries

Write Flink SQL: create tables with connectors, run SELECT, WHERE, GROUP BY, CASE and CTE queries, use INSERT INTO, the SQL client and statement sets, with runnable portable SQL.

Flink SQL is ANSI-style SQL that runs as a streaming job. You describe what result you want, the planner chooses the operators, and the result keeps updating as new events arrive. If you know SQL from databases, you already know most of the syntax; what changes is that tables are unbounded streams and queries never finish on their own.

This lesson covers the day-to-day language. The runnable blocks use portable SQL in the site's SQLite-based runner, so you can practise the query shapes; Flink-only features (connectors, watermarks, windows) are shown read-only. The Table API counterpart is in Table API First Steps.

Real versus stand-in. The runner here is a classic database on a fixed set of rows, not Flink. A Flink query over a stream returns the same answers for the same data, but it produces them as a continuous stream of updates. Dynamic Tables and Changelogs explains the difference.

Tables with connectors

In Flink SQL you first declare tables. The WITH clause names the connector; this is what links SQL to Kafka, files, JDBC and more.

SQL
CREATE TABLE clicks (
  user_id  STRING,
  page     STRING,
  ts       TIMESTAMP(3),
  WATERMARK FOR ts AS ts - INTERVAL '10' SECOND
) WITH (
  'connector' = 'kafka',
  'topic' = 'clicks',
  'properties.bootstrap.servers' = 'broker:9092',
  'properties.group.id' = 'sql-demo',
  'scan.startup.mode' = 'earliest-offset',
  'format' = 'json'
);

CREATE TABLE page_counts (
  page STRING,
  views BIGINT,
  PRIMARY KEY (page) NOT ENFORCED
) WITH (
  'connector' = 'upsert-kafka',
  'topic' = 'page-counts',
  'properties.bootstrap.servers' = 'broker:9092',
  'key.format' = 'json',
  'value.format' = 'json'
);

A PRIMARY KEY ... NOT ENFORCED is a promise to Flink that the key is unique; Flink does not check it, but sinks such as upsert-kafka and JDBC need it to overwrite rows by key.

SELECT, WHERE and expressions

Selecting and filtering look exactly as in any database. Run this: a few orders, filtered and with a computed column.

SQL SELECT, WHERE and computed columns

Press Run (or Ctrl + Enter) to see the result here.

In a streaming query, ORDER BY is only allowed on a time attribute (ascending), because sorting an unbounded stream by anything else would require waiting forever. Use ORDER BY freely in batch mode or inside a Top-N pattern (SQL Windows, Joins and Top-N).

Aggregation: GROUP BY and HAVING

GROUP BY produces one row per group. In streaming mode Flink keeps the running aggregate for each group in state and emits a corrected row whenever it changes. HAVING filters groups after aggregation. CASE computes conditional values, and COUNT(*) FILTER (WHERE ...) counts a subset in one pass.

SQL GROUP BY, HAVING, CASE and FILTER

Press Run (or Ctrl + Enter) to see the result here.

Each row is a state entry in Flink. If the group-by key has unlimited distinct values (for example a session id) the state grows without bound; use windows or configure idle state retention (SET 'table.exec.state.ttl' = '1 d';) so old groups expire.

CTEs, subqueries and set operations

A WITH clause names intermediate results and keeps long queries readable. UNION ALL merges streams with the same schema, a typical way to combine two sources.

SQL CTE and UNION ALL

Press Run (or Ctrl + Enter) to see the result here.

UNION without ALL removes duplicates and needs state to remember everything seen, so prefer UNION ALL in streaming jobs.

INSERT INTO: running the pipeline

In Flink, a continuous query becomes a job only when its result goes somewhere. INSERT INTO sink SELECT ... submits that job:

SQL
INSERT INTO page_counts
SELECT page, COUNT(*) AS views
FROM clicks
GROUP BY page;

This statement returns immediately with a job id; the job keeps running on the cluster. To run several inserts as one job that reads the source only once, use a statement set:

SQL
EXECUTE STATEMENT SET
BEGIN
  INSERT INTO page_counts SELECT page, COUNT(*) FROM clicks GROUP BY page;
  INSERT INTO audit_log    SELECT user_id, page, ts FROM clicks WHERE page LIKE '/admin%';
END;

In Java the equivalent is tEnv.createStatementSet().addInsertSql(...).addInsertSql(...).execute().

The SQL Client and handy statements

The SQL Client (./bin/sql-client.sh) is an interactive shell for sessions with a cluster. Useful statements:

Statement Purpose
SHOW TABLES; / DESCRIBE clicks; Inspect the catalog
SET 'execution.runtime-mode' = 'batch'; Switch between streaming and batch
SET 'table.exec.mini-batch.enabled' = 'true'; Turn on an aggregation optimization
EXPLAIN SELECT ...; Show the plan (look for state-heavy operators)
SHOW JOBS; / STOP JOB '<id>' WITH SAVEPOINT; Manage running jobs

Mini-batching buffers input rows for a short time and applies them to state in bulk, so a hot key writes state once per batch instead of once per row. It trades a little latency for a lot of throughput.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does INSERT INTO sink SELECT ... do in Flink?

    A streaming query has no end. INSERT INTO turns it into a long-running job and returns a job id.
  2. 2. Why is UNION ALL preferred over UNION in streaming SQL?

    Distinct needs memory of every row seen. UNION ALL has no state.
  3. 3. What does PRIMARY KEY ... NOT ENFORCED mean?

    Flink does not own the data, so it cannot enforce constraints. Sinks use the declared key for upserts.
  4. 4. Which setting reduces state writes for hot keys in aggregations?

    Mini-batch groups input rows and updates state once per batch per key.

Summary

  • Flink SQL is ANSI-style SQL over streams; tables are declared with CREATE TABLE ... WITH ('connector' = ...).
  • SELECT, WHERE, CASE, GROUP BY, HAVING, FILTER, CTEs and UNION ALL work as in a database; results update as data arrives.
  • Aggregations keep per-group state, so bound it with windows or idle-state TTL.
  • INSERT INTO starts the job; statement sets share a source between several inserts.
  • The SQL Client, EXPLAIN, SET and mini-batching are your everyday tools.

Next: Dynamic Tables and Changelogs