Examples with TableResult org.apache.flink.table.api.TableResult used on opensource projects

Example 21 with TableResult

use of org.apache.flink.table.api.TableResult in project flink by apache.

the class KafkaChangelogTableITCase method testKafkaCanalChangelogSource.

@Test
public void testKafkaCanalChangelogSource() throws Exception {
    final String topic = "changelog_canal";
    createTestTopic(topic, 1, 1);
    // configure time zone of  the Canal Json metadata "ingestion-timestamp"
    tEnv.getConfig().setLocalTimeZone(ZoneId.of("UTC"));
    // enables MiniBatch processing to verify MiniBatch + FLIP-95, see FLINK-18769
    Configuration tableConf = tEnv.getConfig().getConfiguration();
    tableConf.setString("table.exec.mini-batch.enabled", "true");
    tableConf.setString("table.exec.mini-batch.allow-latency", "1s");
    tableConf.setString("table.exec.mini-batch.size", "5000");
    tableConf.setString("table.optimizer.agg-phase-strategy", "TWO_PHASE");
    // ---------- Write the Canal json into Kafka -------------------
    List<String> lines = readLines("canal-data.txt");
    try {
        writeRecordsToKafka(topic, lines);
    } catch (Exception e) {
        throw new Exception("Failed to write canal data to Kafka.", e);
    }
    // ---------- Produce an event time stream into Kafka -------------------
    String bootstraps = getBootstrapServers();
    String sourceDDL = String.format("CREATE TABLE canal_source (" + // test format metadata
    " origin_database STRING METADATA FROM 'value.database' VIRTUAL," + " origin_table STRING METADATA FROM 'value.table' VIRTUAL," + " origin_sql_type MAP<STRING, INT> METADATA FROM 'value.sql-type' VIRTUAL," + " origin_pk_names ARRAY<STRING> METADATA FROM 'value.pk-names' VIRTUAL," + " origin_ts TIMESTAMP(3) METADATA FROM 'value.ingestion-timestamp' VIRTUAL," + " origin_es TIMESTAMP(3) METADATA FROM 'value.event-timestamp' VIRTUAL," + " id INT NOT NULL," + " name STRING," + " description STRING," + " weight DECIMAL(10,3)," + // test connector metadata
    " origin_topic STRING METADATA FROM 'topic' VIRTUAL," + // unused
    " origin_partition STRING METADATA FROM 'partition' VIRTUAL," + " WATERMARK FOR origin_es AS origin_es - INTERVAL '5' SECOND" + ") WITH (" + " 'connector' = 'kafka'," + " 'topic' = '%s'," + " 'properties.bootstrap.servers' = '%s'," + " 'scan.startup.mode' = 'earliest-offset'," + " 'value.format' = 'canal-json'" + ")", topic, bootstraps);
    String sinkDDL = "CREATE TABLE sink (" + " origin_topic STRING," + " origin_database STRING," + " origin_table STRING," + " origin_sql_type MAP<STRING, INT>," + " origin_pk_names ARRAY<STRING>," + " origin_ts TIMESTAMP(3)," + " origin_es TIMESTAMP(3)," + " name STRING," + " PRIMARY KEY (name) NOT ENFORCED" + ") WITH (" + " 'connector' = 'values'," + " 'sink-insert-only' = 'false'" + ")";
    tEnv.executeSql(sourceDDL);
    tEnv.executeSql(sinkDDL);
    TableResult tableResult = tEnv.executeSql("INSERT INTO sink " + "SELECT origin_topic, origin_database, origin_table, origin_sql_type, " + "origin_pk_names, origin_ts, origin_es, name " + "FROM canal_source");
    /*
         * Canal captures change data on the `products` table:
         *
         * <pre>
         * CREATE TABLE products (
         *  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
         *  name VARCHAR(255),
         *  description VARCHAR(512),
         *  weight FLOAT
         * );
         * ALTER TABLE products AUTO_INCREMENT = 101;
         *
         * INSERT INTO products
         * VALUES (default,"scooter","Small 2-wheel scooter",3.14),
         *        (default,"car battery","12V car battery",8.1),
         *        (default,"12-pack drill bits","12-pack of drill bits with sizes ranging from #40 to #3",0.8),
         *        (default,"hammer","12oz carpenter's hammer",0.75),
         *        (default,"hammer","14oz carpenter's hammer",0.875),
         *        (default,"hammer","16oz carpenter's hammer",1.0),
         *        (default,"rocks","box of assorted rocks",5.3),
         *        (default,"jacket","water resistent black wind breaker",0.1),
         *        (default,"spare tire","24 inch spare tire",22.2);
         * UPDATE products SET description='18oz carpenter hammer' WHERE id=106;
         * UPDATE products SET weight='5.1' WHERE id=107;
         * INSERT INTO products VALUES (default,"jacket","water resistent white wind breaker",0.2);
         * INSERT INTO products VALUES (default,"scooter","Big 2-wheel scooter ",5.18);
         * UPDATE products SET description='new water resistent white wind breaker', weight='0.5' WHERE id=110;
         * UPDATE products SET weight='5.17' WHERE id=111;
         * DELETE FROM products WHERE id=111;
         *
         * > SELECT * FROM products;
         * +-----+--------------------+---------------------------------------------------------+--------+
         * | id  | name               | description                                             | weight |
         * +-----+--------------------+---------------------------------------------------------+--------+
         * | 101 | scooter            | Small 2-wheel scooter                                   |   3.14 |
         * | 102 | car battery        | 12V car battery                                         |    8.1 |
         * | 103 | 12-pack drill bits | 12-pack of drill bits with sizes ranging from #40 to #3 |    0.8 |
         * | 104 | hammer             | 12oz carpenter's hammer                                 |   0.75 |
         * | 105 | hammer             | 14oz carpenter's hammer                                 |  0.875 |
         * | 106 | hammer             | 18oz carpenter hammer                                   |      1 |
         * | 107 | rocks              | box of assorted rocks                                   |    5.1 |
         * | 108 | jacket             | water resistent black wind breaker                      |    0.1 |
         * | 109 | spare tire         | 24 inch spare tire                                      |   22.2 |
         * | 110 | jacket             | new water resistent white wind breaker                  |    0.5 |
         * +-----+--------------------+---------------------------------------------------------+--------+
         * </pre>
         */
    List<String> expected = Arrays.asList("+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:38:35.477, 2020-05-13T12:38:35, 12-pack drill bits]", "+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:38:35.477, 2020-05-13T12:38:35, spare tire]", "+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:39:06.301, 2020-05-13T12:39:06, hammer]", "+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:39:09.489, 2020-05-13T12:39:09, rocks]", "+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:39:18.230, 2020-05-13T12:39:18, jacket]", "+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:42:33.939, 2020-05-13T12:42:33, car battery]", "+I[changelog_canal, inventory, products2, {name=12, weight=7, description=12, id=4}, [id], 2020-05-13T12:42:33.939, 2020-05-13T12:42:33, scooter]");
    waitingExpectedResults("sink", expected, Duration.ofSeconds(10));
    // ------------- cleanup -------------------
    // stop the job
    tableResult.getJobClient().get().cancel().get();
    deleteTestTopic(topic);
}

