текст перекладу
Фото Родіона Куцаєва на Unsplash
Чи траплялося вам, що потрібно мати всю інформацію про запити в одному місці? Включаючи найбільш актуальні та найстаріші запити?
У цій статті ми розглянемо, як досягти цього оптимальним, масштабованим і підтримуваним способом. Якщо ви хочете дізнатися більше, продовжуйте читати (не забудьте поставити лайк і підписатися 🙂 ).
Основна ідея полягає в тому, щоб поєднати інформацію з ACCOUNT_USAGE (AU) та INFORMATION_SCHEMA (IS), що стосується запитів, в одному місці. AU містить дані за останні 365 днів, але з затримкою до 45 хвилин, а в IS є інформація за останні 7 днів.
Стратегія буде зосереджена на збереженні всієї інформації AU у нормалізованому вигляді і додаванні найбільш оновленої інформації з IS для підтримки.
Отже, наша основна мета — встановити чітку методологію для витягування найсвіжішої інформації до 45 хвилин з IS, бути динамічними, масштабованими та мати хорошу продуктивність.
Ми починаємо з основної проблеми: у вигляді історії запитів IS ми не можемо отримати більше 10 тис. рядків. Це проблема! Але ми її вирішимо 🙂
Наша основна мета — отримати правильний часовий інтервал для того, щоб динамічно сформувати запит і отримати всю найсвіжішу інформацію без пропусків.
Ідея полягає в тому, щоб сформувати кілька запитів до IS, враховуючи обмеження на кількість рядків у різних вікнах, використовуючи оптимальний часовий інтервал для нашої платформи даних.
В кінці статті ми оберемо весь код в процедуру для автоматичного вибору всієї інформації, що використовує її кожного разу, коли ми хочемо отримати ці дані, якщо ви хочете пропустити наступний розділ з поясненням, ви можете одразу перейти до нього. Але зараз ми збираємося зрозуміти основні терміни, які потрібні для досягнення цього автоматизаційного процесу.
Отримання оптимального часовго інтервалу та інших важливих термінів
Ми будемо слідувати крок за кроком, виконуючи всю функціональність, яку використовуємо для нашого автоматичного запиту, щоб зрозуміти, як ми його будуємо.
Ми повинні сканувати нашу історичну інформацію, щоб отримати ці інсайти та застосувати деякі прогностичні маржі, як ви побачите.
Перший крок: проаналізувати історичну інформацію по різних часових інтервалах, щоб отримати найкращий. Для кожного періоду часу до 45 хвилин (у секундах) ми будемо шукати максимальну кількість запитів, які були виконані протягом історії. Часовий інтервал, що перевищує максимальний, не має сенсу, оскільки затримка часу для AU не перевищить цей період.
Наприклад, процес буде шукати період у 1 секунду, скільки запитів було виконано (максимально) за всю історію. Потім ми зробимо те ж саме для періоду в кожні 2 секунди, скільки запитів було виконано (максимально) за всю історію. І так далі, шукаючи найкращу кількість запитів, яка відповідає нашому часовому інтервалу. Тому ми будемо використовувати це пізніше, щоб отримати інформацію для історії запитів в IS, щоб переконатися, що не буде пропущено жодної інформації.
Перед початком ми будемо використовувати два важливі параметри:
- queryadjustrate: Кількість запитів, яку ми хочемо скоригувати нашою маржею. Ми хочемо надати деяку маржу для неочікуваних збільшень. У цьому випадку ми будемо використовувати 7500 запитів для кожного зразка. Якщо буде 2500 запитів, що обробляються неочікувано, ми досягнемо цієї маржі. Ви можете налаштувати її ближче до 10k, якщо вважаєте, що навантаження на вашу платформу даних дуже стабільне, але це не рекомендується... закон Мерфі ж працює... ви знаєте 🙂
- timeframedelayinminutes: Максимальна затримка, що надається запитом AS. Це буде використовуватися як максимальний часовий інтервал для сканування кожного періоду.
текст перекладу
З 1 секунди, 2 секунди, 3 секунди, … до 2700 (45*60) секунд за період часу.
Скріншот частини інформації, отриманої із запиту для періоду часу в 1 секунду:
Кожну 1 секунду за всю історію будуть показані кількість виконаних запитів
Ось уривок для кожних 2 секунд (прокручуючи вниз) і так далі:
Ця інформація буде повторюватися до вікна періоду часу в 2700 секунд.
Тепер нам потрібно для кожного періоду часу отримати максимальну кількість виконаних запитів. Це дозволить нам перевірити, який найкращий часовий інтервал підходить для нашого параметра queryadjustrate (7500).
Ми виконаємо наступний запит для отримання всіх цих результатів:
Але тепер нам потрібно знайти, який найкращий часовий інтервал? Це буде перший часовий інтервал, який досягне параметра queryadjustrate (7500):
Але як ми можемо отримати цей період часу з усіх наших попередніх результатів? Застосовуючи наступні фільтри агрегації:
Ми бачимо, що для нашого облікового запису, аналізуючи поведінку виконання наших запитів, найкращим періодом часу в секундах є 413 секунд, цей інтервал буде нашим значенням, яке використовуватиметься для наших різних запитів до історії запитів IS. Ми також бачимо, що в цей період було виконано 7453 запити. Наша основна мета — не пропустити жодної інформації, тому період часу 413 секунд буде ключовим на подальших етапах.
Нарешті, ми отримаємо останні ключові параметри, які нам потрібні:
- max_timestamp: максимальна мітка часу для історії запитів AU. Це буде використовуватися пізніше для фільтрації інформації з історії запитів IS.
- totalnumberfractions: кількість фрагментів часу, які нам знадобляться для створення відповідних частин історії запитів IS.
Кількість фрагментів часу буде 2, оскільки різниця між currenttimestamp і maxtimestamp з історії запитів AU перевищує лише на 1 одиницю кількість секунд інтервалу часу. Зважайте на те, що вимірювання можуть значно варіюватися в залежності від кожного облікового запису, навантаження на систему, послуг, часу виконання тощо…
Тепер ми дійсно розуміємо весь процес, і у нас є всі інгредієнти для створення нашого динамічного запиту, ми будемо його готувати!
Ми подивимося, як генерувати різні запити до IS:
Ми отримали 2 фрагменти часу, тому побачимо, що були створені 2 запити до історії запитів IS, вказуючи різні часові вікна, використовуючи інтервал часу 413 секунд.
Тепер ми маємо найважливіше, нам потрібно лише об'єднати це з історією запитів AU, тому ми отримаємо запит, схожий на цей:
Зверніть увагу, що наші результати QUERYHISTORY в основному базуються на AU, який містить деяку додаткову інформацію, порівняно з IS. Цю додаткову інформацію буде вказано в цьому випадку як значення NULL для зручності цієї статті. Ми будемо відрізняти обидва набори даних за допомогою додаткового поля під назвою RECORDSOURCE, щоб знати, звідки походить інформація.
Автоматизація повної QUERY_HISTORY
У наведеному коді нижче ми побачимо підсумок всього, що було згадано раніше, а також як ми генеруємо динамічний запит для об'єднання всіх результатів з історії запитів AU + різних ітерацій історії запитів IS для кожного інтервалу часу.
текст перекладу
Ми створили процедуру з назвою SPQUERYHISTORY.
Цю процедуру було створено в нашій базі даних SECURITY, у схемі OBSERVABILITY.
CREATE OR REPLACE PROCEDURE SECURITY.OBSERVABILITY.SP_QUERY_HISTORY(var_query_adjust_rate int, var_time_frame_delay_in_minutes int)
RETURNS TABLE(
RECORD_SOURCE VARCHAR(18),QUERY_ID VARCHAR(16777216),QUERY_TEXT VARCHAR(16777216),DATABASE_ID NUMBER(38,0),DATABASE_NAME VARCHAR(16777216),
SCHEMA_ID NUMBER(38,0),SCHEMA_NAME VARCHAR(16777216),QUERY_TYPE VARCHAR(16777216),SESSION_ID NUMBER(38,0),USER_NAME VARCHAR(16777216),
ROLE_NAME VARCHAR(16777216),WAREHOUSE_ID NUMBER(38,0),WAREHOUSE_NAME VARCHAR(16777216),WAREHOUSE_SIZE VARCHAR(16777216),
WAREHOUSE_TYPE VARCHAR(16777216),CLUSTER_NUMBER NUMBER(38,0),QUERY_TAG VARCHAR(16777216),EXECUTION_STATUS VARCHAR(16777216),
ERROR_CODE NUMBER(38,5),ERROR_MESSAGE VARCHAR(16777216),START_TIME TIMESTAMP_LTZ(6),END_TIME TIMESTAMP_LTZ(6),TOTAL_ELAPSED_TIME NUMBER(38,0),
BYTES_SCANNED NUMBER(38,0),PERCENTAGE_SCANNED_FROM_CACHE FLOAT,BYTES_WRITTEN NUMBER(38,0),BYTES_WRITTEN_TO_RESULT NUMBER(38,0),
BYTES_READ_FROM_RESULT NUMBER(38,0),ROWS_PRODUCED NUMBER(38,0),ROWS_INSERTED NUMBER(38,0),ROWS_UPDATED NUMBER(38,0),ROWS_DELETED NUMBER(38,0),
ROWS_UNLOADED NUMBER(38,0),BYTES_DELETED NUMBER(38,0),PARTITIONS_SCANNED NUMBER(38,0),PARTITIONS_TOTAL NUMBER(38,0),BYTES_SPILLED_TO_LOCAL_STORAGE NUMBER(38,0),
BYTES_SPILLED_TO_REMOTE_STORAGE NUMBER(38,0),BYTES_SENT_OVER_THE_NETWORK NUMBER(38,0),COMPILATION_TIME NUMBER(38,0),EXECUTION_TIME NUMBER(38,0),
QUEUED_PROVISIONING_TIME NUMBER(38,0),QUEUED_REPAIR_TIME NUMBER(38,0),QUEUED_OVERLOAD_TIME NUMBER(38,0),TRANSACTION_BLOCKED_TIME NUMBER(38,0),
OUTBOUND_DATA_TRANSFER_CLOUD VARCHAR(16777216),OUTBOUND_DATA_TRANSFER_REGION VARCHAR(16777216),OUTBOUND_DATA_TRANSFER_BYTES NUMBER(38,0),
INBOUND_DATA_TRANSFER_CLOUD VARCHAR(16777216),INBOUND_DATA_TRANSFER_REGION VARCHAR(16777216),INBOUND_DATA_TRANSFER_BYTES NUMBER(38,0),
LIST_EXTERNAL_FILES_TIME NUMBER(38,0),CREDITS_USED_CLOUD_SERVICES FLOAT,RELEASE_VERSION VARCHAR(16777216),EXTERNAL_FUNCTION_TOTAL_INVOCATIONS NUMBER(38,0),
EXTERNAL_FUNCTION_TOTAL_SENT_ROWS NUMBER(38,0),EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS NUMBER(38,0),EXTERNAL_FUNCTION_TOTAL_SENT_BYTES NUMBER(38,0),
EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES NUMBER(38,0),QUERY_LOAD_PERCENT NUMBER(38,0),IS_CLIENT_GENERATED_STATEMENT BOOLEAN,
QUERY_ACCELERATION_BYTES_SCANNED NUMBER(38,0),QUERY_ACCELERATION_PARTITIONS_SCANNED NUMBER(38,0),QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR NUMBER(38,0),
TRANSACTION_ID NUMBER(38,0),CHILD_QUERIES_WAIT_TIME NUMBER(38,0),ROLE_TYPE VARCHAR(16777216),QUERY_HASH VARCHAR(16777216),QUERY_HASH_VERSION NUMBER(38,0),
QUERY_PARAMETERIZED_HASH VARCHAR(16777216),QUERY_PARAMETERIZED_HASH_VERSION NUMBER(38,0),SECONDARY_ROLE_STATS VARCHAR(16777216),ROWS_WRITTEN_TO_RESULT NUMBER(38,0),
QUERY_RETRY_TIME NUMBER(38,0),QUERY_RETRY_CAUSE VARCHAR(16777216),FAULT_HANDLING_TIME NUMBER(38,0)
)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
-- my_sql_command для об'єднання всіх інформаційних схем бази даних
my_sql_command STRING DEFAULT '';
-- Оголошуємо результат
rs RESULTSET;
res_out RESULTSET;
-- змінні для розрахунку оптимального періоду часу
query_adjust_rate int DEFAULT var_query_adjust_rate;
time_frame_delay_in_minutes int DEFAULT var_time_frame_delay_in_minutes;
time_frame int;
max_timestamp DATETIME;
total_number_fractions int;
BEGIN
time_frame := (
SELECT time_frame
FROM (
select
secs time_frame, min(end_time) min_end_time, max(end_time) max_end_time,
sum(queries) total_queries, avg(queries) avg_queries_fract, min(queries) min_queries_fract,
max(queries) max_queries_fract
from (
select tim.secs,time_slice(end_time::TIMESTAMP_NTZ , tim.secs, 'seconds') end_time,count(1) queries
From snowflake.account_usage.query_history q
inner join (SELECT seq4()+1 secs
текст перекладу
FROM TABLE(GENERATOR(ROWCOUNT => 50*60)) v ) tim
where tim.secs<=:time_frame_delay_in_minutes*60
group by 1,2
)
group by ALL
having max_queries_fract<= :query_adjust_rate --Регулюємо до 75% від максимально дозволеного (10k), дозволяючи запас у 25% для раптових зростань
qualify secs=max(secs) over (partition by '') -- Отримуємо найефективнішу частину (найбільшу)
)
);
max_timestamp := (SELECT max(end_time) FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY);
total_number_fractions := (SELECT
TRUNCATE(
datediff('seconds',:max_timestamp,current_timestamp()) --загальна різниця в секундах до поточного часу
/:time_frame)+1); -- Рахуємо кількість необхідних періодів часу для наших запитів
-- Призначаємо запит для результату і виконуємо запит.
текст перекладу
Ми виключаємо бази даних SHARE
rs := (SELECT
'WITH query_history_last AS (
SELECT DISTINCT *
FROM ('||
listagg(select_query_history, ' union all ') ||'))' SELECT_IC_QUERY_HISTORY
FROM (
SELECT time_frame start_fraction,
COALESCE(lead(time_frame) over(PARTITION BY '' ORDER BY time_frame desc),0) end_fraction,
'select *
from table(SECURITY.information_schema.query_history(
dateadd(''seconds'',-'||start_fraction||',current_timestamp()),
dateadd(''seconds'',-'||end_fraction||',current_timestamp()),
result_limit=>10000))' select_query_history
FROM (
SELECT (seq4()+1)*:time_frame time_frame
FROM TABLE(GENERATOR(ROWCOUNT => :total_number_fractions)) v
)
));
-- Використовуємо цикл FOR для ітерації через записи у результатах
FOR record IN rs DO
my_sql_command := record.SELECT_IC_QUERY_HISTORY;
END FOR;
my_sql_command := my_sql_command ||'
SELECT
''ACCOUNT_USAGE'' RECORD_SOURCE,
QUERY_ID, QUERY_TEXT, DATABASE_ID, DATABASE_NAME, SCHEMA_ID, SCHEMA_NAME, QUERY_TYPE, SESSION_ID, USER_NAME, ROLE_NAME,
WAREHOUSE_ID, WAREHOUSE_NAME, WAREHOUSE_SIZE, WAREHOUSE_TYPE, CLUSTER_NUMBER, QUERY_TAG, EXECUTION_STATUS, ERROR_CODE, ERROR_MESSAGE,
START_TIME, END_TIME, TOTAL_ELAPSED_TIME, BYTES_SCANNED, PERCENTAGE_SCANNED_FROM_CACHE, BYTES_WRITTEN, BYTES_WRITTEN_TO_RESULT,
BYTES_READ_FROM_RESULT, ROWS_PRODUCED, ROWS_INSERTED, ROWS_UPDATED, ROWS_DELETED, ROWS_UNLOADED, BYTES_DELETED, PARTITIONS_SCANNED,
PARTITIONS_TOTAL, BYTES_SPILLED_TO_LOCAL_STORAGE, BYTES_SPILLED_TO_REMOTE_STORAGE, BYTES_SENT_OVER_THE_NETWORK, COMPILATION_TIME, EXECUTION_TIME,
QUEUED_PROVISIONING_TIME, QUEUED_REPAIR_TIME, QUEUED_OVERLOAD_TIME, TRANSACTION_BLOCKED_TIME, OUTBOUND_DATA_TRANSFER_CLOUD, OUTBOUND_DATA_TRANSFER_REGION,
OUTBOUND_DATA_TRANSFER_BYTES, INBOUND_DATA_TRANSFER_CLOUD, INBOUND_DATA_TRANSFER_REGION, INBOUND_DATA_TRANSFER_BYTES, LIST_EXTERNAL_FILES_TIME,
CREDITS_USED_CLOUD_SERVICES, RELEASE_VERSION, EXTERNAL_FUNCTION_TOTAL_INVOCATIONS, EXTERNAL_FUNCTION_TOTAL_SENT_ROWS, EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,
EXTERNAL_FUNCTION_TOTAL_SENT_BYTES, EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES, QUERY_LOAD_PERCENT, IS_CLIENT_GENERATED_STATEMENT, QUERY_ACCELERATION_BYTES_SCANNED,
QUERY_ACCELERATION_PARTITIONS_SCANNED, QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR, TRANSACTION_ID, CHILD_QUERIES_WAIT_TIME, ROLE_TYPE, QUERY_HASH,
QUERY_HASH_VERSION, QUERY_PARAMETERIZED_HASH, QUERY_PARAMETERIZED_HASH_VERSION, SECONDARY_ROLE_STATS, ROWS_WRITTEN_TO_RESULT, QUERY_RETRY_TIME,
QUERY_RETRY_CAUSE, FAULT_HANDLING_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
UNION ALL
SELECT
''INFORMATION_SCHEMA'' RECORD_SOURCE,QUERY_ID, QUERY_TEXT, NULL DATABASE_ID, DATABASE_NAME, NULL SCHEMA_ID, SCHEMA_NAME, QUERY_TYPE, SESSION_ID, USER_NAME, ROLE_NAME,
NULL WAREHOUSE_ID, WAREHOUSE_NAME, WAREHOUSE_SIZE, WAREHOUSE_TYPE, CLUSTER_NUMBER, QUERY_TAG, EXECUTION_STATUS, ERROR_CODE, ERROR_MESSAGE,
START_TIME, END_TIME, TOTAL_ELAPSED_TIME, BYTES_SCANNED, NULL PERCENTAGE_SCANNED_FROM_CACHE, NULL BYTES_WRITTEN, NULL BYTES_WRITTEN_TO_RESULT,
NULL BYTES_READ_FROM_RESULT, ROWS_PRODUCED, ROWS_INSERTED, NULL ROWS_UPDATED, NULL ROWS_DELETED, NULL ROWS_UNLOADED, NULL BYTES_DELETED,
NULL PARTITIONS_SCANNED, NULL PARTITIONS_TOTAL, NULL BYTES_SPILLED_TO_LOCAL_STORAGE,NULL BYTES_SPILLED_TO_REMOTE_STORAGE,NULL BYTES_SENT_OVER_THE_NETWORK,
COMPILATION_TIME,EXECUTION_TIME, QUEUED_PROVISIONING_TIME,QUEUED_REPAIR_TIME,QUEUED_OVERLOAD_TIME,TRANSACTION_BLOCKED_TIME,OUTBOUND_DATA_TRANSFER_CLOUD,
OUTBOUND_DATA_TRANSFER_REGION, OUTBOUND_DATA_TRANSFER_BYTES,INBOUND_DATA_TRANSFER_CLOUD,INBOUND_DATA_TRANSFER_REGION,INBOUND_DATA_TRANSFER_BYTES,
NULL LIST_EXTERNAL_FILES_TIME, CREDITS_USED_CLOUD_SERVICES,RELEASE_VERSION,EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,
текст перекладу
EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS, EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,NULL QUERY_LOAD_PERCENT,
IS_CLIENT_GENERATED_STATEMENT,QUERY_ACCELERATION_BYTES_SCANNED, QUERY_ACCELERATION_PARTITIONS_SCANNED,QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR,
TRANSACTION_ID,NULL CHILD_QUERIES_WAIT_TIME,NULL ROLE_TYPE,QUERY_HASH, QUERY_HASH_VERSION,QUERY_PARAMETERIZED_HASH,QUERY_PARAMETERIZED_HASH_VERSION,
NULL SECONDARY_ROLE_STATS,ROWS_WRITTEN_TO_RESULT,QUERY_RETRY_TIME, QUERY_RETRY_CAUSE,FAULT_HANDLING_TIME
FROM query_history_last q
WHERE NOT EXISTS
(SELECT 1 FROM snowflake.account_usage.query_history h
WHERE h.query_id = q.query_id)';
res_out := (EXECUTE IMMEDIATE :my_sql_command);
-- Повертаємо текст, коли зберігається процедура завершена
RETURN TABLE(res_out);
END;
Тепер ми виконаємо наш запит QUERY_HISTORY, який витягне всю історію за останні 365 днів, включаючи найсвіжішу інформацію без втрати запитів (він також включатиме ті, що щойно виконані!).
Наш запит був виконаний за 32 секунди, що не так погано, враховуючи, що він автоматично обчислює адаптивну смарт-логіку і динамічно витягує всю історію для 180 тис. рядків!!
Зараз ми перевіримо, як виглядає наш запит у дії, отримуючи приклад узагальнених інсайтів.
Я виконаю наступний запит:
select
record_source,
date_trunc('minute',end_time) end_time, --групування за хвилиною
count(1) number_of_queries
From table(SECURITY.OBSERVABILITY.SP_QUERY_HISTORY(7500, 45))
group by all
order by 3 desc;
Я розбив вісь "час завершення" на групи, оскільки було занадто багато точок для представлення, але ви можете побачити різницю між інформацією, отриманою з AS та IS.
Тепер ваша черга! Ви можете отримати процедуру, розгорнути її у вашому середовищі і експериментувати з нею!
Висновки
Як ви могли побачити, ми розширили поточне обмеження, яке за замовчуванням не надає Snowflake.
текст перекладу
Цей метод QUERY_HISTORY надасть повну розширену історію запитів від поточного часу до 1 року назад. Процедура та метод, що використовуються, є динамічними, тому вони адаптуються до вашої платформи даних, отримуючи різні часові проміжки в залежності від ваших навантажень.
Це дослідження було ретельно розроблено, враховуючи різні сценарії, з якими ми стикалися до цього часу, нам потрібно легко отримати всі запити з одного місця. Сподіваюся, Snowflake надасть це за замовчуванням, але до того часу ви можете скористатися цією статтею.
Про мене
Як експерт з різних технологій даних з понад 20-річним досвідом у галузі даних, я керую та консультую команди для досягнення найкращих підходів і застосування найкращих практик.
Я є архітектором Snowflake, членом Snowflake Spotlight Squad Team і членом Snowflake Barcelona User Group — Chapter.
Як сертифікований практик Data Vault, я керував архітектурами Data Vault, використовуючи методології, засновані на метаданих.
Якщо ви хочете дізнатися більше деталей про аспекти, згадані тут, або інші, ви можете слідкувати за мною на Medium або LinkedIn тут.
Перекладено з: SNOWFLAKE: Extending a full QUERY_HISTORY