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

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

13/03/2011 | פורסם על ידי

היום התבקשתי על ידי אחת המפתחות לעזור לה לשמור שליפה של 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: מנטרלת הצגה למסך אם הסקריפט שנריץ יורץ על ידי שימוש ב-@ או ב-@@.
שורה 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; 17 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

נתראה ברשומה הבאה !

זהר אלקיים
לרשומה המקורית בבלוג של זהר: יצירת CSV על ידי שימוש ב-SQLPlus.

The following two tabs change content below.
זהר אלקיים הוא Oracle DBA מאז 1998 ויועץ בכיר מאז 2007 ובעל תואר Oracle ACE Associate. הוא מתמחה בתשתיות בסיסי נתונים, ארכיטקטורה ושיפור ביצועים. זהר הוא גם מדריך, מרצה בכנסים ובלוגר באתר www.realdbamagic.com מאז 2010. כיום הוא משמש כיום כ-CTO בחברת בריליקס ומנהל את האתר ilDBA.

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

הפונקציה COALESCE

כולם מכירים את הפונקציה NVL, ומשתמשים בה לא מעט. לעומת זאת, הפונקציה COALESCE, השייכת לאותה משפחה של פונקציות, פחות מוכרת, למרות שהיא קיימת כבר מגרסה 9i ועולה ביכולותיה על NVL הישנה והטובה. מצד שני, אף [...]

טיפ: איך לזכור ב-SQLPLUS שאתה בסביבת היצור

כמה פעמים קרה לנו שעבדנו עם sqlplus  מול סביבת יצור ביחד עם עוד כמה חלונות פתוחים ועם קצת לחץ? בקיצור כרגיל… מכוון שהכול כל כך לחוץ, הרצנו איזה פקודה הרסנית, ומיד הצטערנו עליה מאוד: אוי! הרצתי [...]
עבודה

עבודה תחת סביבת FireWall

אורי לרנר בטיפ קצר ושימושי על עבודה בסביבת אורקל [...]
Indexes

Indexes in SQL Server 2

ערן שקד ממשיך בסדרת מאמרים על אינדקסים בSQL [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: