Index of every page on this site: llms.txt
All skills
google avatar

/bigquery-ai-ml

@37de74d
by googlegoogle/adk-python22k stars
4,392
Run it yourself

Leverages BigQuery's built-in machine learning and GenAI capabilities for advanced data analytics. Use when you need to write SQL queries that perform time-series forecasting, predict values, detect outliers or anomalies, find key drivers, perform semantic search or vector search, classify text, calculate similarity, summarize content, translate language, evaluate models, filter by semantic conditions, measure the causal effect of an intervention, compute correlations between columns, detect change points or structural breaks, extract trend or seasonality components, or leverage generative AI capabilities in BigQuery. Do not use for general BigQuery dataset, table, or job management requests.

Use this Skill: https://skilld.dev/gh/google/adk-python/bigquery-ai-ml

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

referencesai_key_drivers.md

≈1.8k tokens on demand. Your agent reads this file only when SKILL.md points to it.

BigQuery AI.Key_Drivers

AI.KEY_DRIVERS automatically identifies the key dimensional segments most responsible for driving changes in a specified metric between a defined interest group and a reference group.

Syntax Reference

SELECT
  *
FROM
  AI.KEY_DRIVERS(
    { TABLE TABLE | (QUERY_STATEMENT) },
    metric_col => 'METRIC_COL',
    dimension_cols => DIMENSION_COLS,
    interest_label_col => 'INTEREST_LABEL_COL'
    [, min_apriori_support => MIN_APRIORI_SUPPORT]
    [, top_k => TOP_K]
    [, enable_pruning => ENABLE_PRUNING]
  )

Input Arguments

Argument Requirement Type Description
input_data Required The source table or
: : : : subquery containing the :
: : : : data to analyze. :
metric_col Required STRING Metric column name. Must
: : : : be of type: INT64, :
: : : : NUMERIC, BIGNUMERIC, or :
: : : : FLOAT64. :
interest_label_col Required STRING Boolean column name:
: : : : TRUE for interest group, :
: : : : FALSE for reference :
: : : : group. :
dimension_cols Required ARRAY<STRING> 1-12 dimension columns
: : : : (INT64, BOOL, STRING); :
: : : : cannot be metric_col or :
: : : : interest_label_col. :
min_apriori_support Optional FLOAT64 Minimum apriori support
: : : : threshold [0, 1] for :
: : : : output segments. Default: :
: : : : 0.1. Cannot be used with :
: : : : top_k. :
top_k Optional INT64 Return top k insights [1,
: : : : 1M] by apriori support. If :
: : : : unset, uses :
: : : : min_apriori_support=0.1. :
: : : : Cannot be used with :
: : : : min_apriori_support. :
enable_pruning Optional BOOL If TRUE (default),
: : : : redundant insights are :
: : : : pruned. If FALSE, all :
: : : : insights meeting :
: : : : thresholds are returned. :
: : : : Two segments are redundant :
: : : : if two conditions are :
: : : : met: 1) their metric :
: : : : values are equal 2) The :
: : : : dimensions and :
: : : : corresponding values of :
: : : : one row are a subset of :
: : : : the dimensions and :
: : : : corresponding values of :
: : : : the other. In this case, :
: : : : the row with more :
: : : : dimensions (the more :
: : : : descriptive row) is kept. :

Output Schema

Returns a STRUCT with the following fields:

Column Name Type Description
drivers ARRAY<STRING> Provides a list of drivers, or dimension values of interest, which describes each of the segments.
metric_interest NUMERIC The sum of the metric_column for the data in the interest segment.
metric_reference NUMERIC The sum of the metric_column for data in the reference segment.
difference NUMERIC The difference between the interest and reference metric values for a segment.
relative_difference NUMERIC The relative change of a segment, calculated as the difference divided by the reference metric value.
unexpected_difference NUMERIC Measures deviation of segment from the rest of the population's growth. Calculated as: (segment relative_difference - complement relative_difference) * segment reference metric.
relative_unexpected_difference NUMERIC The unexpected_difference divided by the expected interest metric value for a segment.
contribution NUMERIC Contains the absolute value of the difference value: ABS(difference).
apriori_support NUMERIC Segment size relative to the total population (filters small segments).

Examples

Identifying Key Drivers in 2024 H2 Liquor Sales

WITH InputData AS (
  SELECT
    sale_dollars,
    city,
    category_name,
    vendor_name,
    (date > '2024-07-01') AS IS_H2
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  WHERE EXTRACT(YEAR FROM DATE) = 2024
)
SELECT *
FROM AI.KEY_DRIVERS(
  TABLE InputData,
  metric_col => 'sale_dollars',
  dimension_cols => ['city', 'vendor_name', 'category_name'],
  interest_label_col => 'IS_H2',
  min_apriori_support => 0
);

Finding Top 5 Key Drivers in Bottle Costs

SELECT *
FROM AI.KEY_DRIVERS(
  (
    SELECT
      state_bottle_cost,
      city,
      category_name,
      (EXTRACT(MONTH FROM date) >= 7) AS is_h2
    FROM `bigquery-public-data.iowa_liquor_sales.sales`
    WHERE EXTRACT(YEAR FROM date) = 2024
  ),
  metric_col => 'state_bottle_cost',
  dimension_cols => ['city', 'category_name'],
  interest_label_col => 'is_h2',
  top_k => 5
);

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No alerts5mo3 checks · Risk SAFE
  • Gen Agent Trust Hub5mo

    This skill provides a comprehensive interface for BigQuery AI and Machine Learning functions using standard SQL. It includes security considerations regarding the handling of untrusted data within AI-driven queries, which is standard for this type of functionality. Users should apply appropriate data validation when using these features.

  • Socket5mo

    No alerts

  • Snyk5mo

    Risk: LOW · No issues

Signed by skilld at 37de74d. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub yesterday.

Activeupdated last month
metadata
{
  "author": "google-adk",
  "version": "1.0"
}

README badge

README badge for google/adk-python/bigquery-ai-ml