יצירת קובץ CSV על ידי שימוש ב-SQLPLUS

היום התבקשתי על ידי אחת המפתחות לעזור לה לשמור שליפה של 230 עמודות ולמעלה מ-50 מליון רשומות לקובץ CSV. מבחינתי זו הזדמנות טובה לתעד את זה המסקנות שלי מהבעיה והפתרון היחסית גנרי שיצרתי בשבילו.

שליפות ב-sqlplus מחזירות בדרך כלל את הרשומות באחד משתי דרכים: הדרך הראשונה היא fixed length columns (כלומר העמודה הראשונה מתחילה תמיד במיקום 1, העמודה השנייה תמיד במיקום 7 וכן הלאה). הדרך השנייה היא כ-HTML ועל זה נדבר בהזדמנות אחרת. קובץ CSV הוא בסופו של דבר קובץ טקסט לכל דבר שבו העמודות (ערכים) מופרדים על ידי פסיקים (ומכאן שמו: Comma-separated values).

הכי פשוט היה אם היה אפשר לשלוף את הנתונים ב-sqlplus, לעשות להם spool ושלום על ישראל אבל לצערי זה לא עובד בצורה פשוטה שכזו .לשמחתי ישנן כמה וכמה דרכים להתגבר על זה. כדי למצוא פתרון חקרתי במספר כיוונים – חלק מהמסקנות היו טובות באופן כללי וחלק היו טובות למקרים מסויימים אבל לא למקרה הזה.

מסקנה ראשונה:

ניתן להשתמש במאפיינים שונים של sqlplus כדי ליצור קובץ נקי ומבלי להציג אותו למסך מה שמאיץ את העבודה כי אין צורך להמתין לכתיבה למסך.

set termout off
set feedback off
set timi off
set lines 30000
set trims on
set pages 0
set arrays 500

שורה 1: מנטרלת הצגה למסך אם הסקריפט שנריץ יורץ על ידי שימוש ב[email protected] או ב[email protected]@.
שורה 2: מנטרלת את ה-feedback שאומר כמה שורות נשלפו והודעות שגיאה.
שורה 3: ביטול תזמון שליפות.
שורה 4: הגדלת גודל השורה עד ל-30 אלף תווים (כמעט המקסימום).
שורה 5: מחיקת רווחים בסוף שורה כאשר עושים spool.
שורה 6: ביטול כותרות העמודות.
שורה 7: הגדלת גודל המערך של החזרת הרשומות ל-500 ערכים. אפשר לשחק עם ערך זה בהתאם לאורך הרשומה והמיקום שממנו מריצים את השליפה. 100 יכול להתאים למרבית המקרים וזה גם ערך ברירת המחדל החל מגרסה 10.

הפרמטרים האלה טובים לכל פתרון לבעיה שאני אציג בהמשך.

מסקנה שנייה:

ניתן להשתמש במאפיין של sqlplus כדי להוסיף את הפסיק בסוף כל עמודה:

set colsep ,

החסרון בפתרון הזה הוא שהוא לא משנה את העובדה שהשליפה עדיין מחזירה תוצאה בתור fixed length ו-sqlplus ימשיך לדפן את הערכים ברווחים. במקרה של הבעיה הנתונה ההבדל היה של מאות ג’יגות בקובץ ה-output. הפתרון הזה מעולה כאשר יש צורך במספר קטן של עמודות ושורות ואז ניתן לייבא את הקובץ לאקסל בקלות.

מסקנה שלישית:

אני מניח שזה הפתרון שבסופו של דבר רוב האנשים מגיעים אליו – שירשור העמודות עם פסיקים כך שבסוף מתקבלת עמודה אחת ארוכה ביותר עם ערכים המופרדים על ידי פסיקים. הבעיה במקרה הזה היא איך לייצר את השליפה בלי יותר מדי מאמץ (אני מזכיר לכם, במקרה שלי היה מדובר בשליפה עם 250 עמודות).
כדי לפתור את הבעיה החלטתי ליצור מהשליפה שביקשה המפתחת באופן זמני view ולהריץ סקריפט קצר אך שימושי שייצר לי את השליפה מה-view. ניתן באותה צורה לייצא ככה כל טבלה בבסיס הנתונים.

declare
    line_cmd     clob;
    p_view_owner varchar2(40) := 'SCOTT';
    p_view_name  varchar2(40) := 'FOR_MIC1';
    p_delimiter  varchar2(5) := '¿';
