George Leonard
07/09/2024, 1:47 PMD. Draco O'Brien
07/10/2024, 8:43 AMD. Draco O'Brien
07/10/2024, 8:44 AMD. Draco O'Brien
07/10/2024, 8:44 AMGeorge Leonard
07/10/2024, 9:30 AMCREATE TABLE avro_salescompleted_x (
INVOICENUMBER STRING,
SALEDATETIME STRING,
SALETIMESTAMP STRING,
TERMINALPOINT STRING,
NETT DOUBLE,
VAT DOUBLE,
TOTAL DOUBLE,
STORE row<ID STRING, NAME STRING>,
CLERK row<ID STRING, NAME STRING>,
BASKETITEMS array<row<ID STRING, NAME STRING, BRAND STRING, CATEGORY STRING, PRICE DOUBLE, QUANTITY INT>>,
FINTRANSACTIONID STRING,
PAYDATETIME STRING,
PAYTIMESTAMP STRING,
PAID DOUBLE,
SALESTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(SALETIMESTAMP AS BIGINT) / 1000)),
PAYTIMESTAMP_WM AS TO_TIMESTAMP(FROM_UNIXTIME(CAST(PAYTIMESTAMP AS BIGINT) / 1000)),
WATERMARK FOR SALESTIMESTAMP_WM AS SALESTIMESTAMP_WM
) WITH (
'connector' = 'kafka',
'topic' = 'avro_salescompleted_x',
'properties.bootstrap.servers' = 'broker:29092',
'properties.group.id' = 'testGroup',
'value.format' = 'avro-confluent',
'value.avro-confluent.url' = 'http://schema-registry:8081',
'value.fields-include' = 'ALL'
);
Insert
Insert into avro_salescompleted_x
SELECT
b.INVOICENUMBER,
b.SALEDATETIME,
b.SALETIMESTAMP,
b.TERMINALPOINT,
b.NETT,
b.VAT,
b.TOTAL,
b.STORE,
b.CLERK,
b.BASKETITEMS,
a.FINTRANSACTIONID,
a.PAYDATETIME,
a.PAYTIMESTAMP,
a.PAID
FROM
avro_salespayments_x a,
avro_salesbaskets_x b
WHERE a.INVOICENUMBER = b.INVOICENUMBER
AND PAYTIMESTAMP_WM > SALESTIMESTAMP_WM
AND SALESTIMESTAMP_WM > (SALESTIMESTAMP_WM - INTERVAL '1' HOUR);
Errors into
[ERROR] Could not execute SQL statement. Reason:
org.apache.flink.table.api.TableException: Table sink 'default_catalog.default_database.avro_salescompleted_x' doesn't support consuming update and delete changes which is produced by node Join(joinType=[InnerJoin], where=[((INVOICENUMBER = INVOICENUMBER0) AND (PAYTIMESTAMP_WM > SALESTIMESTAMP_WM))], select=[INVOICENUMBER, FINTRANSACTIONID, PAYDATETIME, PAYTIMESTAMP, PAID, PAYTIMESTAMP_WM, INVOICENUMBER0, SALEDATETIME, SALETIMESTAMP, TERMINALPOINT, NETT, VAT, TOTAL, STORE, CLERK, BASKETITEMS, SALESTIMESTAMP_WM], leftInputSpec=[JoinKeyContainsUniqueKey], rightInputSpec=[JoinKeyContainsUniqueKey])George Leonard
07/10/2024, 9:31 AMGeorge Leonard
07/10/2024, 9:32 AMD. Draco O'Brien
07/10/2024, 9:43 AMGeorge Leonard
07/10/2024, 9:53 AMD. Draco O'Brien
07/10/2024, 10:06 AMD. Draco O'Brien
07/10/2024, 10:11 AMimport org.apache.flink.api.common.functions.MapFunction;
import org.apache.flink.api.java.typeutils.RowTypeInfo;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.streaming.connectors.kafka.FlinkKafkaProducer;
import org.apache.flink.streaming.connectors.kafka.KafkaSerializationSchema;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.types.Row;
public class AppendOnlyToKafka {
public static void main(String[] args) throws Exception {
final StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);
// assume you already have DataStreams for avro_salespayments_x and avro_salesbaskets_x
// assume these are already defined and named as salesPaymentsStream and salesBasketsStream
// Perform the join operation using DataStream API to have more control
DataStream<Row> joinedStream = salesPaymentsStream
.keyBy(row -> row.getFieldAsString("INVOICENUMBER"))
.intervalJoin(salesBasketsStream.keyBy(row -> row.getFieldAsString("INVOICENUMBER")))
.between(Time.minutes(-1), Time.minutes(0))
// Define the join function to merge rows as needed
.process(new MyJoinFunction());
// define Kafka Serialization Schema for output
KafkaSerializationSchema<Row> kafkaSerializationSchema = new KafkaSerializationSchema<Row>() {
@Override
public ProducerRecord<byte[], byte[]> serialize(Row element, @Nullable Long timestamp) {
// Convert Row to byte[] as needed for Kafka message value
byte[] value = ...; // Implement serialization logic
return new ProducerRecord<>("avro_salescompleted_x", null, null, value);
}
@Override
public boolean equals(Object obj) {
return obj instanceof KafkaSerializationSchema;
}
@Override
public int hashCode() {
return KafkaSerializationSchema.class.getName().hashCode();
}
};
// configure FlinkKafkaProducer to send data to Kafka
FlinkKafkaProducer<Row> kafkaProducer = new FlinkKafkaProducer<>(
"avro_salescompleted_x",
kafkaSerializationSchema,
// Properties for Kafka connection
props,
// Semantic to AT_LEAST_ONCE or EXACTLY_ONCE depending on your requirement
FlinkKafkaProducer.Semantic.AT_LEAST_ONCE);
// add the producer to the DataStream to send data to Kafka
joinedStream.addSink(kafkaProducer);
env.execute("Append Only Data to Kafka");
}
// define your custom join function if needed
public static class MyJoinFunction extends ProcessJoinFunction<Row, Row, Row> {
@Override
public void processElement1(Row left, Context ctx, Collector<Row> out) throws Exception {
// impl your join logic
}
@Override
public void processElement2(Row right, Context ctx, Collector<Row> out) throws Exception {
// impl your join logic
}
}
}D. Draco O'Brien
07/10/2024, 10:13 AMGeorge Leonard
07/10/2024, 10:28 AM