USQL create user defined table type with user defined data type columns

Question

I defined a User Defined Type as

namespace AddOns{
[SqlUserDefinedType(typeof(JsonObjectFormatter))]
    public class JsonObject
    {
        public string Value {get;set;}
        ... // this is just a dummy representation 
    }
}

I want to define a Table Valued Function that returns a datatype

REFERENCE ASSEMBLY [AddOns];

CREATE TYPE Insight.dbo.JsonRow
AS TABLE
(
        [Id] Guid,
        [Value] AddOns.JsonObject
);

However I get an error

'E_CSC_USER_INVALIDCOLUMNTYPE: 'AddOns.JsonObject' cannot be used as column type.
Description:
The column type must be a supported scalar, complex or user defined type.
Resolution:
Ensure the column type is a supported type. For a user defined type, make sure the type is registered, the type name is fully qualified, and the required assembly is referenced by the script.'
*** Compile failed !

I have registered the appropriate DLL in my local instance of ADLA and I am able to access the type in the procedures SELECT statements when I am persisting the data to a file. But cannot return it as a TVF return type

score 0 · Accepted Answer · answered May 22 '18 at 19:04

You are limited to only the built in types when creating a custom type. From the MS documentation page (the literal last line I pasted says only built in types):

U-SQL can name and register table types with the CREATE TYPE statement.

Create_Type_Statement :=                                                                                 
    'CREATE' 'TYPE' ['IF' 'NOT' 'EXISTS'] Type_Identifier   
    'AS' Anonymous_Table_Type.
Type_Identifier := 
    DB_Object_Identifier.

Anonymous_Table_Type :=  
    'TABLE' '(' Column_Definition_List ')'.

Semantics of Syntax Elements

...

Column_Definition_List

Defines the table schema as follows :

Column_Definition_List :=                                                                           
     Column_Definition { ',' Column_Definition }.

Column_Definition

A column definition is of the form

Column_Definition :=    
    Quoted_or_Unquoted_Identifier Built_in_Type.

You may find a solution [here](https://stackoverflow.com/a/49698841/5148426) if you choose to use an ArrayAgg (complex built in type) to house your data in the form of primitives. — jraffdev, May 22 '18 at 19:20

USQL create user defined table type with user defined data type columns

1 Answers1