qua_set_partitioning_schema#

Sets partitioning mode and schema for a given view.

Arguments#

  • @view_name - Fully qualified name of view which partitioning mode will be changed.

  • @partition_mode - NCHAR(MAX) that contains one of the following values:

    • ‘no_partitioning’ - No partitioning

    • ‘random_repartitioning’ - Random repartitioning

    • ‘linear’ - Linear column partitioning

    • ‘column_value_conf’ - Column value based

    • ‘column_values_ranges_conf’ - Column value ranges based

    • ‘predicates_conf’ - Predicate based. For details regarding each partitioning mode, please consult reference guide’s caching and partitioning chapter.

  • @schema NVARCHAR(MAX) that contains JSON string describing details of partitioning configuration.

    Allowed configuration options depend on @partition_mode that was selected.

Examples#

  1. Sets no partitioning for AdventureWorksDW2012_spark.dbo.DimEmployee.

EXEC qua_set_partitioning_schema
    @view_name = 'AdventureWorksDW2012_spark.dbo.DimEmployee'
  , @partition_mode = 'no_partitioning'
  , @schema ='';

  1. Sets random repartitioning for AdventureWorksDW2012_spark.dbo.DimEmployee.

EXEC qua_set_partitioning_schema
    @view_name = 'AdventureWorksDW2012_spark.dbo.DimEmployee',
    @partition_mode = 'random_repartitioning',
    @schema ='{"partitionCount":"5"}';

  1. Sets linear partitioning for AdventureWorksDW2012_spark.dbo.DimEmployee.

EXEC qua_set_partitioning_schema
    @view_name = 'AdventureWorksDW2012_spark.dbo.DimEmployee',
    @partition_mode = 'linear',
    @schema ='{"partitionColumn":"SalesTerritoryKey", "partitionCount":5, "partitionMin": 1, "partitionMax": 15}';

  1. Sets column value based for AdventureWorksDW2012_spark.dbo.DimEmployee.

EXEC qua_set_partitioning_schema
    @view_name = 'AdventureWorksDW2012_spark.dbo.DimEmployee',
    @partition_mode = 'column_value_conf',
    @schema = '{"partitionColumn":"SalesTerritoryKey","column_value_conf":[{"value":"1"},{"value":"2"},{"value":"3"},{"value":"4"},{"value":"5"},{"value":"6"},{"value":"7"},{"value":"8"},{"value":"9"},{"value":"10"},{"value":"11"}]}'

  1. Sets column value ranges based partitioning for AdventureWorksDW2012_spark.dbo.DimEmployee.

EXEC qua_set_partitioning_schema
    @view_name = 'AdventureWorksDW2012_spark.dbo.DimEmployee',
    @partition_mode = 'column_values_ranges_conf',
    @schema = '{"partitionColumn":"SalesTerritoryKey","column_values_ranges_conf":[{"min":"1","max":"3"},{"min":"4","max":"7"},{"min":"8","max":"11"}]}'

  1. Sets predicate based partitioning for AdventureWorksDW2012_spark.dbo.DimEmployee.

EXEC qua_set_partitioning_schema
    @view_name = 'AdventureWorksDW2012_spark.dbo.DimEmployee',
    @partition_mode = 'predicates_conf',
    @schema = '{"predicates_conf":[{"predicate":"SalesTerritoryKey >= 0 AND SalesTerritoryKey  <=3"},{"predicate":"SalesTerritoryKey >= 4 AND SalesTerritoryKey  <=7"},{"predicate":"SalesTerritoryKey >= 8 AND SalesTerritoryKey  <= 12"}]}'

See also#