class UDTFRegistration extends Logging

Provides methods to register a UDTF (user-defined table function) in the Snowflake database.

Session.udtf returns an object of this class.

To register an UDTF, you must:

  1. Define a UDTF class.
  2. Create an instance of that class, and register that instance as a UDTF.

The next sections describe these steps in more detail.

Defining the UDTF Class

Define a class that inherits from one of the UDTF[N] classes (e.g. UDTF0 , UDTF1 , etc.), where n specifies the number of input arguments for your UDTF. For example, if your UDTF passes in 3 input arguments, extend the UDTF3 class.

In your class, override the following three methods:

  • process() , which is called once for each row in the input partition.
  • endPartition() , which is called once for each partition after all rows have been passed to process() .
  • outputSchema() , which returns a types.StructType object that describes the schema for the returned rows.

When a UDTF is called, the rows are grouped into partitions before they are passed to the UDTF:

  • If the statement that calls the UDTF specifies the PARTITION clause (explicit partitions), that clause determines how the rows are partitioned.
  • If the statement does not specify the PARTITION clause (implicit partitions), Snowflake determines how best to partition the rows.

For an explanation of partitions, see Table Functions and Partitions

Defining the process() Method

This method is invoked once for each row in the input partition.

The arguments passed to the registered UDTF are passed to process() . For each argument passed to the UDTF, you must have a corresponding argument in the signature of the process() method. Make sure that the type of the argument in the process() method matches the Snowflake data type of the corresponding argument in the UDTF.

Snowflake supports the following data types for the parameters for a UDTF:

SQL Type

Scala Type

Notes

NUMBER

Short or Option[Short]

Supported

NUMBER

Int or Option[Int]

Supported

NUMBER

Long or Option[Long]

Supported

FLOAT

Float or Option[Float]

Supported

DOUBLE

Double or Option[Double]

Supported

NUMBER

java.math.BigDecimal

Supported

VARCHAR

String or java.lang.String

Supported

BOOL

Boolean or Option[Boolean]

Supported

DATE

java.sql.Date

Supported

TIMESTAMP

java.sql.Timestamp

Supported

BINARY

Array[Byte]

Supported

ARRAY

Array[String] or Array[Variant]

Supported array of type Array[String] or Array[Variant]

OBJECT

Map[String, String] or Map[String, Variant]

Supported mutable map of type scala.collection.mutable.Map[String, String] or scala.collection.mutable.Map[String, Variant]

VARIANT

com.snowflake.snowpark.types.Variant

Supported

Defining the endPartition() Method

This method is invoked once for each partition, after all rows in that partition have been passed to the process() method.

You can use this method to generate output rows, based on any state information that you aggregate in the process() method.

Defining the outputSchema() Method

In this method, define the output schema for the rows returned by the process() and endPartition() methods.

Construct and return a types.StructType object that uses an Array of types.StructField objects to specify the Snowflake data type of each field in a returned row.

Snowflake supports the following DataTypes for the output schema for a UDTF:

DataType

SQL Type

Notes

BooleanType

Boolean

Supported

ShortType

NUMBER

Supported

IntegerType

NUMBER

Supported

LongType

NUMBER

Supported

DecimalType

NUMBER

Supported

FloatType

FLOAT

Supported

DoubleType

DOUBLE

Supported

StringType

VARCHAR

Supported

BinaryType

BINARY

Supported

TimeType

TIME

Supported

DateType

DATE

Supported

TimestampType

TIMESTAMP

Supported

VariantType

VARIANT

Supported

ArrayType(StringType)

ARRAY

Supported

ArrayType(VariantType)

ARRAY

Supported

MapType(StringType, StringType)

OBJECT

Supported

MapType(StringType, VariantType)

OBJECT

Supported

Example of a UDTF Class

The following is an example of a UDTF class that generates a range of rows.

The UDTF passes in 2 arguments, so the class extends UDTF2 .

The arguments start and count specify the starting number for the row and the number of rows to generate.

class MyRangeUdtf extends UDTF2[Int, Int] {
    override def process(start: Int, count: Int): Iterable[Row] =
        (start until (start + count)).map(Row(_))
    override def endPartition(): Iterable[Row] = Array.empty[Row]
    override def outputSchema(): StructType = StructType(StructField("C1", IntegerType))
}

Registering the UDTF

Next, create an instance of the new class, and register the class by calling one of the UDTFRegistration methods. You can register a temporary or permanent UDTF by name. If you don't need to call the UDTF by name, you can register an anonymous UDTF.

Registering a Temporary UDTF By Name

To register a temporary UDTF by name, call registerTemporary , passing in a name for the UDTF and an instance of the UDTF class. For example:

// Use the MyRangeUdtf defined in previous example.
val tableFunction = session.udtf.registerTemporary("myUdtf", new MyRangeUdtf())
session.tableFunction(tableFunction, lit(10), lit(5)).show

Registering a Permanent UDTF By Name

If you need to use the UDTF in subsequent sessions, register a permanent UDTF.

When registering a permanent UDTF, you must specify a stage where the registration method will upload the JAR files for the UDTF and its dependencies. For example:

val tableFunction = session.udtf.registerPermanent("myUdtf", new MyRangeUdtf(), "@myStage")
session.tableFunction(tableFunction, lit(10), lit(5)).show

Registering an Anonymous Temporary UDTF

