daily pastebin goal
50%
SHARE
TWEET

Untitled

a guest Nov 6th, 2018 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SQL(gen_par_ins.sql):
  2.  
  3.  
  4.  
  5. select concat('beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/',decode(unhex(hex(59)), 'US-ASCII'),  
  6.  'principal=hive/bigdataplatform-uat.nam.nsroot.net',decode(unhex(hex(64)), 'US-ASCII'),'CTIP.NAM.NSROOT.NET',decode(unhex(hex(59)), 'US-ASCII'),  
  7.  'ssl=true"  --outputformat=csv2 --verbose=false  --fastConnect=true   --silent=true -e  "set hive.exec.dynamic.partition.mode=nonstrict',decode(unhex(hex(59)), 'US-ASCII'),  
  8.  'set hive.merge.mapfiles=true',decode(unhex(hex(59)), 'US-ASCII'),  
  9.  'set hive.merge.mapredfiles=true',decode(unhex(hex(59)), 'US-ASCII'),  
  10.  'set hive.merge.smallfiles.avgsize=67108864',decode(unhex(hex(59)), 'US-ASCII'),  
  11.  'set mapred.max.split.size=67108864',decode(unhex(hex(59)), 'US-ASCII'),  
  12.  'set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;',  
  13.  'insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = \'',pfilter,  
  14.  '\' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.',row_number() over(),'.log  
  15.  ')  
  16.  from (  
  17.  select distinct pfilter from gfocnnsd_standardization.eq_single_order_detail_sf_tmp ) ccc;  
  18.  
  19.  
  20.  
  21.  
  22. Command:
  23.  
  24.  
  25.  
  26. beeline -u 'jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true' \
  27.  
  28. --outputformat=csv2 --verbose=false   --silent=true -f gen_par_ins.sql>par_ins.sh
  29.  
  30.  
  31.  
  32. Result:
  33.  
  34.  
  35.  
  36. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE-20180920.SRC_SYS+099.TICKER_SYMBOL-NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+099.EVENT_TYPE_CD+ALL' sort by trd_date, src_sys,sys_order_id">logs/insp.eq_single_order_detail_sf2.1.log  
  37.    
  38.  
  39. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE+20180920.SRC_SYS+UK.TICKER_SYMBOL+NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+NULL.EVENT_TYPE_CD+NEW_REQ' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.2.log  
  40.  
  41. ...  
  42. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE+20180920.SRC_SYS+UK.TICKER_SYMBOL+NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+NULL.EVENT_TYPE_CD+NEW_CONF' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.3.log  
  43.  
  44.  
  45.  
  46.  
  47.  
  48.  
  49. Now you can execute it.
  50.  
  51.  
  52.  
  53.  
  54.  
  55. FAIL=0  
  56. echo "starting"  
  57. (  
  58. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE-20180920.SRC_SYS+099.TICKER_SYMBOL-NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+099.EVENT_TYPE_CD+ALL' sort by trd_date, src_sys,sys_order_id">logs/insp.eq_single_order_detail_sf2.1.log  
  59.  
  60. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE+20180920.SRC_SYS+UK.TICKER_SYMBOL+NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+NULL.EVENT_TYPE_CD+NEW_REQ' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.2.log  
  61.  
  62. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE+20180920.SRC_SYS+UK.TICKER_SYMBOL+NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+NULL.EVENT_TYPE_CD+NEW_CONF' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.3.log  
  63. )&  
  64.  
  65.  
  66. . . .  
  67.  
  68. (  
  69. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE-20180920.SRC_SYS+099.TICKER_SYMBOL-NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+099.EVENT_TYPE_CD+ALL' sort by trd_date, src_sys,sys_order_id">logs/insp.eq_single_order_detail_sf2.1.log  
  70.  
  71. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE+20180920.SRC_SYS+UK.TICKER_SYMBOL+NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+NULL.EVENT_TYPE_CD+NEW_REQ' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.2.log  
  72.  
  73. beeline -u "jdbc:hive2://bigdataplatform-uat.nam.nsroot.net:10000/;principal=hive/bigdataplatform-uat.nam.nsroot.net@CTIP.NAM.NSROOT.NET;ssl=true"   --fastConnect=true     -e  "set hive.exec.dynamic.partition.mode=nonstrict; set hive.merge.mapfiles=true; set hive.merge.mapredfiles=true; set hive.merge.smallfiles.avgsize=67108864; set mapred.max.split.size=67108864; set mapred.min.split.size=67108864; set hive.exec.parallel=true; set hive.execution.engine=mr;insert overwrite table gfocnnsd_standardization.eq_single_order_detail_sf2 partition(p_year,p_month,p_day,pfilter)  select * from gfocnnsd_standardization.eq_single_order_detail_sf_tmp  where pfilter = 'TRD_DATE+20180920.SRC_SYS+UK.TICKER_SYMBOL+NOTLIKE.REG_NMS_LINK_TYPE+NULL.OCEAN_PRODUCT_ID+099.PRODUCT_CAT_CD+NULL.EVENT_TYPE_CD+NEW_CONF' sort by trd_date, src_sys,sys_order_id" >logs/insp.eq_single_order_detail_sf2.3.log  
  74. )&  
  75.  
  76.  
  77.  
  78. for job in `jobs -p`  
  79. do  
  80. echo $job  
  81.     wait $job || let "FAIL+=1"  
  82. done  
  83.  
  84. echo $FAIL  
  85.  
  86. if [ "$FAIL" == "0" ];  
  87. then  
  88. echo "Part Load DONE"  
  89. else  
  90. echo "FAIL! ($FAIL)"  
  91. fi  
  92.  
  93.  
  94.  
  95.  
  96. Result for 50Gb/ 53 part table:
  97.  
  98.  
  99.  
  100.  
  101.  
  102. Part Load DONE
  103.  
  104.  
  105.  
  106.  
  107.  
  108. real 100m16.758s
  109.  
  110. user 18m23.229s
  111.  
  112. sys 2m3.298s
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top