To create from svm_iris an input table in the sparse format that the SparseSVMTrainer function requires, use the function Unpivot:
CREATE TABLE svm_iris_input DISTRIBUTE BY hash(ID) AS
SELECT id, species, attribute, value :: double AS value
FROM Unpivot (
ON svm_iris
ColsToUnpivot ('Sepal_Length', 'Sepal_Width', 'Petal_Length', 'Petal_Width')
ColsToAccumulate ('ID', 'Species')
) ORDER BY ID;
id | species | attribute | value |
---|---|---|---|
1 | setosa | sepal_length | 5.1 |
1 | setosa | sepal_width | 3.5 |
1 | setosa | petal_length | 1.4 |
1 | setosa | petal_width | 0.2 |
2 | setosa | sepal_length | 4.9 |
2 | setosa | sepal_width | 3.0 |
2 | setosa | petal_length | 1.4 |
2 | setosa | petal_width | 0.2 |
3 | setosa | sepal_length | 4.7 |
3 | setosa | sepal_width | 3.2 |
3 | setosa | petal_length | 1.3 |
3 | setosa | petal_width | 0.2 |
... | ... | ... | ... |