العمل مع بيانات CSV وTSV في ClickHouse

يدعم ClickHouse استيراد البيانات من CSV وتصديرها إليه. ونظرًا إلى أن ملفات CSV قد تختلف في تفاصيل التنسيق، بما في ذلك صفوف العناوين والفواصل المخصصة ورموز الإفلات، فإن ClickHouse يوفّر تنسيقات وإعدادات للتعامل مع كل حالة بكفاءة.

قبل استيراد البيانات، لنُنشئ جدولًا ذا بنية مناسبة:

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

لاستيراد البيانات من ملف CSV إلى الجدول sometable، يمكننا توجيه ملفنا مباشرةً إلى clickhouse-client:

clickhouse-client -q "INSERT INTO sometable FORMAT CSV" < data_small.csv

لاحظ أننا نستخدم FORMAT CSV لإبلاغ ClickHouse بأننا نُدخِل بيانات بتنسيق CSV. وبدلًا من ذلك، يمكننا تحميل البيانات من ملف محلي باستخدام عبارة FROM INFILE:

INSERT INTO sometable
FROM INFILE 'data_small.csv'
FORMAT CSV

هنا، نستخدم العبارة FORMAT CSV لكي يتعرّف ClickHouse على تنسيق الملف. ويمكننا أيضًا تحميل البيانات مباشرةً من عناوين URL باستخدام الدالة url()، أو من ملفات S3 باستخدام الدالة s3().

لنفترض أن ملف CSV يتضمن صف العناوين:

head data-small-headers.csv

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34

لاستيراد البيانات من هذا الملف، يمكن استخدام تنسيق CSVWithNames:

clickhouse-client -q "INSERT INTO sometable FORMAT CSVWithNames" < data_small_headers.csv

في هذه الحالة، يتخطّى ClickHouse الصف الأول عند استيراد البيانات من الملف.

إذا كان ملف CSV يستخدم محددًا غير الفاصلة، فيمكننا استخدام الخيار format_csv_delimiter لتحديد الرمز المناسب:

SET format_csv_delimiter = ';'

الآن، عند الاستيراد من ملف CSV، سيُستخدم الرمز ; كفاصل بدلًا من الفاصلة.

أحيانًا، قد نحتاج إلى تخطي عدد معيّن من الأسطر أثناء استيراد البيانات من ملف CSV. ويمكن إجراء ذلك باستخدام الخيار input_format_csv_skip_first_lines:

SET input_format_csv_skip_first_lines = 10

في هذه الحالة، سنتجاوز الأسطر العشرة الأولى من ملف CSV:

SELECT count(*) FROM file('data-small.csv', CSV)

┌─count()─┐
│     990 │
└─────────┘

يحتوي الملف على 1k صف، لكن ClickHouse حمّل 990 صفًا فقط لأننا طلبنا تخطي أول 10 صفوف.

يمكن تمثيل قيم NULL بطرق مختلفة بحسب التطبيق الذي أنشأ الملف. وبشكل افتراضي، يستخدم ClickHouse القيمة \N لتمثيل NULL في CSV. لكن يمكننا تغيير ذلك باستخدام الخيار format_csv_null_representation.

لنفترض أن لدينا ملف CSV التالي:

> cat nulls.csv
Donald,90
Joe,Nothing
Nothing,70

إذا قمنا بتحميل البيانات من هذا الملف، فسيتعامل ClickHouse مع Nothing باعتباره String (وهذا صحيح):

SELECT * FROM file('nulls.csv')

┌─c1──────┬─c2──────┐
│ Donald  │ 90      │
│ Joe     │ Nothing │
│ Nothing │ 70      │
└─────────┴─────────┘

إذا أردنا أن يتعامل ClickHouse مع Nothing باعتباره NULL، فيمكننا ضبط ذلك باستخدام الخيار التالي:

SET format_csv_null_representation = 'Nothing'

الآن أصبح لدينا NULL في الموضع الذي نتوقعه فيه:

SELECT * FROM file('nulls.csv')

┌─c1─────┬─c2───┐
│ Donald │ 90   │
│ Joe    │ ᴺᵁᴸᴸ │
│ ᴺᵁᴸᴸ   │ 70   │
└────────┴──────┘

يُستخدم تنسيق البيانات المفصول بعلامات الجدولة على نطاق واسع كتنسيق لتبادل البيانات. لتحميل البيانات من ملف TSV إلى ClickHouse، يُستخدم التنسيق TabSeparated:

clickhouse-client -q "INSERT INTO sometable FORMAT TabSeparated" < data_small.tsv

هناك أيضًا صيغة TabSeparatedWithNames تتيح العمل مع ملفات TSV التي تحتوي على صف عناوين. وكما في CSV، يمكننا تخطي أول X أسطر باستخدام الخيار input_format_tsv_skip_first_lines.

أحيانًا تُحفَظ ملفات TSV من دون إفلات علامات الجدولة وفواصل الأسطر. لذا ينبغي استخدام TabSeparatedRaw للتعامل مع هذه الملفات.

يمكن أيضًا استخدام أي تنسيق ورد في الأمثلة السابقة لتصدير البيانات. ولتصدير البيانات من جدول (أو من استعلام) بتنسيق CSV، نستخدم عبارة FORMAT نفسها:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

لإضافة صفّ رؤوس الأعمدة إلى ملف CSV، نستخدم التنسيق CSVWithNames:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNames

"path","month","hits"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

لحفظ البيانات المُصدَّرة في ملف، يمكن استخدام العبارة INTO…OUTFILE:

SELECT *
FROM sometable
INTO OUTFILE 'out.csv'
FORMAT CSVWithNames

36838935 rows in set. Elapsed: 1.304 sec. Processed 36.84 million rows, 1.42 GB (28.24 million rows/s., 1.09 GB/s.)

لاحظ أن ClickHouse استغرق ~1 ثانية فقط لحفظ 36 مليون صف في ملف CSV.

إذا أردنا استخدام محددات غير الفاصلة، فيمكننا استخدام خيار الإعداد format_csv_delimiter لهذا الغرض:

SET format_csv_delimiter = '|'

الآن سيستخدم ClickHouse الرمز | كمحدد في تنسيق CSV:

SELECT *
FROM sometable
LIMIT 5
FORMAT CSV

"Akiba_Hebrew_Academy"|"2017-08-01"|241
"Aegithina_tiphia"|"2018-02-01"|34
"1971-72_Utah_Stars_season"|"2016-10-01"|1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8"|"2015-12-01"|73
"2016_Greater_Western_Sydney_Giants_season"|"2017-05-01"|86

إذا أردنا أن يعمل ملف CSV بشكل جيد في بيئة Windows، فينبغي مراعاة تفعيل خيار output_format_csv_crlf_end_of_line. سيؤدي ذلك إلى استخدام \r\n كفواصل أسطر بدلًا من \n:

SET output_format_csv_crlf_end_of_line = 1;

في كثير من الحالات، قد نتعامل مع ملفات CSV غير معروفة، لذا نحتاج إلى تحديد الأنواع المناسبة للأعمدة. يحاول ClickHouse، افتراضيًا، استنتاج تنسيقات البيانات بناءً على تحليله لملف CSV معيّن. ويُعرف ذلك باسم "استنتاج المخطط". ويمكن الاطّلاع على أنواع البيانات المكتشفة باستخدام عبارة DESCRIBE بالاقتران مع الدالة file():

DESCRIBE file('data-small.csv', CSV)

┌─name─┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ c1   │ Nullable(String) │              │                    │         │                  │                │
│ c2   │ Nullable(Date)   │              │                    │         │                  │                │
│ c3   │ Nullable(Int64)  │              │                    │         │                  │                │
└──────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

هنا، يستطيع ClickHouse استنتاج أنواع الأعمدة في ملف CSV الخاص بنا بكفاءة. وإذا لم نرد أن يقوم ClickHouse بهذا الاستنتاج، فيمكننا تعطيل ذلك باستخدام الخيار التالي:

SET input_format_csv_use_best_effort_in_schema_inference = 0

ستُعامل جميع أنواع الأعمدة على أنها String في هذه الحالة.

يتيح ClickHouse أيضًا تحديد أنواع الأعمدة صراحةً عند تصدير البيانات باستخدام CSVWithNamesAndTypes (وصيغ WithNames الأخرى ضمن الفئة نفسها):

SELECT *
FROM sometable
LIMIT 5
FORMAT CSVWithNamesAndTypes

