📈 Загрузка данных временных рядов на сервер SQL с помощью Python

Цель статьи – познакомить читателей с процессом сбора, интерпретации и обработки данных для оперирования временными рядами с помощью сервера SQL и Python.

Адаптированный текст публикуется с сокращениями, автор оригинальной статьи Rick Dobson.

Распространённая задача анализа данных – обработка временно́го ряда. Это может быть отслеживание распространения вируса или анализ стоимости ценных бумаг, который мы рассмотрим в данной статье. В качестве сервера SQL возьмём Microsoft SQL Server, а программировать будем на Python.

Для начала выделим три общие задачи:

1. Управление информацией после заполнения базы данных.
2. Увеличение числа отслеживаемых элементов после первоначального заполнения БД.
   
3. Добавление свежих данных для более поздних интервалов времени.
   

Cбор и настройка исходного набора данных

Начнем со сбора сведений. Раздобыть их можно в транзакционных базах данных, хранилищах информации вашей компании или в общедоступных интернет-источниках. Необходимо связать между собой по крайней два измерения: метки времени и соответствующие им значениями переменной.

Ниже показана информация о мартовских ценах акций корпорации Microsoft на Yahoo Finance. Обратите внимание, что торгующиеся на бирже ценные бумаги обозначаются специальными идентификаторами – тикерами (тикерными символами – от англ. ticker symbols). Например, MSFT – тикер акций Microsoft.

В серой части страницы приведены основные параметры запроса: в частности можно указать интервал времени и частоту.

Разберем скрипт Python, предназначенный для сбора данных временных рядов из файла MSSQLTips_4.txt.

import pandas_datareader.data as web
import datetime
 
symbol = []
with open('C:\python_programs\MSSQLTips_4.txt') as f:
    for line in f:
        symbol.append(line.strip())
f.close()
 
start = datetime.date(2019,9,17)
end = datetime.date(2019,9,24)
 
path_out = 'c:/python_programs_output/'
file_out = 'yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv'
 
i = 0
while i < len(symbol):
    try:
        df = web.DataReader(symbol[i], 'yahoo', start, end)
        df.insert(0, 'Symbol', symbol[i])
        df = df.drop(['Adj Close'], axis=1)
        if i == 0:
            df.to_csv(path_out+file_out)
            print (i, symbol[i],'has data stored to csv file')
        else:
            df.to_csv(path_out+file_out,mode = 'a', header=False)
            print (i, symbol[i],'has data stored to csv file')
    except:
        print("No information for symbol or file is open in Excel:")
        print (i, symbol[i])
        continue
    i+=1

Скрипт записывает информацию в файл yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv , который выглядит следующим образом:

Вы можете вставить данные временных рядов из yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv в таблицу на сервере SQL с помощью группового insert-а. Приведённый ниже сценарий демонстрирует этот процесс:

• Начинается он с инструкции use для базы данных, содержащей таблицу для начальной загрузки.
• Сценарий создает новую копию таблицы yahoo_prices_volumes_for_MSSQLTips.
  
• Таблица yahoo_prices_volumes_for_MSSQLTips заполняется начальными данными.
  
• Оператор if exists проверяет наличие таблицы и если она существует, старая копия удаляется.
  
• Вставка содержимого файла yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv в таблицу yahoo_prices_volumes_for_MSSQLTips осуществляется с помощью insert.
  
• Завершает скрипт оператор select, который возвращает содержимое заполненной таблицы.
  

use [for_csv_from_python]
go
 
-- drop table for watchlist if it exists
if exists(select object_id('dbo.yahoo_prices_volumes_for_MSSQLTips'))
drop table dbo.yahoo_prices_volumes_for_MSSQLTips
 
-- create table for watchlist
create table dbo.yahoo_prices_volumes_for_MSSQLTips(
   [Date] date,
   [Symbol] nvarchar(10),
   [Open] money NULL,
   [High] money NULL,
   [Low] money NULL,
   [Close] money NULL,
   [Volume] int NULL
) 
go 
 
-- bulk insert first batch of symbols to watchlist
bulk insert dbo.yahoo_prices_volumes_for_MSSQLTips
from 'C:\python_programs_output\yahoo_prices_volumes_for_MSSQLTips_4_to_csv_demo.csv'
with
(
    firstrow = 2,
    fieldterminator = ',',  --CSV field delimiter
    rowterminator = '\n'
) 
 
-- display watchlist table with data for first batch of symbols
select * from dbo.yahoo_prices_volumes_for_MSSQLTips order by symbol, date

Ниже приведён набор значений из последнего select в предыдущем скрипте. Строки упорядочиваются с помощью директивы order by.

Добавление новых тикеров в начальную загрузку

После начальной загрузки данными нужно управлять. Пользователи могут запрашивать информацию о дополнительных элементах: например, о ценах в том же диапазоне дат ещё для трех тикеров, вроде ENPH, INS и KL. Вам в помощь txt-файл MSSQLTips_3.txt с тикерными символами для начальной загрузки.

Вы можете загрузить цены и объёмы для этих новых символов с помощью скрипта Python read_mssqltips_3_for_export_via_csv.py. Он ссылается на MSSQLTips_3.txt и сохраняет выходные данные в файле yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv. Полный список всех скриптов Python в этом руководстве доступен по этой ссылке.

Приведем файл с данными: yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv

Обновление списка наблюдения может быть выполнено с помощью инструкции bulk insert, которая перекачивает содержимое yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv в таблицу yahoo_prices_volumes_for_MSSQLTips. Следующий скрипт показывает код T-SQL для обновления таблицы:

-- bulk insert second batch of symbols to watchlist
bulk insert dbo.yahoo_prices_volumes_for_MSSQLTips
from 'C:\python_programs_output\yahoo_prices_volumes_for_MSSQLTips_3_to_csv_demo.csv'
with
(
    firstrow = 2,
    fieldterminator = ',',  --CSV field delimiter
    rowterminator = '\n'
)
 
-- display watchlist table with data for first and second batches of symbols
select * from dbo.yahoo_prices_volumes_for_MSSQLTips order by symbol, date

Последний select показывает таблицу наблюдения после того, как в нее добавились тикеры ENPH, INS и KL. Напомним, что изначально в таблице их было всего четыре: AMZN, MSFT, ORCL, PAYS.

Таблица наблюдения содержит сорок две строки данных — по шесть строк для каждого из семи тикеров.

Добавление новых строк с данными

В этом разделе представлен обзор изменений кода для обновления существующей таблицы значений временных рядов данными для дополнительного периода времени. До этого момента в статье фигурировал интервал с 17 сентября 2019 по 24 сентября 2019. При работе с данными временных рядов обычно происходит регулярное обновление информации, например, один раз в день. Поскольку в примерах используются последовательные торговые дни, добавим данные за 25 сентября 2019 года для тикеров из MSSLQTIPS_7.txt.

В файле yahoo_prices_volumes_for_MSSQLTips_7_to_csv_demo.csv с данными за 25 сентября 2019 года для каждого тикера существует только одна строка.

Содержимое файла можно добавить в таблицу наблюдения значений временных рядов (yahoo_prices_volumes_for_MSSQLTips) с помощью следующего кода.

-- bulk insert fresh time series data to watchlist
bulk insert dbo.yahoo_prices_volumes_for_MSSQLTips
from 'C:\python_programs_output\yahoo_prices_volumes_for_MSSQLTips_7_to_csv_demo.csv'
with
(
    firstrow = 2,
    fieldterminator = ',',  --CSV field delimiter
    rowterminator = '\n'
)
  
-- display watchlist table with data for first and second batches of symbols
-- and with an extra row of time series data from the preceding select statement
select * from dbo.yahoo_prices_volumes_for_MSSQLTips order by symbol, date

В выводе оператора select из предыдущего скрипта показаны результаты по AMZN для начальной загрузки данных временных рядов, а также по ENPH из обновления. Как видите, оба символа имеют исторические значения временных рядов для торговых дат с 17 сентября 2019 года по 25 сентября 2019 года. Остальные пять символов, также имеют значения временных рядов в том же диапазоне дат.

Заключение

Для закрепления информации, предлагаем вам домашнее задание:

• попробуйте повторить описанные в статье действия;
• измените тикеры и даты на те, которые вам больше нравятся;
  
• замените текущие файлы cave новыми, из систем вашей компании.
  

Не останавливайтесь на достигнутом. Удачи!

Источники

• https://www.mssqltips.com/sqlservertip/6232/load-time-series-data-with-sql-server/