amazon redshift - universo - Desplazamiento al rojo Convertir valores delimitados por comas en filas

ley de hubble universo estatico

Question

Me pregunto cómo convertir valores delimitados por comas en filas en Redshift. Me temo que mi propia solución no es óptima. Por favor aconséjame. Tengo tabla con una de las columnas con valores separados por coma. Por ejemplo:

Yo tengo:

user_id|user_name|user_action
-----------------------------
1      | Shone   | start,stop,cancell...

Me gustaría ver

user_id|user_name|parsed_action 
------------------------------- 
1      | Shone   | start        
1      | Shone   | stop         
1      | Shone   | cancell      
....

Answer 1

Aquí está mi igualmente terrible respuesta.

Tengo una tabla de users y luego una tabla de events con una columna que es solo una cadena de usuarios delimitada por comas en dicho evento. p.ej

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

En este caso, usé las funciones LIKE y comodín para construir una nueva tabla que represente cada borde de usuario de evento.

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

No es bonito, pero lo incluyo en una cláusula WITH para no tener que ejecutarlo más de una vez por consulta. De todos modos, es probable que solo construya un ETL para crear esa tabla todas las noches.

Además, esto solo funciona si tiene una segunda tabla que tiene una fila por posibilidad única. Si no, puede hacer LISTAGG para obtener una sola celda con todos sus valores, exportarlo a un CSV y volver a cargarlo como una tabla para ayudar.

Como dije: una solución terrible, no buena.

Answer 2

Llegué tarde a la fiesta, pero tengo algo funcionando (aunque muy lento)

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) 

Gracias a la respuesta de Bob Baxley por su inspiración.

Answer 3

Otra idea es transformar su cadena CSV en JSON primero, seguido de extracto JSON, en las siguientes líneas:

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

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

Donde "números" es la tabla de la primera respuesta. La ventaja de este enfoque es la capacidad de usar la funcionalidad JSON incorporada.

Answer 4

Puede intentar copiar el comando para copiar su archivo en tablas de desplazamiento al rojo

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 '',''

Puedes usar delimitador '','' opción.

Para más detalles de las opciones de comando de copia puede visitar esta página

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

Answer 5

Puede obtener el resultado esperado con la siguiente consulta. Estoy usando "UNION ALL" para convertir una columna en fila.

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

Answer 6

Solo mejora para la respuesta anterior https://.com/a/31998832/1265306

Está generando una tabla de números utilizando el siguiente https://discourse.looker.com/t/generating-a-numbers-table-in-mysql-and-redshift/482

SELECT 
  p0.n 
  + p1.n*2 
  + p2.n * POWER(2,2) 
  + p3.n * POWER(2,3)
  + p4.n * POWER(2,4)
  + p5.n * POWER(2,5)
  + p6.n * POWER(2,6)
  + p7.n * POWER(2,7) 
  as number  
INTO numbers
FROM  
  (SELECT 0 as n UNION SELECT 1) p0,  
  (SELECT 0 as n UNION SELECT 1) p1,  
  (SELECT 0 as n UNION SELECT 1) p2, 
  (SELECT 0 as n UNION SELECT 1) p3,
  (SELECT 0 as n UNION SELECT 1) p4,
  (SELECT 0 as n UNION SELECT 1) p5,
  (SELECT 0 as n UNION SELECT 1) p6,
  (SELECT 0 as n UNION SELECT 1) p7
ORDER BY 1
LIMIT 100

"ORDER BY" está ahí solo en caso de que quiera pegarlo sin la cláusula INTO y ver los resultados

Answer 7

Una pequeña mejora con respecto a la respuesta existente es usar una segunda tabla de "números" que enumere todas las longitudes posibles de la lista y luego usar una cross join para hacer que la consulta sea más compacta.

Redshift no tiene un método directo para crear una tabla de números que yo sepa, pero podemos usar un poco de hack desde https://www.periscope.io/blog/generate-series-in-redshift-and-mysql.html para crear uno usando números de fila.

Específicamente, si asumimos que el número de filas en cmd_logs es mayor que el número máximo de comas en la columna user_action , podemos crear una tabla de números contando filas. Para comenzar, supongamos que hay como máximo 99 comas en la columna user_action :

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

Si queremos obtener fantasía, podemos calcular el número de comas de la tabla cmd_logs para crear un conjunto más preciso de filas en 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;

Una vez que hay una tabla de numbers , podemos hacer:

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) != '''';