ייצוא תוצאות של שאילתה לקובץ

במאמר הזה מוסבר איך לשמור תוצאות של שאילתה כקובץ, כמו CSV או JSON.

מגבלות

‫BigQuery לא תומך בייצוא נתונים מקוננים או חוזרים (RECORD / STRUCT או סוגי נתונים של ARRAY) לפורמט CSV. גם אם שדות RECORD או STRUCT לא נראים מוטמעים עמוק, BigQuery מתייחס אליהם כשדות מוטמעים בסכימה ומחזיר שגיאה שמציינת שלא ניתן להטמיע את הסכימה.

כדי לייצא את תוצאות השאילתה לקובץ CSV מובנה, צריך לנרמל את השאילתה למבנה שטוח עם שדה אחד לכל עמודה בשאילתה. אפשר לנרמל מבני struct מוטמעים על ידי בחירת שדות בודדים באמצעות סימון נקודות, או לנרמל מערכים חוזרים באמצעות האופרטור UNNEST.

לדוגמה, אם הטבלה מכילה את המבנה customer ואת המערך items, צריך לנרמל את השאילתה לפני שמריצים אותה ושומרים את התוצאות בקובץ CSV:

SELECT
  customer.id AS customer_id,
  customer.name AS customer_name,
  item
FROM
  `my_project.my_dataset.my_table`,
  UNNEST(items) AS item;

הורדת תוצאות של שאילתות לקובץ מקומי

אי אפשר להוריד תוצאות של שאילתות לקובץ מקומי באמצעות כלי שורת הפקודה bq או ה-API.

כדי להוריד את תוצאות השאילתה כקובץ CSV או כקובץ JSON עם תווי שורה חדשה, משתמשים בGoogle Cloud מסוף:

  1. נכנסים לדף BigQuery במסוף Google Cloud .

    כניסה ל-BigQuery

  2. לוחצים על שאילתת SQL.

  3. מזינים שאילתת GoogleSQL תקינה באזור הטקסט של עורך השאילתות.

  4. אופציונלי: כדי לשנות את מיקום העיבוד, לוחצים על עריכה > הגדרות שאילתה. בקטע Data location (מיקום הנתונים), בוחרים את המיקום של הנתונים.

  5. לוחצים על Run.

  6. אחרי שהתוצאות מוצגות, לוחצים על שמירת התוצאות ובוחרים את הפורמט או המיקום שבו רוצים לשמור את התוצאות.

    הקובץ יורד למיקום ברירת המחדל להורדות בדפדפן.

שמירת תוצאות של שאילתה ב-Google Drive

אי אפשר לשמור תוצאות של שאילתות ב-Google Drive באמצעות כלי שורת הפקודה bq או ה-API.

יכול להיות שתקבלו שגיאה כשאתם מנסים לשמור את התוצאות של BigQuery ב-Google Drive. השגיאה הזו נגרמת בגלל ש-Drive SDK API לא יכול לגשת ל-Google Workspace. כדי לפתור את הבעיה, צריך להפעיל את האפשרות לגשת אל Google Drive באמצעות Drive SDK API בחשבון המשתמש.

כדי לשמור את תוצאות השאילתה ב-Google Drive, משתמשים במסוף Google Cloud :

  1. נכנסים לדף BigQuery במסוף Google Cloud .

    כניסה ל-BigQuery

  2. לוחצים על שאילתת SQL.

  3. מזינים שאילתת GoogleSQL תקינה באזור הטקסט של עורך השאילתות.

  4. לוחצים על Run.

  5. כשמקבלים את התוצאות, לוחצים על שמירת התוצאות.

  6. בקטע Google Drive, בוחרים באפשרות CSV או JSON. כששומרים תוצאות ב-Google Drive, אי אפשר לבחור את המיקום. התוצאות תמיד נשמרות במיקום הבסיסי 'האחסון שלי'.

  7. יכול להיות שייקח כמה דקות עד שהתוצאות יישמרו ב-Google Drive. כששומרים את התוצאות, מוצגת הודעה בתיבת דו-שיח שכוללת את שם הקובץ – bq-results-[TIMESTAMP]-[RANDOM_CHARACTERS].[CSV or JSON].

    צילום מסך של כפתור שמירת התוצאות

  8. בתיבת הדו-שיח, לוחצים על פתיחה כדי לפתוח את הקובץ, או מנווטים אל Google Drive ולוחצים על האחסון שלי.

שמירת תוצאות של שאילתה ב-Google Sheets

אי אפשר לשמור תוצאות של שאילתות ב-Google Sheets באמצעות כלי שורת הפקודה bq או ה-API.

יכול להיות שתקבלו שגיאה כשנסו לפתוח את התוצאות של BigQuery מ-Google Sheets. השגיאה הזו נגרמת בגלל ש-Drive SDK API לא יכול לגשת ל-Google Workspace. כדי לפתור את הבעיה, צריך לאפשר לחשבון המשתמש גישה אל Google Sheets באמצעות Drive SDK API.

