Автоматизация баз данных с PgAgent: Как автоматизировать сбор и хранение данных.
В современном мире данные - это король, и автоматизированный сбор и хранение данных критически важны для обеспечения своевременного и эффективного анализа данных. Если вы работаете с PostgreSQL, вы можете автоматизировать сбор и хранение данных, используя PgAgent, планировщик заданий PostgreSQL.
В этой статье я проведу вас через процесс настройки системы автоматизации баз данных PostgreSQL с использованием PgAgent. Мы рассмотрим следующие шаги:
- Добавление новых данных в основную таблицу.
- Автоматизация добавления новых данных.
- Связывание основной таблицы с временной таблицей Проверка, обновились ли все тикеры.
- Если вы никогда не работали с приложением для PgAdmin, ознакомьтесь с его функциями по этой ссылке: документация PgAgent.
Шаг 1: Добавление новых данных в основную таблицу.
Первым шагом является добавление основных данных в основную таблицу. Вы можете сделать это, написав скрипт в стандартном окне ввода PgAdmin. Например, вы можете добавить данные из CSV-файла, используя следующий код:
COPY nyse_data FROM 'C:\file_path\nyse_data.csv' DELIMITER ',' CSV HEADER;
Чтобы проверить, были ли данные добавлены в таблицу, можно использовать следующий код:
select count(*) from nyse_data;
select * from nyse_ticker_list;
Мы имеем пустую таблицу nyse_data_new для хранения новых поступающих данных от Yahoo Finance. Эта таблица будет хранить все новые котировки для выбранных инструментов. Это делается для предотвращения дублирования данных в основной таблице. Поскольку мы используем ежедневный интервал, наша таблица будет хранить данные за вчерашний день.
Шаг 2: Автоматизация добавления новых данных
Чтобы автоматизировать процесс добавления новых данных в таблицу nyse_data_new, мы будем использовать PgAgent. Мы создадим скрипт на PL/pgSQL, который будет копировать данные из CSV-файла и вставлять их в таблицу nyse_data_new. Скрипт будет запускаться каждую ночь в 5:40 утра, при условии, что скрипт Python парсит новую информацию в 3:11 утра по серверному времени и занимает ровно один час на парсинг и сохранение новых данных в файл.
Шаг 3: Связывание главной таблицы с временной таблицей
Затем нам нужно связать главную таблицу nyse_data с временной таблицей nyse_data_new, чтобы избежать дубликатов. Для этого мы будем использовать функцию и триггер.
Сначала мы создадим функцию PL/pgSQL с именем nyse_trig (), которая берет значения из новой строки, вставленной в таблицу nyse_data_new, и вставляет их в таблицу nyse_data, если дата равна дате предыдущего дня.
create or replace function nyse_trig()
returns trigger AS $$
declare
Date DATE;
Symbols VARCHAR(10);
Adj_Close NUMERIC(21, 15);
Close NUMERIC(21, 15);
High NUMERIC(21, 15);
Low NUMERIC(21, 15);
Open NUMERIC(21, 15);
Volume NUMERIC(13, 2);
BEGIN
date = new.date;
Symbols = new.symbols;
Adj_close = new.adj_close;
close = new.close;
High = new.High;
Low = new.Low;
Open = new.Open;
Volume = new.volume;
if(date = CURRENT_DATE -1) then
insert into nyse_data values(
new.date, new.symbols, new.adj_close, new.close, new.High, new.Low, new.Open, new.volume
);
end if;
return new;
END;
$$ language plpgsql
Далее мы создадим триггер с именем nyse_trig_on_insert, который будет вызывать функцию nyse_trig() каждый раз при вставке новой строки в таблицу nyse_data_new.
create trigger nyse_trig_on_insert
after insert on
nyse_data_new
for each row
execute procedure nyse_trig()
Шаг 4: Проверка отсутствующих тикеров.
Мы также хотим проверить отсутствующие тикеры и добавить их в таблицу nyse_data_new. Мы будем использовать PL/pgSQL скрипт для генерации списка отсутствующих тикеров в таблице nyse_data, сравнивая ее со списком тикеров в таблице nyse_ticker_list. Скрипт будет запускаться каждое утро в 6:30 утра.
COPY (
SELECT '[' || array_to_string(array_agg(''''||ticker||''','), ' ') || ']'
FROM nyse_ticker_list
WHERE ticker NOT IN (
SELECT symbols
FROM nyse_data
WHERE date = CURRENT_DATE-1
GROUP BY symbols
)
) TO 'C:\folder_name\file_name.csv';
После повторного разбора файла с помощью скрипта на Python нам просто нужно добавить новые данные обратно во временную таблицу. После этого наш триггер и функция автоматически подберут необходимую информацию для главной таблицы.
COPY nyse_data_new FROM 'C:\file_path\missing_tickers_data.csv'
DELIMITER ',' CSV HEADER;
Вот и всё! Наш конвейер автоматизации данных теперь завершён. Вот краткое описание процесса:
- 3:11 утра - Python начинает загрузку новых данных.
- 5:40 утра - Postgres добавляет новые данные в таблицу nyse_data_new.
- 6:30 утра - скрипт PgAgent проверяет и создает файл с отсутствующими тикерами.
- 7:03 утра - Python загружает отсутствующие данные.
- 8:01 утра - Postgres добавляет отсутствующие тикеры в nyse_data_new.
Это всего лишь пример, и вы можете настроить время выполнения скриптов в соответствии с вашими конкретными потребностями. С помощью этого автоматизированного процесса вы можете иметь надежную и актуальную базу данных без необходимости ручного вмешательства.
Код на GitHub: Перейти >>>