הרצת הצהרות SQL באמצעות Cloud SQL Data API

בדף הזה מוסבר איך להריץ הצהרות SQL במסדי נתונים במופעים של Cloud SQL באמצעות Data API. בעזרת Data API, אתם יכולים להשתמש ב-Cloud SQL Admin API וב-ה-CLI של gcloud כדי להריץ הצהרות SQL בכל מכונה שבה הפעלתם גישה ל-Data API.

אפשר להשתמש ב-Data API עם מופעים שמשתמשים בכתובות IP ציבוריות, בגישה לשירותים פרטיים או ב-Private Service Connect. ממשק Data API תומך בכל סוגי הצהרות SQL, כולל שפת טיפול בנתונים (DML), שפת הגדרת נתונים (DDL) ושפת שאילתות נתונים (DQL). ה-Data API מתאים להרצת הצהרות אדמיניסטרטיביות קטנות ומהירות, כמו יצירת תפקידים או משתמשים במסד נתונים וביצוע עדכונים קטנים בסכימה. אפשר גם להשתמש ב-Data API כדי להפעיל תוספים של PostgreSQL.

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

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

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

כברירת מחדל, למשתמשים או לחשבונות שירות עם אחד מהתפקידים הבאים יש הרשאה להריץ הצהרות SQL במופע Cloud SQL ‏ (cloudsql.instances.executesql):

  • Cloud SQL Admin (roles/cloudsql.admin)
  • Cloud SQL Instance User (roles/cloudsql.instanceUser)
  • Cloud SQL Studio User (roles/cloudsql.studioUser)

אפשר גם להגדיר תפקיד מותאם אישית ב-IAM למשתמש או לחשבון השירות שכולל את ההרשאה cloudsql.instances.executesql. ההרשאה הזו נתמכת בתפקידים בהתאמה אישית ב-IAM.

הפעלה או השבתה של Data API

כדי להשתמש ב-Data API, צריך להפעיל אותו לכל מופע. אפשר להשבית את Data API בכל שלב.

המסוף

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

    כניסה לדף Cloud SQL Instances

  2. כדי לפתוח את הדף סקירה כללית של מכונה, לוחצים על שם המכונה.
  3. בתפריט הניווט של SQL, בוחרים באפשרות Connections (קישורים).
  4. נכנסים לכרטיסייה Networking.
  5. מסמנים את תיבת הסימון Allow Data API (התרת שימוש ב-Data API).
  6. לוחצים על Save.

gcloud

כדי להפעיל גישה ל-Data API במופע, משתמשים בפקודה gcloud sql instances patch עם הדגל --data-api-access=ALLOW_DATA_API:

gcloud sql instances patch INSTANCE_NAME --data-api-access=ALLOW_DATA_API

כדי להשבית את הגישה ל-Data API, משתמשים בדגל --data-api-access=DISALLOW_DATA_API:

gcloud sql instances patch INSTANCE_NAME --data-api-access=DISALLOW_DATA_API

מחליפים את INSTANCE_NAME בשם המכונה שרוצים להפעיל או להשבית בה את Data API.

הפעלת הצהרת SQL

אפשר להריץ הצהרות SQL מול מסדי נתונים במופע Cloud SQL באמצעות ה-CLI של gcloud או API בארכיטקטורת REST.

gcloud

כדי להריץ הצהרת SQL במסד נתונים במכונה באמצעות ה-CLI של gcloud, משתמשים בפקודה gcloud sql instances execute-sql:

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--partial_result_mode=PARTIAL_RESULT_MODE

מחליפים את הפרטים הבאים:

  • ‫INSTANCE_NAME: השם של המכונה.
  • ‫DATABASE_NAME: השם של מסד הנתונים בתוך המכונה.
  • ‫SQL_STATEMENT: הצהרת ה-SQL לביצוע. אם ההצהרה מכילה רווחים או תווים מיוחדים של מעטפת, צריך להוסיף לה מרכאות.
  • ‫PARTIAL_RESULT_MODE: אופציונלי. המדיניות הזו קובעת איך להגיב כשהתוצאה לא מלאה. יכול להיות ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT או PARTIAL_RESULT_MODE_UNSPECIFIED. מידע נוסף על שינוי התנהגות החיתוך

אפשר גם להוסיף את הדגל --project=PROJECT_ID אם צריך.

Terraform

אפשר להשתמש ב-Data API ב-Terraform כדי להקצות משאבים במסד הנתונים, כמו מסדי נתונים, טבלאות, תוספים, משתמשים והענקת הרשאות, בלי להתחבר למופע באופן ידני. כדי להריץ סקריפט SQL ב-Terraform, משתמשים במשאב google_sql_provision_script Terraform.

