The DECLARE statement allows us to define parameters to store values for things that aren’t RowSets.

We’ll start with this snippet

@rows = 
  EXTRACT 
      name string, 
      id int
  FROM "/data.csv"
  USING Extractors.Csv();

DECLARE can assign constant values to a name. In this case we can assign the input file to a parameter.

DECLARE @inputfile string = "/data.csv";

@rows = 
  EXTRACT 
    name string, 
    id   int
  FROM @inputfile
  USING Extractors.Csv();

Parameter values cannot be assigned directly from a RowSet.

Values cannot be assigned from a RowSet to a DECLARE parameter.

// This does NOT work
DECLARE @maxval int = SELECT MAX(value) FROM data;

An alternative is to get a single-row RowSet with a single column and then JOIN that single-row RowSet other RowSet to get what you need.

Supported datatypes

Text

DECLARE @text1 string = "Hello World";
DECLARE @text2 string = @"Hello World";

DECLARE @text3 char   = 'a';

Datetimes

DECLARE @d1 DateTime = System.DateTime.Parse("1979/03/31");
DECLARE @d2 DateTime= DateTime.Now;

Signed numerics

DECLARE @a sbyte  = 0;
DECLARE @b short  = 1;

DECLARE @c int    = 2;
DECLARE @d long   = 3L;

DECLARE @e float  = 4.0f;
DECLARE @f double = 5.0;

Unsigned numerics

DECLARE @g byte   = 0;
DECLARE @h ushort = 1;
DECLARE @i uint   = 2;
DECLARE @j ulong  = 3L;

.NET arrays

DECLARE @array1 byte [] = new byte[] { 0, 1, 2, 3, 4 };
DECLARE @array2 string [] = new string[] { "foo", "bar", "beer" };

Complex types

DECLARE @m SqlMap<string, string> = new SqlMap<string, string> 
    { 
        {"This", "is a string in a map"}, 
        {"That", "is also a string in a map"} 
    };

Miscellaneous

DECLARE @misc1 bool    = true;
DECLARE @misc2 Guid    = System.Guid.Parse("BEF7A4E8-F583-4804-9711-7E608215EBA6");

Using Expressions with DECLARE

DECLARE parameters can be expressions

DECLARE @a string = "BEGIN" + @text1 + "END";
DECLARE @b string = string.Format("BEGIN{0}END", @text1);
DECLARE @c string = MyHelper.GetMyName();

Parameter type inference

DECLARE can infer the type as shown below.

DECLARE @a = "Hello World"; // string
DECLARE @b = 'a'; // char
DECLARE @c = 2; // int
DECLARE @d = 2L; // long
DECLARE @d = 4.0f; // float
DECLARE @e = 5.0; // double

DECLARE @m = new SqlMap<string, string> 
    {
        {"This", "is a string in a map"},
        {"That", "is also a string in a map"}
    };

Native Data types:

U-SQL has many built-in Native U-SQL datatypes in that U-SQL has special support for them so that using them will be more performant. In general use the U-SQL’s native data types wherever possible.

Most of the datatypes below will be familiar to a C#/.NET programmer. Indeed except for MAP and ARRAY, these ARE the normal .NET datatypes you are used to.

Numeric signed

sbyte
int
long
float
double
decimal
short

Numeric unsigned

byte
uint
ulong
ushort

Text

char
string

Complex

MAP<k,v>
ARRAY<v>

Miscellaneous

bool
Guid
DateTime
byte[]

Nullable Data Types:

In .NET types that cannot be null such as int are called value types. Types that can have a null value such as string are called reference types. Sometimes it is convenient though to value that is an value type but that that can also have a null value. These types are called nullable types.

Using nullable types in C# code

// This is C# code, not U-SQL
int? i = 100;
i = 200;
i = null;

Supported nullable types

U-SQL supports the following nullable types

byte?
sbyte?
int?
uint?
long?
ulong?
float?
double?
decimal?
short?
ushort?
bool?
Guid?
DateTime?
char?

Nullable types don’t apply to reference types such as string

The purpose of “nullable” is enable null values for a type that doesn’t already support null values. The string type – like all .NET reference types – already supports null values.