Can I use WHERE clause in Pipeline?

Help
1

right now, i have the code below to create a pipeline from a kafka topic and replace into a memsql rowstore.

my Q is this:
can i FILTER using a WHERE clause which of the Kakfa messages are actually included?
in other words, can i make the 2nd line something like this:

replace into table ‘XYZ’ where Field = ‘value’

create pipeline XYZ as load data kafka ‘kafka/XYZ’
replace into table XYZ
format Avro
(
_posDt ← data::date,
account ← data::account,
)
SCHEMA
’
{
“type”: “record”,
“name”: “confluent_kafka_wrapper”,
“fields”: [
{
“name”: “metadata”,
“type”: {
“type”: “fixed”,
“size”: 5,
“name”: “unused_confluent_metadata”
}
},
{
“name”: “data”,
“type”: {
“type”: “record”,
“name”: “EdfFuturesEodPosition”,
“fields”: [
{
“name”: “date”,
“type”: “string”
},
{
“name”: “account”,
“type”: “string”
},
{
“name”: “cusip”,
“type”: “string”
},
]
}
}
]
}
';

2

You can do exactly that using the where sub-clause of the as load data clause. The where clause can be any SQL expression referencing columns of the parsed row or @-variables defined in the subvalue_mapping clause. We won’t attempt to insert parsed rows for which the expression is false.

We don’t have an example involving Avro, but the syntax and behavior of the where clause is identical to CSV and JSON pipelines/loads. See example 2 in the JSON section:
https://docs.memsql.com/sql-reference/v6.8/load-data/#examples.

Note that an expression using @avar there would also be valid in the where clause.