הרצת שאילתות מאוחדות ב-PostgreSQL

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

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

מגבלות

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

  • אפשר להריץ רק שאילתות לקריאה בלבד באמצעות הפונקציה EXTERNAL_QUERY. אין תמיכה בהצהרות של שפת הגדרת נתונים (DDL) ושל שפת טיפול בנתונים (DML).
  • אפשר לגשת לאיחוד רק דרך הפונקציה EXTERNAL_QUERY. אי אפשר ליצור טבלאות חיצוניות או מערכי נתונים קבועים ב-BigQuery שממופים ישירות לטבלאות ב-PostgreSQL.
  • יכול להיות שזמן האחזור של שאילתה מאוחדת יהיה ארוך יותר באופן משמעותי מאשר הרצת השאילתה ישירות ב-PostgreSQL.
  • יכול להיות ששירות החיבור הבסיסי יעביר חיבורים לא פעילים למצב שינה. השאילתה הראשונה שמשתמשת בחיבור PostgreSQL אחרי תקופה של חוסר פעילות עשויה להימשך יותר מדקה, בזמן שהמחבר מבצע אתחול.
  • אין תמיכה בקישור בין רשתות שכנות (peering) ב-DNS. צריך להתחבר באמצעות כתובת IP או שם מארח שניתן לפתור באופן מלא ברשת ה-VPC.
  • בשאילתת ה-SQL של PostgreSQL צריך לציין כינויים מפורשים לעמודות שמכילות תוצאות או ביטויים של פונקציות (לדוגמה, SELECT COUNT(*) AS total_count). ביטויים ללא כינויים יוצרים שמות של עמודות עם תווים מיוחדים שלא תקינים בהגדרות הסכימה של הטבלה ב-BigQuery.
  • אם השימוש ב-Compute Engine API בפרויקט השאילתה מוגבל על ידי VPC Service Controls, השאילתה המאוחדת נכשלת אם החיבור משתמש במחבר רשת. מוודאים שמוגדרים כללים מתאימים לתעבורת נתונים נכנסת ויוצאת ב-VPC Service Controls.

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

  • אפשר לאמת רק את שם המשתמש והסיסמה של מסד הנתונים ב-PostgreSQL. אין תמיכה בספקי זהויות של צד שלישי, ב-Kerberos ובאימות IAM של ספק שירותי ענן.

  • CREATE TABLE ... AS SELECT (CTAS) statements fail with Unsupported type INT32 or Unsupported type FLOAT errors if the query selects 16-bit integer columns (int2, smallint, smallserial) or single-precision float columns (float4, real). To save query results into a new BigQuery table, explicitly cast integer columns to INT64 and float columns to FLOAT64 in your query:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_col,
...
FROM EXTERNAL_QUERY(...);

    הוספה לטבלה קיימת עם INSERT INTO <table> SELECT ... מתבצעת בלי המרות ידניות, כי BigQuery מרחיב באופן אוטומטי את הערכים של INT32 ו-FLOAT כך שיתאימו לעמודות INT64 ו-FLOAT64 בטבלת היעד.

  • גם time [ without time zone ] וגם time with time zone (timetz) חותכים ערכים של חלקי שנייה (מיקרו-שנייה) לשניות שלמות. אם נדרש דיוק של מיקרו-שניות, צריך להגדיר את הפורמט או להמיר את העמודה ל-text או ל-varchar בהצהרת ה-EXTERNAL_QUERY.

  • כל העמודות של numeric ו-decimal ב-PostgreSQL ממופות ל-BIGNUMERIC(76, 38). עמודות עם קנה מידה גדול מ-38 מעוגלות ל-38 מקומות עשרוניים, וערכים שחורגים מהטווח של BIGNUMERIC (יותר מ-38 ספרות שלמות) נכשלים עם שגיאה של חריגה מהטווח. בנוסף, הערכים המיוחדים NaN,‏ Infinity ו--Infinity לא נתמכים ונכשלים בזמן הריצה. כדי לשלוח שאילתות על מספרים עם חלקים שלמים גדולים יותר, דיוק גבוה יותר של שברים או ערכים מיוחדים, צריך להמיר את העמודה ל-text בהצהרת EXTERNAL_QUERY.

  • ערכי character(n) / char(n) (bpchar) באורך קבוע שומרים על רווחים בסוף עד לאורך n כשהם ממופים ל-STRING ב-BigQuery.

  • תאריכים וחותמות זמן שקדמו ל-15 באוקטובר 1582 כפופים לשינוי של 10 ימים בלוח השנה, בגלל המעבר ההיסטורי ללוח השנה הגרגוריאני. כדי לשמור על התאריכים המדויקים, צריך להמיר תאריכים היסטוריים ל-text בהצהרה EXTERNAL_QUERY.

