مرحبا سكان! هل تخيفك الحاجة إلى معالجة مجموعات بيانات البيتابايت؟ تعرف على Google BigQuery ، وهو محرك تخزين يمكنه دمج البيانات عبر المؤسسة ، وتسهيل التحليل التفاعلي ، وتمكين التعلم الآلي. يمكنك الآن تخزين البيانات والاستعلام عنها واستردادها واستكشافها بكفاءة في بيئة مناسبة واحدة. سوف يعلمك Walyappa Lakshmanan و Jordan Taijani كيفية العمل في مستودع بيانات حديث باستخدام القوة الكاملة لسحابة عامة قابلة للتطوير وبدون خادم. باستخدام هذا الكتاب ، ستتمكن من: - التعمق في العناصر الداخلية لـ BigQuery - استكشاف أنواع البيانات والوظائف والمشغلين التي تدعمها Big Query - تحسين الاستعلامات وتنفيذ المخططات لتحسين الأداء أو تقليل التكاليف - التعرف على نظم المعلومات الجغرافية والسفر عبر الزمن و DDL / DML.وظائف مخصصة ونصوص SQL - حل العديد من مشكلات التعلم الآلي - تعرف على كيفية حماية البيانات وتتبع الأداء ومصادقة المستخدمين.
تقليل تكاليف الشبكة
تقليل تكاليف الشبكة إلى الحد الأدنى تُعد BigQuery خدمة إقليمية متوفرة في جميع أنحاء العالم. على سبيل المثال ، إذا كنت تطلب مجموعة بيانات مخزنة في منطقة الاتحاد الأوروبي ، فسيتم تشغيل الطلب على خوادم موجودة في مركز بيانات في الاتحاد الأوروبي. لكي تتمكن من تخزين نتائج الاستعلام في جدول ، يجب أن تكون في مجموعة بيانات موجودة أيضًا في منطقة الاتحاد الأوروبي. ومع ذلك ، يمكن استدعاء واجهة برمجة تطبيقات BigQuery REST (أي تشغيل استعلام) من أي مكان في العالم ، حتى من أجهزة كمبيوتر خارج GCP. عند العمل مع موارد GCP الأخرى ، مثل Google Cloud Storage أو Cloud Pub / Sub ، يتم الحصول على أفضل أداء إذا كانوا في نفس المنطقة مثل مجموعة البيانات. لذلك ، إذا تم تنفيذ الطلب من مثيل Compute Engine أو مجموعة Cloud Dataproc ، فسيكون الحمل على الشبكة في حده الأدنى ،إذا كان المثيل أو المجموعة أيضًا في نفس المنطقة مثل مجموعة البيانات المطلوبة. عند الوصول إلى BigQuery من خارج GCP ، ضع في اعتبارك مخطط الشبكة وحاول تقليل عدد القفزات بين جهاز الكمبيوتر العميل ومركز GCP حيث توجد مجموعة البيانات.
ردود موجزة وغير كاملة من
خلال الوصول المباشر إلى واجهة برمجة تطبيقات REST ، يمكن تقليل عبء الشبكة عن طريق قبول استجابات موجزة وغير كاملة. لقبول الردود المضغوطة ، يمكنك أن تحدد في رأس HTTP أنك مستعد لقبول أرشيف gzip والتأكد من ظهور سطر "gzip" في رأس وكيل المستخدم ، على سبيل المثال:
Accept-Encoding: gzip
User-Agent: programName (gzip)في هذه الحالة ، سيتم ضغط جميع الردود باستخدام gzip. بشكل افتراضي ، تحتوي ردود BigQuery على جميع الحقول المدرجة في الوثائق. ومع ذلك ، إذا عرفنا أي جزء من الإجابة يهمنا ، يمكننا أن نطلب من BigQuery إرسال هذا الجزء فقط ، وبالتالي تقليل عبء الشبكة. على سبيل المثال ، في هذا الفصل ، رأينا كيفية الحصول على معلومات كاملة حول وظيفة باستخدام واجهة برمجة تطبيقات الوظائف. إذا كنت مهتمًا فقط بمجموعة فرعية من الإجابة الكاملة (على سبيل المثال ، الخطوات فقط في خطة الاستعلام) ، يمكنك تحديد الحقول ذات الأهمية للحد من حجم الاستجابة:
JOBSURL="https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"
curl --silent \
-H "Authorization: Bearer $access_token" \
-H "Accept-Encoding: gzip" \
-H "User-Agent: get_job_details (gzip)" \
-X GET \
"${JOBSURL}/${JOBID}?fields=${FIELDS}" \
| zcatيرجى ملاحظة أنه ينص أيضًا على قبول بيانات gzip المضغوطة.
الجمع بين طلبات متعددة في حزم
عند استخدام واجهة برمجة تطبيقات REST ، يمكن دمج استدعاءات BigQuery API متعددة باستخدام نوع المحتوى متعدد الأجزاء / المختلط وطلبات HTTP المتداخلة في كل جزء. يحدد نص كل جزء عملية HTTP (GET و PUT وما إلى ذلك) والمسار إلى عنوان URL والعناوين والجسم. استجابةً لذلك ، سيرسل الخادم استجابة HTTP واحدة بنوع المحتوى متعدد الأجزاء / المختلط ، وسيحتوي كل جزء منها على الاستجابة (بالترتيب) للطلب المقابل في الطلب الدفعي. على الرغم من إرجاع الردود بترتيب معين ، يمكن للخادم معالجة المكالمات بأي ترتيب. لذلك ، يمكن اعتبار الطلب المجمّع بمثابة مجموعة من الطلبات المنفذة بالتوازي. فيما يلي مثال على إرسال طلب دفعة للحصول على بعض التفاصيل من خطط التنفيذ الخاصة بالطلبات الخمسة الأخيرة في مشروعنا. نستخدم أولاً أداة سطر أوامر BigQuery ،للحصول على آخر خمس مهام ناجحة:
# 5
JOBS=$(bq ls -j -n 50 | grep SUCCESS | head -5 | awk '{print $1}')يتم إرسال الطلب إلى نقطة نهاية BigQuery للمعالجة المجمعة:
BATCHURL="https://www.googleapis.com/batch/bigquery/v2"
JOBSPATH="/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"يمكنك تحديد الطلبات الفردية في مسار URL:
request=""
for JOBID in $JOBS; do
read -d '' part << EOF
--batch_part_starts_here
GET ${JOBSPATH}/${JOBID}?fields=${FIELDS}
EOF
request=$(echo "$request"; echo "$part")
doneثم يمكنك إرسال الطلب كطلب مركب:
curl --silent \
-H "Authorization: Bearer $access_token" \
-H "Content-Type: multipart/mixed; boundary=batch_part_starts_here" \
-X POST \
-d "$request" \
"${BATCHURL}"قراءة مجمعة باستخدام BigQuery Storage API
في الفصل الخامس ، ناقشنا استخدام BigQuery REST API ومكتبات العميل لتعداد الجداول واسترداد نتائج الاستعلام. تقوم واجهة برمجة تطبيقات REST بإرجاع البيانات كسجلات مرقمة تكون أكثر ملاءمة لمجموعات النتائج الصغيرة نسبيًا. ومع ذلك ، مع ظهور التعلم الآلي وأدوات الاستخراج والتحويل والتحميل الموزعة (ETL) ، تتطلب الأدوات الخارجية الآن وصولاً مجمّعًا سريعًا وفعالاً إلى مستودع BigQuery المُدار. يتم توفير وصول القراءة المجمعة هذا في BigQuery Storage API من خلال بروتوكول استدعاء الإجراء البعيد (RPC). باستخدام BigQuery Storage API ، يتم نقل البيانات المنظمة عبر الشبكة بتنسيق تسلسل ثنائي يتطابق بشكل وثيق مع تنسيق تخزين البيانات العمودي.يوفر هذا موازاة إضافية لمجموعة النتائج عبر العديد من المستهلكين.
لا يستخدم المستخدمون النهائيون واجهة برمجة تطبيقات BigQuery Storage مباشرةً. بدلاً من ذلك ، يستخدمون Cloud Dataflow و Cloud Dataproc و TensorFlow و AutoML والأدوات الأخرى التي تستخدم واجهة برمجة تطبيقات التخزين لقراءة البيانات مباشرةً من التخزين المُدار بدلاً من BigQuery API.
نظرًا لأن Storage API يصل إلى البيانات المخزنة مباشرةً ، يختلف إذن الوصول إلى BigQuery Storage API عن BigQuery API الحالية. يجب تهيئة أذونات BigQuery Storage API بشكل مستقل عن أذونات BigQuery.
توفر واجهة برمجة تطبيقات BigQuery Storage العديد من المزايا للأدوات التي تقرأ البيانات مباشرةً من وحدة التخزين المُدارة في BigQuery. على سبيل المثال ، يمكن للمستهلكين قراءة مجموعات السجلات المنفصلة من جدول باستخدام خيوط متعددة (على سبيل المثال ، من خلال السماح بقراءات موزعة للبيانات من خوادم إنتاج مختلفة في Cloud Dataproc) ، وتقسيم هذه الخيوط ديناميكيًا (وبالتالي تقليل زمن انتقال الذيل ، والذي يمكن أن يكون مشكلة خطيرة لوظائف MapReduce) ، حدد مجموعة فرعية من الأعمدة لقراءتها (لتمرير الميزات المستخدمة بواسطة النموذج فقط إلى هياكل التعلم الآلي) ، وقم بتصفية قيم العمود (تقليل كمية البيانات المنقولة عبر الشبكة) وفي نفس الوقت ضمان اتساق اللقطات (أي قراءة البيانات من نقطة زمنية معينة).
في الفصل الخامس ، غطينا استخدام امتداد ٪٪ bigquery في Jupyter Notebook لتحميل نتائج الاستعلام في DataFrames. ومع ذلك ، استخدمت الأمثلة مجموعات بيانات صغيرة نسبيًا - من عشرات إلى عدة مئات من السجلات. هل من الممكن تحميل مجموعة بيانات london_bicycles بأكملها (24 مليون سجل) في DataFrame؟ نعم ، يمكنك ، ولكن في هذه الحالة ، يجب عليك استخدام واجهة برمجة تطبيقات التخزين ، وليس BigQuery API ، لتحميل البيانات في DataFrame. أولاً ، تحتاج إلى تثبيت مكتبة عميل Python Storage API مع دعم Avro و pandas. يمكن القيام بذلك باستخدام الأمر
%pip install google-cloud-bigquery-storage[fastavro,pandas]بعد ذلك ، كل ما تبقى هو استخدام امتداد ٪٪ bigquery ، كما كان من قبل ، ولكن أضف معلمة تتطلب استخدام واجهة برمجة تطبيقات التخزين:
%%bigquery df --use_bqstorage_api --project $PROJECT
SELECT
start_station_name
, end_station_name
, start_date
, duration
FROM `bigquery-public-data`.london_bicycles.cycle_hireلاحظ أننا هنا نستخدم قدرة Storage API على توفير وصول مباشر إلى الأعمدة الفردية ؛ ليس من الضروري قراءة جدول BigQuery بأكمله في DataFrame. إذا أرجع الطلب كمية صغيرة من البيانات ، فستستخدم الإضافة BigQuery API تلقائيًا. لذلك ، ليس مخيفًا إذا كنت تشير دائمًا إلى هذا العلم في خلايا دفتر الملاحظات. لتمكين علامة --usebqstorageapi في جميع خلايا دفتر الملاحظات ، يمكنك تعيين علامة السياق:
import google.cloud.bigquery.magics
google.cloud.bigquery.magics.context.use_bqstorage_api = Trueاختيار تنسيق تخزين فعال
يعتمد أداء الاستعلام على مكان تخزين البيانات التي يتكون منها الجدول وبأي تنسيق. بشكل عام ، كلما قل طلب الاستعلام لإجراء عمليات بحث أو تحويلات ، كان الأداء أفضل.
مصادر البيانات الداخلية والخارجية
يدعم BigQuery الاستعلامات مقابل المصادر الخارجية مثل Google Cloud Storage و Cloud Bigtable و Google Sheets ، ولكن يمكنك فقط الحصول على أفضل أداء من جداولك الخاصة.
نوصي باستخدام BigQuery كمستودع للبيانات التحليلية لجميع بياناتك المنظمة وشبه المنظمة. من الأفضل استخدام مصادر البيانات الخارجية للتخزين المرحلي (Google Cloud Storage) ، أو التحميلات المباشرة (Cloud Pub / Sub ، أو Cloud Bigtable) ، أو التحديثات الدورية (Cloud SQL ، Cloud Spanner). بعد ذلك ، قم بإعداد مسار البيانات لتحميل البيانات وفقًا لجدول زمني من هذه المصادر الخارجية إلى BigQuery (انظر الفصل 4).
إذا كنت بحاجة إلى طلب بيانات من Google Cloud Storage ، فاحفظها بتنسيق عمودي مضغوط (مثل Parquet) إن أمكن. استخدم التنسيقات المستندة إلى السجلات مثل JSON أو CSV كحل أخير.
التدريج لإدارة دورة حياة الجرافة
إذا قمت بتحميل البيانات إلى BigQuery بعد وضعها في Google Cloud Storage ، فتأكد من حذفها من السحابة بعد التحميل. إذا كنت تستخدم خط أنابيب ETL لتحميل البيانات في BigQuery (لتحويلها بشكل كبير أو ترك جزء فقط من البيانات على طول الطريق) ، فقد ترغب في حفظ البيانات الأصلية في Google Cloud Storage. في مثل هذه الحالات ، يمكنك المساعدة في تقليل التكاليف من خلال تحديد قواعد إدارة دورة حياة الحاوية التي تعمل على تقليل مساحة التخزين في Google Cloud Storage.
فيما يلي كيفية تشغيل إدارة دورة حياة الحاوية وإعداد النقل التلقائي للبيانات من المناطق الموحدة أو الفئات القياسية التي يزيد عمرها عن 30 يومًا إلى التخزين على الإنترنت ، والبيانات المخزنة في التخزين القريب لأكثر من 90 يومًا إلى Coldline Storage:
gsutil lifecycle set lifecycle.yaml gs://some_bucket/في هذا المثال ، يحتوي ملف lifecycle.yaml على التعليمات البرمجية التالية:
{
"lifecycle": {
"rule": [
{
"action": {
"type": "SetStorageClass",
"storageClass": "NEARLINE"
},
"condition": {
"age": 30,
"matchesStorageClass": ["MULTI_REGIONAL", "STANDARD"]
}
},
{
"action": {
"type": "SetStorageClass",
"storageClass": "COLDLINE"
},
"condition": {
"age": 90,
"matchesStorageClass": ["NEARLINE"]
}
}
]}}يمكنك استخدام إدارة دورة الحياة ليس فقط لتغيير فئة الكائن ، ولكن أيضًا لإزالة الكائنات الأقدم من حد معين.
تخزين البيانات كمصفوفات وهياكل
بالإضافة إلى مجموعات البيانات الأخرى المتاحة للجمهور ، يحتوي BigQuery على مجموعة بيانات تحتوي على معلومات حول العواصف الإعصارية (الأعاصير ، والأعاصير الحلزونية ، وما إلى ذلك) التي حصلت عليها خدمات الأرصاد الجوية في جميع أنحاء العالم. يمكن أن تستمر العواصف الإعصارية عدة أسابيع ، ويتم قياس بارامتراتها الجوية كل ثلاث ساعات تقريبًا. لنفترض أنك قررت أن تجد في مجموعة البيانات هذه جميع العواصف التي حدثت في عام 2018 ، وأقصى سرعة للرياح وصلت إليها كل عاصفة ، ووقت ومكان العاصفة عندما تم الوصول إلى تلك السرعة القصوى. يسترد الاستعلام التالي كل هذه المعلومات من مجموعة البيانات العامة:
SELECT
sid, number, basin, name,
ARRAY_AGG(STRUCT(iso_time, usa_latitude, usa_longitude, usa_wind) ORDER BY
usa_wind DESC LIMIT 1)[OFFSET(0)].*
FROM
`bigquery-public-data`.noaa_hurricanes.hurricanes
WHERE
season = '2018'
GROUP BY
sid, number, basin, name
ORDER BY number ASCيسترجع الاستعلام معرّف العاصفة (SID) ، ومواسمها ، وبركة السباحة ، واسم العاصفة (إذا تم تخصيصها) ، ثم يجد مجموعة من الملاحظات التي تم إجراؤها لتلك العاصفة ، ويرتب الملاحظات بترتيب تنازلي لسرعة الرياح واختيار السرعة القصوى لكل عاصفة ... العواصف نفسها مرتبة برقم متسلسل. تتضمن النتيجة 88 سجلاً وتبدو كالتالي:
استغرق الطلب 1.4 ثانية ومعالجته 41.7 ميغابايت. يصف الإدخال الأول عاصفة Bolaven ، التي بلغت سرعتها القصوى 29 م / ث في 2 يناير 2018 الساعة 18:00 بالتوقيت العالمي المنسق.
نظرًا لأن العديد من خدمات الأرصاد الجوية تتم عمليات المراقبة ، فيمكن توحيد هذه البيانات باستخدام الحقول المتداخلة وتخزينها في BigQuery ، كما هو موضح أدناه:
CREATE OR REPLACE TABLE ch07.hurricanes_nested AS
SELECT sid, season, number, basin, name, iso_time, nature, usa_sshs,
STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
... AS cma,
... AS hko,
... AS newdelhi,
... AS reunion,
... bom,
... AS wellington,
... nadi
FROM `bigquery-public-data`.noaa_hurricanes.hurricanesتبدو الاستعلامات في هذا الجدول مماثلة لطلبات البحث الموجودة في الجدول الأصلي ، ولكن مع تغيير طفيف في أسماء الأعمدة (usa.latitude بدلاً من usa_latitude):
SELECT
sid, number, basin, name,
ARRAY_AGG(STRUCT(iso_time, usa.latitude, usa.longitude, usa.wind) ORDER BY
usa.wind DESC LIMIT 1)[OFFSET(0)].*
FROM
ch07.hurricanes_nested
WHERE
season = '2018'
GROUP BY
sid, number, basin, name
ORDER BY number ASCيعالج هذا الطلب المقدار نفسه من البيانات ويعمل في نفس مقدار الوقت مثل الأصل ، باستخدام مجموعة البيانات العامة. لا يؤدي استخدام الحقول (الهياكل) المتداخلة إلى تغيير سرعة الاستعلام أو تكلفته ، ولكن يمكن أن يجعل الاستعلام أكثر قابلية للقراءة. نظرًا لوجود العديد من الملاحظات لنفس العاصفة خلال مدتها ، يمكننا تغيير التخزين ليناسب في سجل واحد مجموعة الملاحظات الكاملة لكل عاصفة:
CREATE OR REPLACE TABLE ch07.hurricanes_nested_track AS
SELECT sid, season, number, basin, name,
ARRAY_AGG(
STRUCT(
iso_time,
nature,
usa_sshs,
STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
... AS cma,
... AS hko,
... AS newdelhi,
... AS reunion,
... bom,
... AS wellington,
... nadi
) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, nameلاحظ أننا نقوم الآن بتخزين الصفوف والموسم والخصائص الأخرى للعاصفة كأعمدة عددية ، لأنها لا تتغير اعتمادًا على مدتها.
يتم تخزين باقي البيانات ، المتغيرة مع كل ملاحظة ، كمصفوفة من الهياكل. هذه هي الطريقة التي يبدو بها استعلام الجدول الجديد:
SELECT
number, name, basin,
(SELECT AS STRUCT iso_time, usa.latitude, usa.longitude, usa.wind
FROM UNNEST(obs) ORDER BY usa.wind DESC LIMIT 1).*
FROM ch07.hurricanes_nested_track
WHERE season = '2018'
ORDER BY number ASCسيعيد هذا الطلب نفس النتيجة ، ولكن هذه المرة سيعالج 14.7 ميغابايت فقط (خفض التكلفة بمقدار ثلاثة أضعاف) ويكتمل في ثانية واحدة (زيادة السرعة بنسبة 30٪). ما سبب تحسن الأداء هذا؟ عندما يتم تخزين البيانات كمصفوفة ، ينخفض عدد السجلات في الجدول بشكل كبير (من 682،000 إلى 14،000) ، 2 لأنه يوجد الآن سجل واحد فقط لكل عاصفة ، وليس هناك العديد من السجلات - واحد لكل ملاحظة. بعد ذلك ، عندما نقوم بتصفية الصفوف حسب الموسم ، يمكن لـ BigQuery إسقاط العديد من الحالات ذات الصلة في نفس الوقت ، كما هو موضح في الشكل 1. 7.13.
ميزة أخرى هي أنه ليست هناك حاجة لتكرار سجلات البيانات عندما يتم تخزين الحالات ذات المستويات المختلفة من التفاصيل في نفس الجدول. يمكن لجدول واحد تخزين بيانات خطوط الطول والعرض لتغييرات العواصف والبيانات عالية المستوى مثل اسم العاصفة والموسم. ونظرًا لأن BigQuery يخزن البيانات المجدولة في أعمدة باستخدام الضغط ، يمكنك الاستعلام عن البيانات عالية المستوى ومعالجتها دون خوف من تكلفة العمل مع البيانات التفصيلية - الآن يتم تخزينها كمصفوفة منفصلة من القيم لكل عاصفة.
على سبيل المثال ، لمعرفة عدد العواصف حسب السنة ، يمكنك الاستعلام فقط عن الأعمدة المطلوبة:
WITH hurricane_detail AS (
SELECT sid, season, number, basin, name,
ARRAY_AGG(
STRUCT(
iso_time,
nature,
usa_sshs,
STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind
AS wind, tokyo_pressure AS pressure) AS tokyo
) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name
)
SELECT
COUNT(sid) AS count_of_storms,
season
FROM hurricane_detail
GROUP BY season
ORDER BY season DESCتمت معالجة الطلب السابق 27 ميغابايت ، وهو نصف 56 ميغابايت التي يجب معالجتها إذا لم يتم استخدام الحقول المتكررة المتداخلة.
لا تعمل الحقول المتداخلة بمفردها على تحسين الأداء ، على الرغم من أنها يمكن أن تحسن قابلية القراءة من خلال تنفيذ صلة بالفعل بجداول أخرى مرتبطة. بالإضافة إلى ذلك ، تعد الحقول المتكررة المتداخلة مفيدة للغاية من وجهة نظر الأداء. ضع في اعتبارك استخدام الحقول المكررة المتداخلة في مخططك لأنها يمكن أن تزيد السرعة بشكل كبير وتقليل تكلفة تصفية الاستعلامات في عمود غير متداخل أو متكرر (في حالتنا ، الموسم).
يتمثل العيب الرئيسي للحقول المتكررة المتداخلة في صعوبة تنفيذ الدفق في مثل هذا الجدول إذا كانت تحديثات التدفق تتضمن إضافة عناصر إلى المصفوفات الموجودة. هذا أكثر صعوبة في التنفيذ من إضافة سجلات جديدة: ستحتاج إلى تعديل سجل موجود - بالنسبة لجدول معلومات العاصفة ، يعد هذا عيبًا كبيرًا ، حيث تتم إضافة ملاحظات جديدة إليه باستمرار ، وهذا يفسر سبب عدم استخدام مجموعة البيانات العامة هذه التكرارات المتداخلة مجالات.
ممارسة استخدام المصفوفات
أظهرت التجربة أن الأمر يتطلب بعض الممارسة لاستخدام الحقول المتكررة المتداخلة بنجاح. تعد عينة مجموعة بيانات Google Analytics في BigQuery مثالية لهذا الغرض. أسهل طريقة لتحديد البيانات المتداخلة في المخطط هي العثور على الكلمة RECORD في عمود النوع ، والتي تتوافق مع نوع البيانات STRUCT ، والكلمة REPEATED في عمود Mode ، كما هو موضح أدناه:
في هذا المثال ، يكون الحقل TOTALS هو STRUCT (ولكن غير مكرر) ، وحقل HITS هو STRUCT ويتكرر. هذا منطقي إلى حد ما ، لأن Google Analytics يتتبع بيانات جلسة الزائر على مستوى التجميع (قيمة جلسة واحدة لـ totals.hits) وعلى مستوى الدقة (قيم منفصلة لوقت الوصول لكل صفحة والصور المستردة من موقعك) ... لا يمكن تخزين البيانات على هذه المستويات المختلفة من التفاصيل دون تكرار معرف الزائر في السجلات إلا باستخدام المصفوفات. بعد حفظ البيانات بتنسيق متكرر باستخدام المصفوفات ، عليك التفكير في نشر تلك البيانات في طلباتك باستخدام UNNEST ، على سبيل المثال:
SELECT DISTINCT
visitId
, totals.pageviews
, totals.timeOnsite
, trafficSource.source
, device.browser
, device.isMobile
, h.page.pageTitle
FROM
`bigquery-public-data`.google_analytics_sample.ga_sessions_20170801,
UNNEST(hits) AS h
WHERE
totals.timeOnSite IS NOT NULL AND h.page.pageTitle =
'Shopping Cart'
ORDER BY pageviews DESC
LIMIT 10
, [1,2,3,4,5] :
[1,
2
3
4
5]يمكنك بعد ذلك إجراء عمليات SQL عادية مثل WHERE لتصفية النتائج على الصفحات ذات العناوين مثل عربة التسوق جربها!
من ناحية أخرى ، تستخدم مجموعة بيانات معلومات الالتزام العامة على GitHub (bigquery-publicdata.githubrepos.commits) حقلًا متكررًا متداخلًا (reponame) لتخزين قائمة بالمستودعات المتأثرة بالالتزام. لا يتغير بمرور الوقت ويوفر استعلامات أسرع تعمل على التصفية في أي مجال آخر.
تخزين البيانات كأنواع جغرافية
تحتوي مجموعة بيانات BigQuery العامة على جدول لحدود منطقة الرمز البريدي للولايات المتحدة (bigquery-public-data.utilityus.zipcodearea) وجدول آخر به مضلعات تصف حدود المدن الأمريكية (bigquery-publicdata.utilityus.uscitiesarea). عمود zipcodegeom عبارة عن سلسلة ، بينما عمود city_geom هو نوع جغرافي.
من هذين الجدولين ، يمكنك الحصول على قائمة بجميع الرموز البريدية الخاصة بسانتا في في نيو مكسيكو ، كما هو موضح أدناه:
SELECT name, zipcode
FROM `bigquery-public-data`.utility_us.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(ST_GeogFromText(zipcode_geom), city_geom)
WHERE name LIKE '%Santa Fe%'يستغرق هذا الاستعلام 51.9 ثانية ، ويعالج 305.5 ميغا بايت من البيانات ، ويعيد النتائج التالية:
لماذا هذا الطلب يستغرق وقتا طويلا؟ هذا ليس بسبب عملية STINTERSECTS ، ولكن بشكل أساسي لأن وظيفة STGeogFromText يجب أن تقيم خلايا S2 وإنشاء نوع GEOGRAPHY المقابل لكل رمز بريدي.
يمكننا محاولة تعديل جدول الرمز البريدي عن طريق القيام بذلك مسبقًا وتخزين الهندسة كقيمة جغرافية:
CREATE OR REPLACE TABLE ch07.zipcode_area AS
SELECT
* REPLACE(ST_GeogFromText(zipcode_geom) AS zipcode_geom)
FROM
`bigquery-public-data`.utility_us.zipcode_areaيعد REPLACE (راجع الاستعلام السابق) طريقة مناسبة لاستبدال عمود من تعبير SELECT *.يبلغ حجم مجموعة البيانات الجديدة 131.8 ميجابايت ، وهو أكبر بكثير من 116.5 ميجابايت في الجدول الأصلي. ومع ذلك ، يمكن أن تستخدم الاستعلامات الواردة في هذا الجدول تغطية S2 وتكون أسرع بكثير. على سبيل المثال ، يستغرق الاستعلام التالي 5.3 ثانية (زيادة في السرعة بمعدل 10x) ويعالج 320.8 ميجابايت (زيادة طفيفة في التكلفة عند استخدام خطة تعريفة "عند الطلب"):
SELECT name, zipcode
FROM ch07.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(zipcode_geom, city_geom)
WHERE name LIKE '%Santa Fe%'فوائد الأداء لتخزين البيانات الجغرافية في عمود جغرافي أكثر من مقنعة. هذا هو سبب إهمال مجموعة بيانات الأداة المساعدة (ما زالت متاحة للحفاظ على الاستعلامات المكتوبة بالفعل) حية. نوصي باستخدام جدول bigquery-public-data.geousboundaries.uszip_codes ، الذي يخزن المعلومات الجغرافية في عمود GEOGRAPHY ويتم تحديثه باستمرار.
»يمكن العثور على مزيد من التفاصيل حول الكتاب على الموقع الإلكتروني لدار النشر
» جدول المحتويات
» مقتطفات
لـ Habitants خصم 25٪ على القسيمة - Google
عند الدفع مقابل النسخة الورقية من الكتاب ، يتم إرسال كتاب إلكتروني عبر البريد الإلكتروني.