resource "google_sql_database_instance" "instance" {
  name             = "my-instance"
  database_version = "POSTGRES_17"

  settings {
    tier            = "db-perf-optimized-N-2"
    data_api_access = "ALLOW_DATA_API"  # This allows the use of Data API.
    database_flags {
      name  = "cloudsql.iam_authentication"
      value = "on"
    }
  }
}

/*
 * Create a database user for your account and grant roles so it has privilege to
 * access the database. Set the type to CLOUD_IAM_USER for huamn account or
 * CLOUD_IAM_SERVICE_ACCOUNT for service account. If a service account is used
 * and the instance is Postgres, trim the ".gserviceaccount.com"
 * suffix to avoid exceeding the username length limit.
*/
resource "google_sql_user" "iam_user" {
  name     = "account-used-to-apply-this-config@example.com"
  instance = google_sql_database_instance.instance.name
  type     = "CLOUD_IAM_USER"

  # Roles granted to the user. Smaller roles are preferred, if exist.
  database_roles = ["cloudsqlsuperuser"]
}

resource "google_sql_database" "database" {
  name     = "my-database"
  instance = google_sql_database_instance.instance.name
}

resource "google_sql_provision_script" "script" {
  # You can inline the script or import from a file like script  = file("${path.module}/script.sql")
  # When modified, the whole script will be executed again. It's recommended to
  # make the script idempotent with patterns like create if not exists ... or
  # if not exists (select ...) then ... end if.
  script  = "CREATE TABLE IF NOT EXISTS table1 ( col VARCHAR(16) NOT NULL );"

  instance = google_sql_database_instance.instance.name
  database = google_sql_database.database.name
  description = "sql script to create tables"

  # The identity account used to apply your Terraform config must exist as an
  # IAM user or IAM service account in the instance. Terraform connects to the
  # instance via IAM database authentication to execute the script.
  depends_on = [google_sql_user.iam_user]
}

החלה של השינויים

כדי להחיל את הגדרות Terraform בפרויקט ב- Google Cloud , מבצעים את השלבים בקטעים הבאים.

הכנת Cloud Shell

  1. מפעילים את Cloud Shell.

  2. מגדירים את פרויקט ברירת המחדל שבו רוצים להחיל את ההגדרות של Terraform. Google Cloud

    תצטרכו להריץ את הפקודה הזו רק פעם אחת לכל פרויקט, ותוכלו לעשות זאת בכל ספרייה.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

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

הכנת הספרייה

לכל קובץ תצורה של Terraform צריכה להיות ספרייה משלו (שנקראת גם מודול ברמה הבסיסית).

  1. יוצרים ספרייה חדשה ב-Cloud Shell ובה יוצרים קובץ חדש. שם הקובץ חייב לכלול את הסיומת .tf, למשל main.tf. במדריך הזה, הקובץ נקרא main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. אם אתם עוקבים אחרי המדריך, תוכלו להעתיק את הקוד לדוגמה בכל קטע או שלב.

    מעתיקים את הקוד לדוגמה בקובץ main.tf החדש שיצרתם.

    לחלופין, אפשר גם להעתיק את הקוד מ-GitHub. כדאי לעשות את זה כשקטע הקוד של Terraform הוא חלק מפתרון מקצה לקצה.

  3. בודקים את הפרמטרים לדוגמה ומשנים אותם בהתאם לסביבה שלכם.
  4. שומרים את השינויים.
  5. מפעילים את Terraform. צריך לעשות זאת רק פעם אחת לכל ספרייה. 
    terraform init

    אופציונלי: תוכלו לכלול את האפשרות -upgrade, כדי להשתמש בגרסה העדכנית ביותר של הספק של Google: 

    terraform init -upgrade

החלה של השינויים

  1. בודקים את ההגדרות ומוודאים שהמשאבים שמערכת Terraform תיצור או תעדכן תואמים לציפיות שלכם: 
    terraform plan

    מתקנים את ההגדרות לפי הצורך.

  2. מריצים את הפקודה הבאה ומזינים yes בהודעה שמופיעה, כדי להחיל את הגדרות Terraform: 
    terraform apply

    ממתינים עד שב-Terraform תוצג ההודעה "Apply complete!‎".

  3. פותחים את Google Cloud הפרויקט כדי לראות את התוצאות. במסוף Google Cloud , נכנסים למשאבים בממשק המשתמש כדי לוודא שהם נוצרו או עודכנו ב-Terraform.

מחיקת השינויים

מחיקה של משאב google_sql_provision_script לא תמחק את המשאבים שנוצרו במסד הנתונים. כדי למחוק אותם, אפשר להוסיף הצהרות באופן מפורש בסקריפט, כמו drop ... if exists, ואז להחיל את השינויים.

