Allows raw aggregates to be used in parameters when QueryPassthrough is enabled.

Syntax

Connect-GoogleBigQuery -AllowAggregateParameters SwitchParameter

Data Type

bool

Default Value

false

Remarks

This option affects how string parameters are handled when using direct queries through QueryPassthrough. For example, consider this query:

INSERT INTO proj.data.tbl(x) VALUES (@x)

By default, this option is disabled and string parameters are quoted and escaped into SQL strings. That means that any value can be safely used as a string parameter, but it also means that parameters cannot be used as raw aggregate values:

/*
 * If @x is set to: test value ' contains quote
 *
 * Result is a valid query
*/
INSERT INTO proj.data.tbl(x) VALUES ('test value \' contains quote')

/*
 * If @x is set to: ['valid', ('aggregate', 'value')]
 *
 * Result contains string instead of aggregate:
*/
INSERT INTO proj.data.tbl(x) VALUES ('[\'valid\', (\'aggregate\', \'value\')]')

When this option is enabled, string parameters are inserted directly into the query. This means that raw aggregates can be used as parameters, but it also means that all simple strings must be escaped:

/*
 * If @x is set to: test value ' contains quote
 *
 * Result is an invalid query
*/
INSERT INTO proj.data.tbl(x) VALUES (test value ' contains quote)

/*
 * If @x is set to: ['valid', ('aggregate', 'value')]
 *
 * Result is an aggregate
*/
INSERT INTO proj.data.tbl(x) VALUES (['valid', ('aggregate', 'value')])