Suchindexe verwalten

Ein Suchindex ist eine Datenstruktur, die eine sehr effiziente Suche mit der Funktion SEARCH ermöglicht. Ähnlich wie der Index, den Sie in einem Buch finden würden, agiert ein Suchindex für eine Spalte mit Stringdaten wie eine Hilfstabelle, die zwei Spalten enthält: eine für bestimmte Wörter und eine weitere für die Stelle, an der die Wörter in den Daten vorkommen.

Suchindex erstellen

Verwenden Sie zum Erstellen eines Suchindex die DDL-Anweisung CREATE SEARCH INDEX. Sie können einen Suchindex für diese Spaltentypen erstellen:

  • STRING
  • ARRAY<STRING>
  • STRUCT, das mindestens ein verschachteltes Feld vom Typ STRING oder ARRAY<STRING> enthält
  • JSON

Wenn Sie einen Suchindex erstellen, können Sie den zu verwendenden Textanalysator angeben. Der Textanalysator steuert, wie Daten für die Indexierung und Suche tokenisiert werden. Der Standardwert ist LOG_ANALYZER. Dieser Analysetool funktioniert gut mit maschinengenerierten Logs. Er hat spezielle Regeln für Tokens, die häufig in Beobachtbarkeitsdaten wie IP-Adressen oder E-Mails gefunden werden. Verwenden Sie NO_OP_ANALYZER, wenn vorverarbeitete Daten, die Sie genau abgleichen möchten, bereits vorhanden sind. PATTERN_ANALYZER extrahiert Tokens aus einem Text mithilfe eines regulären Ausdrucks.

Im folgenden Beispiel wird ein Suchindex für die Spalten a und c von simple_table erstellt.

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

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

Wenn Sie einen Suchindex für ALL COLUMNS erstellen, werden alle STRING- oder JSON-Daten in der Tabelle indexiert. Wenn die Tabelle keine solchen Daten enthält, z. B. wenn alle Spalten Ganzzahlen enthalten, schlägt die Indexerstellung fehl. Wenn Sie eine zu indexierende STRUCT-Spalte angeben, werden alle verschachtelten Unterfelder indexiert.

Im folgenden Beispiel wird ein Suchindex für a, c.e und c.f.g erstellt und der Textanalyse NO_OP_ANALYZER verwendet.

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');

Da der Suchindex für ALL COLUMNS erstellt wurde, werden alle der Tabelle hinzugefügten Spalten automatisch indexiert, wenn sie STRING-Daten enthalten.

Informationen zur Indexaktualisierung

Suchindexe werden von BigQuery vollständig verwaltet und automatisch aktualisiert, wenn sich die Tabelle ändert. Das folgende Schema, das die Tabelle ändert, kann eine vollständige Aktualisierung auslösen:

  • Eine neue indexierbare Spalte wird einer Tabelle mit einem Suchindex für ALL COLUMNS hinzugefügt.
  • Eine indexierte Spalte wird aufgrund einer Änderung des Tabellenschemas aktualisiert.

Wenn Sie die einzige indexierte Spalte in einer Tabelle löschen oder die Tabelle selbst umbenennen, wird der Suchindex automatisch gelöscht.

Suchindexe sind für große Tabellen konzipiert. Wenn Sie einen Suchindex für eine Tabelle erstellen, die kleiner als 10 GB ist, wird der Index nicht ausgefüllt. Dies gilt auch umgekehrt: Wenn Sie Daten aus einer indexierten Tabelle löschen und die Tabellengröße unter 10 GB liegt, wird der Index vorübergehend deaktiviert. In diesem Fall verwenden Suchanfragen nicht den Index und der IndexUnusedReason-Code ist BASE_TABLE_TOO_SMALL. Dies geschieht unabhängig davon, ob Sie Ihre eigene Reservierung für Ihre Indexverwaltungsjobs verwenden. Wenn die Größe einer indexierten Tabelle 10 GB überschreitet, wird der Index automatisch ausgefüllt. Der Speicher wird Ihnen erst in Rechnung gestellt, wenn der Suchindex ausgefüllt und aktiv ist. Abfragen, die die SEARCH-Funktion verwenden, geben immer korrekte Ergebnisse zurück, auch wenn einige Daten noch nicht indexiert sind.

Informationen zu Suchindexen abrufen

Sie können das Vorhandensein und die Bereitschaft eines Suchindex prüfen, indem Sie INFORMATION_SCHEMA abfragen. Es gibt zwei Ansichten, die Metadaten zu Suchindexen enthalten. Die Ansicht INFORMATION_SCHEMA.SEARCH_INDEXES enthält Informationen zu jedem Suchindex, der für ein Dataset erstellt wurde. Die Ansicht INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS enthält Informationen dazu, welche Spalten jeder Tabelle im Dataset indexiert sind.

