مشاركة عبر

الاستعلام عن ملفات CSV

  • مقالة

في هذه المقالة، ستتعلم كيفية الاستعلام عن ملف CSV واحد باستخدام تجمع SQL بدون خادم في Azure Synapse Analytics. قد تحتوي ملفات CSV على تنسيقات مختلفة:

  • مع صف عناوين وبدونه
  • قيم فواصل وجداول محددة
  • نهايات خطوط أنماط Microsoft وUnix
  • قيم المقتبسات وغير المقتبسات، والأحرف الهاربة

سيتم تغطية جميع الاختلافات المذكورة أعلاه أدناه.

مثال على البدء السريع

OPENROWSETتمكنك خاصية من قراءة محتوى ملف CSV عن طريق تزويد عنوان URL لملفك.

قراءة ملف csv

أسهل طريقة للاطلاع على محتوى الملف CSV هي توفير عنوان URL للوظيفة OPENROWSET ليعمل، وتحديد csv FORMAT، و2.0 PARSER_VERSION. إذا كان الملف متوفرا بشكل عام أو إذا كانت هوية Microsoft Entra الخاصة بك يمكنها الوصول إلى هذا الملف، يجب أن تكون قادرا على رؤية محتوى الملف باستخدام الاستعلام مثل الاستعلام الموضح في المثال التالي:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    format = 'csv',
    parser_version = '2.0',
    firstrow = 2 ) as rows

يستخدم الخيار firstrow لتخطي الصف الأول في ملف CSV الذي يمثل رأس الملف في هذه الحالة. تأكد من أنه يمكنك الوصول إلى هذا الملف. إذا كان الملف محميًا بمفتاح SAS أو هوية مخصصة، فستحتاج إلى ⁧⁩إعداد بيانات التعريف على مستوى الخادم لتسجيل الدخول على SQL⁧⁩.

هام

إذا كان ملف CSV يحتوي على أحرف UTF-8، فتأكد من استخدام ترتيب قاعدة بيانات UTF-8 (على سبيل المثال Latin1_General_100_CI_AS_SC_UTF8). قد يؤدي وجود عدم تطابق بين ترميز النص في الملف والترتيب إلى أخطاء غير متوقعة في التحويل. يمكنك بسهولة تغيير الترتيب الافتراضي لقاعدة البيانات الحالية باستخدام عبارة T-SQL التالية: alter database current collate Latin1_General_100_CI_AI_SC_UTF8

استخدام مصادر البيانات

يستخدم المثال السابق المسار الكامل للملف. كبديل، يمكنك إنشاء مصدر بيانات خارجي مع الموقع الذي يشير إلى المجلد الجذر للتخزين:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );

بمجرد إنشاء مصدر بيانات يمكنك استخدام مصدر البيانات هذا والمسار النسبي للملف في وظيفة OPENROWSET:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.csv',
        data_source = 'covid',
        format = 'csv',
        parser_version ='2.0',
        firstrow = 2
    ) as rows

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

تحديد المخطط بشكل صريح

يمكّنك OPENROWSET من التحديد الصريح للأعمدة التي تريد قراءتها من الملف باستخدام عبارة WITH:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.csv',
        data_source = 'covid',
        format = 'csv',
        parser_version ='2.0',
        firstrow = 2
    ) with (
        date_rep date 1,
        cases int 5,
        geo_id varchar(6) 8
    ) as rows

تمثل الأرقام بعد نوع بيانات في العبارة WITH فهرس عمود في ملف CSV.

هام

إذا كان ملف CSV يحتوي على أحرف UTF-8، فتأكد من تحديد بعض ترتيب UTF-8 بشكل صريح (على سبيل المثال Latin1_General_100_CI_AS_SC_UTF8) لجميع الأعمدة في WITH عبارة أو تعيين بعض ترتيب UTF-8 على مستوى قاعدة البيانات. قد يؤدي عدم التطابق بين ترميز النص في الملف والترنيم إلى حدوث أخطاء تحويل غير متوقعة. يمكنك بسهولة تغيير الترتيب الافتراضي لقاعدة البيانات الحالية باستخدام عبارة T-SQL التالية: alter database current collate Latin1_General_100_CI_AI_SC_UTF8 يمكنك بسهولة تعيين الترتيب على أنواع الأعمدة باستخدام التعريف التالي: geo_id varchar(6) collate Latin1_General_100_CI_AI_SC_UTF8 8

في الأقسام التالية، يمكنك مشاهدة كيفية الاستعلام عن أنواع مختلفة من ملفات CSV.

المتطلبات الأساسية

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

سطر جديد بنمط Windows

يوضح الاستعلام التالي كيفية قراءة ملف CSV بدون صف رأس، بسطر جديد بنمط Windows وأعمدة مفصولة بفواصل.

معاينة الملف:

أول 10 صفوف من ملف CSV بدون عنوان، Windows نمط جديد. 

SELECT *
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

سطر جديد بنمط Unix

يوضح الاستعلام التالي كيفية قراءة ملف بدون صف رأس، وبسطر جديد بنمط Unix وبأعمدة مفصولة بفواصل. لاحظ الموقع المختلف للملف مقارنة بالأمثلة الأخرى.

معاينة الملف:

أول 10 صفوف من ملف CSV بدون صف رأس وبسطر جديد من نمط Unix. 

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

صف الرأس

يوضح الاستعلام التالي كيفية قراءة ملف يحتوي على صف رأس، وبسطر جديد بنمط Unix وبأعمدة مفصولة بفواصل. لاحظ الموقع المختلف للملف مقارنة بالأمثلة الأخرى.

معاينة الملف:

أول 10 صفوف من ملف CSV مع صف الرأس ومع سطر جديد من نمط Unix. 

SELECT *
FROM OPENROWSET(
    BULK 'csv/population-unix-hdr/population.csv',
    DATA_SOURCE = 'SqlOnDemandDemo',
    FORMAT = 'CSV', PARSER_VERSION = '2.0',
    FIELDTERMINATOR =',',
    HEADER_ROW = TRUE
    ) AS [r]

سيؤدي الخيار HEADER_ROW = TRUE إلى قراءة أسماء الأعمدة من صف الرأس في الملف. إنه أمر رائع لأغراض الاستكشاف عندما لا تكون على دراية بمحتوى الملف. للحصول على أفضل أداء راجع استخدام أنواع البيانات المناسبة المقطع في أفضل الممارسات. أيضاً، يمكنك قراءة المزيد حول بناء جملة OPENROWSET هنا.

حرف اقتباس مخصص

يوضح الاستعلام التالي كيفية قراءة ملف يحتوي على صف رأس، مع سطر جديد بنمط Unix، وأعمدة مفصولة بفواصل، وقيم مقتبسة. لاحظ الموقع المختلف للملف مقارنة بالأمثلة الأخرى.

معاينة الملف:

أول 10 صفوف من ملف CSV مع صف الرأس ومع سطر جديد وقيم مقتبسة بنمط Unix. 

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-quoted/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2,
        FIELDQUOTE = '"'
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

إشعار

هذا الاستعلام سيرجع نفس النتائج إذا حذفت المعلمة FIELDQUOTE نظرًا إلى أن القيمة الافتراضية لـ FIELDQUOTE ذات اقتباس مزدوج.

شخصيات الهروب

يوضح الاستعلام التالي كيفية قراءة ملف بصف رأس، وبسطر جديد بنمط Unix وأعمدة محددة بفواصل وحرف إلغاء يُستخدم لمحدد الحقل (فاصلة) ضمن القيم. لاحظ الموقع المختلف للملف مقارنة بالأمثلة الأخرى.

معاينة الملف:

أول 10 صفوف من ملف CSV مع صف الرأس ومع سطر جديد بنمط Unix وحرف الإلغاء المستخدم لمحدد الحقل. 

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-escape/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2,
        ESCAPECHAR = '\\'
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Slovenia';

إشعار

هذا الاستعلام سيفشل إذا لم يتم تحديد ESCAPECHAR نظرًا إلى أنه تتم معاملة الفاصلة في "Slov, enia" كمحدد الحقل بدلاً من جزء من اسم البلد/المنطقة. "Slov,enia" يتم التعامل معها كعمودين. لذلك، سيكون للصف المعين عمود واحد أكثر من الصفوف الأخرى، وعمود واحد أكثر مما حددته في جملة WITH.

إلغاء أحرف الاقتباس

يوضح الاستعلام التالي كيفية قراءة ملف بصف عنوان وسطر جديد بنمط Unix وأعمدة محددة بفواصل وحرف اقتباس مزدوج مُلغى ضمن القيم. لاحظ الموقع المختلف للملف مقارنة بالأمثلة الأخرى.

معاينة الملف:

يوضح الاستعلام التالي كيفية قراءة ملف بصف عنوان وسطر جديد بنمط Unix وأعمدة محددة بفواصل وحرف اقتباس مزدوج مُلغى ضمن القيم. 

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-escape-quoted/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Slovenia';

إشعار

يجب أن يكون حرف الاقتباس تم إلغاؤه بحرف اقتباس آخر. يمكن أن يظهر حرف اقتباس داخل قيمة العمود فقط إذا كانت القيمة مغلفة بأحرف اقتباس.

الملفات المحددة بعلامات التبويب

يوضح الاستعلام التالي كيفية قراءة ملف يحتوي على صف رأس وسطر جديد بنمط Unix وأعمدة محددة بعلامات جدولة. لاحظ الموقع المختلف للملف مقارنة بالأمثلة الأخرى.

معاينة الملف:

أول 10 صفوف من ملف CSV يحتوي على صف رأس وسطر جديد بنمط Unix ومحدد بعلامة جدولة. 

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-tsv/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR ='\t',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017

قم بإرجاع مجموعة فرعية من الأعمدة

حتى الآن، لقد قمت بتحديد مخطط ملف CSV باستخدام WITH وعرض كافة الأعمدة. يمكنك فقط تحديد الأعمدة التي تحتاجها بالفعل في الاستعلام الخاص بك باستخدام رقم ترتيبي لكل عمود مطلوب. كما ستحذف أعمدة ليست ذات أهمية.

يقوم الاستعلام التالي بإرجاع عدد أسماء البلدان/المناطق المميزة في ملف مع تحديد الأعمدة المطلوبة فقط:

إشعار

ألق نظرة على عبارة WITH في الاستعلام أدناه ولاحظ أن هناك "2" (بدون علامات اقتباس) في نهاية الصف حيث تحدد عمود [country_name]. وهذا يعني أن عمود [country_name] هو العمود الثاني في الملف. سيتجاهل الاستعلام كافة الأعمدة في الملف باستثناء الثاني.

SELECT
    COUNT(DISTINCT country_name) AS countries
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
    )
WITH (
    --[country_code] VARCHAR (5),
    [country_name] VARCHAR (100) 2
    --[year] smallint,
    --[population] bigint
) AS [r]

الاستعلام عن الملفات القابلة للإلحاق

يجب عدم تغيير ملفات CSV المستخدمة في الاستعلام أثناء تشغيل الاستعلام. في الاستعلام الذي يتم تشغيله لفترة طويلة، قد يقوم مجمع SQL بإعادة محاولة القراءة أو قراءة أجزاء من الملفات أو حتى قراءة الملف عدة مرات. قد تؤدي التغييرات في محتوى الملف إلى نتائج خاطئة. لذلك، يفشل تجمع SQL في الاستعلام إذا اكتشف تغيير وقت التعديل لأي ملف أثناء تنفيذ الاستعلام.

في بعض السيناريوهات، قد ترغب في قراءة الملفات الملحقة باستمرار. لتجنب فشل الاستعلام بسبب الملفات الملحقة باستمرار، يمكنك السماح OPENROWSETللوظيفة بتجاهل القراءات غير المتسقة المحتملة باستخدامROWSET_OPTIONS الإعداد.

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    format = 'csv',
    parser_version = '2.0',
    firstrow = 2,
    ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}') as rows

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

الخطوات التالية

ستوضح لك المقالات التالية كيفية: