Start Free Trial   Support

lyft_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

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

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

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

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

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

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

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

EXEC lyft_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"}]}'

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

EXEC lyft_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"}]}'

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

EXEC lyft_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