-
Notifications
You must be signed in to change notification settings - Fork 683
/
5.1-Ambulance-StreamSets.usql
82 lines (67 loc) · 2.14 KB
/
5.1-Ambulance-StreamSets.usql
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
// Unstructured file sets
// Assumes that vehicle CSV data has been already loaded to specified ADL store location
// Define paths
DECLARE @ADL_DIR string = "/Samples/Data/AmbulanceData/";
DECLARE @ADL_FILESET_REQVID string = @ADL_DIR + "vehicle{vid}_{date:MM}{date:dd}{date:yyyy}.{*}";
@data_req = EXTRACT vehicle_id int
, entry_id long
, event_date DateTime
, latitude float
, longitude float
, speed int
, direction string
, trip_id int?
, vid int // virtual file set column
, date DateTime // virtual file set column
FROM @ADL_FILESET_REQVID
USING Extractors.Csv();
@alldata_req = SELECT *
FROM @data_req
WHERE date>= DateTime.Parse("2014-08-31") AND date < DateTime.Parse("2014-10-31")
AND vid == 1
;
OUTPUT @alldata_req
TO "/output/vehicles_req.txt"
USING Outputters.Csv();
////////////////////
DECLARE @ADL_FILESET_OPTVID string = @ADL_DIR + "vehicle{vid}_{date:MM}{date:dd}{date:yyyy}.{*}";
@data_opt = EXTRACT vehicle_id int
, entry_id long
, event_date DateTime
, latitude float
, longitude float
, speed int
, direction string
, trip_id int?
, vid int // virtual file set column
, date DateTime // virtual file set column
FROM @ADL_FILESET_OPTVID
USING Extractors.Csv();
@alldata_opt = SELECT *
FROM @data_opt
WHERE date>= DateTime.Parse("2014-08-31") AND date < DateTime.Parse("2014-10-31")
;
OUTPUT @alldata_opt
TO "/output/vehicles_opt.txt"
USING Outputters.Csv();
//////////////////////////////
DECLARE @ADL_FILESET_PATH string = @ADL_DIR + "{date:yyyy}/{date:MM}/{date:dd}/vehicle{vid}_{date:MM}{date:dd}{date:yyyy}.csv";
@data_opt = EXTRACT vehicle_id int
, entry_id long
, event_date DateTime
, latitude float
, longitude float
, speed int
, direction string
, trip_id int?
, vid int // virtual file set column
, date DateTime // virtual file set column
FROM @ADL_FILESET_PATH
USING Extractors.Csv();
@alldata_path = SELECT *
FROM @data_opt
WHERE date>= DateTime.Parse("2014-09-16")
;
OUTPUT @alldata_path
TO "/output/vehicles_path.txt"
USING Outputters.Csv();