I-DIY: sikwenza kanjani ngokuzenzakalelayo ukuqapha kwe-warehouse

I-X5 isebenzisa izikhungo zokusabalalisa ezingu-43 kanye namaloli ayo angu-4, iqinisekisa ukutholakala kwemikhiqizo engaphazamiseki ezitolo ezingu-029. Kulesi sihloko ngizokwabelana ngolwazi lwami lokudala uhlelo olusebenzisanayo lokuqapha imicimbi ye-warehouse kusukela ekuqaleni. Ulwazi luyoba usizo kubaphathi bezinkampani ezihwebayo ezinezikhungo eziningana zokusabalalisa ezilawula inhlobonhlobo yemikhiqizo.

Njengomthetho, ukwakhiwa kokuqapha kanye nezinhlelo zokuphatha inqubo yebhizinisi kuqala ngokucubungula imilayezo nezigameko. Ngesikhathi esifanayo, iphuzu elibalulekile lezobuchwepheshe elihlobene nethuba lokuzenzakalela iqiniso lokwenzeka kwemicimbi yebhizinisi kanye nezigameko zokurekhoda liphuthelwe. Amasistimu amaningi ebhizinisi afana ne-WMS, i-TMS, njll., anamathuluzi akhelwe ngaphakathi okuqapha izinqubo zawo. Kodwa, uma lezi kuyizinhlelo ezivela kubakhiqizi abahlukene noma ukusebenza kokuqapha kungathuthukisiwe ngokwanele, kufanele u-ode ukuguqulwa okumba eqolo noma uhehe abaxhumanisi abakhethekile ukuze uthole izilungiselelo ezengeziwe.

Ake sicabangele indlela lapho sidinga khona ingxenye encane yokubonisana ehlobene nokuhlonza imithombo (amathebula) ukuze sithole izinkomba ohlelweni.

Ukucaciswa kwezindawo zethu zokugcina izimpahla ukuthi amasistimu okuphatha ama-warehouse (WMS Exceed) asebenza endaweni eyodwa yezinto. Ama-warehouse ahlukaniswe ngokwezigaba zokugcinwa kwezimpahla (ezomile, utshwala, eqandisiwe, njll.) hhayi nje ngokunengqondo. Ngaphakathi kwenxanxathela yempahla eyodwa kunezakhiwo eziningana ezihlukene ze-warehouse, ngayinye yazo ephethwe yi-WMS yayo.

Ukwakha isithombe esijwayelekile sezinqubo ezenzeka endaweni yokugcina impahla, abaphathi bahlaziya ukubikwa kwe-WMS ngayinye izikhathi eziningana ngosuku, bacubungule imilayezo evela kubaqhubi bezindlu ezigcina impahla (abamukelayo, abacosheli, izitaki) futhi bafingqe izinkomba zokusebenza zangempela ukuze bazibonise ebhodini lemininingwane.

Ukuze konge isikhathi sabaphathi, sinqume ukwakha isistimu engabizi yokulawula ukusebenza kwemicimbi ye-warehouse. Uhlelo olusha, ngaphezu kokubonisa izinkomba “ezishisayo” zokusebenza kwezinqubo ze-warehouse, kufanele futhi zisize abaphathi ekurekhodeni izehlakalo kanye nokuqapha ukuqaliswa kwemisebenzi ukuze kuqedwe izimbangela ezithinta izinkomba ezinikeziwe. Ngemva kokwenza ucwaningo olujwayelekile lwezakhiwo ze-IT zenkampani, saqaphela ukuthi izingxenye ngazinye zesistimu edingekayo sezivele zikhona ngendlela eyodwa noma enye endaweni yethu futhi kubo kukhona kokubili ukuhlolwa kwezilungiselelo kanye nezinsizakalo ezidingekayo zokusekela. Okusele nje ukuletha umqondo wonke kwisixazululo esisodwa sezakhiwo nokulinganisa ububanzi bentuthuko.

Ngemva kokuhlola inani lomsebenzi okufanele wenziwe ukuze kwakhiwe uhlelo olusha, kwanqunywa ukuhlukanisa iphrojekthi ibe izigaba eziningana:

