हाइव जॉइन & उदाहरण के साथ SubQuery ट्यूटोरियल

विषय - सूची:

Anonim

इस ट्यूटोरियल में, आप सीखेंगे-

  • पूछताछ में शामिल हों
  • विभिन्न प्रकार के जोड़
  • उप प्रश्न
  • कस्टम स्क्रिप्ट एम्बेड करना
  • यूडीएफ (उपयोगकर्ता परिभाषित कार्य)

प्रश्नों में शामिल हों:

हाइव में मौजूद दो तालिकाओं पर शामिल हो सकते हैं। स्पष्ट में सम्मिलित हों अवधारणाओं को समझने के लिए यहां हम दो तालिकाओं का निर्माण कर रहे हैं,

  • नमूना_joins (ग्राहकों के विवरण से संबंधित)
  • नमूना_joins1 (कर्मचारियों द्वारा किए गए आदेशों से संबंधित विवरण)

चरण 1) कर्मचारियों के कॉलम नाम आईडी, नाम, आयु, पता और वेतन के साथ तालिका "नमूना_जांच" का निर्माण

चरण 2) डेटा लोड करना और प्रदर्शित करना

ऊपर स्क्रीन शॉट से

  1. Customers.txt से sample_joins में डेटा लोड कर रहा है
  2. नमूना_joins तालिका सामग्री प्रदर्शित करना

चरण 3) sample_joins1 तालिका का निर्माण और डेटा प्रदर्शित करना

उपरोक्त स्क्रीनशॉट से, हम निम्नलिखित देख सकते हैं

  1. स्तंभ नमूना, तारीख 1, आईडी, राशि के साथ तालिका का नमूना_joins1 का निर्माण
  2. Sample.tjoins1 में डेटा को आदेशों से लोड करना
  3. Sample_joins1 में मौजूद रिकॉर्ड प्रदर्शित करना

आगे बढ़ते हुए हम विभिन्न प्रकार के जुड़ावों को देखेंगे जिन्हें हमने बनाई गई तालिकाओं पर किया जा सकता है लेकिन इससे पहले आपको जोड़ों के लिए निम्नलिखित बिंदुओं पर विचार करना होगा।

जोड़ों में निरीक्षण करने के लिए कुछ बिंदु:

  • जोड़ों में केवल समानता जुड़ने की अनुमति है
  • एक ही क्वेरी में दो से अधिक टेबल शामिल हो सकते हैं
  • LEFT, RIGHT, FULL OUTER ज्वाइन होती है, जिसके लिए ON Clause पर अधिक नियंत्रण होता है, जिसके लिए कोई मैच नहीं है
  • जॉइन कम्यूटेटिव नहीं हैं
  • जॉइंट लेफ्ट-एसोसिएटिव हैं चाहे वे LEFT हो या राइट जॉइन

विभिन्न प्रकार के जोड़

जोड़ 4 प्रकार के होते हैं, ये हैं

  • आंतरिक रूप से जुड़ा
  • बाईं ओर का बाहरी जोड़
  • राइट आउटर जॉइन करें
  • पूर्ण बाहरी सम्मिलित हों

आंतरिक रूप से जुड़ा:

दोनों तालिकाओं के लिए सामान्य रिकॉर्ड इस इनर जॉइन द्वारा प्राप्त किया जाएगा।

उपरोक्त स्क्रीनशॉट से, हम निम्नलिखित देख सकते हैं

  1. यहाँ हम टेबल नमूना_joins और नमूना_joins1 के बीच मिलान की स्थिति के रूप में (c.Id = o.Id) के साथ JOIN कीवर्ड का उपयोग करके ज्वाइन क्वेरी का प्रदर्शन कर रहे हैं।
  2. क्वेरी में उल्लिखित स्थिति की जांच करके दोनों तालिका में मौजूद सामान्य रिकॉर्ड प्रदर्शित करता है

प्रश्न:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

बाईं ओर का बाहरी जोड़:

  • हाइव क्वेरी भाषा LEFT OUTER JOIN बाएं टेबल से सभी पंक्तियों को वापस करती है, भले ही सही तालिका में कोई मेल न हों
  • यदि सही तालिका में ON क्लॉज शून्य रिकॉर्ड से मेल खाता है, तो जॉइन्ट अभी भी सही तालिका के प्रत्येक कॉलम में NULL के साथ परिणाम में एक रिकॉर्ड लौटाता है

उपरोक्त स्क्रीनशॉट से, हम निम्नलिखित देख सकते हैं

  1. यहाँ हम टेबल नमूना_joins और नमूना_joins1 के बीच मिलान स्थिति के साथ "c.Id = o.Id" के बीच "LEFT OUTER JOIN" कीवर्ड का उपयोग करके क्वेरी से जुड़ रहे हैं।

    उदाहरण के लिए यहां हम एक संदर्भ के रूप में कर्मचारी आईडी का उपयोग कर रहे हैं, यह जांचता है कि आईडी सही में सामान्य है और साथ ही टेबल छोड़ दिया है या नहीं। यह मिलान स्थिति के रूप में कार्य करता है।

  2. क्वेरी में उल्लिखित स्थिति की जांच करके दोनों तालिका में मौजूद सामान्य रिकॉर्ड प्रदर्शित करता है।

    उपरोक्त आउटपुट में पूर्ण मान सम्‍मिलित तालिका से कोई मान नहीं है, जो नमूना_जॉन्‍स 1 है

प्रश्न:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

सही बाहरी सम्मिलित हों:

  • Hive क्वेरी भाषा RIGHT OUTER JOIN बाईं तालिका में कोई मेल नहीं होने के बावजूद राइट टेबल से सभी पंक्तियों को वापस कर देती है
  • यदि ON Clause लेफ्ट टेबल में जीरो रिकॉर्ड से मेल खाता है, तो लेफ्ट टेबल से प्रत्येक कॉलम में NULL के साथ रिजल्ट में रिकॉर्ड अभी भी वापस आता है।
  • दाएं तालिका से राइट रिकॉर्ड हमेशा जुड़ते हैं और बाएं टेबल से रिकॉर्ड से मेल खाते हैं। यदि बाईं तालिका में कॉलम के अनुरूप कोई मान नहीं है, तो यह उस स्थान पर NULL मान लौटाएगा।

उपरोक्त स्क्रीनशॉट से, हम निम्नलिखित देख सकते हैं

  1. यहाँ हम टेबल नमूना_joins और sample_joins1 के बीच मिलान स्थिति के साथ "RIGHT OUTER JOIN" कीवर्ड का उपयोग करके क्वेरी से जुड़ रहे हैं (c.Id = o.Id)।
  2. क्वेरी में उल्लिखित स्थिति की जांच करके दोनों तालिका में मौजूद सामान्य रिकॉर्ड प्रदर्शित करता है

प्रश्न :

 SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

पूर्ण बाहरी जुड़ाव:

यह क्वेरी में दिए गए JOIN कंडीशन के आधार पर दोनों सारणियों के नमूने sample_joins और नमूना_joins1 को जोड़ता है।

यह दोनों तालिकाओं से सभी रिकॉर्ड लौटाता है और दोनों तरफ से मिलान किए गए कॉलम गुम मूल्यों के लिए NULL मानों में भरता है।

उपरोक्त स्क्रीन शॉट से हम निम्नलिखित देख सकते हैं:

  1. यहाँ हम टेबल नमूना_joins और नमूना_joins1 के बीच मिलान स्थिति के साथ "c.Id = o.Id" के बीच "FULL OUTER JOIN" कीवर्ड का उपयोग करके क्वेरी से जुड़ रहे हैं।
  2. क्वेरी में उल्लिखित शर्त की जाँच करके आउटपुट दोनों तालिका में मौजूद सभी रिकॉर्ड प्रदर्शित करता है। यहां आउटपुट में अशक्त मान दोनों तालिकाओं के स्तंभों से लापता मानों को इंगित करता है।

