BigQuery में Wikipedia डेटासेट की क्वेरी करना

1. खास जानकारी

BigQuery, Google का पूरी तरह से मैनेज किया गया NoOps, कम लागत वाला Analytics डेटाबेस है. BigQuery की मदद से, टेराबाइट और टेराबाइट डेटा को क्वेरी किया जा सकता है. इसके लिए, मैनेज करने के लिए किसी इंफ़्रास्ट्रक्चर की ज़रूरत नहीं होती. साथ ही, आपको डेटाबेस एडमिन की भी ज़रूरत नहीं पड़ती. BigQuery, जाने-पहचाने एसक्यूएल का इस्तेमाल करता है और यह इस्तेमाल के हिसाब से पैसे चुकाने वाले मॉडल का फ़ायदा ले सकता है. BigQuery की मदद से काम की इनसाइट पाने के लिए, डेटा का विश्लेषण किया जा सकता है.

इस कोडलैब में, BigQuery का इस्तेमाल करके Wikipedia डेटासेट के बारे में जाना जा सकता है.

आप इन चीज़ों के बारे में जानेंगे

  • BigQuery का इस्तेमाल कैसे करें
  • BigQuery में रीयल-वर्ल्ड डेटासेट लोड करने का तरीका
  • किसी बड़े डेटासेट के बारे में अहम जानकारी पाने के लिए, क्वेरी लिखने का तरीका

आपको इनकी ज़रूरत होगी

  • Google Cloud प्रोजेक्ट
  • ब्राउज़र, जैसे कि Google Chrome या Firefox

सर्वे

इस ट्यूटोरियल का इस्तेमाल कैसे किया जाएगा?

रीड ओनली इसे पढ़ें और कसरतों को पूरा करें

Google Cloud के साथ अपने अनुभव को कैसे रेटिंग दें?

शुरुआती इंटरमीडिएट कुशल

2. सेटअप और ज़रूरी शर्तें

BigQuery चालू करना

अगर आपके पास पहले से कोई Google खाता नहीं है, तो आपको एक खाता बनाना होगा.

  1. Google Cloud Console में साइन इन करें और BigQuery पर जाएं. आपके पास सीधे अपने ब्राउज़र में यह यूआरएल डालकर भी BigQuery वेब यूज़र इंटरफ़ेस (यूआई) खोलने का विकल्प है.
https://console.cloud.google.com/bigquery
  1. सेवा की शर्तें स्वीकार करें.
  2. BigQuery का इस्तेमाल करने से पहले, आपको एक प्रोजेक्ट बनाना होगा. अपना नया प्रोजेक्ट बनाने के लिए निर्देशों का पालन करें.

प्रोजेक्ट का कोई नाम चुनें और प्रोजेक्ट आईडी नोट कर लें. 1884405a64ce5765.png अभी तक किसी भी व्यक्ति ने चेक इन नहीं किया है

प्रोजेक्ट आईडी, Google Cloud के सभी प्रोजेक्ट के लिए एक यूनीक नाम होता है. बाद में, इस कोडलैब को इस कोडलैब में PROJECT_ID के तौर पर दिखाया जाएगा.

यह कोडलैब, BigQuery सैंडबॉक्स की सीमाओं में मौजूद BigQuery संसाधनों का इस्तेमाल करता है. बिलिंग खाता होना ज़रूरी नहीं है. अगर आपको बाद में सैंडबॉक्स की सीमाओं को हटाना हो, तो Google Cloud के मुफ़्त ट्रायल के लिए साइन अप करके बिलिंग खाता जोड़ा जा सकता है.

अगले सेक्शन में Wikipedia डेटासेट लोड किया जाता है.

3. डेटासेट बनाना

सबसे पहले, प्रोजेक्ट में एक नया डेटासेट बनाएं. डेटासेट में कई टेबल होती हैं.

  1. डेटासेट बनाने के लिए, संसाधन पैनल में जाकर प्रोजेक्ट के नाम पर क्लिक करें. इसके बाद, डेटासेट बनाएं पर क्लिक करें:

4a5983b4dc299705.png

  1. डेटासेट आईडी के तौर पर lab डालें:

a592b5b9be20fdec.png

  1. खाली डेटासेट बनाने के लिए, डेटासेट बनाएं पर क्लिक करें.

4. bq कमांड-लाइन प्रोग्राम की मदद से डेटा लोड करें

Cloud Shell चालू करें

  1. Cloud Console में, Cloud Shell चालू करें 4292cbf4971c9786.png पर क्लिक करें.

