Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 37 ยท Table API and SQL

Table API First Steps

Start with the Flink Table API: create a TableEnvironment, define tables with connectors, write relational queries, and convert between Table and DataStream.

The DataStream API gives you full control: you write functions, manage state and decide how events flow. Most analytical jobs, however, are really relational: filter rows, project columns, group and aggregate, join. For those, Flink provides relational APIs on top of the same runtime: the Table API (a fluent, type-checked Java/Python/Scala API) and Flink SQL. They share one planner and one set of operators, so you can mix them freely.

This lesson introduces the Table API. The next one covers SQL. Flink code is shown read-only because the site cannot run a cluster; the runnable examples are Java stand-ins that model a tiny relational pipeline. It builds on State Backends and Savepoints.

One engine, three doors

DataStream Most control, you manage state.
Table API Fluent relational code.
Flink SQL Plain SQL text.

Same planner, same runtime.

1 / 2

The TableEnvironment

Everything starts with a TableEnvironment. It holds the catalog of tables, the configuration and the entry points to run statements.

Java
EnvironmentSettings settings = EnvironmentSettings.newInstance()
    .inStreamingMode()                // or inBatchMode()
    .build();
TableEnvironment tEnv = TableEnvironment.create(settings);

The same program works in streaming mode (unbounded input, results update continuously) or batch mode (bounded input, one final result). If you also need DataStream code in the same program, create a StreamTableEnvironment from a StreamExecutionEnvironment instead, which adds the bridge methods shown below.

Defining a table

A table is a name, a schema and a connector that says where rows come from or go. The built-in datagen connector produces random rows and print writes rows to the log, which makes them ideal for learning.

Java
tEnv.executeSql("""
    CREATE TABLE orders (
      order_id BIGINT,
      customer STRING,
      amount   DECIMAL(10, 2),
      order_ts TIMESTAMP(3),
      WATERMARK FOR order_ts AS order_ts - INTERVAL '5' SECOND
    ) WITH (
      'connector' = 'datagen',
      'rows-per-second' = '5'
    )
    """);

tEnv.executeSql("""
    CREATE TABLE big_spenders (
      customer STRING,
      total    DECIMAL(10, 2)
    ) WITH ('connector' = 'print')
    """);

You can also create tables from a Schema builder in Java, or register an existing DataStream as a table. The WATERMARK FOR clause declares the event-time attribute and how much lateness to tolerate; it is the same watermark idea as in the time and windows chapter.

Writing a query with the Table API

Java
Table orders = tEnv.from("orders");

Table totals = orders
    .filter($("amount").isGreater(10))
    .groupBy($("customer"))
    .select($("customer"), $("amount").sum().as("total"));

totals.executeInsert("big_spenders");     // submits the job

$("name") refers to a column. Each method returns a new immutable Table, so nothing runs until you call a terminal method such as executeInsert, execute() (which gives you an iterator of results) or print. The planner turns the whole chain into one optimized streaming job.

Let us rebuild that experience with a miniature Table class in plain Java. The stand-in has the same style: filter, group, aggregate, select, chained immutably.

Java Stand-in: a tiny fluent Table API

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The original orders table is unchanged; each step returned a new one, exactly like the real API.

Switching between Table and DataStream

You can move in both directions inside one program using a StreamTableEnvironment:

Java
StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);

DataStream<Order> stream = ...;
Table table = tEnv.fromDataStream(stream);            // DataStream -> Table

Table result = table.groupBy($("customer")).select($("customer"), $("amount").sum());

DataStream<Row> out = tEnv.toDataStream(result);      // append-only Table -> DataStream
DataStream<Row> changes = tEnv.toChangelogStream(result);   // updating Table -> changelog stream

toDataStream only works when the result never updates previous rows (it is append-only). A grouped aggregation updates existing rows, so you use toChangelogStream, which tags each row with a change kind. The lesson Dynamic Tables and Changelogs explains why.

A good rule of thumb: use Table API or SQL for filtering, projecting, aggregating, joining and windowing, and drop to DataStream for custom state machines, timers, side outputs or complicated per-event logic. Mixing the two in one job is normal.

Expressions and functions

The Table API has a rich expression language: arithmetic, isNull(), cast(DataTypes.INT()), string functions (upperCase(), substring(...)), time functions and conditionals such as ifThenElse. For logic the built-ins lack, write a user-defined function (UDF):

Java
public static class MaskEmail extends ScalarFunction {
    public String eval(String email) {
        int at = email.indexOf('@');
        return at <= 1 ? email : email.charAt(0) + "***" + email.substring(at);
    }
}

tEnv.createTemporarySystemFunction("MASK_EMAIL", MaskEmail.class);
// then: table.select(call("MASK_EMAIL", $("email")))   or in SQL: SELECT MASK_EMAIL(email) ...

The same function is usable from SQL. Besides scalar functions, there are table functions (one row in, many rows out), aggregate functions and table aggregate functions.

Java Stand-in: a scalar UDF applied to a column

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a Table API chain like filter().groupBy().select() do when it is built?

    Each method returns a new Table describing the plan. Only a terminal call such as executeInsert or execute submits a job.
  2. 2. Why does toDataStream fail on a grouped aggregation in streaming mode?

    A running aggregate must correct previous results. toChangelogStream carries those corrections as insert, update and delete rows.
  3. 3. What is the role of a connector in CREATE TABLE ... WITH (...)?

    A table is a schema plus a connector configuration; the connector connects the logical table to an external system.
  4. 4. When should you drop from the Table API to DataStream?

    Relational operators cover most analytics. Custom state machines, timers and side outputs are the DataStream API's strengths.

Summary

  • Flink offers relational APIs (Table API and SQL) on the same runtime as DataStream, with one optimizer.
  • A TableEnvironment holds the catalog; choose streaming or batch mode.
  • Tables are schema plus connector; WATERMARK FOR declares event time.
  • Table API calls build immutable logical plans, executed with executeInsert or execute.
  • Convert with fromDataStream, toDataStream (append-only) and toChangelogStream (updating results); use UDFs for custom scalar logic.

Next: Flink SQL Queries