1. Ukuqoqwa kwezinkomba zezinqubo ze-warehouse, ukubona ngeso kanye nokulawulwa kwezinkomba nokuchezuka
2. Ukuzenzakalela kwamazinga enqubo nokubhaliswa kwezicelo kusevisi yamasevisi ebhizinisi ngokuchezuka
3. Ukuqapha okusebenzayo ngokubikezela umthwalo nokudalwa kwezincomo zabaphathi.

Esigabeni sokuqala, uhlelo kufanele luqoqe izingcezu ezilungisiwe zedatha yokusebenza kuzo zonke i-WMS ze-complex. Ukufunda kwenzeka cishe ngesikhathi sangempela (izikhawu ezingaphansi kwemizuzu emi-5). Iqhinga liwukuthi idatha kufanele itholakale ku-DBMS yezindawo zokugcina izimpahla ezimbalwa lapho kuthunyelwa isistimu kuyo yonke inethiwekhi. Idatha yokusebenza etholiwe icutshungulwa ngomqondo womongo wesistimu ukubala ukuchezuka ezinkomba ezihleliwe nokubala izibalo. Idatha ecutshungulwe ngale ndlela kufanele iboniswe kuthebhulethi yomphathi noma ebhodini lemininingwane ye-warehouse ngendlela yamagrafu nemidwebo eqondakalayo.

Lapho sikhetha uhlelo olufanele lokuqaliswa komshayeli wesigaba sokuqala, sikhethe i-Zabbix. Lolu hlelo seluvele lusetshenziselwa ukuqapha ukusebenza kwe-IT kwezinhlelo ze-warehouse. Ngokungeza ukufakwa okuhlukile kokuqoqa amamethrikhi ebhizinisi okusebenza kwe-warehouse, ungathola isithombe esiphelele sempilo yendawo yokugcina impahla.

Ukwakheka okujwayelekile kwesistimu kuvele njengomfanekiso.

Isenzakalo ngasinye se-WMS sichazwa njengomsingathi wesistimu yokuqapha. Amamethrikhi aqoqwa iseva emaphakathi kunethiwekhi yesikhungo sedatha ngokusebenzisa iskripthi esinombuzo olungiselelwe we-SQL. Uma udinga ukuqapha uhlelo olungakukhuthazi ukufinyelela okuqondile ku-database (isibonelo, i-SAP EWM), ungasebenzisa amakholi weskripthi emisebenzini ye-API ebhaliwe ukuze uthole izinkomba noma ubhale uhlelo olulula ku-python/vbascript.

Isibonelo sommeleli we-Zabbix sisetshenziswa kunethiwekhi ye-warehouse ukuze kusatshalaliswe umthwalo kusuka kuseva eyinhloko. Nge-Proxy, ukusebenza nazo zonke izimo ze-WMS zendawo kuqinisekiswa. Ngesikhathi esilandelayo lapho iseva ye-Zabbix icela amapharamitha, iskripthi sisetshenziswa kumsingathi ngommeleli we-Zabbix ukuze kucelwe amamethrikhi kusizindalwazi se-WMS.

Ukuze sibonise amagrafu nezinkomba ze-warehouse kuseva emaphakathi ye-Zabbix, sisebenzisa i-Grafana. Ngokungeziwe ekuboniseni amadeshibhodi alungisiwe ane-infographics yokusebenza kwe-warehouse, i-Grafana izosetshenziselwa ukuqapha ukuchezuka kwezinkomba futhi idlulisele izexwayiso ezizenzakalelayo ohlelweni lwenkonzo ye-warehouse yokusebenza ngezigameko zebhizinisi.

Njengesibonelo, ake sicabangele ukuqaliswa kokulawulwa komthwalo endaweni yokwamukela impahla. Okulandelayo kukhethwe njengezinkomba eziyinhloko zokusebenza kwenqubo kule ndawo yendawo yokugcina impahla:

