داده ها را با ابزار خط فرمان bq برای BigQuery بارگیری و پرس و جو کنید

1. مقدمه

BigQuery پایگاه داده تحلیلی کاملاً مدیریت شده، NoOps و کم هزینه Google است. با BigQuery، می‌توانید ترابایت داده را بدون مدیر پایگاه داده یا زیرساخت جستجو کنید. BigQuery از SQL آشنا و یک مدل شارژ فقط پرداخت برای آنچه شما استفاده می کنید استفاده می کند. BigQuery به شما اجازه می دهد تا بر تجزیه و تحلیل داده ها تمرکز کنید تا بینش های معنی دار پیدا کنید. در این کد لبه، از ابزار خط فرمان bq برای بارگذاری یک فایل CSV محلی در یک جدول BigQuery جدید استفاده خواهید کرد.

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

• نحوه استفاده از ابزار خط فرمان bq برای BigQuery
• نحوه بارگذاری فایل های داده محلی در جدول BigQuery

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

• یک پروژه Google Cloud
• یک مرورگر، مانند Google Chrome

2. راه اندازی شوید

BigQuery را فعال کنید

اگر قبلاً یک حساب Google ندارید، باید یک حساب ایجاد کنید .

1. به Google Cloud Console وارد شوید و به BigQuery بروید. همچنین می توانید با وارد کردن URL زیر در مرورگر خود، رابط وب BigQuery را مستقیماً باز کنید.

https://console.cloud.google.com/bigquery

2. شرایط خدمات را بپذیرید.
3. قبل از اینکه بتوانید از BigQuery استفاده کنید، باید یک پروژه ایجاد کنید. برای ایجاد پروژه جدید خود، دستورات را دنبال کنید.

نام پروژه را انتخاب کنید و شناسه پروژه را یادداشت کنید.

شناسه پروژه یک نام منحصر به فرد در تمام پروژه های Google Cloud است. بعداً در این آزمایشگاه کد به عنوان PROJECT_ID نامیده خواهد شد.

این آزمایشگاه کد از منابع BigQuery در محدوده جعبه ایمنی BigQuery استفاده می کند. حساب صورتحساب مورد نیاز نیست. اگر بعداً می‌خواهید محدودیت‌های جعبه ایمنی را حذف کنید، می‌توانید با ثبت‌نام در آزمایشی رایگان Google Cloud یک حساب صورت‌حساب اضافه کنید.

پوسته ابری

شما از Cloud Shell ، یک محیط خط فرمان در حال اجرا در Google Cloud استفاده خواهید کرد.

Cloud Shell را فعال کنید

1. از Cloud Console، روی Activate Cloud Shell کلیک کنید .

اگر قبلاً Cloud Shell را راه‌اندازی نکرده‌اید، یک صفحه میانی (در زیر تاشو) برای شما نمایش داده می‌شود که آن را توصیف می‌کند. اگر اینطور است، روی Continue کلیک کنید (و دیگر آن را نخواهید دید). در اینجا به نظر می رسد که آن صفحه یک بار مصرف:

تهیه و اتصال به Cloud Shell فقط باید چند لحظه طول بکشد.

این ماشین مجازی با تمام ابزارهای توسعه مورد نیاز شما بارگذاری شده است. این دایرکتوری اصلی 5 گیگابایتی دائمی را ارائه می دهد و در Google Cloud اجرا می شود و عملکرد شبکه و احراز هویت را بسیار افزایش می دهد. بیشتر، اگر نه همه، کار شما در این کد لبه را می توان به سادگی با یک مرورگر یا Chromebook انجام داد.

پس از اتصال به Cloud Shell، باید ببینید که قبلاً احراز هویت شده اید و پروژه قبلاً روی ID پروژه شما تنظیم شده است.

2. برای تایید احراز هویت، دستور زیر را در 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`

توجه: ابزار خط فرمان gcloud ابزار قدرتمند و یکپارچه خط فرمان در Google Cloud است. در Cloud Shell از پیش نصب شده است. متوجه پشتیبانی آن برای تکمیل تب خواهید شد. برای اطلاعات بیشتر، نمای کلی ابزار خط فرمان gcloud را ببینید.

3. دستور زیر را در Cloud Shell اجرا کنید تا تأیید کنید که دستور gcloud از پروژه شما اطلاع دارد:

gcloud config list project

خروجی فرمان

[core]
project = <PROJECT_ID>

اگر اینطور نیست، می توانید آن را با این دستور تنظیم کنید:

gcloud config set project <PROJECT_ID>

خروجی فرمان

Updated property [core/project].

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

مجموعه داده ای ایجاد کنید که شامل جداول شما باشد.

مجموعه داده چیست؟

مجموعه داده 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,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. فایل دیتا را ایجاد کنید

BigQuery می‌تواند داده‌ها را از چندین فرمت داده، از جمله JSON، Avro و CSV با خط جدید بارگیری کند. برای سادگی، از CSV استفاده خواهید کرد.

یک فایل CSV ایجاد کنید

در Cloud Shell، یک فایل CSV خالی ایجاد کنید.

touch customer_transactions.csv

فایل CSV را در ویرایشگر کد در Cloud Shell با اجرای دستور ویرایش cloudshell باز کنید، که یک پنجره مرورگر جدید با یک ویرایشگر کد و پانل Cloud Shell باز می شود.

cloudshell edit customer_transactions.csv

توجه: ویرایشگر کد برای عملکرد به کوکی‌های شخص ثالث نیاز دارد زیرا از دامنه متفاوتی نسبت به Google Cloud Console بارگیری می‌شود.

در ویرایشگر کد، مقادیری را وارد کنید که با کاما از هم جدا شده اند تا در 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 ذخیره کنید.

5. داده ها را بارگذاری کنید

از دستور 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 می‌تواند فایل‌ها را از فضای ذخیره‌سازی ابری با 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

6. داده ها را پرس و جو کنید

اکنون که داده‌های شما بارگیری شده است، می‌توانید با استفاده از BigQuery web UI ، دستور 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

7. پاکسازی کنید

مجموعه داده ای را که با دستور bq rm ایجاد کرده اید حذف کنید. از پرچم -r برای حذف هر جدولی که در آن وجود دارد استفاده کنید.

bq rm -r bq_load_codelab

8. تبریک می گویم!

شما یک جدول را در BigQuery آپلود کردید و آن را پرس و جو کردید!

آنچه شما پوشش داده اید

• استفاده از ابزار خط فرمان bq برای تعامل با BigQuery.
• پیوستن به داده های خود و مجموعه داده های عمومی با پرس و جو BigQuery.

بعدش چی

بیشتر بدانید در مورد:

• ابزار خط فرمان bq
• راه های دیگر برای بارگیری داده ها در BigQuery .
• سایر مجموعه‌های داده عمومی در دسترس از طریق BigQuery .
• آب و هوا، جرم و جنایت و انواع دیگر داده ها در TIL با BigQuery .