‫Google משתמשת בטכנולוגיית AI כדי לתרגם תוכן לשפה המועדפת עליך. בתרגומים כאלו עשויות להיות שגיאות.

הרצת הצהרות 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 כמשתמש במסד הנתונים.

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

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

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

לחשבונות המשתמשים או לחשבונות השירות שמשמשים לקריאה ל-Data API צריכה להיות ההרשאה cloudsql.instances.executesql כדי להריץ הצהרות SQL. ההרשאה כלולה באחד מהתפקידים המוגדרים מראש הבאים:

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.

כשמשתמשים בסוד של Secret Manager לצורך אימות, למשתמש או לחשבון השירות צריכה להיות גם הרשאה לגשת לסוד, secretmanager.versions.access. ההרשאה כלולה באחד מהתפקידים המוגדרים מראש הבאים:

Secret Manager Secret Accessor (roles/secretmanager.secretAccessor)
Secret Manager Admin (roles/secretmanager.admin)

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

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

המסוף

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

כניסה לדף Cloud SQL Instances
כדי לפתוח את הדף Overview של מכונה, לוחצים על שם המכונה.
בתפריט הניווט של SQL, לוחצים על Connections.
נכנסים לכרטיסייה Networking.
מסמנים את התיבה Allow Data API.
לוחצים על 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.

REST

כדי להפעיל גישה ל-Data API במכונה, שולחים בקשת PATCH לנקודת הקצה (endpoint) של instances.patch:

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

גוף הבקשה צריך להכיל את השדה dataApiAccess שמוגדר לערך ALLOW_DATA_API:

{
  "dataApiAccess": "ALLOW_DATA_API"
}

כדי להשבית את הגישה ל-Data API, מגדירים את dataApiAccess לערך DISALLOW_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

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

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

הכנת הספרייה

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

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

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

לחלופין, אפשר גם להעתיק את הקוד מ-GitHub. כדאי לעשות את זה כשקטע הקוד של Terraform הוא חלק מפתרון מקצה לקצה.
בודקים את הפרמטרים לדוגמה ומשנים אותם בהתאם לסביבה שלכם.
שומרים את השינויים.
מפעילים את Terraform. צריך לעשות זאת רק פעם אחת לכל ספרייה.
```
terraform init
```
אופציונלי: תוכלו לכלול את האפשרות -upgrade, כדי להשתמש בגרסה העדכנית ביותר של הספק של Google:
```
terraform init -upgrade
```

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

בודקים את ההגדרות ומוודאים שהמשאבים שמערכת Terraform תיצור או תעדכן תואמים לציפיות שלכם:
```
terraform plan
```
מתקנים את ההגדרות לפי הצורך.
מריצים את הפקודה הבאה ומזינים yes בהודעה שמופיעה, כדי להחיל את הגדרות Terraform:
```
terraform apply
```
ממתינים עד שב-Terraform תוצג ההודעה "Apply complete!‎".
פותחים את הפרויקט ב- Google Cloud כדי להציג את התוצאות. במסוף Google Cloud , נכנסים למשאבים בממשק המשתמש כדי לוודא שהם נוצרו או עודכנו ב-Terraform.

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

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

REST

כדי להריץ הצהרת SQL במסד נתונים במכונה באמצעות ה-API בארכיטקטורת REST, שולחים בקשת POST לנקודת הקצה (endpoint) של 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: ברירת המחדל. אם התוצאה חורגת מ-10MB או אם אפשר לאחזר רק תוצאה חלקית, תוצג שגיאה. אל תחזיר את התוצאה.
‫ALLOW_PARTIAL_RESULT: החזרת תוצאה מקוצרת והגדרת partial_result ל-true אם התוצאה חורגת מ-10MB או אם אפשר לאחזר רק תוצאה חלקית בגלל שגיאה. לא להציג שגיאה.
‫PARTIAL_RESULT_MODE_UNSPECIFIED: מצב לא מוגדר, זהה בפועל ל-FAIL_PARTIAL_RESULT.

