پارتیشن بندی و خوشه بندی در BigQuery

1. مقدمه

BigQuery یک انبار داده سازمانی با مدیریت کامل، در مقیاس پتابایت و کم هزینه برای تجزیه و تحلیل است. BigQuery بدون سرور است. شما نیازی به راه اندازی و مدیریت کلاسترها ندارید.

مجموعه داده BigQuery در یک پروژه GCP قرار دارد و شامل یک یا چند جدول است. می توانید این مجموعه داده ها را با SQL پرس و جو کنید.

در این کد لبه، شما از رابط کاربری وب BigQuery در کنسول GCP برای درک پارتیشن بندی و خوشه بندی در BigQuery استفاده خواهید کرد. پارتیشن بندی و خوشه بندی جدول BigQuery به ساختاربندی داده های شما برای مطابقت با الگوهای رایج دسترسی به داده کمک می کند. پارتیشن بندی و خوشه بندی کلیدی برای به حداکثر رساندن کامل عملکرد و هزینه BigQuery هنگام پرس و جو در یک محدوده داده خاص است. این منجر به اسکن داده های کمتر در هر پرس و جو می شود و هرس قبل از زمان شروع پرس و جو تعیین می شود.

توجه: علاوه بر رابط کاربری وب BigQuery، می توانید از ابزار خط فرمان bq برای انجام عملیات روی مجموعه داده های BigQuery استفاده کنید. برای اطلاعات بیشتر در مورد ابزار خط فرمان bq ، به ابزار خط فرمان bq مراجعه کنید.

برای اطلاعات بیشتر درباره BigQuery، به مستندات BigQuery مراجعه کنید.

چیزی که یاد خواهید گرفت

• نحوه ایجاد و پرس و جو جداول پارتیشن بندی شده و خوشه ای
• عملکرد پرس و جو را با جداول پارتیشن بندی شده و خوشه ای مقایسه کنید

آنچه شما نیاز دارید

برای تکمیل این آزمایشگاه به موارد زیر نیاز دارید:

• آخرین نسخه گوگل کروم
• یک حساب صورت‌حساب Google Cloud Platform

2. راه اندازی

برای کار با BigQuery، باید یک پروژه GCP ایجاد کنید یا یک پروژه موجود را انتخاب کنید.

یک پروژه ایجاد کنید

برای ایجاد یک پروژه جدید، مراحل زیر را دنبال کنید:

1. اگر قبلاً یک حساب Google (Gmail یا Google Apps) ندارید، یک حساب ایجاد کنید .
2. به کنسول Google Cloud Platform ( consol.cloud.google.com ) وارد شوید و یک پروژه جدید ایجاد کنید.
3. اگر هیچ پروژه ای ندارید، روی دکمه ایجاد پروژه کلیک کنید:

در غیر این صورت، یک پروژه جدید از منوی انتخاب پروژه ایجاد کنید:

4. نام پروژه را وارد کرده و Create را انتخاب کنید. توجه داشته باشید که ID پروژه، یک نام منحصر به فرد در تمام پروژه های Google Cloud است.

3. کار با مجموعه داده های عمومی

BigQuery به شما امکان می دهد با مجموعه داده های عمومی، از جمله BBC News، مخازن GitHub، Stack Overflow، و مجموعه داده های اداره ملی اقیانوسی و جوی ایالات متحده (NOAA) کار کنید. شما نیازی به بارگیری این مجموعه داده ها در BigQuery ندارید. شما فقط باید مجموعه داده ها را باز کنید تا آنها را در BigQuery جستجو و جستجو کنید. در این کد لبه، شما با مجموعه داده عمومی Stack Overflow کار خواهید کرد.

مجموعه داده های Stack Overflow را مرور کنید

مجموعه داده Stack Overflow حاوی اطلاعاتی درباره پست ها، برچسب ها، نشان ها، نظرات، کاربران و موارد دیگر است. برای مرور مجموعه داده Stack Overflow در رابط کاربری وب BigQuery، این مراحل را دنبال کنید:

1. مجموعه داده Stack Overflow را باز کنید. رابط کاربری وب BigQuery در کنسول GCP باز می شود و اطلاعات مربوط به مجموعه داده Stackoverflow را نمایش می دهد.
2. در پانل پیمایش، bigquery-public-data را انتخاب کنید. منو برای فهرست کردن مجموعه داده های عمومی گسترش می یابد. هر مجموعه داده شامل یک یا چند جدول است.
3. به پایین بروید و stackoverflow را انتخاب کنید. منو برای فهرست کردن جداول در مجموعه داده های Stack Overflow گسترش می یابد.
4. برای مشاهده طرح جدول نشان ها ، نشان ها را انتخاب کنید. به نام فیلدهای جدول توجه کنید.
5. در بالای نام فیلدها، برای مشاهده نمونه داده‌های جدول نشان‌ها، روی پیش‌نمایش کلیک کنید.

برای اطلاعات بیشتر درباره همه مجموعه داده‌های عمومی موجود در BigQuery، به مجموعه داده‌های عمومی Google BigQuery مراجعه کنید.

مجموعه داده Stackoverflow را پرس و جو کنید

مرور یک مجموعه داده راه خوبی برای درک داده هایی است که با آنها کار می کنید، اما جستجو در مجموعه داده ها جایی است که BigQuery واقعاً می درخشد. این بخش به شما آموزش می دهد که چگونه کوئری های BigQuery را اجرا کنید. در این مرحله نیازی به دانستن هیچ SQL ندارید. می توانید درخواست های زیر را کپی و جایگذاری کنید.

برای اجرای پرس و جو، مراحل زیر را انجام دهید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.
2. در قسمت متن ویرایشگر Query ، عبارت SQL زیر را کپی و جایگذاری کنید. BigQuery پرس و جو را تأیید می کند و رابط کاربری وب یک علامت سبز رنگ را در زیر ناحیه متن نشان می دهد تا نشان دهد که نحو معتبر است.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Run را انتخاب کنید. پرس و جو تعداد پست های Stack Overflow یا سوالات ارسال شده در هر سال را برمی گرداند.

توجه: این عبارت از نام جدول کامل برای جدول posts_questions، از جمله نام پروژه، نام مجموعه داده و نام جدول استفاده می‌کند: bigquery-public-data.stackoverflow.posts_questions .

4. ایجاد یک جدول جدید

در بخش قبل، مجموعه داده های عمومی را که BigQuery در دسترس شما قرار می دهد، پرس و جو کردید. در این بخش، یک جدول جدید در BigQuery از یک جدول موجود ایجاد خواهید کرد. شما یک جدول جدید با داده های نمونه برداری شده از جدول posts_questions مجموعه داده عمومی Stack Overflow ایجاد می کنید و سپس جدول را پرس و جو می کنید.

یک مجموعه داده جدید ایجاد کنید

برای ایجاد و بارگذاری داده‌های جدول در BigQuery، ابتدا یک مجموعه داده BigQuery برای نگهداری داده‌ها با تکمیل مراحل زیر ایجاد کنید:

1. در پانل ناوبری کنسول GCP، نام پروژه ایجاد شده به عنوان بخشی از تنظیمات را انتخاب کنید.
2. در سمت راست، در پانل جزئیات، ایجاد مجموعه داده را انتخاب کنید.

3. در گفتگوی ایجاد مجموعه داده ، برای شناسه مجموعه داده ، stackoverflow تایپ کنید. تمام تنظیمات پیش فرض دیگر را در جای خود رها کنید و روی OK کلیک کنید.

یک جدول جدید با پست های StackOverflow 2018 ایجاد کنید

اکنون که یک مجموعه داده BigQuery ایجاد کرده اید، می توانید یک جدول جدید در BigQuery ایجاد کنید. برای ایجاد یک جدول با داده‌ها از یک جدول موجود، مجموعه داده‌های پست‌های پشته سرریز 2018 را پرس و جو کرده و با انجام مراحل زیر، نتایج را در یک جدول جدید بنویسید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.

2. در قسمت متن ویرایشگر Query ، پرس و جوی SQL زیر را کپی و جایگذاری کنید تا یک جدول جدید ایجاد کنید که یک عبارت DDL است.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Run را انتخاب کنید. پرس و جو یک جدول جدید questions_2018 در مجموعه داده stackoverflow در پروژه شما با داده های حاصل از اجرای پرس و جو در مجموعه داده BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions ایجاد می کند.