• inombolo yezimoto endaweni yokwamukela abantu, kucatshangelwa izimo (ezihleliwe, ezifikile, imibhalo, ukuthulula, ukuhamba;
• umthwalo womsebenzi wokubekwa kanye nezindawo zokugcwalisa (ngokwezimo zokugcina).

Izilungiselelo

Ukufakwa nokucushwa kwezingxenye eziyinhloko zesistimu (SQLcl, Zabbix, Grafana) kuchazwe emithonjeni ehlukahlukene futhi ngeke kuphindwe lapha. Ukusetshenziswa kwe-SQLcl esikhundleni se-SQLplus kungenxa yokuthi i-SQLcl (isixhumanisi somugqa womyalo we-Oracle DBMS, ebhalwe nge-java) ayidingi ukufakwa okwengeziwe kwe-Oracle Client futhi isebenza ngaphandle kwebhokisi.

Ngizochaza amaphuzu asemqoka okufanele anakwe lapho usebenzisa i-Zabbix ukuqapha izinkomba zenqubo yebhizinisi le-warehouse, kanye nenye yezindlela ezingenzeka zokuzisebenzisa. Futhi, lokhu akukona okuthunyelwe mayelana nokuphepha. Ukuvikeleka kokuxhumana kanye nokusetshenziswa kwezindlela ezethulwe kudinga ucwaningo olwengeziwe enqubweni yokudlulisela isisombululo sokuhlola ekusebenzeni okukhiqizayo.

Into esemqoka ukuthi lapho usebenzisa uhlelo olunjalo, kungenzeka ukwenza ngaphandle kokuhlela, usebenzisa izilungiselelo ezinikezwe uhlelo.

Isistimu yokuqapha ye-Zabbix ihlinzeka ngezinketho ezimbalwa zokuqoqa amamethrikhi kusistimu egadiwe. Lokhu kungenziwa ngabasingathi abaqashwe ngepholi ngokuqondile, noma ngendlela ethuthuke kakhulu yokuthumela idatha kuseva nge-zabbix_sender yomsingathi, okuhlanganisa nezindlela zokumisa amapharamitha okuthola izinga eliphansi. Ukuxazulula inkinga yethu, indlela yokuvota okuqondile kwababungazi ngeseva emaphakathi ifaneleka impela, ngoba lokhu kukuvumela ukuthi uthole ukulawula okugcwele ngokulandelana kokutholwa kwamamethrikhi futhi kuqinisekisa ukuthi usebenzisa isethi eyodwa yezilungiselelo/izikripthi ngaphandle kwesidingo sokuzisabalalisa kumsingathi ngamunye ogadiwe.

“Njengezihloko zokuhlola” zokulungisa iphutha nokusetha isistimu, sisebenzisa ishidi lokusebenzela le-WMS ekulawuleni ukwamukela:

1. Izimoto ezamukelwayo, zonke ezifikile: Zonke izimoto ezinezimo zenkathi “- amahora angu-72 kusukela esikhathini samanje” - Inkomba yombuzo we-SQL: getCars.
2. Umlando wazo zonke izimo zemoto: Izimo zazo zonke izimoto ezifika phakathi kwamahora angu-72 - Inkomba yombuzo we-SQL: carsHistory.
3. Izimoto ezishejuliwe ukuthi zamukelwe: Izimo zazo zonke izimoto ezifika esimweni "Esihleliwe", isikhawu sesikhathi "- Amahora angu-24" kanye "namahora angu-+24" kusukela esikhathini samanje - Inkomba yombuzo we-SQL: izimotoIn.

Ngakho-ke, ngemva kokuba sesinqume ngesethi yamamethrikhi okusebenza kwe-warehouse, sizolungiselela imibuzo ye-SQL kusizindalwazi se-WMS. Ukwenza imibuzo, kungakuhle ukuthi ungasebenzisi i-database eyinhloko, kodwa ikhophi yayo "eshisayo" - i-standby.

Sixhuma kokubekwe eceleni kwe-Oracle DBMS ukuze samukele idatha. Ikheli le-IP lokuxhuma kusizindalwazi sokuhlola 192.168.1.106. Silondoloza imingcele yoxhumano kuseva ye-Zabbix ku-TNSNames.ORA yefolda esebenzayo ye-SQLcl:

# cat  /opt/sqlcl/bin/TNSNames.ORA
WH1_1=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =  WH1_1)
    )
  )

