טעינת נתונים מ-PostgreSQL ל-BigQuery

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

מגבלות

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

• המספר המקסימלי של העברות בו-זמניות למסד נתונים יחיד של PostgreSQL נקבע לפי המספר המקסימלי של חיבורים בו-זמניים שמסד הנתונים של PostgreSQL תומך בהם. מספר העבודות להעברה בו-זמנית צריך להיות מוגבל לערך שקטן ממספר החיבורים המקסימלי בו-זמנית שנתמכים על ידי מסד הנתונים של PostgreSQL.
• כל הגדרה של העברת נתונים יכולה לתמוך רק בהרצה אחת של העברת נתונים בכל זמן נתון. אם מתוכננת העברת נתונים שנייה לפני שההעברה הראשונה מסתיימת, רק ההעברה הראשונה תושלם. כל העברת נתונים אחרת שחופפת להעברה הראשונה תדלג.
  • כדי להימנע מדילוג על העברות במסגרת הגדרת העברה יחידה, מומלץ להגדיר את תדירות החזרה כדי להגדיל את משך הזמן בין העברות של נתונים בכמות גדולה.
• במהלך העברת נתונים, מחבר PostgreSQL מזהה עמודות מפתח עם אינדקסים ומחיצות כדי להעביר את הנתונים שלכם באצוות מקבילות. לכן, מומלץ לציין עמודות של מפתח ראשי או להשתמש בעמודות עם אינדקס בטבלה כדי לשפר את הביצועים ולהקטין את שיעור השגיאות בהעברות הנתונים.
  • אם יש לכם אילוצים של אינדקס או של מפתח ראשי, אפשר ליצור אצוות מקבילות רק עם סוגי העמודות הבאים:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • העברות נתונים מ-PostgreSQL שלא משתמשות במפתח ראשי או בעמודות עם אינדקס לא יכולות לתמוך ביותר מ-2,000,000 רשומות לכל טבלה.

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

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

הגדרת TLS

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

• הצפנת נתונים ואימות של CA ושם מארח: במצב הזה מתבצע אימות מלא של השרת באמצעות TLS בפרוטוקול TCPS. הוא מצפין את כל הנתונים בזמן ההעברה ומוודא שהאישור של שרת מסד הנתונים נחתם על ידי רשות אישורים (CA) מהימנה. בנוסף, במצב הזה נבדק אם שם המארח שאליו מתחברים תואם בדיוק לשם הנפוץ (CN) או לשם חלופי של בעלים (subject) (SAN) באישור של השרת. המצב הזה מונע מהתוקפים להשתמש באישור תקף לדומיין אחר כדי להתחזות לשרת מסד הנתונים שלכם.
  • אם שם המארח לא תואם ל-CN או ל-SAN של האישור, החיבור נכשל. צריך להגדיר פתרון DNS שתואם לאישור או להשתמש במצב אבטחה אחר.
  • השתמשו במצב הזה כדי לקבל את האפשרות המאובטחת ביותר למניעת מתקפות מסוג 'אדם באמצע' (PITM).
• הצפנת נתונים ואימות רשות האישורים בלבד: במצב הזה כל הנתונים מוצפנים באמצעות TLS בפרוטוקול TCPS, ומאומת שהאישור של השרת נחתם על ידי רשות אישורים שהלקוח סומך עליה. עם זאת, במצב הזה לא מתבצע אימות של שם המארח של השרת. החיבור במצב הזה יצליח כל עוד האישור תקף והונפק על ידי רשות אישורים מהימנה, בלי קשר לשאלה אם שם המארח באישור תואם לשם המארח שאליו מתחברים.
  • כדאי להשתמש במצב הזה אם רוצים לוודא שמתחברים לשרת שהאישור שלו חתום על ידי רשות מהימנה שמנפיקה אישורים (CA), אבל אי אפשר לאמת את שם המארח או שאין לכם שליטה על הגדרת שם המארח.
• הצפנה בלבד: במצב הזה מוצפנים כל הנתונים שמועברים בין הלקוח לשרת. היא לא מבצעת אימות של אישורים או של שמות מארחים.
  • המצב הזה מספק רמת אבטחה מסוימת על ידי הגנה על נתונים במעבר, אבל הוא עלול להיות פגיע למתקפות PITM.
  • משתמשים במצב הזה אם רוצים לוודא שכל הנתונים מוצפנים, אבל לא יכולים או לא רוצים לאמת את זהות השרת. מומלץ להשתמש במצב הזה כשעובדים עם רשתות VPC פרטיות.
• ללא הצפנה או אימות: במצב הזה לא מוצפנים נתונים ולא מתבצע אימות של אישורים או שמות מארחים. כל הנתונים נשלחים כטקסט רגיל.
  • לא מומלץ להשתמש במצב הזה בסביבה שבה מתבצע טיפול במידע אישי רגיש.
  • מומלץ להשתמש במצב הזה רק למטרות בדיקה ברשת מבודדת שבה האבטחה לא מהווה בעיה.

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

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

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

כשיוצרים הגדרת העברה של PostgreSQL, אפשר לספק אישורים בקידוד PEM בשדה Trusted PEM Certificate (אישור PEM מהימן), עם הדרישות הבאות:

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

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

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

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

אם אתם מתכוונים להגדיר התראות על הפעלת העברה ב-Pub/Sub, ודאו שיש לכם הרשאה לניהול זהויות והרשאות גישה (IAM) pubsub.topics.setIamPolicy. אם מגדירים רק התראות באימייל, לא צריך הרשאות Pub/Sub. מידע נוסף זמין במאמר בנושא התראות על הפעלת שירות העברת נתונים ל-BigQuery.

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

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

ההרשאות הנדרשות

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

• הרשאות של שירות העברת נתונים ל-BigQuery:
  • bigquery.transfers.update
  • bigquery.transfers.get
• הרשאות ב-BigQuery:
  • bigquery.datasets.get
  • bigquery.datasets.getIamPolicy
  • bigquery.datasets.update
  • bigquery.datasets.setIamPolicy
  • bigquery.jobs.create

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

מידע נוסף מופיע במאמר בנושא מתן גישה ל-bigquery.admin.

חיבורים לרשת

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

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

• אם אתם מעבירים מ-Cloud SQL, כדאי לעיין במאמר בנושא הגדרת גישה למופע Cloud SQL.
• אם אתם מעבירים נתונים מ-AWS, כדאי לעיין במאמר בנושא הגדרת ה-VPN של AWS-Google Cloud והקשר לרשת.
• אם אתם מעבירים מ-Azure, כדאי לעיין במאמר בנושא הגדרת ה-VPN של Azure-Google Cloud והצמדת הרשת.

הגדרת העברת נתונים מ-PostgreSQL

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

המסוף

1. עוברים לדף העברות נתונים.

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

2. לוחצים על Create transfer (יצירת העברה).

3. בקטע סוג המקור, בשדה מקור, בוחרים באפשרות PostgreSQL.

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

5. בקטע Schedule options:

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

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

7. בקטע Data source details (פרטים של מקור הנתונים):

   • בקטע Network attachment (צירוף לרשת), בוחרים צירוף קיים לרשת או לוחצים על Create Network Attachment (יצירת צירוף לרשת). מידע נוסף זמין בקטע חיבורי רשת במאמר הזה.
   • בשדה מארח, מזינים את שם המארח או את כתובת ה-IP של שרת מסד הנתונים של PostgreSQL.
   • בשדה מספר היציאה, מזינים את מספר היציאה של שרת מסד הנתונים של PostgreSQL.
   • בשדה שם מסד הנתונים, מזינים את השם של מסד הנתונים של PostgreSQL.
   • בשדה שם משתמש, מזינים את שם המשתמש של משתמש PostgreSQL שמבצע את החיבור למסד הנתונים של PostgreSQL.
   • בשדה סיסמה, מזינים את הסיסמה של משתמש PostgreSQL שיזם את הקישור למסד הנתונים של PostgreSQL.
   • בתפריט מצב TLS, בוחרים באחת מהאפשרויות. מידע נוסף על מצבי TLS זמין במאמר בנושא הגדרת TLS.
   • בשדה Trusted PEM Certificate (אישור PEM מהימן), מזינים את האישור הציבורי של רשות האישורים (CA) שהנפיקה את אישור ה-TLS של שרת מסד הנתונים. מידע נוסף זמין במאמר אישור שרת מהימן (PEM).

   • בקטע PostgreSQL objects to transfer (אובייקטים של PostgreSQL להעברה), מבצעים אחת מהפעולות הבאות:

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

