/
q10.sql
31 lines (29 loc) · 1.28 KB
/
q10.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- -------------------------------------------------------------------------------------------------
-- Query 10: Log to File System (Not in original suite)
-- -------------------------------------------------------------------------------------------------
-- Log all events to file system. Illustrates windows streaming data into partitioned file system.
--
-- Every minute, save all events from the last period into partitioned log files.
-- -------------------------------------------------------------------------------------------------
CREATE TABLE nexmark_q10 (
auction BIGINT,
bidder BIGINT,
price BIGINT,
dateTime TIMESTAMP(3),
extra VARCHAR,
dt STRING,
hm STRING
) PARTITIONED BY (dt, hm) WITH (
'connector' = 'filesystem',
'path' = 'file://${NEXMARK_DIR}/data/output/${SUBMIT_TIME}/bid/',
'format' = 'csv',
'sink.partition-commit.trigger' = 'partition-time',
'sink.partition-commit.delay' = '1 min',
'sink.partition-commit.policy.kind' = 'success-file',
'partition.time-extractor.timestamp-pattern' = '$dt $hm:00',
'sink.rolling-policy.rollover-interval' = '1min',
'sink.rolling-policy.check-interval' = '1min'
);
INSERT INTO nexmark_q10
SELECT auction, bidder, price, dateTime, extra, DATE_FORMAT(dateTime, 'yyyy-MM-dd'), DATE_FORMAT(dateTime, 'HH:mm')
FROM bid;