שאילתות מאוחדות ב-Cloud SQL

כאנליסטים של נתונים, אתם יכולים להריץ שאילתות על נתונים ב-Cloud SQL מ-BigQuery באמצעות שאילתות מאוחדות.

פדרציית Cloud SQL ב-BigQuery מאפשרת ל-BigQuery לשלוח שאילתות לנתונים שנמצאים ב-Cloud SQL בזמן אמת, בלי להעתיק או להעביר את הנתונים. איחוד השאילתות תומך במופעי MySQL (דור שני) ו-PostgreSQL ב-Cloud SQL.

לחלופין, כדי לשכפל נתונים ל-BigQuery, אפשר להשתמש גם ב-Cloud Data Fusion או ב-Datastream. מידע נוסף על שימוש ב-Cloud Data Fusion זמין במאמר שכפול נתונים מ-MySQL ל-BigQuery.

לפני שמתחילים

שאילתת נתונים

כדי לשלוח שאילתה לכמה מסדי נתונים ל-Cloud SQL משאילתת GoogleSQL, משתמשים בפונקציה EXTERNAL_QUERY.

נניח שאתם מאחסנים טבלת לקוחות ב-BigQuery, ומאחסנים טבלת מכירות ב-Cloud SQL, ואתם רוצים לבצע הצטרפות של שתי הטבלאות בשאילתה אחת. בדוגמה הבאה מוצגת שאילתה לכמה מסדי נתונים לטבלה ב-Cloud SQL בשם orders, והתוצאות מצורפות לטבלה ב-BigQuery בשם mydataset.customers.

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

שאילתת הדוגמה כוללת 3 חלקים:

  1. מריצים את השאילתה החיצונית SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id במסד הנתונים התפעולי של PostgreSQL כדי לקבל את תאריך ההזמנה הראשון של כל לקוח באמצעות הפונקציה EXTERNAL_QUERY().
  2. מצטרפים לטבלת תוצאות השאילתה החיצונית עם טבלת הלקוחות ב-BigQuery באמצעות customer_id.
  3. בוחרים את נתוני הלקוח ואת תאריך ההזמנה הראשונה.

הצגת סכימת טבלה ב-Cloud SQL

אפשר להשתמש בפונקציה EXTERNAL_QUERY() כדי לבצע שאילתות בטבלאות information_schema כדי לגשת למטא-נתונים של מסד הנתונים, כמו רשימה של כל הטבלאות במסד הנתונים או הצגה של סכימת הטבלה. הדוגמאות הבאות של שאילתות information_schema פועלות גם ב-MySQL וגם ב-PostgreSQL. מידע נוסף זמין במאמרים בנושא טבלאות information_schema של MySQL וטבלאות information_schema של PostgreSQL.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("connection_id",
"select * from information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("connection_id",
"select * from information_schema.columns where table_name='x';");

פרטי התחברות

בטבלה הבאה מפורטים מאפייני החיבור של Cloud SQL:

שם המאפיין ערך תיאור
name מחרוזת השם של משאב החיבור בפורמט: project_id.location_id.connection_id.
location מחרוזת המיקום של החיבור חייב להיות זהה למיקום של מכונת Cloud SQL או להיות מיקום במספר אזורים בתחום השיפוט המתאים. לדוגמה, מופע של Cloud SQL ב-us-east4 יכול להשתמש ב-US, ומכונה של Cloud SQL ב-europe-north1 יכולה להשתמש ב-EU. רק שאילתות BigQuery שמופעלות במיקום הזה יוכלו להשתמש בחיבור הזה.
friendlyName מחרוזת שם ידידותי לתצוגה של החיבור.
description מחרוזת תיאור החיבור.
cloudSql.type מחרוזת יכול להיות POSTGRES או MYSQL.
cloudSql.instanceId מחרוזת השם של המופע של Cloud SQL, בדרך כלל בפורמט הבא:

Project-id:location-id:instance-id

אפשר למצוא את מזהה המופע בדף הפרטים של המופע של Cloud SQL.
cloudSql.database מחרוזת מסד הנתונים של Cloud SQL שאליו רוצים להתחבר.
cloudSql.serviceAccountId מחרוזת חשבון השירות שהוגדר לגישה למסד הנתונים של Cloud SQL.

בטבלה הבאה מפורטים המאפיינים של פרטי הכניסה של מכונת Cloud SQL:

שם המאפיין ערך תיאור
username מחרוזת שם המשתמש במסד הנתונים
password מחרוזת סיסמת מסד הנתונים

מעקב אחרי שאילתות מאוחדות ב-BigQuery

כשמריצים שאילתה לכמה מסדי נתונים ב-Cloud SQL,‏ BigQuery מוסיף לשאילתה הערה שדומה להערה הבאה:

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

אם אתם עוקבים אחרי יומנים של שימוש בשאילתות במסד נתונים של MySQL או PostgreSQL, ההערה הבאה יכולה לעזור לכם לזהות שאילתות שמגיעות מ-BigQuery.

  1. עוברים לדף Logs Explorer.

    כניסה לדף Logs Explorer

  2. בכרטיסייה שאילתה, מזינים את השאילתה הבאה:

    resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"

  3. לוחצים על Run query.

    אם יש רשומות שזמינות לשאילתות מאוחדות ב-BigQuery, רשימה של רשומות שדומה לרשימה הבאה תופיע בתוצאות השאילתה:

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

פתרון בעיות

בקטע הזה מוסבר איך לפתור בעיות שאתם עלולים להיתקל בהן כשאתם שולחים שאילתה לכמה מסדי נתונים ל-Cloud SQL.

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

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

לחלופין, אם אתם שולחים שאילתה למסד נתונים של PostgreSQL, יכול להיות שתיתקלו בשגיאה הבאה:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

פתרון: מוודאים שהשתמשתם בפרטי כניסה תקינים ופעלתם לפי כל הדרישות המוקדמות כדי ליצור את החיבור ל-Cloud SQL. בודקים אם לחשבון השירות שנוצר באופן אוטומטי כשנוצר חיבור ל-Cloud SQL יש את התפקיד 'לקוח Cloud SQL' (roles/cloudsql.client). חשבון השירות הוא בפורמט הבא: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. הוראות מפורטות מופיעות במאמר מתן גישה לחשבון השירות.

אם מכונת Cloud SQL משתמשת בכתובת IP פרטית, צריך לוודא שהפעלתם נתיב פרטי כשיצרתם את מכונת Cloud SQL. כך BigQuery יכול לגשת לנתונים ב-Cloud SQL ולהריץ שאילתות על הנתונים האלה דרך חיבור פרטי.

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