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

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

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

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

כדי להריץ שאילתות לכמה מסדי נתונים ב-Cloud SQL:

1. מוודאים שהאדמין ב-BigQuery יצר חיבור ל-Cloud SQL ושיתף את החיבור איתכם.
2. מוודאים שלסוכן השירות של החיבור ל-BigQuery הוקצה התפקיד "לקוח Cloud SQL" (roles/cloudsql.client). מידע נוסף זמין במאמר מתן גישה לסוכן השירות.
3. אם למכונה של Cloud SQL יש כתובת IP פרטית, צריך לוודא שהופעל נתיב פרטי כשהמכונה נוצרה. מידע נוסף זמין במאמרים התחברות ל-Cloud SQL והגדרת כתובת IP פרטית.

התפקידים הנדרשים

כדי לקבל את ההרשאות שנדרשות לשליחת שאילתות למכונה של Cloud SQL, צריך לבקש מהאדמין להקצות לכם את התפקיד משתמש בחיבורים של BigQuery (roles/bigquery.connectionUser) ב-IAM בפרויקט. כדי לקרוא הסבר על מתן תפקידים, ראו איך מנהלים את הגישה ברמת הפרויקט, התיקייה והארגון.

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

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

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

פורמט המזהה של החיבור

הארגומנט הראשון של הפונקציה EXTERNAL_QUERY הוא מזהה החיבור. אפשר לציין את מזהה החיבור באחד מהפורמטים הבאים:

• Shorts: [LOCATION.]CONNECTION_ID (לדוגמה, 'us.my-connection' או 'my-connection')
• פורמט מלא: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
• פורמט נתיב ידידותי: PROJECT_ID.LOCATION.CONNECTION_ID (לדוגמה, 'my-project.us.my-connection')

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

הרצת שאילתה לכמה מסדי נתונים

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

בוחרים אחת מהאפשרויות הבאות כדי להריץ את השאילתה לכמה מסדי נתונים:

המסוף

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

   כניסה ל-BigQuery

2. בעורך השאילתות, מזינים את השאילתה:

   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;
   

   מחליפים את CONNECTION_ID במזהה של החיבור ל-BigQuery.

3. לוחצים על הפעלה.

   תוצאות השאילתה מופיעות בחלונית Query results.

BQ

2. מפעילים את Cloud Shell במסוף Google Cloud .

   הפעלת Cloud Shell

   בחלק התחתון של מסוף Google Cloud יתחיל סשן של Cloud Shell ותופיע הודעה של שורת הפקודה. Cloud Shell היא סביבת מעטפת שבה ה-CLI של Google Cloud מותקן ומוגדרים ערכים לפרויקט הקיים. הסשן יופעל תוך כמה שניות.

3. מריצים את הפקודה bq query עם הדגל --use_legacy_sql=false:

   bq query \
       --use_legacy_sql=false \
       '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;'
   

   מחליפים את CONNECTION_ID במזהה של החיבור ל-BigQuery.

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

1. מריצים את השאילתה החיצונית SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id במסד הנתונים התפעולי של Cloud SQL כדי לקבל את תאריך ההזמנה הראשונה של כל לקוח באמצעות הפונקציה 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';");

מחליפים את CONNECTION_ID במזהה של החיבור ל-BigQuery.

מעקב אחרי שאילתות לכמה מסדי נתונים ב-BigQuery

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

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

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

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

   כניסה לדף Logs Explorer

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

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

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

   אם יש רשומות זמינות לשאילתות לכמה מסדי נתונים ב-BigQuery, רשימה של רשומות שדומה לזו שבהמשך תופיע ב-Query results:

   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:

שם המאפיין	ערך	תיאור
name	מחרוזת	שם המשאב של החיבור בפורמט: projects/PROJECT_ID/locations/LOCATION/connections/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:INSTANCE_ID. מזהה המכונה מופיע בדף Cloud SQL instances.
cloudSql.database	מחרוזת	מסד הנתונים של Cloud SQL שאליו רוצים להתחבר.
cloudSql.serviceAccountId	מחרוזת	חשבון השירות שמוגדר לגשת למסד הנתונים של Cloud SQL.

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

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

