دالة الجدول s3

يوفّر واجهة شبيهة بالجداول للاستعلام عن الملفات وإدراجها في Amazon S3 وGoogle Cloud Storage. تشبه دالة الجدول هذه دالة hdfs، لكنها توفّر ميزات خاصة بـ S3.

إذا كان لديك عدة نسخ متماثلة في عنقودك، فيمكنك استخدام دالة s3Cluster بدلًا من ذلك لموازاة عمليات الإدراج.

عند استخدام s3 table function مع INSERT INTO...SELECT، تُقرأ البيانات وتُدرج بأسلوب تدفقي. ولا يبقى في الذاكرة إلا عدد قليل من كتل البيانات، بينما تُقرأ الكتل باستمرار من S3 وتُرسَل إلى الجدول الوجهة.

s3(url [, NOSIGN | access_key_id, secret_access_key, [session_token]] [,format] [,structure] [,compression_method],[,headers], [,extra_credentials], [,partition_strategy], [,partition_columns_in_data_file])
s3(named_collection[, option=value [,..]])

المعلمات

تدعم دالة الجدول s3 المعلمات البسيطة التالية:

يمكن أيضًا تمرير الوسيطات باستخدام named collections. في هذه الحالة، تعمل url وaccess_key_id وsecret_access_key وformat وstructure وcompression_method بالطريقة نفسها، وتكون بعض المعلمات الإضافية مدعومة:

جدول ذو البنية المحددة لقراءة البيانات أو كتابتها في الملف المحدد.

تحديد أول 5 صفوف من الجدول من ملف S3 ‏https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv:

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

┌───────Date─┬────Open─┬────High─┬─────Low─┬───Close─┬───Volume─┬─OpenInt─┐
│ 1984-09-07 │ 0.42388 │ 0.42902 │ 0.41874 │ 0.42388 │ 23220030 │       0 │
│ 1984-09-10 │ 0.42388 │ 0.42516 │ 0.41366 │ 0.42134 │ 18022532 │       0 │
│ 1984-09-11 │ 0.42516 │ 0.43668 │ 0.42516 │ 0.42902 │ 42498199 │       0 │
│ 1984-09-12 │ 0.42902 │ 0.43157 │ 0.41618 │ 0.41618 │ 37125801 │       0 │
│ 1984-09-13 │ 0.43927 │ 0.44052 │ 0.43927 │ 0.43927 │ 57822062 │       0 │
└────────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘

لنفترض أن لدينا عدة ملفات بعناوين URI التالية على S3:

• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_1.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_2.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_3.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/some_prefix/some_file_4.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_1.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_2.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_3.csv'
• 'https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/another_prefix/some_file_4.csv'

احسب عدد الصفوف في الملفات التي تنتهي بأرقام من 1 إلى 3:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

احسب العدد الإجمالي للصفوف في جميع الملفات الموجودة في هذين الدليلين:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/{some,another}_prefix/*', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32')

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

احسب العدد الإجمالي للصفوف في الملفات المسماة file-1.csv، …، file-4.csv:

SELECT count(*)
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/my-test-bucket-768/big_prefix/file-{1..4}.csv', NOSIGN, 'CSV', 'column1 UInt32, column2 UInt32, column3 UInt32');

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

أدرج البيانات في الملف test-data.csv.gz:

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
VALUES ('test-data', 1), ('test-data-2', 2);

أدرج البيانات في الملف test-data.csv.gz من جدول موجود:

INSERT INTO FUNCTION s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip')
SELECT name, value FROM existing_table;

يمكن استخدام ** في Glob للاجتياز التعاودي للأدلة. انظر المثال أدناه، إذ سيجلب جميع الملفات من دليل my-test-bucket-768 بشكل تعاودي:

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

يجلب ما يلي البيانات من جميع ملفات test-data.csv.gz الموجودة في أي دليل داخل دليل my-test-bucket بشكل تعاودي:

SELECT * FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

ملاحظة. يمكن تحديد محوّلات URL مخصصة في ملف تكوين الخادم. مثال:

SELECT * FROM s3('s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz', NOSIGN, 'CSV', 'name String, value UInt32', 'gzip');

سيُستبدل URL ‏'s3://clickhouse-public-datasets/my-test-bucket-768/**/test-data.csv.gz' بـ 'http://clickhouse-public-datasets.s3.amazonaws.com/my-test-bucket-768/**/test-data.csv.gz'

يمكن إضافة محوّل مخصّص إلى config.xml:

<url_scheme_mappers>
   <s3>
      <to>https://{bucket}.s3.amazonaws.com</to>
   </s3>
   <gs>
      <to>https://{bucket}.storage.googleapis.com</to>
   </gs>
   <oss>
      <to>https://{bucket}.oss.aliyuncs.com</to>
   </oss>
</url_scheme_mappers>

في حالات الاستخدام في بيئة الإنتاج، يُوصى باستخدام named collections. وفيما يلي مثال على ذلك:

CREATE NAMED COLLECTION creds AS
        access_key_id = '***',
        secret_access_key = '***';
SELECT count(*)
FROM s3(creds, url='https://s3-object-url.csv')