If you do not need to refer to a UDTF by name, use UDTF) to create an anonymous UDTF instead.

Calling a UDTF

The methods that register a UDTF return a TableFunction object, which you can use in Session.tableFunction .

val tableFunction = session.udtf.registerTemporary("myUdtf", new MyRangeUdtf())
session.tableFunction(tableFunction, lit(10), lit(5)).show
Since

1.2.0

Linear Supertypes
Logging , AnyRef , Any
Ordering
  1. Alphabetic
  2. By Inheritance
Inherited
  1. UDTFRegistration
  2. Logging
  3. AnyRef
  4. Any
  1. Hide All
  2. Show All
Visibility
  1. Public
  2. All

Instance Constructors

  1. new UDTFRegistration ( session: Session )

Value Members

  1. final def != ( arg0: Any ) : Boolean
    Definition Classes
    AnyRef → Any
  2. final def ## () : Int
    Definition Classes
    AnyRef → Any
  3. final def == ( arg0: Any ) : Boolean
    Definition Classes
    AnyRef → Any
  4. final def asInstanceOf [ T0 ] : T0
    Definition Classes
    Any
  5. def clone () : AnyRef
    Attributes
    protected[ lang ]
    Definition Classes
    AnyRef
    Annotations
    @throws ( ... ) @native () @HotSpotIntrinsicCandidate ()
  6. final def eq ( arg0: AnyRef ) : Boolean
    Definition Classes
    AnyRef
  7. def equals ( arg0: Any ) : Boolean
    Definition Classes
    AnyRef → Any
  8. final def getClass () : Class [_]
    Definition Classes
    AnyRef → Any
    Annotations
    @native () @HotSpotIntrinsicCandidate ()
  9. def hashCode () : Int
    Definition Classes
    AnyRef → Any
    Annotations
    @native () @HotSpotIntrinsicCandidate ()
  10. final def isInstanceOf [ T0 ] : Boolean
    Definition Classes
    Any
  11. def log () : Logger
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  12. def logDebug ( msg: String , throwable: Throwable ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  13. def logDebug ( msg: String ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  14. def logError ( msg: String , throwable: Throwable ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  15. def logError ( msg: String ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  16. def logInfo ( msg: String , throwable: Throwable ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  17. def logInfo ( msg: String ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  18. def logTrace ( msg: String , throwable: Throwable ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  19. def logTrace ( msg: String ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  20. def logWarning ( msg: String , throwable: Throwable ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  21. def logWarning ( msg: String ) : Unit
    Attributes
    protected[ internal ]
    Definition Classes
    Logging
  22. final def ne ( arg0: AnyRef ) : Boolean
    Definition Classes
    AnyRef
  23. final def notify () : Unit
    Definition Classes
    AnyRef
    Annotations
    @native () @HotSpotIntrinsicCandidate ()
  24. final def notifyAll () : Unit
    Definition Classes
    AnyRef
    Annotations
    @native () @HotSpotIntrinsicCandidate ()
  25. def registerPermanent ( funcName: String , udtf: UDTF , stageLocation: String ) : TableFunction

    Registers an UDTF instance as a Snowflake Java UDTF.

    Registers an UDTF instance as a Snowflake Java UDTF.

    The function uploads the JAR files that the UDTF depends upon to the specified stage. Each JAR file is uploaded to a subdirectory named after the MD5 checksum for the file.

    If you register multiple UDTFs and specify the same stage location, any dependent JAR files used by those functions will only be uploaded once. The JAR file for the UDTF code itself will be uploaded to a subdirectory named after the UDTF.

    funcName

    The UDTF function name

    udtf

    The UDTF instance to be registered.

    stageLocation

    Stage location where the JAR files for the UDTF and its and its dependencies should be uploaded

    Since

    1.2.0

  26. def registerTemporary ( funcName: String , udtf: UDTF ) : TableFunction

    Registers an UDTF instance as a temporary Snowflake Java UDTF that you plan to use in the current session.

    Registers an UDTF instance as a temporary Snowflake Java UDTF that you plan to use in the current session.

    funcName

    The UDTF function name

    udtf

    The UDTF instance to be registered

    Since

    1.2.0

  27. def registerTemporary ( udtf: UDTF ) : TableFunction

    Registers an UDTF instance as a temporary anonymous UDTF that is scoped to this session.

    Registers an UDTF instance as a temporary anonymous UDTF that is scoped to this session.

    udtf

    The UDTF instance to be registered

    Since

    1.2.0

  28. final def synchronized [ T0 ] ( arg0: ⇒ T0 ) : T0
    Definition Classes
    AnyRef
  29. def toString () : String
    Definition Classes
    AnyRef → Any
  30. final def wait ( arg0: Long , arg1: Int ) : Unit
    Definition Classes
    AnyRef
    Annotations
    @throws ( ... )
  31. final def wait ( arg0: Long ) : Unit
    Definition Classes
    AnyRef
    Annotations
    @throws ( ... ) @native ()
  32. final def wait () : Unit
    Definition Classes
    AnyRef
    Annotations
    @throws ( ... )

Deprecated Value Members

  1. def finalize () : Unit
    Attributes
    protected[ lang ]
    Definition Classes
    AnyRef
    Annotations
    @throws ( classOf[java.lang.Throwable] ) @Deprecated
    Deprecated

Inherited from Logging

Inherited from AnyRef

Inherited from Any

Ungrouped