Блог

Главная - Вопросы по программированию - Какой наиболее эффективный способ заполнить таблицу, содержащую изменения в наборе данных, используя стандартный SQL?

Какой наиболее эффективный способ заполнить таблицу, содержащую изменения в наборе данных, используя стандартный SQL?

#sql #database #snowflake-cloud-data-platform

#sql #База данных #snowflake-cloud-data-platform

Вопрос:

У меня есть таблица (таблица a), которая содержит следующие поля:

 customer_id  (varchar)
customer_status (varchar)
segment1_flag  (smallint)
segment2_flag  (smallint)
run_time (datetime)

Эта таблица очищается и обновляется каждый день. Копия данных хранится в таблице истории (таблица b) со следующими полями

 history_ID (varchar)
run_time (datetime)
customer_id  (varchar)
customer_status (varchar)
segment1_flag  (smallint)
segment2_flag  (smallint)

Затем у меня есть таблица (таблица c), которая содержит изменения между обновлением таблицы (т. е. Если значение поля изменилось между текущей загрузкой и последней загрузкой). Таблица C содержит следующие поля.

  customer_id  (varchar)
 customer_status (varchar)
 segment1_flag  (smallint)
 segment2_flag  (smallint)
 run_time (datetime)

Этот SQL я запускаю для обновления таблицы c

 SELECT  
    sha2(a.CUSTOMER_ID,256),
    A.CUSTOMER_STATUS,
    A.SEGMENT1_FLAG,
    A.SEGMENT2_FLAG,
    CURRENT_TIMESTAMP()      
FROM TABLE_A  as a
LEFT JOIN TABLE C as b
ON a.CUSTOMER_ID = b.CUSTOMER_ID    
WHERE 
B.CUSTOMER_ID IS NULL
OR((
A.CUSTOMER_ID <> B.CUSTOMER_ID OR
A.CUSTOMER_STATUS <> B.CUSTOMER_STATUS OR
A.SEGMENT1_FLAG <> B.SEGMENT1_FLAG OR
A.SEGMENT2_FLAG <> B.SEGMENT2_FLAG) 
AND TO_DATE(B.RUN_DATE) = DATEADD(Day,-1,TO_DATE(A.RUN_DATE)))

Мне нужно вести историю таблицы a, и мне нужна таблица, которая показывает изменения между обновлениями таблицы. Это самый эффективный способ сделать это (3 таблицы)? Или есть более эффективный метод? Для контекста в таблице около 30 полей (приведенное выше сокращенно), и я запускаю это в Snowflake, но это должно быть применимо для других хранилищ.

Заранее спасибо, Ник

Комментарии:

1. Примеры данных и желаемые результаты прояснили бы вопрос.

2. зачем вам нужна таблица C, если у вас уже есть таблица истории? Разве вы не можете запросить таблицу истории, чтобы определить изменения? Также EXCEPT или MINUS можно сравнить все столбцы сразу без необходимости выполнять цепочку из 30 <> проверок.

3. Мне тоже интересно, зачем вам нужен C, когда его данные могут быть определены из A и B. В любом случае, поскольку вы спрашиваете о стандарте SQL: в стандартном SQL вы, вероятно, использовали бы триггер для записи C каждый раз, когда в A записывается. Если вы не хотите этого, но хотите получить дельту позже, вы бы использовали MERGE стандартный SQL.

4. @GabrielDurac Требуется таблица C, потому что у меня есть скрипт, которому нужно прочитать таблицу и перенести данные в другое место. Скрипт не может выполнить запрос, он может только прочитать таблицу c. Желаемый результат — это то, что я в настоящее время имею в таблице c — я просто ищу способы сделать это более эффективным. т. Е. Могу ли я сделать все это только с помощью таблицы B / таблицы C?

Ответ №1:

В Snowflake, в зависимости от того, как долго вам нужно хранить историю, вы могли бы просто использовать отслеживание изменений и перемещение во времени в Snowflake. В версии Enterprise или выше вы можете выполнять это для истории продолжительностью до 90 дней. Если вам требуется больше времени, вам следует использовать клоны с нулевым копированием для хранения старых данных.

https://docs.snowflake.com/en/sql-reference/constructs/changes.html

https://docs.snowflake.com/en/user-guide/data-time-travel.html

https://docs.snowflake.com/en/sql-reference/sql/create-clone.html

Ответ №2:

В дополнение к вашему процессу, просто убедитесь, что вы создали таблицу a как временную таблицу. Поскольку эта таблица проходит полный процесс обновления. В противном случае это увеличит для вас затраты на отказоустойчивость.