CREATE ASSEMBLY (U-SQL)

  • Article

Summary

If a .NET assembly is required during script compilation (for example because it specifies a function or type that needs to be resolved during compile time), then it needs to be registered.

The CREATE ASSEMBLY statement registers an assembly and optionally associated resources.

Syntax

Create_Assembly_Statement :=                                                                             
    'CREATE' 'ASSEMBLY' ['IF' 'NOT' 'EXISTS'] Assembly_Name  
    'FROM' Assembly_Source   
    ['WITH' 'ADDITIONAL' 'FILES' '='   
            '(' Assembly_Additional_File_List ')'].
 
Assembly_Name :=
    Quoted_or_Unquoted_Identifier.

Assembly_Source :=
    Static_String_Expression | lexical_binary_value.

Remarks

  • Assembly_Name
    Specifies the name of the assembly in the current database context.

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

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

  • Assembly_Source
    Specifies the assembly DLL either in form of a binary literal or as a string literal or static string expression/string variable. The binary literal represents the actual .NET assembly DLL, while the string values represent a URI or file path to a .NET assembly DLL file in either an accessible Azure Data Lake Storage or Windows Azure Blob Storage. If the provided source is a valid .NET assembly, the assembly will be copied and registered, otherwise an error is raised. A database cannot contain more than one version of the same assembly.

    Note that the binary literal is mainly useful for tools that want to register the assembly without first creating and then copying a file.

  • Assembly_Additional_File_List
    Optionally, additional files can be provided in the WITH ADDITIONAL FILES clause:

Syntax

  Assembly_Additional_File_List :=                                                                    
      Assembly_Additional_File {',' Assembly_Additional_File}.
                                                                                                    
  Assembly_Additional_File :=  
      Assembly_Source ['AS' string_literal].

An additional file is specified with:

  • Assembly_Additional_File
    Each additional file can be represented in the same way as the main assembly by either referring to a file location or the actual file content in binary format. The file can be any type of file that may be needed in conjunction with the main assembly. Some general use cases are: configuration text, JSON or XML files, native DLLs or executables that are called by the code in the assembly.

    Other .NET assembly files can be added, as long as they are only needed during runtime and not script compilation, although it is not recommended. If they are needed during compilation, they have to be separately registered with CREATE ASSEMBLY and referenced in the script with REFERENCE ASSEMBLY. Note that an additionally added .NET assembly may conflict with an explicitly referenced assembly in the same script, so care has to be taken when deciding to include a .NET assembly file as an additional file.

  • 'AS' string_literal
    This clause optionally specifies the name that is used for the additional file, when it is placed into the runtime working directory. The string literal has to be a valid filename (and not contain a path) or an error is raised. Note that this should the name of the file that the main assembly will be using to refer to the file or the assembly code will fail to find the file at runtime and raise an error.

Examples

  1. Download and install ENU\x64\SQLSysClrTypes.msi from Microsoft® SQL Server® Feature Pack.

  2. Create directory upload\asm\spatial in your Azure Data Lake Store. Upload the following files to upload\asm\spatial:

    • C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Types.dll
    • C:\Windows\System32\SqlServerSpatial130.dll

  3. Register assembly

DECLARE @ASSEMBLY_PATH string = "/upload/asm/spatial/";
DECLARE @SPATIAL_ASM string = @ASSEMBLY_PATH+"Microsoft.SqlServer.Types.dll";
DECLARE @SPATIAL_NATIVEDLL string = @ASSEMBLY_PATH+"SqlServerSpatial130.dll";

CREATE DATABASE IF NOT EXISTS SQLSpatial;
USE DATABASE SQLSpatial;

DROP ASSEMBLY IF EXISTS SqlSpatial;
CREATE ASSEMBLY SqlSpatial
FROM @SPATIAL_ASM
WITH ADDITIONAL FILES =
     (
         @SPATIAL_NATIVEDLL
    );

See Also