Tableau, SQL und Suche für schnelle Datenvisualisierungen verwenden

Dieser Beitrag baut auf meinem vorherigen Blogbeitrag auf, in dem ich den Fusion SQL-Dienst vorgestellt habe: https://lucidworks.com/2017/02/01/sql-in-fusion-3/. Seit diesem Beitrag…

Dieser Beitrag baut auf meinem vorherigen Blogbeitrag auf, in dem ich den Fusion SQL-Dienst vorgestellt habe: https://lucidworks.com/2017/02/01/sql-in-fusion-3/. Seit diesem Beitrag haben wir fleißig neue Optimierungen hinzugefügt und eine bessere Integration mit BI-Tools wie Tableau sichergestellt, insbesondere für größere Datensätze.

Aus Zeitgründen gehe ich davon aus, dass Sie mit den Konzepten vertraut sind, die ich im vorherigen Blogbeitrag behandelt habe. In diesem Beitrag stelle ich einige der interessanten Funktionen vor, die wir in Fusion 4.1 hinzugefügt haben.

Abfrage größerer Datensätze

Die Solr-Community stößt immer wieder an die Grenzen der Größe und Komplexität von Datensätzen, die von Solr verarbeitet werden können. Auf der diesjährigen Activate-Konferenz wird eine Reihe von Vorträgen über die Skalierung von Solr auf Hunderte von Millionen bis Milliarden von Dokumenten gehalten. Darüber hinaus kann Solr Facetten und grundlegende Aggregationen (min, max, sum, count, avg und percentiles) über diese großen Datensätze berechnen. Der Fusion SQL-Dienst nutzt die beeindruckende Skalierbarkeit von Solr, um SQL-basierte Analysen über Datensätze mit Dutzenden bis Hunderten von Millionen von Zeilen zu ermöglichen, und das oft nahezu in Echtzeit ohne vorherige Aggregation. Um diese Größenordnung mit herkömmlichen BI-Plattformen zu erreichen, sind Sie in der Regel gezwungen, Aggregationen im Voraus zu berechnen, die nur eine kleine Gruppe von vorgegebenen Abfragen erfüllen können.

Self-Service-Analysen stehen auf der Prioritätenliste vieler CIOs nach wie vor ganz oben, vor allem, weil sich die Unternehmen bemühen, „datengesteuerter“ zu werden. Ich kann mir jedoch nicht vorstellen, dass CIOs Geschäftsanwendern erlauben, ein Tool wie Tableau auch nur auf einen bescheidenen Datensatz im Sinne von Solr zu richten. Fusion SQL macht jedoch echte Self-Service-Analysen zur Realität, ohne dass Sie auf traditionelle Data Warehouse-Techniken zurückgreifen müssen.

Lassen Sie uns zur Veranschaulichung den Datensatz movielens 20M ratings von https://grouplens.org/datasets/movielens/20m/ verwenden . Ich habe diesen Datensatz ausgewählt, weil er mit dem Datensatz übereinstimmt, den ich im ersten Blogbeitrag über Fusion SQL verwendet habe. Um es klar zu sagen, 20M ist ziemlich klein für Solr, aber wie wir gleich sehen werden, belastet es bereits traditionelle SQL-Datenbanken wie MySQL. Um diesen Datensatz zu indizieren, verwenden Sie den Parallel Bulk Loader von Fusion(https://doc.lucidworks.com/fusion-server/4.1/reference-guides/jobs/parallel-bulk-loader.html) unter Verwendung des Fusion Spark Bootcamp Labs: https://github.com/lucidworks/fusion-spark-bootcamp/tree/master/labs/ml-20m
(Hinweis: Sie müssen das Lab nur ausführen, um die 20M Bewertungen zu indizieren, wenn Sie die Abfragen in diesem Blog selbst ausprobieren möchten).

Sie können eine Verknüpfung zwischen der Tabelle movies_ml20m über (id) und der Tabelle ratings_ml20m (movie_id) in Tableau einrichten, wie im folgenden Screenshot gezeigt.

Wenn der Benutzer 1000 Zeilen lädt, sendet Tableau die folgenden Daten an den Fusion SQL-Dienst:

SELECT 1 AS `Anzahl_der_Datensätze`,
`Filme_ml20m`.`Genre` AS `Genre`,
`Ratings_ml20m`.`id` AS `id__ratings_ml20m_`,
`Filme_ml20m`.`id` AS `id`,
`ratings_ml20m`.`movie_id` AS `movie_id`,
`ratings_ml20m`.`rating` AS `rating`,
`ratings_ml20m`.`Timestamp_tdt` AS `Timestamp_tdt`,
`movies_ml20m`.`title` AS `title`,
`ratings_ml20m`.`user_id` AS `user_id`
FROM `default`.`Filme_ml20m` `Filme_ml20m`
JOIN `default`.`Ratings_ml20m` `Ratings_ml20m` ON (`Filme_ml20m`.`id` = `Ratings_ml20m`.`movie_id`)
LIMIT 1000