توجه: این مرحله 2 دقیقه طول می کشد تا یک جدول جدید ایجاد شود. لطفا قبل از ادامه به مرحله بعدی صبر کنید.

جدول جدید را با پست‌های سرریز پشته 2018 جستجو کنید

اکنون که یک جدول BigQuery ایجاد کرده اید، بیایید یک پرس و جو برای بازگرداندن پست های Stack Overflow با سوالات و عناوین همراه با چند آمار دیگر مانند تعداد پاسخ ها، نظرات، بازدیدها و موارد دلخواه اجرا کنیم. مراحل زیر را کامل کنید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.
2. در قسمت متن ویرایشگر Query ، عبارت SQL زیر را کپی و جایگذاری کنید

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Run را انتخاب کنید. این پرس و جو سوالات Stack Overflow ایجاد شده در ماه ژانویه 2018 را برمی گرداند که با عنوان android به همراه سؤال و چند آمار دیگر برچسب گذاری شده اند.
4. به طور پیش فرض، BigQuery نتایج پرس و جو را در حافظه پنهان ذخیره می کند . همان کوئری را اجرا کنید و خواهید دید که BigQuery زمان کمتری برای برگرداندن نتایج صرف کرده است زیرا نتایج را از حافظه پنهان برمی گرداند.
5. دوباره همان پرس و جو را اجرا کنید اما این بار با غیرفعال کردن حافظه پنهان BigQuery. ما کش را برای بقیه قسمت های آزمایشگاه غیرفعال می کنیم تا در مقایسه عملکرد با جداول پارتیشن بندی شده و خوشه ای که در بخش های بعدی اجرا می شوند، منصفانه باشد. در ویرایشگر پرس و جو، روی More کلیک کنید و تنظیمات Query را انتخاب کنید.
6. در قسمت برگزیده Cache ، علامت Use cache results را بردارید.
7. در نتایج پرس و جو، باید مدت زمان تکمیل پرس و جو و حجم داده های پردازش شده برای دریافت نتایج را مشاهده کنید.

5. ایجاد و پرس و جو یک جدول پارتیشن بندی شده

در بخش قبل، یک جدول جدید در BigQuery با داده های جدول posts_questions با استفاده از مجموعه داده عمومی Stack Overflow ایجاد کردید. ما این مجموعه داده را با غیرفعال کردن حافظه پنهان جستجو کردیم و عملکرد پرس و جو را مشاهده کردیم. در این بخش، یک جدول پارتیشن بندی شده جدید از همان جدول posts_questions مجموعه داده عمومی Stack Overflow ایجاد می کنید و عملکرد پرس و جو را مشاهده می کنید.

جدول پارتیشن بندی شده یک جدول ویژه است که به بخش هایی به نام پارتیشن تقسیم می شود که مدیریت و پرس و جو داده های شما را آسان تر می کند. معمولاً می‌توانید جداول بزرگ را با استفاده از زمان انتقال داده یا ستون TIMESTAMP/DATE یا ستون INTEGER به پارتیشن‌های کوچک‌تر تقسیم کنید. ما یک جدول پارتیشن بندی شده DATE ایجاد خواهیم کرد.

در اینجا درباره جداول پارتیشن بندی شده بیشتر بیاموزید.

یک جدول پارتیشن بندی شده جدید با پست های StackOverflow 2018 ایجاد کنید

برای ایجاد یک جدول پارتیشن بندی شده با داده های یک جدول یا پرس و جوی موجود، مجموعه داده پست های Stackoverflow 2018 را پرس و جو می کنید و نتایج را در یک جدول جدید می نویسید، مراحل زیر را کامل کنید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.

2. در قسمت متن ویرایشگر Query ، پرس و جوی SQL زیر را کپی و جایگذاری کنید تا یک جدول جدید ایجاد کنید که یک عبارت DDL است.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Run را انتخاب کنید. این پرس و جو یک جدول جدید questions_2018_partitioned در مجموعه داده stackoverflow در پروژه شما با داده های حاصل از اجرای پرس و جو در مجموعه داده BigQuery Stack Overflow ایجاد می کند bigquery-public-data.stackoverflow.posts_questions

توجه: این مرحله 2 دقیقه طول می کشد تا یک جدول جدید ایجاد شود. لطفا قبل از ادامه به مرحله بعدی صبر کنید.

