INSERT INTO
概述
INSERT INTO语句是Flink SQL中核心的数据写入语句,用于将SELECT查询的结果或显式指定的值写入目标表(Sink表)。AI DataLake支持INSERT INTO,即向目标表追加数据,不覆盖已有数据。这是最常用的写入模式,适用于流式和批处理场景。
此外,AI DataLake支持通过STATEMENT SET语法将多条INSERT语句合并为一个Flink作业执行,使多条写入操作共享同一个DAG(有向无环图),从而降低TaskManager资源开销,适用于多Sink分流场景。
适用场景
- 实时数据入湖:将Kafka等消息队列中的实时数据通过INSERT INTO写入Paimon或Hive等存储,构建实时数仓。
- 多Sink分流:同一数据源经ETL处理后需要写入多个目标表(如明细表、聚合表同时写入),使用STATEMENT SET合并为一个作业,减少资源消耗。
- 动态参数调优:通过Table Hints在INSERT语句中动态调整连接器参数,无需修改表DDL即可适应不同运行环境。
约束与限制
- STATEMENT SET中的多条INSERT语句作为一个整体提交,无法单独控制某一条INSERT的启停。
- INSERT语句中SELECT子句的列数量和类型必须与目标表的列定义匹配,或通过显式column_list指定对应列。
- 分区写入时,动态分区列必须出现在SELECT子句的最后,且与PARTITION子句中声明的顺序一致。
- Table Hints(/*+ OPTIONS(...) */)仅对当前INSERT语句中的表实例生效,不会修改表在Catalog中的持久化定义。
语法结构
INSERT INTO单Sink写入
INSERT INTO [catalog_name.][db_name.]table_name [PARTITION part_spec] [column_list] select_statement | VALUES (value [, value ...]) [, (value [, value ...]) ...]; part_spec ::= (key1=value1, key2=value2, ...) column_list ::= (col1 [, col2, ...])
INSERT INTO多Sink写入
EXECUTE STATEMENT SET BEGIN INSERT INTO sink_table1 select_statement1; INSERT INTO sink_table2 select_statement2; [...] END;
EXECUTE STATEMENT SET与END之间的所有INSERT语句将被合并为一个Flink作业。多条INSERT共享Source算子,合并到同一DAG中执行,有效减少TaskManager资源占用。
Table Hints动态传参
INSERT INTO table_name /*+ OPTIONS('key1'='value1', 'key2'='value2') */
select_statement; 使用示例
示例1:INSERT INTO单Sink写入
使用DataGen连接器生成模拟传感器数据,通过INSERT INTO写入Print连接器打印输出。
-- 创建传感器数据源表 CREATE TABLE sensor_source ( sensor_id INT, temperature DOUBLE, humidity DOUBLE, record_time TIMESTAMP(3), WATERMARK FOR record_time AS record_time - INTERVAL '5' SECOND ) WITH ( 'connector' = 'datagen', 'rows-per-second' = '10', 'fields.sensor_id.kind' = 'random', 'fields.sensor_id.min' = '1', 'fields.sensor_id.max' = '100', 'fields.temperature.min' = '15.0', 'fields.temperature.max' = '40.0', 'fields.humidity.min' = '30.0', 'fields.humidity.max' = '90.0' ); -- 创建传感器数据结果表 CREATE TABLE sensor_sink ( sensor_id INT, temperature DOUBLE, humidity DOUBLE ) WITH ( 'connector' = 'print', 'print-identifier' = 'sensor_data' ); -- 提交作业:筛选温度超过30度的传感器数据 INSERT INTO sensor_sink SELECT sensor_id, temperature, humidity FROM sensor_source WHERE temperature > 30;
示例2:INSERT INTO多Sink写入(STATEMENT SET)
使用DataGen连接器生成订单数据,通过STATEMENT SET将数据分别写入明细表和聚合统计表,两条INSERT在同一个Flink作业中执行。
-- 创建订单数据源表
CREATE TABLE order_source ( order_id BIGINT, customer_id BIGINT, product_id BIGINT, amount DECIMAL(10, 2), order_time TIMESTAMP(3), WATERMARK FOR order_time AS order_time - INTERVAL '5' SECOND ) WITH ( 'connector' = 'datagen', 'rows-per-second' = '100', 'fields.order_id.kind' = 'sequence', 'fields.order_id.start' = '1', 'fields.order_id.end' = '1000000', 'fields.customer_id.kind' = 'random', 'fields.customer_id.min' = '1', 'fields.customer_id.max' = '5000', 'fields.product_id.kind' = 'random', 'fields.product_id.min' = '1', 'fields.product_id.max' = '200', 'fields.amount.min' = '10.00', 'fields.amount.max' = '1000.00' ); -- 创建订单明细结果表 CREATE TABLE order_detail_sink ( order_id BIGINT, customer_id BIGINT, amount DECIMAL(10, 2) ) WITH ( 'connector' = 'print', 'print-identifier' = 'order_detail' ); -- 创建客户聚合结果表 CREATE TABLE customer_agg_sink ( customer_id BIGINT, order_count BIGINT, total_amount DECIMAL(10, 2) ) WITH ( 'connector' = 'print', 'print-identifier' = 'customer_agg' ); -- 提交作业:多Sink写入,两条INSERT共享同一DAG EXECUTE STATEMENT SET BEGIN INSERT INTO order_detail_sink SELECT order_id, customer_id, amount FROM order_source; INSERT INTO customer_agg_sink SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM order_source GROUP BY customer_id; END;