Das folgende Beispiel zeigt alle aktiven Suchindexe für Tabellen im Dataset my_dataset, das sich im Projekt my_project befindet. Sie enthält die Namen, die zum Erstellen verwendeten DDL-Anweisungen, den Deckungsprozentsatz und den Textanalysator. Ist eine indexierte Basistabelle kleiner als 10 GB, wird der Index nicht ausgefüllt. In diesem Fall ist 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';

Die Ergebnisse sollten so aussehen:

+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| 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   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

Im folgenden Beispiel wird ein Suchindex für alle Spalten von my_table erstellt.

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);

Die folgende Abfrage extrahiert Informationen darüber, welche Felder indexiert wurden. index_field_path gibt an, welches Feld einer Spalte indexiert ist. Dies unterscheidet sich von index_column_name nur im Fall eines STRUCT, bei dem der vollständige Pfad zum indexierten Feld angegeben wird. In diesem Beispiel enthält die Spalte c das ARRAY<STRING>-Feld e und ein weiteres STRUCT namens f, das das STRING-Feld g enthält, und beide sind indexiert.

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

Das Ergebnis sieht etwa so aus:

+------------+------------+-------------------+------------------+
| 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            |
+------------+------------+-------------------+------------------+

Die folgende Abfrage verknüpft die Ansicht INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS mit den Ansichten INFORMATION_SCHEMA.SEARCH_INDEXES und INFORMATION_SCHEMA.COLUMNS, um den Suchindexstatus und den Datentyp jeder Spalte einzubeziehen:

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;

Das Ergebnis sieht etwa so aus: 

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| 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>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

Optionen für die Indexverwaltung

Sie haben zwei Möglichkeiten, Indexe zu erstellen und von BigQuery verwalten zu lassen:

  • Standardmäßigen freigegebenen Slot-Pool verwenden: Wenn die zu indexierenden Daten unter dem Limit pro Organisation liegen, können Sie den kostenlosen freigegebenen Slot-Pool für die Indexverwaltung verwenden.
  • Eigene Reservierung verwenden: Um einen vorhersehbaren und konsistenten Fortschritt bei der Indexierung für Ihre größeren Produktionsarbeitslasten zu erreichen, können Sie Ihre eigenen Reservierungen für die Indexverwaltung verwenden.

Freigegebene Slots verwenden

Wenn Sie Ihr Projekt nicht für die Verwendung einer dedizierten Reservierung für die Indexierung konfiguriert haben, wird die Indexverwaltung im kostenlosen, freigegebenen Slot-Pool durchgeführt, wobei die folgenden Einschränkungen gelten.

Wenn Sie einer Tabelle Daten hinzufügen, wodurch die Gesamtgröße der indexierten Tabelle das Limit Ihrer Organisation überschreitet, pausiert BigQuery die Indexverwaltung für alle indexierten Tabellen. In diesem Fall wird im Feld index_status in der Ansicht INFORMATION_SCHEMA.SEARCH_INDEXES PENDING DISABLEMENT angezeigt und der Index wird zum Löschen in die Warteschlange gestellt. Solange die Deaktivierung des Index aussteht, wird er noch in Anfragen verwendet und Indexspeicher wird Ihnen weiterhin in Rechnung gestellt. Nachdem ein Index gelöscht wurde, wird im Feld index_status der Index als TEMPORARILY DISABLED angezeigt. In diesem Status wird der Index nicht von Abfragen verwendet und der Indexspeicher wird Ihnen nicht in Rechnung gestellt. In diesem Fall lautet der IndexUnusedReason-Code BASE_TABLE_TOO_LARGE.

Wenn Sie Daten aus der Tabelle löschen und die Gesamtgröße der indexierten Tabelle unter das Limit pro Organisation fällt, wird die Indexverwaltung für alle indexierten Tabellen fortgesetzt. Das Feld index_status in der Ansicht INFORMATION_SCHEMA.SEARCH_INDEXES ist ACTIVE. Anfragen können den Index verwenden und Ihnen wird der Indexspeicher in Rechnung gestellt.

BigQuery gibt keine Garantien für die verfügbaren Kapazitäten des gemeinsamen Pools oder den angezeigten Durchsatz der Indexierung. Für Produktionsanwendungen kann die Verwendung dedizierter Slots für die Indexverarbeitung sinnvoll sein.

