שאילתות מאוחדות ב-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 ולהריץ שאילתות על הנתונים האלה דרך חיבור פרטי.

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

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

פתרון:

השגיאה הזו מתרחשת אצל משתמשי מסד נתונים שמשתמשים באימות caching_sha2_password, שלא נתמך בשאילתות מאוחדות.

אם אתם משתמשים במכונת Cloud SQL עם MySQL בגרסה 8.0 או בגרסה מוקדמת יותר, אתם יכולים לשנות את פלאגין האימות למשתמשים שמתחברים מ-BigQuery. כדי לשנות את פלאגין האימות, מריצים את הפקודה הבאה במופע Cloud SQL:

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

מחליפים את מה שכתוב בשדות הבאים:

  • ‫USERNAME: חשבון המשתמש במסד הנתונים שמשמש את BigQuery לאימות ולהתחברות למופע Cloud SQL ל-MySQL.
  • ‫PASSWORD: הסיסמה של משתמש מסד הנתונים.

אם אתם משתמשים במופע Cloud SQL עם MySQL בגרסה 8.4, אין פתרון זמין כי התוסף mysql_native_password הוצא משימוש בגרסה 8.4. אנחנו לא ממליצים להוריד גרסה של מסד נתונים קיים מ-8.4 ל-8.0 כפתרון לעומסי עבודה בסביבת הייצור.

שדרוג מכונת Cloud SQL שמופעלת בה גרסה של MySQL מוקדמת יותר מ-8.4 לגרסה 8.4 לא משפיע על חיבורי מסד נתונים קיימים, בתנאי שלא משנים את תוסף האימות של המשתמשים לפני השדרוג.

מכיוון שאפשר להשתמש רק במשתמשים שנוצרו באמצעות התוסף mysql_native_password (בדרך כלל משתמשים שנוצרו לפני השדרוג לגרסה 8.4) לשאילתות מאוחדות, גם אחרי שמופע MySQL ישודרג לגרסה 8.4, צריך לשלוח שאילתות מאוחדות ל-MySQL עם הגדרות חיבור שמשתמשות במשתמש מסד נתונים שהיה קיים בגרסה של MySQL מוקדמת יותר מ-8.4.

מגבלות

  • הפלאגין caching_sha2_password של MySQL לא נתמך בשאילתות מאוחדות. כתוצאה מכך, שאילתות מאוחדות עבור MySQL 8.0 ו-8.4 באמצעות הפלאגין הזה ייכשלו. מידע נוסף זמין במאמר בנושא פתרון בעיות.

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