ניהול של אינדקסי החיפוש

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

יצירת אינדקס חיפוש

כדי ליצור אינדקס חיפוש, משתמשים בהצהרת ה-DDL‏ CREATE SEARCH INDEX. כדי לציין סוגי נתונים פרימיטיביים להוספה לאינדקס, קראו את המאמר יצירת אינדקס חיפוש וציון העמודות וסוגי הנתונים. אם לא מציינים סוגי נתונים, כברירת מחדל BigQuery מוסיף לאינדקס עמודות מהסוגים הבאים שמכילות נתוני STRING:

  • STRING
  • ARRAY<STRING>
  • STRUCT שמכיל לפחות שדה אחד בתצוגת עץ מסוג STRING או ARRAY<STRING>
  • JSON

כשיוצרים אינדקס חיפוש, אפשר לציין את סוג מנתח הטקסט שבו רוצים להשתמש. מנתח הטקסט קובע איך הנתונים מחולקים לאסימונים לצורך הוספה לאינדקס וחיפוש. ערך ברירת המחדל הוא LOG_ANALYZER. הניתוח הזה מתאים ליומנים שנוצרו על ידי מכונה, ויש לו כללים מיוחדים לגבי אסימונים שנמצאים בדרך כלל בנתוני ניראות, כמו כתובות IP או אימיילים. השתמשו ב-NO_OP_ANALYZER כשיש לכם נתונים שעברו עיבוד מראש ואתם רוצים להתאים אותם באופן מדויק. ‫PATTERN_ANALYZER מחלץ אסימונים מטקסט באמצעות ביטוי רגולרי.

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

בדוגמה הבאה, אינדקס חיפוש נוצר בעמודות a ו-c של simple_table, ומשתמש במנתח הטקסט LOG_ANALYZER כברירת מחדל:

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, c);

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

כשיוצרים אינדקס חיפוש ב-ALL COLUMNS, כל הנתונים מסוג STRING או JSON בטבלה נוספים לאינדקס. אם הטבלה לא מכילה נתונים כאלה, למשל אם כל העמודות מכילות מספרים שלמים, יצירת האינדקס תיכשל. כשמציינים עמודה מסוג STRUCT להוספה לאינדקס, כל שדות המשנה המוטמעים מתווספים לאינדקס.

בדוגמה הבאה, נוצר אינדקס חיפוש ב-a, ב-c.e וב-c.f.g, והוא משתמש במנתח הטקסט NO_OP_ANALYZER:

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS (analyzer = 'NO_OP_ANALYZER');

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

יוצרים אינדקס חיפוש ומציינים את העמודות וסוגי הנתונים

כשיוצרים אינדקס חיפוש, אפשר לציין את סוגי הנתונים שבהם רוצים להשתמש. סוגי נתונים שולטים בסוגי העמודות ושדות המשנה של עמודות JSON ו-STRUCT לצורך הוספה לאינדקס. סוג הנתונים שמוגדר כברירת מחדל לאינדקס הוא STRING. כדי ליצור אינדקס חיפוש עם יותר סוגי נתונים (לדוגמה, סוגים מספריים), משתמשים בהצהרה CREATE SEARCH INDEX עם האפשרות data_types.

בדוגמה הבאה, נוצר אינדקס חיפוש בעמודות a,‏ b,‏ c ו-d בטבלה בשם simple_table. סוגי הנתונים הנתמכים בעמודות הם STRING,‏ INT64 ו-TIMESTAMP.

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON, d TIMESTAMP);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, b, c, d)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

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

כשיוצרים אינדקס חיפוש ב-ALL COLUMNS עם האפשרות data_types, כל עמודה שתואמת לאחד מסוגי הנתונים שצוינו מתווספת לאינדקס. בעמודות JSON ו-STRUCT, כל שדה משנה בתצוגת עץ שתואם לאחד מסוגי הנתונים שצוינו נוסף לאינדקס.

בדוגמה הבאה, אינדקס החיפוש נוצר ב-ALL COLUMNS עם סוגי הנתונים שצוינו. העמודות a,‏ b,‏ c,‏ d.e,‏ d.f,‏ d.g.h,‏ d.g.i בטבלה בשם my_table נוספו לאינדקס:

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c TIMESTAMP,
  d STRUCT <e INT64,
            f ARRAY<STRING>,
            g STRUCT<h STRING, i INT64>>)
AS (
  SELECT
    'hello' AS a,
    10 AS b,
    TIMESTAMP('2008-12-25 15:30:00 UTC') AS c,
    (20, ['x', 'y'], ('z', 30)) AS d;
)

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

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

אינדקס עם רמת פירוט של עמודה

כשיוצרים אינדקס חיפוש, אפשר לציין את רמת הפירוט של העמודה שנוסף לה אינדקס. רמת פירוט של עמודה מאפשרת ל-BigQuery לבצע אופטימיזציה של סוגים מסוימים של שאילתות חיפוש, על ידי אחסון מידע נוסף על עמודות באינדקס החיפוש. כדי להגדיר את רמת הפירוט של עמודה שנוסף לה אינדקס, משתמשים באפשרות index_granularity ב-index_column_option_list כשמריצים הצהרת CREATE SEARCH INDEX.

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

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

CREATE TABLE my_dataset.job_postings (job_id INT64, company_name STRING, job_description STRING);

המילה skills כנראה מופיעה לעיתים קרובות בעמודה job_description, אבל לעיתים רחוקות בעמודה company_name. נניח שאתם מריצים את השאילתה הבאה:

SELECT * FROM my_dataset.job_postings WHERE SEARCH(company_name, 'skills');

אם יצרתם אינדקס חיפוש בעמודות company_name ו-job_description בלי לציין רמת פירוט של עמודה, BigQuery יסרוק כל קובץ שבו המילה skills מופיעה בעמודה job_description או בעמודה company_name. כדי לשפר את הביצועים של השאילתה הזו, אפשר להגדיר את רמת הפירוט של העמודה company_name לערך COLUMN:

CREATE SEARCH INDEX my_index
ON my_dataset.job_postings (
  company_name OPTIONS(index_granularity = 'COLUMN'),
  job_description);

עכשיו, כשמריצים את השאילתה, BigQuery סורק רק את הקבצים שבהם המילה skills מופיעה בעמודה company_name.

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

יש מגבלות על מספר העמודות שאפשר להוסיף לאינדקס ברמת פירוט של עמודה. מידע נוסף זמין במאמר מכסות ומגבלות.

הסבר על רענון האינדקס

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

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

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

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

אינדקס החיפוש מיועד לטבלאות גדולות. אם יוצרים אינדקס חיפוש בטבלה שקטנה מ-10GB, האינדקס לא מאוכלס. באופן דומה, אם מוחקים נתונים מטבלה שנוסף לה אינדקס וגודל הטבלה קטן מ-10GB, האינדקס מושבת באופן זמני. במקרה כזה, שאילתות החיפוש לא משתמשות באינדקס וקוד ה-IndexUnusedReason הוא BASE_TABLE_TOO_SMALL. זה קורה גם אם אתם משתמשים בהזמנה משלכם למשימות ניהול האינדקסים וגם אם לא. כשהגודל של טבלה שנוסף לה אינדקס חורג מ-10GB, האינדקס שלה מאוכלס באופן אוטומטי. לא תחויבו על האחסון עד שאינדקס החיפוש יאוכלס ויהיה פעיל. שאילתות שמשתמשות בפונקציה SEARCH תמיד מחזירות תוצאות נכונות, גם אם חלק מהנתונים עדיין לא נוספו לאינדקס.

עדכון של אינדקס חיפוש

כדי לעדכן את ההגדרות של אינדקס חיפוש, משתמשים בהצהרת ה-DDL‏ ALTER SEARCH INDEX. עדכון אינדקס שימושי במיוחד לטבלאות גדולות, כי הוא מאפשר לבצע שינויים מצטברים, במקום להסיר את האינדקס וליצור אותו מחדש עם הגדרה חדשה.

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

הטבלה הבאה יכולה לעזור לכם להבין את העלות של שינויים שונים באינדקס:

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

אתם יכולים להשתמש בשדה last_index_alteration_info בתצוגה INFORMATION_SCHEMA.SEARCH_INDEXES כדי לעקוב אחרי הכיסוי של האינדקס המתוקן. לדוגמה, השאילתה הבאה מציגה את סטטוס השינוי של כל האינדקסים בטבלאות במערך הנתונים my_dataset בפרויקט my_project. אם שינוי האינדקס הסתיים, הערך של last_index_alteration_info הוא NULL.

SELECT
  table_name,
  index_name,
  last_index_alteration_info.status AS status,
  last_index_alteration_info.new_coverage_percentage AS coverage
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES

התוצאה אמורה להיראות כך:

+------------+------------+-------------+----------+
| table_name | index_name | status      | coverage |
+------------+------------+-------------+----------+
| table1     | index_a    | IN_PROGRESS | 50       |
| table2     | index_b    | null        | null     |
+------------+------------+-------------+----------+

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

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

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

INFORMATION_SCHEMA.SEARCH_INDEXES צפייה בדוגמאות

בקטע הזה מופיעות שאילתות לדוגמה בתצוגה INFORMATION_SCHEMA.SEARCH_INDEXES.

בדוגמה הבאה מוצגים כל אינדקסי החיפוש הפעילים בטבלאות במערך הנתונים my_dataset, שנמצא בפרויקט my_project. הוא כולל את השמות שלהם, את הצהרות ה-DDL ששימשו ליצירה שלהם, את אחוז הכיסוי שלהם ואת מנתח הטקסט שלהם. אם מסד נתונים טבלאי שנוסף לאינדקס קטן מ-10GB, האינדקס שלו לא מאוכלס, ובמקרה כזה הערך של coverage_percentage הוא 0.

SELECT table_name, index_name, ddl, coverage_percentage, analyzer
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE index_status = 'ACTIVE';

התוצאות אמורות להיראות כך:

+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| table_name  | index_name  | ddl                                                                                  | coverage_percentage | analyzer       |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names)      | 0                   | NO_OP_ANALYZER |
| large_table | logs_index  | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100                 | LOG_ANALYZER   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS הצגת דוגמאות

בקטע הזה מופיעות שאילתות לדוגמה בתצוגה INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS.

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

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS);

השאילתה הבאה מחלצת מידע על השדות שנוסף להם אינדקס. השדה index_field_path מציין איזה שדה בעמודה נוסף לאינדקס. השדה הזה שונה מ-index_column_name רק במקרה של STRUCT, שבו מצוין הנתיב המלא לשדה שנוסף לאינדקס. בדוגמה הזו, העמודה c מכילה שדה ARRAY<STRING> בשם e ושדה STRUCT נוסף בשם f, שמכיל שדה STRING בשם g. כל אחד מהשדות האלה נוסף לאינדקס.

SELECT table_name, index_name, index_column_name, index_field_path
FROM my_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS

התוצאה אמורה להיראות כך:

+------------+------------+-------------------+------------------+
| table_name | index_name | index_column_name | index_field_path |
+------------+------------+-------------------+------------------+
| my_table   | my_index   | a                 | a                |
| my_table   | my_index   | c                 | c.e              |
| my_table   | my_index   | c                 | c.f.g            |
+------------+------------+-------------------+------------------+

השאילתה הבאה מחברת את התצוגה INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS עם התצוגות INFORMATION_SCHEMA.SEARCH_INDEXES ו-INFORMATION_SCHEMA.COLUMNS, כדי לכלול את סטטוס אינדקס החיפוש ואת סוג הנתונים של כל עמודה:

SELECT
  index_columns_view.index_catalog AS project_name,
  index_columns_view.index_SCHEMA AS dataset_name,
  indexes_view.TABLE_NAME AS table_name,
  indexes_view.INDEX_NAME AS index_name,
  indexes_view.INDEX_STATUS AS status,
  index_columns_view.INDEX_COLUMN_NAME AS column_name,
  index_columns_view.INDEX_FIELD_PATH AS field_path,
  columns_view.DATA_TYPE AS data_type
FROM
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES indexes_view
INNER JOIN
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS index_columns_view
  ON
    indexes_view.TABLE_NAME = index_columns_view.TABLE_NAME
    AND indexes_view.INDEX_NAME = index_columns_view.INDEX_NAME
LEFT OUTER JOIN
  mydataset.INFORMATION_SCHEMA.COLUMNS columns_view
  ON
    indexes_view.INDEX_CATALOG = columns_view.TABLE_CATALOG
    AND indexes_view.INDEX_SCHEMA = columns_view.TABLE_SCHEMA
    AND index_columns_view.TABLE_NAME = columns_view.TABLE_NAME
    AND index_columns_view.INDEX_COLUMN_NAME = columns_view.COLUMN_NAME
ORDER BY
  project_name,
  dataset_name,
  table_name,
  column_name;

התוצאה אמורה להיראות כך: 

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| project    | dataset    | table    | index_name | status | column_name | field_path | data_type                                                     |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| my_project | my_dataset | my_table | my_index   | ACTIVE | a           | a          | STRING                                                        |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.e        | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.f.g      | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION צפייה בדוגמאות

בקטע הזה מופיעות שאילתות לדוגמה בתצוגה INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION.

איך בודקים אם הצריכה חורגת מהמגבלה באזור מסוים

הדוגמה הבאה ממחישה מצב שבו הגודל הכולל של מסד הנתונים הטבלאי שנוסף לאינדקס בארגון, באמצעות משבצות משותפות בתוך מספר אזורים בארה"ב, חורג מ-100TB:

WITH
 indexed_base_table_size AS (
 SELECT
   SUM(base_table.total_logical_bytes) AS total_logical_bytes
 FROM
   `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION AS search_index
 JOIN
   `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION AS base_table
 ON
   (search_index.table_name = base_table.table_name
     AND search_index.project_id = base_table.project_id
     AND search_index.index_schema = base_table.table_schema)
 WHERE
   TRUE
   -- Excludes search indexes that are permanently disabled.
   AND search_index.index_status != 'PERMANENTLY DISABLED'
   -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is
   -- less than 10 GB. These tables don't count toward the limit.
   AND search_index.index_status_details.throttle_status != 'BASE_TABLE_TOO_SMALL'
   -- Excludes search indexes whose project has BACKGROUND reservation purchased
   -- for search indexes.
   AND search_index.use_background_reservation = false
 -- Outputs the total indexed base table size if it exceeds 100 TB,
 -- otherwise, doesn't return any output.
)
SELECT * FROM indexed_base_table_size
WHERE total_logical_bytes >= 109951162777600 -- 100 TB

התוצאה אמורה להיראות כך:

+---------------------+
| total_logical_bytes |
+---------------------+
|     109951162777601 |
+---------------------+

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

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

SELECT
 search_index.project_id,
 search_index.use_background_reservation,
 SUM(base_table.total_logical_bytes) AS total_logical_bytes
FROM
 `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION AS search_index
JOIN
 `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION AS base_table
ON
 (search_index.table_name = base_table.table_name
   AND search_index.project_id = base_table.project_id
   AND search_index.index_schema = base_table.table_schema)
WHERE
 TRUE
  -- Excludes search indexes that are permanently disabled.
  AND search_index.index_status != 'PERMANENTLY DISABLED'
  -- Excludes BASE_TABLE_TOO_SMALL search indexes whose base table size is
  -- less than 10 GB. These tables don't count toward limit.
 AND search_index.index_status_details.throttle_status != 'BASE_TABLE_TOO_SMALL'
GROUP BY search_index.project_id, search_index.use_background_reservation

התוצאה אמורה להיראות כך:

+---------------------+----------------------------+---------------------+
|     project_id      | use_background_reservation | total_logical_bytes |
+---------------------+----------------------------+---------------------+
| projecta            |     true                   |     971329178274633 |
+---------------------+----------------------------+---------------------+
| projectb            |     false                  |     834638211024843 |
+---------------------+----------------------------+---------------------+
| projectc            |     false                  |     562910385625126 |
+---------------------+----------------------------+---------------------+

איך מוצאים אינדקסים של חיפוש שהוגבלו

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

SELECT project_id, index_schema, table_name, index_name
FROM
 `region-us`.INFORMATION_SCHEMA.SEARCH_INDEXES_BY_ORGANIZATION
WHERE
 -- Excludes search indexes that are permanently disabled.
 index_status != 'PERMANENTLY DISABLED'
 AND index_status_details.throttle_status IN ('ORGANIZATION_LIMIT_EXCEEDED', 'BASE_TABLE_TOO_LARGE')

התוצאה אמורה להיראות כך:

+--------------------+--------------------+---------------+----------------+
|     project_id     |    index_schema    |  table_name   |   index_name   |
+--------------------+--------------------+---------------+----------------+
|     projecta       |     dataset_us     |   table1      |    index1      |
|     projectb       |     dataset_us     |   table1      |    index1      |
+--------------------+--------------------+---------------+----------------+

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

כדי ליצור אינדקסים ולתחזק אותם ב-BigQuery, יש שתי אפשרויות:

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

שימוש במשבצות משותפות

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

אם תוסיפו נתונים לטבלה והגודל הכולל של הטבלאות שנוספו לאינדקס יחרוג מהמגבלה של הארגון, BigQuery ישהה את ניהול האינדקס של הטבלה הזו. במקרה כזה, בשדה index_status בתצוגה INFORMATION_SCHEMA.SEARCH_INDEXES מופיע הערך PENDING DISABLEMENT והאינדקס מועבר לתור למחיקה. בזמן שהאינדקס ממתין להשבתה, הוא עדיין משמש בשאילתות ואתם מחויבים על אחסון האינדקס. אחרי שהאינדקס נמחק, בשדה index_status האינדקס מוצג כ-TEMPORARILY DISABLED. במצב הזה, שאילתות לא משתמשות באינדקס, ואתם לא מחויבים על אחסון האינדקס. במקרה הזה, קוד IndexUnusedReason הוא BASE_TABLE_TOO_LARGE.

אם תמחקו נתונים מהטבלה והגודל הכולל של הטבלאות שנוספו לאינדקס יהיה קטן מהמגבלה לארגון, ניהול האינדקסים יתחדש. השדה index_status בתצוגה INFORMATION_SCHEMA.SEARCH_INDEXES הוא ACTIVE, השאילתות יכולות להשתמש באינדקס ואתם מחויבים על אחסון האינדקס.

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

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

שימוש בהזמנה משלכם

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

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

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

SQL

משתמשים בהצהרת ה-DDL‏ CREATE ASSIGNMENT.

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

    כניסה ל-BigQuery

  2. בעורך השאילתות, מזינים את ההצהרה הבאה:

    CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

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

    • ‫ADMIN_PROJECT_ID: מזהה הפרויקט של פרויקט הניהול שבבעלותו משאב ההזמנה
    • ‫LOCATION: המיקום של ההזמנה
    • ‫RESERVATION_NAME: שם ההזמנה

    • ‫ASSIGNMENT_ID: מזהה המטלה

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

    • ‫PROJECT_ID: מזהה הפרויקט שמכיל את הטבלאות שרוצים להוסיף לאינדקס. הפרויקט הזה מוקצה להזמנה.

  3. ‫לוחצים על Run.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

BQ

משתמשים בפקודה bq mk:

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation_assignment \
    --reservation_id=RESERVATION_NAME \
    --assignee_id=PROJECT_ID \
    --job_type=BACKGROUND \
    --assignee_type=PROJECT

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

  • ‫ADMIN_PROJECT_ID: מזהה הפרויקט של הפרויקט לניהול שבבעלותו משאב ההזמנה
  • ‫LOCATION: המיקום של ההזמנה
  • ‫RESERVATION_NAME: השם של ההזמנה
  • ‫PROJECT_ID: מזהה הפרויקט שרוצים להקצות לשמירת המקום הזו

הצגת משימות ההוספה לאינדקס

משימת הוספה חדשה לאינדקס נוצרת בכל פעם שנוצר או מתעדכן אינדקס בטבלה אחת. כדי לראות מידע על המשימה, שולחים שאילתה לתצוגות של INFORMATION_SCHEMA.JOBS*. כדי לסנן לפי משימות הוספה לאינדקס, מגדירים את job_type IS NULL AND SEARCH(job_id, '`search_index`') בסעיף WHERE של השאילתה. בדוגמה הבאה מוצגת רשימה של חמש משימות ההוספה לאינדקס האחרונות בפרויקט my_project:

SELECT *
FROM
 region-us.INFORMATION_SCHEMA.JOBS
WHERE
  project_id  = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
ORDER BY
 creation_time DESC
LIMIT 5;

בחירת גודל ההזמנה

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

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

מספר המשבצות שצריך למשימת ניהול אינדקס בטבלה תלוי בגורמים הבאים:

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

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

  • נתונים קיימים: עם הזמנה של 1,000 משבצות, אפשר להוסיף לאינדקס טבלה קיימת ב-BigQuery בקצב ממוצע של עד 4GiB לשנייה, שהם בערך 336TiB ליום.
  • נתונים שנוספו לאחרונה: בדרך כלל, הוספה לאינדקס של נתונים שנוספו לאחרונה דורשת יותר משאבים, כי הטבלה והאינדקס שלה עוברים כמה סבבים של אופטימיזציות טרנספורמטיביות. בממוצע, הוספה לאינדקס של נתונים חדשים שנקלטו צורכת פי שלושה משאבים בהשוואה להוספה לאינדקס של מילוי חוסרים ראשוני של אותם נתונים.
  • נתונים שמשתנים לעיתים רחוקות: טבלאות שנוספו לאינדקס עם מעט שינויים בנתונים או ללא שינויים בכלל, דורשות הרבה פחות משאבים לתחזוקה שוטפת של האינדקס. מומלץ להתחיל עם חמישית מה-slots שנדרשו למילוי החוסרים הראשוני של הנתונים, ולא פחות מ-250 slots.
  • התקדמות תהליך ההוספה לאינדקס היא פחות או יותר לינארית, בהתאם לגודל ההזמנה. עם זאת, לא מומלץ להשתמש בהזמנות של פחות מ-250 משבצות לאינדוקס, כי זה עלול להוביל לחוסר יעילות שיאט את התקדמות האינדוקס.
  • ההערכות האלה עשויות להשתנות בהתאם לתכונות, לאופטימיזציות ולשימוש בפועל.
  • אם הגודל הכולל של הטבלאות בארגון חורג ממגבלת האינדקס באזור, צריך להקצות לאינדקס הזמנה שאינה אפס. אחרת, יכול להיות שהוספת האינדקסים תתבצע ברמת ברירת המחדל, וכתוצאה מכך כל האינדקסים יימחקו בטעות.
