تحميل البيانات والاستعلام عنها باستخدام أداة سطر الأوامر bq في BigQuery

1. مقدمة

‫BigQuery هي قاعدة بيانات إحصاءات مُدارة بالكامل ومنخفضة التكلفة من Google ولا تتطلّب أي عمليات. باستخدام BigQuery، يمكنك طلب البحث عن تيرابايت من البيانات بدون الحاجة إلى مسؤول قاعدة بيانات أو بنية أساسية. تستخدم BigQuery لغة SQL المألوفة ونموذج محاسبة يعتمد على الدفع مقابل الاستخدام فقط. يتيح لك BigQuery التركيز على تحليل البيانات للعثور على إحصاءات مفيدة. في هذا الدرس التطبيقي حول الترميز، ستستخدم أداة سطر الأوامر bq لتحميل ملف CSV محلي إلى جدول BigQuery جديد.

ما ستتعلمه

  • كيفية استخدام أداة سطر الأوامر bq في BigQuery
  • كيفية تحميل ملفات البيانات المحلية إلى جدول BigQuery

المتطلبات

  • مشروع على السحابة الإلكترونية من Google
  • متصفّح، مثل Google Chrome

2. طريقة الإعداد

تفعيل BigQuery

إذا لم يكن لديك حساب على Google، عليك إنشاء حساب.

  1. سجِّل الدخول إلى وحدة تحكّم Google Cloud وانتقِل إلى BigQuery. يمكنك أيضًا فتح واجهة مستخدم الويب في BigQuery مباشرةً من خلال إدخال عنوان URL التالي في المتصفّح.
https://console.cloud.google.com/bigquery
  1. اقبَل بنود الخدمة.
  2. قبل أن تتمكّن من استخدام BigQuery، عليك إنشاء مشروع. اتّبِع التعليمات لإنشاء مشروعك الجديد.

اختَر اسم مشروع ودوِّن رقم تعريف المشروع. 1884405a64ce5765.png

رقم تعريف المشروع هو اسم فريد في جميع مشاريع Google Cloud. سيتم الإشارة إليه لاحقًا في هذا الدرس العملي باسم PROJECT_ID.

يستخدم هذا الدرس التطبيقي العملي موارد BigQuery ضمن حدود وضع الحماية في BigQuery. ليس من الضروري توفُّر حساب فوترة. إذا أردت إزالة حدود وضع الحماية لاحقًا، يمكنك إضافة حساب فوترة من خلال الاشتراك في الفترة التجريبية المجانية من Google Cloud.

Cloud Shell

ستستخدم Cloud Shell، وهي بيئة سطر أوامر تعمل في Google Cloud.

تفعيل Cloud Shell

  1. من Cloud Console، انقر على تفعيل Cloud Shell 4292cbf4971c9786.png.

bce75f34b2c53987.png

إذا لم يسبق لك بدء 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. نفِّذ الأمر التالي في 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,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. إنشاء ملف البيانات

يمكن لخدمة BigQuery تحميل البيانات من عدة تنسيقات، بما في ذلك JSON المحدّد بفواصل أسطر جديدة وAvro وCSV. لتسهيل الأمر، ستستخدم ملف CSV.

إنشاء ملف CSV

في Cloud Shell، أنشئ ملف CSV فارغًا.

touch customer_transactions.csv

افتح ملف CSV في أداة تعديل الرموز في Cloud Shell من خلال تنفيذ الأمر cloudshell edit، ما سيؤدي إلى فتح نافذة متصفِّح جديدة تحتوي على أداة تعديل الرموز ولوحة 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 بالنقر على ملف > تعديل.

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 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

6. الاستعلام عن البيانات

بعد تحميل بياناتك، يمكنك طلبها باستخدام واجهة مستخدم الويب في BigQuery أو الأمر bq أو واجهة برمجة التطبيقات. يمكن أن تنضم طلبات البحث إلى بياناتك مقابل أي مجموعة بيانات (أو مجموعات بيانات، طالما أنّها في الموقع الجغرافي نفسه) لديك إذن بقراءتها.

نفِّذ طلب بحث 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

الخطوات التالية

يمكنك الاطّلاع على المقالات التالية: