Allowed SQL functions

  • Certain SQL functions are restricted in ADH for privacy reasons, including specific scalar, aggregate, and analytic functions.

  • All scalar functions are generally allowed, with a list of explicit exceptions.

  • For aggregated analysis queries, functions that combine data from multiple users are restricted, but all aggregate and analytic functions are allowed when processing single user data.

  • HyperLogLog++ functions are supported but use specialized data types with limitations, such as not being supported with noise injection.

  • In user list queries, aggregate functions are allowed, while analytic functions are not.

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. See User-level aggregations for details.

Aggregate functions

The following functions are allowed for aggregation across users:

  • SUM
  • COUNT
  • COUNTIF
  • APPROX_COUNT_DISTINCT
  • AVG

In the difference check privacy mode, the following functions are also allowed:

  • ANY_VALUE
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

See noise injection for details on additional noise-specific aggregate functions and features.

Analytic functions

All analytic functions are restricted.

HyperLogLog++ functions

Ads Data Hub supports HyperLogLog++ (HLL++) functions. However, they use specialized data types with more limitations.

Supported types

Type Description
ADH.USER_HLL A sketch aggregated from values of type ADH.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 use HLL_COUNT.EXTRACT or HLL_COUNT.MERGE before 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 over COUNT(DISTINCT) in a single layer.

Supported functions

Function name Supported types Return type
HLL_COUNT.EXTRACT ADH.USER_HLL, ADH.BYTE_HLL INT64
HLL_COUNT.INIT All standard supported types, ADH.USER_ID ADH.USER_HLL for ADH.USER_ID input, otherwise ADH.BYTE_HLL
HLL_COUNT.INITNT.MERGE ADH.USER_HLL, ADH.BYTE_HLL INT64
HLL_COUNT.MERGE_PARTIAL ADH.USER_HLL, ADH.BYTE_HLL Same as input type

Limitations

Noise injection: HLL++ functions are not supported when running a query using noise injection.

User-level aggregations

We allow all aggregate and analytic functions in SELECT statements that group or partition by user_id. For this to work, any preceding joins must make it clear that the data is kept at the user level by joining on user_id, like in this example:

CREATE TABLE paths AS
SELECT ARRAY_AGG(campaign_id ORDER BY query_id.time_usec) AS path
FROM
  adh.google_ads_impressions
  LEFT JOIN adh.google_ads_creative_conversions
    USING(query_id, user_id)
GROUP BY user_id;

User list queries

In user list queries, aggregate functions are allowed, and analytic functions aren't supported.