۱. مرور کلی
بیگکوئری (BigQuery) پایگاه داده تحلیلی کمهزینه، بدون نیاز به عملیات (NoOps) و کاملاً مدیریتشده گوگل است. با بیگکوئری، میتوانید ترابایتها داده را بدون نیاز به هیچ زیرساختی برای مدیریت، جستجو کنید و نیازی به مدیر پایگاه داده ندارید. بیگکوئری از SQL آشنا استفاده میکند و میتواند از مدل پرداخت به ازای استفاده (pay-as-you-go) بهره ببرد. بیگکوئری به شما امکان میدهد تا بر تجزیه و تحلیل دادهها تمرکز کنید تا به بینشهای معناداری برسید.
در این آزمایشگاه کد، شما با استفاده از BigQuery مجموعه دادههای ویکیپدیا را بررسی میکنید.
آنچه یاد خواهید گرفت
- نحوه استفاده از بیگ کوئری
- نحوه بارگذاری یک مجموعه داده واقعی در BigQuery
- چگونه یک کوئری بنویسیم تا بینشی نسبت به یک مجموعه داده بزرگ به دست آوریم
آنچه نیاز دارید
نظرسنجی
چگونه از این آموزش استفاده خواهید کرد؟
تجربه خود را با Google Cloud چگونه ارزیابی میکنید؟
۲. تنظیمات و الزامات
فعال کردن بیگکوئری
اگر از قبل حساب گوگل ندارید، باید یکی ایجاد کنید .
- وارد کنسول گوگل کلود شوید و به BigQuery بروید. همچنین میتوانید با وارد کردن آدرس اینترنتی زیر در مرورگر خود، رابط کاربری وب BigQuery را مستقیماً باز کنید.
https://console.cloud.google.com/bigquery
- شرایط خدمات را بپذیرید.
- قبل از اینکه بتوانید از BigQuery استفاده کنید، باید یک پروژه ایجاد کنید. برای ایجاد پروژه جدید خود، دستورالعملها را دنبال کنید.
یک نام برای پروژه انتخاب کنید و شناسه پروژه را یادداشت کنید. 
شناسه پروژه یک نام منحصر به فرد در تمام پروژههای Google Cloud است که بعداً در این آزمایشگاه کد به عنوان PROJECT_ID به آن اشاره خواهد شد.
این آزمایشگاه کد از منابع BigQuery در محدودهی سندباکس BigQuery استفاده میکند. حساب کاربری لازم نیست. اگر بعداً بخواهید محدودیتهای سندباکس را حذف کنید، میتوانید با ثبتنام در دورهی آزمایشی رایگان Google Cloud، یک حساب کاربری اضافه کنید.
شما مجموعه دادههای ویکیپدیا را در بخش بعدی بارگذاری میکنید.
۳. ایجاد یک مجموعه داده
ابتدا، یک مجموعه داده جدید در پروژه ایجاد کنید. یک مجموعه داده از چندین جدول تشکیل شده است.
- برای ایجاد یک مجموعه داده، روی نام پروژه در زیر پنل منابع کلیک کنید، سپس روی ایجاد مجموعه داده کلیک کنید:
- به عنوان شناسه مجموعه داده،
labوارد کنید:
- برای ایجاد یک مجموعه داده خالی، روی «ایجاد مجموعه داده» کلیک کنید.
۴. بارگذاری دادهها با برنامه خط فرمان bq
فعال کردن پوسته ابری
- از کنسول ابری، روی فعال کردن پوسته ابری کلیک کنید
.
اگر قبلاً Cloud Shell را شروع نکردهاید، یک صفحه میانی (در پایین صفحه) به شما نمایش داده میشود که توضیح میدهد چیست. در این صورت، روی ادامه کلیک کنید (و دیگر هرگز آن را نخواهید دید). آن صفحه یکبار مصرف به این شکل است:
آمادهسازی و اتصال به Cloud Shell فقط چند لحظه طول میکشد.
این ماشین مجازی با تمام ابزارهای توسعه مورد نیاز شما پر شده است. این ماشین یک دایرکتوری خانگی ۵ گیگابایتی دائمی ارائه میدهد و در فضای ابری گوگل اجرا میشود که عملکرد شبکه و احراز هویت را تا حد زیادی بهبود میبخشد. بخش عمدهای از کار شما در این آزمایشگاه کد، اگر نگوییم همه، را میتوان به سادگی با یک مرورگر یا کرومبوک انجام داد.
پس از اتصال به Cloud Shell، باید ببینید که از قبل احراز هویت شدهاید و پروژه از قبل روی شناسه پروژه شما تنظیم شده است.
- برای تأیید احراز هویت، دستور زیر را در 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`
- دستور زیر را در Cloud Shell اجرا کنید تا تأیید کنید که دستور gcloud از پروژه شما اطلاع دارد:
gcloud config list project
خروجی دستور
[core] project = <PROJECT_ID>
اگر اینطور نیست، میتوانید با این دستور آن را تنظیم کنید:
gcloud config set project <PROJECT_ID>
خروجی دستور
Updated property [core/project].
بارگذاری دادهها در BigQuery
برای راحتی شما، برخی از دادههای مربوط به ۱۰ آوریل ۲۰۱۹، از مجموعه دادههای بازدید صفحات ویکیمدیا ، در فضای ذخیرهسازی ابری گوگل به gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz در دسترس هستند. فایل داده یک فایل CSV فشرده شده با GZip است. میتوانید این فایل را مستقیماً با استفاده از ابزار خط فرمان 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
شما از چند گزینه پیشرفته برای بارگذاری فایل page-views استفاده کردهاید:
- مقدار
--source_format CSVطوری تنظیم کنید که نشان دهد فایل باید به صورت فایل CSV تجزیه شود. این مرحله اختیاری است زیرا CSV فرمت پیشفرض است. - برای مشخص کردن اینکه برای جدا کردن فیلدها از یک فاصله استفاده شود، از
--field_delimiter " "استفاده کنید. -
--allow_jagged_rowsطوری تنظیم کنید که ردیفهایی با تعداد ستونهای کمتر را شامل شود و هنگام بارگذاری فایل CSV، خطاها را نادیده بگیرد. - برای نشان دادن اینکه رشتهها بدون نقل قول هستند، از
--quote ""استفاده کنید. - مقدار
--max_bad_records 3قرار دهید تا حداکثر 3 خطا هنگام تجزیه فایل CSV نادیده گرفته شود.
میتوانید اطلاعات بیشتری در مورد خط فرمان bq را در مستندات آن بیابید.
۵. پیشنمایش مجموعه دادهها
در کنسول BigQuery، یکی از جداولی را که بارگذاری کردهاید، باز کنید.
- پروژه را گسترش دهید.
- مجموعه دادهها را گسترش دهید.
- جدول را انتخاب کنید.
میتوانید طرح جدول را در برگه Schema مشاهده کنید. ۴. با رفتن به برگه Details ، میزان دادههای موجود در جدول را بیابید:
- برای مشاهدهی ردیفهای انتخابی از جدول، تب پیشنمایش (Preview ) را باز کنید.
۶. یک پرسوجو بنویسید
- روی ایجاد پرسوجوی جدید کلیک کنید:
این کار ویرایشگر کوئری را باز میکند:
- با نوشتن این کوئری، تعداد کل بازدیدهای ویکیمدیا را بین ساعت ۲ تا ۳ بعد از ظهر در تاریخ ۱۰ آوریل ۲۰۱۹ پیدا کنید:
SELECT SUM(requests) FROM `lab.pageviews_20190410_140000`
- روی اجرا کلیک کنید:
ظرف چند ثانیه، نتیجه در پایین فهرست میشود و همچنین به شما میگوید که چه مقدار داده پردازش شده است:
این پرسوجو ۱۲۳.۹ مگابایت پردازش کرد، در حالی که حجم جدول ۶۹۱.۴ مگابایت است. بیگکوئری فقط بایتهای ستونهایی را پردازش میکند که در پرسوجو استفاده شدهاند، بنابراین حجم کل دادههای پردازششده میتواند بهطور قابلتوجهی کمتر از حجم جدول باشد. با خوشهبندی و پارتیشنبندی ، حجم دادههای پردازششده میتواند حتی بیشتر کاهش یابد.
۷. پرسوجوهای پیشرفتهتر
مشاهده تعداد بازدید صفحات ویکی پدیا
مجموعه دادههای ویکیمدیا شامل تعداد بازدیدهای صفحه برای همه پروژههای ویکیمدیا (از جمله ویکیپدیا، ویکیواژه، ویکیکتاب و ویکیگفتاورد) است. با اضافه کردن یک عبارت WHERE ، پرسوجو را فقط به صفحات انگلیسی ویکیپدیا محدود کنید:
SELECT SUM(requests), wiki FROM `lab.pageviews_20190410_140000` WHERE wiki = "en" GROUP BY wiki
توجه داشته باشید که با پرس و جوی یک ستون اضافی، wiki ، مقدار داده پردازش شده از ۱۲۴ مگابایت به ۲۰۴ مگابایت افزایش یافت.
BigQuery از بسیاری از عبارات SQL آشنا، مانند 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
پرس و جو در چندین جدول
شما میتوانید با استفاده از جدول wildcard، طیفی از جداول را برای تشکیل اتحادیه انتخاب کنید.
- ابتدا، با بارگذاری بازدیدهای صفحه ساعت بعدی در یک جدول جدید، یک جدول دوم برای پرس و جو ایجاد کنید:
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
- در ویرایشگر پرسوجو (Query editor )، با استفاده از پرسوجوی جداولی که پیشوند "
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، جداول را به صورت گزینشیتری فیلتر کنید . این کوئری به جداول مربوط به ۱۰ آوریل محدود میشود.
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
۸. تمیز کردن
در صورت تمایل، مجموعه دادهای که با دستور bq rm ایجاد کردهاید را حذف کنید. از پرچم -r برای حذف جداول موجود در آن استفاده کنید.
bq rm -r lab
۹. تبریک میگویم!
شما از BigQuery و SQL برای پرسوجو در مجموعه دادههای بازدید از صفحات ویکیپدیا در دنیای واقعی استفاده کردید. شما قدرت پرسوجو در مجموعه دادههای در مقیاس پتابایت را دارید!
بیشتر بدانید
- برای اطلاع از نحوهی استفادهی دیگران از BigQuery در حال حاضر، به ساب ردیت BigQuery مراجعه کنید.
- مجموعه دادههای عمومی موجود در BigQuery را پیدا کنید.
- یاد بگیرید چگونه دادهها را در BigQuery بارگذاری کنید .