CREATE PACKAGE (U-SQL)

  • Article

Summary

The CREATE PACKAGE statement creates a package to allow bundling of commonly used together U-SQL assemblies, variables and resources.

A package declaration can consist of the using statement, and declare statements to set its own internal static name context, import from other packages, declare what gets exported by the package, and what resources the package will deploy to the runtime vertices. It also provides an IF statement that has the same semantics as the general U-SQL IF statement but only allows the statements supported inside a package definition.

The optional parameters can be used inside the package definition to help define variables, and to determine which alternative the IF statement will choose.

The package object will be created inside the current database and schema context.

Syntax

Create_Package_Statement :=                                                                              
    'CREATE' 'PACKAGE' ['IF' 'NOT' 'EXISTS'] Identifier '(' [Parameter_List] ')'
    ['AS']
    'BEGIN'
        Package_Statement_List
    'END'.

Package_Statement_List := 
    { Package_Statement }.

Package_Statement :=
    Using_Statement
|   Declare_Variable_Statement
|   Import_Package_Statement
|   Export_Statement
|   Deploy_Resource_Statement
|   If_Package_Statement.

Export_Statement :=
    Export_User_Assembly_Statement | Export_System_Assembly_Statement | Export_Variable_Statement.

If_Package_Statement :=
    'IF' Boolean_Expression 'THEN'
        Package_Statement
    ['ELSEIF' Boolean_Expression 'THEN'
        Package_Statement ]
    ['ELSE' Boolean_Expression 'THEN'
        Package_Statement ]
    'END'.

Remarks

  • IF NOT EXISTS
    If the optional IF NOT EXISTS is specified, then the statement creates the package if it does not already exist, or succeeds without changes if the package already exists and the user has permission to at least enumerate all existing packages.

  • Identifier
    Specifies the name of the package. If the Identifier is a three-part identifier, the package will be created in the specified database and schema. If it is a two-part identifier, then the package will be created in the specified schema of the current database context. If the identifier is a simple identifier, then the package will be created in the current database and schema context.

    If an object of the given name already exists in the specified database and schema context or the user has no permissions to create a package, an error is raised.

  • Parameter_List
    The parameter list provides the arguments and their types and optional default values.

  • Boolean_Expression
    Is a constant-foldable expression that returns TRUE or FALSE.

  • Export_User_Assembly_Statement | Export_System_Assembly_Statement
    The EXPORT ASSEMBLY and EXPORT SYSTEM ASSEMBLY statements specify inside a package definition which user or system assemblies respectively are being exported by the package. They also implicitly reference the assembly for the package context.

    The identifier will be resolved in the static context of the package definition, similarly to table-valued functions and procedures and can refer optionally to a user assembly in a different Azure Data Lake Analytics account.

Syntax

  Export_User_Assembly_Statement :=                                                                   
      'EXPORT' 'ASSEMBLY' Global_Assembly_Identifier.

  Export_System_Assembly_Statement :=
      'EXPORT' 'SYSTEM' 'ASSEMBLY' Assembly_Name.

  Assembly_Name := 
      Quoted_or_Unquoted_Identifier.
  • Export_Variable_Statement
    The EXPORT statement specifies inside a package definition which variable is being exported by the package. It also implicitly declares the variable inside the package context.

Syntax

  Export_Variable_Statement :=                                                                        
      'EXPORT' ['CONST'] User_Variable_Name [Scalar_Type] '=' csharp_expression.

  User_Variable_Name :=
      "@"+Unquoted_Identifier.
   
  Scalar_Type := 
      Built_in_Type | User_defined_Type.

If a variable of a user-defined type gets exported, then the assembly defining the type should also be exported.

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.

Basic Syntax
The following example creates a package XMLFiles in an already existing database TestReferenceDB. The package exports a variable named @xmlfile with the value /configfiles/config.xml and deploys that file. The file does not need to exist during creation of the package, but it will have to exist at the time the import of the package occurs into the main script.

USE DATABASE TestReferenceDB;
DROP PACKAGE IF EXISTS XMLFiles;

CREATE PACKAGE XMLFiles()
BEGIN
    EXPORT @xmlfile = "/configfiles/config.xml";
    DEPLOY RESOURCE @xmlfile;
END;

Parameterized package
A parameterized package, XMLorJSON, is created in the same database that - based on the passed parameter - bundles the previously created custom assembly Microsoft.Analytics.Samples.Formats from the database JSONBlog with a different assembly (NewtonSoft.Json from the database JSONBlog if the parameter is json or the system assembly System.Xml if the parameter is xml) and a @format variable, that is set to different values depending on the parameter. It also imports the previously defined package XMLFiles with the alias xmlpack and re-exports its @xmlfile variable if the parameter is set to xml.

DROP PACKAGE IF EXISTS TestReferenceDB.dbo.XMLorJSON;

CREATE PACKAGE TestReferenceDB.dbo.XMLorJSON(@requestedFormat string = "json")
BEGIN
    // @xml and @json are just used inside the package and are not exported
    DECLARE @xml = "xml";
    DECLARE CONST @json = "json"; 

    // The following IMPORT statements will import variables and resource deployments from XMLFiles. 
    // Since there is no automatic re-export, it does not make sense to put an assembly export into
    // a package that only gets imported into a different package.
    // Note that no argument list is needed if no arguments are being passed.

    IF @requestedFormat == @xml THEN 
        IMPORT PACKAGE XMLFiles AS xmlpack;

        EXPORT ASSEMBLY JSONBlog.[Microsoft.Analytics.Samples.Formats];
        EXPORT SYSTEM ASSEMBLY [System.Xml]; 
        EXPORT @xmlfile = xmlpack.@xmlfile;
        EXPORT @format = "xml";

    ELSEIF @requestedFormat == @json THEN
        EXPORT ASSEMBLY JSONBlog.[Microsoft.Analytics.Samples.Formats];
        EXPORT ASSEMBLY [NewtonSoft.Json];
        EXPORT @format = "json";

    ELSE
        EXPORT @format = "invalid";
    END;
END;

See Also