"path","month","hits"
"String","Date","UInt32"
"Akiba_Hebrew_Academy","2017-08-01",241
"Aegithina_tiphia","2018-02-01",34
"1971-72_Utah_Stars_season","2016-10-01",1
"2015_UEFA_European_Under-21_Championship_qualification_Group_8","2015-12-01",73
"2016_Greater_Western_Sydney_Giants_season","2017-05-01",86

سيتضمن هذا التنسيق صفَّي ترويسة: أحدهما يتضمن أسماء الأعمدة، والآخر أنواع الأعمدة. يتيح ذلك لـ ClickHouse (وللتطبيقات الأخرى) التعرّف على أنواع الأعمدة عند تحميل البيانات من ملفات مثل هذه:

DESCRIBE file('data_csv_types.csv', CSVWithNamesAndTypes)

┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ path  │ String │              │                    │         │                  │                │
│ month │ Date   │              │                    │         │                  │                │
│ hits  │ UInt32 │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

يحدّد ClickHouse الآن أنواع الأعمدة استنادًا إلى صفّ ترويسةٍ ثانٍ بدلًا من التخمين.

في الحالات المعقدة، يمكن تنسيق البيانات النصية بطريقة مخصصة للغاية مع الاحتفاظ ببنية محددة. يوفّر ClickHouse تنسيق CustomSeparated خاصًا لمثل هذه الحالات، إذ يتيح تعيين قواعد إفلات مخصصة، ومحددات، وفواصل أسطر، ورموز بداية ونهاية.

لنفترض أن لدينا البيانات التالية في الملف:

row('Akiba_Hebrew_Academy';'2017-08-01';241),row('Aegithina_tiphia';'2018-02-01';34),...

يمكننا أن نرى أن الصفوف الفردية مُحاطة بـ row()، وتُفصل الأسطر باستخدام ,، وتُفصل القيم الفردية باستخدام ;. في هذه الحالة، يمكننا استخدام الإعدادات التالية لقراءة البيانات من هذا الملف:

SET format_custom_row_before_delimiter = 'row(';
SET format_custom_row_after_delimiter = ')';
SET format_custom_field_delimiter = ';';
SET format_custom_row_between_delimiter = ',';
SET format_custom_escaping_rule = 'Quoted';

الآن يمكننا تحميل البيانات من ملف بتنسيقنا المخصّص:

SELECT *
FROM file('data_small_custom.txt', CustomSeparated)
LIMIT 3

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

يمكننا أيضًا استخدام CustomSeparatedWithNames لضمان تصدير رؤوس الأعمدة واستيرادها بشكل صحيح. استكشف تنسيقات Regex وTemplate للتعامل مع حالات أكثر تعقيدًا.

قد تكون ملفات CSV كبيرة الحجم، ويعمل ClickHouse بكفاءة مع الملفات مهما كان حجمها. وعادةً ما تكون الملفات الكبيرة مضغوطة، ويتعامل ClickHouse مع ذلك دون الحاجة إلى فك الضغط قبل المعالجة. يمكننا استخدام عبارة COMPRESSION أثناء عملية الإدراج:

INSERT INTO sometable
FROM INFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

إذا لم تُذكر عبارة COMPRESSION، فسيحاول ClickHouse رغم ذلك استنتاج ضغط الملف من امتداده. ويمكن اتباع النهج نفسه لتصدير الملفات مباشرةً بصيغ مضغوطة:

SELECT *
FROM for_csv
INTO OUTFILE 'data_csv.csv.gz'
COMPRESSION 'gzip' FORMAT CSV

سيؤدي ذلك إلى إنشاء الملف المضغوط data_csv.csv.gz.

يدعم ClickHouse العديد من التنسيقات، النصية منها والثنائية، لتلبية سيناريوهات ومنصات متنوعة. استكشف المزيد من التنسيقات وطرق العمل معها في المقالات التالية:

• تنسيقات CSV وTSV
• Parquet
• تنسيقات JSON
• Regex وTemplate
• التنسيقات الأصلية والثنائية
• تنسيقات SQL

واطّلع أيضًا على clickhouse-local - وهي أداة محمولة كاملة الميزات للعمل على الملفات المحلية/البعيدة دون الحاجة إلى خادم ClickHouse.