Hinter den Kulissen übersetzt Fusion SQL dies in eine optimierte Abfrage in Solr. Natürlich ist es keine Kleinigkeit, Joins nativ in Solr durchzuführen, da Solr in erster Linie eine Suchmaschine ist, die auf de-normalisierte Daten angewiesen ist, um ihre beste Leistung zu erbringen. Hinter den Kulissen führt Fusion SQL einen Hash-Join zwischen den Sammlungen ratings_ml20m und movies_ml20m durch, und zwar über die Streaming Expression-Schnittstelle von Solr. Auf meinem Laptop benötigt diese Abfrage etwa 2 Sekunden, um zu Tableau zurückzukehren, wobei der größte Teil dieser Zeit auf das Lesen von 1000 Zeilen aus Solr in Tableau entfällt.

Die gleiche Abfrage gegen MySQL auf meinem Laptop dauert ~4 Sekunden, also kein großer Unterschied, so weit, so gut. Eine schnelle Tabellenansicht der Daten ist schön, aber was wir wirklich wollen, sind aggregierte Metriken. Hier kann der Fusion SQL-Dienst wirklich glänzen.

In meinem letzten Blog habe ich ein Beispiel für eine Aggregat- und dann Join-Abfrage gezeigt:

SELECT m.title as title, agg.aggCount as aggCount FROM movies m INNER JOIN (SELECT movie_id, COUNT(*) as aggCount FROM ratings WHERE rating >= 4 GROUP BY movie_id ORDER BY aggCount desc LIMIT 10) as agg ON agg.movie_id = m.id ORDER BY aggCount DESC

Leider bleibt die Abfrage, wenn ich sie gegen MySQL mit einem Index auf dem Feld movie_id in der Tabelle ratings ausführe, im Grunde genommen hängen (ich habe nach einer Minute Wartezeit aufgegeben). Bei 20 Millionen Zeilen schafft Fusion SQL das in 1,2 Sekunden! Ich habe auch MySQL auf einer Ec2-Instanz (r3.xlarge) ausprobiert und die Abfrage lief in 17 Sekunden, was für Self-Service-Analysen immer noch unhaltbar ist.

0: jdbc:hive2://localhost:8768/default> SELECT m.title as title, agg.aggCount as aggCount FROM movies_ml20m m INNER JOIN (SELECT movie_id, COUNT(*) as aggCount FROM ratings_ml20m WHERE rating >= 4 GROUP BY movie_id ORDER BY aggCount desc LIMIT 10) as agg ON agg.movie_id = m.id ORDER BY aggCount DESC;

Nun gut, vielleicht führt MySQL die Aggregation und anschließende Verknüpfung einfach nicht korrekt aus. Versuchen wir eine andere, realistischere Abfrage, die von Tableau für das folgende Diagramm geschrieben wurde:

SELECT COUNT(1) AS `cnt_number_of_records_ok`,
`ratings_ml20m`.`rating` AS `rating`
FROM `default`.`movies_ml20m` `movies_ml20m`
JOIN `default`.`ratings_ml20m` `ratings_ml20m` ON (`movies_ml20m`.`id` = `ratings_ml20m`.`movie_id`)
WHERE (`movies_ml20m`.`genre` = ‚Comedy‘)
GROUP BY `ratings_ml20m`.`rating`

Fusion SQL führt diese Abfrage in ca. 2 Sekunden aus:

Lassen Sie uns das mit MySQL ausprobieren. Zunächst benötigte >2 Minuten, um die eindeutigen Werte des Bewertungsfeldes zu ermitteln, was Fusion SQL mit Hilfe von Facetten fast sofort erledigt:

Auch hier handelt es sich um eine (für Solr-Verhältnisse) bescheidene Tabelle mit 20 Mio. Zeilen und einem für die Bewertungsspalte erstellten Index. Um die grundlegende Visualisierung zu zeichnen, brauchte die Abfrage mehrere Minuten (wie unten zu sehen ist, warten wir nach 4 Minuten immer noch, nach etwa 5 Minuten war sie beendet).

