Stay organized with collectionsSave and categorize content based on your preferences.
For privacy reasons, we restrict which SQL functions you can use in ADH.
Scalar functions
All scalar functions except the following are allowed:
ERROR
ST_AREA
ST_ASBINARY
ST_ASGEOJSON
ST_ASTEXT
ST_BOUNDARY
ST_CENTROID
ST_CENTROID_AGG
ST_CLOSESTPOINT
ST_CLUSTERDBSCAN
ST_CONTAINS
ST_CONVEXHULL
ST_COVEREDBY
ST_COVERS
ST_DIFFERENCE
ST_DIMENSION
ST_DISJOINT
ST_DISTANCE
ST_DUMP
ST_DWITHIN
ST_EQUALS
ST_GEOGFROMGEOJSON
ST_GEOGFROMTEXT
ST_GEOGFROMWKB
ST_GEOGPOINT
ST_GEOGPOINTFROMGEOHASH
ST_GEOHASH
ST_INTERSECTION
ST_INTERSECTS
ST_INTERSECTSBOX
ST_ISCOLLECTION
ST_ISEMPTY
ST_LENGTH
ST_MAKELINE
ST_MAKEPOLYGON
ST_MAKEPOLYGONORIENTED
ST_MAXDISTANCE
ST_NPOINTS
ST_NUMPOINTS
ST_PERIMETER
ST_SIMPLIFY
ST_SNAPTOGRID
ST_TOUCHES
ST_UNION
ST_UNION_AGG
ST_WITHIN
ST_X
ST_Y
Analysis queries
For aggregated analysis queries, we restrict functions that combine data from
multiple users. All aggregate and analytic functions are allowed when processing
a single user's data. SeeUser-level aggregationsbelow for
details.
A sketch aggregated from values of typeADH.USER_ID
ADH.BYTE_HLL
A sketch aggregated from values of any non-ID type
These types support merging and extracting to cardinality estimates, and don't
support direct conversion to any other type. They cannot be directly exported
from Ads Data Hub, so they must useHLL_COUNT.EXTRACTorHLL_COUNT.MERGEbefore reaching the query output.
HLL sketches enable cardinality estimation across multiple layers of
aggregation, but note that in Ads Data Hub they don't give a substantial
performance improvement overCOUNT(DISTINCT)in a single layer.
Noise injection:HLL++ functions are not supported when running a query
usingnoise injection.
User-level aggregations
We allow all aggregate and analytic functions inSELECTstatements that group
or partition byuser_id. For this to work, any preceding joins must make it
clear that the data is kept at the user level by joining onuser_id, like in
this example:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2024-09-18 UTC."],[[["\u003cp\u003eFor privacy reasons, Ads Data Hub (ADH) restricts the use of certain SQL functions, including some scalar, aggregate, and analytic functions, and HyperLogLog++ functions have limitations on data types and supported functions.\u003c/p\u003e\n"],["\u003cp\u003eScalar functions are generally allowed except for a specific list provided, while aggregate functions are allowed for single-user data processing but restricted for combining data from multiple users.\u003c/p\u003e\n"],["\u003cp\u003eAll analytic functions are restricted except when used in \u003ccode\u003eSELECT\u003c/code\u003e statements that group or partition by \u003ccode\u003euser_id\u003c/code\u003e, and these statements must have preceding joins that preserve user-level data.\u003c/p\u003e\n"],["\u003cp\u003eHyperLogLog++ functions in ADH support specific data types (\u003ccode\u003eADH.USER_HLL\u003c/code\u003e, \u003ccode\u003eADH.BYTE_HLL\u003c/code\u003e) and a limited set of functions (e.g., \u003ccode\u003eHLL_COUNT.EXTRACT\u003c/code\u003e, \u003ccode\u003eHLL_COUNT.INIT\u003c/code\u003e), with restrictions on noise injection and direct export.\u003c/p\u003e\n"],["\u003cp\u003eUser list queries allow the use of aggregate functions but do not support analytic functions.\u003c/p\u003e\n"]]],["ADH restricts certain SQL functions for privacy. All scalar functions are permitted except for a list of 45, including `ERROR` and various `ST_` functions related to geometry. Aggregate functions such as `ANY_VALUE`, `AVG`, `COUNT`, `MAX`, `MIN`, and `SUM` are allowed, while `APPROX_QUANTILES`, `ARRAY_AGG`, and `STRING_AGG` are not. All analytic functions are restricted. HyperLogLog++ (HLL++) functions are supported with `ADH.USER_HLL` and `ADH.BYTE_HLL` types, subject to limitations. All functions are permitted when grouping/partitioning by `user_id`. In user list queries, only aggregate functions are allowed.\n"],null,["# Allowed SQL functions\n\nFor privacy reasons, we restrict which SQL functions you can use in ADH.\n\nScalar functions\n----------------\n\nAll scalar functions except the following are allowed:\n\n- `ERROR`\n- `ST_AREA`\n- `ST_ASBINARY`\n- `ST_ASGEOJSON`\n- `ST_ASTEXT`\n- `ST_BOUNDARY`\n- `ST_CENTROID`\n- `ST_CENTROID_AGG`\n- `ST_CLOSESTPOINT`\n- `ST_CLUSTERDBSCAN`\n- `ST_CONTAINS`\n- `ST_CONVEXHULL`\n- `ST_COVEREDBY`\n- `ST_COVERS`\n- `ST_DIFFERENCE`\n- `ST_DIMENSION`\n- `ST_DISJOINT`\n- `ST_DISTANCE`\n- `ST_DUMP`\n- `ST_DWITHIN`\n- `ST_EQUALS`\n- `ST_GEOGFROMGEOJSON`\n- `ST_GEOGFROMTEXT`\n- `ST_GEOGFROMWKB`\n- `ST_GEOGPOINT`\n- `ST_GEOGPOINTFROMGEOHASH`\n- `ST_GEOHASH`\n- `ST_INTERSECTION`\n- `ST_INTERSECTS`\n- `ST_INTERSECTSBOX`\n- `ST_ISCOLLECTION`\n- `ST_ISEMPTY`\n- `ST_LENGTH`\n- `ST_MAKELINE`\n- `ST_MAKEPOLYGON`\n- `ST_MAKEPOLYGONORIENTED`\n- `ST_MAXDISTANCE`\n- `ST_NPOINTS`\n- `ST_NUMPOINTS`\n- `ST_PERIMETER`\n- `ST_SIMPLIFY`\n- `ST_SNAPTOGRID`\n- `ST_TOUCHES`\n- `ST_UNION`\n- `ST_UNION_AGG`\n- `ST_WITHIN`\n- `ST_X`\n- `ST_Y`\n\nAnalysis queries\n----------------\n\nFor aggregated analysis queries, we restrict functions that combine data from\nmultiple users. All aggregate and analytic functions are allowed when processing\na single user's data. See [User-level aggregations](#user_level) below for\ndetails.\n\n### Aggregate functions\n\n| Function | Allowed? |\n|-------------------------|----------|\n| `ANY_VALUE` | Yes |\n| `APPROX_QUANTILES` | No |\n| `APPROX_TOP_COUNT` | No |\n| `APPROX_TOP_SUM` | No |\n| `ARRAY_AGG` | No |\n| `ARRAY_CONCAT_AGG` | No |\n| `AVG` | Yes |\n| `BIT_AND` | No |\n| `BIT_OR` | No |\n| `BIT_XOR` | No |\n| `COUNT` | Yes |\n| `COUNTIF` | Yes |\n| `LOGICAL_AND` | Yes |\n| `LOGICAL_OR` | Yes |\n| `MAX` | Yes |\n| `MIN` | Yes |\n| `SUM` | Yes |\n| `CORR` | Yes |\n| `COVAR_POP` | Yes |\n| `COVAR_SAMP` | Yes |\n| `STDDEV_POP` | Yes |\n| `STDDEV_SAMP` | Yes |\n| `STDDEV` | Yes |\n| `STRING_AGG` | No |\n| `VAR_POP` | Yes |\n| `VAR_SAMP` | Yes |\n| `VARIANCE` | Yes |\n| `APPROX_COUNT_DISTINCT` | Yes |\n\n### Analytic functions\n\nAll [analytic\nfunctions](https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts)\nare restricted.\n\n### HyperLogLog++ functions\n\nAds Data Hub supports [HyperLogLog++ (HLL++)\nfunctions](https://cloud.google.com/bigquery/docs/reference/standard-sql/hll_functions).\nHowever, they use specialized data types with more limitations.\n\n**Supported types**\n\n| Type | Description |\n|----------------|-------------------------------------------------------|\n| `ADH.USER_HLL` | A sketch aggregated from values of type `ADH.USER_ID` |\n| `ADH.BYTE_HLL` | A sketch aggregated from values of any non-ID type |\n\nThese types support merging and extracting to cardinality estimates, and don't\nsupport direct conversion to any other type. They cannot be directly exported\nfrom Ads Data Hub, so they must use `HLL_COUNT.EXTRACT` or `HLL_COUNT.MERGE`\nbefore reaching the query output.\n\nHLL sketches enable cardinality estimation across multiple layers of\naggregation, but note that in Ads Data Hub they don't give a substantial\nperformance improvement over `COUNT(DISTINCT)` in a single layer.\n\n**Supported functions**\n\n| Function name | Supported types | Return type |\n|---------------------------|------------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------|\n| `HLL_COUNT.EXTRACT` | `ADH.USER_HLL`, `ADH.BYTE_HLL` | `INT64` |\n| `HLL_COUNT.INIT` | [All standard supported types](https://cloud.google.com/bigquery/docs/reference/standard-sql/hll_functions#hll_countinit), `ADH.USER_ID` | `ADH.USER_HLL` for `ADH.USER_ID` input, otherwise `ADH.BYTE_HLL` |\n| `HLL_COUNT.INITNT.MERGE` | `ADH.USER_HLL`, `ADH.BYTE_HLL` | `INT64` |\n| `HLL_COUNT.MERGE_PARTIAL` | `ADH.USER_HLL`, `ADH.BYTE_HLL` | Same as input type |\n\n**Limitations**\n\n**Noise injection:** HLL++ functions are not supported when running a query\nusing\n[noise injection](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection).\n\n### User-level aggregations\n\nWe allow all aggregate and analytic functions in `SELECT` statements that group\nor partition by `user_id`. For this to work, any preceding joins must make it\nclear that the data is kept at the user level by joining on `user_id`, like in\nthis example: \n\n CREATE TABLE paths AS (\n SELECT ARRAY_AGG(campaign_id ORDER BY query_id.time_usec) AS path\n FROM\n adh.google_ads_impressions\n LEFT JOIN adh.google_ads_clicks\n USING(query_id, user_id)\n GROUP BY user_id)\n\nUser list queries\n-----------------\n\nIn user list queries, aggregate functions are allowed, and analytic functions\naren't supported."]]