ביצועי שאילתות ושיטות מומלצות

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

• סינון בשלב מוקדם בשאילתה החיצונית: מפעילים מסנני WHERE וצבירות GROUP BY בתוך הצהרת ה-SQL של EXTERNAL_QUERY, במקום לסנן בשאילתה החיצונית של BigQuery. הפעלת מסננים ב-Cloud SQL מפחיתה את כמות הנתונים שמועברים ברשת ל-BigQuery.
• צמצום עמודות: בוחרים רק את העמודות הספציפיות שנדרשות בהצהרת EXTERNAL_QUERY במקום להשתמש ב-SELECT *. כך אפשר לצמצם את נפח ההעברה ברשת ואת זמן הביצוע של השאילתה.
• שימוש ברפליקות לקריאה בלבד: כדי להפריד בין עומסי העבודה של שאילתות הניתוח לבין מסד הנתונים הראשי בסביבת הייצור, יוצרים רפליקה לקריאה בלבד ב-Cloud SQL ומגדירים את החיבור ל-BigQuery כך שישלח שאילתות לרפליקה.
• מיקום משותף של מערכי נתונים ומכונות: כדי להשיג את זמן האחזור הנמוך ביותר, מקמו את מערך הנתונים והחיבור של BigQuery באותו אזור שבו נמצאת מכונת Cloud SQL, או במספר האזורים התואם (כמו US או EU). מידע נוסף זמין בקטע אזורים נתמכים.

פתרון בעיות

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

רזולוציה

כדי לפתור את הבעיה, צריך לוודא שהתנאים המוקדמים הבאים מתקיימים:

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

2. מוודאים שלסוכן השירות של החיבור ל-BigQuery הוקצה התפקיד "לקוח Cloud SQL" (roles/cloudsql.client). הפורמט של כתובת האימייל של סוכן השירות הוא:

   service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.

   הוראות מפורטות זמינות במאמר מתן גישה לסוכן השירות.

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

החיבור למסד הנתונים של MySQL נכשל בגלל פלאגין האימות

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

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

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

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

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

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

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

מגבלות

שאילתות לכמה מסדי נתונים ב-Cloud SQL כפופות למגבלות הבאות:

• שאילתות לקריאה בלבד: שאילתות לכמה מסדי נתונים הן לקריאה בלבד. שאילתות חיצוניות חייבות להיות הצהרות SELECT. אין תמיכה בהצהרות של שפת הגדרת נתונים (DDL) ושל שפת טיפול בנתונים (DML).
• ביצועי השאילתות: שאילתות לכמה מסדי נתונים לא מהירות כמו שאילתות לאחסון ב-BigQuery. ‫BigQuery צריך להמתין עד ש-Cloud SQL יריץ את השאילתה החיצונית ויעביר את קבוצת התוצאות דרך הרשת.
• אילוצים שקשורים לאזור: המיקום שבו מעובדת השאילתה ב-BigQuery צריך להיות תואם למיקום של המכונה ב-Cloud SQL. מידע נוסף זמין במאמר אזורים נתמכים.
• סוגי נתונים שלא נתמכים: אם שאילתה חיצונית מחזירה סוג נתונים שלא נתמך ב-BigQuery, השאילתה נכשלת. אפשר להמיר את סוג הנתונים שלא נתמך לסוג נתונים שנתמך בשאילתה החיצונית. מידע נוסף זמין במאמר מיפויים של סוגי נתונים.
• מספר החיבורים המקסימלי: שאילתה לכמה מסדי נתונים יכולה להפנות ל-10 חיבורים ייחודיים לכל היותר.
• מגבלת פלאגין אימות: הפלאגין caching_sha2_password של MySQL לא נתמך בשאילתות לכמה מסדי נתונים. מידע נוסף זמין במאמר פתרון בעיות.

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

• מידע על שאילתות לכמה מסדי נתונים
• מיפוי סוגי נתונים מ-MySQL ל-BigQuery
• מידע ולמידה על מיפוי סוגי נתונים מ-PostgreSQL ל-BigQuery
• מידע על סוגי נתונים שלא נתמכים