Ich will hier nicht auf MySQL herumhacken, denn ich bin sicher, dass ein guter DBA es so konfigurieren kann, dass es diese grundlegenden Aggregationen, die von Tableau gesendet werden, verarbeiten kann, oder dass eine andere Datenbank wie Postgres oder SQL Server schneller ist. Aber wenn die Datengröße zunimmt, werden Sie irgendwann ein Data Warehouse mit einigen vorberechneten Aggregationen einrichten müssen, um interessante Fragen zu Ihrem Datensatz zu beantworten. Der springende Punkt ist, dass Fusion SQL es dem Unternehmensanalysten ermöglicht, ein Datenvisualisierungstool wie Tableau auf große Datensätze zu richten, um leistungsstarke Dashboards und Berichte zu erstellen, die auf Ad-hoc-Abfragen basieren, ohne ein Data Warehouse zu verwenden. Im Zeitalter von Big Data werden die Datenmengen immer größer und komplexer.

Wie optimiert Fusion die SQL-Abfragen?

Ein gängiges SQL-Optimierungsmuster ist das Aggregieren und anschließende Verknüpfen, so dass die Verknüpfung mit einer kleineren Menge von aggregierten Zeilen funktioniert, anstatt erst zu verknüpfen und dann zu aggregieren, was zu viel mehr zu verknüpfenden Zeilen führt. Es hat sich herausgestellt, dass die Facetten-Engine von Solr sehr gut für Abfragen im Stil von Aggregat und Verknüpfung geeignet ist.

Beim Aggregieren und anschließenden Verknüpfen können wir Solr-Facetten verwenden, um Metriken für den Verknüpfungsschlüssel-Bucket zu berechnen und dann die Verknüpfung durchzuführen. Wir nutzen auch die Unterstützung von Solr für Rollup-Streaming-Ausdrücke, um Rollup über verschiedene Dimensionen durchzuführen. Dies funktioniert natürlich nur für Equi-Joins, bei denen Sie den Join verwenden, um Metadaten an die Metriken aus anderen Tabellen anzuhängen. Mit der Zeit wird Fusion SQL weitere Optimierungen für andere Arten von Joins hinzufügen.

Was ist mit hochkardinalen Feldern?

Wenn Sie mit Solr vertraut sind, dann wissen Sie wahrscheinlich bereits, dass die verteilte Facettierungs-Engine Zählungen und grundlegende Metriken für Buckets mit geringer Kardinalität durchführen kann. Aber manchmal benötigt eine SQL-Abfrage Dimensionen, die zu einer großen Anzahl von Buckets führen, über die facettiert werden soll (hohe Kardinalität). Stellen Sie sich zum Beispiel eine Gruppierung nach einem Feld mit einer bescheidenen Anzahl eindeutiger Werte (ca. 100.000) vor, die dann aber nach einer zeitlichen Dimension (Woche oder Tag) gruppiert wird, und Sie kommen schnell in eine Situation mit hoher Kardinalität (100K * 365 Tage * N Jahre = viele Dimensionen).

Um mit dieser Situation umzugehen, versucht Fusion SQL, die Kardinalität der Felder in der Gruppenklausel abzuschätzen und verwendet dies, um die richtige Abfragestrategie für Solr zu wählen, entweder Facette für niedrige Kardinalität oder einen Streaming-Ausdruck im Stil von Map/Rece (Rollup) für hohe Kardinalität. Das Wichtigste dabei ist, dass Sie sich als Abfrageautor nicht so viele Gedanken darüber machen müssen, wie Sie dies mit Solr-Streaming-Ausdrücken richtig machen. Fusion SQL übernimmt die harte Arbeit der Übersetzung einer SQL-Abfrage in eine optimierte Solr-Abfrage unter Verwendung der Eigenschaften der zugrunde liegenden Daten.

Das wirft die Frage auf, was eine hohe Kardinalität ausmacht. Lassen Sie uns ein kurzes Experiment mit der Tabelle ratings_ml20m durchführen:

select count(1) as cnt, user_id from ratings_ml20m
group by user_id having count(1) > 1000 order by cnt desc limit 10

Die Abfrage führt eine Zählung für jeden Benutzer in der Bewertungstabelle ~138K durch. Mit Facettierung wird diese Abfrage in 1,8 Sekunden auf meinem Laptop ausgeführt. Wenn Sie den Rollup-Streaming-Ausdruck verwenden, dauert die Abfrage über 40 Sekunden! Bei dieser Größenordnung sind wir mit Facettierung also immer noch besser dran. Als nächstes wollen wir die Kardinalität mit der folgenden Aggregation über user_id und rating erhöhen, die >800K eindeutige Werte hat:

select count(1) as cnt, user_id, rating from ratings_ml20m
group by user_id, rating order by cnt desc limit 10