begin
    line_cmd := 'select';
    for i in (select table_name || '.' || column_name || ' || ''' || p_delimiter || ''' ||' col
                from dba_tab_columns
               where table_name = upper(p_view_name)
                 and owner = upper(p_view_owner)
               order by COLUMN_ID)
    loop
        dbms_output.put_line(line_cmd);
        line_cmd := i.col;
    end loop;
    line_cmd := trim(trailing ' || ''' || p_delimiter || ''' ||' from line_cmd);
    dbms_output.put_line(line_cmd);
    line_cmd := 'from ' || p_view_owner || '.' || p_view_name || ';';
    dbms_output.put_line(line_cmd);
end;
/

לאחר מכן, שמתי את השליפה שיוצרה על ידי הסקריט הזה בתוך סקריפט משלו ביחד עם הפרמטרים ל-sqlplus שתיארתי קודם והרצתי את הסקריפט עם spool כדי ליצור לי את הקובץ הסופי.

בין הפתרונות הנוספים שראיתי בדרך היה יצור של קובץ HTML והסבתו מאוחר יותר ל-CSV (ישנן שיטות רבות לבצע את זה) אבל יצירת קובץ HTML מגדילה את גודל הקובץ ולא מצמצמת אותו אז ויתרתי על הרעיון די בתחילת הדרך.

נקודות נוספות שכדאי לשים אליהן לב

  • יש לשים לב שבערכים שבטבלה אין פסיקים – דבר שישבש את הקובץ.
  • יש לסכם מראש עם הצרכן של הקובץ את הפורמט של התאריך ולטפל בזה בשליפה לקובץ.
  • למרות הניסיון שלא להשאיר “שאריות” בקובץ, יתכן עדיין שיהיה צורך לערוך את הקובץ אחר כך ולבטל את השורות המיותרות (בדרך כלל של הפעלת השליפה וסיום ה-spool). אם רוצים להימנע מזה, יש להריץ את הסקריפט על ידי הפקודה הבאה:
    sqlplus -S [username]/[password]@[SID] @make_csv_for_mic.sql
    

    יש לכם מה להוסיף? אני אשמח לשמוע! 🙂

  • עדכון: שיניתי את הקוד כך שידע לקבל כל delimiter שנרצה כדי להתגבר על הבעיה שבנקודה הראשונה.

    8 תגובות
      • Zohar Elkayam
        Zohar Elkayam says:

        מיכאל,
        נראה לי שלא ירדתי לסוף דעתך – בהנחה שזה סתם CSV אז לגרשים אין משמעות מיוחדת גם אם הם מופיעים בתוך ערכים.
        אם מחליטים מראש שעוטפים כל ערך מחרוזת (string) בגרשים כדי לפתור את בעיית המפריד בתוך ערך אז באמת צריך לטפל בהם בצורה מיוחדת.

        בפתרון שנתתי למפתחת במקרה הזה פשוט בחרנו במפריד שבוודאות לא קיים בערכים. השתמשנו בתו ¿ (סימן שאלה הפוך – ASCII 168) אבל באותה מידה יכולנו להשתמש במפריד אחר (~) או ברצף של ערכים למפריד (~,~ לדוגמה).
        זהר.

        הגב

    Trackbacks & Pingbacks

    1. […] כמה שבועות כתבתי פוסט בנושא יצירת קובץ CSV על ידי שימוש בשליפה (כלומר מתוך sqlplus). הפוסט התגלה […]

    2. […] פעם, מזמן מזמן, כתבתי על איך ניתן לייצר קבצי CSV על ידי שימוש בפיצ'רים של SQL*Plus ואחרי זה כתבתי עוד פוסט על איך ניתן לייצר קבצי CSV על ידי […]

    3. יצירת קובץ CSV על ידי שימוש ב-SQLPLUS…

      במאמר זה נלמד איך ליצור קובץ CSV על ידי שימוש ב-SQLPLUS……

    4. […] כמה שבועות כתבתי פוסט בנושא יצירת קובץ CSV על ידי שימוש בשליפה (כלומר מתוך sqlplus). הפוסט התגלה […]

    השאירו תגובה

    Want to join the discussion?
    Feel free to contribute!

    השאר תגובה

    This site uses Akismet to reduce spam. Learn how your comment data is processed.