יצירה וניהול של עמודות שנוצרו על ידי AI

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

הוספת עמודה שנוצרה לטבלה חדשה

בקטע הקוד CREATE TABLE הבא, אנחנו יוצרים טבלה לאחסון מידע על משתמשים. יש לנו עמודות ל-FirstName ול-LastName, ואנחנו מגדירים עמודה שנוצרת באופן אוטומטי ל-FullName, שהיא שרשור של FirstName ושל LastName. ה-SQL שבסוגריים נקרא ביטוי יצירה.

אפשר לסמן עמודה שנוצרה כ-STORED כדי לחסוך את העלות של הערכת הביטוי בזמן השאילתה. כתוצאה מכך, הערך של FullName מחושב רק כשמוסיפים שורה חדשה או כשמעדכנים את FirstName או LastName בשורה קיימת. הערך המחושב מאוחסן יחד עם עמודות אחרות בטבלה.

GoogleSQL 

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (FirstName || ' ' || LastName) STORED
) PRIMARY KEY (Id);

PostgreSQL 

CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);

כדי ליצור עמודה וירטואלית, משמיטים את מאפיין STORED ב-DDL. הערכה של עמודה שנוצרה כזו מתבצעת בזמן השאילתה, והיא יכולה לפשט את השאילתה. ב-PostgreSQL, אפשר ליצור עמודה וירטואלית שלא מאוחסנת באמצעות המאפיין VIRTUAL.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

  • ‫expression יכול להיות כל ביטוי SQL תקין שאפשר להקצות אותו לסוג הנתונים של העמודה, עם ההגבלות הבאות.

    • הביטוי יכול להתייחס רק לעמודות באותה טבלה.

    • הביטוי לא יכול להכיל שאילתות משנה.

    • אי אפשר להפוך ביטויים עם פונקציות לא דטרמיניסטיות כמו PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() ו- CURRENT_TIMESTAMP() לעמודה שנוצרה על ידי STORED או לעמודה שנוצרה עם אינדקס.

    • אי אפשר לשנות את הביטוי של עמודה וירטואלית STORED או של עמודה שנוצרה עם אינדקס.

  • במסדי נתונים של ניב GoogleSQL, לעמודה וירטואלית שנוצרה מסוג STRING או BYTES חייבת להיות אורך של MAX.

  • במסדי נתונים עם ניב PostgreSQL, לעמודה וירטואלית שנוצרה ולא מאוחסנת, מהסוג VARCHAR, צריכה להיות אורך של MAX.

  • המאפיין STORED שמופיע אחרי הביטוי מאחסן את התוצאה של הביטוי יחד עם עמודות אחרות בטבלה. עדכונים עתידיים של אחת מהעמודות שאליהן יש הפניה גורמים ל-Spanner להעריך מחדש את הביטוי ולאחסן אותו.

  • אי אפשר לסמן עמודות שנוצרו ולא מסוג STORED כNOT NULL.

  • אסור לכתוב ישירות בעמודות שנוצרו.

  • אי אפשר להשתמש באפשרות העמודה allow_commit_timestamp בעמודות שנוצרו או בעמודות שהעמודות שנוצרו מפנות אליהן.

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

  • אי אפשר להשמיט עמודה שעמודה שנוצרה מפנה אליה.

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

    • המפתח הראשי שנוצר לא יכול להפנות לעמודות אחרות שנוצרו.

    • המפתח הראשי שנוצר יכול להפנות לעמודה אחת לכל היותר שאינה עמודת מפתח.

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

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

    • ממשקי API לקריאה: צריך לציין באופן מלא את עמודות המפתח, כולל עמודות המפתח שנוצרו.
    • ממשקי API של מוטציות: ב-INSERT,‏ INSERT_OR_UPDATE ו-REPLACE,‏ Spanner לא מאפשר לציין עמודות של מפתחות שנוצרו. ב-UPDATE, אפשר לציין עמודות של מפתחות שנוצרו. במקרה של DELETE, צריך לציין באופן מלא את עמודות המפתחות, כולל המפתחות שנוצרו.
    • DML: אי אפשר לכתוב באופן מפורש למפתחות שנוצרו בהצהרות INSERT או UPDATE.
    • שאילתה: באופן כללי, מומלץ להשתמש בעמודה של המפתח שנוצר כמסנן בשאילתה. אופציונלית, אם הביטוי של עמודת המפתח שנוצרה משתמש רק בעמודה אחת כהפניה, השאילתה יכולה להחיל תנאי שוויון (=) או IN על העמודה שאליה מתבצעת ההפניה. מידע נוסף ודוגמה מופיעים במאמר יצירת מפתח ייחודי שנגזר מעמודת ערכים.

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

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

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

GoogleSQL 

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL 

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

יצירת אינדקס בעמודה שנוצרה

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

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

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

הוספת עמודה שנוצרה לטבלה קיימת

באמצעות ההצהרה ALTER TABLE הבאה, אפשר להוסיף עמודה שנוצרה לטבלה Users כדי ליצור ולאחסן את ראשי התיבות של שם המשתמש.

GoogleSQL 

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL 

ALTER TABLE users ADD COLUMN initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(firstname, 0, 1) || SUBSTR(lastname, 0, 1)) STORED;

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