כדי לשמור את תוצאות השאילתה ב-Google Sheets, משתמשים במסוף Google Cloud :

  1. נכנסים לדף BigQuery במסוף Google Cloud .

    כניסה ל-BigQuery

  2. לוחצים על שאילתת SQL.

  3. מזינים שאילתת GoogleSQL תקינה באזור הטקסט של עורך השאילתות.

  4. אופציונלי: כדי לשנות את מיקום העיבוד, לוחצים על עריכה > הגדרות שאילתה. בקטע Data location (מיקום הנתונים), בוחרים את המיקום של הנתונים.

  5. לוחצים על Run.

  6. אחרי שהתוצאות מוחזרות, לוחצים על שמירת התוצאות ובוחרים באפשרות Google Sheets.

  7. אם צריך, פועלים לפי ההנחיות כדי להיכנס לחשבון המשתמש ולוחצים על אישור כדי לתת ל-BigQuery הרשאה לכתוב את הנתונים בתיקייה My Drive ב-Google Drive.

    אחרי שמבצעים את ההוראות, אמורה להתקבל הודעת אימייל שמאשרת שכלי הלקוח של BigQuery קושרו לחשבון המשתמש. האימייל מכיל מידע על ההרשאות שנתת, וגם הוראות להסרת ההרשאות.

  8. כששומרים את התוצאות, מוצגת הודעה דומה להודעה הבאה מתחת לתוצאות השאילתה במסוף Google Cloud : Saved to Sheets as "results-20190225-103531". לוחצים על הקישור בהודעה כדי לראות את התוצאות ב-Google Sheets, או עוברים לתיקייה My Drive ופותחים את הקובץ באופן ידני.

    כששומרים תוצאות של שאילתה ב-Google Sheets, שם הקובץ מתחיל ב-results-[DATE], כאשר [DATE] הוא התאריך של היום בפורמט YYYYMMDD.

פתרון בעיות בשמירת תוצאות ב-Google Sheets

כששומרים נתונים מ-BigQuery ב-Google Sheets, יכול להיות שחלק מהתאים בגיליונות יהיו ריקים. הבעיה הזו מתרחשת כשהנתונים שאתם כותבים בתא חורגים מהמגבלה של Google Sheets של 50,000 תווים. כדי לפתור את הבעיה, צריך להשתמש בפונקציית מחרוזת בשאילתת GoogleSQL כדי לפצל את העמודה עם הנתונים הארוכים לשתי עמודות או יותר, ואז לשמור את התוצאה שוב בגיליונות.

שמירת תוצאות של שאילתה ב-Cloud Storage

כדי לייצא את תוצאות השאילתה ל-Cloud Storage במסוף Google Cloud :

  1. פותחים את הדף BigQuery במסוף Google Cloud .

    לדף BigQuery

  2. לוחצים על שאילתת SQL.

  3. מזינים שאילתת GoogleSQL תקינה באזור הטקסט של עורך השאילתות.

  4. לוחצים על Run.

  5. אחרי שהתוצאות מוחזרות, לוחצים על שמירת התוצאות > Cloud Storage.

  6. בתיבת הדו-שיח Export to Google Cloud Storage:

    • בקטע מיקום ב-GCS, בוחרים את הדלי, התיקייה או הקובץ שאליהם רוצים לייצא את הנתונים.
    • בקטע פורמט ייצוא, בוחרים את הפורמט של הנתונים המיוצאים: CSV,‏ JSON (עם תווי שורה), Avro או Parquet.
    • בקטע דחיסה, בוחרים פורמט דחיסה או בוחרים באפשרות None כדי לא לדחוס את הקובץ.

  7. לוחצים על שמירה כדי לייצא את תוצאות השאילתה.

כדי לבדוק את התקדמות העבודה, מרחיבים את החלונית Job history ומחפשים את העבודה עם הסוג EXTRACT.

הגבלת ההורדה של תוצאות שאילתה

כדי למנוע מהמשתמשים להוריד תוצאות של שאילתות מ Google Cloud המסוף, אפשר להשתמש באחת מהשיטות הבאות:

  • הגדרת גבולות גזרה של VPC Service Controls כדי למנוע זליגת נתונים. ההגדרה הזו מונעת מהמשתמשים להוריד ולייצא נתונים מחוץ לגבולות ההיקף שהוגדרו.

  • כדי לבקש להוסיף את הפרויקט או הארגון שלכם לרשימה מוגבלת, אתם יכולים לפנות אל Cloud Customer Care. Google Cloud הפעולה הזו משביתה את האפשרויות להורדה ולייצוא של נתונים ישירות במסוף Google Cloud .

המאמרים הבאים