كيف من PostgreSQL و ClickHouse في Python كثيرًا ، بشكل سريع وفوري في numpy

انكسر الكثير من الدوائر بحثًا عن حل للحصول على سجلات أسعار طويلة بسرعة لعدد كبير من الأصول في Python. كما أتيحت لي الشجاعة لرغبتي في العمل مع الأسعار في المصفوفات المرقمة ، ولكن من الأفضل على الفور في الباندا.

عملت الأساليب القياسية للجبهة بشكل مخيب للآمال ، مما أدى إلى تنفيذ الاستعلام إلى قاعدة البيانات لمدة 30 ثانية أو أكثر. لا أريد طرحها ، لقد وجدت العديد من الحلول التي أرضتني تمامًا.

تنمو الأرجل من طبيعة كائن Python. بعد كل شيء ، حتى الأرقام الصحيحة هي الأشياء ، مما يؤثر سلبًا للغاية على سرعة العمل. لم أرد بشكل قاطع تغيير اللغة.

كان الحل الأول هو تجميع سجل الأسعار من خلال PostgreSQL ، مما أدى إلى انخفاض طفيف في الأداء من جانب قاعدة البيانات ، لكنه سرع المهمة حوالي 3 مرات تقريبًا. يتم وصف الطريقة بمزيد من التفصيل في مقال آخر.

كانت النتيجة فهم أنه في Python ، يجب أن تحصل بطريقة ما على مجموعة البيانات بالكامل في قطعة واحدة ، على الأقل سلسلة. وحلل بواسطة صفائف numpy أو على الفور في الباندا.

النتائج النهائية:

حل الجبين ل PostgreSQL

نقوم بتجميع البيانات في استعلام SQL. مثال:

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}') 

تحليل البيانات سهل:

 { 'symbol': np.array(r[0].split(',')), # str 'dt': np.array(r[1].split(','), dtype='datetime64'), # str w/type 'open': np.fromstring(r[2], sep=','), # numbers # ... } 

إنتاجية عند 1.7 مليون خط:

 %timeit get_prices_fast(is_adj=False) # 11.9s 

حزم Python الجاهزة

بايثون جيدة لمجتمعها الذي يواجه مشاكل مماثلة. فيما يلي مناسبة لغرضنا:

• odo - تم إنشاؤها لتحسين سرعة نقل البيانات من مصدر إلى آخر. بالكامل في بايثون. يتفاعل مع PostgreSQL من خلال SQLAlchemy.
• warp_prism - الامتداد C الذي يستخدمه مشروع Quantopian لاسترداد البيانات من PostgreSQL. الأساس هو وظيفة odo.

تستخدم الحزمتان قدرة PostgreSQL على نسخ البيانات إلى CSV:

 COPY {query} TO :path WITH ( FORMAT CSV, HEADER :header, DELIMITER :delimiter, QUOTE :quotechar, NULL :na_value, ESCAPE :escapechar, ENCODING :encoding ) 

يتم تحليل الإخراج إلى pandas.DataFrame () أو numpy.ndarray ().

نظرًا لأن warp_prism مكتوبًا في لغة C ، فإنه يتمتع بميزة كبيرة من حيث تحليل البيانات. ولكن في نفس الوقت لديها عيب كبير - دعم محدود لأنواع البيانات. أي أنه يوزع int و float و date و str لكن ليس رقميًا. ليس لدى Odo مثل هذه القيود.

للاستخدام ، من الضروري وصف هيكل الجدول والاستعلام باستخدام حزمة 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') 

اختبارات السرعة:

 %timeit odo(query, pd.DataFrame, bind=engine) # 13.8s %timeit warp_prism.to_dataframe(query, bind=engine) # 8.4s %timeit warp_prism.to_arrays(query, bind=engine) # 8.0s 

warp_prism.to_arrays () - تحضير قاموس بيثون مع صفائف numpy.

ما الذي يمكن فعله باستخدام ClickHouse؟

يعد PostgreSQL جيدًا للجميع ، باستثناء الشهية لحجم التخزين والحاجة إلى تكوين تقسيم إلى جداول كبيرة. ClickHouse نفسه يشق ، يخزن كل شيء بشكل مضغوط ، ويعمل بسرعة البرق. على سبيل المثال ، جدول PostgreSQL بحجم ~ 5Gb في ClickHouse يناسب ~ 1Gb. يوصف استخدام ClickHouse لتخزين الأسعار في مقال آخر.

إلى غضبي ، لم يساعد odo ، على الرغم من وجود ملحق Clickhouse ل sqlalchemy. قادني ذكرى سرعة كليك هاوس في وحدة التحكم إلى فكرة الوصول إلى قاعدة البيانات من خلال إنشاء عملية منفصلة. أعلم أنها طويلة وتستهلك الموارد ، ولكن النتائج كانت أكثر من الثناء.

 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']) 

النتيجة:

 %timeit ch_pandas(cmd) # 1.6s 

ClickHouse طلب منفذ HTTP

ساءت النتائج قليلاً عند الوصول مباشرة إلى المنفذ 8123 ، حيث تستجيب قاعدة البيانات:

 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']) # 1.9s 

ولكن ليس بدون ذبابة في المرهم.

تحليق في المرهم مع ClickHouse

كانت قاعدة البيانات مثيرة للإعجاب على عينات كبيرة ، ولكن النتائج الصغيرة كانت مخيبة للآمال. ~ 20 مرة أسوأ من odo. ولكن هذه هي تكلفة مجموعة إضافية مع إطلاق العملية أو الوصول عبر HTTP.

النتائج:

الخلاصة

مع هذا المقال ، انتهى السعي لتسريع التفاعل بين بايثون وقواعد البيانات. بالنسبة إلى PostgreSQL مع الحقول القياسية والحاجة إلى الوصول الشامل إلى الأسعار ، فإن أفضل طريقة هي استخدام حزمة warp_prism من Quantopian. إذا كنت بحاجة إلى تخزين كميات كبيرة من التاريخ وعدد كبير من الطلبات لعدد كبير من الأسطر ، فإن ClickHouse مثالي.