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

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

BigQuery, Google का पूरी तरह से मैनेज किया जाने वाला, NoOps, और कम लागत वाला डेटाबेस है. 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 का इस्तेमाल नहीं किया है, तो आपको एक इंटरमीडिएट स्क्रीन दिखेगी. इसमें Cloud Shell के बारे में जानकारी दी गई होगी. अगर ऐसा है, तो जारी रखें पर क्लिक करें. इसके बाद, आपको यह स्क्रीन कभी नहीं दिखेगी. एक बार दिखने वाली स्क्रीन ऐसी दिखती है:

70f315d7b402b476.png

Cloud Shell से कनेक्ट होने में कुछ ही सेकंड लगेंगे.

fbe3a0674c982259.png

इस वर्चुअल मशीन में, डेवलपमेंट के लिए ज़रूरी सभी टूल पहले से मौजूद होते हैं. यह 5 जीबी की होम डायरेक्ट्री उपलब्ध कराता है और 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. यह पुष्टि करने के लिए कि gcloud कमांड को आपके प्रोजेक्ट के बारे में पता है, Cloud Shell में यह कमांड चलाएं:
gcloud config list project

कमांड आउटपुट

[core]
project = <PROJECT_ID>

अगर ऐसा नहीं है, तो इस कमांड का इस्तेमाल करके इसे सेट किया जा सकता है:

gcloud config set project <PROJECT_ID>

कमांड आउटपुट

Updated property [core/project].

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

आपकी सुविधा के लिए, 10 अप्रैल, 2019 का कुछ डेटा, Wikimedia पेजव्यू डेटासेट से 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

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

  • --source_format CSV को सेट करके यह बताएं कि फ़ाइल को CSV फ़ाइल के तौर पर पार्स किया जाना चाहिए. यह चरण ज़रूरी नहीं है, क्योंकि CSV डिफ़ॉल्ट फ़ॉर्मैट होता है.
  • --field_delimiter " " को सेट करें, ताकि यह पता चल सके कि फ़ील्ड को अलग करने के लिए एक स्पेस का इस्तेमाल किया गया है.
  • कॉलम की कम संख्या वाली लाइनों को शामिल करने के लिए, --allow_jagged_rows को सेट करें. साथ ही, CSV फ़ाइल लोड करते समय गड़बड़ियों को अनदेखा करें.
  • --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 बजे के बीच Wikimedia पर मिले व्यू की कुल संख्या का पता लगाएं:
SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`
  1. Run पर क्लिक करें:

9abb7c4039961f5b.png

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

a119b65f2ca49e41.png

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

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

Wikipedia पेज के व्यू ढूंढना

Wikimedia के डेटासेट में, Wikimedia के सभी प्रोजेक्ट (इनमें Wikipedia, Wiktionary, Wikibooks, और Wikiquotes शामिल हैं) के पेज व्यू का डेटा शामिल होता है. 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_SUFFIX छद्म कॉलम की मदद से, टेबल को ज़्यादा चुनिंदा तरीके से फ़िल्टर किया जा सकता है. इस क्वेरी में, 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. बधाई हो!

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

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