Lokhu kuzosivumela ukuthi siqhube imibuzo ye-SQL kumsingathi ngamunye nge-EZconnect, sicacisa kuphela igama lokungena/lokungena kanye negama lesizindalwazi:

# sql znew/Zabmon1@WH1_1

Sigcina imibuzo ye-SQL elungisiwe kufolda yokusebenza kuseva ye-Zabbix:

/etc/zabbix/sql

futhi uvumele ukufinyelela kumsebenzisi we-zabbix weseva yethu:

# chown zabbix:zabbix -R /etc/zabbix/sql

Amafayela anezicelo athola isihlonzi-igama eliyingqayizivele ukuze afinyeleleke kusuka kuseva ye-Zabbix. Umbuzo ngamunye wesizindalwazi nge-SQLcl usibuyisela amapharamitha ambalwa. Ngokucabangela imininingwane ye-Zabbix, engacubungula imethrikhi eyodwa kuphela ngesicelo ngasinye, sizosebenzisa imibhalo eyengeziwe ukuze sihlukanise imiphumela yombuzo ibe amamethrikhi angawodwana.

Masilungise umbhalo oyinhloko, masiwubize ngokuthi wh_Metrics.sh, ukuze sibize umbuzo we-SQL kusizindalwazi, silondoloze imiphumela futhi sibuyisele imethrikhi yobuchwepheshe enezinkomba zokuphumelela kokubuyiswa kwedatha:

#!/bin/sh 
## настройка окружения</i>
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:/usr/lib:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
export JAVA_HOME=/
alias sql="opt/sqlcl/bin/sql"
## задаём путь к файлу с sql-запросом и параметризованное имя файла
scriptLocation=/etc/zabbix/sql
sqlFile=$scriptLocation/sqlScript_"$2".sql
## задаём путь к файлу для хранения результатов
resultFile=/etc/zabbix/sql/mon_"$1"_main.log
## настраиваем строку подключения к БД
username="$3"
password="$4"
tnsname="$1"
## запрашиваем результат из БД
var=$(sql -s $username/$password@$tnsname < $sqlFile)
## форматируем результат запроса и записываем в файл
echo $var | cut -f5-18 -d " " > $resultFile
## проверяем наличие ошибок
if grep -q ora "$resultFile"; then
    echo null > $resultFile
    echo 0
else
    echo 1
fi

Sibeka ifayela eliqediwe neskripthi kufolda yokugcina imibhalo yangaphandle ngokuhambisana nezilungiselelo zokucushwa kommeleli we-Zabbix (ngokuzenzakalelayo - /usr/local/share/zabbix/externalscripts).

Ukuhlonzwa kwesizindalwazi lapho iskripthi sizothola khona imiphumela kuzodluliswa njengepharamitha yombhalo. I-ID yesizindalwazi kufanele ifane nomugqa wezilungiselelo kufayela le-TNSNames.ORA.

Umphumela wekholi yombuzo we-SQL ugcinwa efayeleni elifana ne mon_base_id_main.log where base_id = Isihlonzi sesizindalwazi sitholwe njengepharamitha yombhalo. Ukuhlukaniswa kwefayela lomphumela ngezihlonzi zesizindalwazi kunikezwa uma kunezicelo ezivela kuseva eziya kuzigcinilwazi ezimbalwa ngesikhathi esisodwa. Umbuzo ubuyisela amanani afanayo anezinhlangothi ezimbili ezihlungiwe.

Isikripthi esilandelayo, masisibize ngokuthi i-getMetrica.sh, siyadingeka ukuze kutholwe imethrikhi eshiwo efayelini enomphumela wesicelo:

#!/bin/sh 
## определяем имя файла с результатом запроса
resultFile=/etc/zabbix/sql/mon_”$1”_main.log
## разбираем массив значений результата средствами скрипта:
## при работе со статусами, запрос возвращает нам двумерный массив (RSLT) в виде 
## {статус1 значение1 статус2 значение2…} разделённых пробелами (значение IFS)
## параметром запроса передаём код статуса и скрипт вернёт значение
IFS=’ ‘
str=$(cat $resultFile)
status_id=null
read –ra RSLT <<< “$str”
for i in “${RSLT[@]}”; do
if [[ “$status_id” == null ]]; then
status_id=”$I"
elif [[ “$status_id” == “$2” ]]; then
echo “$i”
break
else
status_id=null
fi
done

