How to customize key_properties for table clustering

[xiangshiyin]

The doc about the config cluster_on_key_properties says

Determines whether to cluster on the key properties from the tap. Defaults to false. When false, clustering will be based on _sdc_batched_at instead.

The code confirms that key_properties is used to define clustering key set and used as the primary key in merge operation

• target-bigquery/target_bigquery/core.py

  Line 468 in 9d1d0b0

  kwargs["table"]["clustering_fields"] = tuple(key_properties[:4])

• target-bigquery/target_bigquery/core.py

  Line 527 in 9d1d0b0

  f"QUALIFY ROW_NUMBER() OVER (PARTITION BY {', '.join(f'`{p}`' for p in self.key_properties)} "

Anyone knows how to determine the default key_properties value and how to customize (is it even possible)? Thanks!

[xiangshiyin]

With some further digging, we believe the key_properties here should be the primary keys defined in the stream. With the current clustering key configuration in the sink, the BQ table clustering is directly influenced by the order of columns in the primary key combo defined in the incoming stream. It'll be good if we could have a configurable parameter under the sink so we have more flexibility.

cc. @epapineau