· 5 years ago · Feb 20, 2020, 04:18 PM
1CREATE TABLE IF NOT EXISTS fonia_ddl_cella ( a_msisdn varchar(100), a_tac varchar(8), a_imsi varchar(100), a_tipo varchar(1), flag_cc varchar(2), a_marcaggio varchar(1), a_prov_res varchar(4), mcc_mnc varchar(6), subscription_type varchar(3), gruppo_t varchar(6), k_ope_orig varchar(38), k_paese_orig varchar(38), b_msisdn varchar(100), b_tac varchar(8), b_imsi varchar(100), b_tipo varchar(1), b_flag_cc varchar(2), b_marcaggio varchar(1), b_prov_res varchar(4), k_direttrice varchar(38), k_ope_dest varchar(38), k_paese_dest varchar(38), direttrice_t varchar(6), macrodir varchar(5), b_gruppo_bit varchar(4), a_gruppo_bit varchar(4), data_inizio date, data_inizio_conference date, data_inizio_media_change date, data_inizio_srvcc date, durata_2 int, fascia_oraria varchar(2), ora_inizio_orig varchar(35), ora_inizio_conference varchar(6), ora_inizio_media_change varchar(6), ora_inizio_srvcc varchar(6), tipo_giorno varchar(1), data_produzione_edl date, record_origine varchar(2), nome_acq varchar(20), call_position varchar(1), k_tipo_traffico varchar(38), cin varchar(100), network_call_reference varchar(10), ricostruita varchar(1), numero_parziali varchar(3), msc_code varchar(6), cgi varchar(8), last_cgi varchar(8), tac_eci varchar(12), o_route varchar(7), i_route varchar(7), if_human varchar(1), flag_gos varchar(1), flag_nr varchar(1), flag_rievo varchar(1), flag_servizi varchar(2), flag_tt varchar(1), flag_tt_best varchar(1), service_feature_code varchar(3), internal_cause_and_location varchar(5), disconnecting_party varchar(1), cause_code varchar(8), fault_code varchar(5), ranap_cc varchar(3), bssmap_cc varchar(4), conference_id varchar(100), destrealm varchar(30), sipringingdate varchar(8), sipringingtime varchar(6), list_of_media varchar(100), list_of_media_change varchar(100), direzione varchar(1) )
2
3PARTITIONED BY (partition_date date)
4ROW FORMAT
5SERDE
6 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
7STORED AS INPUTFORMAT
8 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
9OUTPUTFORMAT
10 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';
11
12INSERT OVERWRITE TABLE fonia_ddl_cella
13PARTITION (partition_date = ${hivevar:data_elaborazione})
14
15SELECT
16 CONCAT(TRIM(a_ac), TRIM(a_sn)) AS a_msisdn,
17 SUBSTR(a_imei, 1, 8) AS a_tac,
18 a_imsi,
19 a_tipo,
20 flag_cc,
21 a_marcaggio,
22 a_prov_res,
23 mcc_mnc,
24 subscription_type,
25 gruppo_t,
26 k_ope_orig,
27 k_paese_orig,
28 CONCAT(TRIM(b_ac), TRIM(b_sn)) AS b_msisdn,
29 SUBSTR(b_imei, 1, 8) AS b_tac,
30 b_imsi,
31 b_tipo,
32 b_flag_cc,
33 b_marcaggio,
34 b_prov_res,
35 k_direttrice,
36 k_ope_dest,
37 k_paese_dest,
38 direttrice_t,
39 macrodir,
40 b_gruppo_bit,
41 a_gruppo_bit,
42 data_inizio,
43 data_inizio_conference,
44 data_inizio_media_change,
45 data_inizio_srvcc,
46 durata_2,
47 fascia_oraria,
48 ora_inizio_orig,
49 ora_inizio_conference,
50 ora_inizio_media_change,
51 ora_inizio_srvcc,
52 tipo_giorno,
53 CURRENT_DATE() AS data_produzione_edl,
54 record_origine,
55 nome_acq,
56 call_position,
57 k_tipo_traffico,
58 cin,
59 network_call_reference,
60 ricostruita,
61 numero_parziali,
62 msc_code,
63 cgi,
64 last_cgi,
65 tac_eci,
66 o_route,
67 i_route,
68 if_human,
69 flag_gos,
70 flag_nr,
71 flag_rievo,
72 flag_servizi,
73 flag_tt,
74 flag_tt_best,
75 service_feature_code,
76 internal_cause_and_location,
77 disconnecting_party,
78 cause_code,
79 fault_code,
80 ranap_cc,
81 bssmap_cc,
82 conference_id,
83 destrealm,
84 sipringingdate,
85 sipringingtime,
86 list_of_media,
87 list_of_media_change,
88 direzione
89FROM (
90SELECT s_gsm_ml_raw.a_ac,s_gsm_ml_raw.a_sn,s_gsm_ml_raw.a_imei,s_gsm_ml_raw.a_imsi,s_gsm_ml_raw.a_tipo,s_gsm_ml_raw.flag_cc,s_gsm_ml_raw.a_marcaggio,s_gsm_ml_raw.a_prov_res,s_gsm_ml_raw.mcc_mnc,s_gsm_ml_raw.subscription_type,s_gsm_ml_raw.gruppo_t,s_gsm_ml_raw.k_ope_orig,s_gsm_ml_raw.k_paese_orig,s_gsm_ml_raw.b_ac,s_gsm_ml_raw.b_sn,cast(null AS varchar(100)) as b_imei,s_gsm_ml_raw.b_imsi,cast(null AS varchar(1)) as b_tipo,cast(null AS varchar(2)) as b_flag_cc,s_gsm_ml_raw.b_marcaggio,cast(null AS varchar(4)) as b_prov_res,s_gsm_ml_raw.k_direttrice,s_gsm_ml_raw.k_ope_dest,s_gsm_ml_raw.k_paese_dest,s_gsm_ml_raw.direttrice_t,s_gsm_ml_raw.macrodir,s_gsm_ml_raw.b_gruppo_bit,s_gsm_ml_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_gsm_ml_raw.data_inizio, case when substr(cast(s_gsm_ml_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_gsm_ml_raw.data_produzione,s_gsm_ml_raw.durata_2,s_gsm_ml_raw.fascia_oraria,s_gsm_ml_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_gsm_ml_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_gsm_ml_raw.record_origine,s_gsm_ml_raw.nome_acq,s_gsm_ml_raw.call_position,s_gsm_ml_raw.k_tipo_traffico,s_gsm_ml_raw.cin,s_gsm_ml_raw.network_call_reference,s_gsm_ml_raw.ricostruita,s_gsm_ml_raw.numero_parziali,s_gsm_ml_raw.msc_code,s_gsm_ml_raw.cgi,s_gsm_ml_raw.last_cgi,cast(null AS varchar(12)) as tac_eci,s_gsm_ml_raw.o_route,cast(null AS varchar(7)) as i_route,s_gsm_ml_raw.if_human,s_gsm_ml_raw.flag_gos,s_gsm_ml_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,s_gsm_ml_raw.flag_servizi,s_gsm_ml_raw.flag_tt,s_gsm_ml_raw.flag_tt_best,cast(null AS varchar(3)) as service_feature_code,s_gsm_ml_raw.internal_cause_and_location,s_gsm_ml_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,s_gsm_ml_raw.fault_code,s_gsm_ml_raw.ranap_cc,s_gsm_ml_raw.bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"O" AS direzione
91FROM ${hivevar:input_database}.s_gsm_ml_raw
92WHERE s_gsm_ml_raw.partition_date=${hivevar:data_elaborazione}
93
94UNION ALL
95SELECT s_ri_o_raw.a_ac,s_ri_o_raw.a_sn,s_ri_o_raw.a_imei,s_ri_o_raw.a_imsi,s_ri_o_raw.a_tipo,s_ri_o_raw.flag_cc,s_ri_o_raw.a_marcaggio,s_ri_o_raw.a_prov_res,s_ri_o_raw.mcc_mnc,s_ri_o_raw.subscription_type,s_ri_o_raw.gruppo_t,s_ri_o_raw.k_ope_orig,s_ri_o_raw.k_paese_orig,s_ri_o_raw.b_ac,s_ri_o_raw.b_sn,cast(null AS varchar(100)) as b_imei,s_ri_o_raw.b_imsi,s_ri_o_raw.b_tipo,s_ri_o_raw.b_flag_cc,s_ri_o_raw.b_marcaggio,cast(null AS varchar(4)) as b_prov_res,s_ri_o_raw.k_direttrice,s_ri_o_raw.k_ope_dest,s_ri_o_raw.k_paese_dest,s_ri_o_raw.direttrice_t,s_ri_o_raw.macrodir,s_ri_o_raw.b_gruppo_bit,s_ri_o_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_ri_o_raw.data_inizio, case when substr(cast(s_ri_o_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_ri_o_raw.data_produzione,s_ri_o_raw.durata_2,s_ri_o_raw.fascia_oraria,s_ri_o_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_ri_o_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_ri_o_raw.record_origine,s_ri_o_raw.nome_acq,s_ri_o_raw.call_position,s_ri_o_raw.k_tipo_traffico,s_ri_o_raw.cin,s_ri_o_raw.network_call_reference,s_ri_o_raw.ricostruita,s_ri_o_raw.numero_parziali,s_ri_o_raw.msc_code,s_ri_o_raw.cgi,IF (s_ri_o_raw.flag_rievo=1, s_ri_o_raw.last_cgi, s_gsm_ml_agg_raw.last_cgi) AS last_cgi,cast(null AS varchar(12)) as tac_eci,s_ri_o_raw.o_route,s_ri_o_raw.i_route,s_ri_o_raw.if_human,s_ri_o_raw.flag_gos,s_ri_o_raw.flag_nr,s_ri_o_raw.flag_rievo,s_ri_o_raw.flag_servizi,s_ri_o_raw.flag_tt,s_ri_o_raw.flag_tt_best,s_ri_o_raw.service_feature_code,s_ri_o_raw.internal_cause_and_location,s_ri_o_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,s_ri_o_raw.fault_code,s_ri_o_raw.ranap_cc,s_ri_o_raw.bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"O" AS direzione
96FROM (SELECT * FROM
97${hivevar:input_database}.s_ri_o_raw
98 WHERE s_ri_o_raw.partition_date=${hivevar:data_elaborazione}
99)
100s_ri_o_raw
101LEFT JOIN
102 (
103 SELECT *
104 FROM (
105 SELECT *,
106 ROW_NUMBER() over (partition by network_call_reference, CONCAT(trim(s_gsm_ml_agg_raw.a_ac), trim(s_gsm_ml_agg_raw.a_sn))) as rnk
107 FROM ${hivevar:input_database}.s_gsm_ml_agg_raw
108 WHERE s_gsm_ml_agg_raw.partition_date=${hivevar:data_elaborazione}
109 AND TRIM(NVL(last_cgi, "")) != ""
110 ) tmp
111 WHERE rnk = 1
112 ) s_gsm_ml_agg_raw
113 ON (trim(s_ri_o_raw.network_call_reference) = trim(s_gsm_ml_agg_raw.network_call_reference) and
114 CONCAT(trim(s_ri_o_raw.a_ac), trim(s_ri_o_raw.a_sn)) = CONCAT(trim(s_gsm_ml_agg_raw.a_ac), trim(s_gsm_ml_agg_raw.a_sn)))
115
116UNION ALL
117SELECT s_gsm_camel_raw.a_ac,s_gsm_camel_raw.a_sn,s_gsm_camel_raw.a_imei,s_gsm_camel_raw.a_imsi,s_gsm_camel_raw.a_tipo,s_gsm_camel_raw.flag_cc,s_gsm_camel_raw.a_marcaggio,s_gsm_camel_raw.a_prov_res,cast(null AS varchar(6)) as mcc_mnc,s_gsm_camel_raw.subscription_type,s_gsm_camel_raw.gruppo_t,s_gsm_camel_raw.k_ope_orig,s_gsm_camel_raw.k_paese_orig,s_gsm_camel_raw.b_ac,s_gsm_camel_raw.b_sn,cast(null AS varchar(100)) as b_imei,s_gsm_camel_raw.b_imsi,cast(null AS varchar(1)) as b_tipo,cast(null AS varchar(2)) as b_flag_cc,s_gsm_camel_raw.b_marcaggio,cast(null AS varchar(4)) as b_prov_res,s_gsm_camel_raw.k_direttrice,s_gsm_camel_raw.k_ope_dest,s_gsm_camel_raw.k_paese_dest,s_gsm_camel_raw.direttrice_t,s_gsm_camel_raw.macrodir,s_gsm_camel_raw.b_gruppo_bit,s_gsm_camel_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_gsm_camel_raw.data_inizio, case when substr(cast(s_gsm_camel_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_gsm_camel_raw.data_produzione,s_gsm_camel_raw.durata_2,s_gsm_camel_raw.fascia_oraria,s_gsm_camel_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_gsm_camel_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_gsm_camel_raw.record_origine,s_gsm_camel_raw.nome_acq,s_gsm_camel_raw.call_position,s_gsm_camel_raw.k_tipo_traffico,s_gsm_camel_raw.cin,s_gsm_camel_raw.network_call_reference,s_gsm_camel_raw.ricostruita,s_gsm_camel_raw.numero_parziali,s_gsm_camel_raw.msc_code,s_gsm_camel_raw.cgi,s_gsm_camel_raw.last_cgi,cast(null AS varchar(12)) as tac_eci,s_gsm_camel_raw.o_route,s_gsm_camel_raw.i_route,s_gsm_camel_raw.if_human,s_gsm_camel_raw.flag_gos,s_gsm_camel_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,cast(null AS varchar(2)) as flag_servizi,s_gsm_camel_raw.flag_tt,s_gsm_camel_raw.flag_tt_best,cast(null AS varchar(3)) as service_feature_code,s_gsm_camel_raw.internal_cause_and_location,s_gsm_camel_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,s_gsm_camel_raw.fault_code,s_gsm_camel_raw.ranap_cc,s_gsm_camel_raw.bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"O" AS direzione
118FROM ${hivevar:input_database}.s_gsm_camel_raw
119WHERE s_gsm_camel_raw.partition_date=${hivevar:data_elaborazione} AND TRIM(s_gsm_camel_raw.subscription_type) not in ("41","42","43","44")
120
121UNION ALL
122SELECT s_ims_orig_raw.a_ac,s_ims_orig_raw.a_sn,s_ims_orig_raw.a_imei,s_ims_orig_raw.a_imsi,s_ims_orig_raw.a_tipo,s_ims_orig_raw.flag_cc,s_ims_orig_raw.a_marcaggio,s_ims_orig_raw.a_prov_res,s_ims_orig_raw.mcc_mnc,cast(null AS varchar(3)) as subscription_type,s_ims_orig_raw.gruppo_t,cast(null AS varchar(38)) as k_ope_orig,cast(null AS varchar(38)) as k_paese_orig,s_ims_orig_raw.b_ac,s_ims_orig_raw.b_sn,s_ims_orig_raw.b_imei,s_ims_orig_raw.b_imsi,s_ims_orig_raw.b_tipo,s_ims_orig_raw.b_flag_cc,s_ims_orig_raw.b_marcaggio,s_ims_orig_raw.b_prov_res,s_ims_orig_raw.k_direttrice,cast(null AS varchar(38)) as k_ope_dest,cast(null AS varchar(38)) as k_paese_dest,s_ims_orig_raw.direttrice_t,s_ims_orig_raw.macrodir,s_ims_orig_raw.b_gruppo_bit,s_ims_orig_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_ims_orig_raw.data_inizio, case when substr(cast(s_ims_orig_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(to_date(from_unixtime(unix_timestamp(s_ims_orig_raw.data_inizio_conference, case when substr(cast(s_ims_orig_raw.data_inizio_conference as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio_conference,cast(to_date(from_unixtime(unix_timestamp(s_ims_orig_raw.data_inizio_media_change, case when substr(cast(s_ims_orig_raw.data_inizio_media_change as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio_media_change,cast(to_date(from_unixtime(unix_timestamp(s_ims_orig_raw.data_inizio_srvcc, case when substr(cast(s_ims_orig_raw.data_inizio_srvcc as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio_srvcc,s_ims_orig_raw.data_produzione,s_ims_orig_raw.durata_2,s_ims_orig_raw.fascia_oraria,s_ims_orig_raw.ora_inizio_orig,s_ims_orig_raw.ora_inizio_conference,s_ims_orig_raw.ora_inizio_media_change,s_ims_orig_raw.ora_inizio_srvcc,s_ims_orig_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_ims_orig_raw.record_origine,s_ims_orig_raw.nome_acq,s_ims_orig_raw.call_position,s_ims_orig_raw.k_tipo_traffico,s_ims_orig_raw.cin,cast(null AS varchar(10)) as network_call_reference,s_ims_orig_raw.ricostruita,s_ims_orig_raw.numero_parziali,s_ims_orig_raw.msc_code,s_ims_orig_raw.cgi,cast(null AS varchar(8)) as last_cgi,s_ims_orig_raw.tac_eci,cast(null AS varchar(7)) as o_route,cast(null AS varchar(7)) as i_route,s_ims_orig_raw.if_human,s_ims_orig_raw.flag_gos,s_ims_orig_raw.flag_nr,s_ims_orig_raw.flag_rievo,s_ims_orig_raw.flag_servizi,s_ims_orig_raw.flag_tt,s_ims_orig_raw.flag_tt_best,s_ims_orig_raw.service_feature_code,cast(null AS varchar(5)) as internal_cause_and_location,s_ims_orig_raw.disconnecting_party,s_ims_orig_raw.cause_code,cast(null AS varchar(5)) as fault_code,cast(null AS varchar(3)) as ranap_cc,cast(null AS varchar(4)) as bssmap_cc,s_ims_orig_raw.conference_id,s_ims_orig_raw.destrealm,s_ims_orig_raw.sipringingdate,s_ims_orig_raw.sipringingtime,s_ims_orig_raw.list_of_media,s_ims_orig_raw.list_of_media_change,"O" AS direzione
123FROM ${hivevar:input_database}.s_ims_orig_raw
124WHERE s_ims_orig_raw.partition_date=${hivevar:data_elaborazione}
125
126UNION ALL
127SELECT s_gsm_ido_raw.a_ac,s_gsm_ido_raw.a_sn,cast(null AS varchar(100)) as a_imei,cast(null AS varchar(100)) as a_imsi,s_gsm_ido_raw.a_tipo,s_gsm_ido_raw.flag_cc,s_gsm_ido_raw.a_marcaggio,s_gsm_ido_raw.a_prov_res,cast(null AS varchar(6)) as mcc_mnc,cast(null AS varchar(3)) as subscription_type,s_gsm_ido_raw.gruppo_t,cast(null AS varchar(38)) as k_ope_orig,cast(null AS varchar(38)) as k_paese_orig,s_gsm_ido_raw.b_ac,s_gsm_ido_raw.b_sn,cast(null AS varchar(100)) as b_imei,cast(null AS varchar(100)) as b_imsi,cast(null AS varchar(1)) as b_tipo,cast(null AS varchar(2)) as b_flag_cc,s_gsm_ido_raw.b_marcaggio,cast(null AS varchar(4)) as b_prov_res,s_gsm_ido_raw.k_direttrice,cast(null AS varchar(38)) as k_ope_dest,cast(null AS varchar(38)) as k_paese_dest,s_gsm_ido_raw.direttrice_t,cast(null AS varchar(5)) as macrodir,s_gsm_ido_raw.b_gruppo_bit,s_gsm_ido_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_gsm_ido_raw.data_inizio, case when substr(cast(s_gsm_ido_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_gsm_ido_raw.data_produzione,s_gsm_ido_raw.durata_2,s_gsm_ido_raw.fascia_oraria,s_gsm_ido_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_gsm_ido_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_gsm_ido_raw.record_origine,s_gsm_ido_raw.nome_acq,s_gsm_ido_raw.call_position,s_gsm_ido_raw.k_tipo_traffico,s_gsm_ido_raw.cin,s_gsm_ido_raw.network_call_reference,s_gsm_ido_raw.ricostruita,s_gsm_ido_raw.numero_parziali,s_gsm_ido_raw.msc_code,cast(null AS varchar(8)) as cgi,cast(null AS varchar(8)) as last_cgi,cast(null AS varchar(12)) as tac_eci,cast(null AS varchar(7)) as o_route,cast(null AS varchar(7)) as i_route,s_gsm_ido_raw.if_human,cast(null AS varchar(1)) as flag_gos,s_gsm_ido_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,s_gsm_ido_raw.flag_servizi,s_gsm_ido_raw.flag_tt,cast(null AS varchar(1)) as flag_tt_best,cast(null AS varchar(3)) as service_feature_code,cast(null AS varchar(5)) as internal_cause_and_location,s_gsm_ido_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,cast(null AS varchar(5)) as fault_code,cast(null AS varchar(3)) as ranap_cc,cast(null AS varchar(4)) as bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"O" AS direzione
128FROM ${hivevar:input_database}.s_gsm_ido_raw
129WHERE s_gsm_ido_raw.partition_date=${hivevar:data_elaborazione}
130
131UNION ALL
132SELECT s_gsm_cf_raw.a_ac,s_gsm_cf_raw.a_sn,cast(null AS varchar(100)) as a_imei,s_gsm_cf_raw.a_imsi,s_gsm_cf_raw.a_tipo,s_gsm_cf_raw.flag_cc,s_gsm_cf_raw.a_marcaggio,s_gsm_cf_raw.a_prov_res,cast(null AS varchar(6)) as mcc_mnc,s_gsm_cf_raw.subscription_type,s_gsm_cf_raw.gruppo_t,s_gsm_cf_raw.k_ope_orig,s_gsm_cf_raw.k_paese_orig,s_gsm_cf_raw.b_ac,s_gsm_cf_raw.b_sn,cast(null AS varchar(100)) as b_imei,s_gsm_cf_raw.b_imsi,cast(null AS varchar(1)) as b_tipo,cast(null AS varchar(2)) as b_flag_cc,s_gsm_cf_raw.b_marcaggio,cast(null AS varchar(4)) as b_prov_res,s_gsm_cf_raw.k_direttrice,s_gsm_cf_raw.k_ope_dest,s_gsm_cf_raw.k_paese_dest,s_gsm_cf_raw.direttrice_t,s_gsm_cf_raw.macrodir,s_gsm_cf_raw.b_gruppo_bit,s_gsm_cf_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_gsm_cf_raw.data_inizio, case when substr(cast(s_gsm_cf_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_gsm_cf_raw.data_produzione,s_gsm_cf_raw.durata_2,s_gsm_cf_raw.fascia_oraria,s_gsm_cf_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_gsm_cf_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_gsm_cf_raw.record_origine,s_gsm_cf_raw.nome_acq,s_gsm_cf_raw.call_position,s_gsm_cf_raw.k_tipo_traffico,s_gsm_cf_raw.cin,s_gsm_cf_raw.network_call_reference,s_gsm_cf_raw.ricostruita,s_gsm_cf_raw.numero_parziali,s_gsm_cf_raw.msc_code,cast(null AS varchar(8)) as cgi,cast(null AS varchar(8)) as last_cgi,cast(null AS varchar(12)) as tac_eci,s_gsm_cf_raw.o_route,s_gsm_cf_raw.i_route,s_gsm_cf_raw.if_human,cast(null AS varchar(1)) as flag_gos,s_gsm_cf_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,s_gsm_cf_raw.flag_servizi,s_gsm_cf_raw.flag_tt,cast(null AS varchar(1)) as flag_tt_best,cast(null AS varchar(3)) as service_feature_code,s_gsm_cf_raw.internal_cause_and_location,s_gsm_cf_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,s_gsm_cf_raw.fault_code,cast(null AS varchar(3)) as ranap_cc,cast(null AS varchar(4)) as bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"O" AS direzione
133FROM ${hivevar:input_database}.s_gsm_cf_raw
134WHERE s_gsm_cf_raw.partition_date=${hivevar:data_elaborazione}
135
136UNION ALL
137SELECT s_gsm_lm_raw.a_ac,s_gsm_lm_raw.a_sn,cast(null AS varchar(100)) as a_imei,s_gsm_lm_raw.a_imsi,cast(null AS varchar(1)) as a_tipo,s_gsm_lm_raw.flag_cc,s_gsm_lm_raw.a_marcaggio,cast(null AS varchar(4)) as a_prov_res,s_gsm_lm_raw.mcc_mnc,s_gsm_lm_raw.subscription_type,s_gsm_lm_raw.gruppo_t,s_gsm_lm_raw.k_ope_orig,s_gsm_lm_raw.k_paese_orig,s_gsm_lm_raw.b_ac,s_gsm_lm_raw.b_sn,s_gsm_lm_raw.b_imei,s_gsm_lm_raw.b_imsi,s_gsm_lm_raw.b_tipo,s_gsm_lm_raw.b_flag_cc,s_gsm_lm_raw.b_marcaggio,s_gsm_lm_raw.b_prov_res,s_gsm_lm_raw.k_direttrice,s_gsm_lm_raw.k_ope_dest,s_gsm_lm_raw.k_paese_dest,s_gsm_lm_raw.direttrice_t,s_gsm_lm_raw.macrodir,s_gsm_lm_raw.b_gruppo_bit,s_gsm_lm_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_gsm_lm_raw.data_inizio, case when substr(cast(s_gsm_lm_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_gsm_lm_raw.data_produzione,s_gsm_lm_raw.durata_2,s_gsm_lm_raw.fascia_oraria,s_gsm_lm_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_gsm_lm_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_gsm_lm_raw.record_origine,s_gsm_lm_raw.nome_acq,s_gsm_lm_raw.call_position,s_gsm_lm_raw.k_tipo_traffico,s_gsm_lm_raw.cin,s_gsm_lm_raw.network_call_reference,s_gsm_lm_raw.ricostruita,s_gsm_lm_raw.numero_parziali,s_gsm_lm_raw.msc_code,s_gsm_lm_raw.cgi,s_gsm_lm_raw.last_cgi,cast(null AS varchar(12)) as tac_eci,cast(null AS varchar(7)) as o_route,s_gsm_lm_raw.i_route,s_gsm_lm_raw.if_human,s_gsm_lm_raw.flag_gos,s_gsm_lm_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,s_gsm_lm_raw.flag_servizi,s_gsm_lm_raw.flag_tt,s_gsm_lm_raw.flag_tt_best,cast(null AS varchar(3)) as service_feature_code,s_gsm_lm_raw.internal_cause_and_location,s_gsm_lm_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,s_gsm_lm_raw.fault_code,s_gsm_lm_raw.ranap_cc,s_gsm_lm_raw.bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"T" AS direzione
138FROM ${hivevar:input_database}.s_gsm_lm_raw
139WHERE s_gsm_lm_raw.partition_date=${hivevar:data_elaborazione}
140
141UNION ALL
142SELECT s_gsm_rcf_raw.a_ac,s_gsm_rcf_raw.a_sn,cast(null AS varchar(100)) as a_imei,cast(null AS varchar(100)) as a_imsi,cast(null AS varchar(1)) as a_tipo,s_gsm_rcf_raw.flag_cc,s_gsm_rcf_raw.a_marcaggio,cast(null AS varchar(4)) as a_prov_res,s_gsm_rcf_raw.mcc_mnc,s_gsm_rcf_raw.subscription_type,s_gsm_rcf_raw.gruppo_t,s_gsm_rcf_raw.k_ope_orig,s_gsm_rcf_raw.k_paese_orig,s_gsm_rcf_raw.b_ac,s_gsm_rcf_raw.b_sn,cast(null AS varchar(100)) as b_imei,s_gsm_rcf_raw.b_imsi,s_gsm_rcf_raw.b_tipo,s_gsm_rcf_raw.b_flag_cc,s_gsm_rcf_raw.b_marcaggio,s_gsm_rcf_raw.b_prov_res,s_gsm_rcf_raw.k_direttrice,s_gsm_rcf_raw.k_ope_dest,s_gsm_rcf_raw.k_paese_dest,s_gsm_rcf_raw.direttrice_t,s_gsm_rcf_raw.macrodir,s_gsm_rcf_raw.b_gruppo_bit,s_gsm_rcf_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_gsm_rcf_raw.data_inizio, case when substr(cast(s_gsm_rcf_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(NULL as date) AS data_inizio_conference,cast(NULL as date) AS data_inizio_media_change,cast(NULL as date) AS data_inizio_srvcc,s_gsm_rcf_raw.data_produzione,s_gsm_rcf_raw.durata_2,s_gsm_rcf_raw.fascia_oraria,s_gsm_rcf_raw.ora_inizio_orig,cast(null AS varchar(6)) as ora_inizio_conference,cast(null AS varchar(6)) as ora_inizio_media_change,cast(null AS varchar(6)) as ora_inizio_srvcc,s_gsm_rcf_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_gsm_rcf_raw.record_origine,s_gsm_rcf_raw.nome_acq,s_gsm_rcf_raw.call_position,s_gsm_rcf_raw.k_tipo_traffico,s_gsm_rcf_raw.cin,s_gsm_rcf_raw.network_call_reference,s_gsm_rcf_raw.ricostruita,s_gsm_rcf_raw.numero_parziali,s_gsm_rcf_raw.msc_code,cast(null AS varchar(8)) as cgi,cast(null AS varchar(8)) as last_cgi,cast(null AS varchar(12)) as tac_eci,s_gsm_rcf_raw.o_route,s_gsm_rcf_raw.i_route,s_gsm_rcf_raw.if_human,cast(null AS varchar(1)) as flag_gos,s_gsm_rcf_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,s_gsm_rcf_raw.flag_servizi,s_gsm_rcf_raw.flag_tt,cast(null AS varchar(1)) as flag_tt_best,cast(null AS varchar(3)) as service_feature_code,s_gsm_rcf_raw.internal_cause_and_location,s_gsm_rcf_raw.disconnecting_party,cast(null AS varchar(8)) as cause_code,s_gsm_rcf_raw.fault_code,cast(null AS varchar(3)) as ranap_cc,cast(null AS varchar(4)) as bssmap_cc,cast(null AS varchar(100)) as conference_id,cast(null AS varchar(30)) as destrealm,cast(null AS varchar(8)) as sipringingdate,cast(null AS varchar(6)) as sipringingtime,cast(null AS varchar(100)) as list_of_media,cast(null AS varchar(100)) as list_of_media_change,"T" AS direzione
143FROM ${hivevar:input_database}.s_gsm_rcf_raw
144WHERE s_gsm_rcf_raw.partition_date=${hivevar:data_elaborazione}
145
146UNION ALL
147SELECT s_ims_term_raw.a_ac,s_ims_term_raw.a_sn,s_ims_term_raw.a_imei,s_ims_term_raw.a_imsi,s_ims_term_raw.a_tipo,s_ims_term_raw.flag_cc,s_ims_term_raw.a_marcaggio,s_ims_term_raw.a_prov_res,s_ims_term_raw.mcc_mnc,cast(null AS varchar(3)) as subscription_type,s_ims_term_raw.gruppo_t,cast(null AS varchar(38)) as k_ope_orig,cast(null AS varchar(38)) as k_paese_orig,s_ims_term_raw.b_ac,s_ims_term_raw.b_sn,s_ims_term_raw.b_imei,s_ims_term_raw.b_imsi,s_ims_term_raw.b_tipo,s_ims_term_raw.b_flag_cc,s_ims_term_raw.b_marcaggio,s_ims_term_raw.b_prov_res,s_ims_term_raw.k_direttrice,cast(null AS varchar(38)) as k_ope_dest,cast(null AS varchar(38)) as k_paese_dest,s_ims_term_raw.direttrice_t,s_ims_term_raw.macrodir,s_ims_term_raw.b_gruppo_bit,s_ims_term_raw.a_gruppo_bit,cast(to_date(from_unixtime(unix_timestamp(s_ims_term_raw.data_inizio, case when substr(cast(s_ims_term_raw.data_inizio as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio,cast(to_date(from_unixtime(unix_timestamp(s_ims_term_raw.data_inizio_conference, case when substr(cast(s_ims_term_raw.data_inizio_conference as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio_conference,cast(to_date(from_unixtime(unix_timestamp(s_ims_term_raw.data_inizio_media_change, case when substr(cast(s_ims_term_raw.data_inizio_media_change as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio_media_change,cast(to_date(from_unixtime(unix_timestamp(s_ims_term_raw.data_inizio_srvcc, case when substr(cast(s_ims_term_raw.data_inizio_srvcc as string), 5, 2) > 12 then 'ddMMyyyy' else 'yyyyMMdd' end ))) as date) AS data_inizio_srvcc,s_ims_term_raw.data_produzione,s_ims_term_raw.durata_2,s_ims_term_raw.fascia_oraria,s_ims_term_raw.ora_inizio_orig,s_ims_term_raw.ora_inizio_conference,s_ims_term_raw.ora_inizio_media_change,s_ims_term_raw.ora_inizio_srvcc,s_ims_term_raw.tipo_giorno,cast(NULL as date) AS data_produzione_edl,s_ims_term_raw.record_origine,s_ims_term_raw.nome_acq,s_ims_term_raw.call_position,s_ims_term_raw.k_tipo_traffico,s_ims_term_raw.cin,cast(null AS varchar(10)) as network_call_reference,s_ims_term_raw.ricostruita,s_ims_term_raw.numero_parziali,s_ims_term_raw.msc_code,s_ims_term_raw.cgi,cast(null AS varchar(8)) as last_cgi,s_ims_term_raw.tac_eci,cast(null AS varchar(7)) as o_route,cast(null AS varchar(7)) as i_route,s_ims_term_raw.if_human,cast(null AS varchar(1)) as flag_gos,s_ims_term_raw.flag_nr,cast(null AS varchar(1)) as flag_rievo,s_ims_term_raw.flag_servizi,s_ims_term_raw.flag_tt,s_ims_term_raw.flag_tt_best,cast(null AS varchar(3)) as service_feature_code,cast(null AS varchar(5)) as internal_cause_and_location,s_ims_term_raw.disconnecting_party,s_ims_term_raw.cause_code,cast(null AS varchar(5)) as fault_code,cast(null AS varchar(3)) as ranap_cc,cast(null AS varchar(4)) as bssmap_cc,s_ims_term_raw.conference_id,s_ims_term_raw.destrealm,s_ims_term_raw.sipringingdate,s_ims_term_raw.sipringingtime,s_ims_term_raw.list_of_media,s_ims_term_raw.list_of_media_change,"T" AS direzione
148FROM ${hivevar:input_database}.s_ims_term_raw
149WHERE s_ims_term_raw.partition_date=${hivevar:data_elaborazione}
150) tmp