שאילתות ביקורת

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

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

אתם יכולים גם להשתמש בביקורת של מסד נתונים ב-SQL Server כדי לתעד שאילתות למטרות אבטחה או תאימות.

מגבלות

גודל התשובה המקסימלי הוא 10MB. תוצאות שחורגות מהגודל הזה נחתכות אם partialResultMode מוגדר ל-ALLOW_PARTIAL_RESULT, אחרת נזרקת שגיאה.
הבקשות מוגבלות ל-0.5MB.
אפשר להריץ הצהרות 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 שאילתות למכונות עם פחות מ-10GB של זיכרון כולל, ו-10 שאילתות למכונות עם לפחות 10GB של זיכרון כולל.
כל תשובה יכולה להכיל עד 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 והתגובה להפעלה שלו עשויים לעבור דרך מיקומי ביניים בין הלקוח לבין המיקום של מכונת היעד. לכן, בקשות ייכשלו עם השגיאה "not supported for instances in certain Assured Workloads control packages folders" בפרויקטים מסוימים של Assured Workloads ובפרויקטים שבהם constraints/sql.restrictNoncompliantResourceCreation נאכף באופן ידני.

פתרון בעיות

בקטע הזה מפורט מידע על בעיות שקשורות לשימוש ב-Data API, וגם שלבים לפתרון הבעיות.

שגיאה	פתרון בעיות
`The instance doesn't allow using ExecuteSql to access this instance. You can allow it by patching the instance with {settings: { dataApiAccess: "ALLOW_DATA_API" }}`	‫Data API מושבת כברירת מחדל. כדי לפתור את הבעיה, מפעילים את Data API במכונה.
`Secret cannot be provided when auto_iam_authn is true.`	כשמגדירים את `auto_iam_authn` ל-`true`, מבצעים אימות למסד הנתונים באמצעות IAM. בשיטת האימות הזו לא נדרשת סיסמה או סוד. אימות באמצעות IAM
`ExecuteSql API is not supported for instances in certain Assured Workloads control packages folders yet.`	סקריפט ה-SQL והתגובה להפעלה שלו עשויים לעבור דרך מיקומי ביניים בין הלקוח לבין המיקום של מכונת היעד. לכן, בקשות ייכשלו במכונות בפרויקטים מסוימים של Assured Workloads. אם הפרויקט לא רשום ל-Assured Workloads אבל `constraints/sql.restrictNoncompliantResourceCreation` נאכף באופן ידני, צריך לבקש מהאדמין של הארגון להסיר את האילוץ, והבעיה תיפתר במכונות חדשות שייווצרו.
`The server principal USERNAME is not able to access the database DATABASE_NAME under the current security context.`	המשתמש לא חבר במסד הנתונים. מתחברים למסד הנתונים כמשתמש `sqlserver` ומוסיפים את המשתמש החדש, ואז נותנים למשתמש החדש את התפקיד `db_owner` במסד הנתונים. לדוגמה: EXEC sp_adduser 'user'; EXEC sp_addrolemember 'db_owner', 'user'
`The database is currently unavailable.`	יכול להיות שהמכונה מופעלת מחדש, עוברת תחזוקה או במצב לא תקין. צריך לבדוק את הסטטוס של המכונה ולנסות שוב מאוחר יותר.

הרצת הצהרות SQL באמצעות Cloud SQL Data API קל לארגן דפים בעזרת אוספים אפשר לשמור ולסווג תוכן על סמך ההעדפות שלך.

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

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

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

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

המסוף

gcloud

REST

הרצת הצהרת SQL

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

gcloud

Terraform

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

הכנת Cloud Shell

הכנת הספרייה

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

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

REST

שינוי ההתנהגות של קיצור הטקסט

שאילתות ביקורת

מגבלות

פתרון בעיות

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