Eigene Reservierung verwenden

Anstatt den standardmäßigen freigegebenen Slot-Pool zu verwenden, können Sie optional eine eigene Reservierung festlegen, um Ihre Tabellen zu indexieren. Die Verwendung Ihrer eigenen Reservierung sorgt für eine vorhersagbare und konsistente Leistung von Indexverwaltungsjobs, z. B. Erstellungs-, Aktualisierungs- und Hintergrundoptimierungen.

  • Wenn ein Indexjob in Ihrer Reservierung ausgeführt wird, gibt es keine Größenbeschränkungen für Tabellen.
  • Mit der eigenen Reservierung können Sie Ihre Indexverwaltung flexibel verwalten. Wenn Sie einen sehr großen Index erstellen oder eine wichtige Aktualisierung einer indexierten Tabelle vornehmen müssen, können Sie der Zuweisung vorübergehend weitere Slots hinzufügen.

Um die Tabelle in einem Projekt mit einer bestimmten Reservierung zu indexieren, erstellen Sie eine Reservierung in der Region, in der sich Ihre Tabellen befinden. Weisen Sie der Reservierung dann das Projekt zu, wobei job_type auf BACKGROUND gesetzt ist:

SQL

Verwenden Sie die DDL-Anweisung CREATE ASSIGNMENT.

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

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

    Dabei gilt:

    • ADMIN_PROJECT_ID: die Projekt-ID des Administrationsprojekts, dem die Reservierungsressource gehört
    • LOCATION: der Standort der Reservierung
    • RESERVATION_NAME: der Name der Reservierung

    • ASSIGNMENT_ID: die ID der Zuweisung

      Die ID muss für das Projekt und den Standort eindeutig sein, mit einem Kleinbuchstaben oder einer Zahl beginnen und enden und darf nur Kleinbuchstaben, Zahlen und Bindestriche enthalten.

    • PROJECT_ID: die ID des Projekts, das die zu indexierenden Tabellen enthält. Dieses Projekt wird der Reservierung zugewiesen.

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

bq

Führen Sie den Befehl bq mk aus:

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

Dabei gilt:

  • ADMIN_PROJECT_ID: die Projekt-ID des Administrationsprojekts, dem die Reservierungsressource gehört
  • LOCATION: der Standort der Reservierung
  • RESERVATION_NAME: der Name der Reservierung
  • PROJECT_ID: die ID des Projekts, das der Reservierung zugewiesen werden soll

Indexierungsjobs ansehen

Bei jeder Erstellung oder Aktualisierung eines Index für eine einzelne Tabelle wird ein neuer Indexierungsjob erstellt. Fragen Sie die Ansichten INFORMATION_SCHEMA.JOBS* ab, um Informationen zum Job anzuzeigen. Sie können nach Indexierungsjobs filtern, indem Sie job_type IS NULL AND SEARCH(job_id, '`search_index`') in der WHERE-Klausel Ihrer Abfrage festlegen. Im folgenden Beispiel werden die fünf neuesten Indexierungsjobs im Projekt my_project aufgelistet:

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;

Reservierungsgröße auswählen

Wenn Sie die richtige Anzahl von Slots für Ihre Reservierung auswählen möchten, sollten Sie berücksichtigen, wann Jobs mit Indexverwaltung ausgeführt werden, wie viele Slots sie verwenden und wie Ihre Nutzung im Laufe der Zeit aussieht. BigQuery löst in den folgenden Situationen einen Indexverwaltungsjob aus:

  • Sie erstellen einen Index für eine Tabelle.
  • Daten werden in einer indexierten Tabelle geändert.
  • Das Schema einer Tabelle ändert sich, was sich darauf auswirkt, welche Spalten indexiert werden.
  • Indexdaten und Metadaten werden regelmäßig optimiert oder aktualisiert.

Die Anzahl der Slots, die Sie für einen Indexverwaltungsjob in einer Tabelle benötigen, hängt von den folgenden Faktoren ab:

  • Die Größe der Tabelle
  • Die Rate der Datenaufnahme in die Tabelle
  • Die Rate der DML-Anweisungen, die auf die Tabelle angewendet werden
  • Die akzeptable Verzögerung zum Erstellen und Verwalten des Index
  • Die Komplexität des Index; normalerweise bestimmt durch Attribute der Daten, z. B. die Anzahl doppelter Begriffe
Erste Schätzung