REST

כדי להריץ הצהרת SQL מול מסד נתונים במופע באמצעות API בארכיטקטורת REST, שולחים בקשת POST לנקודת הקצה executeSql:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/executeSql

גוף הבקשה צריך לכלול את שם מסד הנתונים ואת הצהרת ה-SQL:

{
  "database": "DATABASE_NAME",
  "sqlStatement": "SQL_STATEMENT",
  "partialResultMode": "PARTIAL_RESULT_MODE"
  "autoIamAuthn": true
}

מחליפים את הפרטים הבאים:

  • PROJECT_ID: מזהה הפרויקט.
  • ‫INSTANCE_NAME: השם של המכונה.
  • ‫DATABASE_NAME: השם של מסד הנתונים בתוך המכונה.
  • ‫SQL_STATEMENT: הצהרת ה-SQL לביצוע.
  • ‫PARTIAL_RESULT_MODE: אופציונלי. קובעת איך ה-API מגיב כשהתוצאה גדולה מ-10MB. הערך יכול להיות FAIL_PARTIAL_RESULT או ALLOW_PARTIAL_RESULT. מידע נוסף על שינוי התנהגות החיתוך

שינוי התנהגות הקיצור

אתם יכולים לקבוע איך לטפל בתוצאות גדולות כשמריצים SQL.

  • הבקשה צריכה לכלול את השדה "partialResultMode". אפשר להזין בשדה הזה את הערכים הבאים:
    • ‫FAIL_PARTIAL_RESULT: השגיאה תופעל אם התוצאה חורגת מ-10 MB או אם אפשר לאחזר רק תוצאה חלקית. לא להחזיר את התוצאה.
    • ‫ALLOW_PARTIAL_RESULT: מחזירה תוצאה קטועה ומגדירה את partial_result כ-true אם התוצאה גדולה מ-10 MB או אם אפשר לאחזר רק תוצאה חלקית בגלל שגיאה. לא להקפיץ הודעת שגיאה.

מגבלות

  • המגבלה על גודל התגובה היא 10 MB. אם התוצאות חורגות מהגודל הזה, הן נחתכות אם הערך של partialResultMode הוא ALLOW_PARTIAL_RESULT. אחרת, מוצגת שגיאה.
  • הבקשות מוגבלות ל-0.5 MB.
  • אפשר להריץ הצהרות SQL רק עבור מופעים של Cloud SQL ל-PostgreSQL שפועלים.
  • ‫Cloud SQL לא תומך בשימוש ב-Data API עם מופעים שמוגדרים לשכפול שרת חיצוני.
  • בקשות שנמשכות יותר מ-30 שניות מבוטלות. אי אפשר להגדיר פסק זמן ארוך יותר להצהרה באמצעות SET STATEMENT_TIMEOUT.
  • ב-Cloud SQL, מספר הבקשות המקבילות של executeSql מוגבל ל-10 לכל מופע לכל משתמש. אם מגיעים למגבלה הזו, בקשות עוקבות נכשלות עם השגיאה 'אפשר להריץ לכל היותר 10 שאילתות בו-זמנית במופע הזה'. כדאי לנסות שוב מאוחר יותר" או "הגעת למספר המקסימלי של קריאות בו-זמניות: 10".
  • כל תגובה יכולה להכיל עד 10 הודעות או אזהרות ממסד הנתונים.
  • אם יש שגיאה בתחביר או בהרצה של ההצהרה, לא מוחזרת תוצאה.
  • הצהרות שצורכות כמות גדולה של זיכרון עלולות לגרום לשגיאות שקשורות לזיכרון. מידע נוסף על הימנעות מהשגיאות האלה זמין במאמר שיטות מומלצות לניהול השימוש בזיכרון. מופע של מסד נתונים שפועל עם ניצול גבוה של הזיכרון גורם לעיתים קרובות לבעיות בביצועים, להשהיות או אפילו להשבתה של מסד הנתונים.
  • יכול להיות ש-Data API ייחסם באופן זמני למטרות של שמירה על תקינות נתונים, בזמן שמתבצעות פעולות תחזוקה מסוימות במופע. אם זה קורה, אפשר לנסות שוב מאוחר יותר.
  • יכול להיות שתסריט ה-SQL והתגובה להרצה שלו יעברו דרך מיקומים ביניים בין הלקוח לבין המיקום של מופע היעד. לכן, בקשות ייכשלו עם השגיאה 'לא נתמך עבור מופעים בתיקיות מסוימות של חבילות בקרה של Assured Workloads' עבור פרויקטים מסוימים של Assured Workloads ועבור פרויקטים עם constraints/sql.restrictNoncompliantResourceCreation שנאכף באופן ידני.