۱. مقدمه
بیگکوئری (BigQuery) پایگاه داده تحلیلی کمهزینه، بدون عملیات و کاملاً مدیریتشده گوگل است. با بیگکوئری، میتوانید ترابایتها داده را بدون نیاز به مدیر پایگاه داده یا زیرساخت، جستجو کنید. بیگکوئری از SQL آشنا و مدل پرداخت «فقط برای آنچه استفاده میکنید، هزینه کنید» استفاده میکند. بیگکوئری به شما امکان میدهد تا بر تجزیه و تحلیل دادهها برای یافتن بینشهای معنادار تمرکز کنید. در این آزمایشگاه کد، از ابزار خط فرمان bq برای بارگذاری یک فایل CSV محلی در یک جدول جدید بیگکوئری استفاده خواهید کرد.
آنچه یاد خواهید گرفت
- نحوه استفاده از ابزار خط فرمان bq برای BigQuery
- نحوه بارگذاری فایلهای داده محلی در جدول BigQuery
آنچه نیاز دارید
- یک پروژه ابری گوگل
- یک مرورگر، مثلاً گوگل کروم
۲. آماده شوید
فعال کردن بیگکوئری
اگر از قبل حساب گوگل ندارید، باید یکی ایجاد کنید .
- وارد کنسول گوگل کلود شوید و به BigQuery بروید. همچنین میتوانید با وارد کردن آدرس اینترنتی زیر در مرورگر خود، رابط کاربری وب BigQuery را مستقیماً باز کنید.
https://console.cloud.google.com/bigquery
- شرایط خدمات را بپذیرید.
- قبل از اینکه بتوانید از BigQuery استفاده کنید، باید یک پروژه ایجاد کنید. برای ایجاد پروژه جدید خود، دستورالعملها را دنبال کنید.
یک نام برای پروژه انتخاب کنید و شناسه پروژه را یادداشت کنید. 
شناسه پروژه یک نام منحصر به فرد در تمام پروژههای Google Cloud است که بعداً در این آزمایشگاه کد به عنوان PROJECT_ID به آن اشاره خواهد شد.
این آزمایشگاه کد از منابع BigQuery با محدودیتهای سندباکس BigQuery استفاده میکند. حساب کاربری صورتحساب لازم نیست. اگر بعداً بخواهید محدودیتهای سندباکس را حذف کنید، میتوانید با ثبتنام در دوره آزمایشی رایگان Google Cloud، یک حساب کاربری صورتحساب اضافه کنید.
پوسته ابری
شما از Cloud Shell ، یک محیط خط فرمان که در Google Cloud اجرا میشود، استفاده خواهید کرد.
فعال کردن پوسته ابری
- از کنسول ابری، روی فعال کردن پوسته ابری کلیک کنید
.
اگر قبلاً 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 مجموعهای از جداول است. تمام جداول موجود در یک مجموعه داده در یک مکان داده ذخیره میشوند. همچنین میتوانید کنترلهای دسترسی سفارشی را برای محدود کردن دسترسی به یک مجموعه داده و جداول آن پیوست کنید.
ایجاد مجموعه داده
در Cloud Shell، از دستور bq mk برای ایجاد یک مجموعه داده به نام " bq_load_codelab " استفاده کنید.
bq mk bq_load_codelab
مشاهده ویژگیهای مجموعه دادهها
با مشاهده ویژگیهای مجموعه داده با دستور bq show، تأیید کنید که مجموعه داده را ایجاد کردهاید.
bq show bq_load_codelab
باید خروجی مشابه زیر را ببینید:
Dataset my-project:bq_load_codelab
Last modified ACLs Labels
----------------- -------------------- --------
15 Jun 14:12:49 Owners:
projectOwners,
your-email@example.com
Writers:
projectWriters
Readers:
projectReaders
۴. فایل داده را ایجاد کنید
BigQuery میتواند دادهها را از چندین قالب داده، از جمله JSON با خط جدید ، Avro و CSV بارگذاری کند. برای سادگی، از CSV استفاده خواهید کرد.
ایجاد فایل CSV
در Cloud Shell، یک فایل CSV خالی ایجاد کنید.
touch customer_transactions.csv
با اجرای دستور cloudshell edit، فایل CSV را در ویرایشگر کد در Cloud Shell باز کنید، که یک پنجره مرورگر جدید با ویرایشگر کد و پنل Cloud Shell باز میکند.
cloudshell edit customer_transactions.csv
در ویرایشگر کد، مقادیری را که با کاما از هم جدا شدهاند وارد کنید تا در BigQuery بارگذاری شوند.
ID,Zipcode,Timestamp,Amount,Feedback,SKU c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5 c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0
فایل CSV را با کلیک روی File > Edit ذخیره کنید.
۵. بارگذاری دادهها
از دستور bq load برای بارگذاری فایل CSV خود در جدول BigQuery استفاده کنید.
bq load \
--source_format=CSV \
--skip_leading_rows=1 \
bq_load_codelab.customer_transactions \
./customer_transactions.csv \
id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string
شما از گزینههای زیر استفاده کردید:
-
--source_format=CSVهنگام تجزیه فایل داده از قالب داده CSV استفاده میکند. -
--skip_leading_rows=1 از خط اول فایل CSV صرف نظر میکند زیرا یک ردیف سرصفحه است. -
Bq_load_codelab.customer_transactions—the first positional argument—تعریف میکند که دادهها باید در کدام جدول بارگذاری شوند. -
./customer_transactions.csv— دومین آرگومان موقعیتی — فایلی را که باید بارگذاری شود تعریف میکند. علاوه بر فایلهای محلی، دستور bq load میتواند فایلها را از Cloud Storage باgs://my_bucket/path/to/file URIsبارگذاری کند. - یک طرحواره، که میتواند در یک فایل طرحواره JSON یا به صورت یک لیست جدا شده با کاما تعریف شود. (شما برای سادگی از یک لیست جدا شده با کاما استفاده کردید.)
شما از طرحواره زیر در جدول customer_transactions استفاده کردهاید:
-
Id:string: شناسه مشتری -
Zip:string: کد پستی ایالات متحده -
Ttime:timestamp: تاریخ و زمان انجام تراکنش -
Amount:numeric: مبلغ تراکنش (یک ستون عددی دادهها را به صورت اعشاری ذخیره میکند که برای مقادیر پولی مفید است.) -
Fdbk:float: امتیاز حاصل از یک نظرسنجی بازخورد در مورد تراکنش -
Sku:string: شناسهای برای کالای خریداری شده
جزئیات جدول را دریافت کنید
با نمایش ویژگیهای جدول، از بارگذاری صحیح جدول اطمینان حاصل کنید.
bq show bq_load_codelab.customer_transactions
خروجی:
Table my-project:bq_load_codelab.customer_transactions
Last modified Schema Total Rows Total Bytes
----------------- --------------------- ------------ -------------
15 Jun 15:13:55 |- id: string 3 159
|- zip: string
|- ttime: timestamp
|- amount: numeric
|- fdbk: float
|- sku: string
۶. پرسوجوی دادهها
اکنون که دادههای شما بارگذاری شده است، میتوانید با استفاده از رابط کاربری وب BigQuery ، دستور bq یا API، آن را جستجو کنید. جستجوهای شما میتوانند دادههای شما را در برابر هر مجموعه دادهای (یا مجموعه دادههایی، تا زمانی که در یک مکان باشند) که اجازه خواندن آنها را دارید، به هم متصل کنند.
یک کوئری SQL استاندارد اجرا کنید که مجموعه دادههای شما را با مجموعه دادههای کدهای پستی ایالات متحده پیوند دهد و تراکنشها را بر اساس ایالت ایالات متحده خلاصه کند. برای اجرای کوئری از دستور bq query استفاده کنید.
bq query --nouse_legacy_sql ' SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code FROM `bq_load_codelab.customer_transactions` c JOIN `bigquery-public-data.utility_us.zipcode_area` z ON c.zip = z.zipcode GROUP BY state_code '
آن دستور باید چیزی شبیه به این خروجی بدهد:
Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE +--------------+------------+ | amount_total | state_code | +--------------+------------+ | 53.6 | NY | | 7.18 | TX | +--------------+------------+
پرسوجویی که شما اجرا کردید از یک مجموعه داده عمومی و مجموعه داده خصوصی شما استفاده کرد. با خواندن این نسخه کامنتگذاری شده از همان پرسوجو، اطلاعات بیشتری کسب کنید:
#standardSQL SELECT /* Total of all transactions in the state. */ SUM(c.amount) AS amount_total, /* State corresponding to the transaction's zipcode. */ z.state_code AS state_code /* Query the table you just constructed. * Note: If you omit the project from the table ID, * the dataset is read from your project. */ FROM `bq_load_codelab.customer_transactions` c /* Join the table to the zipcode public dataset. */ JOIN `bigquery-public-data.utility_us.zipcode_area` z /* Find the state corresponding to the transaction's zipcode. */ ON c.zip = z.zipcode /* Group over all transactions by state. */ GROUP BY state_code
۷. تمیز کردن
مجموعه دادهای که با دستور bq rm ایجاد کردهاید را حذف کنید. از پرچم -r برای حذف جداول موجود در آن استفاده کنید.
bq rm -r bq_load_codelab
۸. تبریک میگویم!
شما یک جدول را در BigQuery آپلود کردید و از آن کوئری گرفتید!
آنچه را که پوشش دادید
- استفاده از ابزار خط فرمان
bqبرای تعامل با BigQuery. - اتصال دادههای شما و یک مجموعه داده عمومی با استفاده از یک کوئری BigQuery.
قدم بعدی چیست؟
اطلاعات بیشتر در مورد:
- ابزار خط فرمان
bq - روشهای دیگر برای بارگذاری دادهها در BigQuery
- سایر مجموعه دادههای عمومی موجود از طریق BigQuery .
- آب و هوا، جرم و جنایت و سایر انواع دادهها در TIL با BigQuery .