Allowed SQL functions

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

  • In aggregated analysis queries, functions that combine data from multiple users are restricted, but most aggregate and analytic functions are allowed when processing a single user's data.

  • HyperLogLog++ functions with specialized data types are supported in ADH for cardinality estimation but have limitations, such as not being supported with noise injection.

  • All aggregate and analytic functions are allowed in SELECT statements that group or partition by user_id for user-level aggregations.

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

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 below for details.

Aggregate functions

Function Allowed?
ANY_VALUE Yes
APPROX_QUANTILES No
APPROX_TOP_COUNT No
APPROX_TOP_SUM No
ARRAY_AGG No
ARRAY_CONCAT_AGG No
AVG Yes
BIT_AND No
BIT_OR No
BIT_XOR No
COUNT Yes
COUNTIF Yes
LOGICAL_AND Yes
LOGICAL_OR Yes
MAX Yes
MIN Yes
SUM Yes
CORR Yes
COVAR_POP Yes
COVAR_SAMP Yes
STDDEV_POP Yes
STDDEV_SAMP Yes
STDDEV Yes
STRING_AGG No
VAR_POP Yes
VAR_SAMP Yes
VARIANCE Yes
APPROX_COUNT_DISTINCT Yes

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_clicks
    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.