Presto: Create new columns with some default values

My data does not have columns of label and source, I want to add new columns for label and source in the data. However, the values are fixed and it doesn't matter which row gets which value.

I want to add column label = ('A', 'B', 'C') and source = ('X', 'Y', 'Z')

The data already has over 500 rows, I just want new columns to arbitrarily take any of these 3 values. Is there a way to do it?

1 answer

  • answered 2019-10-23 07:18 Piotr Findeisen

    As I understand you want to read your data, projecting additional label and source columns, randomly.

    You can do this by adding a projection using random like

    • ARRAY['A', 'B', 'C'][1 + random(3)] AS label
    • same for source

    Example:

    presto:default> SELECT *, ARRAY['A', 'B', 'C'][1 + random(3)] AS label
                 -> FROM (VALUES 1,2,3,4) t(data);
     data | label
    ------+-------
        1 | C
        2 | A
        3 | B
        4 | B
    (4 rows)
    

    (tested on Presto 322)