Example 22 with TableResult

use of org.apache.flink.table.api.TableResult in project flink by apache.

the class KafkaChangelogTableITCase method testKafkaMaxwellChangelogSource.

@Test
public void testKafkaMaxwellChangelogSource() throws Exception {
    final String topic = "changelog_maxwell";
    createTestTopic(topic, 1, 1);
    // configure time zone of  the Maxwell Json metadata "ingestion-timestamp"
    tEnv.getConfig().setLocalTimeZone(ZoneId.of("UTC"));
    // enables MiniBatch processing to verify MiniBatch + FLIP-95, see FLINK-18769
    Configuration tableConf = tEnv.getConfig().getConfiguration();
    tableConf.setString("table.exec.mini-batch.enabled", "true");
    tableConf.setString("table.exec.mini-batch.allow-latency", "1s");
    tableConf.setString("table.exec.mini-batch.size", "5000");
    tableConf.setString("table.optimizer.agg-phase-strategy", "TWO_PHASE");
    // ---------- Write the Maxwell json into Kafka -------------------
    List<String> lines = readLines("maxwell-data.txt");
    try {
        writeRecordsToKafka(topic, lines);
    } catch (Exception e) {
        throw new Exception("Failed to write maxwell data to Kafka.", e);
    }
    // ---------- Produce an event time stream into Kafka -------------------
    String bootstraps = getBootstrapServers();
    String sourceDDL = String.format("CREATE TABLE maxwell_source (" + // test format metadata
    " origin_database STRING METADATA FROM 'value.database' VIRTUAL," + " origin_table STRING METADATA FROM 'value.table' VIRTUAL," + " origin_primary_key_columns ARRAY<STRING> METADATA FROM 'value.primary-key-columns' VIRTUAL," + " origin_ts TIMESTAMP(3) METADATA FROM 'value.ingestion-timestamp' VIRTUAL," + " id INT NOT NULL," + " name STRING," + " description STRING," + " weight DECIMAL(10,3)," + // test connector metadata
    " origin_topic STRING METADATA FROM 'topic' VIRTUAL," + // unused
    " origin_partition STRING METADATA FROM 'partition' VIRTUAL" + ") WITH (" + " 'connector' = 'kafka'," + " 'topic' = '%s'," + " 'properties.bootstrap.servers' = '%s'," + " 'scan.startup.mode' = 'earliest-offset'," + " 'value.format' = 'maxwell-json'" + ")", topic, bootstraps);
    String sinkDDL = "CREATE TABLE sink (" + " origin_topic STRING," + " origin_database STRING," + " origin_table STRING," + " origin_primary_key_columns ARRAY<STRING>," + " origin_ts TIMESTAMP(3)," + " name STRING," + " PRIMARY KEY (name) NOT ENFORCED" + ") WITH (" + " 'connector' = 'values'," + " 'sink-insert-only' = 'false'" + ")";
    tEnv.executeSql(sourceDDL);
    tEnv.executeSql(sinkDDL);
    TableResult tableResult = tEnv.executeSql("INSERT INTO sink " + "SELECT origin_topic, origin_database, origin_table, origin_primary_key_columns, " + "origin_ts, name " + "FROM maxwell_source");
    /*
         * Maxwell captures change data on the `products` table:
         *
         * <pre>
         * CREATE TABLE products (
         *  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
         *  name VARCHAR(255),
         *  description VARCHAR(512),
         *  weight FLOAT
         * );
         * ALTER TABLE products AUTO_INCREMENT = 101;
         *
         * INSERT INTO products
         * VALUES (default,"scooter","Small 2-wheel scooter",3.14),
         *        (default,"car battery","12V car battery",8.1),
         *        (default,"12-pack drill bits","12-pack of drill bits with sizes ranging from #40 to #3",0.8),
         *        (default,"hammer","12oz carpenter's hammer",0.75),
         *        (default,"hammer","14oz carpenter's hammer",0.875),
         *        (default,"hammer","16oz carpenter's hammer",1.0),
         *        (default,"rocks","box of assorted rocks",5.3),
         *        (default,"jacket","water resistent black wind breaker",0.1),
         *        (default,"spare tire","24 inch spare tire",22.2);
         * UPDATE products SET description='18oz carpenter hammer' WHERE id=106;
         * UPDATE products SET weight='5.1' WHERE id=107;
         * INSERT INTO products VALUES (default,"jacket","water resistent white wind breaker",0.2);
         * INSERT INTO products VALUES (default,"scooter","Big 2-wheel scooter ",5.18);
         * UPDATE products SET description='new water resistent white wind breaker', weight='0.5' WHERE id=110;
         * UPDATE products SET weight='5.17' WHERE id=111;
         * DELETE FROM products WHERE id=111;
         *
         * > SELECT * FROM products;
         * +-----+--------------------+---------------------------------------------------------+--------+
         * | id  | name               | description                                             | weight |
         * +-----+--------------------+---------------------------------------------------------+--------+
         * | 101 | scooter            | Small 2-wheel scooter                                   |   3.14 |
         * | 102 | car battery        | 12V car battery                                         |    8.1 |
         * | 103 | 12-pack drill bits | 12-pack of drill bits with sizes ranging from #40 to #3 |    0.8 |
         * | 104 | hammer             | 12oz carpenter's hammer                                 |   0.75 |
         * | 105 | hammer             | 14oz carpenter's hammer                                 |  0.875 |
         * | 106 | hammer             | 18oz carpenter hammer                                   |      1 |
         * | 107 | rocks              | box of assorted rocks                                   |    5.1 |
         * | 108 | jacket             | water resistent black wind breaker                      |    0.1 |
         * | 109 | spare tire         | 24 inch spare tire                                      |   22.2 |
         * | 110 | jacket             | new water resistent white wind breaker                  |    0.5 |
         * +-----+--------------------+---------------------------------------------------------+--------+
         * </pre>
         */
    List<String> expected = Arrays.asList("+I[changelog_maxwell, test, product, null, 2020-08-06T03:34:43, 12-pack drill bits]", "+I[changelog_maxwell, test, product, null, 2020-08-06T03:34:43, spare tire]", "+I[changelog_maxwell, test, product, null, 2020-08-06T03:34:53, hammer]", "+I[changelog_maxwell, test, product, null, 2020-08-06T03:34:57, rocks]", "+I[changelog_maxwell, test, product, null, 2020-08-06T03:35:06, jacket]", "+I[changelog_maxwell, test, product, null, 2020-08-06T03:35:28, car battery]", "+I[changelog_maxwell, test, product, null, 2020-08-06T03:35:28, scooter]");
    waitingExpectedResults("sink", expected, Duration.ofSeconds(10));
    // ------------- cleanup -------------------
    // stop the job
    tableResult.getJobClient().get().cancel().get();
    deleteTestTopic(topic);
}