מעקב אחרי השימוש וההתקדמות

הדרך הכי טובה להעריך את מספר ה-slots שאתם צריכים כדי להריץ ביעילות את משימות ניהול האינדקסים היא לעקוב אחרי ניצול ה-slots ולשנות את גודל ההזמנה בהתאם. השאילתה הבאה מפיקה את השימוש היומי במשבצות למשימות של ניהול אינדקסים. רק 30 הימים האחרונים נכללים באזור us-west1:

SELECT
  TIMESTAMP_TRUNC(job.creation_time, DAY) AS usage_date,
  -- Aggregate total_slots_ms used for index-management jobs in a day and divide
  -- by the number of milliseconds in a day. This value is most accurate for
  -- days with consistent slot usage.
  SAFE_DIVIDE(SUM(job.total_slot_ms), (1000 * 60 * 60 * 24)) AS average_daily_slot_usage
FROM
  `region-us-west1`.INFORMATION_SCHEMA.JOBS job
WHERE
  project_id = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
GROUP BY
  usage_date
ORDER BY
  usage_date DESC
limit 30;

אם אין מספיק משבצות להרצת משימות של ניהול אינדקסים, יכול להיות שהאינדקס לא יהיה מסונכרן עם הטבלה שלו, ויכול להיות שמשימות ההוספה לאינדקס ייכשלו. במקרה כזה, BigQuery בונה מחדש את האינדקס מאפס. כדי למנוע מצב שבו האינדקס לא מסונכרן, ודאו שיש לכם מספיק משבצות לתמיכה בעדכוני אינדקס מהטמעת נתונים ומאופטימיזציה. למידע נוסף על מעקב אחרי השימוש ב-slots, קראו את המאמר תרשימי משאבים לאדמינים.

שיטות מומלצות

  • אינדקס החיפוש מיועד לטבלאות גדולות. השיפור בביצועים שנובע מאינדקס חיפוש גדל ככל שהטבלה גדולה יותר.
  • אל תוסיפו לאינדקס עמודות שמכילות רק מספר קטן מאוד של ערכים ייחודיים.
  • אל תוסיפו לאינדקס עמודות שאתם לא מתכוונים להשתמש בהן עם הפונקציה SEARCH או עם כל אחת מהפונקציות והאופרטורים הנתמכים האחרים.
  • חשוב להיזהר כשיוצרים אינדקס חיפוש ב-ALL COLUMNS. בכל פעם שמוסיפים עמודה שמכילה נתונים מסוג STRING או JSON, המערכת מוסיפה אותם לאינדקס.
  • באפליקציות בסביבת הייצור, כדאי להשתמש בהזמנה משלכם לניהול אינדקסים. אם תבחרו להשתמש במאגר ברירת המחדל של משבצות משותפות למשימות של ניהול האינדקסים, יחולו מגבלות הגודל לכל ארגון.

מחיקת אינדקס חיפוש

אם אתם לא צריכים יותר אינדקס חיפוש או שאתם רוצים לשנות את העמודות שנוסף להן אינדקס בטבלה, אתם יכולים למחוק את האינדקס שקיים כרגע בטבלה. משתמשים בהצהרת ה-DDL‏ DROP SEARCH INDEX.

אם טבלה שנוסף לה אינדקס נמחקת, האינדקס שלה נמחק באופן אוטומטי.

דוגמה:

DROP SEARCH INDEX my_index ON dataset.simple_table;

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