Mit den folgenden Schätzungen können Sie ungefähr bestimmen, wie viele Slots für Ihre Reservierung erforderlich sind. Da die Indexierung von Arbeitslasten sehr variabel ist, sollten Sie Ihre Anforderungen nach der Indexierung von Daten noch einmal neu bewerten.

  • Vorhandene Daten: Mit einer Reservierung mit 1.000 Slots kann eine vorhandene Tabelle in BigQuery mit einer durchschnittlichen Rate von bis zu 4 GiB pro Sekunde indexiert werden. Dies entspricht ungefähr 336 TiB pro Tag.
  • Neu aufgenommene Daten: Die Indexierung ist in der Regel ressourcenintensiver für neu aufgenommene Daten, da die Tabelle und ihr Index mehrere Transformationsoptimierungen durchlaufen. Im Durchschnitt verbrauchen neu aufgenommene Daten bei der Indexierung im Vergleich zur ersten Backfill-Indexierung derselben Daten dreimal so viele Ressourcen.
  • Selten geänderte Daten: Indexierte Tabellen mit wenig oder gar keiner Datenänderung benötigen wesentlich weniger Ressourcen für die fortlaufende Indexwartung. Ein empfohlener Ausgangspunkt ist die Beizubehalten von 1/5 der Slots, die für die anfängliche Backfill-Indexierung derselben Daten erforderlich sind, und nicht weniger als 250 Slots.
  • Der Fortschritt der Indexierung wird ungefähr linear mit der Reservierungsgröße skaliert. Wir raten jedoch davon ab, Reservierungen mit weniger als 250 Slots für die Indexierung zu verwenden, da dies zu Ineffizienzen führen kann, was den Indexierungsfortschritt verlangsamen kann.
  • Diese Schätzungen können sich ändern, wenn die Funktionen, Optimierungen und Ihre tatsächliche Nutzung variieren.
  • Wenn die gesamte Tabellengröße der Organisation das Indexierungslimit Ihrer Region überschreitet, sollten Sie für die Indexierung eine Reservierung ungleich null beibehalten. Andernfalls kann die Indexierung auf die Standardstufe zurückfallen, was zu einem unbeabsichtigten Löschen aller Indexe führt.
Nutzung und Fortschritt überwachen

Die beste Möglichkeit, die Anzahl der Slots zu ermitteln, die Sie für die effiziente Ausführung Ihrer Indexverwaltungsjobs benötigen, besteht darin, die Slotauslastung zu überwachen und die Reservierungsgröße entsprechend anzupassen. Die folgende Abfrage erzeugt die tägliche Slot-Nutzung für Jobs zur Indexverwaltung. Nur die letzten 30 Tage sind in der Region us-west1 enthalten:

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;

Wenn nicht genügend Slots zum Ausführen von Indexverwaltungsjobs vorhanden sind, ist der Index möglicherweise nicht mehr mit seiner Tabelle synchron und Indexierungsjobs können fehlschlagen. In diesem Fall erstellt BigQuery den Index von Grund auf neu. Achten Sie darauf, dass genügend Slots vorhanden sind, um Indexaktualisierungen aus der Datenaufnahme und -optimierung zu unterstützen, damit kein nicht synchronisierter Index. Weitere Informationen zur Überwachung der Slot-Nutzung finden Sie unter Administratorressourcendiagramme.

Best Practices

  • Suchindexe sind für große Tabellen konzipiert. Die Leistung eines Suchindex nimmt mit der Größe der Tabelle zu.
  • Indexieren Sie keine Spalten, die nur eine sehr kleine Anzahl eindeutiger Werte enthalten.
  • Indexieren Sie keine Spalten, für die Sie nie die Funktion SEARCH aufrufen möchten.
  • Seien Sie vorsichtig, wenn Sie einen Suchindex für ALL COLUMNS erstellen. Jedes Mal, wenn Sie eine Spalte mit STRING- oder JSON-Daten hinzufügen, wird sie indexiert.
  • Sie sollten für die Indexverwaltung in Produktionsanwendungen Ihre eigene Reservierung verwenden. Wenn Sie für Ihre Indexverwaltungsjobs den standardmäßigen gemeinsamen Slot-Pool verwenden, gelten die Größen-Limits für die einzelnen Organisationen.

Suchindex löschen

Wenn Sie einen Suchindex nicht mehr benötigen oder die in einer Tabelle indexierten Spalten ändern möchten, können Sie den Index löschen, der aktuell für diese Tabelle gilt. Verwenden Sie dazu die DDL-Anweisung DROP SEARCH INDEX.

Wenn eine indexierte Tabelle gelöscht wird, wird ihr Index automatisch gelöscht.

Beispiel:

DROP SEARCH INDEX my_index ON dataset.simple_table;

Nächste Schritte