Pecah banyak lingkaran dalam mencari solusi untuk dengan cepat mendapatkan sejarah harga panjang untuk sejumlah besar aset di Python. Saya juga berani ingin bekerja dengan harga di array numpy, tetapi lebih baik segera di panda.
Pendekatan standar ke dahi bekerja mengecewakan, yang menyebabkan eksekusi permintaan ke database selama 30 detik atau lebih. Tidak ingin memasang, saya menemukan beberapa solusi yang benar-benar memuaskan saya.
Kaki tumbuh dari sifat objek Python. Bagaimanapun, bilangan bulat bahkan adalah objek, yang sangat negatif mempengaruhi kecepatan kerja. Saya pasti tidak ingin mengubah bahasa.
Solusi pertama adalah pengelompokan sejarah harga oleh PostgreSQL, yang menyebabkan penurunan kinerja pada sisi basis data, tetapi mempercepat tugas sekitar ~ 3 kali. Metode ini dijelaskan secara lebih rinci di artikel lain.
Hasilnya adalah pemahaman bahwa dalam Python Anda perlu entah bagaimana mendapatkan seluruh kumpulan data dalam satu potong, setidaknya sebuah string. Dan parsing dengan numpy-arrays atau langsung di panda.
Hasil akhir:
Solusi Dahi untuk PostgreSQL
Kami membuat pengelompokan data dalam kueri sql. Contoh:
SELECT string_agg(symbol::text, ',') AS symbol_list , string_agg(dt::text, ',') AS dt_list , string_agg(open::text, ',') AS open_list , string_agg(high::text, ',') AS high_list , string_agg(low::text, ',') AS low_list , string_agg("close"::text, ',') AS close_list , string_agg(volume::text, ',') AS volume_list , string_agg(adj::text, ',') AS adj_list FROM v_prices_fast WHERE symbol IN ('{symbols}')
Parsing data mudah:
{ 'symbol': np.array(r[0].split(',')),
Produktivitas pada ~ 1,7 juta baris:
%timeit get_prices_fast(is_adj=False)
Paket Python siap pakai
Python bagus untuk komunitasnya, yang menghadapi masalah serupa. Berikut ini cocok untuk tujuan kita:
- odo - dibuat untuk mengoptimalkan kecepatan transfer data dari satu sumber ke sumber lainnya. Sepenuhnya dalam Python. Berinteraksi dengan PostgreSQL melalui SQLAlchemy.
- warp_prism - Ekstensi C yang digunakan oleh proyek Quantopian untuk mengambil data dari PostgreSQL. Dasarnya adalah fungsi Odo.
Kedua paket menggunakan kemampuan PostgreSQL untuk menyalin data ke CSV:
COPY {query} TO :path WITH ( FORMAT CSV, HEADER :header, DELIMITER :delimiter, QUOTE :quotechar, NULL :na_value, ESCAPE :escapechar, ENCODING :encoding )
Output diuraikan menjadi panda. DataFrame () atau numpy.ndarray ().
Karena warp_prism ditulis dalam C, ia memiliki keuntungan yang signifikan dalam hal penguraian data. Tetapi pada saat yang sama memiliki kelemahan yang signifikan - dukungan terbatas untuk tipe data. Yaitu, ia mem-parsing int, float, date, dan str, tetapi tidak numerik. Odo tidak memiliki batasan seperti itu.
Untuk digunakan, perlu untuk menggambarkan struktur tabel dan permintaan menggunakan paket sqlalchemy:
tbl_prices = sa.Table( 'prices', metadata, sa.Column('symbol', sa.String(16)), sa.Column('dt', sa.Date), sa.Column('open', sa.FLOAT), sa.Column('high', sa.FLOAT), sa.Column('low', sa.FLOAT), sa.Column('close', sa.FLOAT), sa.Column('volume', sa.BIGINT), sa.Column('adj', sa.FLOAT), ) query = sa.select(tbl_prices.c).where( tbl_prices.c.symbol.in_(SYMBOLS) ).order_by('symbol', 'dt')
Tes kecepatan:
%timeit odo(query, pd.DataFrame, bind=engine)
warp_prism.to_arrays () - menyiapkan kamus python dengan array numpy.
Apa yang bisa dilakukan dengan ClickHouse?
PostgreSQL bagus untuk semua orang, kecuali selera untuk ukuran penyimpanan dan kebutuhan untuk mengkonfigurasi sharding untuk tabel besar. ClickHouse itu sendiri pecahan, menyimpan semuanya dengan kompak, dan bekerja dengan kecepatan kilat. Misalnya, tabel PostgreSQL dengan ukuran ~ 5Gb di ClickHouse cocok dengan ~ 1Gb. Penggunaan ClickHouse untuk menyimpan harga dijelaskan dalam artikel lain.
Saya kecewa, odo tidak membantu, meskipun ada ekstensi clickhouse untuk sqlalchemy. Memori kecepatan clickhouse di konsol menuntun saya pada gagasan untuk mengakses database melalui penciptaan proses yang terpisah. Saya tahu bahwa ini panjang dan memakan banyak sumber daya, tetapi hasilnya tidak bisa dipuji.
sql = 'SELECT days.symbol, days.date, days.open/10000, days.high/10000, days.low/10000, days.close/10000, days.volume FROM days ' \ 'WHERE days.symbol IN (\'{0}\') ORDER BY days.symbol, days.date;'.format("','".join(SYMBOLS)) cmd = 'clickhouse-client --query="{0}"'.format(sql) def ch_pandas(cmd): p = subprocess.Popen([cmd], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True) return pd.io.parsers.read_csv(p.stdout, sep="\t", names=['symbol', 'date', 'open', 'high', 'low', 'close', 'volume'])
Hasil:
%timeit ch_pandas(cmd)
Permintaan Port HTTP ClickHouse
Hasilnya sedikit memburuk ketika mengakses langsung ke port 8123, di mana database merespons:
import urllib %timeit pd.io.parsers.read_csv('http://localhost:8123/?{0}'.format(urllib.parse.urlencode({'query': sql})), sep="\t", names=['symbol', 'date', 'open', 'high', 'low', 'close', 'volume'])
Tapi bukan tanpa lalat di salep.
Terbang dalam salep dengan ClickHouse
Basis data sangat mengesankan pada sampel besar, tetapi pada hasil kecil itu mengecewakan. ~ 20 kali lebih buruk dari Odo. Tetapi ini adalah biaya kit tambahan dengan memulai proses atau mengakses melalui HTTP.
Hasil:
Kesimpulan
Dengan artikel ini, upaya mempercepat interaksi antara Python dan basis data telah berakhir. Untuk PostgreSQL dengan bidang standar dan kebutuhan untuk akses universal ke harga, cara terbaik adalah dengan menggunakan paket warp_prism dari Quantopian. Jika Anda perlu menyimpan volume besar sejarah dan permintaan frekuensi tinggi untuk sejumlah besar baris, ClickHouse sangat ideal.