Example 23 with TableResult

use of org.apache.flink.table.api.TableResult in project flink by apache.

the class KafkaTableITCase method testStartFromGroupOffsetsWithNoneResetStrategy.

private void testStartFromGroupOffsetsWithNoneResetStrategy() throws ExecutionException, InterruptedException {
    // we always use a different topic name for each parameterized topic,
    // in order to make sure the topic can be created.
    final String resetStrategy = "none";
    final String tableName = resetStrategy + "Table";
    final String topic = "groupOffset_" + format;
    String groupId = resetStrategy + (new Random()).nextInt();
    TableResult tableResult = null;
    try {
        tableResult = startFromGroupOffset(tableName, topic, groupId, resetStrategy, "MySink");
        tableResult.await();
    } finally {
        // ------------- cleanup -------------------
        if (tableResult != null) {
            tableResult.getJobClient().ifPresent(JobClient::cancel);
        }
        deleteTestTopic(topic);
    }
}

Example 24 with TableResult

use of org.apache.flink.table.api.TableResult in project flink by apache.

the class KafkaTableITCase method testStartFromGroupOffsets.

private void testStartFromGroupOffsets(String resetStrategy) throws Exception {
    // we always use a different topic name for each parameterized topic,
    // in order to make sure the topic can be created.
    final String tableName = "Table" + format + resetStrategy;
    final String topic = "groupOffset_" + format + resetStrategy + ThreadLocalRandom.current().nextLong();
    String groupId = format + resetStrategy;
    String sinkName = "mySink" + format + resetStrategy;
    List<String> expected = Arrays.asList("+I[0, 0]", "+I[0, 1]", "+I[0, 2]", "+I[1, 3]", "+I[1, 4]", "+I[1, 5]");
    TableResult tableResult = null;
    try {
        tableResult = startFromGroupOffset(tableName, topic, groupId, resetStrategy, sinkName);
        if ("latest".equals(resetStrategy)) {
            expected = appendNewData(topic, tableName, groupId, expected.size());
        }
        KafkaTableTestUtils.waitingExpectedResults(sinkName, expected, Duration.ofSeconds(15));
    } finally {
        // ------------- cleanup -------------------
        if (tableResult != null) {
            tableResult.getJobClient().ifPresent(JobClient::cancel);
        }
        deleteTestTopic(topic);
    }
}

