Hi everyone,
i’m new to flink and tried some queries with flink sql.
Currently I have a problem with the LAG function. I want to emit a new record
when the ORDERS_ID changes. To do this, I use the LAG function to detect
whether this has changed.
However, I noticed that every now and then I cannot access the ORDERS_ID of the
previous message. It seems to have to do with the proctime I use in the ORDER
BY with LAG. As soon as the proctime changes in the range of seconds, I cannot
access the last value and gives me NULL. Do any of you know what this could be?
Here is the query I use:
CREATE TABLE UNITS_DATA(
proctime AS PROCTIME()
, `IDENT` DOUBLE
, `STEPS_ID` DOUBLE
, `ORDERS_ID` DOUBLE
) WITH (
'connector' = 'kafka',
'topic' = 'UNITS_DATA',
'properties.bootstrap.servers' = 'http://myserver:9094',
'scan.startup.mode' = 'latest-offset',
'format' = 'avro-confluent',
'avro-confluent.url' = 'http://myserver:8080/apis/ccompat/v6/'
);
WITH current_and_previous as (
SELECT proctime
, ud.STEPS_ID as STEPS_ID
, ud.IDENT as UNITS_DATA_ID
, ud.ORDERS_ID as ORDERS_ID
, LAG(ud.ORDERS_ID, 1) OVER (PARTITION BY STEPS_ID
ORDER BY proctime) PREV_ORDERS_ID
FROM UNITS_DATA ud
WHERE STEPS_ID=64911
)
select *
from current_and_previous;
Thanks in advance and best regards
Zollner Elektronik AG
Manfred-Zollner-Str. 1, 93499 Zandt, GERMANY / Phone: +49 9944 201-0 / Fax: +49
9944 201-1314 / i...@zollner.de / www.zollner.de
Registered office of the company: Zandt / Registration court Regensburg HRB 8354
Managing Board: Ludwig Zollner (Spokesman of the Board) / Manfred Zollner jun.
/ Christian Zollner / Thomas Schreiner / Markus Aschenbrenner
Supervisory Board: Manfred Zollner sen. (Chairman)
Wichtiger Hinweis: Diese E-Mail enthält vertrauliche Informationen. Wenn Sie
diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den
Absender und vernichten Sie diese E-Mail. Die Anfertigung unerlaubter Kopien,
die Weitergabe der Information sowie die unbefugte Weitersendung der E-Mail ist
nicht gestattet. Weiterführend können Hinweise zur Verarbeitung Ihrer
personenbezogenen Daten (nach Art. 13 und 14 Datenschutzgrundverordnung -
DSGVO) unter dem folgenden Link abgerufen werden:
https://www.zollner.de/datenschutz-verarbeitungsverzeichnis-extern
Important note: This e-mail contains confidential information. If you received
this e-mail by mistake, please inform the sender immediately and destroy this
e-mail. The making of unauthorized copies, the passing on of the information
and the unauthorized forwarding of the e-mail is not permitted. Furhermore
information about the processing of your personal Information (according to
art. 13 and 14 general data protection regulation - GDPR) can be retrieved on
the page:
https://www.zollner-electronics.com/en/data-protection-processing-directory-external