Mit Facettierung dauert diese Abfrage 8 Sekunden und mit Rollup etwa eine Minute. Das Wichtigste dabei ist, dass der Facettenansatz viel schneller ist als Rollup, selbst bei fast 1 Million eindeutiger Gruppen. Abhängig von der Größe Ihrer Daten und der Komplexität der Gruppen können Sie jedoch einen Punkt erreichen, an dem die Facettierung versagt und Sie Rollup verwenden müssen. Mit der Einstellung fusion.sql.bucket_size_limit.threshold können Sie den Schwellenwert konfigurieren, ab dem Fusion SQL Rollup anstelle von Facet verwendet.

Volltext-Abfragen

Eine der schönen Funktionen von Solr als Backend für Fusion SQL ist, dass wir Volltextsuchen durchführen und nach Wichtigkeit sortieren können. In älteren Versionen von Fusion waren wir darauf angewiesen, eine vollständige Subquery an den parallelen SQL-Handler von Solr weiterzuleiten, um eine Volltextabfrage mit der Syntax _query_ durchzuführen. In Fusion 4.1 können Sie dies jedoch einfach tun:

select title from movies where plot_txt_en = ‚dogs‘
oder
select title from movies where plot_txt_en IN (‚dogs‘, ‚cats‘)

Fusion SQL konsultiert die Solr-Schema-API, um zu wissen, dass plot_txt_de ein indiziertes Textfeld ist, und führt eine Volltextabfrage durch, anstatt zu versuchen, eine exakte Übereinstimmung mit dem Feld plot_txt_en zu erzielen. Fusion SQL stellt auch eine UDF namens _query_ zur Verfügung, mit der Sie jede gültige Solr-Abfrage über SQL an Solr übergeben können, wie z.B.:

select ort_name,zip_code from zipcodes where _query_(‚{!geofilt sfield=geo_location pt=44.9609,-93.2642 d=50}‘)

Vermeiden von Tabellen-Scans

Was passiert, wenn wir eine optimierte Abfrage nicht in Solr einfügen können? Spark gibt WHERE-Filter und Feldprojektionen automatisch an die spark-solr-Bibliothek weiter. Wenn eine Abfrage jedoch mit 10 Millionen Dokumenten in Solr übereinstimmt, wird Spark diese von Solr streamen, um die Abfrage auszuführen. Wie Sie sich vorstellen können, kann dies langsam sein, je nachdem, wie viele Solr-Knoten Sie haben. Wir haben Tabellen-Scan-Raten von 1-2 Mio. Dokumenten pro Sekunde pro Solr-Knoten gesehen, so dass das Lesen von 10 Mio. Dokumenten in einem Cluster mit 3 Knoten bestenfalls 3-5 Sekunden dauern kann (plus eine heftige E/A-Spitze zwischen Solr und Spark). Natürlich haben wir dies in spark-solr so gut wie möglich optimiert, aber das Wichtigste dabei ist, dass Sie Abfragen vermeiden, die große Tabellenscans von Solr erfordern.

Eines der Risiken, wenn Sie ein Self-Service-Analysetool auf sehr große Datensätze ausrichten, besteht darin, dass die Benutzer eine Abfrage erstellen, die eine große Tabellensuche erfordert, was die Ressourcen Ihres Clusters in Anspruch nehmen kann. Fusion SQL verfügt über einen konfigurierbaren Schutzmechanismus für diese Situation. Wenn eine Abfrage mehr als 2 Millionen Zeilen benötigt, schlägt sie standardmäßig fehl. Bei größeren Clustern kann dieser Schwellenwert zu niedrig sein. Daher können Sie den Schwellenwert mit der Konfigurationseigenschaft fusion.sql.max_scan_rows erhöhen.

Nachbereitung

In diesem Beitrag habe ich beschrieben, wie Fusion SQL die Erstellung umfangreicher Visualisierungen mit Tools wie Tableau auf großen Datenbeständen ermöglicht. Durch die Nutzung der Facetten-Engine von Solr und der Streaming-Ausdrücke können Sie SQL-Aggregationen, Ad-hoc-Abfragen und Joins über Millionen von Zeilen in Solr nahezu in Echtzeit durchführen. Darüber hinaus war die horizontale Skalierung von Fusion zur Verarbeitung größerer Datenmengen noch nie so einfach und kosteneffizient, insbesondere im Vergleich zu herkömmlichen BI-Ansätzen. Wenn Sie in Ihrem Unternehmen Self-Service-Analysen anbieten möchten, empfehle ich Ihnen, Fusion 4.1 noch heute herunterzuladen und den SQL-Service auszuprobieren.

Nächste Schritte:

• Lesen: Lucidworks 4.1 bereit zum Download
• Beobachten: Was ist neu in Lucidworks Fusion 4.1
• Kontaktieren Sie uns, wir helfen Ihnen gerne.