تخزين جداول BigQuery حسب تاريخ آخر استعلام

لماذا يهم هذا

يمكن للمؤسسات الكبيرة أن تراكم آلاف الجداول التي لم تعد مستخدمة — بقايا خطوط أنابيب قديمة أو تحليلات لمرة واحدة أو ميزات مُهملة. هذه الجداول تراكم رسوم التخزين بصمت كل شهر. بتحديد الجداول التي لم يُستعلم عنها منذ أسابيع أو أشهر، يمكنك أرشفتها أو حذفها أو تحويلها إلى مستويات تخزين أرخص.

كيف يعمل

يربط الاستعلام INFORMATION_SCHEMA.JOBS_BY_PROJECT (للعثور على آخر وقت استعلام لكل جدول) مع TABLE_STORAGE (لبيانات الحجم) وTABLE_OPTIONS (لتحديد نموذج الفوترة المنطقي مقابل المادي). يحسب تكلفة التخزين بناءً على البايتات النشطة مقابل البايتات طويلة الأمد بأسعار Google المنشورة لكل GiB.

استعلام SQL

-- Find stale tables sorted by last query date with storage cost

DECLARE lookback_days INT64 DEFAULT 30;

-- US multi-region pricing ($/GiB/month)
DECLARE logical_active_price NUMERIC DEFAULT 0.02;
DECLARE logical_longterm_price NUMERIC DEFAULT 0.01;
DECLARE physical_active_price NUMERIC DEFAULT 0.04;
DECLARE physical_longterm_price NUMERIC DEFAULT 0.02;

WITH last_access AS (
  SELECT
    ref.project_id, ref.dataset_id, ref.table_id,
    MAX(j.creation_time) AS last_query_at
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
    UNNEST(j.referenced_tables) ref
  WHERE j.state = 'DONE' AND j.job_type = 'QUERY'
    AND j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
  GROUP BY 1, 2, 3
),
storage_info AS (
  SELECT
    s.project_id,
    s.table_schema AS dataset_id,
    s.table_name AS table_id,
    IFNULL(opt.option_value, 'LOGICAL') AS model,
    IF(opt.option_value = 'PHYSICAL', s.total_physical_bytes, s.total_logical_bytes) AS size_bytes,
    IF(opt.option_value = 'PHYSICAL', s.active_physical_bytes, s.active_logical_bytes) AS active_bytes,
    IF(opt.option_value = 'PHYSICAL', s.long_term_physical_bytes, s.long_term_logical_bytes) AS lt_bytes
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE s
...

شرح الاستعلام

يرجع الاستعلام إلى مصدرَي بيانات: بيانات وصفية للمهام (لآخر وقت استعلام لكل جدول) وبيانات وصفية لتخزين الجدول (للحجم ونموذج الفوترة). تظهر الجداول التي لم يُستعلم عنها في نافذة الاسترجاع أولاً (NULLS FIRST). تُحسب تكلفة التخزين باستخدام البايتات النشطة مقابل البايتات طويلة الأمد مع التسعير المناسب لنموذج الفوترة المنطقي أو المادي.

رؤى أساسية

• lightbulb

  الجداول في أعلى القائمة (لم يُستعلم عنها أبداً أو استُعلم عنها منذ زمن طويل) هي مرشحة مثالية للحذف أو الأرشفة.

• lightbulb

  قد تظل الجداول الكبيرة التي لم يُستعلم عنها مشار إليها بواسطة أنظمة أخرى — تحقق قبل الحذف.

• lightbulb

  الجداول الأقدم من 90 يوماً تحصل تلقائياً على تسعير طويل الأمد (أرخص بنسبة 50%)، لكن حذف الجداول غير المستخدمة يُوفّر 100%.

• lightbulb

  جداول نموذج الفوترة المادية تكلّف ضعف السعر لكل GiB لكنها تستفيد من الضغط — تحقق مما إذا كانت جداولك قابلة للضغط بشكل كبير.

أفضل الممارسات

1. 1

   اضبط مراجعة شهرية للجداول التي لم يُستعلم عنها في آخر 30 يوماً.

2. 2

   نفّذ سياسات انتهاء صلاحية الجداول (default_table_expiration_ms على مجموعات البيانات) للبيانات المؤقتة.

3. 3

   أرشف الجداول المهمة لكن غير المستخدمة إلى Google Cloud Storage (أرخص من تخزين BigQuery).

4. 4

   حوّل مجموعات البيانات ذات نسب الضغط العالية إلى نموذج الفوترة المادية لتوفير ما يصل إلى 70% من تكاليف التخزين.

أدلة ذات صلة