Redshift. Преобразование значений, разделенных запятыми в строки

Ответ 1

Небольшое улучшение по сравнению с существующим ответом заключается в использовании второй таблицы "числа", которая перечисляет все возможные длины списков, а затем использует cross join, чтобы сделать запрос более компактным.

Redshift не имеет прямого метода для создания таблицы чисел, о которой я знаю, но мы можем использовать немного взлома из https://www.periscope.io/blog/generate-series-in-redshift-and-mysql.html для создания один из которых использует номера строк.

В частности, если предположить, что число строк в cmd_logs больше максимального числа запятых в столбце user_action, мы можем создать таблицу чисел, подсчитывая строки. Для начала предположим, что в столбце user_action не более 99 запятых:

select 
  row_number() over (order by true) as n
into numbers
from cmd_logs
limit 100;

Если мы хотим получить фантазию, мы можем вычислить количество запятых из таблицы cmd_logs, чтобы создать более точный набор строк в numbers:

select
  n::int
into numbers
from
  (select 
      row_number() over (order by true) as n
   from cmd_logs)
cross join
  (select 
      max(regexp_count(user_action, '[,]')) as max_num 
   from cmd_logs)
where
  n <= max_num + 1;

Когда есть таблица numbers, мы можем сделать:

select
  user_id, 
  user_name, 
  split_part(user_action,',',n) as parsed_action 
from
  cmd_logs
cross join
  numbers
where
  split_part(user_action,',',n) is not null
  and split_part(user_action,',',n) != '';

Ответ 2

Еще одна идея - сначала преобразовать вашу строку CSV в JSON, а затем извлечь JSON в следующих строках:

... '["' || replace( user_action, '.', '", "' ) || '"]' AS replaced

... JSON_EXTRACT_ARRAY_ELEMENT_TEXT(replaced, numbers.i) AS parsed_action

Где "числа" - таблица из первого ответа. Преимуществом этого подхода является возможность использования встроенных функций JSON.

Ответ 3

Вы можете получить ожидаемый результат со следующим запросом. Я использую "UNION ALL" для преобразования столбца в строку.

select user_id, user_name, split_part(user_action,',',1) as parsed_action from cmd_logs
union all
select user_id, user_name, split_part(user_action,',',2) as parsed_action from cmd_logs
union all
select user_id, user_name, split_part(user_action,',',3) as parsed_action from cmd_logs

Ответ 4

Вот мой не менее страшный ответ.

У меня есть таблица users, а затем таблица events с столбцом, который представляет собой только строку пользователей с разделителями-запятыми в указанном событии. например,

event_id | user_ids
1        | 5,18,25,99,105

В этом случае я использовал функции LIKE и подстановочные знаки для создания новой таблицы, которая представляет каждый край пользовательского интерфейса.

SELECT e.event_id, u.id as user_id
FROM events e
LEFT JOIN users u ON e.user_ids like '%' || u.id || '%'

Это не очень, но я бросаю его в предложение WITH, так что мне не нужно запускать его более одного раза за запрос. Я, скорее всего, просто создам ETL, чтобы каждую неделю создавать эту таблицу.

Кроме того, это работает только если у вас есть вторая таблица, у которой есть одна строка за уникальную возможность. Если нет, вы можете сделать LISTAGG, чтобы получить одну ячейку со всеми вашими значениями, экспортировать ее в CSV и повторно загрузить в качестве таблицы.

Как я сказал: ужасное, нехорошее решение.

Ответ 5

Поздно к партии, но у меня что-то работает (хотя и очень медленно)

with nums as (select n::int n
from
  (select 
      row_number() over (order by true) as n
   from table_with_enough_rows_to_cover_range)
cross join
  (select 
      max(json_array_length(json_column)) as max_num 
   from table_with_json_column )
where
  n <= max_num + 1)
select *, json_extract_array_element_text(json_column,nums.n-1) parsed_json
from  nums, table_with_json_column
where json_extract_array_element_text(json_column,nums.n-1) != ''
and nums.n <= json_array_length(json_column) 

Благодаря ответ Боба Бэкли для вдохновения

Ответ 6

Вы можете попробовать скопировать команду, чтобы скопировать файл в таблицы красного смещения

copy table_name from 's3://mybucket/myfolder/my.csv' CREDENTIALS 'aws_access_key_id=my_aws_acc_key;aws_secret_access_key=my_aws_sec_key' delimiter ','

Вы можете использовать опцию разделитель,.

Для получения дополнительной информации о параметрах команды копирования вы можете посетить эту страницу

http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html