PARTITION (U-SQL)

Artikel
03/10/2017

Summary

The PARTITION intrinsic object returns true if the specified partition for the given table exists and the user has access to said table. Otherwise, false is returned. The function will be evaluated at compile-time (and is thus constant-foldable).

Syntax

bool PARTITION.EXISTS(                                                                                   
     Identifier, 
     partition_value {, partition_value}
).

Remarks

Identifier
Identifies the table to be checked. If the Identifier is a three-part identifier, the table from the specified database and schema will be chosen. If the Identifier is a two-part identifier, then the table of the given schema and of the given name of the current static database context is chosen. If the identifier is a simple identifier, then the table of the given name in the current static database and schema context is chosen.

If the provided table does not exists or the user does not have access to it, the error E_CSC_USER_DDLENTITYDOESNOTEXIST is raised.
partition_value
The typed values that define the particular partition of the table. The partition_value expression must be constant-foldable; otherwise, the error E_CSC_USER_EXPRESSIONNOTCONSTANTFOLDABLE is raised.

Return Type

bool

Examples

The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.

Single column partition

CREATE DATABASE IF NOT EXISTS TestReferenceDB;
USE DATABASE TestReferenceDB; 

DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderID int,
    CustomerID int,
    OrderDetailID int,
    OrderTotal double,
    OrderDate DateTime,
    INDEX clx_OrderID_CustomerID CLUSTERED(OrderID, CustomerID ASC)
    PARTITIONED BY (OrderDate)
    DISTRIBUTED BY HASH (OrderID, CustomerID)
);

DECLARE @partition1 DateTime = new DateTime(2016, 01, 01, 00,00,00,00, DateTimeKind.Utc);
DECLARE @partition2 DateTime = @partition1.AddDays(1);

IF (!PARTITION.EXISTS(TestReferenceDB.dbo.Orders, @partition1))
THEN
    ALTER TABLE TestReferenceDB.dbo.Orders ADD PARTITION (@partition1);
END;

Multiple columns partition

USE DATABASE TestReferenceDB; 

DROP TABLE IF EXISTS dbo.PartTable;
CREATE TABLE dbo.PartTable
(
    PartId int,
    market string,
    description string,
    price decimal,
    INDEX idx CLUSTERED(price)
    PARTITIONED BY (PartId, market)
    DISTRIBUTED BY RANGE(price)
);

IF (!PARTITION.EXISTS(dbo.PartTable, 1, "en-us"))
THEN
    ALTER TABLE dbo.PartTable ADD PARTITION (1, "en-us");
END;

INSERT INTO PartTable(description, price)
            PARTITION(1,"en-us")
VALUES
   ("description 1", (decimal) 12.99),
   ("description 2", (decimal) 49.99);

Freigeben über