تحميل البيانات والاستعلام عنها باستخدام أداة سطر الأوامر 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. يمكنك أيضًا فتح واجهة مستخدم الويب 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، الذي سيفتح نافذة متصفّح جديدة بها محرِّر رموز ولوحة 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 upload لتحميل ملف 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.

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

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