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

‫MySQL   |  PostgreSQL   |  SQL Server

בדף הזה מוסבר איך להריץ הצהרות 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 מתאים להרצת הצהרות אדמיניסטרטיביות קטנות ומהירות, כמו יצירת תפקידים או משתמשים במסד נתונים וביצוע עדכונים קטנים בסכימה.

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

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

הגדרת משתמש במסד הנתונים

כדי להריץ הצהרות SQL, צריך לאמת את Data API כמשתמש במסד הנתונים.

כדי לבצע אימות כמשתמש מובנה באמצעות סיסמה:

1. יוצרים חשבון משתמש עם סיסמה לא ריקה. אפשר גם להשתמש במשתמש ברירת המחדל sqlserver.
2. מקצים לחשבון את התפקידים או ההרשאות הנדרשים כדי להריץ הצהרות SQL. אם המשתמש לא sqlserver, נותנים למשתמש את התפקיד db_owner.
3. משתמשים ב-Secret Manager כדי ליצור Secret אזורי לאחסון הסיסמה. מטעמי אבטחה, בבקשת ה-API, Data API מבקש את שם המשאב של הסוד במקום את הסיסמה. הסוד האזורי צריך להיות מאוחסן באותו אזור שבו נמצאת מכונת Cloud SQL. לא ניתן להשתמש בסוד שנוצר באמצעות נקודת הקצה הגלובלית של Secret Manager, גם אם הוא מאוחסן באותו אזור.
4. מומלץ להגדיר תנאים ב-IAM כדי לאפשר למשתמש גישה לסוד ספציפי אבל לא לסודות אחרים בפרויקט.

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

כברירת מחדל, למשתמשים או לחשבונות שירות עם אחד מהתפקידים הבאים יש הרשאה להריץ הצהרות 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.

אימות באמצעות סיסמה

אפשר להריץ הצהרות SQL באמצעות אימות סיסמה מובנה, כשהסיסמה מאוחסנת כסוד אזורי ב-Secret Manager באותו אזור כמו מופע Cloud SQL.

gcloud

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

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--user=USER \
--password-secret-version=PASSWORD_SECRET_VERSION \
--partial-result-mode=PARTIAL_RESULT_MODE

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

• ‫INSTANCE_NAME: השם של המכונה.
• ‫DATABASE_NAME: השם של מסד הנתונים בתוך המכונה.
• ‫SQL_STATEMENT: הצהרת ה-SQL להרצה. אם ההצהרה מכילה רווחים או תווים מיוחדים של מעטפת, צריך להוסיף לה מרכאות.
• USER: משתמש מסד הנתונים שיש לבצע אימות בתורו.
• ‫PASSWORD_SECRET_VERSION: שם המשאב של הסוד ב-Secret Manager שמכיל את הסיסמה של משתמש מסד הנתונים. הסוד צריך להיות סוד אזורי ולאחסן אותו באותו אזור שבו נמצא מופע Cloud SQL. הפורמט הצפוי של שם המשאב הוא projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• ‫PARTIAL_RESULT_MODE: אופציונלי. המדיניות הזו קובעת איך להגיב כשהתוצאה לא מלאה. יכול להיות ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT או PARTIAL_RESULT_MODE_UNSPECIFIED. מידע נוסף על שינוי התנהגות החיתוך

Terraform

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

resource "google_sql_user" "built_in_user" {
  name     = "tf-user"
  host     = "%"  # Don't set this field for PostgreSQL and SQL Server.
  instance = google_sql_database_instance.instance.name
  password = "changeme"
  type     = "BUILT_IN"
}

# Create a regional secret. Global secrets are not supported even if
# located in one region only.
resource "google_secret_manager_regional_secret" "secret" {
  secret_id = "db-password"

  # Use the same region as the Cloud SQL instance.
  location = "us-central1"
}

resource "google_secret_manager_regional_secret_version" "secret_version" {
  secret = google_secret_manager_regional_secret.secret.id
  secret_data = "changeme"
}

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"
  user = google_sql_user.built_in_user.name

  # The location should be the same as the Cloud SQL instance's location.
  password_secret_version = "projects/my-project/locations/us-central1/secrets/db-password/versions/latest"

  # The built-in database user and password secret version must be created
  # first. Cloud SQL will retrieve password from Secret Manager
  # and connect to this user account to execute your script.
  depends_on = [
    google_sql_user.built_in_user,
    google_secret_manager_regional_secret_version.secret_version
  ]
}

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

