קישור אל PostgreSQL

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

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

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

  • מכונות PostgreSQL מקומיות שמחוברות ל- Google Cloudבאמצעות Cloud VPN או Cloud Interconnect
  • מכונות PostgreSQL בניהול עצמי ב-Compute Engine
  • מכונות PostgreSQL שמתארחות בענן ושאפשר לגשת אליהן דרך כתובות IP ציבוריות או מחברי רשת

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

  • ‫PostgreSQL 16 (מומלץ)
  • ‫PostgreSQL 15

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

מפעילים את ממשקי ה-API הנדרשים, מגדירים את מסד הנתונים של PostgreSQL ונותנים תפקידים בניהול זהויות והרשאות גישה (IAM) שמעניקים למשתמשים את ההרשאות הנדרשות כדי לבצע כל משימה במסמך הזה.

  1. מפעילים את BigQuery Connection API.

    להפעלת ה-API

  2. אם אתם מתכננים לנתב תנועה דרך רשתות פרטיות באמצעות חיבורי רשת, צריך להפעיל גם את Compute Engine API.

    הפעלת Compute Engine API

  3. במסד הנתונים של PostgreSQL, יוצרים משתמש ייעודי למסד הנתונים עם הרשאות SELECT בטבלאות או בסכימה שרוצים לשלוח אליהן שאילתות:

    CREATE USER USER_NAME WITH PASSWORD 'PASSWORD';
GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;

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

    • ‫USER_NAME: שם המשתמש של המשתמש במסד הנתונים.
    • ‫PASSWORD: הסיסמה של המשתמש במסד הנתונים.
    • ‫SCHEMA_NAME: הסכימה של הטבלאות שרוצים לשלוח אליהן שאילתות.

  4. מגדירים את הקישוריות לרשת של מכונה של מסד נתונים ב-PostgreSQL באחת מהדרכים הבאות:

    • אם אתם מנתבים תנועה דרך רשתות פרטיות, צרו מחבר רשת באותו אזור שבו נמצא החיבור ל-BigQuery. אין תמיכה בקישור בין רשתות שכנות (peering) ב-DNS. צריך להתחבר באמצעות כתובת IP או שם מארח שאפשר לזהות ברשת ה-VPC.
    • אם אתם מתחברים דרך האינטרנט הציבורי, הוסיפו את כל טווחי כתובות ה-IP של Google לכללי חומת האש של מסד הנתונים של PostgreSQL.

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

כדי לקבל את ההרשאות שדרושות להתחברות ל-PostgreSQL, בקשו מהאדמין להקצות לכם את התפקידים הבאים ב-IAM:

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

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

חיבור של BigQuery ל-PostgreSQL

אפשר לקשר את BigQuery ל-PostgreSQL במסוףGoogle Cloud או בכלי שורת הפקודה של BigQuery.

המסוף

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

    כניסה לדף BigQuery

  2. בחלונית Explorer, לוחצים על Add data.

    הדף Add data נפתח.

  3. בחלונית Filter By, בקטע Data Source Type בוחרים באפשרות Databases.

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

  4. בקטע מקורות נתונים נבחרים, לוחצים על PostgreSQL.

  5. בקטע Access external data in place, לוחצים על כרטיס הפתרון BigQuery Federation עם התיאור Federated queries to PostgreSQL (לא על הכרטיסים של Cloud SQL או AlloyDB ל-PostgreSQL).

    החלונית External data source נפתחת.

  6. בחלונית External data source, מגדירים את הפרטים הבאים:

    • בשדה Connection ID, מזינים מזהה חיבור ייחודי (לדוגמה, postgres_prod_conn).
    • בקטע Location type, בוחרים באפשרות Region ואז בוחרים את האזור שבו נמצא מערך הנתונים ב-BigQuery. שאילתות שמשתמשות בחיבור הזה חייבות לפעול מהאזור הזה.
    • (אופציונלי) בשדה Friendly name, מזינים שם תיאורי לחיבור.
    • (אופציונלי) בשדה Description, מזינים תיאור למשאב החיבור.
    • בקטע Encryption, בוחרים באפשרות Google-managed encryption key או באפשרות Cloud KMS key כדי להשתמש במפתח הצפנה בניהול הלקוח (CMEK).
    • בשדה Host:port, מזינים את המארח והיציאה של שרת מסד הנתונים של PostgreSQL בפורמט HOST:PORT (לדוגמה, 10.10.2.2:5432).
    • בשדה Username, מזינים את משתמש מסד הנתונים שיצרתם בדרישות המוקדמות.
    • בשדה Password, מזינים את הסיסמה של המשתמש במסד הנתונים.
    • בשדה מסד נתונים, מזינים את השם של מסד הנתונים ב-PostgreSQL.
    • ‫(אופציונלי) בשדה Network attachment, אם אתם משתמשים בקישוריות פרטית, מזינים את נתיב המשאב של חיבור הרשת בפורמט projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME.
    • ברשימה מצב TLS, בוחרים באחד ממצבי ה-TLS הנתמכים (לדוגמה, הצפנת נתונים, אימות של רשות האישורים (CA) ושם המארח (מומלץ)).
    • ברשימה רשות אישורים (CA), בוחרים באפשרות רשות אישורים ציבורית (Web PKI), או בוחרים רשות אישורים בהתאמה אישית ומדביקים את האישור בפורמט PEM.

  7. לוחצים על יצירת חיבור.

BQ

משתמשים בפקודה bq mk עם הדגל --connection:

bq mk --connection \
  --location=LOCATION \
  --project_id=PROJECT_ID \
  --connector_configuration '{
    "connector_id": "postgresql",
    "endpoint": {
      "host_port": "HOST_PORT"
    },
    "asset": {
      "database": "DATABASE"
    },
    "authentication": {
      "username_password": {
        "username": "USERNAME",
        "password": {
          "plaintext": "PASSWORD"
        }
      }
    },
    "tls": {
      "mode": "TLS_MODE"
    },
    "network": {
      "private_service_connect": {
        "network_attachment": "projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME"
      }
    }
  }' \
  CONNECTION_ID

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

  • ‫LOCATION: האזור ב-BigQuery (לדוגמה, us-central1) שתואם למערך הנתונים שרוצים לשלוח אליו שאילתות יחד עם PostgreSQL. צריך להריץ מהאזור הזה שאילתות שמשתמשות בחיבור הזה.
  • ‫PROJECT_ID: מזהה הפרויקט ב- Google Cloud .
  • ‫HOST_PORT: המארח והיציאה של שרת מסד הנתונים של PostgreSQL בפורמט HOST:PORT (לדוגמה, 10.10.2.2:5432).
  • ‫DATABASE: השם של מסד הנתונים ב-PostgreSQL.
  • ‫USERNAME: המשתמש במסד הנתונים של PostgreSQL.
  • ‫PASSWORD: הסיסמה של המשתמש במסד הנתונים.
  • ‫TLS_MODE: מצב ההצפנה של TLS (ENCRYPT_VERIFY_CA_AND_HOST,‏ ENCRYPT_VERIFY_CA,‏ ENCRYPT_VERIFY_NONE או DISABLE). אם משתמשים באישור CA בהתאמה אישית עם ENCRYPT_VERIFY_CA_AND_HOST או ENCRYPT_VERIFY_CA, צריך לכלול את האובייקט "private_pki": { "trusted_certificates_pem": "CERT_CONTENT" } בקטע "tls".
  • ‫NA_REGION: האזור שבו נמצא מחבר הרשת.
  • ‫ATTACHMENT_NAME: השם של חיבור הרשת. אם מתחברים באמצעות כתובת IP ציבורית, משמיטים את האובייקט "network".
  • ‫CONNECTION_ID: המזהה הייחודי של החיבור הזה.
  • ‫--kms_key_name=KMS_KEY_NAME (אופציונלי): מפתח הצפנה בניהול הלקוח (CMEK). אם לא מציינים את הפרמטר, פרטי הכניסה מוגנים על ידי ברירת המחדל של Google-owned and Google-managed encryption key.

הגדרת Transport Layer Security (TLS)

המחבר של PostgreSQL תומך ב-Transport Layer Security‏ (TLS) כדי להצפין את תנועת השאילתות במעבר בין BigQuery לבין מסד הנתונים של PostgreSQL.

כשמקשרים את BigQuery ל-PostgreSQL, בוחרים אחד ממצבי ה-TLS הבאים:

  • הצפנת נתונים ואימות של רשות האישורים (CA) ושם המארח (מצב ENCRYPT_VERIFY_CA_AND_HOST). מבצע אימות מלא של השרת באמצעות TLS. הצפנה של כל הנתונים במעבר, אימות שהאישור של שרת מסד הנתונים חתום על ידי רשות אישורים (CA) מהימנה, ובדיקה ששם המארח שאליו מתחברים תואם לשם הנפוץ (CN) או לשם החלופי של הבעלים (SAN) באישור. חובה לחיבורים דרך כתובות IP ציבוריות.

  • הצפנת נתונים ואימות של רשות האישורים בלבד (מצב ENCRYPT_VERIFY_CA). הצפנה של כל הנתונים במעבר ואימות שהאישור של השרת נחתם על ידי רשות אישורים (CA) מהימנה, אבל בלי לאמת את שם המארח של השרת. השתמשו במצב הזה אם אישור השרת חתום על ידי רשות אישורים (CA) מהימנה, אבל אתם מתחברים באמצעות כתובת IP או שם מארח שלא תואמים ל-CN או ל-SAN של האישור.

  • הצפנה בלבד (מצב ENCRYPT_VERIFY_NONE). הצפנה של כל הנתונים שמועברים בין BigQuery לשרת. לא מבצע אימות של האישור או של שם המארח. מומלץ רק בתוך רשתות VPC פרטיות.

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

אישורי שרת מהימנים בפורמט PEM

אם אתם משתמשים במצב ENCRYPT_VERIFY_CA_AND_HOST או במצב ENCRYPT_VERIFY_CA:

  • אם האישור של שרת מסד הנתונים נחתם על ידי רשות אישורים ציבורית (כמו אלה שכלולות בתוכנית Mozilla Root CA, כולל Let's Encrypt,‏ DigiCert או Google Trust Services), אתם לא צריכים לספק אישור. אישורי בסיס של רשויות אישורים ציבוריות נחשבים מהימנים כברירת מחדל.
  • אם אתם משתמשים באישור שחתום על ידי רשות אישורים פנימית או פרטית בארגון, או באישור בחתימה עצמית, אתם צריכים לספק את שרשרת האישורים המלאה בפורמט PEM (דואר עם פרטיות משודרגת) בשדה אישור PEM מהימן במהלך הגדרת החיבור.

שיתוף חיבורים עם משתמשים

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

  • ‫roles/bigquery.connectionUser: מאפשרת למשתמשים להשתמש בחיבורים כדי להתחבר למקורות נתונים חיצוניים ולהריץ עליהם שאילתות.

  • ‫roles/bigquery.connectionAdmin: מאפשר למשתמשים לנהל חיבורים.

במאמר תפקידים והרשאות מוגדרים מראש תוכלו לקרוא מידע נוסף על תפקידים והרשאות של IAM ב-BigQuery.

בוחרים באחת מהאפשרויות הבאות:

המסוף

  1. נכנסים לדף BigQuery.

    כניסה ל-BigQuery

    החיבורים מופיעים בפרויקט בקבוצה שנקראת Connections.

  2. בחלונית הימנית, לוחצים על Explorer:

    כפתור מודגש לחלונית Explorer.

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

  3. לוחצים על הפרויקט, לוחצים על Connections ובוחרים חיבור.

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

    1. בתיבת הדו-שיח Connection permissions, משתפים את החיבור עם חשבונות משתמשים אחרים על ידי הוספה או עריכה של חשבונות משתמשים.

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

BQ

אי אפשר לשתף חיבור באמצעות כלי שורת הפקודה של BigQuery. כדי לשתף חיבור, משתמשים במסוף Google Cloud או ב-BigQuery Connections API.

API

משתמשים בשיטה projects.locations.connections.setIAM בקטע של מאמרי העזרה של BigQuery Connections REST API, ומספקים מופע של המשאב policy.

Java

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי הוראות ההגדרה של Java במדריך למתחילים של BigQuery באמצעות ספריות לקוח. למידע נוסף, קראו את מאמרי העזרה של BigQuery Java API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח. 

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:example-analyst-group@google.com")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}

תמחור

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

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