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

  • مقالة
  • قراءة خلال 7 دقائق

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

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

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

مثال على التشغيل السريع

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

قراءة ملف csv

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

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

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

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

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

Windows نمط خط جديد

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

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

First 10 rows of the CSV file without header, Windows style new line.

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;

خط جديد على غرار يونكس

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

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

First 10 rows of the CSV file without header row and with Unix-Style new line.

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;

صف الرأس

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

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

First 10 rows of the CSV file with header row and with Unix-Style new line.

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 هنا.

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

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

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

First 10 rows of the CSV file with header row and with Unix-Style new line and quoted values.

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 ذات اقتباس مزدوج.

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

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

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

First 10 rows of the CSV file with header row and with Unix-Style new line and escape char used for field delimiter.

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.

الهروب من اقتباس الشخصيات

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

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

The following query shows how to read a file with a header row, with a Unix-style new line, comma-delimited columns, and an escaped double quote char within values.

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';

ملاحظة

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

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

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

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

First 10 rows of the CSV file with header row and with Unix-Style new line and tab delimiter.

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 التجمع سوف تتجاهل فقط بعض الصفوف التي يتم إلحاقها بالملفات أثناء تنفيذ الاستعلام.

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

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