جدول پارتیشن بندی شده را با پست های سرریز پشته 2018 جستجو کنید

اکنون که یک جدول پارتیشن بندی شده BigQuery ایجاد کرده اید، بیایید همان پرس و جو را، این بار در جدول پارتیشن بندی شده، اجرا کنیم تا پست های Stack Overflow را با سوالات و عناوین به همراه چند آمار دیگر مانند تعداد پاسخ ها، نظرات، بازدیدها و موارد دلخواه برگردانیم. مراحل زیر را کامل کنید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.
2. در قسمت متن ویرایشگر Query ، عبارت SQL زیر را کپی و جایگذاری کنید

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Run with BigQuery caching disabled را انتخاب کنید (بخش قبلی را برای غیرفعال کردن کش BigQuery بررسی کنید). این پرس و جو سوالات Stack Overflow ایجاد شده در ماه ژانویه 2018 را برمی گرداند که با عنوان android به همراه سؤال و چند آمار دیگر برچسب گذاری شده اند.
4. در نتایج پرس و جو، باید مدت زمان تکمیل پرس و جو و حجم داده های پردازش شده برای دریافت نتایج را مشاهده کنید.

باید ببینید که عملکرد پرس و جو با جدول پارتیشن بندی شده بهتر از جدول غیر پارتیشن بندی شده است زیرا BigQuery پارتیشن ها را هرس می کند یعنی فقط پارتیشن های مورد نیاز را اسکن می کند و داده های کمتری را پردازش می کند و سریعتر اجرا می شود. این هزینه های پرس و جو و عملکرد پرس و جو را بهینه می کند.

6. ایجاد و پرس و جو یک جدول خوشه ای

در بخش قبل، یک جدول پارتیشن بندی شده در BigQuery با داده های جدول posts_questions در مجموعه داده عمومی Stack Overflow ایجاد کردید. ما این جدول را با غیرفعال کردن کش جستجو کردیم و عملکرد پرس و جو را با جداول غیر پارتیشن بندی شده و پارتیشن بندی شده مشاهده کردیم. در این بخش، یک جدول خوشه‌بندی‌شده جدید از جدول posts_questions مجموعه داده عمومی Stack Overflow ایجاد می‌کنید و عملکرد پرس و جو را مشاهده می‌کنید.

هنگامی که یک جدول در BigQuery خوشه بندی می شود، داده های جدول به طور خودکار بر اساس محتوای یک یا چند ستون در طرح جدول سازماندهی می شوند. ستون هایی که مشخص می کنید برای تجمیع داده های مرتبط استفاده می شوند. هنگامی که داده ها در یک جدول خوشه ای نوشته می شوند، BigQuery داده ها را با استفاده از مقادیر موجود در ستون های خوشه بندی مرتب می کند. این مقادیر برای سازماندهی داده ها در چندین بلوک در ذخیره سازی BigQuery استفاده می شود. ترتیب ستون های خوشه ای ترتیب مرتب سازی داده ها را تعیین می کند. هنگامی که داده های جدیدی به یک جدول یا یک پارتیشن خاص اضافه می شود، BigQuery دسته بندی مجدد خودکار را در پس زمینه انجام می دهد تا ویژگی مرتب سازی جدول یا پارتیشن را بازیابی کند.

در اینجا درباره کار با جداول خوشه‌ای بیشتر بیاموزید.

یک جدول خوشه ای جدید با پست های سرریز پشته 2018 ایجاد کنید

در این بخش، یک جدول جدید ایجاد می‌کنید که در creation_date پارتیشن بندی شده و بر اساس الگوی دسترسی پرس و جو در ستون tags خوشه‌بندی شده است. برای ایجاد یک جدول خوشه‌بندی شده با داده‌های یک جدول یا پرس و جوی موجود، جدول پست‌های Stack Overflow 2018 را پرس و جو می‌کنید و با انجام مراحل زیر، نتایج را در یک جدول جدید می‌نویسید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.