Example 25 with TableResult

use of org.apache.flink.table.api.TableResult in project flink by apache.

the class KafkaTableITCase method testPerPartitionWatermarkWithIdleSource.

@Test
public void testPerPartitionWatermarkWithIdleSource() throws Exception {
    // we always use a different topic name for each parameterized topic,
    // in order to make sure the topic can be created.
    final String topic = "idle_partition_watermark_topic_" + format;
    createTestTopic(topic, 4, 1);
    // ---------- Produce an event time stream into Kafka -------------------
    String groupId = getStandardProps().getProperty("group.id");
    String bootstraps = getBootstrapServers();
    tEnv.getConfig().getConfiguration().set(TABLE_EXEC_SOURCE_IDLE_TIMEOUT, Duration.ofMillis(100));
    final String createTable = String.format("CREATE TABLE kafka (\n" + "  `partition_id` INT,\n" + "  `value` INT,\n" + "  `timestamp` TIMESTAMP(3),\n" + "  WATERMARK FOR `timestamp` AS `timestamp`\n" + ") WITH (\n" + "  'connector' = 'kafka',\n" + "  'topic' = '%s',\n" + "  'properties.bootstrap.servers' = '%s',\n" + "  'properties.group.id' = '%s',\n" + "  'scan.startup.mode' = 'earliest-offset',\n" + "  'sink.partitioner' = '%s',\n" + "  'format' = '%s'\n" + ")", topic, bootstraps, groupId, TestPartitioner.class.getName(), format);
    tEnv.executeSql(createTable);
    // Only two partitions have elements and others are idle.
    // When idle timer triggers, the WatermarkOutputMultiplexer will use the minimum watermark
    // among active partitions as the output watermark.
    // Therefore, we need to make sure the watermark in the each partition is large enough to
    // trigger the window.
    String initialValues = "INSERT INTO kafka\n" + "VALUES\n" + " (0, 0, TIMESTAMP '2020-03-08 13:12:11.123'),\n" + " (0, 1, TIMESTAMP '2020-03-08 13:15:12.223'),\n" + " (0, 2, TIMESTAMP '2020-03-08 16:12:13.323'),\n" + " (1, 3, TIMESTAMP '2020-03-08 13:13:11.123'),\n" + " (1, 4, TIMESTAMP '2020-03-08 13:19:11.133'),\n" + " (1, 5, TIMESTAMP '2020-03-08 16:13:11.143')\n";
    tEnv.executeSql(initialValues).await();
    // ---------- Consume stream from Kafka -------------------
    env.setParallelism(1);
    String createSink = "CREATE TABLE MySink(\n" + "  `id` INT,\n" + "  `cnt` BIGINT\n" + ") WITH (\n" + "  'connector' = 'values'\n" + ")";
    tEnv.executeSql(createSink);
    TableResult tableResult = tEnv.executeSql("INSERT INTO MySink\n" + "SELECT `partition_id` as `id`, COUNT(`value`) as `cnt`\n" + "FROM kafka\n" + "GROUP BY `partition_id`, TUMBLE(`timestamp`, INTERVAL '1' HOUR) ");
    final List<String> expected = Arrays.asList("+I[0, 2]", "+I[1, 2]");
    KafkaTableTestUtils.waitingExpectedResults("MySink", expected, Duration.ofSeconds(5));
    // ------------- cleanup -------------------
    tableResult.getJobClient().ifPresent(JobClient::cancel);
    deleteTestTopic(topic);
}