גרסאות וסביבות נתמכות של PostgreSQL

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

  • מסדי נתונים של PostgreSQL שמתארחים בענן או מקומיים, שאפשר לגשת אליהם באמצעות כתובות IP ציבוריות או מחברי רשת (דרך Cloud VPN או Cloud Interconnect)
  • מכונות PostgreSQL באירוח עצמי ב-Compute Engine או בתשתית אחרת
  • ‫PostgreSQL 16 (מומלץ) ו-PostgreSQL 15

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

מוודאים שהאדמין ב-BigQuery יצר חיבור ל-PostgreSQL ושיתף אותו איתכם.

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

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

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

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

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

בדוגמה הבאה מוצגת שאילתה מאוחדת שמצרפת רשומות של לקוחות ב-BigQuery בשם mydataset.customers עם הזמנות מכירה טרנזקציונליות ב-PostgreSQL:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/postgres_prod_conn',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

הצגת סכימה של טבלת PostgreSQL

אתם יכולים לשלוח שאילתות לטבלאות information_schema של PostgreSQL באמצעות EXTERNAL_QUERY כדי לאחזר מטא-נתונים של מסד הנתונים:

הצגת רשימה של כל הטבלאות בסכימה

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_name''');

הצגת עמודות וסוגי נתונים בטבלה

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, is_nullable
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

מיפוי סוגי נתונים

כשמריצים שאילתה מאוחדת, BigQuery ממיר את הנתונים מ-PostgreSQL לסוגי נתונים של GoogleSQL, לפי הטבלה הבאה:

סוג הנתונים ב-PostgreSQL סוג הנתונים ב-BigQuery
סוגים בוליאניים
bool, boolean BOOL
סוגים מספריים
int2, smallint, smallserial ‫INT64 (שאילתות אינטראקטיביות; ראו מגבלות של CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real ‫FLOAT64 (שאילתות אינטראקטיביות; ראו מגבלות של CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
סוגי תאריך ושעה
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
סוגי תווים וטקסט
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text STRING

תמחור

העלות של הרצת שאילתה מאוחדת מבוססת על שלושה גורמים:

  • עלות החישוב של הרצת השאילתה ב-PostgreSQL (החיוב מתבצע על ידי ספק מסד הנתונים של PostgreSQL או סביבת האירוח)
  • עלות רוחב הפס של העברת תוצאות השאילתה מ-PostgreSQL ל-BigQuery (חיובים על תעבורת נתונים יוצאת ברשת מספק האירוח של מסד הנתונים)
  • עלות המחשוב של הרצת השאילתה ב-BigQuery

כל עלויות המחשוב ותעבורת הנתונים היוצאת של PostgreSQL מחויבות ישירות על ידי ספק PostgreSQL או מארח התשתית, ולא על ידי Google. כדי להגביל את עלות רוחב הפס, מומלץ לכתוב את השאילתה בהצהרה EXTERNAL_QUERY כך שהיא לא תכלול את כל העמודות והשורות שלא נדרשות לחישוב התוצאה הסופית.

אין עלות נוספת להרצת שאילתות מאוחדות ב-BigQuery. מידע נוסף על התמחור ב-BigQuery

אם החיבור שלכם משתמש ברכיבי רשת של Google Cloud , יכול להיות שיחולו גם המחירים הבאים:

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