SQL in Fusion 3
In diesem Beitrag stelle ich Ihnen einen neuen Dienst in Fusion vor, mit dem Benutzer SQL-Abfragen gegen ihre Fusion-Sammlungen mit…
In diesem Beitrag stelle ich Ihnen einen neuen Dienst in Fusion vor, mit dem Benutzer SQL-Abfragen gegen ihre Fusion-Sammlungen mit einer beliebigen JDBC-kompatiblen Anwendung wie Tableau oder Apache Zeppelin durchführen können. Bevor wir in die Details der Nutzung des SQL-Dienstes eintauchen, sollten Sie zunächst verstehen, warum Lucidworks sich freut, diese neue Funktion anbieten zu können.
Motivation
Eines der Muster, die wir bei unseren Kunden beobachten, ist, dass sie Fusion verwenden, um ein Suchproblem zu lösen, dann aber schnell feststellen, dass sie ein Big Data-Problem haben, da sie nun benutzergenerierte Ereignisse, in Fusion als Signale bezeichnet, aus der Suchanwendung erfassen können. Das ist ein gutes Problem, denn diese Signale können in Form von Relevanzsteigerung, Empfehlungen und anderen maschinellen Lernmodellen, wie z.B. der Klassifizierung von Suchanfragen, in das Sucherlebnis zurückgeführt werden. Diese Signale sind auch eine wahre Goldgrube an halbstrukturierten Informationen über Ihre Benutzer und der Fusion SQL-Service bietet neue Möglichkeiten zur Analyse dieser Daten mit BI-Tools wie Tableau.
Selbstbedienungs-Analytik
Die Bereitstellung von Self-Service-Analyselösungen steht für viele CIOs ganz oben auf der Prioritätenliste, insbesondere wenn es um Big-Data-Projekte geht, da Datenwissenschaftler und Big-Data-Ingenieure knappe Ressourcen sind. Außerdem ist SQL nach wie vor die lingua-franca der Analytik und es gibt viele Datenvisualisierungs- und Dashboarding-Tools, die SQL unterstützen. Mit der Veröffentlichung von Fusion 3 können Unternehmensanalysten mit dem SQL-Tool ihrer Wahl Datensätze von Interesse entdecken, analysieren und visualisieren. Damit entfällt die Umschulung der Benutzer auf proprietäre Tools und Abfragesprachen, was die Rentabilität der Investitionen in Visualisierungstools erhöht.
Was nützt ein Self-Service, wenn die Daten in Silos eingeschlossen sind? Fusion bietet Konnektoren für die Indizierung von Daten aus über 60 Unternehmenssystemen und Datenbanken. Letztendlich werden zahlreiche geschäftskritische Datensätze aufgrund der Geschwindigkeit, Flexibilität und Skalierbarkeit von Solr in Fusion verarbeitet. Der SQL-Dienst stellt die Fähigkeiten von Solr zur Verfügung, darunter die Relevanzeinstufung von Dokumenten, die unscharfe Freitextsuche, die Durchquerung von Graphen über Beziehungen und die fliegende Anwendung von Klassifizierungs- und maschinellen Lernmodellen für diese unternehmenskritischen Datensätze.
In den meisten Unternehmen gibt es Fachexperten (SME) für bestimmte Datensätze. Der Fusion SQL-Service bietet KMUs die Möglichkeit, ihr Fachwissen in Form von Datentransformationen, Anreicherungen und Filtern zu nutzen und gleichzeitig die Komplexität vor anderen Nutzern eines gemeinsamen Datensatzes zu verbergen. Ein Dateningenieur, der mit der Support-Organisation zusammenarbeitet, kann beispielsweise Fusion verwenden, um eine Ansicht für die Marketing- und Vertriebsteams zu erstellen, die ihnen hilft, die aktuellen Probleme beim Kundensupport zu verstehen. Die Möglichkeit, „Ansichten“ komplexer Datenquellen zu erstellen, ist besonders wichtig für Unternehmen mit einem Data Lake, in dem die Quelldaten unübersichtlich sein können und eine Reihe von Transformationen erfordern, um für Unternehmensanalysten nützlich zu sein. Obwohl die SQL-Engine von massiv skalierbaren Technologien wie Apache Spark und Apache Solr angetrieben wird, muss sich der Unternehmensanalyst nicht mit diesen komplexen Technologien auseinandersetzen und kann sich darauf konzentrieren, die wichtigen Fragen zu seinen Daten zu stellen.
Tiefergehende Analysefähigkeiten
Bis zu diesem Punkt konzentrierte sich die Analysegeschichte von Fusion auf die Visualisierung von Facettenabfragen für in Solr gespeicherte Daten. Mit dem SQL-Dienst können wir jetzt über Facettenabfragen hinausgehen und fortgeschrittenere Analysen durchführen, z. B. leistungsstarke SQL-Aggregationen mit einer Fülle von integrierten UDF/UDAF-Funktionen, mehrdimensionale OLAP-Würfel und Joins über mehrere Solr-Sammlungen und/oder externe Datenquellen (Hive-Tabelle, Cassandra CF, JDBC-Datenbank usw.).
Auch die übliche Vorschrift, Daten für eine schnelle Suche in Solr zu denormalisieren, ist für analytische Anwendungsfälle nicht immer praktisch/möglich. Daher bietet der SQL-Dienst von Fusion die Möglichkeit, Tabellen zu verknüpfen.
Datenverwaltung
Und schließlich ist die Datenverwaltung, insbesondere für Big Data, nach wie vor ein wichtiges Anliegen für CIOs. Der SQL-Dienst verwendet Fusion-Berechtigungen, um den Zugang zu sensiblen Datensätzen zu schützen und gibt Fusion-Administratoren die volle Kontrolle darüber, welche Tabellen ein Benutzer abfragen kann.
Ich hoffe, dass ich Ihnen jetzt ein paar Gründe genannt habe, sich für den SQL-Dienst in Fusion zu begeistern. Als Nächstes wollen wir die Engine ausprobieren, um zu sehen, wie sie funktioniert.
Erste Schritte
Falls Sie dies noch nicht getan haben, laden Sie bitte Fusion herunter und installieren Sie es unter: lucidworks.com/download
In diesem Blog verwenden wir $FUSION_HOME für das Verzeichnis, in dem Sie Fusion installiert haben, z.B: /opt/fusion/3.0.0.
Starten Sie Fusion, wenn es nicht läuft:
cd $FUSION_HOME bin/fusion start
Melden Sie sich bei der Fusion Admin UI an unter: http://localhost:8764/
Sie sollten auch den Fusion SQL-Dienst starten:
bin/sql start
Sie können sofort loslegen und den SQL-Dienst mit Ihren eigenen Daten verwenden. Zur Veranschaulichung führe ich Sie jedoch durch das movielens-Labor in unserem fusion-spark-bootcamp Projekt. Der movielens-Datensatz ist ein gutes Beispiel für den SQL-Dienst, da er mehrere Tabellen enthält, die wir miteinander verknüpfen können, sowie Volltextfelder und geografische Daten.
Öffnen Sie ein Kommandozeilen-Terminal und klonen Sie das Github-Projekt, indem Sie Folgendes tun:
git clone https://github.com/lucidworks/fusion-spark-bootcamp.git
Tipp: Wenn Sie kein Git-Benutzer sind, können Sie die Zip-Datei des Projekts problemlos unter fusion-spark-bootcamp/master.zip herunterladen.
cd fusion-spark-bootcamp
Bearbeiten Sie das Skript myenv.sh, um die Variablen für Ihre Umgebung zu setzen.
cd labs/movielens ./setup_movielens.sh
Das Setup-Skript lädt den movielens-Datensatz herunter und lädt ihn in Fusion. Es kann einige Minuten dauern, bis es das erste Mal ausgeführt wird, da das Fusion spark-shell Wrapper-Skript das Fusion job jar für Spark-Jobs herunterladen muss. Nachdem das Setup-Skript abgeschlossen ist, stehen Ihnen die folgenden Tabellen für SQL-Abfragen zur Verfügung:
Um zu überprüfen, ob die SQL-Engine läuft und die movielens-Tabellen korrekt geladen sind, können Sie Folgendes tun:
curl -XPOST -H "Content-Type:application/json"
"http://localhost:8765/api/v1/catalog/fusion/query" -d '{"sql":"show tables"}'
Außerdem enthält das movielens-Labor eine explore_movielens.sh Skript, das die Fusion Catalog API verwendet, um den Datenbestand zu untersuchen und SQL-Anweisungen auszuführen. Die Catalog API ist eine API auf höherer Ebene, die JDBC verwendet, um Abfragen zu senden und Datenbestände in der SQL-Engine zu verwalten. Weitere Informationen über die Catalog-API finden Sie unter:
REST_API_Reference/Catalog-API.html
JDBC-Konnektivität
Im nächsten Abschnitt werde ich Tableau verwenden, um den movielens-Datensatz zu untersuchen. Wenn Sie Tableau nicht zur Verfügung haben, können Sie jede Client-Anwendung verwenden, die JDBC unterstützt. Eine gute Open-Source-Option ist Apache Zeppelin, über das wir demnächst in einem Blog berichten werden. Hier sind die Einstellungen, wenn Sie JDBC für den Zugriff auf den SQL-Dienst verwenden möchten:
Driver: org.apache.hive.jdbc.HiveDriver URL: jdbc:hive2://:8768/default?hive.server2.transport.mode=http;hive.server2.thrift.http.path=fusion
Der Benutzername und das Passwort sind dieselben, die Sie für die Authentifizierung bei Fusion verwenden.
Das folgende Diagramm zeigt die Architektur der SQL-Engine, damit Sie sehen können, wo Tools wie Tableau und die Catalog API hineinpassen:
Verbindung mit dem SQL-Dienst von Fusion von Tableau aus
Ich habe mich für diesen Blog für Tableau entschieden, da es mit geringem Aufwand wunderschöne Visualisierungen erstellt und in vielen Unternehmen eingesetzt wird. Ich biete eine Anleitung für die Verbindung mit Tableau Desktop Professional und Tableau Public.
Tableau Desktop Professional
Wenn Sie die Professional-Version von Tableau Desktop haben, können Sie den Spark SQL-Connector verwenden. Möglicherweise müssen Sie den Spark SQL-Treiber für Tableau installieren, siehe https://onlinehelp.tableau.com/current/pro/desktop/en-us/examples_sparksql.html und https://databricks.com/spark/odbc-driver-download
Füllen Sie die Eigenschaften der Spark SQL-Verbindung wie folgt aus:
Sobald Sie verbunden sind, müssen Sie das „Standard“-Schema auswählen, wie im folgenden Screenshot dargestellt.
Nachdem Sie das Standardschema ausgewählt haben, klicken Sie auf das Suchsymbol, um alle in der SQL-Engine durch den Fusion Catalog registrierten Tabellen / Ansichten anzuzeigen.
Hinweis: Es kann sein, dass Sie eine andere Liste von Tabellen sehen, als in dieser Abbildung gezeigt wird.
Fusion Web Data Connector mit Tableau Public
Wenn Sie keinen Zugang zur professionellen Version von Tableau Desktop haben, können Sie die experimentelle(nicht offiziell unterstützte) Beta-Version des Web Data Connector (WDC) von Fusion für Tableau und/oder Tableau Public verwenden. Der WDC hat im Vergleich zur professionellen Desktop-Version einen eingeschränkten Funktionsumfang (er ist kostenlos), ist aber für den Einstieg in die Fusion SQL-Engine nützlich.
Öffnen Sie ein Terminal und klonen Sie das folgende Projekt von github:
git clone https://github.com/lucidworks/tableau-fusion-wdc.git
Nachdem Sie das Repository geklont haben, wechseln Sie in das Verzeichnis tableau-fusion-wdc und führen Sie aus:
npm install --production npm start
Sie haben jetzt einen Webserver und einen Cors-Proxy, die auf den Ports 8888 bzw. 8889 laufen.
Als Referenz siehe: http://tableau.github.io/tableau-fusion-wdc/docs/
Laden Sie Tableau Public herunter, installieren und starten Sie es unter: https://public.tableau.com/en-us/s/download
Klicken Sie auf den Link Web Data Connector unter Verbinden und geben Sie ein: http://localhost:8888/fusion.html wie unten gezeigt:
Klicken Sie im Dialog Fusion auf die Schaltfläche Fusion-Tabellen laden:
Hinweis: Die Liste der Tabellen, die Sie sehen, kann sich von der Liste im obigen Beispielscreenshot unterscheiden.
Wählen Sie die Tabellen, die Sie importieren möchten, und wenden Sie optional Filter und/oder Stichproben an. Für kleine Tabellen (<50K rows) müssen Sie keine Filter oder Stichproben anwenden. Klicken Sie auf Fertig, wenn Sie bereit sind, die Tabellen in Tableau Public zu laden. Nach ein paar Sekunden sollten Sie Ihre Tabellen auf der linken Seite aufgelistet sehen.
Hinweis: Es werden nur Fusion-Sammlungen angezeigt, die Daten enthalten. Wenn Sie Daten zu einer zuvor leeren Sammlung hinzufügen, müssen Sie Tableau Public aktualisieren, um sie zu übernehmen.
Lassen Sie uns eine Tableau-Datenquelle erstellen, die die 4 Tabellen (Filme, Bewertungen, Benutzer und Postleitzahlen) wie unten gezeigt miteinander verbindet:
Als nächstes klicken Sie auf die Schaltfläche Blatt 1 und erstellen eine Visualisierung mit unseren Daten aus Fusion. Beachten Sie, dass Tableau Public einen Auszug Ihres Datensatzes erstellt und diesen auf den Desktop zieht, bevor irgendwelche Filter angewendet werden. Seien Sie daher vorsichtig mit großen Tabellen. Verwenden Sie die Filteroptionen auf dem ersten Fusion WDC-Bildschirm, um die Daten einzuschränken. Die professionelle Version sendet Abfragen zurück an die Fusion SQL-Engine und kann daher größere Datensätze effizienter verarbeiten.
Hier ist eine Visualisierung, bei der ich eine verschachtelte Sortierung verwendet habe, um die beliebtesten Filme nach Bundesland zu finden (anhand der durchschnittlichen Bewertung):
Hier finden Sie einige SQL-Abfragen für den movielens-Datensatz, um Ihnen den Einstieg zu erleichtern:
Aggregieren und dann Verbinden:
SELECT m.title as title, solr.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 solr ON solr.movie_id = m.movie_id ORDER BY aggCount DESC
Benutzer innerhalb von 50 km von Minneapolis:
SELECT solr.place_name, count(*) as cnt FROM users u INNER JOIN (select place_name,zip_code from zipcodes where _query_='{!geofilt sfield=geo_location pt=44.9609,-93.2642 d=50}') as solr ON solr.zip_code = u.zip_code WHERE u.gender='F' GROUP BY solr.place_name
Avg. Bewertung für Filme mit dem Begriff „Liebe“ im Plot:
SELECT solr.title as title, avg(rating) as avg_rating FROM ratings INNER JOIN (select movie_id,title from movies where _query_='plot_txt_en:love') as solr ON ratings.movie_id = solr.movie_id GROUP BY title ORDER BY avg_rating DESC LIMIT 10
Was für coole Visualisierungen können Sie sich für die movielens oder Ihre Daten ausdenken?
Tipps und Tricks
In diesem Abschnitt gebe ich Tipps, wie Sie die beste Leistung aus der SQL-Engine herausholen können. Im Allgemeinen gibt es drei Klassen von Abfragen, die von der Engine unterstützt werden:
- Lesen einer Reihe von Rohzeilen aus Solr und Zusammenführen/Aggregieren in Spark
- Push-down-Aggregationsabfragen in Solr, die eine kleinere Menge aggregierter Zeilen an Spark zurückgeben
- Ansichten, die Abfragen (Solr SQL oder Streaming-Ausdrücke) direkt an Solr senden und dabei Optionen verwenden, die von der spark-solr-Bibliothek unterstützt werden
Aggregat in Spark
Bei Abfragen, die darauf beruhen, dass Spark Joins und Aggregationen auf aus Solr ausgelesenen Rohzeilen durchführt, besteht Ihr Ziel darin, die Anzahl der aus Solr ausgelesenen Zeilen zu minimieren und die beste Leseleistung für diese Zeilen zu erzielen. Hier finden Sie einige Strategien, um diese Ziele zu erreichen:
Strategie 1: Eine optimale Leseleistung wird erreicht, indem nur Felder abgefragt werden, für die docValues aktiviert sind, da diese über den /export-Handler gezogen werden können.
Es versteht sich von selbst, dass Sie nur die Felder abfragen sollten, die Sie für jede Abfrage benötigen. Der Abfrageplaner von Spark gibt die Feldliste an die SQL-Engine von Fusion weiter, die sie in einen fl-Parameter umwandelt. Wenn Sie zum Beispiel movie_id und title aus der Tabelle movies benötigen, gehen Sie wie folgt vor:
select movie_id, title from movies
Das nicht:
select * from movies
Strategie 2: Verwenden Sie WHERE-Klausel-Kriterien, einschließlich vollständiger Solr-Abfragen, um so viel wie möglich in Solr zu filtern und die Anzahl der Zeilen zu reduzieren.
Der SQL-Abfrageplaner von Spark gibt einfache Filterkriterien an die Fusion SQL-Engine weiter, die SQL-Filter in Solr-Filterabfrageparameter (fq) umsetzt. Wenn Sie zum Beispiel eine Abfrage mit SQL durchführen möchten:
select user_id, movie_id, rating from ratings where rating = 4
Hinter den Kulissen wandelt die SQL-Engine von Fusion diese Abfrage dann in die folgende Solr-Abfrage um:
q=*:*&qt=/export&sort=id+asc&collection=ratings&fl=user_id,movie_id,rating&fq=rating:4
Beachten Sie, dass die WHERE-Klausel in eine fq-Klausel umgewandelt wurde und die für die Abfrage benötigten spezifischen Felder im fl-Parameter mitgeschickt werden. Beachten Sie auch, dass die SQL-Engine von Fusion den /export-Handler verwendet, wenn für alle angeforderten Felder docValues aktiviert sind. Das macht einen großen Unterschied in der Leistung.
Um eine Volltextsuche durchzuführen, müssen Sie die Syntax _query_ verwenden, die von der parallelen SQL-Engine von Solr unterstützt wird, z.B:
select movie_id,title from movies where _query_='plot_txt_en:love'
Die Fusion SQL-Engine versteht die Syntax _query_, um anzuzeigen, dass diese Abfrage direkt in die Parallel SQL-Engine von Solr übertragen werden soll. Weitere Informationen über die Parallel SQL-Unterstützung von Solr finden Sie unter: Parallele SQL-Schnittstelle von Solr. Die wichtigste Erkenntnis ist, dass Sie die _query_ Syntax verwenden müssen, wenn Sie Solr benötigen, um eine Abfrage auszuführen, die über die grundlegende Filterlogik hinausgeht.
Hier ist ein weiteres Beispiel, bei dem wir eine Unterabfrage direkt in Solr einfügen, um einen georäumlichen Filter anzuwenden und dann die resultierenden Zeilen mit einer anderen Tabelle zu verknüpfen:
SELECT solr.place_name, count(*) as cnt
FROM users u
INNER JOIN (select place_name,zip_code from zipcodes where _query_='{!geofilt sfield=geo_location pt=44.9609,-93.2642 d=50}') as solr
ON solr.zip_code = u.zip_code WHERE u.gender='F' GROUP BY solr.place_name
Beachten Sie, dass Sie die Unterabfrage mit dem Alias„as solr“ versehen müssen, damit die Fusion SQL-Engine weiß, dass sie den Push-Down in Solr durchführen soll. Diese Einschränkung wird in einer zukünftigen Version von Fusion gelockert werden.
Strategie 3: Anwendung von Begrenzungsklauseln auf Push-Down-Abfragen
Nehmen wir an, wir haben eine Tabelle mit Filmen und Bewertungen und möchten den Titel mit der Bewertungstabelle verknüpfen, um die Top 100 Filme mit den meisten Bewertungen zu erhalten, indem wir so vorgehen:
select m.title, count(*) as num_ratings from movies m, ratings r where m.movie_id = r.movie_id group by m.title order by num_ratings desc limit 100
Angesichts dieser LIMIT-Klausel könnten Sie denken, dass diese Abfrage sehr schnell sein wird, da Sie nur 100 Zeilen abfragen. Wenn die Bewertungstabelle jedoch sehr groß ist (was in der Regel der Fall ist), muss Spark vor dem Zusammenführen und Aggregieren alle Bewertungen aus Solr lesen. Der bessere Ansatz besteht darin, den LIMIT nach unten in Solr zu verschieben und dann aus der kleineren Ergebnismenge zusammenzuführen.
select m.title, solr.num_ratings from movies m inner join (select movie_id, count(*) as num_ratings from ratings group by movie_id order by num_ratings desc limit 100) as solr on m.movie_id = solr.movie_id order by num_ratings desc
Beachten Sie, dass die Begrenzung jetzt für die Unterabfrage gilt, die direkt von Solr ausgeführt wird. Sie sollten diese Strategie verwenden, egal ob Sie in Solr aggregieren oder nur Rohzeilen abrufen, wie z.B.:
SELECT e.id, e.name, solr.* FROM ecommerce e INNER JOIN (select timestamp_tdt, query_s, filters_s, type_s, user_id_s, doc_id_s from ecommerce_signals order by timestamp_tdt desc limit 50000) as solr ON solr.doc_id_s = e.id
Die Unterabfrage holt die letzten 50.000 Signale aus Solr, bevor sie mit der E-Commerce-Tabelle verbunden wird.
Strategie 4: Wenn Sie Felder aus Solr zurückgeben müssen, die docValues nicht unterstützen, sollten Sie die Leseoptionen des zugrunde liegenden Datenbestands in Fusion anpassen
Hinter den Kulissen verwendet die Fusion SQL-Engine parallele Abfragen an jeden Shard und cursorMark, um alle Dokumente in jedem Shard durchzublättern. Dieser Ansatz ist zwar effizient, aber nicht so schnell wie das Lesen aus dem /export-Handler. Unsere E-Commerce-Tabelle enthält beispielsweise Textfelder, die nicht mit docValues exportiert werden können, so dass wir die Leseleistung mit der Catalog-API anpassen können:
curl -X PUT -H "Content-type:application/json" --data-binary '{
"name": "ecommerce",
"assetType": "table",
"projectId": "fusion",
"description": "ecommerce demo data",
"tags": ["fusion"],
"format": "solr",
"cacheOnLoad": false,
"options" : [ "collection -> ecommerce", "splits_per_shard -> 4", "solr.params -> sort=id asc", "exclude_fields -> _lw_*,_raw_content_", "rows -> 10000" ]
}' $FUSION_API/catalog/fusion/assets/ecommerce
Beachten Sie in diesem Fall, dass wir alle Felder lesen, außer denen, die den Mustern in der Option exclude_fields entsprechen. Außerdem haben wir die Anzahl der Zeilen, die pro Paging-Anfrage gelesen werden, auf 10.000 erhöht und wollen 4 splits_per_shard, was bedeutet, dass wir 4 Tasks pro Shard verwenden, um Daten über alle Replikate dieses Shards zu lesen. Weitere Informationen zur Optimierung der Leseleistung finden Sie unter: spark-solr. Wenn Ihre Abfragen Felder benötigen, die nicht exportiert werden können, müssen Sie die Optionen für das Daten-Asset manuell anpassen, um eine optimale Leistung zu erzielen.
Aggregieren in Solr
Solr ist eine erstaunliche verteilte Aggregations-Engine, die Sie so weit wie möglich nutzen sollten, da dies die Berechnung von Aggregationen verteilt und die Anzahl der Zeilen, die von Solr an Spark zurückgegeben werden, reduziert. Je geringer die Anzahl der von Solr zurückgegebenen Zeilen ist, desto besser kann Spark die Abfrage optimieren, z.B. eine Übertragung einer kleinen Tabelle über alle Partitionen einer großen Tabelle (Hash Join).
Strategie 5: Führen Sie Aggregationen in Solr mit Parallel SQL durch
Hier ist ein Beispiel, bei dem wir eine Gruppenzählung (im Grunde eine Facette) mit Hilfe einer Unterabfrage in Solr durchführen.
SELECT m.title as title, solr.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 solr
ON solr.movie_id = m.movie_id
ORDER BY aggCount DESC
Solr gibt aggregierte Zeilen nach movie_id zurück und dann nutzen wir Spark, um die Verknüpfung zwischen Filmen und den aggregierten Ergebnissen der Unterabfrage durchzuführen, was mit einem Hash Join mit Broadcast schnell möglich ist. Das Aggregieren und anschließende Verknüpfen ist ein gängiges Muster in SQL, siehe:
Unterabfragen-verwenden-um-50x-schneller-zu-zählen.
Strategie 6: Aggregieren Sie in Solr mit Streaming-Ausdrücken
Die Parallel SQL-Unterstützung von Solr befindet sich noch in der Entwicklung und bietet noch nicht die Möglichkeit, alle Aggregationsfunktionen von Solr zu nutzen. Sie können jedoch einen Solr-Streaming-Ausdruck schreiben und diesen dann als „Ansicht“ in der Fusion SQL-Engine bereitstellen. Der folgende Streaming-Ausdruck verbindet zum Beispiel E-Commerce-Produkte und Signale:
select(
hashJoin(
search(ecommerce,
q="*:*",
fl="id,name",
sort="id asc",
qt="/export",
partitionKeys="id"),
hashed=facet(ecommerce_signals,
q="*:*",
buckets="doc_id_s",
bucketSizeLimit=10000,
bucketSorts="count(*) desc",
count(*)),
on="id=doc_id_s"),
name as product_name,
count(*) as click_count,
id as product_id
)
Dieser Streaming-Ausdruck führt einen HashJoin zwischen der E-Commerce-Tabelle und den Ergebnissen eines Facettenausdrucks für die Signalsammlung durch. Wir verwenden auch den Select Expression Decorator, um menschenfreundliche Feldnamen zurückzugeben. Weitere Informationen über das Schreiben von Streaming-Ausdrücken finden Sie unter: Solr Streaming-Ausdrücke
Hier ist ein weiteres Beispiel für einen Streaming-Ausdruck, der die Unterstützung der zugrundeliegenden Facetten-Engine für die Berechnung von Aggregationen nutzt, die über eine reine Zählung hinausgehen:
select(
facet(
ratings,
q="*:*",
buckets="rating",
bucketSorts="count(*) desc",
bucketSizeLimit=100,
count(*),
sum(rating),
min(rating),
max(rating),
avg(rating)
),
rating,
count(*) as the_count,
sum(rating) as the_sum,
min(rating) as the_min,
max(rating) as the_max,
avg(rating) as the_avg
)
Sie müssen die Katalog-API verwenden, um ein Daten-Asset zu erstellen, das Ihren Streaming-Ausdruck ausführt. Zum Beispiel:
curl -XPOST -H "Content-Type:application/json" --data-binary '{
"name": "ecomm_popular_docs",
"assetType": "table",
"projectId": "fusion",
"description": "Join product name with facet counts of docs in signals",
"tags": ["ecommerce"],
"format": "solr",
"cacheOnLoad": true,
"options": ["collection -> ecommerce", "expr -> select(hashJoin(search(ecommerce,q="*:*",fl="id,name",sort="id asc",qt="/export",partitionKeys="id"),hashed=facet(ecommerce_signals,q="*:*",buckets="doc_id_s",bucketSizeLimit=10000,bucketSorts="count(*) desc",count(*)),on="id=doc_id_s"),name as product_name,count(*) as click_count,id as product_id)"]}' $FUSION_API/catalog/fusion/assets
Strategie 7: Stichproben verwenden
Ab Fusion 3 müssen Sie eine zurückgesetzte Ansicht definieren, um eine Zufallsstichprobe von Dokumenten zu erhalten, da die Parallel SQL-Implementierung in Solr noch keine Zufallsstichproben unterstützt. Sie müssen die Option sample_pct verwenden, wenn Sie aus Solr lesen:
{
"name": "sampled_ratings",
"assetType": "table",
"projectId": "fusion",
"description": "movie ratings data",
"tags": ["movies"],
"format": "solr",
"cacheOnLoad": true,
"options": ["collection -> movielens_ratings", "sample_pct -> 0.1", "sample_seed -> 5150", "fields -> user_id,movie_id,rating,rating_timestamp", "solr.params -> sort=id asc"]
}
HINWEIS: Es gibt einen Dekorator für zufällige Streaming-Ausdrücke, aber aufgrund eines Fehlers wurde er bis Solr 6.4 nicht korrekt angezeigt, siehe: SOLR-9919
Strategie 8: Zwischenspeichern Sie die Ergebnisse in Spark, wenn Sie weitere Abfragen mit den Ergebnissen durchführen möchten.
Katalog-Assets unterstützen das Attribut cacheOnLoad, mit dem die Ergebnisse der Abfrage in Spark zwischengespeichert werden (im Speicher mit Auslagerung auf die Festplatte). Sie können auch verlangen, dass die Ergebnisse jeder Abfrage, die an die Katalog-API gesendet wird, mit dem Parameter cacheResultsAs zwischengespeichert werden:
curl -XPOST -H "Content-Type:application/json" -d '{
"sql":"SELECT u.user_id as user_id, age, gender, occupation, place_name, county, state, zip_code, geo_location_rpt, title, movie_id, rating, rating_timestamp FROM minn_users u INNER JOIN movie_ratings m ON u.user_id = m.user_id",
"cacheResultsAs": "ratings_by_minn_users"
}' "$FUSION_API/catalog/fusion/query"
Seien Sie vorsichtig! Wenn eine Ansicht im Cache gespeichert wird, sind Aktualisierungen der zugrunde liegenden Datenquelle, höchstwahrscheinlich Solr, nicht mehr sichtbar. Um Spark zu veranlassen, eine zwischengespeicherte Ansicht neu zu berechnen, indem Sie auf den zugrunde liegenden Speicher zurückgreifen, können Sie den folgenden SQL-Befehl verwenden:
curl -XPOST -H "Content-Type:application/json" -d '{"sql":"refresh table"}' "$FUSION_API/catalog/fusion/query"
Wenn eine Tabelle nicht im Cache gespeichert ist, können Sie sie mit:
curl -XPOST -H "Content-Type:application/json" -d '{"sql":"cache table"}' "$FUSION_API/catalog/fusion/query"
Oder öffnen Sie den Cache:
curl -XPOST -H "Content-Type:application/json" -d '{"sql":"uncache table"}' "$FUSION_API/catalog/fusion/query"
Strategie 9: Verwenden Sie SQL und die benutzerdefinierten Funktionen (UDF) von Spark, um Daten aus Solr zu bereinigen und/oder zu transformieren.
Die Such-Hub-Signale verwenden zum Beispiel komplexe Feldnamen, die von Snowplow generiert werden. Die folgende Data Asset-Definition verwendet SQL, um die Daten aus Solr etwas benutzerfreundlicher zu gestalten:
{
"name": "shub_signals",
"assetType": "table",
"projectId": "fusion",
"description": "SearchHub signals",
"tags": ["shub"],
"format": "solr",
"cacheOnLoad": false,
"options": ["collection -> shub_signals", "solr.params -> sort=id asc", "fields -> timestamp_tdt,type_s,params.useragent_family_s,params.useragent_os_family_s,params.tz_s,params.totalResults_s,params.lang_s,params.useragent_type_name_s,params.terms_s,params.query_unique_id,params.useragent_v,params.doc_0,params.doc_1,params.doc_2,params.facet_ranges_publishedOnDate_before_d,params.uid_s,params.refr_s,params.useragent_category_s,params.sid_s,ip_sha_s,params.vid_s,params.page_s,params.fp_s"],
"sql": "SELECT timestamp_tdt as timestamp, type_s as signal_type, `params.useragent_family_s` as ua_family,`params.useragent_os_family_s` as ua_os,`params.tz_s` as tz,cast(`params.totalResults_s` as int) as num_found, `params.lang_s` as lang, `params.useragent_type_name_s` as ua_type, `params.terms_s` as query_terms, `params.query_unique_id` as query_id, `params.useragent_v` as ua_vers, `params.doc_0` as doc0, `params.doc_1` as doc1, `params.doc_2` as doc2, `params.facet_ranges_publishedOnDate_before_d` as pubdate_range, `params.uid_s` as user_id, `params.refr_s` as referrer, `params.useragent_category_s` as ua_category, `params.sid_s` as session_id, `ip_sha_s` as ip, cast(`params.vid_s` as int) as num_visits, `params.page_s` as page_name, `params.fp_s` as fingerprint FROM shub_signals"
}
Da Spark die verzögerte Auswertung von DataFrame-Transformationen verwendet, entsteht neben den Kosten für das Lesen von Solr zur Ausführung einer SQL-Anweisung auf den Rohergebnissen von Solr nur wenig zusätzlicher Aufwand. Neben der einfachen Umbenennung von Feldern können Sie auch 100 integrierte UDFs nutzen, um Felder anzureichern/umzuwandeln, siehe: Spark SQL-Funktionen
Im obigen Beispiel verwenden Sie das Feld „cast“, um ein String-Feld aus Solr in ein int-Feld umzuwandeln:
cast(`params.vid_s` as int)
Denken Sie daran, dass Solr eine viel flexiblere Daten-Engine ist, als es BI-Visualisierungstools wie Tableau normalerweise sind. Daher können Sie mit der Fusion Catalog API mehr Struktur auf weniger strukturierte Daten in Solr anwenden. Hier ein Beispiel für die Verwendung einer UDF zur Aggregation nach Tagen (ähnlich dem Abrundungsoperator von Solr):
select count(*) as num_per_day, date_format(rating_timestamp,"yyyy-MM-dd") as date_fmt from ratings group by date_format(rating_timestamp,"yyyy-MM-dd") order by num_per_day desc LIMIT 10
Strategie 10: Erstellen Sie temporäre Tabellen mit SQL
Mit der Catalog-API können Sie jede SQL-Abfrage als temporäre Tabelle zwischenspeichern, wie oben in Strategie 8 beschrieben. Wenn Ihre einzige Schnittstelle SQL / JDBC ist, dann können Sie eine temporäre Tabelle mit der folgenden Syntax definieren:
CREATE TEMPORARY TABLE my_view_of_solr USING solr OPTIONS ( collection 'ratings', expr 'search(ratings, q="*:*", fl="movie_id,user_id,rating", sort="movie_id asc", qt="/export")' )
Die aufgeführten Optionen entsprechen den unterstützten Spark-Solr-Optionen.
Nachbereitung
Ich hoffe, dass Ihnen dies einige Anregungen gibt, wie Sie die SQL-Engine in Fusion nutzen können, um Ihre Datensätze zu analysieren und zu visualisieren. Zusammenfassend lässt sich sagen, dass die SQL-Engine von Fusion alle Ihre Fusion-Sammlungen als Tabellen bereitstellt, die mit SQL über JDBC abgefragt werden können. In Kombination mit den mehr als 60 Konnektoren und den leistungsstarken ETL-Parsern und -Pipelines von Fusion können Geschäftsanalysten geschäftskritische Datensätze mit den BI-Tools ihrer Wahl schnell analysieren – mit einer SQL-Engine, die von massiv skalierbaren Open-Source-Technologien wie Apache Spark und Apache Solr unterstützt wird. Im Gegensatz zu anderen SQL-Datenquellen bietet Fusion Suchmaschinenfunktionen wie die Relevanzeinstufung von Dokumenten, unscharfe Freitextsuche, Graphenüberquerungen über Beziehungen und die fliegende Anwendung von Klassifizierungs- und maschinellen Lernmodellen auf eine Vielzahl von Datenquellen und Datentypen, einschließlich Freitext, numerische, räumliche und benutzerdefinierte Typen.