सवाल

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

उप प्रश्न:

क्वेरी के भीतर मौजूद क्वेरी को उप क्वेरी के रूप में जाना जाता है। मुख्य क्वेरी उपश्रेणियों द्वारा लौटाए गए मूल्यों पर निर्भर करेगी।

उपश्रेणियों को दो प्रकारों में वर्गीकृत किया जा सकता है

  • FROM क्लॉज में उपश्रेणियाँ
  • WHERE क्लॉज में उपश्रेणियाँ

कब इस्तेमाल करें:

  • विभिन्न तालिकाओं से दो स्तंभ मानों से संयुक्त एक विशेष मान प्राप्त करने के लिए
  • अन्य तालिकाओं पर एक तालिका मानों की निर्भरता
  • अन्य तालिकाओं से एक कॉलम मान की तुलनात्मक जाँच

वाक्य - विन्यास:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

उदाहरण:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

यहाँ t1 और t2 तालिका के नाम हैं। रंगीन एक टेबल टी 1 पर किया जाने वाला सबक्वेरी है। यहाँ a और b ऐसे कॉलम हैं जो एक सबक्वेरी में जोड़े जाते हैं और col1 को सौंपे जाते हैं। Col1 मुख्य तालिका में मौजूद स्तंभ मान है। उप-स्तंभ में मौजूद यह कॉलम "col1" कॉलम col1 की मुख्य तालिका क्वेरी के बराबर है।

कस्टम स्क्रिप्ट एम्बेड करना:

हाइव क्लाइंट आवश्यकताओं के लिए उपयोगकर्ता विशिष्ट स्क्रिप्ट लिखने की व्यवहार्यता प्रदान करता है। उपयोगकर्ता अपने स्वयं के मानचित्र को लिखने और आवश्यकताओं के लिए स्क्रिप्ट को कम करने में सक्षम हो सकते हैं। इन्हें एंबेडेड कस्टम स्क्रिप्ट कहा जाता है। कोडिंग तर्क को कस्टम स्क्रिप्ट में परिभाषित किया गया है और हम उस स्क्रिप्ट को ETL समय में उपयोग कर सकते हैं।

एंबेडेड लिपियों का चयन कब करें:

  • क्लाइंट विशिष्ट आवश्यकताओं में डेवलपर्स को हाइव में स्क्रिप्ट लिखना और तैनात करना है
  • जहां हाइव इनबिल्ट फ़ंक्शंस विशिष्ट डोमेन आवश्यकताओं के लिए काम नहीं करने वाले हैं

इसके लिए हाइव में यह मैप और रिड्यूसर स्क्रिप्ट दोनों को एम्बेड करने के लिए ट्रांसफ़ॉर्म क्लॉज़ का उपयोग करता है।

इस एंबेडेड कस्टम स्क्रिप्ट में, हमें निम्नलिखित बिंदुओं को देखना होगा

  • उपयोगकर्ता स्क्रिप्ट को देने से पहले कॉलम को TAB द्वारा स्ट्रिंग और सीमांकित में बदल दिया जाएगा
  • उपयोगकर्ता स्क्रिप्ट के मानक आउटपुट को TAB- पृथक स्ट्रिंग कॉलम के रूप में माना जाएगा

नमूना एंबेडेड स्क्रिप्ट,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING 'reduce_script'AS date, count;

उपरोक्त लिपि से, हम निम्नलिखित देख सकते हैं

यह केवल समझने के लिए नमूना लिपि है

  • pv_users यूज़र्स टेबल है जो कि user_ और फ़ील्ड की तरह है, जैसा कि map_script में वर्णित है
  • Reducer स्क्रिप्ट pv_users तालिकाओं की तिथि और गणना पर परिभाषित की गई है