bce75f34b2c53987.png

अगर आपने Cloud Shell का इस्तेमाल पहले कभी नहीं किया है, तो आपको इसके बारे में जानकारी देने वाली एक इंटरमीडिएट स्क्रीन (पेज के फ़ोल्ड के नीचे) दिखेगी. अगर ऐसा है, तो जारी रखें पर क्लिक करें (यह आपको फिर कभी नहीं दिखेगा). एक बार इस्तेमाल होने वाली स्क्रीन कुछ इस तरह दिखती है:

70f315d7b402b476.png

प्रावधान करने और Cloud Shell से कनेक्ट होने में कुछ ही समय लगेगा.

fbe3a0674c982259.png

इस वर्चुअल मशीन में ऐसे सभी डेवलपमेंट टूल मौजूद हैं जिनकी आपको ज़रूरत है. यह पांच जीबी की स्थायी होम डायरेक्ट्री उपलब्ध कराता है और Google Cloud में चलता है. यह नेटवर्क की परफ़ॉर्मेंस और पुष्टि करने की प्रोसेस को बेहतर बनाता है. अगर सभी नहीं, तो इस कोडलैब में आपका बहुत सारा काम बस किसी ब्राउज़र या आपके Chromebook से किया जा सकता है.

Cloud Shell से कनेक्ट करने के बाद, आपको दिखेगा कि आपकी पुष्टि पहले ही हो चुकी है. साथ ही, यह प्रोजेक्ट पहले से ही आपके प्रोजेक्ट आईडी पर सेट है.

  1. यह पुष्टि करने के लिए Cloud Shell में नीचे दिया गया कमांड चलाएं कि आपकी पुष्टि हो गई है:
gcloud auth list

कमांड आउटपुट

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`
  1. Cloud Shell में यह कमांड चलाएं, ताकि यह पुष्टि की जा सके कि gcloud के लिए कमांड को आपके प्रोजेक्ट के बारे में जानकारी है:
gcloud config list project

कमांड आउटपुट

[core]
project = <PROJECT_ID>

अगर ऐसा नहीं है, तो आप इसे इस निर्देश की मदद से सेट कर सकते हैं:

gcloud config set project <PROJECT_ID>

कमांड आउटपुट

Updated property [core/project].

BigQuery में डेटा लोड करना

आपकी सुविधा के लिए, विकीमीडिया पेज व्यू के डेटासेट से 10 अप्रैल, 2019 का कुछ डेटा, Google Cloud Storage पर gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz पर उपलब्ध है. डेटा फ़ाइल, GZip की CSV फ़ाइल है. bq कमांड-लाइन सुविधा का इस्तेमाल करके, इस फ़ाइल को सीधे लोड किया जा सकता है. लोड कमांड के हिस्से के तौर पर, फ़ाइल की स्कीमा के बारे में भी बताया जाता है.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

पेज-व्यू फ़ाइल को लोड करने के लिए, आपने कुछ बेहतर विकल्पों का इस्तेमाल किया है:

  • फ़ाइल को CSV फ़ाइल के तौर पर पार्स करने के लिए, --source_format CSV सेट करें. यह चरण ज़रूरी नहीं है, क्योंकि CSV डिफ़ॉल्ट फ़ॉर्मैट है.
  • --field_delimiter " " सेट करके यह बताएं कि फ़ील्ड का दायरा बढ़ाने के लिए एक ही स्पेस का इस्तेमाल किया जाता है.
  • CSV फ़ाइल लोड करते समय गड़बड़ियों को अनदेखा करने के लिए, --allow_jagged_rows को सेट करें, ताकि कॉलम की कम संख्या वाली पंक्तियां शामिल की जा सकें.
  • यह बताने के लिए कि स्ट्रिंग को कोट नहीं किया गया है, --quote "" को सेट करें.
  • CSV फ़ाइल पार्स करते समय, ज़्यादा से ज़्यादा तीन गड़बड़ियों को अनदेखा करने के लिए, --max_bad_records 3 को सेट करें.

bq कमांड लाइन के बारे में ज़्यादा जानने के लिए, दस्तावेज़ पढ़ें.

5. डेटासेट की झलक देखें

BigQuery कंसोल में, उस टेबल को खोलें जिसे आपने अभी-अभी लोड किया है.

  1. प्रोजेक्ट को बड़ा करें.
  2. डेटासेट को बड़ा करें.
  3. टेबल चुनें. 99f875c838ed9a58.png अभी तक किसी भी व्यक्ति ने चेक इन नहीं किया है

स्कीमा टैब में, टेबल स्कीमा देखा जा सकता है. 4. ब्यौरा टैब पर जाकर देखें कि टेबल में कितना डेटा है:

742cd54fbf17085.png

  1. टेबल से चुनी गई पंक्तियां देखने के लिए, झलक देखें टैब खोलें.

397a9c25480735cc.png

6. क्वेरी लिखें

  1. नई क्वेरी लिखें पर क्लिक करें:

cc28282a25c9746e.png

यहां आपको क्वेरी एडिटर दिखेगा:

e881286d275ab4ec.png

  1. यह क्वेरी लिखकर, 10 अप्रैल, 2019 को दोपहर 2 से 3 बजे के बीच विकीमीडिया पर मिले व्यू की कुल संख्या देखें:
SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`
  1. Run पर क्लिक करें:

9abb7c4039961f5b.png

कुछ ही सेकंड में, नतीजा स्क्रीन पर सबसे नीचे दिखने लगता है और यह आपको यह भी बताता है कि कितना डेटा प्रोसेस हुआ है:

a119b65f2ca49e41.png

टेबल का साइज़ 691.4 एमबी होने के बावजूद, इस क्वेरी का प्रोसेस 123.9 एमबी है. BigQuery सिर्फ़ क्वेरी में इस्तेमाल किए गए कॉलम की बाइट प्रोसेस करता है. इसलिए, प्रोसेस किए गए डेटा की कुल संख्या, टेबल के साइज़ से काफ़ी कम हो सकती है. क्लस्टरिंग और पार्टिशन की मदद से, प्रोसेस किए गए डेटा की मात्रा को और भी कम किया जा सकता है.

7. ज़्यादा बेहतर क्वेरी

Wikipedia के पेज व्यू देखना

विकीमीडिया डेटासेट में सभी विकिमीडिया प्रोजेक्ट (इसमें विकिपीडिया, विकीबुक, और विकीकोट शामिल हैं) के पेज व्यू शामिल हैं. WHERE कथन जोड़कर क्वेरी को केवल अंग्रेज़ी Wikipedia पेजों तक सीमित करें:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

d6c6c7901c314da7.png

ध्यान दें कि एक अतिरिक्त कॉलम, wiki की क्वेरी करने पर, प्रोसेस किया गया डेटा 124 एमबी से बढ़कर 204 एमबी हो गया है.

BigQuery में, कई जाने-पहचाने एसक्यूएल क्लॉज़ का इस्तेमाल होता है, जैसे कि CONTAINS, GROUP BY, ORDER BY, और कई एग्रीगेशन फ़ंक्शन. इसके अलावा, क्वेरी टेक्स्ट फ़ील्ड के लिए भी रेगुलर एक्सप्रेशन का इस्तेमाल किया जा सकता है! कोई एक तरीका आज़माएं:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

कई टेबल में क्वेरी करना

वाइल्डकार्ड टेबल का इस्तेमाल करके, यूनियन बनाने के लिए टेबल की एक रेंज चुनी जा सकती है.

  1. सबसे पहले, अगले घंटे के पेज व्यू को नई टेबल में लोड करके, क्वेरी करने के लिए दूसरी टेबल बनाएं:
bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer
  1. क्वेरी एडिटर में, "pageviews_2019" वाली टेबल का इस्तेमाल करके लोड की गई दोनों टेबल पर क्वेरी करें प्रीफ़िक्स के तौर पर:
SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

आप _TABLE_ पैरामीटर स्यूडो कॉलम का इस्तेमाल करके, टेबल को और भी बेहतर तरीके से फ़िल्टर कर सकते हैं. यह क्वेरी सिर्फ़ 10 अप्रैल के लिए टेबल तक सीमित है.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. साफ़ किया जा रहा है

आप चाहें, तो bq rm कमांड का इस्तेमाल करके बनाए गए डेटासेट को मिटाएं. इसमें मौजूद किसी भी टेबल को हटाने के लिए, -r फ़्लैग का इस्तेमाल करें.

bq rm -r lab

9. बधाई हो!

आपने असल दुनिया में Wikipedia के पेज व्यू के डेटासेट की क्वेरी करने के लिए, BigQuery और SQL का इस्तेमाल किया. आपके पास पेटाबाइट-स्केल डेटासेट की क्वेरी करने की क्षमता है!

ज़्यादा जानें