Manje sesilungele ukumisa i-Zabbix futhi siqale izinkomba zokuqapha zezinqubo zokwamukelwa kwe-warehouse.

I-ejenti ye-Zabbix ifakiwe futhi ilungiselelwe endaweni ngayinye yokugcinwa kwedatha.

Kuseva eyinhloko sichaza wonke amaseva ane-proxy ye-Zabbix. Ukuze uthole izilungiselelo, hamba endleleni elandelayo:

Ukuphatha → Ummeleli → Dala ummeleli

Sichaza abasingathi abalawulwayo:

Izilungiselelo → Abasingathi → Dala umsingathi

Igama lomethuleli kufanele lifane negama lomethuleli elishiwo efayeleni lokumisa umenzeli.

Sicacisa iqembu le-node, kanye nekheli le-IP noma igama le-DNS le-node ene-database.

Sidala amamethrikhi futhi sicacise izici zawo:

Izilungiselelo → Amanodi → 'igama le-node' → Izinto Zedatha>Dala Into Yedatha

1) Dala imethrikhi eyinhloko ukuze ubuze wonke amapharamitha kusizindalwazi

Setha igama le-elementi yedatha, sibonise uhlobo "Ukuqinisekisa kwangaphandle". Esigabeni esithi "Ukhiye", sichaza iskripthi esidlulisela kuso njengamapharamitha igama lesizindalwazi se-Oracle, igama lombuzo we-sql, igama lokungena nephasiwedi yokuxhuma kusizindalwazi. Setha isikhawu sokubuyekeza umbuzo sibe amaminithi angu-5 (amasekhondi angu-300).

2) Dala amamethrikhi asele wesimo ngasinye semoto. Amanani alawa mamethrikhi azokhiqizwa ngokusekelwe kumphumela wokuhlola imethrikhi eyinhloko.

Setha igama le-elementi yedatha, khombisa uhlobo "Ukuqinisekisa kwangaphandle". Kunkambu ethi "Ukhiye", sichaza iskripthi esidlulisela kuso njengamapharamitha igama lesizindalwazi se-Oracle kanye nekhodi yesimo inani laso esifuna ukulilandelelwa. Setha isikhawu sokubuyekeza umbuzo sibe amasekhondi angu-10 ubude kunemethrikhi eyinhloko (amasekhondi angu-310) ukuze imiphumela ibe nesikhathi sokubhalwa efayelini.

Ukuze uthole amamethrikhi ngendlela efanele, i-oda okwenziwa ngalo amasheke asebenze libalulekile. Ukuze ugweme ukungqubuzana lapho sithola idatha, okokuqala sisebenzisa imethrikhi eyinhloko i-GetCarsByStatus ngokubiza umbhalo - wh_Metrics.sh.

Izilungiselelo → Amanodi → 'igama lenodi' → Izakhi zedatha → Isihlungi esingaphansi “Ukuhlola kwangaphandle”. Maka isheke elidingekayo bese uchofoza okuthi "Cupha".

Okulandelayo, senza ama-metrics asele asebenze ekusebenzeni okukodwa, siwakhethe ndawonye:

Manje i-Zabbix isiqalile ukuqoqa amamethrikhi ebhizinisi le-warehouse.

Ezihlokweni ezilandelayo, sizobhekisisa ukuxhuma i-Grafana nokudala amadeshibhodi olwazi okusebenza kwe-warehouse yezigaba ezihlukahlukene zabasebenzisi. I-Grafana iphinde isetshenziselwe ukuqapha ukuchezuka ekusebenzeni kwe-warehouse futhi, kuye ngemingcele nokuphindaphinda kokuchezuka, irejista izigameko ohlelweni lwesikhungo sesikhungo sokuphatha impahla nge-API noma ivele ithumele izaziso kumphathi nge-imeyili.

Source: www.habr.com