APPLY_ONE_HOT_ENCODER

A user-defined transform function (UDTF) that loads the one hot encoder model and writes out a table that contains the encoded columns.

Syntax

APPLY_ONE_HOT_ENCODER( input‑columns 
                    USING PARAMETERS model_name='model‑name'
                                     [, drop_first='is‑first']
                                     [, ignore_null='ignore']
                                     [, separator='separator‑character']
                                     [, column_naming='name‑output']
                                     [, null_column_name='null‑column‑name'] )

Arguments

input‑columns Comma-separated list of columns to use from the input table/view, or asterisk (*) to select all columns.

Parameter Settings

Parameter name Set to…
model_name Name of the model (case-insensitive) , stores the categories and their corresponding levels.
drop_first

Boolean value, one of the following:

  • true (default): Treat the first level of the categorical variable as the reference level.
  • false: Every level of the categorical variable has a corresponding column in the output view
ignore_null

Boolean value, one of the following:

  • true (default): NULL values set all corresponding one-hot binary columns to NULL.
  • false: NULL values in input‑columns are treated as a categorical level
separator

The separator character between the input variable name and the indicator variable level in the output table. Use NULL to avoid using any separator.

Default: Underscore (_)
column_naming

The output for column names, one of the following:

  • indices (default): Return integer indices as column names.
  • values: Return categorical levels as column names. APPLY_ONE_HOT_ENCODER returns an error if this setting results in duplicate column names.
  • values_relaxed: Return categorical levels as the column names. Duplicate column names are disambiguated by appending _n, where n is a zero-based integer index (_0, _1,…).

APPLY_ONE_HOT_ENCODER truncates input column names with more than 128 characters. Column names can contain special characters.
null_column_name For null values, the name of the indicator column.
Note: When a level not stored in the model appears in an input row, the columns corresponding to that categorical level in the output row are returned as NULL values.

Examples

=> SELECT APPLY_ONE_HOT_ENCODER(cyl USING PARAMETERS model_name='one_hot_encoder_model', 
drop_first='true', ignore_null='false') FROM mtcars;
cyl | cyl_1 | cyl_2
----+-------+-------
8   |     0 |     1
4   |     0 |     0
4   |     0 |     0
8   |     0 |     1
8   |     0 |     1
8   |     0 |     1
4   |     0 |     0
8   |     0 |     1
8   |     0 |     1
4   |     0 |     0
8   |     0 |     1
6   |     1 |     0
4   |     0 |     0
4   |     0 |     0
6   |     1 |     0
6   |     1 |     0
8   |     0 |     1
8   |     0 |     1
4   |     0 |     0
4   |     0 |     0
6   |     1 |     0
8   |     0 |     1
8   |     0 |     1
6   |     1 |     0
4   |     0 |     0
8   |     0 |     1
8   |     0 |     1
8   |     0 |     1
6   |     1 |     0
6   |     1 |     0
4   |     0 |     0
4   |     0 |     0
(32 rows)

See Also