API tools faq
paste
Guest User

Untitled

a guest
Nov 6th, 2018
119
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.79 KB | None | 0 0
raw download clone embed print report
  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
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!