Search code examples
stringprestocross-join

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?

Solution

  • 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)