2. در قسمت متن ویرایشگر Query ، پرس و جوی SQL زیر را کپی و جایگذاری کنید تا یک جدول جدید ایجاد کنید که یک عبارت DDL است.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Run را انتخاب کنید. پرس و جو یک جدول جدید questions_2018_clustered در مجموعه داده stackoverflow در پروژه شما با داده های حاصل از اجرای پرس و جو در جدول BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions ایجاد می کند. جدول جدید در ایجاد_تاریخ پارتیشن بندی شده و در ستون تگ ها خوشه بندی می شود.

توجه: این مرحله 2 دقیقه طول می کشد تا یک جدول جدید ایجاد شود. لطفا قبل از ادامه به مرحله بعدی صبر کنید.

جدول خوشه‌ای را با پست‌های سرریز پشته ۲۰۱۸ جستجو کنید

اکنون که یک جدول خوشه ای BigQuery ایجاد کرده اید، بیایید دوباره همان پرس و جو را، این بار در جدول پارتیشن بندی شده و خوشه بندی شده، اجرا کنیم تا پست های Stack Overflow با سوالات و عناوین به همراه چند آمار دیگر مانند تعداد پاسخ ها، نظرات، بازدیدها را برگردانیم. و موارد دلخواه مراحل زیر را کامل کنید:

1. نزدیک سمت راست بالای کنسول GCP، نوشتن عبارت جدید را انتخاب کنید.
2. در قسمت متن ویرایشگر Query ، عبارت SQL زیر را کپی و جایگذاری کنید

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Run with BigQuery caching disabled را انتخاب کنید (بخش قبلی را برای غیرفعال کردن کش BigQuery بررسی کنید). این پرس و جو سوالات Stack Overflow ایجاد شده در ماه ژانویه 2018 را برمی گرداند که با عنوان android به همراه سؤال و چند آمار دیگر برچسب گذاری شده اند.
4. در نتایج پرس و جو، باید مدت زمان تکمیل پرس و جو و حجم داده های پردازش شده برای دریافت نتایج را مشاهده کنید.

با یک جدول پارتیشن بندی شده و خوشه ای، پرس و جو داده های کمتری را نسبت به یک جدول پارتیشن بندی شده یا یک جدول غیرپارتیشن بندی شده اسکن می کند. روشی که داده ها توسط پارتیشن بندی و خوشه بندی سازماندهی می شوند، میزان داده های اسکن شده توسط Slot Workers را به حداقل می رساند و در نتیجه عملکرد پرس و جو را بهبود می بخشد و هزینه ها را بهینه می کند.

7. تمیز کردن

مگر اینکه قصد ادامه کار با مجموعه داده stackoverflow خود را داشته باشید، باید آن را حذف کنید و پروژه ای را که برای این Codelab ایجاد کرده اید حذف کنید.

مجموعه داده BigQuery را حذف کنید

برای حذف مجموعه داده BigQuery، مراحل زیر را انجام دهید:

1. مجموعه داده stackoverflow را از پانل پیمایش سمت چپ در BigQuery انتخاب کنید.
2. در پانل جزئیات، حذف مجموعه داده را انتخاب کنید.
3. در گفتگوی حذف مجموعه داده ، stackoverflow را وارد کرده و Delete را انتخاب کنید تا تأیید کنید که می خواهید مجموعه داده را حذف کنید.

پروژه را حذف کنید

برای حذف پروژه GCP که برای این کد لبه ایجاد کردید، مراحل زیر را انجام دهید:

1. در منوی پیمایش GCP، IAM & Admin را انتخاب کنید.
2. در پانل پیمایش، تنظیمات را انتخاب کنید.
3. در پانل جزئیات، تأیید کنید که پروژه فعلی شما پروژه ای است که برای این کد لبه ایجاد کرده اید و Shut down را انتخاب کنید.
4. در محاوره Shut down project ، شناسه پروژه (نه نام پروژه) را برای پروژه خود وارد کنید و برای تایید Shut down را انتخاب کنید.

تبریک می گویم! الان یاد گرفتی

• نحوه استفاده از رابط کاربری وب BigQuery برای ایجاد یک جدول جدید از جداول موجود
• نحوه ایجاد و پرس و جو جداول پارتیشن بندی شده و خوشه ای
• چگونه پارتیشن بندی و خوشه بندی عملکرد و هزینه پرس و جو را بهینه می کند

توجه داشته باشید که برای کار با مجموعه داده ها نیازی به راه اندازی یا مدیریت خوشه ها ندارید.