Hi Jorn
Yes I will do that test. Same file size but with less columns.
I created a table with simple columns (all strings) and not nested and I do not
do any transformations. Attach both tables schema.
As per default the hive.vectorized.execution.enabled is set to false.
I have not enable it.
Just an example that it took 1 hours :
0: jdbc:hive2://localhost:10000> insert into table ret_rec_cdrs_orc PARTITION
(country='DE',year='2016',month='12') select * from ret_rec_cdrs where
country='DE' and year='2016' and month='12';
+---------+--+
| Result |
+---------+--+
+---------+--+
No rows selected (3837.457 seconds)
0: jdbc:hive2://localhost:10000> select count(*) from ret_rec_cdrs where
country='DE' and year='2016' and month='12';
+----------+--+
| _c0 |
+----------+--+
| 3900155 |
+----------+--+
1 row selected (24.722 seconds)
0: jdbc:hive2://localhost:10000> select count(*) from ret_rec_cdrs_orc where
country='DE' and year='2016' and month='12';
+----------+--+
| _c0 |
+----------+--+
| 3900155 |
+----------+--+
1 row selected (82.071 seconds)
From: Jörn Franke [mailto:[email protected]]
Sent: 09 December 2016 10:22
To: [email protected]
Subject: Re: Hive Stored Textfile to Stored ORC taking long time
Ok.
No do no split in smaller files. This is done automatically. Your behavior
looks strange. For that file size I would expect that it takes below one minute.
Maybe you hit a bug in the spark on hive engine. You could try with a file with
less columns, but the same size. I assume that this is a hive table with simple
columns (nothing deeply nested) and that you do not any transformations.
What is the CTAS query?
Do you enable vectorization in Hive?
If you just need a simple mapping from CSV to orc you can use any framework
(mr, tez, spark etc), because performance does not differ so much in these
cases, especially for the small amount of data you process.
On 9 Dec 2016, at 11:02, Joaquin Alzola
<[email protected]<mailto:[email protected]>> wrote:
Hi Jorn
The file is about 1.5GB with 1.5 milion records and about 550 fields in each
row.
ORC is compress as Zlib.
I am using a standalone solution before expanding it, so everything is on the
same node.
Hive 2.0.1 --> Spark 1.6.3 --> HDFS 2.6.5
The configuration is much more as standard and have not change anything much.
It cannot be a network issue because all the apps are on the same node.
Since I am doing all of this translation on the Hive point (from textfile to
ORC) I wanted to know if I could do it quicker on the Spark or HDFS level
(doing the file conversion some other way) not on the stop of the “stack”
We take the files every day once so if I put them in textfile and then to ORC
it will take me almost half a day just to display the data.
It is basicly a time consuming task, and want to do it much quicker. A better
solution of course would be to put smaller files with FLUME but this I will do
it in the future.
From: Jörn Franke [mailto:[email protected]]
Sent: 09 December 2016 09:48
To: [email protected]<mailto:[email protected]>
Subject: Re: Hive Stored Textfile to Stored ORC taking long time
How large is the file? Might IO be an issue? How many disks have you on the
only node?
Do you compress the ORC (snappy?).
What is the Hadoop distribution? Configuration baseline? Hive version?
Not sure if i understood your setup, but might network be an issue?
On 9 Dec 2016, at 02:08, Joaquin Alzola
<[email protected]<mailto:[email protected]>> wrote:
HI List
The transformation from textfile table to stored ORC table takes quiet a long
time.
Steps follow>
1.Create one normal table using textFile format
2.Load the data normally into this table
3.Create one table with the schema of the expected results of your normal hive
table using stored as orcfile
4.Insert overwrite query to copy the data from textFile table to orcfile table
I have about 1,5 million records with about 550 fields in each row.
Doing step 4 takes about 30 minutes (moving from one format to the other).
I have spark with only one worker (same for HDFS) so running now a standalone
server but with 25G and 14 cores on that worker.
BR
Joaquin
This email is confidential and may be subject to privilege. If you are not the
intended recipient, please do not copy or disclose its content but contact the
sender immediately upon receipt.
This email is confidential and may be subject to privilege. If you are not the
intended recipient, please do not copy or disclose its content but contact the
sender immediately upon receipt.
This email is confidential and may be subject to privilege. If you are not the
intended recipient, please do not copy or disclose its content but contact the
sender immediately upon receipt.
CREATE TABLE IF NOT EXISTS RET_rec_cdrs (
CDR_ID String,
CDR_SUB_ID String,
TIME_STAMP String,
ServiceKey String,
CallingPartyNumber String,
CalledPartyNumber String,
CallingPartyIMSI String,
CalledPartyIMSI String,
DialedNumber String,
OriginalCalledParty String,
ServiceFlow String,
CallForwardingIndicator String,
CallingRoamInfo String,
CallingCellID String,
CalledRoamInfo String,
CalledCellID String,
TimeStampOfSSP String,
TimeZoneOfSSP String,
BearerCapability String,
Reserved1 String,
CUST_LOCAL_START_DATE String,
WaitDuration String,
ACTUAL_USAGE String,
TerminationReason String,
CallReferenceNumber String,
IMEI String,
RATE_USAGE String,
AccessPrefix String,
RoutingPrefix String,
RedirectingPartyID String,
BrandID String,
MainOfferingID String,
PRI_IDENTITY String,
ChargePartyIndicator String,
PayType String,
BILL_CYCLE_ID String,
ChargingType String,
CallType String,
RoamState String,
RESULT_CODE String,
AccessPrefix2 String,
CallingHomeCountryCode String,
CallingHomeAreaNumber String,
CallingHomeNetworkCode String,
CallingRoamCountryCode String,
CallingRoamAreaNumber String,
CallingRoamNetworkCode String,
CalledHomeCountryCode String,
CalledHomeAreaNumber String,
CalledHomeNetworkCode String,
CalledRoamCountryCode String,
CalledRoamAreaNumber String,
CalledRoamNetworkCode String,
LastEffectOffering String,
ServiceType String,
HotLineIndicator String,
HomeZoneID String,
NPFlag String,
NPPrefix String,
CallingCUGNo String,
CalledCUGNo String,
UserState String,
Subscriber_ID String,
OpposeNumberType String,
Reserved2 String,
FREE_UNIT_AMOUNT_OF_TIMES String,
FREE_UNIT_AMOUNT_OF_DURATION String,
DEBIT_AMOUNT22 String,
DEBIT_FROM_ADVANCE_PREPAID String,
CURRENT_AMOUNT22 String,
DEBIT_FROM_ADVANCE_POSTPAID String,
CURRENT_AMOUNT33 String,
ACCT_ID22 String,
CurrencyCode String,
Reserved3 String,
OBJECT_TYPE_ID String,
CHARGE_CODE String,
CHG_AMOUNT String,
CUR_AMOUNT String,
OBJECT_TYPE_ID15 String,
CHARGE_CODE15 String,
CHG_AMOUNT15 String,
CUR_AMOUNT15 String,
OBJECT_TYPE_ID25 String,
CHARGE_CODE25 String,
CHG_AMOUNT25 String,
CUR_AMOUNT25 String,
OBJECT_TYPE_ID35 String,
CHARGE_CODE35 String,
CHG_AMOUNT35 String,
CUR_AMOUNT35 String,
OBJECT_TYPE_ID45 String,
CHARGE_CODE45 String,
CHG_AMOUNT45 String,
CUR_AMOUNT45 String,
OBJECT_TYPE_ID55 String,
CHARGE_CODE55 String,
CHG_AMOUNT55 String,
CUR_AMOUNT55 String,
OBJECT_TYPE_ID65 String,
CHARGE_CODE65 String,
CHG_AMOUNT65 String,
CUR_AMOUNT65 String,
OBJECT_TYPE_ID75 String,
CHARGE_CODE75 String,
CHG_AMOUNT75 String,
CUR_AMOUNT75 String,
OBJECT_TYPE_ID85 String,
CHARGE_CODE85 String,
CHG_AMOUNT85 String,
CUR_AMOUNT85 String,
OBJECT_TYPE_ID95 String,
CHARGE_CODE95 String,
CHG_AMOUNT95 String,
CUR_AMOUNT95 String,
CallingVPNTopGroupNumber String,
CallingVPNGroupNumber String,
CallingVPNShortNumber String,
CalledVPNTopGroupNumber String,
CalledVPNGroupNumber String,
CalledVPNShortNumber String,
CallingNetworkType String,
CalledNetworkType String,
GroupCallType String,
GroupPayFlag String,
Reserved4 String,
Reserved5 String,
Reserved6 String,
Reserved7 String,
Reserved8 String,
AdditionalInfo String,
StartTimeOfBillCycle String,
PRIMARY_IDENTITY String,
ACCT_PAID_TYPE String,
BILL_CYCLE_ID11 String,
FREE_UNIT_AMOUNT_OF_TIMES2 String,
FREE_UNIT_AMOUNT_OF_DURATION2 String,
DEBIT_AMOUNT221 String,
DEBIT_AMOUNT222 String,
CURRENT_AMOUNT String,
ACCT_ID String,
SecondAccountCurrencyCode String,
Reserved9 String,
OBJECT_TYPE_ID101 String,
CHARGE_CODE10 String,
CHG_AMOUNT101 String,
CUR_AMOUNT101 String,
OBJECT_TYPE_ID111 String,
CHARGE_CODE11 String,
CHG_AMOUNT111 String,
CUR_AMOUNT111 String,
OBJECT_TYPE_ID121 String,
CHARGE_CODE12 String,
CHG_AMOUNT121 String,
CUR_AMOUNT121 String,
OBJECT_TYPE_ID131 String,
CHARGE_CODE13 String,
CHG_AMOUNT131 String,
CUR_AMOUNT131 String,
OBJECT_TYPE_ID141 String,
CHARGE_CODE14 String,
CHG_AMOUNT141 String,
CUR_AMOUNT141 String,
CallingPartyNumber2 String,
Reserved10 String,
Reserved11 String,
RECIPIENT_NUMBER String,
Reserved12 String,
Reserved13 String,
Reserved14 String,
SESSION_ID String,
Reserved15 String,
Reserved16 String,
Reserved17 String,
Reserved18 String,
Reserved19 String,
Reserved20 String,
Reserved21 String,
Reserved22 String,
Reserved23 String,
Reserved24 String,
OnNetIndicator String,
CDR_TYPE String,
SPLIT_CDR_REASON String,
CDR_BATCH_ID String,
CUST_LOCAL_END_DATE String,
EventSource_Cate String,
STD_EVT_TYPE_ID String,
USAGE_SERVICE_TYPE String,
OWNER_CUST_ID String,
DEFAULT_ACCT_ID String,
OWNER_CUST_CODE String,
DEFAULT_ACCT_CODE String,
USAGE_MEASURE_ID String,
SERVICE_UNIT_TYPE String,
USAGE_MEASURE_ID1 String,
ACTUAL_USAGE1 String,
RATE_USAGE1 String,
SERVICE_UNIT_TYPE1 String,
DEBIT_FROM_PREPAID String,
DEBIT_FROM_POSTPAID String,
DEBIT_FROM_CREDIT_POSTPAID String,
TOTAL_TAX String,
FREE_UNIT_AMOUNT_OF_FLUX String,
ACCT_Code String,
Cust_ID String,
Cust_Code String,
CHARGE_FROM_ACCOUNT String,
Group_Type String,
GROUP_ID String,
GROUP_CODE String,
ACCT_Code1 String,
ACCT_ID1 String,
Cust_ID1 String,
Cust_Code1 String,
ACCT_PAID_TYPE1 String,
CHARGE_FROM_ACCOUNT1 String,
CURRENT_AMOUNT1 String,
BILL_CYCLE_ID1 String,
Group_Type1 String,
PRIMARY_IDENTITY1 String,
GROUP_ID1 String,
GROUP_CODE1 String,
ACCT_Code2 String,
ACCT_ID2 String,
Cust_ID2 String,
Cust_Code2 String,
ACCT_PAID_TYPE2 String,
CHARGE_FROM_ACCOUNT2 String,
CURRENT_AMOUNT2 String,
BILL_CYCLE_ID2 String,
Group_Type2 String,
PRIMARY_IDENTITY2 String,
GROUP_ID2 String,
GROUP_CODE2 String,
ACCT_Code3 String,
ACCT_ID3 String,
Cust_ID3 String,
Cust_Code3 String,
ACCT_PAID_TYPE3 String,
CHARGE_FROM_ACCOUNT3 String,
CURRENT_AMOUNT3 String,
BILL_CYCLE_ID3 String,
Group_Type3 String,
PRIMARY_IDENTITY3 String,
GROUP_ID3 String,
GROUP_CODE3 String,
ACCT_Code4 String,
ACCT_ID4 String,
Cust_ID4 String,
Cust_Code4 String,
ACCT_PAID_TYPE4 String,
CHARGE_FROM_ACCOUNT4 String,
CURRENT_AMOUNT4 String,
BILL_CYCLE_ID4 String,
Group_Type4 String,
PRIMARY_IDENTITY4 String,
GROUP_ID4 String,
GROUP_CODE4 String,
OBJECT_TYPE String,
OWNER_TYPE String,
OWN_Code String,
OBJECT_ID String,
MEASURE_ID String,
OPER_TYPE String,
TOTAL_CURRENT_AMOUNT String,
OfferingCode String,
OBJECT_TYPE1 String,
OWNER_TYPE1 String,
OWN_Code1 String,
OBJECT_ID1 String,
OBJECT_TYPE_ID1 String,
CUR_AMOUNT1 String,
CHG_AMOUNT1 String,
MEASURE_ID1 String,
OPER_TYPE1 String,
TOTAL_CURRENT_AMOUNT1 String,
OfferingCode1 String,
OBJECT_TYPE2 String,
OWNER_TYPE2 String,
OWN_Code2 String,
OBJECT_ID2 String,
OBJECT_TYPE_ID2 String,
CUR_AMOUNT2 String,
CHG_AMOUNT2 String,
MEASURE_ID2 String,
OPER_TYPE2 String,
TOTAL_CURRENT_AMOUNT2 String,
OfferingCode2 String,
OBJECT_TYPE3 String,
OWNER_TYPE3 String,
OWN_Code3 String,
OBJECT_ID3 String,
OBJECT_TYPE_ID3 String,
CUR_AMOUNT3 String,
CHG_AMOUNT3 String,
MEASURE_ID3 String,
OPER_TYPE3 String,
TOTAL_CURRENT_AMOUNT3 String,
OfferingCode3 String,
OBJECT_TYPE4 String,
OWNER_TYPE4 String,
OWN_Code4 String,
OBJECT_ID4 String,
OBJECT_TYPE_ID4 String,
CUR_AMOUNT4 String,
CHG_AMOUNT4 String,
MEASURE_ID4 String,
OPER_TYPE4 String,
TOTAL_CURRENT_AMOUNT4 String,
OfferingCode4 String,
OBJECT_TYPE5 String,
OWNER_TYPE5 String,
OWN_Code5 String,
OBJECT_ID5 String,
OBJECT_TYPE_ID5 String,
CUR_AMOUNT5 String,
CHG_AMOUNT5 String,
MEASURE_ID5 String,
OPER_TYPE5 String,
TOTAL_CURRENT_AMOUNT5 String,
OfferingCode5 String,
OBJECT_TYPE6 String,
OWNER_TYPE6 String,
OWN_Code6 String,
OBJECT_ID6 String,
OBJECT_TYPE_ID6 String,
CUR_AMOUNT6 String,
CHG_AMOUNT6 String,
MEASURE_ID6 String,
OPER_TYPE6 String,
TOTAL_CURRENT_AMOUNT6 String,
OfferingCode6 String,
OBJECT_TYPE7 String,
OWNER_TYPE7 String,
OWN_Code7 String,
OBJECT_ID7 String,
OBJECT_TYPE_ID7 String,
CUR_AMOUNT7 String,
CHG_AMOUNT7 String,
MEASURE_ID7 String,
OPER_TYPE7 String,
TOTAL_CURRENT_AMOUNT7 String,
OfferingCode7 String,
OBJECT_TYPE8 String,
OWNER_TYPE8 String,
OWN_Code8 String,
OBJECT_ID8 String,
OBJECT_TYPE_ID8 String,
CUR_AMOUNT8 String,
CHG_AMOUNT8 String,
MEASURE_ID8 String,
OPER_TYPE8 String,
TOTAL_CURRENT_AMOUNT8 String,
OfferingCode8 String,
OBJECT_TYPE9 String,
OWNER_TYPE9 String,
OWN_Code9 String,
OBJECT_ID9 String,
OBJECT_TYPE_ID9 String,
CUR_AMOUNT9 String,
CHG_AMOUNT9 String,
MEASURE_ID9 String,
OPER_TYPE9 String,
TOTAL_CURRENT_AMOUNT9 String,
OfferingCode9 String,
OBJECT_TYPE10 String,
OWNER_TYPE10 String,
OWN_Code10 String,
OBJECT_ID10 String,
OBJECT_TYPE_ID10 String,
CUR_AMOUNT10 String,
CHG_AMOUNT10 String,
MEASURE_ID10 String,
OPER_TYPE10 String,
TOTAL_CURRENT_AMOUNT10 String,
OfferingCode10 String,
OBJECT_TYPE11 String,
OWNER_TYPE11 String,
OWN_Code11 String,
OBJECT_ID11 String,
OBJECT_TYPE_ID11 String,
CUR_AMOUNT11 String,
CHG_AMOUNT11 String,
MEASURE_ID11 String,
OPER_TYPE11 String,
TOTAL_CURRENT_AMOUNT11 String,
OfferingCode11 String,
OBJECT_TYPE12 String,
OWNER_TYPE12 String,
OWN_Code12 String,
OBJECT_ID12 String,
OBJECT_TYPE_ID12 String,
CUR_AMOUNT12 String,
CHG_AMOUNT12 String,
MEASURE_ID12 String,
OPER_TYPE12 String,
TOTAL_CURRENT_AMOUNT12 String,
OfferingCode12 String,
OBJECT_TYPE13 String,
OWNER_TYPE13 String,
OWN_Code13 String,
OBJECT_ID13 String,
OBJECT_TYPE_ID13 String,
CUR_AMOUNT13 String,
CHG_AMOUNT13 String,
MEASURE_ID13 String,
OPER_TYPE13 String,
TOTAL_CURRENT_AMOUNT13 String,
OfferingCode13 String,
OBJECT_TYPE14 String,
OWNER_TYPE14 String,
OWN_Code14 String,
OBJECT_ID14 String,
OBJECT_TYPE_ID14 String,
CUR_AMOUNT14 String,
CHG_AMOUNT14 String,
MEASURE_ID14 String,
OPER_TYPE14 String,
TOTAL_CURRENT_AMOUNT14 String,
OfferingCode14 String,
OFFERING_ID String,
PAY_ACCT_Code String,
PAY_OBJECT_TYPE String,
PAY_OBJECT_TYPE_ID String,
DEDUCT_CHARGE_AMOUNT String,
CurrentAmount2_1 String,
TAX_CODE1 String,
IINCLUSIVE_TAX_FLAG1 String,
TAX_AMOUNT1 String,
CHARGE_CODE1 String,
OFFERING_ID1 String,
PAY_ACCT_Code1 String,
PAY_OBJECT_TYPE1 String,
PAY_OBJECT_TYPE_ID1 String,
DEDUCT_CHARGE_AMOUNT1 String,
CurrentAmount2_2 String,
TAX_CODE11 String,
IINCLUSIVE_TAX_FLAG11 String,
TAX_AMOUNT11 String,
CHARGE_CODE2 String,
OFFERING_ID2 String,
PAY_ACCT_Code2 String,
PAY_OBJECT_TYPE2 String,
PAY_OBJECT_TYPE_ID2 String,
DEDUCT_CHARGE_AMOUNT2 String,
CurrentAmount2_3 String,
TAX_CODE12 String,
IINCLUSIVE_TAX_FLAG12 String,
TAX_AMOUNT12 String,
CHARGE_CODE3 String,
OFFERING_ID3 String,
PAY_ACCT_Code3 String,
PAY_OBJECT_TYPE3 String,
PAY_OBJECT_TYPE_ID3 String,
DEDUCT_CHARGE_AMOUNT3 String,
CurrentAmount2_4 String,
TAX_CODE13 String,
IINCLUSIVE_TAX_FLAG13 String,
TAX_AMOUNT13 String,
CHARGE_CODE4 String,
OFFERING_ID4 String,
PAY_ACCT_Code4 String,
PAY_OBJECT_TYPE4 String,
PAY_OBJECT_TYPE_ID4 String,
DEDUCT_CHARGE_AMOUNT4 String,
CurrentAmount2_5 String,
TAX_CODE14 String,
IINCLUSIVE_TAX_FLAG14 String,
TAX_AMOUNT14 String,
CHARGE_CODE5 String,
OFFERING_ID5 String,
PAY_ACCT_Code5 String,
PAY_OBJECT_TYPE5 String,
PAY_OBJECT_TYPE_ID5 String,
DEDUCT_CHARGE_AMOUNT5 String,
CurrentAmount5 String,
TAX_CODE15 String,
IINCLUSIVE_TAX_FLAG15 String,
TAX_AMOUNT15 String,
CHARGE_CODE6 String,
OFFERING_ID6 String,
PAY_ACCT_Code6 String,
PAY_OBJECT_TYPE6 String,
PAY_OBJECT_TYPE_ID6 String,
DEDUCT_CHARGE_AMOUNT6 String,
CurrentAmount6 String,
TAX_CODE16 String,
IINCLUSIVE_TAX_FLAG16 String,
TAX_AMOUNT16 String,
CHARGE_CODE7 String,
OFFERING_ID7 String,
PAY_ACCT_Code7 String,
PAY_OBJECT_TYPE7 String,
PAY_OBJECT_TYPE_ID7 String,
DEDUCT_CHARGE_AMOUNT7 String,
CurrentAmount7 String,
TAX_CODE17 String,
IINCLUSIVE_TAX_FLAG17 String,
TAX_AMOUNT17 String,
CHARGE_CODE8 String,
OFFERING_ID8 String,
PAY_ACCT_Code8 String,
PAY_OBJECT_TYPE8 String,
PAY_OBJECT_TYPE_ID8 String,
DEDUCT_CHARGE_AMOUNT8 String,
CurrentAmount8 String,
TAX_CODE18 String,
IINCLUSIVE_TAX_FLAG18 String,
TAX_AMOUNT18 String,
CHARGE_CODE9 String,
OFFERING_ID9 String,
PAY_ACCT_Code9 String,
PAY_OBJECT_TYPE9 String,
PAY_OBJECT_TYPE_ID9 String,
DEDUCT_CHARGE_AMOUNT9 String,
CurrentAmount9 String,
TAX_CODE19 String,
IINCLUSIVE_TAX_FLAG19 String,
TAX_AMOUNT19 String,
CHG_BALANCE String,
CHG_FREE_UNIT String,
DETAIL_FEE String,
BONUS_OFFERING String,
INSTALMENT String,
SUB_LIFECYCLE String,
MSCAddress String,
SpecialZoneID String,
OpposeMainOfferingID String,
ExtendDaysbycharge String,
SUB_IDEN_TYPE String)
COMMENT 'Retail REC CDRs'
PARTITIONED BY(country STRING, year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
CREATE TABLE IF NOT EXISTS RET_rec_cdrs_orc (
CDR_ID String,
CDR_SUB_ID String,
TIME_STAMP String,
ServiceKey String,
CallingPartyNumber String,
CalledPartyNumber String,
CallingPartyIMSI String,
CalledPartyIMSI String,
DialedNumber String,
OriginalCalledParty String,
ServiceFlow String,
CallForwardingIndicator String,
CallingRoamInfo String,
CallingCellID String,
CalledRoamInfo String,
CalledCellID String,
TimeStampOfSSP String,
TimeZoneOfSSP String,
BearerCapability String,
Reserved1 String,
CUST_LOCAL_START_DATE String,
WaitDuration String,
ACTUAL_USAGE String,
TerminationReason String,
CallReferenceNumber String,
IMEI String,
RATE_USAGE String,
AccessPrefix String,
RoutingPrefix String,
RedirectingPartyID String,
BrandID String,
MainOfferingID String,
PRI_IDENTITY String,
ChargePartyIndicator String,
PayType String,
BILL_CYCLE_ID String,
ChargingType String,
CallType String,
RoamState String,
RESULT_CODE String,
AccessPrefix2 String,
CallingHomeCountryCode String,
CallingHomeAreaNumber String,
CallingHomeNetworkCode String,
CallingRoamCountryCode String,
CallingRoamAreaNumber String,
CallingRoamNetworkCode String,
CalledHomeCountryCode String,
CalledHomeAreaNumber String,
CalledHomeNetworkCode String,
CalledRoamCountryCode String,
CalledRoamAreaNumber String,
CalledRoamNetworkCode String,
LastEffectOffering String,
ServiceType String,
HotLineIndicator String,
HomeZoneID String,
NPFlag String,
NPPrefix String,
CallingCUGNo String,
CalledCUGNo String,
UserState String,
Subscriber_ID String,
OpposeNumberType String,
Reserved2 String,
FREE_UNIT_AMOUNT_OF_TIMES String,
FREE_UNIT_AMOUNT_OF_DURATION String,
DEBIT_AMOUNT22 String,
DEBIT_FROM_ADVANCE_PREPAID String,
CURRENT_AMOUNT22 String,
DEBIT_FROM_ADVANCE_POSTPAID String,
CURRENT_AMOUNT33 String,
ACCT_ID22 String,
CurrencyCode String,
Reserved3 String,
OBJECT_TYPE_ID String,
CHARGE_CODE String,
CHG_AMOUNT String,
CUR_AMOUNT String,
OBJECT_TYPE_ID15 String,
CHARGE_CODE15 String,
CHG_AMOUNT15 String,
CUR_AMOUNT15 String,
OBJECT_TYPE_ID25 String,
CHARGE_CODE25 String,
CHG_AMOUNT25 String,
CUR_AMOUNT25 String,
OBJECT_TYPE_ID35 String,
CHARGE_CODE35 String,
CHG_AMOUNT35 String,
CUR_AMOUNT35 String,
OBJECT_TYPE_ID45 String,
CHARGE_CODE45 String,
CHG_AMOUNT45 String,
CUR_AMOUNT45 String,
OBJECT_TYPE_ID55 String,
CHARGE_CODE55 String,
CHG_AMOUNT55 String,
CUR_AMOUNT55 String,
OBJECT_TYPE_ID65 String,
CHARGE_CODE65 String,
CHG_AMOUNT65 String,
CUR_AMOUNT65 String,
OBJECT_TYPE_ID75 String,
CHARGE_CODE75 String,
CHG_AMOUNT75 String,
CUR_AMOUNT75 String,
OBJECT_TYPE_ID85 String,
CHARGE_CODE85 String,
CHG_AMOUNT85 String,
CUR_AMOUNT85 String,
OBJECT_TYPE_ID95 String,
CHARGE_CODE95 String,
CHG_AMOUNT95 String,
CUR_AMOUNT95 String,
CallingVPNTopGroupNumber String,
CallingVPNGroupNumber String,
CallingVPNShortNumber String,
CalledVPNTopGroupNumber String,
CalledVPNGroupNumber String,
CalledVPNShortNumber String,
CallingNetworkType String,
CalledNetworkType String,
GroupCallType String,
GroupPayFlag String,
Reserved4 String,
Reserved5 String,
Reserved6 String,
Reserved7 String,
Reserved8 String,
AdditionalInfo String,
StartTimeOfBillCycle String,
PRIMARY_IDENTITY String,
ACCT_PAID_TYPE String,
BILL_CYCLE_ID11 String,
FREE_UNIT_AMOUNT_OF_TIMES2 String,
FREE_UNIT_AMOUNT_OF_DURATION2 String,
DEBIT_AMOUNT221 String,
DEBIT_AMOUNT222 String,
CURRENT_AMOUNT String,
ACCT_ID String,
SecondAccountCurrencyCode String,
Reserved9 String,
OBJECT_TYPE_ID101 String,
CHARGE_CODE10 String,
CHG_AMOUNT101 String,
CUR_AMOUNT101 String,
OBJECT_TYPE_ID111 String,
CHARGE_CODE11 String,
CHG_AMOUNT111 String,
CUR_AMOUNT111 String,
OBJECT_TYPE_ID121 String,
CHARGE_CODE12 String,
CHG_AMOUNT121 String,
CUR_AMOUNT121 String,
OBJECT_TYPE_ID131 String,
CHARGE_CODE13 String,
CHG_AMOUNT131 String,
CUR_AMOUNT131 String,
OBJECT_TYPE_ID141 String,
CHARGE_CODE14 String,
CHG_AMOUNT141 String,
CUR_AMOUNT141 String,
CallingPartyNumber2 String,
Reserved10 String,
Reserved11 String,
RECIPIENT_NUMBER String,
Reserved12 String,
Reserved13 String,
Reserved14 String,
SESSION_ID String,
Reserved15 String,
Reserved16 String,
Reserved17 String,
Reserved18 String,
Reserved19 String,
Reserved20 String,
Reserved21 String,
Reserved22 String,
Reserved23 String,
Reserved24 String,
OnNetIndicator String,
CDR_TYPE String,
SPLIT_CDR_REASON String,
CDR_BATCH_ID String,
CUST_LOCAL_END_DATE String,
EventSource_Cate String,
STD_EVT_TYPE_ID String,
USAGE_SERVICE_TYPE String,
OWNER_CUST_ID String,
DEFAULT_ACCT_ID String,
OWNER_CUST_CODE String,
DEFAULT_ACCT_CODE String,
USAGE_MEASURE_ID String,
SERVICE_UNIT_TYPE String,
USAGE_MEASURE_ID1 String,
ACTUAL_USAGE1 String,
RATE_USAGE1 String,
SERVICE_UNIT_TYPE1 String,
DEBIT_FROM_PREPAID String,
DEBIT_FROM_POSTPAID String,
DEBIT_FROM_CREDIT_POSTPAID String,
TOTAL_TAX String,
FREE_UNIT_AMOUNT_OF_FLUX String,
ACCT_Code String,
Cust_ID String,
Cust_Code String,
CHARGE_FROM_ACCOUNT String,
Group_Type String,
GROUP_ID String,
GROUP_CODE String,
ACCT_Code1 String,
ACCT_ID1 String,
Cust_ID1 String,
Cust_Code1 String,
ACCT_PAID_TYPE1 String,
CHARGE_FROM_ACCOUNT1 String,
CURRENT_AMOUNT1 String,
BILL_CYCLE_ID1 String,
Group_Type1 String,
PRIMARY_IDENTITY1 String,
GROUP_ID1 String,
GROUP_CODE1 String,
ACCT_Code2 String,
ACCT_ID2 String,
Cust_ID2 String,
Cust_Code2 String,
ACCT_PAID_TYPE2 String,
CHARGE_FROM_ACCOUNT2 String,
CURRENT_AMOUNT2 String,
BILL_CYCLE_ID2 String,
Group_Type2 String,
PRIMARY_IDENTITY2 String,
GROUP_ID2 String,
GROUP_CODE2 String,
ACCT_Code3 String,
ACCT_ID3 String,
Cust_ID3 String,
Cust_Code3 String,
ACCT_PAID_TYPE3 String,
CHARGE_FROM_ACCOUNT3 String,
CURRENT_AMOUNT3 String,
BILL_CYCLE_ID3 String,
Group_Type3 String,
PRIMARY_IDENTITY3 String,
GROUP_ID3 String,
GROUP_CODE3 String,
ACCT_Code4 String,
ACCT_ID4 String,
Cust_ID4 String,
Cust_Code4 String,
ACCT_PAID_TYPE4 String,
CHARGE_FROM_ACCOUNT4 String,
CURRENT_AMOUNT4 String,
BILL_CYCLE_ID4 String,
Group_Type4 String,
PRIMARY_IDENTITY4 String,
GROUP_ID4 String,
GROUP_CODE4 String,
OBJECT_TYPE String,
OWNER_TYPE String,
OWN_Code String,
OBJECT_ID String,
MEASURE_ID String,
OPER_TYPE String,
TOTAL_CURRENT_AMOUNT String,
OfferingCode String,
OBJECT_TYPE1 String,
OWNER_TYPE1 String,
OWN_Code1 String,
OBJECT_ID1 String,
OBJECT_TYPE_ID1 String,
CUR_AMOUNT1 String,
CHG_AMOUNT1 String,
MEASURE_ID1 String,
OPER_TYPE1 String,
TOTAL_CURRENT_AMOUNT1 String,
OfferingCode1 String,
OBJECT_TYPE2 String,
OWNER_TYPE2 String,
OWN_Code2 String,
OBJECT_ID2 String,
OBJECT_TYPE_ID2 String,
CUR_AMOUNT2 String,
CHG_AMOUNT2 String,
MEASURE_ID2 String,
OPER_TYPE2 String,
TOTAL_CURRENT_AMOUNT2 String,
OfferingCode2 String,
OBJECT_TYPE3 String,
OWNER_TYPE3 String,
OWN_Code3 String,
OBJECT_ID3 String,
OBJECT_TYPE_ID3 String,
CUR_AMOUNT3 String,
CHG_AMOUNT3 String,
MEASURE_ID3 String,
OPER_TYPE3 String,
TOTAL_CURRENT_AMOUNT3 String,
OfferingCode3 String,
OBJECT_TYPE4 String,
OWNER_TYPE4 String,
OWN_Code4 String,
OBJECT_ID4 String,
OBJECT_TYPE_ID4 String,
CUR_AMOUNT4 String,
CHG_AMOUNT4 String,
MEASURE_ID4 String,
OPER_TYPE4 String,
TOTAL_CURRENT_AMOUNT4 String,
OfferingCode4 String,
OBJECT_TYPE5 String,
OWNER_TYPE5 String,
OWN_Code5 String,
OBJECT_ID5 String,
OBJECT_TYPE_ID5 String,
CUR_AMOUNT5 String,
CHG_AMOUNT5 String,
MEASURE_ID5 String,
OPER_TYPE5 String,
TOTAL_CURRENT_AMOUNT5 String,
OfferingCode5 String,
OBJECT_TYPE6 String,
OWNER_TYPE6 String,
OWN_Code6 String,
OBJECT_ID6 String,
OBJECT_TYPE_ID6 String,
CUR_AMOUNT6 String,
CHG_AMOUNT6 String,
MEASURE_ID6 String,
OPER_TYPE6 String,
TOTAL_CURRENT_AMOUNT6 String,
OfferingCode6 String,
OBJECT_TYPE7 String,
OWNER_TYPE7 String,
OWN_Code7 String,
OBJECT_ID7 String,
OBJECT_TYPE_ID7 String,
CUR_AMOUNT7 String,
CHG_AMOUNT7 String,
MEASURE_ID7 String,
OPER_TYPE7 String,
TOTAL_CURRENT_AMOUNT7 String,
OfferingCode7 String,
OBJECT_TYPE8 String,
OWNER_TYPE8 String,
OWN_Code8 String,
OBJECT_ID8 String,
OBJECT_TYPE_ID8 String,
CUR_AMOUNT8 String,
CHG_AMOUNT8 String,
MEASURE_ID8 String,
OPER_TYPE8 String,
TOTAL_CURRENT_AMOUNT8 String,
OfferingCode8 String,
OBJECT_TYPE9 String,
OWNER_TYPE9 String,
OWN_Code9 String,
OBJECT_ID9 String,
OBJECT_TYPE_ID9 String,
CUR_AMOUNT9 String,
CHG_AMOUNT9 String,
MEASURE_ID9 String,
OPER_TYPE9 String,
TOTAL_CURRENT_AMOUNT9 String,
OfferingCode9 String,
OBJECT_TYPE10 String,
OWNER_TYPE10 String,
OWN_Code10 String,
OBJECT_ID10 String,
OBJECT_TYPE_ID10 String,
CUR_AMOUNT10 String,
CHG_AMOUNT10 String,
MEASURE_ID10 String,
OPER_TYPE10 String,
TOTAL_CURRENT_AMOUNT10 String,
OfferingCode10 String,
OBJECT_TYPE11 String,
OWNER_TYPE11 String,
OWN_Code11 String,
OBJECT_ID11 String,
OBJECT_TYPE_ID11 String,
CUR_AMOUNT11 String,
CHG_AMOUNT11 String,
MEASURE_ID11 String,
OPER_TYPE11 String,
TOTAL_CURRENT_AMOUNT11 String,
OfferingCode11 String,
OBJECT_TYPE12 String,
OWNER_TYPE12 String,
OWN_Code12 String,
OBJECT_ID12 String,
OBJECT_TYPE_ID12 String,
CUR_AMOUNT12 String,
CHG_AMOUNT12 String,
MEASURE_ID12 String,
OPER_TYPE12 String,
TOTAL_CURRENT_AMOUNT12 String,
OfferingCode12 String,
OBJECT_TYPE13 String,
OWNER_TYPE13 String,
OWN_Code13 String,
OBJECT_ID13 String,
OBJECT_TYPE_ID13 String,
CUR_AMOUNT13 String,
CHG_AMOUNT13 String,
MEASURE_ID13 String,
OPER_TYPE13 String,
TOTAL_CURRENT_AMOUNT13 String,
OfferingCode13 String,
OBJECT_TYPE14 String,
OWNER_TYPE14 String,
OWN_Code14 String,
OBJECT_ID14 String,
OBJECT_TYPE_ID14 String,
CUR_AMOUNT14 String,
CHG_AMOUNT14 String,
MEASURE_ID14 String,
OPER_TYPE14 String,
TOTAL_CURRENT_AMOUNT14 String,
OfferingCode14 String,
OFFERING_ID String,
PAY_ACCT_Code String,
PAY_OBJECT_TYPE String,
PAY_OBJECT_TYPE_ID String,
DEDUCT_CHARGE_AMOUNT String,
CurrentAmount2_1 String,
TAX_CODE1 String,
IINCLUSIVE_TAX_FLAG1 String,
TAX_AMOUNT1 String,
CHARGE_CODE1 String,
OFFERING_ID1 String,
PAY_ACCT_Code1 String,
PAY_OBJECT_TYPE1 String,
PAY_OBJECT_TYPE_ID1 String,
DEDUCT_CHARGE_AMOUNT1 String,
CurrentAmount2_2 String,
TAX_CODE11 String,
IINCLUSIVE_TAX_FLAG11 String,
TAX_AMOUNT11 String,
CHARGE_CODE2 String,
OFFERING_ID2 String,
PAY_ACCT_Code2 String,
PAY_OBJECT_TYPE2 String,
PAY_OBJECT_TYPE_ID2 String,
DEDUCT_CHARGE_AMOUNT2 String,
CurrentAmount2_3 String,
TAX_CODE12 String,
IINCLUSIVE_TAX_FLAG12 String,
TAX_AMOUNT12 String,
CHARGE_CODE3 String,
OFFERING_ID3 String,
PAY_ACCT_Code3 String,
PAY_OBJECT_TYPE3 String,
PAY_OBJECT_TYPE_ID3 String,
DEDUCT_CHARGE_AMOUNT3 String,
CurrentAmount2_4 String,
TAX_CODE13 String,
IINCLUSIVE_TAX_FLAG13 String,
TAX_AMOUNT13 String,
CHARGE_CODE4 String,
OFFERING_ID4 String,
PAY_ACCT_Code4 String,
PAY_OBJECT_TYPE4 String,
PAY_OBJECT_TYPE_ID4 String,
DEDUCT_CHARGE_AMOUNT4 String,
CurrentAmount2_5 String,
TAX_CODE14 String,
IINCLUSIVE_TAX_FLAG14 String,
TAX_AMOUNT14 String,
CHARGE_CODE5 String,
OFFERING_ID5 String,
PAY_ACCT_Code5 String,
PAY_OBJECT_TYPE5 String,
PAY_OBJECT_TYPE_ID5 String,
DEDUCT_CHARGE_AMOUNT5 String,
CurrentAmount5 String,
TAX_CODE15 String,
IINCLUSIVE_TAX_FLAG15 String,
TAX_AMOUNT15 String,
CHARGE_CODE6 String,
OFFERING_ID6 String,
PAY_ACCT_Code6 String,
PAY_OBJECT_TYPE6 String,
PAY_OBJECT_TYPE_ID6 String,
DEDUCT_CHARGE_AMOUNT6 String,
CurrentAmount6 String,
TAX_CODE16 String,
IINCLUSIVE_TAX_FLAG16 String,
TAX_AMOUNT16 String,
CHARGE_CODE7 String,
OFFERING_ID7 String,
PAY_ACCT_Code7 String,
PAY_OBJECT_TYPE7 String,
PAY_OBJECT_TYPE_ID7 String,
DEDUCT_CHARGE_AMOUNT7 String,
CurrentAmount7 String,
TAX_CODE17 String,
IINCLUSIVE_TAX_FLAG17 String,
TAX_AMOUNT17 String,
CHARGE_CODE8 String,
OFFERING_ID8 String,
PAY_ACCT_Code8 String,
PAY_OBJECT_TYPE8 String,
PAY_OBJECT_TYPE_ID8 String,
DEDUCT_CHARGE_AMOUNT8 String,
CurrentAmount8 String,
TAX_CODE18 String,
IINCLUSIVE_TAX_FLAG18 String,
TAX_AMOUNT18 String,
CHARGE_CODE9 String,
OFFERING_ID9 String,
PAY_ACCT_Code9 String,
PAY_OBJECT_TYPE9 String,
PAY_OBJECT_TYPE_ID9 String,
DEDUCT_CHARGE_AMOUNT9 String,
CurrentAmount9 String,
TAX_CODE19 String,
IINCLUSIVE_TAX_FLAG19 String,
TAX_AMOUNT19 String,
CHG_BALANCE String,
CHG_FREE_UNIT String,
DETAIL_FEE String,
BONUS_OFFERING String,
INSTALMENT String,
SUB_LIFECYCLE String,
MSCAddress String,
SpecialZoneID String,
OpposeMainOfferingID String,
ExtendDaysbycharge String,
SUB_IDEN_TYPE String)
COMMENT 'Retail REC CDRs'
PARTITIONED BY(country STRING, year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS ORC tblproperties ("orc.compress"="ZLIB");
