Flink Streaming SQL 2018 Part II

Flink Streaming SQL 2018 Part II
展开查看详情

1. SELECT * FROM RatesHistory; ෸ᳵ ᨵ૰ ࿤ሲ time currency rate 09:00 USD 102 09:00 Euro 114 09:00 Yen 1 10:45 Euro 116 11:15 Euro 119 11:49 USD 99 ... ... ...

2. RatesHistory ෸ᳵ ᨵ૰ ࿤ሲ TemporalTableFunction rates = time currency rate ratesHistory 09:00 USD 102 .createTemporalTableFunction( 09:00 Euro 114 "time", "currency"); 09:00 Yen 1 10:45 Euro 116 11:15 Euro 119 tableEnv.registerFunction("Rates", rates); 11:49 USD 99 ... ... ...

3. RatesHistory ෸ᳵ ᨵ૰ ࿤ሲ time currency rate SELECT * FROM Rates('10:15'); 09:00 USD 102 09:00 Euro 114 time currency rate 09:00 Yen 1 09:00 USD 102 10:45 Euro 116 09:00 Euro 114 11:15 Euro 119 09:00 Yen 1 11:49 USD 99 ... ... ...

4. RatesHistory ෸ᳵ ᨵ૰ time currency rate SELECT * FROM Rates('11:50’); 09:00 USD 102 09:00 Euro 114 time currency rate 09:00 Yen 1 11:49 USD 99 10:45 Euro 116 11:15 Euro 119 11:15 Euro 119 09:00 Yen 1 11:49 USD 99 ... ... ...

5. RatesHistory ෸ᳵ ᨵ૰ ࿤ሲ Orders time currency rate ෸ᳵ ᨵ૰ ᰁ 09:00 USD 102 time currency amount 09:00 Euro 114 10:15 Euro 2 09:00 Yen 1 10:00 Yen 50 10:45 Euro 116 11:35 Euro 2 11:15 Euro 119 ... ... ... 11:49 USD 99 ... ... ...

6. ܲ‫ݥ‬ᤒᬳള Temporal Table Join SELECT o.amount * r.rate FROM Orders o, LATERAL TABLE (Rates(o.time)) r WHERE o.currency = r.currency

7. RatesHistory Orders Result ෸ᳵ ᨵ૰ ࿤ሲ ෸ᳵ ᨵ૰ ᰁ ࿤ሲ ᰁ SELECT o.amount * r.rate time currency rate time currency amount rate * amount FROM Orders o, 09:00 USD 102 10:15 Euro 2 228 LATERAL TABLE (Rates(o.time)) r 09:00 Euro 114 ... ... ... ... WHERE 09:00 Yen 1 o.currency = r.currency 10:45 Euro 116 11:15 Euro 119 11:49 USD 99 ... ... ...

8. RatesHistory Orders Result ෸ᳵ ᨵ૰ ࿤ሲ ෸ᳵ ᨵ૰ ᰁ ࿤ሲ ᰁ SELECT o.amount * r.rate time currency rate time currency amount rate * amount FROM Orders o, 09:00 USD 102 10:15 Euro 2 228 LATERAL TABLE (Rates(o.time)) r 09:00 Euro 114 10:00 Yen 50 50 WHERE 09:00 Yen 1 11:35 Euro 2 238 o.currency = r.currency 10:45 Euro 116 ... ... ... ... 11:15 Euro 119 11:49 USD 99 ... ... ...

9. ଉᥠᬳള Regular joins ᧍ဩ SELECT * FROM A a, B b WHERE a.id = b.id Syntax ᝜ࢱ ӷӻᤒ໒੒ஂྌਠ‫ݢق‬ᥠ Scope All rows from both tables visible to each other 㲌ਂ ಅํᦕ୯஠ᶳ෫ᴴ๗‫ਂכ‬ Memory All records must be persisted indefinitely

10. ෸ᳵᑻ‫ݗ‬ᬳള Time-windowed Joins ᧍ဩ SELECT * FROM A a, B b WHERE a.id = b.id AND Syntax a.time BETWEEN b.time AND b.time + 1 `DAY` ᝜ࢱ ಅํᤈࣁᤩਧԎጱ෸ᳵᑻ‫ݗ‬Ӿ‫ݢ‬ᥠ Scope Rows visible within a defined time window 㲌ਂ ಅํᦕ୯ࣁሿࣁ޾ᑻ‫ݗ‬ᳩଶԏᳵҁ‫ے‬Ӥ࿜֖ᕚ୊᬴҂ Memory All records between now and window length (plus watermark delay)

11. ܲ‫ݥ‬ᤒᬳള Temporal Table Joins ᧍ဩ SELECT * FROM A a, LATERAL TABLE (B(a.time)) Syntax WHERE a.id = b.id ᝜ࢱ ‫ݝ‬੒ᕳਧጱ%ጱ๋ෛᇇ๜‫ݢ‬ᥠ a.time Scope Visible is only the latest version of B for given a.time 㲌ਂ ᤒ໒%ғಅํ%ጱᇇ๜ࣁሿࣁ޾࿜֖ᕚ୊᬴ԏᳵ Memory Table B: all versions of B between now and watermark delay ᤒ໒$ғಅํᦕ୯ࣁሿࣁ޾࿜֖ᕚ୊᬴ԏᳵ Table A: all records between now and watermark delay

12. ཛྷୗᦩ‫ڦ‬ Pattern recognition

13. ཛྷୗᦩ‫ڦ‬ Pattern recognition ᐏֺғ Examples: ! (S M{2,} E) ! (A B+ C* D) ! (START_ROW PRICE_DOWN+ PRICE_UP)

14.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)

15.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)

16.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)

17.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)

18.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)

19.SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY rowtime MEASURES B.price AS endPrice, COUNT(A.price) AS count ONE ROW PER MATCH AFTER MATCH SKIP TO FIRST B PATTERN (A+ B) DEFINE A AS AVG(A.price) < 15)

20. ཛྷୗᦩ‫ڦ‬ Pattern recognition MATCH_RECOGNIZE ՗‫ݙ‬Ө GROUP BY ํӞԶፘ֒ԏ॒ MATCH_RECOGNIZE clause has some similarities with GROUP BY ฎ 64/ຽ‫ٵ‬ጱӞ᮱‫ړ‬ It is a part of the SQL 2016 standard

21. ٌ՜౮ຎ Other work

22. SQL Client Flink SQL ጱ޸եᤈള‫ݗ‬ Command line interface for Flink SQL ෫ᖫᑕᵱᥝ No programming required

23.ᬰᤈӾጱᶱፓ Ongoing work ᬳള࢏޾໒ୗ Connectors & formats SQL Client क़᮱ፓ୯ඪ೮ External catalog support

24.