יצירת אינדקס חלקי באמצעות עמודה שנוצרה

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

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

    GoogleSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL));

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;

  2. יוצרים אינדקס בעמודה החדשה הזו ומשביתים את יצירת האינדקס של ערכים עם מילת המפתח NULL NULL_FILTERED ב-GoogleSQL או עם התנאי IS NOT NULL ב-PostgreSQL. האינדקס החלקי הזה קטן ויעיל יותר מאינדקס רגיל, כי הוא לא כולל את כל מי שגילו 18 ומטה.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. כדי לאחזר את Id ואת Age של כל המשתמשים מעל גיל 18, מריצים את השאילתה הבאה.

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 IS NOT NULL;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 IS NOT NULL;

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

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 > 21;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 > 21;

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

הסרת עמודה שנוצרה

הצהרת ה-DDL הבאה משמיטה עמודה שנוצרה מהטבלה Users:

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

שינוי של ביטוי בעמודה שנוצר

GoogleSQL 

ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

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

יצירת מפתח ראשי בעמודה שנוצרה

ב-Spanner, אפשר להשתמש בSTOREDעמודה שנוצרה במפתח הראשי.

בדוגמה הבאה מוצגת הצהרת DDL שיוצרת את הטבלה UserInfoLog עם עמודה שנוצרת ShardId. הערך של העמודה ShardId תלוי בעמודה אחרת. הערך הזה נגזר משימוש בפונקציה MOD בעמודה UserId. ‫ShardId מוצהר כחלק מהמפתח הראשי.

GoogleSQL 

CREATE TABLE UserInfoLog (
  ShardId INT64 NOT NULL
  AS (MOD(UserId, 2048)) STORED,
  UserId INT64 NOT NULL,
  FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);

PostgreSQL 

CREATE TABLE UserInfoLog (
  ShardId BIGINT GENERATED ALWAYS
  AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
  UserId BIGINT NOT NULL,
  FullName VARCHAR(1024) NOT NULL,
  PRIMARY KEY(ShardId, UserId));

בדרך כלל, כדי לגשת ביעילות לשורה ספציפית, צריך לציין את כל עמודות המפתח. בדוגמה הקודמת, המשמעות היא שצריך לספק גם ShardId וגם UserId. עם זאת, לפעמים Spanner יכול להסיק את הערך של עמודת המפתח הראשי שנוצרה, אם הוא תלוי בעמודה אחרת יחידה ואם הערך של העמודה שהוא תלוי בה נקבע באופן מלא. התנאי הזה מתקיים אם העמודה שאליה מתייחסת עמודת המפתח הראשי שנוצרה עומדת באחד מהתנאים הבאים:

  • הוא שווה לערך קבוע או לפרמטר מאוגד בסעיף WHERE, או
  • הערך שלו מוגדר על ידי אופרטור IN בסעיף WHERE
  • הערך שלו מגיע מתנאי של צירוף שווה

לדוגמה, בשאילתה הבאה:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

מערכת Spanner יכולה להסיק את הערך של ShardId מהערך UserId שסופק. השאילתה הקודמת שקולה לשאילתה הבאה אחרי אופטימיזציה של השאילתה:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

בדוגמה הבאה מוצג אופן היצירה של הטבלה Students ושימוש בביטוי שמחלץ את השדה id מהעמודה StudentInfo מסוג JSON ומשתמש בו כמפתח הראשי:

GoogleSQL 

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (INT64(StudentInfo.id)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS ((StudentInfo ->> 'id')::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

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

ב-INFORMATION_SCHEMA של Spanner מופיע מידע על העמודות שנוצרו במסד הנתונים. ריכזנו כאן כמה דוגמאות לשאלות שאפשר לענות עליהן כשמבצעים שאילתה בסכימת המידע.

אילו עמודות שנוצרו מוגדרות במסד הנתונים שלי?

GoogleSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

‫IS_STORED הוא YES לעמודות וירטואליות מאוחסנות, NO לעמודות וירטואליות לא מאוחסנות או NULL לעמודות לא וירטואליות.

מה המצב הנוכחי של העמודות שנוצרו בטבלה Users?

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

  • ‫COMMITTED: אפשר להשתמש בעמודה באופן מלא.
  • ‫WRITE_ONLY: העמודה מתמלאת בנתונים רטרואקטיביים. אין אפשרות לקרוא.

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

GoogleSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL 

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

הערה: אפשר לגשת לעמודה שנוצרה ולא נשמרה רק באמצעות שאילתת SQL. אבל אם הוא באינדקס, אפשר להשתמש ב-API לקריאה כדי לגשת לערך מהאינדקס.

ביצועים

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

הביצועים של פעולות כתיבה (הצהרות DML ושינויים) מושפעים כשמשתמשים בSTOREDעמודה שנוצרה או בעמודה שנוצרה שעברה אינדוקס. התקורה נובעת מהערכה של הביטוי של העמודה שנוצרה כשפעולת הכתיבה מוסיפה או משנה עמודות כלשהן שהופנו אליהן בביטוי של העמודה שנוצרה. התקורה משתנה בהתאם לעומס העבודה של פעולות הכתיבה באפליקציה, לעיצוב הסכימה ולמאפיינים של מערך הנתונים, ולכן מומלץ לבצע בדיקות השוואה לאפליקציות לפני שמשתמשים בעמודה שנוצרה.

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