/// <summary>
/// Maps each group of the current DataFrame using a UDF and
/// returns the result as a DataFrame.
///
/// The user-defined function should take an Apache Arrow RecordBatch
/// and return another Apache Arrow RecordBatch. For each group, all
/// columns are passed together as a RecordBatch to the user-function and
/// the returned RecordBatch are combined as a DataFrame.
///
/// The returned <see cref="RecordBatch"/> can be of arbitrary length and its
/// schema must match <paramref name="returnType"/>.
/// </summary>
/// <param name="returnType">
/// The <see cref="StructType"/> that represents the shape of the return data set.
/// </param>
/// <param name="func">A grouped map user-defined function.</param>
/// <returns>New DataFrame object with the UDF applied.</returns>
public DataFrame Apply(StructType returnType, Func <RecordBatch, RecordBatch> func)
{
ArrowGroupedMapWorkerFunction.ExecuteDelegate wrapper =
new ArrowGroupedMapUdfWrapper(func).Execute;
UserDefinedFunction udf = UserDefinedFunction.Create(
Reference.Jvm,
func.Method.ToString(),
CommandSerDe.Serialize(
wrapper,
CommandSerDe.SerializedMode.Row,
CommandSerDe.SerializedMode.Row),
UdfUtils.PythonEvalType.SQL_GROUPED_MAP_PANDAS_UDF,
returnType.Json);
IReadOnlyList <string> columnNames = _dataFrame.Columns();
var columns = new Column[columnNames.Count];
for (int i = 0; i < columnNames.Count; ++i)
{
columns[i] = _dataFrame[columnNames[i]];
}
Column udfColumn = udf.Apply(columns);
return(new DataFrame((JvmObjectReference)Reference.Invoke(
"flatMapGroupsInPandas",
udfColumn.Expr())));
}
private void AddAssemblyButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
SqlAssembly asm;
UserDefinedFunction udf;
UserDefinedFunctionParameter parm;
ListViewItem AssemblyListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get selected database
db = (Database)DatabasesComboBox.SelectedItem;
asm = new SqlAssembly(db, "UtilityConversion");
asm.Owner = "dbo";
asm.AssemblySecurityLevel = AssemblySecurityLevel.Safe;
// This allows the assembly to be on a different server from SQL Server
// Use string array version which serializes the assembly
asm.Create(new String[] { AssemblyFileTextBox.Text });
udf = new UserDefinedFunction(db, "StringToInt32");
udf.TextMode = false;
udf.ImplementationType = ImplementationType.SqlClr;
udf.AssemblyName = "UtilityConversion";
udf.ClassName = "Microsoft.Samples.SqlServer.Conversions";
udf.MethodName = "StringToInt32";
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.DataType = DataType.Int;
parm = new UserDefinedFunctionParameter(udf, "@Input");
udf.Parameters.Add(parm);
parm.DataType = DataType.NVarChar(255);
udf.Create();
ShowAssemblies(true);
// Select the assembly just added
AssemblyListViewItem = AssembliesListView.FindItemWithText(
asm.Name);
AssemblyListViewItem.Selected = true;
AssemblyListViewItem.EnsureVisible();
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
/// <summary>
/// Helper function to register wrapped udf.
/// </summary>
/// <param name="name">Name of the udf</param>
/// <param name="func">Wrapped UDF function</param>
/// <param name="evalType">The EvalType of the function</param>
/// <param name="returnType">The return type of the function in JSON format</param>
private void Register(string name, Delegate func, UdfUtils.PythonEvalType evalType, string returnType)
{
byte[] command = CommandSerDe.Serialize(
func,
CommandSerDe.SerializedMode.Row,
CommandSerDe.SerializedMode.Row);
UserDefinedFunction udf = UserDefinedFunction.Create(
_jvmObject.Jvm,
name,
command,
evalType,
returnType);
_jvmObject.Invoke("registerPython", name, udf);
}
/// <summary>
/// Helper function to register wrapped udf.
/// </summary>
/// <typeparam name="TResult">Return type of the udf</typeparam>
/// <param name="name">Name of the udf</param>
/// <param name="func">Wrapped UDF function</param>
/// <param name="evalType">The EvalType of the function.</param>
internal void Register <TResult>(string name, Delegate func, UdfUtils.PythonEvalType evalType)
{
byte[] command = CommandSerDe.Serialize(
func,
CommandSerDe.SerializedMode.Row,
CommandSerDe.SerializedMode.Row);
var udf = UserDefinedFunction.Create(
_jvmObject.Jvm,
name,
command,
evalType,
UdfUtils.GetReturnType(typeof(TResult)));
_jvmObject.Invoke("registerPython", name, udf);
}
public static void Go()
{
var server = new Server(".");
Database database;
if (server.Databases.Contains("Test_Randal_Sql") == false)
{
database = new Database(server, "Test_Randal_Sql");
database.Create();
}
else
{
database = server.Databases["Test_Randal_Sql"];
}
if (database.StoredProcedures.Contains("mySp") == false)
{
var sp = new StoredProcedure(database, "mySp");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
sp.TextBody = "return -1";
sp.Create();
}
if (database.UserDefinedFunctions.Contains("myFunc") == false)
{
var func = new UserDefinedFunction(database, "myFunc");
func.TextMode = false;
func.ExecutionContext = ExecutionContext.Caller;
func.FunctionType = UserDefinedFunctionType.Scalar;
func.ImplementationType = ImplementationType.TransactSql;
func.DataType = DataType.Int;
func.TextBody = "begin return(-1); end";
func.Create();
}
if (database.Views.Contains("myView") == false)
{
var view = new View(database, "myView");
view.TextMode = false;
view.AnsiNullsStatus = false;
view.TextBody = "select 42 [AnswerToEverything]";
view.Create();
}
}
public void CreateUdfFunction(string name, UserDefinedFunctionType functionType, DataType dataType, string body, params DbParameter[] parameters)
{
var function = new UserDefinedFunction(SmoDatabase, name);
function.TextMode = false;
function.IsSchemaBound = true;
function.FunctionType = functionType;
foreach (var parameter in parameters)
{
var fParameter = new UserDefinedFunctionParameter(function, parameter.Name, parameter.DataType);
function.Parameters.Add(fParameter);
}
function.DataType = dataType;
function.TextBody = body;
function.Create();
}
public static void Go()
{
var server = new Server(".");
Database database;
if (server.Databases.Contains("Test_Randal_Sql") == false)
{
database = new Database(server, "Test_Randal_Sql");
database.Create();
}
else
database = server.Databases["Test_Randal_Sql"];
if(database.StoredProcedures.Contains("mySp") == false)
{
var sp = new StoredProcedure(database, "mySp");
sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;
sp.TextBody = "return -1";
sp.Create();
}
if (database.UserDefinedFunctions.Contains("myFunc") == false)
{
var func = new UserDefinedFunction(database, "myFunc");
func.TextMode = false;
func.ExecutionContext = ExecutionContext.Caller;
func.FunctionType = UserDefinedFunctionType.Scalar;
func.ImplementationType = ImplementationType.TransactSql;
func.DataType = DataType.Int;
func.TextBody = "begin return(-1); end";
func.Create();
}
if(database.Views.Contains("myView") == false)
{
var view = new View(database, "myView");
view.TextMode = false;
view.AnsiNullsStatus = false;
view.TextBody = "select 42 [AnswerToEverything]";
view.Create();
}
}
public static bool SwitchToMo(
this UserDefinedFunction self,
Database inMemDatabase,
ref string error,
ILog logger)
{
var retValue = false;
if (self.IsSystemObject)
{
return(true);
}
if (inMemDatabase.UserDefinedFunctions.Contains(self.Name, self.Schema))
{
logger.Log("\t" + "Already exists", self.FName());
return(true);
}
logger.Log("UDF", self.FName());
var newsp = new UserDefinedFunction(inMemDatabase, self.Name, self.Schema);
newsp.CopyPropertiesFrom(self);
try
{
newsp.Create();
logger.Log("OK", self.Name);
retValue = true;
}
catch (Exception ex)
{
logger.Log("Error", self.FName());
error = string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException)
.Select(ex1 => ex1.Message));
}
newsp = null;
return(retValue);
}
public void GrantAccessToSchemaObjects()
{
var helper = new TestHelper();
try
{
var schema = helper.GetSchema();
//schema.Owner = helper.GetUser().Name;
//schema.Alter();
var table = new Table(helper.GetDatabase(), "Table1", schema.Name);
table.Columns.Add(new Column(table, "Col1", DataType.Int));
table.Columns.Add(new Column(table, "Col2", DataType.NVarCharMax));
table.Create();
helper.AddCleanup(table);
var view = new View(helper.GetDatabase(), "View1", schema.Name)
{
TextMode = false,
TextBody = String.Format("SELECT Col1, Col2 FROM [{0}].[{1}]", table.Schema, table.Name)
};
//view.TextHeader = String.Format("CREATE VIEW [{0}].[{1}] AS", view.Schema, view.Name);
view.Create();
helper.AddCleanup(view);
var scalarTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "ScalarTsqlFunction", schema.Name)
{
TextMode = false,
DataType = DataType.DateTime,
ExecutionContext = ExecutionContext.Caller,
FunctionType = UserDefinedFunctionType.Scalar,
ImplementationType = ImplementationType.TransactSql,
TextBody = "BEGIN RETURN GETDATE() END"
};
scalarTsqlFn.Create();
helper.AddCleanup(scalarTsqlFn);
var inlineTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "InlineTsqlFunction", schema.Name)
{
TextMode = false,
ExecutionContext = ExecutionContext.Caller,
FunctionType = UserDefinedFunctionType.Inline,
ImplementationType = ImplementationType.TransactSql,
TextBody = String.Format("RETURN SELECT * FROM [{0}].[{1}]", view.Schema, view.Name)
};
inlineTsqlFn.Create();
helper.AddCleanup(inlineTsqlFn);
// TODO: Create table valued function
// TODO: Create Clr scalar func
// TODO: Create Clr inline func (Exists?)
// TODO: Create Clr table valued func
// TODO: Create Clr Aggregate
var proc = new StoredProcedure(helper.GetDatabase(), "sproc1", schema.Name)
{
TextMode = false,
AnsiNullsStatus = false,
QuotedIdentifierStatus = false,
TextBody = String.Format("SELECT * FROM [{0}].[{1}]()", inlineTsqlFn.Schema, inlineTsqlFn.Name)
};
proc.Create();
helper.AddCleanup(proc);
// TODO: Create Clr Sproc
// TODO: Create Constraint
// TODO: Create Queue
// TODO: Create Statistic
// TODO: Create Synonym
var user = helper.GetUser();
var permissable = new IObjectPermission[]
{
table,
view,
scalarTsqlFn,
inlineTsqlFn,
proc,
};
permissable.Do(tg => tg.GrantAll(user.Name));
permissable.Do(tg => tg.DenyAll(user.Name));
permissable.Do(tg => tg.RevokeAll(user.Name));
// change all owners
table.Owner = user.Name;
table.Alter();
view.Owner = user.Name;
view.Alter();
scalarTsqlFn.Owner = user.Name;
scalarTsqlFn.Alter();
inlineTsqlFn.Owner = user.Name;
inlineTsqlFn.Alter();
proc.Owner = user.Name;
proc.Alter();
}
finally
{
helper.Cleanup();
}
}
private void AddAssemblyButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
SqlAssembly asm;
UserDefinedFunction udf;
UserDefinedFunctionParameter parm;
ListViewItem AssemblyListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get selected database
db = (Database)DatabasesComboBox.SelectedItem;
asm = new SqlAssembly(db, "UtilityConversion");
asm.Owner = "dbo";
asm.AssemblySecurityLevel = AssemblySecurityLevel.Safe;
// This allows the assembly to be on a different server from SQL Server
// Use string array version which serializes the assembly
asm.Create(new String[] { AssemblyFileTextBox.Text });
udf = new UserDefinedFunction(db, "StringToInt32");
udf.TextMode = false;
udf.ImplementationType = ImplementationType.SqlClr;
udf.AssemblyName = "UtilityConversion";
udf.ClassName = "Microsoft.Samples.SqlServer.Conversions";
udf.MethodName = "StringToInt32";
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.DataType = DataType.Int;
parm = new UserDefinedFunctionParameter(udf, "@Input");
udf.Parameters.Add(parm);
parm.DataType = DataType.NVarChar(255);
udf.Create();
ShowAssemblies(true);
// Select the assembly just added
AssemblyListViewItem = AssembliesListView.FindItemWithText(
asm.Name);
AssemblyListViewItem.Selected = true;
AssemblyListViewItem.EnsureVisible();
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
