Allowed SQL functions

Page Summary

outlined_flag

• 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

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

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

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.