返回提示词目录第 1159 页

分析并降低BigQuery查询与存储成本

基于用量定位查询和存储开支来源,按节省潜力排序优化方案,并提供可执行SQL与效果核验查询。

完整提示词

<context>
BigQuery has two compute models: on-demand, billed by bytes processed, and capacity (editions) billed by slot-hours with reservations and autoscaling. The right fixes differ. Under on-demand, cost follows bytes scanned: `SELECT *` reads every column, `LIMIT` does not reduce bytes, filters on non-partition columns do not prune, a filter wrapped in a function or compared to a subquery may disable partition pruning, and dashboards that re-run the same heavy query every few minutes multiply cost. Partitioning by date with `require_partition_filter`, clustering on common filter columns, materialised views or pre-aggregated tables for dashboards, and incremental models instead of full rebuilds are the usual fixes. Under editions, cost follows slot usage, so baseline and max reservation sizes, idle autoscaling and job concurrency matter more than bytes. Storage can be billed on logical or physical (compressed) bytes per dataset, and long-term storage is cheaper automatically for tables untouched for 90 days. Streaming inserts and some APIs have their own charges. Each estimate must be checked against `INFORMATION_SCHEMA.JOBS` before and after.
</context>

<task>
Usage data: {{usage}}
{{#context}}Context: {{context}}{{/context}}

1. Break down spend by compute (by project, user or service account, and query pattern), storage (active and long-term, logical versus physical), streaming and other services. Note the pricing model in use.
2. Find the top queries by bytes billed (on-demand) or slot-hours (editions), grouped by normalised query text, and for each give the cause: full scans, no partition filter, unpruned filters, select-star, repeated identical dashboard queries, full rebuilds.
3. Find tables that should be partitioned or clustered based on how they are filtered, and whether `require_partition_filter` is safe to turn on.
4. Rank savings by estimated monthly amount, effort and risk: query rewrites, partitioning and clustering, materialised views or summary tables for dashboards, BI caching or extract schedules, incremental models, storage billing model per dataset, table expiration for scratch data, and a pricing model comparison if usage justifies it.
5. Write the DDL and SQL for each change for the user to run, and the INFORMATION_SCHEMA query to measure the effect.
6. Add guardrails: per-user or per-project custom quotas, maximum bytes billed on scheduled and BI queries, and budget alerts.
</task>

<constraints>
- Read-only analysis. Do not execute changes; produce statements for the user.
- Do not quote current list prices from memory as fact; use the user's billing numbers or mark estimates as relative.
- Repartitioning a large table means rewriting it; give the cost and the approach (create new table, swap) rather than presenting it as free.
- Flag changes that can break queries, such as `require_partition_filter`.
</constraints>

<output_format>
## Where the money goes
Table: area | monthly cost or bytes | share.
## Savings ranked
Table: change | est. saving | effort | risk.
## Changes
SQL or DDL blocks per change, with notes.
## Guardrails
Quotas, maximum bytes billed and alerts.
## Queries to collect more evidence
INFORMATION_SCHEMA queries for anything missing and for measuring results.
## Open questions
Numbered, or "None".
</output_format>

可替换内容

  • {{usage}}
  • {{#context}}
  • {{context}}
  • {{/context}}

分类与标签

编程开发、BigQuery优化、云数据成本、SQL调优、数据仓库运维

前往 AI 图片创作