Блог

Сценарий: Медицинские записи, переданные правительству штата, для ввода которых требуется текстовый файл с разделителями каналов.

Задача: Выберите сотни значений из таблицы фактов и создайте широкий набор результатов для выгрузки (красное смещение) на диск.

То, что я пробовал до сих пор, — это SQL, который я хочу превратить в ПРЕДСТАВЛЕНИЕ.

 ;WITH 
CTE_patient_record AS 
(
 SELECT 
   record_id 
 FROM fact_patient_record
 WHERE update_date = <yesterday>
)
,CTE_patient_record_item AS
(
 SELECT 
    record_id
   ,record_item_name
   ,record_item_value
 FROM fact_patient_record_item fpri
 INNER JOIN CTE_patient_record cpr ON fpri.record_id = cpr.record_id
)
 

Обратите внимание, что fact_patient_record содержит 87 миллионов строк, а fact_patient_record_item содержит 97 миллионов строк.
Приведенный выше код выполняется за 2 секунды для 2 тестовых записей, а CTE_patient_record_item CTE содержит около 200 строк на запись, в общей сложности около 400.

Теперь создайте результирующий набор:

 ,CTE_result AS 
(
  SELECT 
     cpr.record_id
    ,cpri002.record_item_value AS diagnosis_1
    ,cpri003.record_item_value AS diagnosis_2
    ,cpri004.record_item_value AS medication_1
    ... 

  FROM CTE_patient_record cpr
  INNER JOIN CTE_patient_record_item cpri002 ON cpr.cpr.record_id = cpri002.cpr.record_id 
             AND cpri002.record_item_name = 'diagnosis_1'
  INNER JOIN CTE_patient_record_item cpri003 ON cpr.cpr.record_id = cpri003.cpr.record_id 
             AND cpri003.record_item_name = 'diagnosis_2'
  INNER JOIN CTE_patient_record_item cpri004 ON cpr.cpr.record_id = cpri004.cpr.record_id 
             AND cpri003.record_item_name = 'mediation_1'
  ...

) SELECT * FROM CTE_result
 

Результирующий набор выглядит следующим образом:

 record_id diagnosis_1 diagnosis_2 medication_1 ...
100001             09          9B          88X ...
 

…а затем я использую команду Reshift UNLOAD для записи на диск с разделителями каналов.

I am testing this on a full production sized environment but only for 2 test records.
Those 2 test records have about 200 items each.
Processing output is 2 rows 200 columns wide.

It takes 30 to 40 minutes to process just just the 2 records.

You might ask me why I am joining on the item name which is a string. Basically there is no item id, no integer, to join on. Long story.

Я ищу предложения о том, как повысить производительность. При наличии всего 2 записей от 30 до 40 минут недопустимы. Что произойдет, когда у меня будет 1000 записей?

Я также попытался сделать ПРЕДСТАВЛЕНИЕ МАТЕРИАЛИЗОВАННЫМ, однако для компиляции материализованного представления также требуется от 30 до 40 минут (что неудивительно).

Я не уверен, какой маршрут выбрать отсюда.

1. Хранимая процедура? У меня есть опыт работы с хранимыми процессами.
2. Создайте новые таблицы, чтобы я мог создавать целочисленные идентификаторы для объединения и индексирования? Однако мои менеджеры не любят «новый стол».
3. ?

Я мог бы просто остановиться на первых двух CTE, перенести данные на python и обработать с помощью фрейма данных pandas, что я успешно делал раньше, но было бы неплохо, если бы у меня был эффективный запрос, просто используйте выгрузку Redshift и покончите с этим.

Любая помощь будет признательна.

ОБНОВЛЕНИЕ: Большое спасибо Полу Коулсону и Биллу Вайнеру за то, что указали мне правильное направление! (Пол, я не могу поддержать ваш ответ, так как я здесь слишком новичок).

Использование (псевдокода):

 MAX(CASE WHEN t1.name = 'somename' THEN t1.value END ) AS name
...
FROM table1 t1
 

сокращено время выполнения с 30 минут до 30 секунд.
ПЛАН ОБЪЯСНЕНИЯ для исходного решения составляет 2700 строк, для нового решения с использованием условной агрегации-40 строк.
Спасибо, ребята.

Без дополнительной информации невозможно точно знать, что происходит, но то, что вы делаете, скорее всего, не идеально. План объяснения и время выполнения каждого шага очень помогли бы.

Что я подозреваю, так это то, что вы читаете таблицу строк 97M 200 раз. Это замедлит процесс, но не должно занять 40 минут. Поэтому я также подозреваю, что имя record_item_name не уникально для каждого значения record_id. Это приведет к репликации строк и может привести к многократному расширению набора данных. Также является ли record_id уникальным в fact_patient_record? Если нет, то это приведет к репликации строк. Если все это достаточно велико, чтобы вызвать значительный разлив и значительную сетевую трансляцию, ваше 40-минутное время выполнения очень вероятно.

Нет необходимости объединяться, когда все данные находятся в одной копии таблицы. @PhilCoulson прав в том, что может быть применена какая-то условная агрегация, и синтаксис decode() может сэкономить вам место, если вам не нравится регистр. Некоторые из вышеперечисленных проблем, которые могут повлиять на ваши объединения, также усложнят это объединение. Что вы ищете, если для каждой пары record_id и record_item_name существует несколько значений record_item_value? Я ожидаю, что у вас есть некоторое представление о том, что ваши данные содержат в вашем будущем.