· 8 years ago · Dec 19, 2017, 11:14 PM
1-- create database if not exists poi_stantec;
2
3-- SETTINGS FOR HIVE;
4set project_db = poi_stantec;
5use ${hiveconf:project_db};
6
7set hive.vectorized.ex ecution.enabled = true;
8set hive.vectorized.execution.reduce.enabled = true;
9set hive.cbo.enable=true;
10set hive.compute.query.using.stats=true;
11set hive.stats.fetch.column.stats=true;
12set hive.stats.fetch.partition.stats=true;
13set hive.exec.reducers.bytes.per.reducer=256000000;
14set hive.auto.convert.join=false;
15set hive.exec.dynamic.partition.mode=nonstrict;
16set hive.exec.max.dynamic.partitions.pernode = 15000;
17set hive.exec.max.dynamic.partitions = 15000;
18set hive.cli.print.header=true;
19set hive.exec.parallel=true;
20set hive.execution.engine = spark;
21
22
23set period = '2017-03';
24set start_period = '2017-03-01';
25set end_period = '2017-03-31';
26
27--Add grids to PM to avoid bouncing
28set location_pm = 57281954,57281978,57279066, 57279073, 57279074, 57279075, 57279076, 57279077, 57279078, 57279079, 57279080, 57279081, 57279082, 57279083, 57279084, 57279085, 57279086, 57279087, 57279091, 57279092, 57279123, 57279124, 57279125, 57281937, 57281938, 57281940, 57281941, 57281942, 57281943, 57281944, 57281945, 57281946, 57281947, 57281948, 57281949, 57281950, 57281956, 57281957;
29set location_levin = 57279587,57279577, 57279578, 57279579, 57279588, 57279589, 57279590, 57280408, 57280409, 57280410, 57280411, 57280412, 57280419, 57280420, 57280421, 57280422;
30
31
32-- ******************************
33-- * Step 1 *
34-- * Cell_id of interest *
35-- * Precursor steps: null *
36-- * Successor steps: 2 *
37-- ******************************
38
39-- *********************************************************************************************
40-- THIS METHOD USE GRID12
41-- BUT I HAVE TO GET THE REGION RTO FOR EXTRAPOLATION PURPOSES
42-- THERE'S A HARD CODED FIX, THERE ARE 2 GRIDS WITH OUT CELL_ID_RTI IN CELL_SECTOR_MASTER TABLE
43-- *********************************************************************************************
44drop table if exists cell_id;
45create table if not exists cell_id STORED as PARQUET as
46select *
47from master.cell_location
48where grid12 in (57279299, 57279568, 57278852, 57278853, 57278854, 57278855, 57278857, 57278858, 57278876, 57278879, 57278880, 57278881, 57278882, 57278883, 57278884, 57278885, 57278886, 57278887, 57278888, 57278889, 57278890, 57278893, 57278894, 57278895, 57278896, 57278897, 57278898, 57278899, 57278900, 57278901, 57278902, 57278903, 57278904, 57278905, 57278906, 57278907, 57278908, 57278909, 57278910, 57278911, 57278912, 57278913, 57278914, 57278915, 57278916, 57278917, 57278918, 57278919, 57278920, 57278921, 57278922, 57278923, 57278924, 57278925, 57278926, 57278927, 57278928, 57278929, 57278930, 57278931, 57278940, 57278941, 57278942, 57278943, 57278944, 57278945, 57278947, 57278964, 57278965, 57278966, 57278969, 57278970, 57278980, 57278981, 57278982, 57278983, 57278984, 57278985, 57278986, 57278987, 57278988, 57278989, 57278992, 57278993, 57278994, 57278995, 57278996, 57278997, 57278998, 57278999, 57279000, 57279001, 57279002, 57279003, 57279004, 57279005, 57279006, 57279007, 57279008, 57279009, 57279010, 57279011, 57279012, 57279013, 57279014, 57279015, 57279016, 57279017, 57279018, 57279019, 57279020, 57279021, 57279022, 57279023, 57279024, 57279025, 57279026, 57279027, 57279028, 57279029, 57279030, 57279031, 57279032, 57279033, 57279034, 57279035, 57279036, 57279037, 57279038, 57279039, 57279040, 57279041, 57279042, 57279043, 57279044, 57279045, 57279046, 57279047, 57279048, 57279049, 57279050, 57279051, 57279052, 57279053, 57279054, 57279055, 57279056, 57279057, 57279058, 57279059, 57279060, 57279061, 57279062, 57279063, 57279064, 57279065, 57279066, 57279067, 57279068, 57279069, 57279070, 57279071, 57279072, 57279073, 57279074, 57279075, 57279076, 57279077, 57279078, 57279079, 57279080, 57279081, 57279082, 57279083, 57279084, 57279085, 57279086, 57279087, 57279088, 57279089, 57279090, 57279091, 57279092, 57279093, 57279094, 57279095, 57279096, 57279097, 57279098, 57279099, 57279100, 57279101, 57279102, 57279103, 57279104, 57279105, 57279106, 57279107, 57279108, 57279109, 57279110, 57279111, 57279112, 57279113, 57279114, 57279115, 57279116, 57279117, 57279118, 57279119, 57279120, 57279121, 57279122, 57279123, 57279124, 57279125, 57279126, 57279127, 57279128, 57279129, 57279130, 57279131, 57279132, 57279133, 57279134, 57279135, 57279136, 57279137, 57279138, 57279139, 57279140, 57279141, 57279150, 57279152, 57279153, 57279154, 57279155, 57279156, 57279157, 57279158, 57279159, 57279160, 57279161, 57279162, 57279163, 57279164, 57279165, 57279166, 57279167, 57279168, 57279169, 57279170, 57279171, 57279172, 57279173, 57279174, 57279175, 57279176, 57279177, 57279178, 57279179, 57279180, 57279181, 57279182, 57279183, 57279184, 57279185, 57279186, 57279187, 57279188, 57279189, 57279190, 57279191, 57279192, 57279193, 57279194, 57279195, 57279196, 57279197, 57279198, 57279199, 57279200, 57279201, 57279202, 57279203, 57279204, 57279205, 57279206, 57279207, 57279208, 57279209, 57279210, 57279211, 57279212, 57279213, 57279214, 57279215, 57279216, 57279217, 57279218, 57279219, 57279220, 57279221, 57279222, 57279223, 57279224, 57279225, 57279226, 57279227, 57279228, 57279229, 57279230, 57279231, 57279232, 57279233, 57279234, 57279235, 57279236, 57279237, 57279238, 57279239, 57279245, 57279246, 57279288, 57279289, 57279290, 57279291, 57279292, 57279293, 57279295, 57279296, 57279570, 57279571, 57279572, 57279573, 57279574, 57279575, 57279576, 57279577, 57279578, 57279579, 57279580, 57279581, 57279584, 57279585, 57279586, 57279587, 57279588, 57279589, 57279590, 57279591, 57279592, 57279593, 57279594, 57279597, 57280394, 57280405, 57280406, 57280407, 57280408, 57280409, 57280410, 57280411, 57280412, 57280413, 57280415, 57280416, 57280417, 57280418, 57280419, 57280420, 57280421, 57280422, 57280423, 57280424, 57280425, 57280426, 57280427, 57280428, 57280429, 57280430, 57280431, 57280432, 57280433, 57280434, 57280435, 57280436, 57280437, 57280438, 57280439, 57280440, 57280444, 57280445, 57280446, 57280447, 57280448, 57280449, 57280451, 57281890, 57281893, 57281894, 57281895, 57281896, 57281897, 57281898, 57281899, 57281900, 57281901, 57281902, 57281903, 57281904, 57281921, 57281922, 57281923, 57281924, 57281925, 57281926, 57281927, 57281928, 57281929, 57281930, 57281931, 57281932, 57281933, 57281934, 57281935, 57281936, 57281937, 57281938, 57281939, 57281940, 57281941, 57281942, 57281943, 57281944, 57281945, 57281946, 57281947, 57281948, 57281949, 57281950, 57281951, 57281952, 57281953, 57281954, 57281955, 57281956, 57281957, 57281958, 57281959, 57281960, 57281961, 57281962, 57281963, 57281964, 57281965, 57281966, 57281967, 57281968, 57281969, 57281970, 57281971, 57281972, 57281973, 57281974, 57281975, 57281976, 57281977, 57281978, 57281979, 57281980, 57281981, 57281982, 57281983, 57281984, 57281985, 57281998, 57281999, 57282000, 57282003, 57282004, 57282112, 57282113, 57282124, 57282125, 57282126, 57282127, 57282128, 57282129, 57282130, 57282131, 57282132, 57282133, 57282134, 57282135, 57282136, 57282137, 57282138, 57282139, 57282140, 57282141, 57282142)
49and cell_id_rti is not null
50and (
51 (start_date <= ${hiveconf:end_period} and eff_end is null)
52 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period})
53);
54
55drop table if exists grid12_towns_of_interest;
56create table if not exists grid12_towns_of_interest STORED as PARQUET
57as
58select *, 'Levin' as town
59from master.cell_location
60where grid12 in (${hiveconf:location_levin})
61and cell_id_rti is not null
62and ((start_date <= ${hiveconf:end_period} and eff_end is null)
63OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}))
64
65union ALL
66
67select *, 'Palmerston North'as town
68from master.cell_location
69where grid12 in (${hiveconf:location_pm})
70and cell_id_rti is not null
71and (
72 (start_date <= ${hiveconf:end_period} and eff_end is null)
73 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}));
74
75
76-- ************************************************************************************
77-- Set the entrance look up table to know where people is coming from
78-- ************************************************************************************
79
80drop table if exists entrance;
81create table if not exists entrance STORED as PARQUET
82as
83select *,
84 1 as entrance
85from master.cell_location
86where grid12 in (57279754,57279757,57279618,57279585, 57279586, 57279590, 57279591, 57279592, 57279593, 57279594, 57279595, 57279596, 57279597, 57279598, 57279599, 57279600, 57279621, 57279622, 57279623, 57279624, 57279625, 57279626, 57279627, 57279628, 57279629, 57279631, 57279632, 57279633, 57279634, 57279635, 57279636, 57279637, 57279639, 57279645, 57279646, 57279647, 57279669, 57279670, 57279671, 57279672, 57279673, 57279674, 57279675, 57279676, 57279677, 57279681, 57279694, 57280405, 57280406, 57279680,57279763,57279760,57279804)
87and cell_id_rti is not null
88and (
89 (start_date <= ${hiveconf:end_period} and eff_end is null)
90 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}))
91
92UNION ALL
93
94select *,
95 2 as entrance
96from master.cell_location
97where grid12 in (57190927, 57190928, 57190929, 57190930, 57190931, 57190932, 57190933, 57190934, 57190935, 57190936, 57190937, 57190938, 57190939, 57190940, 57190941, 57190942, 57190943, 57190944, 57190946, 57190947, 57190948, 57190949, 57190950, 57190951, 57190952, 57190953, 57190954, 57190955, 57190956, 57190957, 57191016, 57191018, 57191019, 57191020, 57191021, 57191022, 57191023, 57191024, 57191025, 57191026, 57191027, 57191032, 57191035, 57191036, 57191037, 57191038, 57191039, 57191040, 57191041, 57191042, 57191043, 57191044, 57191045, 57191046, 57191047, 57191048, 57191049, 57191050, 57191051, 57191052, 57191053, 57191054, 57191055, 57191056, 57191057, 57191058, 57191059, 57191060, 57191061, 57191062, 57191063, 57191064, 57191065, 57191066, 57191067, 57191068, 57191069, 57191070, 57191071, 57191072, 57191073, 57191074, 57191075, 57191076, 57191077, 57191078, 57191079, 57191080, 57191081, 57191082, 57191083, 57191084, 57191085, 57191086, 57191087, 57191088, 57191089, 57191090, 57191091, 57191092, 57191093, 57191094, 57191095, 57191096, 57191097, 57191098, 57191099, 57191100, 57191101, 57191102, 57191103, 57191104, 57191105, 57191106, 57191107, 57191108, 57191109, 57191110, 57191111, 57191112, 57191113, 57191114, 57191115, 57191116, 57191117, 57191118, 57191119, 57191120, 57191121, 57191122, 57191123, 57191124, 57191125, 57191126, 57191127, 57191128, 57191129, 57191130, 57191131, 57191132, 57191133, 57191134, 57191135, 57191136, 57191137, 57191138, 57191139, 57191140, 57191141, 57191142, 57191143, 57191144, 57191145, 57191146, 57191147, 57191148, 57191149, 57191150, 57191151, 57191152, 57191153, 57191154, 57191155, 57191156, 57191157, 57191158, 57191159, 57191160, 57191161, 57191162, 57191165, 57191166, 57191172, 57191173, 57191174, 57191175, 57191176, 57191177, 57191178, 57191199, 57191200, 57191201, 57191202, 57191203, 57191204, 57191205, 57191206, 57191207, 57191208, 57191209, 57191210, 57191211, 57191212, 57191213, 57191214, 57191215, 57191216, 57191217, 57191218, 57191219, 57191220, 57191221, 57191222, 57191223, 57191224, 57191225, 57191226, 57191227, 57191228, 57191229, 57191230, 57191231, 57191232, 57191233, 57191234, 57191235, 57191236, 57191237, 57191238, 57191239, 57191240, 57191241, 57191242, 57191243, 57191244, 57191245, 57191246, 57191247, 57191248, 57191249, 57191262, 57191263, 57191264, 57191265, 57191266, 57191267, 57191276, 57191277, 57191278, 57191279, 57191280, 57191281, 57191282, 57191283, 57191284, 57191285, 57191286, 57191287, 57191288, 57191289, 57191290, 57191291, 57191292, 57191293, 57191294, 57191295, 57191296, 57191297, 57191298, 57191299, 57191300, 57191301, 57191302, 57191303, 57191304, 57191305, 57191306, 57191307, 57191308, 57191309, 57191310, 57191311, 57191312, 57191313, 57191314, 57191315, 57191319, 57191320, 57191323, 57191324, 57191325, 57191326, 57191327, 57191328, 57191329, 57191330, 57191331, 57191332, 57191335, 57191340, 57191341, 57191342, 57191343, 57191344, 57191345, 57191346, 57191347, 57191348, 57191349, 57191350, 57191351, 57191352, 57191353, 57191354, 57191355, 57191356, 57191357, 57191358, 57191359, 57191361, 57191374, 57191375, 57191376, 57191377, 57191378, 57191379, 57191380, 57191381, 57191382, 57191383, 57191384, 57191385, 57191610, 57191625, 57191626, 57191627, 57191628, 57191631, 57191632, 57191633, 57191644, 57191645, 57191646, 57191647, 57191648, 57191649, 57191650, 57191651, 57191652, 57191655, 57191656, 57191658, 57191659, 57191660, 57191661, 57191662, 57191663, 57191664, 57191665, 57191666, 57191667, 57191668, 57191669, 57191670, 57191671, 57278228, 57278229, 57278442, 57278443, 57278444, 57278445, 57278446, 57278447, 57278448, 57278449, 57278450, 57278451, 57278455, 57278456, 57278459, 57278460, 57278461, 57278462, 57278463, 57278464, 57278465, 57278466, 57278467, 57278468, 57278469, 57278470, 57278471, 57278472, 57278473, 57278474, 57278475, 57278476, 57278477, 57278478, 57278479, 57278480, 57278481, 57278482, 57278483, 57278484, 57278485, 57278486, 57278487, 57278488, 57278489, 57278490, 57278491, 57278492, 57278493, 57278494, 57278495, 57278496, 57278498, 57278499, 57278500, 57278501, 57278502, 57278503, 57278505, 57278517, 57278596, 57278597, 57278598, 57278640, 57278641, 57278642, 57278643, 57278647, 57278648, 57278649, 57278650, 57278651, 57278652, 57278653, 57278654, 57278655, 57278656, 57278657, 57278658, 57278659, 57278660, 57278661, 57278662, 57278663, 57278664, 57278665, 57278666, 57278667, 57278668, 57278669, 57278670, 57278671, 57278672, 57278673, 57278674, 57278675, 57278676, 57278677, 57278678, 57278679, 57278680, 57278681, 57278682, 57278683, 57278684, 57278685, 57278686, 57278687, 57278688, 57278689, 57278690, 57278691, 57278692, 57278693, 57278694, 57278695, 57278696, 57278697, 57278698, 57278699, 57278700, 57278701, 57278702, 57278703, 57278704, 57278705, 57278706, 57278707, 57278708, 57278709, 57278710, 57278711, 57278712, 57278713, 57278714, 57278715, 57278716, 57278717, 57278718, 57278719, 57278721, 57278732, 57278734, 57278735, 57278736, 57278737, 57278738, 57278739, 57278740, 57278741, 57278742, 57278743, 57278744, 57278745, 57278746, 57278747, 57278749, 57278922, 57278923, 57278938, 57278939, 57278940, 57278941, 57278942, 57278943, 57278944, 57278945, 57278946, 57278947, 57278948, 57278949, 57278950, 57278951, 57278952, 57278953, 57278954, 57278955, 57278956, 57278957, 57278958, 57278959, 57278960, 57278962, 57278963, 57278964, 57278965, 57278966, 57278967, 57365762, 57365763, 57365764, 57365765, 57365766, 57365767, 57365768, 57365769, 57365770, 57365771, 57365791, 57365792, 57365793, 57365794, 57365795, 57365796, 57365799, 57365800, 57365803, 57365804, 57365805, 57365806, 57365807, 57365808, 57365809, 57365810, 57365811, 57365812, 57365813, 57365814, 57365815, 57365816, 57365817, 57365818, 57365819, 57365820, 57365821, 57365822, 57365823, 57365824, 57365825, 57365826, 57365827, 57365828, 57365829, 57365830, 57365831, 57365832, 57365833, 57365834, 57365835, 57365836, 57365837, 57365838, 57365839, 57365840, 57365841, 57365842, 57365843, 57365844, 57365845, 57365846, 57365847, 57365848, 57365849, 57365850, 57365851, 57365852, 57365853, 57365854, 57365855, 57365856, 57365857, 57365858, 57365859, 57365860, 57365861, 57365862, 57365863, 57365864, 57365865, 57365866, 57365867, 57365868, 57365869, 57365870, 57365871, 57365872, 57365873, 57365874, 57365875, 57365876, 57365877, 57365878, 57365879, 57365880, 57365881, 57365882, 57365883, 57365884, 57365885, 57365886, 57365887, 57365904, 57365907, 57365908, 57365909)
98and cell_id_rti is not null
99and (
100 (start_date <= ${hiveconf:end_period} and eff_end is null)
101 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}))
102
103UNION ALL
104
105select *,
106 3 as entrance
107from master.cell_location
108where grid12 in (57278746, 57278747, 57278748, 57278752, 57278753, 57278754, 57278755, 57278756, 57278757, 57278758, 57278759, 57278760, 57278761, 57278762, 57278763, 57278764, 57278765, 57278766, 57278767, 57278768, 57278769, 57278770, 57278771, 57278772, 57278773, 57278774, 57278775, 57278776, 57278779, 57278780, 57278781, 57278782, 57278783, 57278784, 57278785, 57278786, 57278787, 57278788, 57278789, 57278790, 57278791, 57278792, 57278793, 57278794, 57278795, 57278796, 57278797, 57278798, 57278799, 57278800, 57278801, 57278802, 57278803, 57278804, 57278805, 57278806, 57278807, 57278808, 57278809, 57278810, 57278811, 57278812, 57278813, 57278814, 57278815, 57278816, 57278817, 57278818, 57278819, 57278820, 57278821, 57278822, 57278823, 57278824, 57278825, 57278826, 57278827, 57278828, 57278829, 57278830, 57278831, 57278832, 57278833, 57278834, 57278835, 57278836, 57278837, 57278838, 57278839, 57278840, 57278841, 57278842, 57278843, 57278844, 57278845, 57278846, 57278847, 57278848, 57278849, 57278850, 57278851, 57278852, 57278853, 57278854, 57278855, 57278856, 57278859, 57278860, 57278861, 57278862, 57278863, 57278864, 57278865, 57278867, 57278906, 57278927, 57278928, 57278929, 57278930, 57278931, 57278932, 57278933, 57278934, 57278935, 57278936, 57278937, 57278938, 57278939, 57278940, 57278941, 57278942, 57278946, 57278947, 57278948, 57278949, 57278950, 57278951, 57282198, 57282200, 57282201, 57282202, 57282203, 57282204, 57282207, 57282208, 57282209, 57282210, 57282211, 57282212, 57282213, 57282214, 57282215, 57282216, 57282217, 57282218, 57282219, 57282220, 57282221, 57282222, 57282223, 57282224, 57282225, 57282226, 57282227, 57282228, 57282229, 57282231, 57282236, 57282237, 57282238, 57282239, 57282240, 57364585, 57364586, 57364628, 57364629, 57364630, 57364631, 57364632, 57364633, 57364634, 57364635, 57364636, 57364637, 57364640, 57364642, 57364643, 57364644, 57364645, 57364646, 57364647, 57364648, 57364649, 57364650, 57364992, 57364993, 57365003, 57365004, 57365005, 57365006, 57365007, 57365008, 57365009, 57365010, 57365011, 57365012, 57365013, 57365014, 57365015, 57365016, 57365017, 57365018, 57365019, 57365020, 57365021, 57365022, 57365023, 57365024, 57365025, 57365026, 57365027, 57365028, 57365029, 57365030, 57365031, 57365032, 57365033, 57365034, 57365035, 57365036, 57365037, 57365038, 57365039, 57365040, 57365043, 57365044, 57365045, 57365061, 57365062, 57365065, 57365066, 57365081, 57365082, 57365083, 57365084, 57365087, 57365088, 57365089, 57365090, 57365091, 57365092, 57365093, 57365094, 57365095, 57365096, 57365097, 57365098, 57365099, 57365100, 57365101, 57365102, 57365103, 57365104, 57365105, 57365106, 57365107, 57365108, 57365109, 57365110, 57365111, 57365112, 57365113, 57365114, 57365115, 57365116, 57365117, 57365118, 57365119, 57365120, 57365121, 57365122, 57365123, 57365124, 57365125, 57365126, 57365127, 57365128, 57365129, 57365130, 57365131, 57365132, 57365133, 57365134, 57365135, 57365136, 57365137, 57365138, 57365139, 57365140, 57365141, 57365142, 57365143, 57365144, 57365145, 57365146, 57365147, 57365148, 57365149, 57365150, 57365151, 57365152, 57365153, 57365154, 57365155, 57365156, 57365157, 57365158, 57365159, 57365166, 57365170, 57365171, 57365172, 57365173, 57365174, 57365175, 57365176, 57365177, 57365178, 57365179, 57365180, 57365181, 57365182, 57365183, 57365184, 57365185, 57365186, 57365187, 57365188, 57365189, 57365190, 57365191, 57365192, 57365193, 57365194, 57365195, 57365196, 57365197, 57365198, 57365199, 57365200, 57365201, 57365202, 57365203, 57365204, 57365205, 57365206, 57365207, 57365208, 57365209, 57365210, 57365211, 57365212, 57365213, 57365214, 57365215, 57365216, 57365217, 57365218, 57365219, 57365220, 57365221, 57365222, 57365223, 57365224, 57365225, 57365226, 57365227, 57365228, 57365229, 57365230, 57365231, 57365232, 57365233, 57365234, 57365235, 57365236, 57365237, 57365238, 57365239, 57365240, 57365241, 57365242, 57365243, 57365244, 57365245, 57365246, 57365247, 57365248, 57365249, 57365250, 57365251, 57365252, 57365253, 57365254, 57365255, 57365256, 57365257, 57365258, 57365259, 57365260, 57365261, 57365264, 57365265, 57365266, 57365267, 57365268, 57365269, 57365270, 57365271, 57365272, 57365273, 57365274, 57365275, 57365276, 57365277, 57365278, 57365279, 57365280, 57365281, 57365282, 57365283, 57365284, 57365285, 57365286, 57365287, 57365288, 57365289, 57365290, 57365291, 57365292, 57365293, 57365294, 57365295, 57365296, 57365297, 57365298, 57365299, 57365300, 57365301, 57365302, 57365303, 57365304, 57365305, 57365306, 57365307, 57365308, 57365309, 57365310, 57365311, 57365312, 57365313, 57365314, 57365315, 57365316, 57365317, 57365318, 57365319, 57365320, 57365321, 57365322, 57365323, 57365324, 57365325, 57365326, 57365327, 57365328, 57365329, 57365330, 57365331, 57365332, 57365333, 57365334, 57365335, 57365336, 57365337, 57365338, 57365339, 57365340, 57365341, 57365342, 57365343, 57365344, 57365345, 57365346, 57365347, 57365348, 57365349, 57365350, 57365351, 57365352, 57365353, 57365354, 57365355, 57365356, 57365357, 57365358, 57365359, 57365360, 57365361, 57365362, 57365363, 57365364, 57365365, 57365366, 57365367, 57365368, 57365369, 57365370, 57365371, 57365372, 57365373, 57365374, 57365375, 57365376, 57365377, 57365378, 57365379, 57365380, 57365381, 57365382, 57365383, 57365384, 57365385, 57365386, 57365387, 57365388, 57365389, 57365390, 57365391, 57365392, 57365393, 57365394, 57365395, 57365396, 57365397, 57365398, 57365399, 57365400, 57365401, 57365402, 57365403, 57365404, 57365405, 57365406, 57365407, 57365408, 57365409, 57365411, 57365426, 57365427, 57365428, 57365429, 57365430, 57365431, 57365432, 57365433, 57365434, 57365435, 57365436, 57365437, 57365438, 57365439, 57365440, 57365441, 57365442, 57365443, 57365444, 57365445, 57365446, 57365447, 57365448, 57365449, 57365450, 57365451, 57365452, 57365453, 57365454, 57365455, 57365456, 57365457, 57365458, 57365459, 57365460, 57365461, 57365462, 57365463, 57365464, 57365465, 57365466, 57365467, 57365468, 57365469, 57365470, 57365471, 57365472, 57365473, 57365474, 57365475, 57365476, 57365477, 57365478, 57365479, 57365480, 57365481, 57365482, 57365483, 57365484, 57365485, 57365486, 57365487, 57365488, 57365489, 57365490, 57365491, 57365492, 57365493, 57365494, 57365495, 57365496, 57365497, 57365498, 57365499, 57365500, 57365501, 57365502, 57365503, 57365504, 57365505, 57365506, 57365507, 57365508, 57365509, 57365510, 57365511, 57365512, 57365513, 57365514, 57365515, 57365516, 57365517, 57365518, 57365519, 57365520, 57365521, 57365522, 57365523, 57365524, 57365525, 57365526, 57365527, 57365528, 57365529, 57365530, 57365531, 57365532, 57365533, 57365534, 57365535, 57365536, 57365537, 57365538, 57365539, 57365540, 57365550, 57365551, 57365552, 57365553, 57365554, 57365555, 57365556, 57365557, 57365558, 57365559, 57365560, 57365561, 57365562, 57365563, 57365564, 57365565, 57365566, 57365567, 57365568, 57365569, 57365570, 57365571, 57365572, 57365582, 57365734, 57365737, 57365738, 57365739, 57366443, 57366444, 57366447, 57366448, 57366449, 57366462, 57367898, 57367899, 57367900, 57367906, 57367907, 57367908, 57367909, 57367910, 57367911, 57367912, 57367913, 57367914, 57367915, 57367916, 57367917, 57367918, 57367919, 57367952, 57367953, 57367954, 57367955, 57367956, 57367957, 57367958, 57367959, 57367960, 57367961, 57367962, 57367963, 57367964, 57367965, 57367966, 57367967, 57367968, 57367969, 57367970, 57367971, 57367972, 57367973, 57367974, 57367975)
109and cell_id_rti is not null
110and (
111 (start_date <= ${hiveconf:end_period} and eff_end is null)
112 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}))
113
114UNION ALL
115
116select *,
117 4 as entrance
118from master.cell_location
119where grid12 in (57278822, 57278823, 57278824, 57278825, 57278826, 57278827, 57278828, 57278829, 57278865, 57278866, 57278867, 57278868, 57278869, 57278870, 57278871, 57278872, 57278873, 57278874, 57278875, 57278876, 57278877, 57278878, 57278879, 57278880, 57278881, 57278882, 57278883, 57278884, 57278885, 57278886, 57278887, 57278888, 57278889, 57278890, 57278891, 57278893, 57278894, 57278895, 57279055, 57279056, 57279057, 57279058, 57279059, 57279060, 57279061, 57279062, 57279063, 57279064, 57279065, 57281956, 57281957, 57281958, 57281959, 57281960, 57281961, 57281962, 57281963, 57281964, 57281965, 57282129, 57282130, 57282131, 57282132, 57282133, 57282134, 57282135, 57282136, 57282137, 57282138, 57282139, 57282140, 57282141, 57282142, 57282143, 57282144, 57282145, 57282146, 57282147, 57282148, 57282149, 57282150, 57282151, 57282152, 57282153, 57282154, 57282155, 57282156, 57282157, 57282158, 57282159, 57282160, 57282161, 57282163, 57282164, 57282165, 57282182, 57282183, 57282184, 57282185, 57282186, 57282187, 57282188, 57282189, 57282190, 57282191, 57282192, 57282193, 57282194, 57282195, 57282196, 57282197, 57282198, 57282199, 57282200, 57282201, 57282202, 57282203, 57282204, 57282205, 57282206, 57282207, 57282208, 57282209, 57282210, 57282211, 57282226, 57282227, 57282228, 57282229, 57282230, 57282231, 57282232, 57282233, 57282234, 57282235, 57282236, 57282237, 57282238, 57282239, 57282240, 57282241, 57282254, 57282255)
120and cell_id_rti is not null
121and (
122 (start_date <= ${hiveconf:end_period} and eff_end is null)
123 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}))
124
125UNION ALL
126
127
128select *,
129 5 as entrance
130from master.cell_location
131where grid12 in (57281996, 57281997, 57281986, 57281992, 57281641, 57281642, 57281684, 57281685, 57281686, 57281687, 57281688, 57281689, 57281690, 57281691, 57281692, 57281693, 57281696, 57281697, 57281698, 57281699, 57281700, 57281701, 57281702, 57281703, 57281704, 57281705, 57281706, 57281707, 57281708, 57281709, 57281710, 57281711, 57281712, 57281715, 57281993, 57281994, 57281995, 57282001, 57282002, 57282012, 57282013, 57282014, 57282015, 57282016, 57282017, 57282018, 57282019, 57282028, 57282029, 57282030, 57282031, 57282032, 57282033, 57282034, 57282035, 57282036, 57282037, 57282038, 57282039, 57282040, 57282041, 57282043, 57282044, 57282045, 57282046, 57282047, 57282048, 57282049, 57282051, 57282378, 57282386, 57282387, 57282388, 57282389, 57282390, 57282391, 57282392, 57282393, 57282394, 57282395, 57282396, 57282397, 57282398, 57282399, 57282400, 57282401, 57282402, 57282403, 57282404, 57282405, 57282406, 57282407, 57282408, 57282409, 57282410, 57282411, 57282412, 57282413, 57282414, 57282415, 57282416, 57282417, 57282418, 57282419, 57282420, 57282421, 57282422, 57282423, 57282424, 57282429, 57282430, 57282431, 57282432, 57282433, 57282434, 57282435, 57282436, 57282437, 57282438, 57282439, 57282440, 57282441, 57282442, 57282443, 57282444, 57282445, 57282446, 57282447, 57282448, 57282449, 57282450, 57282451, 57282452, 57282453, 57282454, 57282455, 57282456, 57282457, 57282458, 57282459, 57282460, 57282461, 57282462, 57282463, 57282464, 57282465, 57282466, 57282467, 57282468, 57282471, 57282476, 57282477, 57282478, 57282479, 57282480, 57282481, 57282482, 57282483, 57282484, 57282485, 57282486, 57282487, 57282488, 57282489, 57282490, 57282491, 57282492, 57282493, 57282494, 57282495, 57282496, 57282497, 57282498, 57282499, 57282500, 57282501, 57282502, 57282503, 57282504, 57282505, 57282506, 57282507, 57282508, 57282509, 57282510, 57282511, 57282512, 57282513, 57282514, 57282515, 57282516, 57282517, 57282519, 57282525, 57282526, 57282550, 57282552, 57282553, 57282554, 57282555, 57282556, 57282561, 57282562, 57282563, 57282564, 57282565, 57282566, 57282567, 57282568, 57282569, 57282570, 57282571, 57282572, 57282573, 57282574, 57282575, 57282576, 57282577, 57282578, 57282579, 57282580, 57282581, 57282582, 57282583, 57282584, 57282585, 57282586, 57282587, 57282588, 57282589, 57282590, 57282591, 57282592, 57282593, 57282594, 57282595, 57282596, 57282597, 57282598, 57282599, 57282600, 57282601, 57282602, 57282603, 57282604, 57282605, 57282606, 57282607, 57282608, 57282609, 57282610, 57282611, 57282612, 57282613, 57282614, 57282615, 57282616, 57282617, 57282618, 57282619, 57282620, 57282621, 57282622, 57282623, 57282624, 57282625, 57282626, 57282627, 57282628, 57282629, 57282630, 57282631, 57282632, 57282633, 57282634, 57282635, 57282636, 57282637, 57282638, 57282639, 57282640, 57282641, 57282642, 57282643, 57282644, 57282645, 57282646, 57282647, 57282648, 57282649, 57282650, 57282651, 57282652, 57282653, 57282654, 57282655, 57282656, 57282657, 57282658, 57282659, 57282660, 57282661, 57282663, 57282670, 57282673, 57282674, 57282675, 57282676, 57282677, 57282678, 57282679, 57282680, 57282681, 57282682, 57282683, 57282684, 57282685, 57282686, 57282687, 57282688, 57282773, 57282774, 57282775, 57282776, 57282777, 57282778, 57282779, 57282780, 57282785, 57282786, 57282787, 57282788, 57282789, 57282790, 57282791, 57282792, 57282793, 57282794, 57282795, 57282796, 57282797, 57282798, 57282799, 57282800, 57283482, 57283489, 57283490, 57283491, 57283492, 57283493, 57283494, 57283495, 57283496, 57283497, 57283498, 57283499, 57283500, 57283501, 57283502, 57283503, 57283504, 57283505, 57283506, 57283507, 57283508, 57283517, 57283518, 57283519, 57283520, 57360730, 57360738, 57360739, 57360740, 57360741, 57360742, 57360743, 57360744, 57360745, 57360746, 57360747, 57360748, 57360749, 57360786, 57360787, 57360788, 57360789, 57360790, 57360791, 57360792, 57360793, 57360794, 57360795, 57360796, 57360797, 57360802, 57360803, 57360804, 57360805, 57360806, 57360807, 57360808, 57360809, 57360810, 57360811, 57360812, 57360978, 57360979, 57360980, 57360981, 57360982, 57360983, 57360984, 57360985, 57360986, 57360987, 57360988, 57360989, 57360994, 57360995, 57360996, 57360997, 57360999, 57361000, 57361001, 57361002, 57361003, 57361004, 57361005, 57361042, 57361043, 57361044, 57361045, 57361046, 57361047, 57361048, 57361049, 57361050, 57361051, 57361052, 57361053, 57361058, 57361059, 57361060, 57361061, 57361062, 57361063, 57361064, 57361065, 57361066, 57361067, 57361068, 57361069, 57362427, 57362428, 57362431, 57362432, 57362435, 57362436, 57362437, 57362490, 57362491, 57362492, 57362495, 57362496, 57362497, 57362510, 57362511, 57362512, 57362515, 57362516, 57363610, 57363616, 57363617, 57363618, 57363619, 57363620, 57363621, 57363622, 57363623, 57363624, 57363625, 57363626, 57363627, 57363628, 57363629, 57363630, 57363631, 57363632, 57363633, 57363634, 57363635, 57363636, 57363639, 57363644, 57363645, 57363646, 57363647, 57363648, 57363649, 57363651, 57363652, 57363653, 57363714, 57363715, 57363716, 57363717, 57363718, 57363719, 57363720, 57363721, 57363722, 57363723, 57363724, 57363725, 57363726, 57363727, 57363728, 57363729, 57363730, 57363731, 57363732, 57363733, 57363734, 57363735, 57363736, 57363737, 57363738, 57363739, 57363740, 57363741, 57363742, 57363743, 57363744, 57363745, 57363746, 57363747, 57363748, 57363749, 57363750, 57363751, 57363752, 57363753, 57363754, 57363755, 57363756, 57363757, 57363758, 57363759, 57363760, 57363761, 57363762, 57363763, 57363764, 57363765, 57363766, 57363767, 57363768, 57363769, 57363770, 57363771, 57363772, 57363773, 57363774, 57363775, 57363776, 57363777, 57363778, 57363779, 57363780, 57363781, 57363782, 57363783, 57363784, 57363785, 57363786, 57363787, 57363788, 57363789, 57363790, 57363791, 57363792, 57363793, 57363794, 57363795, 57363796, 57363797, 57363798, 57363799, 57363800, 57363801, 57363802, 57363803, 57363804, 57363805, 57363806, 57363807, 57363808, 57363809, 57363810, 57363811, 57363812, 57363813, 57363814, 57363815, 57363816, 57363817, 57363818, 57363819, 57363820, 57363821, 57363822, 57363823, 57363824, 57363825, 57363826, 57363827, 57363828, 57363829, 57363830, 57363831, 57363832, 57363833, 57363834, 57363835, 57363836, 57363837, 57363838, 57363839, 57363840, 57363841, 57363842, 57363843, 57363844, 57363845, 57363846, 57363847, 57363848, 57363849, 57363850, 57363851, 57363852, 57363853, 57363854, 57363855, 57363856, 57363857, 57363858, 57363859, 57363860, 57363861, 57363862, 57363863, 57363864, 57363865, 57363866, 57363868, 57363869, 57363870, 57363871, 57363872, 57363873, 57363874, 57363876, 57363877, 57363878, 57363879, 57363880, 57363881, 57363882, 57363883, 57363884, 57363885, 57363886, 57363887, 57363888, 57363889, 57363890, 57363891, 57363892, 57363893, 57363894, 57363895, 57363896, 57363897, 57363898, 57363899, 57363900, 57363901, 57363902, 57363903, 57363904, 57363905, 57363906, 57363907, 57363908, 57363909, 57363910, 57363911, 57363912, 57363913, 57363914, 57363915, 57363916, 57363917, 57363918, 57363919, 57363920, 57363921, 57363922, 57363923, 57363924, 57363925, 57363926, 57363927, 57363928, 57363929, 57363930, 57363931, 57363932, 57363933, 57363934, 57363935, 57363936, 57363937, 57363938, 57363939, 57363940, 57363941, 57363942, 57363943, 57363944, 57363945, 57363946, 57363947, 57363948, 57363949, 57363950, 57363951, 57363952, 57363953, 57363954, 57363955, 57363956, 57363957, 57363958, 57363959, 57363960, 57363961, 57363962, 57363963, 57363964, 57363965, 57363966, 57363967, 57363968, 57363969, 57363970, 57363971, 57363972, 57363973, 57363974, 57363975, 57363976, 57363977, 57363978, 57363979, 57363980, 57363981, 57363982, 57363983, 57363984, 57363985, 57363986, 57363987, 57363988, 57363989, 57363990, 57363991, 57363992, 57363993, 57363994, 57363995, 57363996, 57363997, 57363998, 57363999, 57364000, 57364001, 57364002, 57364003, 57364004, 57364005, 57364006, 57364007, 57364008, 57364009, 57364010, 57364011, 57364012, 57364013, 57364014, 57364015, 57364016, 57364017, 57364018, 57364019, 57364020, 57364021, 57364022, 57364023, 57364024, 57364025, 57364026, 57364027, 57364028, 57364029, 57364030, 57364031, 57364032, 57364033, 57364034, 57364035, 57364036, 57364037, 57364038, 57364039, 57364040, 57364041, 57364042, 57364043, 57364044, 57364045, 57364046, 57364047, 57364048, 57364049, 57364050, 57364051, 57364052, 57364053, 57364054, 57364055, 57364056, 57364057, 57364058, 57364059, 57364060, 57364061, 57364062, 57364063, 57364064, 57364065, 57364066, 57364067, 57364068, 57364069, 57364070, 57364071, 57364072, 57364073, 57364074, 57364075, 57364076, 57364077, 57364078, 57364079, 57364080, 57364081, 57364082, 57364083, 57364084, 57364085, 57364086, 57364087, 57364088, 57364089, 57364090, 57364091, 57364092, 57364093, 57364094, 57364095, 57364096, 57364097, 57364098, 57364099, 57364100, 57364101, 57364102, 57364103, 57364104, 57364105, 57364106, 57364107, 57364108, 57364109, 57364110, 57364111, 57364112, 57364113, 57364114, 57364115, 57364116, 57364117, 57364118, 57364119, 57364120, 57364121, 57364122, 57364123, 57364124, 57364125, 57364126, 57364127, 57364128, 57364129, 57364130, 57364131, 57364132, 57364133, 57364135, 57364142, 57364145, 57364146, 57364147, 57364148, 57364149, 57364150, 57364151, 57364152, 57364153, 57364154, 57364155, 57364156, 57364157, 57364158, 57364159, 57364160, 57364161, 57364162, 57364163, 57364164, 57364165, 57364166, 57364167, 57364168, 57364169, 57364170, 57364171, 57364172, 57364173, 57364174, 57364175, 57364176, 57364177, 57364178, 57364179, 57364180, 57364181, 57364182, 57364183, 57364184, 57364185, 57364186, 57364187, 57364188, 57364189, 57364190, 57364191, 57364192, 57364193, 57364194, 57364195, 57364196, 57364197, 57364198, 57364199, 57364200, 57364201, 57364202, 57364203, 57364204, 57364205, 57364206, 57364207, 57364208, 57364209, 57364210, 57364211, 57364212, 57364213, 57364214, 57364215, 57364216, 57364217, 57364218, 57364219, 57364220, 57364221, 57364222, 57364223, 57364224, 57364225, 57364226, 57364227, 57364228, 57364229, 57364230, 57364231, 57364232, 57364233, 57364234, 57364235, 57364236, 57364237, 57364238, 57364239, 57364240, 57364241, 57364242, 57364243, 57364244, 57364245, 57364246, 57364247, 57364248, 57364249, 57364250, 57364251, 57364252, 57364253, 57364254, 57364255, 57364256, 57364257, 57364258, 57364259, 57364260, 57364261, 57364262, 57364263, 57364264, 57364265, 57364266, 57364267, 57364268, 57364269, 57364270, 57364271, 57364272, 57364273, 57364274, 57364275, 57364276, 57364277, 57364278, 57364279, 57364280, 57364281, 57364282, 57364283, 57364284, 57364285, 57364286, 57364287, 57364288, 57364289, 57364290, 57364291, 57364292, 57364293, 57364294, 57364295, 57364296, 57364297, 57364298, 57364299, 57364300, 57364301, 57364302, 57364303, 57364304, 57364305, 57364306, 57364307, 57364308, 57364309, 57364310, 57364311, 57364312, 57364313, 57364314, 57364315, 57364316, 57364317, 57364318, 57364319, 57364320, 57364321, 57364322, 57364323, 57364324, 57364325, 57364338, 57364339, 57364340, 57364341, 57364342, 57364343, 57364344, 57364345, 57364346, 57364347, 57364348, 57364349, 57364351, 57364441, 57364442, 57364450, 57364452, 57364453, 57364454, 57364455, 57364456, 57364457, 57364458, 57364459, 57364460, 57364461, 57364462, 57364463, 57364464, 57364465, 57364467)
132and cell_id_rti is not null
133and (
134 (start_date <= ${hiveconf:end_period} and eff_end is null)
135 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}));
136
137
138drop table if exists country_ext;
139create table if not exists country_ext stored as PARQUET as
140select dim_country.mcc, dim_country.country as country
141 ,max(case when dim_country.country = 'New Zealand' then dim_country.country
142 when ext.country is null then 'Other' else ext.country end) as country_ext
143from rto_raw.dim_country
144left
145join rto_raw.international_extrap_factors ext on dim_country.country = ext.country
146group by dim_country.mcc, dim_country.country;
147
148
149-- **********************************************************************
150-- Get all the imsi in the area of interest in a given period of time
151-- **********************************************************************
152drop table if exists imsi_by_period;
153create table if not exists imsi_by_period STORED as PARQUET
154as
155select
156fact.imsi_rti
157,fact.date
158,fact.month
159from lbs_agg.fact_rti_summary fact
160JOIN cell_id imsi on imsi.cell_id_rti = fact.cell_id_rti and fact.date between ${hiveconf:start_period} and ${hiveconf:end_period}
161group by fact.imsi_rti
162,fact.date
163,fact.month;
164
165
166-- ************************************************************
167-- flag people living in the area of interest
168-- get the region from all imsi for extrapolation purposes
169-- ************************************************************
170drop table if exists imsi_of_interest;
171create table if not exists imsi_of_interest stored as PARQUET
172as
173with a as (
174select f.*
175,case when cell.cell_id_rti is null then 0 else 1 end AS is_local_flag
176,home.cell_id_rti AS home_cell_id_rti
177,home_cell.region_nz
178,home_cell.rto
179from imsi_by_period f
180left
181join lbs_agg.fact_subscriber_home_location home on home.imsi_rti = f.imsi_rti and home.month = f.month
182left
183JOIN cell_id cell ON home.cell_id_rti = cell.cell_id_rti
184LEFT
185JOIN master.cell_sector_master home_cell ON home.cell_id_rti = home_cell.cell_id_rti)
186select *
187from a;
188
189drop table if exists imsi_of_interest1;
190create table if not exists imsi_of_interest1 stored as PARQUET
191as
192select a.*
193,country_name.country_ext as country_w
194,country_name.country
195from imsi_of_interest a
196left
197join (select imsi_rti,
198 substring(mccmnc, 1, 3) as mcc
199 from lbs_raw.dim_subscriber
200 group by imsi_rti,
201 substring(mccmnc, 1, 3)) subscriber
202 on a.imsi_rti = subscriber.imsi_rti
203left
204join country_ext country_name on subscriber.mcc = country_name.mcc;
205
206
207-- **************************************************************************************************************
208-- we dropped people living in the area with: is_local_flag = 0
209-- Get all the events from people we are interested and flag them if they were in the area of interest or not
210-- **************************************************************************************************************
211drop table if exists all_imsi_events;
212create table if not exists all_imsi_events stored as PARQUET
213as
214select f.imsi_rti
215 ,f.cell_id_rti
216 ,c.grid12
217 ,f.start_date_str
218 ,cast((f.start_date)*1.0 as timestamp) as start_date
219 ,f.end_date_str
220 ,cast((f.end_date)*1.0 as timestamp) as end_date
221 ,f.date
222 ,f.month
223 ,case when c.cell_id_rti is null then 0 else 1 end as in_area_flag
224 ,i.region_nz
225 ,i.country_w
226 ,i.country
227 ,i.is_local_flag
228from lbs_agg.fact_rti_summary f
229join imsi_of_interest1 i on i.imsi_rti = f.imsi_rti and f.date = i.date and f.date between ${hiveconf:start_period} and ${hiveconf:end_period}
230left
231join cell_id c on c.cell_id_rti = f.cell_id_rti;
232
233
234
235-- ******************************************
236-- In some cases the cell_id_rti won't be in the master db because they don't have enough events to keep them in the history
237-- we can delete it for testing purposes
238-- ******************************************
239
240--entrance =0 is in the towns of interest
241--entrance =-1 is outside the area of interest
242
243
244drop table if exists all_events_location;
245create table if not exists all_events_location stored as PARQUET
246as
247with cells as (
248select *
249from master.cell_location
250where cell_id_rti is not null
251and (
252 (start_date <= ${hiveconf:end_period} and eff_end is null)
253 OR (start_date >= ${hiveconf:start_period} and end_date <= ${hiveconf:end_period}))
254)
255select f.imsi_rti
256 ,f.cell_id_rti
257 ,case when f.grid12 is not null then f.grid12 else c.grid12 end as grid12
258 ,hour(f.start_date) as start_hour
259 ,f.start_date
260 ,f.end_date
261 ,f.date
262 ,f.month
263 ,f.in_area_flag
264 ,case when e.grid12 is not null then e.entrance
265 when (e.grid12 is null and f.in_area_flag = 1) then 0 else -1 end as entrance
266 ,t.town as town
267 ,f.region_nz
268 ,f.country_w
269 ,f.country
270 ,f.is_local_flag
271from all_imsi_events f
272join cells c on c.cell_id_rti = f.cell_id_rti
273left
274join entrance e on e.cell_id_rti = f.cell_id_rti
275left
276join grid12_towns_of_interest t on t.cell_id_rti = f.cell_id_rti;
277
278
279
280--HERE TO EXCLUDE LOCALS
281-- *************************************************
282-- filter imsi that moved in and out the area of interest by day
283-- step 1
284-- ************************************************
285
286drop table if exists filter_imsi;
287create table if not exists filter_imsi stored as PARQUET
288as
289with a as (
290select imsi_rti
291,date
292,count(distinct entrance)
293from all_events_location
294where entrance <> -1
295group by imsi_rti
296,date
297having count(distinct entrance)>1)
298select distinct imsi_rti, date
299from a;
300
301-- *************************************************
302-- filter imsi that moved in and out the area of interest
303-- step 2
304-- ************************************************
305drop table if exists all_events_location_filtered;
306create table if not exists all_events_location_filtered stored as PARQUET
307as
308select a.*
309from all_events_location a
310join filter_imsi f on f.imsi_rti = a.imsi_rti and f.date = a.date;
311
312-- **********************************************************************************
313-- bring only events in the area of interest
314-- flag rows that are in the same grid12 (this means imsi's stay in that place )
315-- **********************************************************************************e
316
317drop table if exists all_events_location_with_grid;
318create table if not exists all_events_location_with_grid stored as PARQUET
319as
320select
321case when (lag(grid12) over (partition by imsi_rti order by start_date) = grid12) or
322(lead(grid12) over (partition by imsi_rti order by start_date) = grid12) then 1 else 0 end as is_same_grid12
323,*
324from all_events_location_filtered
325where entrance <> -1;
326
327drop table if exists all_events_location_with_grid1;
328create table if not exists all_events_location_with_grid1 stored as PARQUET
329as
330select
331case when (lag(grid12) over (partition by imsi_rti order by start_date) = grid12) then 1 else 0 end as is_same_grid12
332,*
333from all_events_location_filtered
334where entrance <> -1;
335
336
337
338-- **********************************************************************************
339-- DWELL TIME (WE ARE INTERESTED IN GRID12 WITH MORE THAN 1 EVENT)
340-- EXCLUDE THE LOCALS
341-- **********************************************************************************
342drop table if exists duration;
343create table if not exists duration stored as PARQUET
344as
345select imsi_rti
346,grid12
347,is_same_grid12
348,start_hour
349,min(start_date) as start_date
350,max(end_date) as end_date
351,date
352,month
353,entrance
354,town
355,region_nz
356,country_w
357,country
358,is_local_flag
359from all_events_location_with_grid
360where is_local_flag = 0
361group by imsi_rti
362,grid12
363,is_same_grid12
364,start_hour
365,date
366,month
367,entrance
368,town
369,region_nz
370,country_w
371,country
372,is_local_flag;
373
374-- *********************************************************
375-- FIRST AND LAST EVENT WITHIN THE AREA OF INTEREST
376-- STEP 1
377-- *********************************************************
378
379drop table if exists first_event_in_area;
380create table if not exists first_event_in_area stored as PARQUET
381as
382with a as (
383select imsi_rti
384,date
385,min(start_date) as first_event
386,max(end_date) as last_event
387from duration
388where entrance = 0
389group by imsi_rti
390,date)
391select f.*
392,case when fe.imsi_rti is not null then 1 else 0 end as in_area
393,case when le.imsi_rti is not null then 1 else 0 end as out_area
394from duration f
395left
396join a fe on fe.imsi_rti = f.imsi_rti and fe.first_event = f.start_date and fe.date = f.date
397left
398join a le on le.imsi_rti = f.imsi_rti and le.last_event = f.end_date and le.date = f.date;
399
400-- *********************************************************
401-- FIRST AND LAST EVENT WITHIN THE AREA OF INTEREST
402-- STEP 2
403-- *********************************************************
404
405drop table if exists imsi_origin_destination;
406create table if not exists imsi_origin_destination stored as PARQUET
407as
408with a as (
409select *
410,case
411when lead(in_area) over (partition by imsi_rti order by start_date) = 1 then 'Origin'
412when lag(out_area) over (partition by imsi_rti order by start_date) = 1 then 'Destination'
413else null end as Location
414from first_event_in_area)
415select *
416from a;
417
418-- ************************************************
419-- if the imsi wakes up in the area we have to assign origin to the first event and destination to the last event
420-- ************************************************
421drop table if exists finalOutput;
422create table if not exists finalOutput stored as PARQUET
423as
424with origin as (
425select imsi_rti
426,date
427,min(start_date) as first_event
428from imsi_origin_destination
429where location is null
430group by imsi_rti
431,date),
432destination as (
433select imsi_rti
434,date
435,max(start_date) as last_event
436from imsi_origin_destination
437where location is null
438group by imsi_rti
439,date)
440select
441f.imsi_rti
442,f.grid12
443,f.is_same_grid12
444,f.start_hour
445,f.start_date
446,f.end_date
447,f.date
448,f.month
449,f.entrance
450,f.town
451,f.region_nz
452,f.country_w
453,f.country
454,f.in_area
455,f.out_area
456,f.is_local_flag
457,case when o.imsi_rti is not null and f.location is null then 'Origin'
458when d.imsi_rti is not null and f.location is null then 'Destination'
459when f.town is not null and f.location is null then f.town
460else f.location end as location
461from imsi_origin_destination f
462left
463join origin o on o.imsi_rti = f.imsi_rti and o.first_event = f.start_date and f.entrance = 0 and o.date = f.date
464left
465join destination d on d.imsi_rti = f.imsi_rti and d.last_event = f.start_date and f.entrance = 0 and d.date = f.date;
466
467-----WHO HAS THE SAME ENTRANCE AS ORIGIN AND DESTINATION
468drop table if exists same_origin_destination;
469create table if not exists same_origin_destination stored as PARQUET as
470with origin as (
471select imsi_rti
472,entrance
473,date
474from finaloutput
475where location = 'Origin'
476group by imsi_rti
477,entrance
478,date),
479destination as (
480select imsi_rti
481,entrance
482,date
483from finaloutput
484where location = 'Destination'
485group by imsi_rti
486,entrance
487,date)
488select o.*
489from origin o
490join destination d on d.imsi_rti = o.imsi_rti and d.entrance = o.entrance and o.date = d.date;
491
492
493
494drop table if exists finalOutput1;
495create table if not exists finalOutput1 stored as PARQUET
496as
497select f.*
498from finaloutput f
499left
500join same_origin_destination s on s.imsi_rti = f.imsi_rti and s.date = f.date
501where s.imsi_rti is null;
502
503-- *************************************************************************
504-- This query is to clean imsi's without origin or destination
505-- maybe we don't have enough events to calculate this
506-- *************************************************************************
507
508drop table if exists imsis_with_origin_destination;
509create table if not exists imsis_with_origin_destination stored as PARQUET
510as
511with a as (
512select imsi_rti
513,location
514,date
515from finaloutput1
516where location is not NULL
517group by imsi_rti
518,location
519,date)
520select imsi_rti
521,date
522,count(distinct location)
523from a
524group by imsi_rti
525,date
526having count(distinct location)>=2;
527
528
529-- *************************************************************************
530-- Get rid of the null locations
531-- *************************************************************************
532
533drop table if exists finaloutput2;
534create table if not exists finaloutput2 stored as PARQUET
535as
536with a as (
537select f.*
538from finaloutput1 f
539join imsis_with_origin_destination a on a.imsi_rti = f.imsi_rti and a.date = f.date
540where f.location is not null)
541select imsi_rti
542,min(start_date) as start_date
543,max(end_date) as end_date
544,date
545,month
546,entrance
547,location
548,town
549,region_nz
550,country_w
551,country
552,is_local_flag
553from a
554group by imsi_rti
555,date
556,month
557,entrance
558,location
559,town
560,region_nz
561,country_w
562,country
563,is_local_flag;
564
565-- *************************************************************************
566-- FINAL TABLE
567-- *************************************************************************
568
569drop table if exists finaloutput_agg1;
570create table if not exists finaloutput_agg1 stored as PARQUET
571as
572with a as (
573select f.imsi_rti
574,f.date
575,case when f.location = 'Origin' and f.entrance = 0 and f.town is not null then f.town
576when f.location = 'Origin' and f.entrance = 0 and f.town is null then 'AoI'
577when f.location = 'Origin' and f.entrance <> 0 then f.entrance
578end as origin
579,case when f.location = 'Destination' and f.entrance = 0 and f.town is not null then f.town
580when f.location = 'Destination' and f.entrance = 0 and f.town is null then 'AoI'
581when f.location = 'Destination' and f.entrance <> 0 then f.entrance
582end as destination
583,case when f.location='Levin' then 1 else 0 end as through_levi
584,case when f.location='Palmerston North' then 1 else 0 end as through_pm
585,case when f.location='Levin' then (unix_timestamp(f.end_date) - unix_timestamp(f.start_date))/60 else 0 end as duration_levin
586,case when f.location='Palmerston North' then (unix_timestamp(f.end_date) - unix_timestamp(f.start_date))/60 else 0 end as duration_pm
587,f.region_nz
588,f.country_w
589,f.country
590,f.is_local_flag
591from finaloutput2 f)
592select imsi_rti
593,date
594,max(origin) as origin
595,max(destination) as destination
596,max(through_levi) as through_levi
597,max(through_pm) as through_pm
598,max(region_nz) as region_nz
599,max(country_w) as country_w
600,max(country) as country
601,max(is_local_flag) as is_local_flag
602,sum(duration_levin) as duration_levin
603,sum(duration_pm) as duration_pm
604from a
605group by imsi_rti
606,date;
607
608
609-- *************************************************************
610-- BUSINESS LOGIC TO DEFINE ORIGIN - DESTINATION ROUTES
611-- *************************************************************
612
613drop table if exists finaloutput_agg2;
614create table if not exists finaloutput_agg2 stored as PARQUET
615as
616with f as (
617select f.imsi_rti
618,f.date
619,f.origin
620,f.destination
621,f.region_nz
622,f.country_w
623,f.country
624,f.is_local_flag
625,case
626when f.origin in (1,'Levin') and f.destination in (2,3, 'AoI') and f.through_pm = 0 then 1
627when f.origin in (1,'Levin') and f.destination in ('Palmerston North') then 1
628when f.origin in (2,3,'AoI') and f.destination in (1,'Levin') and f.through_pm = 0 then 1
629when f.origin in ('Palmerston North') and f.destination in (1,'Levin') then 1
630else 0 end as through_levi
631,case
632when f.origin in (2,3,4,5) and f.destination in (1,'Levin') and f.through_pm = 1 then 1
633when f.origin in (2,3,4) and f.destination = 5 then 1
634when f.origin = 5 and f.destination in (2,3,4) then 1
635when f.origin in (1,'Levin') and f.destination in (2,3) and f.through_pm = 1 then 1
636when f.origin in (1,'Levin') and f.destination in (4,5) then 1
637else 0 end as through_pm
638,case when duration_levin >= 30 then 1 else 0 end as stayed_in_levin
639,case when duration_pm >= 45 then 1 else 0 end as stayed_in_pm
640from finaloutput_agg1 f)
641select f.imsi_rti
642,f.date
643,f.region_nz
644,f.country_w
645,f.country
646,f.is_local_flag
647,case when f.through_pm = 1 and f.origin in (1,'Levin') then 'Levin'
648when f.through_levi = 1 and f.origin in (1,'Levin') then 1
649else f.origin
650end as origin
651,case
652when f.through_pm = 1 and f.destination in (1,'Levin') then 'Levin'
653when f.through_levi = 1 and f.destination in (1,'Levin') then 1
654else f.destination end as destination
655,f.through_levi
656,f.through_pm
657,f.stayed_in_levin
658,f.stayed_in_pm
659from f;
660
661-- *************************************
662-- EXTRAPOLATE NUMBERS AND GET ORIGIN TO GET FINAL TABLE
663-- *************************************
664
665drop table if exists finaloutput_agg3_no_locals;
666create table if not exists finaloutput_agg3_no_locals stored as PARQUET
667as
668select f.*
669,case when f.region_nz is null then 1 else 0 end as is_international
670,case when f.country = 'New Zealand' then 1 / dom_ext.share
671else 1/int_ext.extrap_factor end as weight
672from finaloutput_agg2 f
673left
674join master.spark_market_share_by_nz_region dom_ext on dom_ext.month = ${hiveconf:period} and dom_ext.region = f.region_nz
675left
676join rto_raw.international_extrap_factors int_ext on int_ext.month = ${hiveconf:period} and int_ext.country = f.country_w;
677
678
679
680-- ********************************************
681-- Get locals, they are treated differently
682-- ********************************************
683
684drop table if exists all_imsi_events_locals;
685create table if not exists all_imsi_events_locals stored as PARQUET
686as
687select *
688from all_events_location_with_grid1
689where is_local_flag = 1;
690
691-- **********************
692-- flag boucing records
693-- ***********************
694drop table if exists boucing_movements;
695create table if not exists boucing_movements stored as PARQUET as
696with a as (
697select *
698from all_imsi_events_locals
699where is_same_grid12 = 1)
700select *,
701 case when lag(entrance) over (partition by imsi_rti order by start_date) <> entrance and
702 entrance <> lead(entrance) over (partition by imsi_rti order by start_date)
703 then 1 else 0 end as row_to_delete
704from a;
705
706-- **********************
707-- delete boucing records
708-- ***********************
709
710drop table if exists boucing_movements1;
711create table if not exists boucing_movements1 stored as PARQUET as
712with a as (
713select *
714from boucing_movements
715where row_to_delete = 0)
716select *
717from a;
718
719-- ******************
720-- Group records by grid, entrance
721-- ******************
722drop table if exists duration_locals;
723create table if not exists duration_locals stored as PARQUET
724as
725select imsi_rti
726,grid12
727,is_same_grid12
728,start_hour
729,min(start_date) as start_date
730,max(end_date) as end_date
731,date
732,month
733,entrance
734,town
735,region_nz
736,country_w
737,country
738,is_local_flag
739from boucing_movements1
740group by imsi_rti
741,grid12
742,is_same_grid12
743,start_hour
744,date
745,month
746,entrance
747,town
748,region_nz
749,country_w
750,country
751,is_local_flag;
752
753-- **********************
754-- delete boucing records
755-- and redefine is_same_grid
756-- ***********************
757
758drop table if exists duration_locals1;
759create table if not exists duration_locals1 stored as PARQUET
760as
761with a as (
762select *,
763 case when lag(entrance) over (partition by imsi_rti order by start_date) <> entrance and
764 lag(entrance) over (partition by imsi_rti order by start_date) = lead(entrance) over (partition by imsi_rti order by start_date) then 1 else 0 end as row_to_delete
765from duration_locals)
766select *,
767case when entrance = 0 and lag(entrance) over(partition by imsi_rti order by start_date) <> 0 then 1 else 0 end as in_area
768,case when entrance = 0 and lead(entrance) over(partition by imsi_rti order by start_date) <> 0 then 1 else 0 end as out_area
769from a
770where row_to_delete = 0;
771
772drop table if exists imsi_origin_destination_locals;
773create table if not exists imsi_origin_destination_locals stored as PARQUET
774as
775with a as (
776select *
777,case
778when lead(in_area) over (partition by imsi_rti order by start_date) = 1 then 'Origin'
779when lag(out_area) over (partition by imsi_rti order by start_date) = 1 then 'Destination'
780else null end as Location
781from duration_locals1)
782select *
783from a;
784
785
786-- ************************************************
787-- if the imsi wakes up in the area we have to assign origin to the first event and destination to the last event
788-- ************************************************
789drop table if exists finalOutput_locals;
790create table if not exists finalOutput_locals stored as PARQUET
791as
792with origin as (
793select imsi_rti
794,date
795,min(start_date) as first_event
796from imsi_origin_destination_locals
797where location is null
798group by imsi_rti
799,date),
800destination as (
801select imsi_rti
802,date
803,max(start_date) as last_event
804from imsi_origin_destination_locals
805where location is null
806group by imsi_rti
807,date)
808select
809f.imsi_rti
810,f.grid12
811,f.is_same_grid12
812,f.start_hour
813,f.start_date
814,f.end_date
815,f.date
816,f.month
817,f.entrance
818,f.town
819,f.region_nz
820,f.country_w
821,f.country
822,f.in_area
823,f.out_area
824,f.is_local_flag
825,case when o.imsi_rti is not null and f.location is null then 'Origin'
826when d.imsi_rti is not null and f.location is null then 'Destination'
827when f.town is not null and f.location is null then f.town
828else f.location end as location
829from imsi_origin_destination_locals f
830left
831join origin o on o.imsi_rti = f.imsi_rti and o.first_event = f.start_date and f.entrance = 0 and o.date = f.date
832left
833join destination d on d.imsi_rti = f.imsi_rti and d.last_event = f.start_date and f.entrance = 0 and d.date = f.date;
834
835-- *************************************************************************
836-- Get rid of the null locations
837-- *************************************************************************
838drop table if exists finalOutput2_locals;
839create table if not exists finalOutput2_locals stored as PARQUET
840as
841select imsi_rti
842,min(start_date) as start_date
843,max(end_date) as end_date
844,date
845,month
846,entrance
847,location
848,town
849,region_nz
850,country_w
851,country
852,is_local_flag
853from finalOutput_locals
854where location is not null
855group by imsi_rti
856,date
857,month
858,entrance
859,location
860,town
861,region_nz
862,country_w
863,country
864,is_local_flag;
865
866
867drop table if exists finalOutput3_locals;
868create table if not exists finalOutput3_locals stored as PARQUET
869as
870with ranking as (
871select *, rank() over (partition by imsi_rti, date, location order by start_date) as rank
872from finalOutput2_locals
873),
874grouping as (
875select
876imsi_rti, date, rank,
877min(start_date) as min_start_date,
878max(start_date) as max_start_date
879from ranking
880where location in ('Origin', 'Destination')
881group by imsi_rti, date, rank
882)
883select a.*, b.rank as rank_all
884from ranking a
885inner join grouping b
886 on a.imsi_rti = b.imsi_rti and a.date = b.date
887where start_date between min_start_date and max_start_date;
888
889
890-- *************************************************************************
891-- FINAL TABLE
892-- *************************************************************************
893drop table if exists finaloutput_agg1_locals;
894create table if not exists finaloutput_agg1_locals stored as PARQUET
895as
896with a as (
897select f.imsi_rti
898,f.date
899,f.rank_all
900,case when f.location = 'Origin' and f.entrance = 0 and f.town is not null then f.town
901when f.location = 'Origin' and f.entrance = 0 and f.town is null then 'AoI'
902when f.location = 'Origin' and f.entrance <> 0 then f.entrance
903end as origin
904,case when f.location = 'Destination' and f.entrance = 0 and f.town is not null then f.town
905when f.location = 'Destination' and f.entrance = 0 and f.town is null then 'AoI'
906when f.location = 'Destination' and f.entrance <> 0 then f.entrance
907end as destination
908,case when f.location='Levin' then 1 else 0 end as through_levi
909,case when f.location='Palmerston North' then 1 else 0 end as through_pm
910,case when f.location='Levin' then (unix_timestamp(f.end_date) - unix_timestamp(f.start_date))/60 else 0 end as duration_levin
911,case when f.location='Palmerston North' then (unix_timestamp(f.end_date) - unix_timestamp(f.start_date))/60 else 0 end as duration_pm
912,f.region_nz
913,f.country_w
914,f.country
915,f.is_local_flag
916from finalOutput3_locals f)
917select imsi_rti
918,date
919,rank_all
920,max(origin) as origin
921,max(destination) as destination
922,max(through_levi) as through_levi
923,max(through_pm) as through_pm
924,max(region_nz) as region_nz
925,max(country_w) as country_w
926,max(country) as country
927,max(is_local_flag) as is_local_flag
928,sum(duration_levin) as duration_levin
929,sum(duration_pm) as duration_pm
930from a
931group by imsi_rti
932,date
933,rank_all;
934
935
936-- *************************************************************
937-- BUSINESS LOGIC TO DEFINE ORIGIN - DESTINATION ROUTES
938-- *************************************************************
939
940drop table if exists finaloutput_agg2_locals;
941create table if not exists finaloutput_agg2_locals stored as PARQUET
942as
943with f as (
944select f.imsi_rti
945,f.date
946,f.origin
947,f.destination
948,f.region_nz
949,f.country_w
950,f.country
951,f.is_local_flag
952,case
953when f.origin in (1,'Levin') and f.destination in (2,3, 'AoI') and f.through_pm = 0 then 1
954when f.origin in (1,'Levin') and f.destination in ('Palmerston North') then 1
955when f.origin in (2,3,'AoI') and f.destination in (1,'Levin') and f.through_pm = 0 then 1
956when f.origin in ('Palmerston North') and f.destination in (1,'Levin') then 1
957else 0 end as through_levi
958,case
959when f.origin in (2,3,4,5) and f.destination in (1,'Levin') and f.through_pm = 1 then 1
960when f.origin in (2,3,4) and f.destination = 5 then 1
961when f.origin = 5 and f.destination in (2,3,4) then 1
962when f.origin in (1,'Levin') and f.destination in (2,3) and f.through_pm = 1 then 1
963when f.origin in (1,'Levin') and f.destination in (4,5) then 1
964else 0 end as through_pm
965,case when duration_levin >= 30 then 1 else 0 end as stayed_in_levin
966,case when duration_pm >= 45 then 1 else 0 end as stayed_in_pm
967from finaloutput_agg1_locals f)
968select f.imsi_rti
969,f.date
970,f.region_nz
971,f.country_w
972,f.country
973,f.is_local_flag
974,case when f.through_pm = 1 and f.origin in (1,'Levin') then 'Levin'
975when f.through_levi = 1 and f.origin in (1,'Levin') then 1
976else f.origin
977end as origin
978,case
979when f.through_pm = 1 and f.destination in (1,'Levin') then 'Levin'
980when f.through_levi = 1 and f.destination in (1,'Levin') then 1
981else f.destination end as destination
982,f.through_levi
983,f.through_pm
984,f.stayed_in_levin
985,f.stayed_in_pm
986from f;
987
988-- *************************************
989-- EXTRAPOLATE NUMBERS AND GET ORIGIN TO GET FINAL TABLE
990-- *************************************
991
992drop table if exists finaloutput_agg3_locals;
993create table if not exists finaloutput_agg3_locals stored as PARQUET
994as
995select f.*
996,case when f.region_nz is null then 1 else 0 end as is_international
997,case when f.country = 'New Zealand' then 1 / dom_ext.share
998else 1/int_ext.extrap_factor end as weight
999from finaloutput_agg2_locals f
1000left
1001join master.spark_market_share_by_nz_region dom_ext on dom_ext.month = ${hiveconf:period} and dom_ext.region = f.region_nz
1002left
1003join rto_raw.international_extrap_factors int_ext on int_ext.month = ${hiveconf:period} and int_ext.country = f.country_w;
1004
1005
1006
1007-- *************************
1008-- FINAL REPORTING TABLE
1009-- ***************************
1010
1011drop table if exists finaloutput_agg3;
1012create table if not exists finaloutput_agg3 stored as PARQUET
1013as
1014select *
1015from finaloutput_agg3_no_locals
1016
1017union all
1018
1019select *
1020from finaloutput_agg3_locals;