مدعومة فقط لاستعلامات INSERT.

wildcard: يستبدل حرف البدل {_partition_id} في مسار الملف بمفتاح التقسيم الفعلي. ويُحدَّد افتراضيًا عندما يحتوي المسار على {_partition_id}.

عند عدم تعيين partition_strategy، لا يستخدم المسار الذي يحتوي على نمط glob آخر أي استراتيجية للتقسيم ويتجاهل PARTITION BY. ويستخدم المسار الذي لا يحتوي على glob استراتيجية hive عندما تكون قيمة file_like_engine_default_partition_strategy هي hive؛ وإلا فلا يستخدم أي استراتيجية للتقسيم.

hive يطبّق التقسيم بنمط Hive على عمليات القراءة والكتابة. ويُنشئ الملفات باستخدام التنسيق التالي: <prefix>/<key1=val1/key2=val2...>/<snowflakeid>.<toLower(file_format)>.

مثال على استراتيجية التقسيم hive

INSERT INTO FUNCTION s3(s3_conn, filename='t_03363_function', format=Parquet, partition_strategy='hive') PARTITION BY (year, country) SELECT 2020 as year, 'Russia' as country, 1 as id;

SELECT _path, * FROM s3(s3_conn, filename='t_03363_function/**.parquet');

   ┌─_path──────────────────────────────────────────────────────────────────────┬─id─┬─country─┬─year─┐
1. │ test/t_03363_function/year=2020/country=Russia/7351295896279887872.parquet │  1 │ Russia  │ 2020 │
   └────────────────────────────────────────────────────────────────────────────┴────┴─────────┴──────┘

أمثلة على استراتيجية التقسيم wildcard

1. يؤدي استخدام معرّف القسم في المفتاح إلى إنشاء ملفات منفصلة:

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket/file_{_partition_id}.csv', 'CSV', 'a String, b UInt32, c UInt32', partition_strategy='wildcard')
    PARTITION BY a VALUES ('x', 2, 3), ('x', 4, 5), ('y', 11, 12), ('y', 13, 14), ('z', 21, 22), ('z', 23, 24);

ونتيجةً لذلك، تُكتَب البيانات في ثلاثة ملفات: file_x.csv وfile_y.csv وfile_z.csv.

2. يؤدّي استخدام معرّف القسم ضمن اسم حاوية التخزين إلى إنشاء ملفات في حاويات تخزين مختلفة:

INSERT INTO TABLE FUNCTION
    s3('http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv', 'CSV', 'a UInt32, b UInt32, c UInt32', partition_strategy='wildcard')
    PARTITION BY a VALUES (1, 2, 3), (1, 4, 5), (10, 11, 12), (10, 13, 14), (20, 21, 22), (20, 23, 24);

ونتيجةً لذلك، تُكتب البيانات في ثلاثة ملفات ضمن حاويات تخزين مختلفة: my_bucket_1/file.csv وmy_bucket_10/file.csv وmy_bucket_20/file.csv.

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

SELECT *
FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

بالنسبة إلى حاويات التخزين غير العامة، يمكن للمستخدمين تمرير aws_access_key_id وaws_secret_access_key إلى الدالة. على سبيل المثال:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv', '<KEY>', '<SECRET>','TSVWithNames')

يُعد هذا مناسبًا لعمليات الوصول لمرة واحدة أو في الحالات التي يمكن فيها تغيير بيانات الاعتماد بسهولة. ومع ذلك، لا يُنصح به كحل طويل الأمد لعمليات الوصول المتكررة أو عندما تكون بيانات الاعتماد حساسة. في هذه الحالة، نوصي بأن يعتمد المستخدمون على الوصول المستند إلى الأدوار.

يَرِد توثيق الوصول المستند إلى الأدوار لـ S3 في ClickHouse Cloud هنا.

بعد الإعداد، يمكن تمرير roleARN إلى الدالة s3 عبر المعلمة extra_credentials. على سبيل المثال:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'))

يمكن أيضًا تمرير external_id اختياريًا إلى جانب role_arn. ويُمرَّر كمعلمة ExternalId في استدعاء AWS STS AssumeRole، ويتيح لسياسة الثقة الخاصة بالدور أن تشترط سرًا مشتركًا، مما يحدّ من مشكلة النائب المرتبك. على سبيل المثال:

SELECT count() FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/mta/*.tsv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001', external_id = 'my-external-id'))

يمكنك العثور على المزيد من الأمثلة هنا

لنفترض أن لدينا عدة ملفات أرشيف بعناوين URI التالية على S3:

• 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-10.csv.zip'
• 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-11.csv.zip'
• 'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-12.csv.zip'

يمكن استخراج البيانات من هذه الأرشيفات باستخدام ::. ويمكن استخدام أنماط globs سواء في جزء URL أو في الجزء الذي يلي :: (وهو المسؤول عن اسم الملف داخل الأرشيف).

SELECT *
FROM s3(
   'https://s3-us-west-1.amazonaws.com/umbrella-static/top-1m-2018-01-1{0..2}.csv.zip :: *.csv',
   NOSIGN
);

لاحظ أن الصفوف لا يمكن إدراجها إلا في الملفات الجديدة. ولا توجد دورات دمج أو عمليات تقسيم للملفات. وما إن يُكتَب ملف حتى تفشل عمليات الإدراج اللاحقة. اطّلع على مزيد من التفاصيل هنا.

• _path — مسار الملف. النوع: LowCardinality(String). في حالة الأرشيف، يعرض المسار بالتنسيق: "{path_to_archive}::{path_to_file_inside_archive}"
• _file — اسم الملف. النوع: LowCardinality(String). في حالة الأرشيف، يعرض اسم الملف داخل الأرشيف.
• _size — حجم الملف بالبايت. النوع: Nullable(UInt64). إذا كان حجم الملف غير معروف، تكون القيمة NULL. في حالة الأرشيف، يعرض حجم الملف غير المضغوط للملف الموجود داخل الأرشيف.
• _time — وقت آخر تعديل للملف. النوع: Nullable(DateTime). إذا كان الوقت غير معروف، تكون القيمة NULL.

هذا تلميح لـ ClickHouse لتحليل الملفات المُقسَّمة بنمط Hive عند القراءة. ولا يؤثر في الكتابة. ولتحقيق تماثل بين القراءة والكتابة، استخدم الوسيطة partition_strategy.

عند ضبط use_hive_partitioning على القيمة 1، سيكتشف ClickHouse التقسيم بنمط Hive في المسار (/name=value/) وسيتيح استخدام أعمدة التقسيم بوصفها أعمدة افتراضية في الاستعلام. وستحمل هذه الأعمدة الافتراضية الأسماء نفسها الموجودة في المسار المُقسَّم.

مثال

SELECT * FROM s3('s3://data/path/date=*/country=*/code=*/*.parquet') WHERE date > '2020-01-01' AND country = 'Netherlands' AND code = 42;

للوصول إلى حاوية التخزين requester-pays، يجب تمرير الترويسة x-amz-request-payer = requester مع أي طلب. ويتم ذلك بتمرير المعلمة headers('x-amz-request-payer' = 'requester') إلى الدالة s3. على سبيل المثال:

SELECT
    count() AS num_rows,
    uniqExact(_file) AS num_files
FROM s3('https://coiled-datasets-rp.s3.us-east-1.amazonaws.com/1trc/measurements-100*.parquet', 'AWS_ACCESS_KEY_ID', 'AWS_SECRET_ACCESS_KEY', headers('x-amz-request-payer' = 'requester'))

┌

يتيح الإعداد s3_base تمرير عنوان URL نسبي إلى الدالة s3. عند تعيين s3_base وعدم احتواء وسيطة الدالة على مخطط، يُحل عنوان URL بالاستناد إلى عنوان URL الأساسي وفقًا لـ RFC 3986، باستخدام القواعد نفسها التي يستخدمها الإعداد url_base في الدالة url. وتُمرَّر عناوين URL المطلقة دون تغيير.

ينطبق هذا الإعداد أيضًا على محرك الجدول S3 ودوال الجدول التي تشترك في تهيئة s3 (s3Cluster وgcs وoss). بالنسبة إلى محرك الجدول S3، يُجسَّد عنوان URL المحلول ضمن تعريف الجدول المخزَّن، لذا لا يعتمد الجدول على قيمة s3_base بعد إنشائه.

مثال

SET s3_base = 's3://clickhouse-public-datasets/';
SELECT count() FROM s3('hits_compatible/hits.csv', NOSIGN);

• s3_truncate_on_insert - يتيح تفريغ الملف قبل الإدراج فيه. معطّل افتراضيًا.
• s3_create_new_file_on_insert - يتيح إنشاء ملف جديد عند كل عملية إدراج إذا كان للتنسيق لاحقة. معطّل افتراضيًا.
• s3_skip_empty_files - يتيح تخطي الملفات الفارغة أثناء القراءة. مفعّل افتراضيًا.
• s3_base - عنوان URL الأساسي لحل عناوين URL النسبية المُمرَّرة إلى الدالة s3. فارغ (معطّل) افتراضيًا.

عند قراءة ملفات Avro التي تحتوي على سجلات متداخلة تختلف من ملف إلى آخر (على سبيل المثال، تحتوي بعض الملفات على حقل إضافي داخل كائن متداخل)، فقد يُرجع ClickHouse خطأً مثل:

عدد الأوراق في السجل لا يطابق عدد العناصر في Tuple…

يحدث ذلك لأن ClickHouse يتوقع أن تتطابق جميع بُنى السجلات المتداخلة مع المخطط نفسه. للتعامل مع هذه الحالة، يمكنك:

• استخدام schema_inference_mode='union' لدمج مخططات السجلات المتداخلة المختلفة، أو
• مواءمة البُنى المتداخلة يدويًا وتمكين use_structure_from_insertion_table_in_table_functions=1.

مثال

INSERT INTO data_stage
SELECT
    id,
    data
FROM s3('https://bucket-name/*.avro', 'Avro')
SETTINGS schema_inference_mode='union';

• S3 engine
• Integrating S3 with ClickHouse