כדי להחיל את הגדרות 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.

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

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

מחיקה של משאב 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",
  "user": "USER",
  "passwordSecretVersion": "PASSWORD_SECRET_VERSION",
  "partialResultMode": "PARTIAL_RESULT_MODE"
}

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

• PROJECT_ID: מזהה הפרויקט.
• ‫INSTANCE_NAME: השם של המכונה.
• ‫DATABASE_NAME: השם של מסד הנתונים בתוך המכונה.
• ‫SQL_STATEMENT: הצהרת ה-SQL להרצה.
• USER: משתמש מסד הנתונים שיש לבצע אימות בתורו.
• ‫PASSWORD_SECRET_VERSION: שם המשאב של הסוד ב-Secret Manager שמכיל את הסיסמה של משתמש מסד הנתונים. הסוד צריך להיות סוד אזורי ולאחסן אותו באותו אזור שבו נמצא מופע Cloud SQL. הפורמט הצפוי של שם המשאב הוא projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.
• ‫PARTIAL_RESULT_MODE: אופציונלי. קובעת איך ה-API מגיב כשגודל התוצאה חורג מ-10MB. יכול להיות FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT או PARTIAL_RESULT_MODE_UNSPECIFIED. מידע נוסף על שינוי התנהגות החיתוך

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

אתם יכולים לקבוע איך המערכת תטפל בתוצאות גדולות כשמריצים SQL, על ידי הכללת השדה "partialResultMode" בבקשה. בשדה הזה אפשר להזין את הערכים הבאים:

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

מגבלות

• המגבלה על גודל התגובה היא 10 MB. אם התוצאות חורגות מהגודל הזה, הן נחתכות אם הערך של partialResultMode הוא ALLOW_PARTIAL_RESULT, אחרת מוצגת שגיאה.
• הבקשות מוגבלות ל-0.5 MB.
• אפשר להריץ הצהרות SQL רק עבור מופעים של Cloud SQL ל-SQL Server שפועלים.
• ‫Cloud SQL לא תומך בשימוש ב-Data API עם מופעים שמוגדרים לשכפול שרת חיצוני.
• בקשות שנמשכות יותר מ-30 שניות מבוטלות. אי אפשר להגדיר פסק זמן ארוך יותר להצהרה באמצעות SET LOCK_TIMEOUT.

• ב-Cloud SQL, מספר הבקשות executeSql בו-זמניות לכל מכונה מוגבל כדי למנוע עומס יתר. אם מגיעים למגבלה, הבקשות הבאות נכשלות ומוחזרת אחת מהשגיאות הבאות:

  • At most 'x' concurrent queries may be run on this instance. Try again later.
  • Maximum concurrent reads 'x' reached.

  המגבלה (x) היא 5 שאילתות למכונות עם פחות מ-10 GB של זיכרון כולל, ו-10 שאילתות למכונות עם 10 GB לפחות של זיכרון כולל.

• כל תגובה יכולה להכיל עד 10 הודעות או אזהרות ממסד הנתונים.

• אם יש שגיאה בתחביר או בהרצה של ההצהרה, לא מוחזרת תוצאה.

• אי אפשר לבצע אימות של Data API כמשתמשים מובנים עם סיסמאות ריקות.

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

• הפקודה GO לא נתמכת. הפקודה הזו משמשת בכלי השירות של Microsoft SQL Server כדי לציין שקבוצה של הצהרות הסתיימה ואפשר לשלוח אותה ל-SQL Server.

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

  לדוגמה, מחליפים את:

  SELECT my_binary_column from my_table2;
  

  עם:

  SELECT CONVERT(NVARCHAR(4000), my_binary_column, 1) from my_table2;
  

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

  BEGIN TRANSACTION
      YOUR_SQL_STATEMENTS
  COMMIT;
  

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

  • ‫YOUR_SQL_STATEMENTS: ההצהרות שרוצים להריץ כחלק מהשאילתה הזו

• יכול להיות שתסריט ה-SQL והתגובה להרצה שלו יעברו דרך מיקומים ביניים בין הלקוח לבין המיקום של מופע היעד. לכן, בקשות ייכשלו עם השגיאה 'לא נתמך עבור מופעים בתיקיות מסוימות של חבילות בקרה של Assured Workloads' עבור פרויקטים מסוימים של Assured Workloads ועבור פרויקטים עם constraints/sql.restrictNoncompliantResourceCreation שנאכף באופן ידני.