מבוא להעברות נתונים מ-PostgreSQL

אתם יכולים לטעון נתונים מ-PostgreSQL ל-BigQuery באמצעות המחבר של שירות העברת הנתונים ל-BigQuery ל-PostgreSQL. המחבר תומך במכונות PostgreSQL שמתארחות בסביבה המקומית, ב-Cloud SQL ובספקי ענן ציבורי אחרים כמו Amazon Web Services ‏ (AWS) ו-Microsoft Azure. במאמר הזה מפורטות האפשרויות להעברת נתונים ב-PostgreSQL, ומוסבר על מיפוי סוגי הנתונים והתמחור.

במאמר טעינת נתוני PostgreSQL ל-BigQuery מוסבר איך מתזמנים העברה של PostgreSQL.

אפשרויות להטמעת נתונים

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

הגדרת TLS

מחבר PostgreSQL תומך בהגדרה של אבטחה ברמת התעבורה (TLS) כדי להצפין את העברות הנתונים ל-BigQuery. מחבר PostgreSQL תומך בהגדרות ה-TLS הבאות:

  • המצב הצפנת נתונים ואימות של רשות האישורים ושם המארח. במצב הזה מתבצע אימות מלא של השרת באמצעות TLS בפרוטוקול TCPS. הוא מצפין את כל הנתונים בזמן ההעברה ומוודא שהאישור של שרת מסד הנתונים נחתם על ידי רשות אישורים (CA) מהימנה. במצב הזה גם נבדק אם שם המארח שאליו אתם מתחברים תואם בדיוק לשם הנפוץ (CN) או לשם החלופי של הבעלים (SAN) באישור של השרת. המצב הזה מונע מתוקפים להשתמש באישור תקף של דומיין אחר כדי להתחזות לשרת של מסד הנתונים.

    אם שם המארח לא תואם ל-CN או ל-SAN של האישור, החיבור ייכשל. צריך להגדיר פענוח DNS שיתאים לאישור או להשתמש במצב אבטחה אחר. כדאי להשתמש במצב הזה כדי למנוע התקפות של אדם באמצע (PITM).

  • המצב הצפנת נתונים ואימות CA בלבד. במצב הזה, כל הנתונים מוצפנים באמצעות TLS בפרוטוקול TCPS, והמערכת מאמתת שהאישור של השרת חתום על ידי רשות אישורים (CA) שהלקוח סומך עליה. עם זאת, במצב הזה לא מתבצע אימות של שם המארח של השרת. במצב הזה, החיבור יצליח כל עוד האישור תקף והונפק על ידי רשות אישורים מהימנה, גם אם שם המארח באישור לא תואם לשם המארח שאליו אתם מתחברים.

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

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

    המצב הזה מספק רמת אבטחה מסוימת על ידי הגנה על נתונים במעבר, אבל הוא עלול להיות פגיע למתקפות PITM.

    השתמשו במצב הזה אם אתם צריכים לוודא שכל הנתונים מוצפנים, אבל אתם לא יכולים או לא רוצים לאמת את זהות השרת. מומלץ להשתמש במצב הזה כשעובדים עם רשתות VPC פרטיות.

  • המצב ללא הצפנה או אימות. במצב הזה לא מתבצעת הצפנה של נתונים ולא מתבצע אימות של אישורים או של שמות מארחים. כל הנתונים נשלחים כטקסט פשוט.

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

אישור שרת מהימן (PEM)

אם אתם משתמשים במצב הצפנת נתונים ואימות של רשות האישורים ושם המארח או במצב הצפנת נתונים ואימות של רשות האישורים, אתם יכולים גם לספק אישור אחד או יותר בקידוד PEM. האישורים האלה נדרשים בתרחישים מסוימים שבהם שירות העברת הנתונים ל-BigQuery צריך לאמת את הזהות של שרת מסד הנתונים במהלך החיבור ל-TLS:

  • אם אתם משתמשים באישור שחתום על ידי רשות אישורים פרטית בארגון שלכם או באישור עם חתימה עצמית, אתם צריכים לספק את שרשרת האישורים המלאה או את האישור היחיד עם החתימה העצמית. הפעולה הזו נדרשת לאישורים שהונפקו על ידי רשויות אישורים פנימיות של שירותי ספק שירותי ענן מנוהלים, כמו Amazon מסד נתונים רלציוני Service (RDS).
  • אם האישור של שרת מסד הנתונים נחתם על ידי רשות אישורים ציבורית (לדוגמה, Let's Encrypt,‏ DigiCert או GlobalSign), אין צורך לספק אישור. אישורי הבסיס של רשויות אישורים ציבוריות אלה מותקנים מראש ומהימנים על ידי שירות העברת הנתונים ל-BigQuery.

אפשר לציין אישורים בקידוד PEM בשדה Trusted PEM Certificate בהגדרות ההעברה, עם הדרישות הבאות:

  • האישור חייב להיות שרשרת אישורים תקפה עם קידוד PEM.
  • האישור חייב להיות נכון לחלוטין. אם חסרים אישורים בשרשרת או אם התוכן שגוי, חיבור ה-TLS ייכשל.
  • אם יש לכם אישור אחד, אתם יכולים לספק אישור אחד עם חתימה עצמית משרת מסד הנתונים.
  • אם מדובר בשרשרת אישורים מלאה שהונפקה על ידי רשות אישורים פרטית, צריך לספק את שרשרת האמון המלאה. הקובץ הזה כולל את האישור משרת מסד הנתונים וכל אישור CA ברמת ביניים וברמה הבסיסית.

העברות מלאות או מצטברות

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

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

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

הוספה למצב כתיבה

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

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

להעברות של PostgreSQL, מומלץ לבחור עמודה שמתעדכנת רק כשהרשומה נוצרה, ולא משתנה בעדכונים הבאים – לדוגמה, העמודה CREATED_AT.

מצב כתיבה של Upsert

במצב הכתיבה "עדכון או הוספה" (upsert), המערכת בודקת אם יש מפתח ראשי, ואז מעדכנת שורה או מוסיפה שורה חדשה בטבלת היעד. אתם יכולים לציין מפתח ראשי כדי לאפשר למחבר של PostgreSQL לקבוע אילו שינויים נדרשים כדי שהטבלה ביעד תהיה מעודכנת בהתאם לטבלה במקור. אם המפתח הראשי שצוין קיים בטבלה ב-BigQuery במהלך העברת נתונים, המחבר של PostgreSQL מעדכן את השורה הזו בנתונים חדשים מטבלת המקור. אם מפתח ראשי לא קיים במהלך העברת נתונים, מחבר PostgreSQL מוסיף שורה חדשה.

כשבוחרים במצב upsert, צריך לבחור עמודה של סימן מים ומפתח ראשי:

  • כדי שהמחבר של PostgreSQL יוכל לעקוב אחרי שינויים בטבלת המקור, צריך להוסיף עמודה של סימן מים.
    • בוחרים עמודת סימן מים שמתעדכנת בכל פעם שמשנים שורה. אנחנו ממליצים על עמודות שדומות לעמודה UPDATED_AT או LAST_MODIFIED.

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

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

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

התנהגות של הוספה מצטברת

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

שינויים במקור הנתונים התנהגות של הטמעה מצטברת
הוספת עמודה חדשה עמודה חדשה נוספת לטבלת היעד ב-BigQuery. כל הרשומות הקודמות בעמודה הזו יכילו ערכי null.
מחיקת עמודה העמודה שנמחקה נשארת בטבלת היעד ב-BigQuery. ערכים חדשים בעמודה שנמחקה יאוכלסו בערכי null.
שינוי סוג הנתונים בעמודה המחבר תומך רק ב המרות של סוגי נתונים שנתמכות על ידי ALTER COLUMN הצהרת DDL. כל המרה אחרת של סוג נתונים תגרום לכך שהעברת הנתונים תיכשל.

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

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

בטבלה הבאה מופיע מיפוי של סוגי הנתונים ב-PostgreSQL לסוגי הנתונים התואמים ב-BigQuery:

סוג הנתונים ב-PostgreSQL סוג הנתונים ב-BigQuery סוג הנתונים המעודכן ב-BigQuery
array STRING
bigint INTEGER
bigserial INTEGER
bit(n) STRING
bit varying(n) STRING
boolean BOOLEAN
box STRING
bytea BYTES
character STRING
character varying STRING
cidr STRING
circle STRING
circularstring STRING
compoundcurve STRING
curvepolygon STRING
date DATE
double precision FLOAT
enum STRING
geometrycollection STRING
inet STRING
integer INTEGER
interval STRING
json STRING JSON
jsonb STRING JSON
line STRING
linestring STRING
lseg STRING
macaddr STRING
macaddr8 STRING
money STRING
multicurve STRING
multilinestring STRING
multipoint STRING
multipolygon STRING
multisurface STRING
numeric(precision, scale)/decimal(precision, scale) NUMERIC
path STRING
point STRING
polygon STRING
polyhedralsurface STRING
range STRING
real FLOAT
serial INTEGER
smallint INTEGER
smallserial INTEGER
text STRING
time [ (p) ] [ without timezone ] TIMESTAMP
time [ (p) ] with time zone TIMESTAMP
tin STRING
timestamp [ (p) ] [ without timezone ] TIMESTAMP DATETIME
timestamp [ (p) ] with time zone TIMESTAMP
triangle STRING
tsquery STRING
tsvector STRING
uuid STRING
xml STRING

תמחור

למידע על מחירי ההעברות מ-PostgreSQL, קראו את המאמר התמחור של שירות העברת הנתונים.

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