8. אופציונלי: בקטע אפשרויות התראות, מבצעים את הפעולות הבאות:

   • כדי להפעיל התראות באימייל, לוחצים על המתג התראות באימייל ומעבירים אותו למצב מופעל. כשמפעילים את האפשרות הזו, האדמין של ההעברה מקבל התראה באימייל כשהרצת ההעברה נכשלת.
   • כדי להגדיר התראות ב-Pub/Sub להעברה, לוחצים על המתג Pub/Sub notifications (התראות Pub/Sub) למצב מופעל. אפשר לבחור את שם הנושא או ללחוץ על יצירת נושא כדי ליצור נושא.

9. לוחצים על Save.

BQ

מזינים את הפקודה bq mk ומציינים את דגל יצירת ההעברה --transfer_config:

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

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

• ‫PROJECT_ID (אופציונלי): מזהה הפרויקט ב- Google Cloud . אם לא מציינים פרויקט ספציפי באמצעות הדגל --project_id, נעשה שימוש בפרויקט שמוגדר כברירת מחדל.
• ‫DATA_SOURCE: מקור הנתונים, שהוא postgresql.
• ‫DISPLAY_NAME: השם המוצג של הגדרת העברת הנתונים. שם ההעברה יכול להיות כל ערך שיעזור לכם לזהות את ההעברה אם תצטרכו לשנות אותה בהמשך.
• ‫DATASET: מערך הנתונים של היעד להגדרת העברת הנתונים.

• ‫PARAMETERS: הפרמטרים של הגדרת ההעברה שנוצרה בפורמט JSON. לדוגמה: --params='{"param":"param_value"}'. אלה הפרמטרים להעברה של PostgreSQL:

  • ‫connector.networkAttachment (אופציונלי): שם הקובץ המצורף לרשת שאליו רוצים להתחבר למסד הנתונים של PostgreSQL.
  • ‫connector.database: השם של מסד הנתונים של PostgreSQL.
  • connector.endpoint.host: שם המארח או כתובת ה-IP של מסד הנתונים.
  • ‫connector.endpoint.port: מספר היציאה של מסד הנתונים.
  • ‫connector.authentication.username: שם המשתמש של המשתמש במסד הנתונים.
  • ‫connector.authentication.password: הסיסמה של משתמש מסד הנתונים.
  • ‫connector.tls.mode: מציינים תצורת TLS לשימוש בהעברה הזו:
    • ‫ENCRYPT_VERIFY_CA_AND_HOST להצפנת נתונים ולאימות של CA ושם מארח
    • ‫ENCRYPT_VERIFY_CA להצפנת נתונים ולאימות CA בלבד
    • ‫ENCRYPT_VERIFY_NONE להצפנת נתונים בלבד
    • ‫DISABLE ללא הצפנה או אימות
  • ‫connector.tls.trustedServerCertificate: (אופציונלי) מציינים אישור אחד או יותר בקידוד PEM. חובה רק אם הערך של connector.tls.mode הוא ENCRYPT_VERIFY_CA_AND_HOST או ENCRYPT_VERIFY_CA.
  • ‫assets: רשימה של שמות הטבלאות ב-PostgreSQL שיועברו ממסד הנתונים של PostgreSQL כחלק מההעברה.

לדוגמה, הפקודה הבאה יוצרת העברה של PostgreSQL בשם My Transfer:

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

API

משתמשים ב-‎projects.locations.transferConfigs.create method ומספקים מופע של TransferConfig resource.

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

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

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

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

סוג הנתונים של PostgreSQL	סוג נתונים ב-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	JSON
jsonb	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
timestamp [ (p) ] with time zone	TIMESTAMP
triangle	STRING
tsquery	STRING
tsvector	STRING
uuid	STRING
xml	STRING

פתרון בעיות

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

תמחור

מידע על התמחור של העברות PostgreSQL זמין במאמר בנושא תמחור של שירות העברת נתונים.

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

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