Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
(Kopie der Originalveröffentlichung)
Rolluptabellen werden häufig in PostgreSQL verwendet, wenn Sie keine detaillierte Analyse durchführen müssen, aber Sie müssen dennoch grundlegende Aggregationsabfragen für ältere Daten beantworten.
Mit Rolluptabellen können Sie Ihre älteren Daten für die Abfragen vorab aggregieren, die Sie noch beantworten müssen. Dann müssen Sie nicht mehr alle älteren Daten speichern. Stattdessen können Sie die älteren Daten löschen oder auf langsameren Speicher zurücksetzen – was Speicherplatz und Rechenleistung spart.
In diesem Artikel wird ein Rolluptabellenbeispiel in PostgreSQL ohne Verwendung von HyperLogLog (HLL) gezeigt.
Rolluptabellen ohne HLL – Verwenden von GitHub-Ereignisdaten als Beispiel
Jeder Datensatz in diesem GitHub-Dataset stellt ein ereignis dar, das in GitHub erstellt wurde. Darüber hinaus verfügt jeder Datensatz über wichtige Informationen zu dem Ereignis, z. B. Ereignistyp, Erstellungsdatum und dem Benutzer, der das Ereignis erstellt hat. Weitere Informationen finden Sie in den ersten Schritten mit GitHub-Ereignisdaten auf Citus.
Wenn Sie ein Diagramm erstellen möchten, um die Anzahl der GitHub-Ereigniserstellungen in jeder Minute anzuzeigen, verwenden Sie eine Rolluptabelle. Bei einer Rolluptabelle müssen Sie nicht alle Benutzerereignisse speichern, um das Diagramm zu erstellen. Stattdessen können Sie die Anzahl der Ereigniserstellungen für jede Minute aggregieren und nur die aggregierten Daten speichern. Sie können dann die restlichen Ereignisdaten wegwerfen, wenn Sie versuchen, Platz zu sparen.
Informationen zum Veranschaulichen des Prozesses finden Sie im folgenden github_events Tabellenbeispiel:
CREATE TABLE github_events
(
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
user_id bigint,
org jsonb,
created_at timestamp
);
\COPY github_events FROM events.csv CSV
Wenn Sie in diesem Beispiel keine detaillierte Analyse ihrer älteren Daten regelmäßig durchführen, müssen Sie keine Ressourcen für die älteren Daten zuordnen. Stattdessen können Sie Rolluptabellen verwenden und die erforderlichen Informationen im Arbeitsspeicher behalten. Sie können eine Rolluptabelle für diesen Zweck erstellen:
CREATE TABLE github_events_rollup_minute
(
created_at timestamp,
event_count bigint
);
Und füllen Sie sie mit INSERT/SELECT auf:
INSERT INTO github_events_rollup_minute(
created_at,
event_count
)
SELECT
date_trunc('minute', created_at) AS created_at,
COUNT(*) AS event_count
FROM github_events
GROUP BY 1;
Jetzt können Sie die älteren (und größeren) Daten in einer kostengünstigeren Ressource wie einem Datenträger speichern, damit Sie in Zukunft darauf zugreifen können– und die github_events_rollup_minute Tabelle im Arbeitsspeicher behalten, damit Sie Ihr Analysedashboard erstellen können.
Durch das Aggregieren der Daten nach Minute im vorherigen Beispiel können Sie Abfragen wie stündliche und tägliche Gesamtereigniserstellungen beantworten. Es ist jedoch nicht möglich, die differenziertere Ereigniserstellungsanzahl für jede Sekunde zu kennen.
Da Sie die Ereigniserstellung für jeden Benutzer nicht separat beibehalten, können Sie keine separate Analyse für jeden Benutzer mit dieser Rolluptabelle erstellen.
Ohne HLL unterliegen Rolluptabellen einigen Einschränkungen.
Bei Abfragen mit eindeutiger Zählung sind Rollup-Tabellen weniger nützlich. Wenn Sie beispielsweise über Minuten vorab aggregieren, können Sie keine Abfragen für eindeutige Zählungen über eine Stunde ausführen. Sie können nicht jedes Minutenergebnis hinzufügen, um stündliche Ereigniserstellungen von eindeutigen Benutzern zu erhalten, da Sie wahrscheinlich überlappende Datensätze in verschiedenen Minuten haben.
Wenn Sie unterschiedliche Anzahlen berechnen möchten, die durch Kombinationen von Spalten eingeschränkt sind, benötigen Sie mehrere Rolluptabellen.
Manchmal möchten Sie die Anzahl der Ereignisse nach eindeutigen Benutzern abrufen, die nach Datum gefiltert wurden, und manchmal möchten Sie eindeutige Ereigniserstellungsanzahlen nach Ereignistyp filtern (und manchmal eine Kombination aus beidem).) Mit HLL kann eine Rolluptabelle alle diese Abfragen beantworten– aber ohne HLL benötigen Sie für jede dieser verschiedenen Arten von Abfragen eine separate Rolluptabelle.
HLL ist im Einsatz
Wenn Sie Rollups mit dem HLL-Datentyp durchführen (anstatt die endgültige eindeutige Benutzeranzahl zusammenzufassen), können Sie das Problem mit überlappenden Datensätzen leicht überwinden. HLL codiert die Daten so, dass einzelne eindeutige Anzahlen addiert werden können, ohne überlappende Datensätze aufzuzählen.
HLL ist auch hilfreich, wenn Sie unterschiedliche Anzahlen berechnen möchten, die durch Kombinationen von Spalten eingeschränkt sind. Wenn Sie beispielsweise eindeutige Ereigniserstellungsanzahlen pro Datum und/oder Ereignistyp mit HLL abrufen möchten, können Sie nur eine Rolluptabelle für alle Kombinationen verwenden.
Wenn Sie jedoch ohne HLL unterschiedliche Anzahlen berechnen möchten, die durch Kombinationen von Spalten eingeschränkt sind, müssen Sie Folgendes erstellen:
- Sieben verschiedene Rolluptabellen, um alle Kombinationen von drei Spalten abzudecken
- 15 Rollup-Tabellen für alle Kombinationen von vier Spalten
- Zwei-in-Eins-Rolluptabellen, um alle Kombinationen in „n“-Spalten abzudecken
HLL- und Rolluptabellen zusammen in Aktion
HLL kann Ihnen helfen, einige typische Abfragen zu einzigartigen Zählungen von GitHub-Ereignisdaten zu beantworten. Erstellen Sie zunächst die github_events Tabelle, und laden Sie die Daten darin:
CREATE TABLE github_events
(
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
user_id bigint,
org jsonb,
created_at timestamp
);
\COPY github_events FROM events.csv CSV
Erstellen Sie nach der Erstellung Ihrer Tabelle eine Rollup-Tabelle. Sie möchten unterschiedliche Anzahlen pro user und pro event_type Basis abrufen. Daher sollten Sie eine leicht unterschiedliche Rolluptabelle verwenden:
DROP TABLE IF EXISTS github_events_rollup_minute;
CREATE TABLE github_events_rollup_minute(
created_at timestamp,
event_type text,
distinct_user_id_count hll
);
Schließlich können Sie INSERT/SELECT verwenden, um Ihre Rolluptabelle zu füllen, und Sie können die hll_hash_bigint-Funktion verwenden, um jede user_id zu hashen.
INSERT INTO github_events_rollup_minute(
created_at,
event_type,
distinct_user_id_count
)
SELECT
date_trunc('minute', created_at) AS created_at,
event_type,
hll_add_agg(hll_hash_bigint(user_id))
FROM github_events
GROUP BY 1, 2;
INSERT 0 2484
Welche Arten von Abfragen kann HLL beantworten?
Um HLL-Werte zu tatsächlichen eindeutigen Zählungen zu materialisieren, sollten Sie die folgende Frage in Betracht ziehen:
Wie viele unterschiedliche Benutzer haben pro Minute um 2016-12-01 05:35:00 Uhr ein Ereignis für jeden Ereignistyp erstellt?
Sie müssen die Funktion hll_cardinality verwenden, um die HLL-Datenstrukturen zu einer tatsächlichen eindeutigen Zählung zu materialisieren.
SELECT
created_at,
event_type,
hll_cardinality(distinct_user_id_count) AS distinct_count
FROM
github_events_rollup_minute
WHERE
created_at = '2016-12-01 05:35:00'::timestamp
ORDER BY 2;
created_at | event_type | distinct_count
---------------------+-------------------------------+------------------
2016-12-01 05:35:00 | CommitCommentEvent | 1
2016-12-01 05:35:00 | CreateEvent | 59
2016-12-01 05:35:00 | DeleteEvent | 6
2016-12-01 05:35:00 | ForkEvent | 20
2016-12-01 05:35:00 | GollumEvent | 2
2016-12-01 05:35:00 | IssueCommentEvent | 42
2016-12-01 05:35:00 | IssuesEvent | 13
2016-12-01 05:35:00 | MemberEvent | 4
2016-12-01 05:35:00 | PullRequestEvent | 24
2016-12-01 05:35:00 | PullRequestReviewCommentEvent | 4
2016-12-01 05:35:00 | PushEvent | 254.135297564883
2016-12-01 05:35:00 | ReleaseEvent | 4
2016-12-01 05:35:00 | WatchEvent | 57
(13 rows)
HLL hilft bei der Beantwortung der folgenden Abfrage:
Wie viele unterschiedliche Benutzer haben während dieses einstündigen Zeitraums ein Ereignis erstellt?
Mit HLLs können Sie die Antwort sehen.
SELECT
hll_cardinality(SUM(distinct_user_id_count)) AS distinct_count
FROM
github_events_rollup_minute
WHERE
created_at BETWEEN '2016-12-01 05:00:00'::timestamp AND '2016-12-01 06:00:00'::timestamp;
distinct_count
------------------
10978.2523520687
(1 row)
Eine weitere Frage, die Sie für die Verwendung der HLL-Additivitätseigenschaft berücksichtigen sollten:
Wie viele eindeutige Benutzer haben während jeder Stunde um 2016-12-01 ein Ereignis erstellt?
SELECT
EXTRACT(HOUR FROM created_at) AS hour,
hll_cardinality(SUM(distinct_user_id_count)) AS distinct_count
FROM
github_events_rollup_minute
WHERE
created_at BETWEEN '2016-12-01 00:00:00'::timestamp AND '2016-12-01 23:59:59'::timestamp
GROUP BY 1
ORDER BY 1;
hour | distinct_count
-------+------------------
5 | 10598.637184899
6 | 17343.2846931687
7 | 18182.5699816622
8 | 12663.9497604266
(4 rows)
Da Daten begrenzt sind, hat die Abfrage nur vier Zeilen zurückgegeben. Betrachten Sie schließlich die folgende Frage:
Wie viele unterschiedliche Benutzer haben während jeder Stunde ein PushEvent erstellt?
SELECT
EXTRACT(HOUR FROM created_at) AS hour,
hll_cardinality(SUM(distinct_user_id_count)) AS distinct_push_count
FROM
github_events_rollup_minute
WHERE
created_at BETWEEN '2016-12-01 00:00:00'::timestamp AND '2016-12-01 23:59:59'::timestamp
AND event_type = 'PushEvent'::text
GROUP BY 1
ORDER BY 1;
hour | distinct_push_count
------+---------------------
5 | 6206.61586498546
6 | 9517.80542100396
7 | 10370.4087640166
8 | 7067.26073810357
(4 rows)
Eine Rolluptabelle mit HLL ist mehr wert als tausend Rolluptabellen ohne HLL.
Eine Rolluptabelle mit HLL kann Abfragen beantworten, bei denen andernfalls für jede Abfrage eine andere Rolluptabelle erforderlich wäre. Im vorherigen Beispiel sehen Sie, dass mit HLL vier Beispielabfragen mit einer einzelnen Rolluptabelle beantwortet werden können. Ohne HLL benötigen Sie drei separate Rolluptabellen, um alle diese Abfragen zu beantworten.
Ohne HLL benötigen Sie wahrscheinlich noch mehr Rolluptabellen, um Ihre Analyseabfragen zu unterstützen. Für alle Kombinationen von "n"-Einschränkungen benötigen Sie "2n - 1"-Rolluptabellen. Mit HLL können Sie den Einzelvorgang mit einer einzigen Rolluptabelle ausführen.
Eine Rolluptabelle mit HLL ist einfacher zu verwalten als mehrere Rolluptabellen, und dass eine Rolluptabelle weniger Arbeitsspeicher benötigt. In einigen Fällen kann der Mehraufwand für Rolluptabellen ohne HLL zu teuer werden und den Nutzen übersteigen, sodass man sich entscheidet, überhaupt keine Rolluptabellen zu verwenden.
Möchten Sie mehr über HLL in PostgreSQL erfahren?
HLL ist auch bei verteilten Systemen nützlich. Wie bei Rolluptabellen platzieren Sie in einem verteilten System wie Citus häufig unterschiedliche Datenteile in verschiedenen Knoten. Daher haben Sie wahrscheinlich überlappende Datensätze auf verschiedenen Knoten. Die Techniken, die HLL verwendet, um Daten zu codieren, um separate eindeutige Anzahlen zusammenzuführen und das Problem mit überlappenden Datensätzen zu beheben, kann auch bei verteilten Systemen hilfreich sein.
Weitere Informationen finden Sie unter Verteilte eindeutige Zählung mit HyperLogLog in PostgreSQL (Distributed distinct count with HyperLogLog on PostgreSQL). In diesem Artikel werden die Interna von HLL erläutert und beschrieben, wie HLL einzelne eindeutige Anzahlen zusammenführt, ohne überlappende Datensätze zu zählen.