Динамическое извлечение данных в Amazon Redshift

#sql #amazon-web-services #amazon-redshift

#sql #amazon-веб-сервисы #amazon-redshift

Вопрос:

Это пример данных в столбце. Я хочу динамически извлекать значения, связанные только с 5.

 '{"2113":5,"2112":5,"2114":4,"2511":5}'

Конечная структура должна состоять из 3 строк имен и значений?

Я пробовал использовать функцию извлечения JSON, но это не помогло. Спасибо

Конечный результат, который я хочу,

   value | Key
  2113     5
  2112     5
  2115     5

1. Пожалуйста, отредактируйте свой вопрос, чтобы показать результат, который вы хотели бы получить из этих входных данных.

2. Будут ли когда-либо только эти значения (2113, 2112, 2114, 2511) в JSON? Изменится ли количество желаемых выходных строк потенциально (например, только 1, или все 4, или, возможно, даже намного больше значений)? Похоже, это лучше всего делать в процессе ETL перед загрузкой данных в Redshift. Хотя, мне интересно, может ли подойти хранимая процедура?

3. Да, мы не знаем о значениях внутри массива, они будут увеличиваться на основе генерации нового элемента.

4. Пожалуйста, отредактируйте свой вопрос, чтобы показать результат, который вы хотели бы получить из этих входных данных.

5. Отредактировал вопрос для вывода.

Ответ №1:

Итак, что вам нужно сделать, это отменить назначение объекта json (иметь пару ключ-значение в строке). Отменить вложение в Readshift сложно. Нужна таблица последовательности, а затем выполнить CROSS JOIN с надлежащим условием фильтрации. Обычно удаление вложенности выполняется в массиве, и тогда это проще, поскольку индексы легко генерировать. Чтобы отменить сопоставление ключ-значение (объект JSON), нужно знать все ключи (Redshift не может этого сделать). Ваш пример удачен, поскольку ключи являются целыми числами, а их мощность относительно невелика.

Это набросанное решение. Пожалуйста, обратите внимание, что вам придется изменить способ создания таблицы последовательности:

 WITH input(json) AS (
    SELECT '{"2113":5,"2112":5,"2114":4,"2511":5}'::varchar
)
   , sequence(idx) AS (
    -- instead of the below you should use sequence table
    SELECT 2113
    UNION ALL
    SELECT 2112
    UNION ALL
    SELECT 2114
    UNION ALL
    SELECT 2511
    UNION ALL
    SELECT 2512
    UNION ALL
    SELECT 2513
    UNION ALL
    SELECT 2514
)
   , unnested(key, val) AS (
    SELECT idx::varchar                      as key,
           json_extract_path_text(json, key) as val
    FROM input
             CROSS JOIN sequence
    WHERE val IS NOT NULL
)
SELECT *
FROM unnested
WHERE val = 5


key  | val 
2113 | 5 
2112 | 5 
2511 | 5

как сгенерировать большую последовательность в Redshift:

 ...
sequence(idx) AS (
    SELECT row_number() OVER () 
    FROM arbitrary_table_having_enough_rows
    limit 10000 
)
...

Другой вариант — иметь специализированную таблицу последовательностей — здесь есть идея о том, как это сделать http://www.silota.com/docs/recipes/redshift-sequential-generate-series-numbers-time.html

1. Отличная идея!! но для завершения в Redshift при соединении с последовательностью требуется слишком много времени.

2. Получена ссылка на результат из вашего запроса. Спасибо.

Ответ №2:

Результат достигнут с использованием нескольких разделений.

 `SELECT  distinct split_part(split_part(replace(replace(replace(json_field,'{',''),'}',''),'"',''),',',i),': ',1) as value,` `split_part(split_part(replace(replace(replace(json_field,'{',''),'}',''),'"',''),',',i),':',2) as key FROM table
  JOIN schema.seq_1_to_100 as numbers
  ON i <=regexp_count(json_field,':') `