SQL 查詢語言

Solr SQL 模組透過將 SQL 與 Solr 的全文檢索功能無縫結合，將 SQL 查詢的功能帶入 Solr。支援 MapReduce 樣式和 JSON 分面 API 彙總，這表示 SQL 查詢可用於支援高查詢量和高基數的使用案例。

模組

這是透過 sql Solr 模組提供的，使用前需要啟用。

SQL 架構

SQL 介面允許將 SQL 查詢傳送至 Solr，並以串流方式傳回文件作為回應。在底層，Solr 的 SQL 介面使用 Apache Calcite SQL 引擎將 SQL 查詢轉換為實體查詢計畫，這些計畫實作為串流表示式。

有關 Solr 如何支援 Solr 的 SQL 查詢的更多資訊，請參閱下方的設定章節。

Solr 集合和資料庫表格

在標準的 SELECT 陳述式（例如 SELECT <expressions> FROM <table>）中，表格名稱對應於 Solr 集合名稱。表格名稱不區分大小寫。

SQL 查詢中的欄位名稱會直接對應到正在查詢的集合之 Solr 索引中的欄位。這些識別碼區分大小寫。支援別名，並且可以在 ORDER BY 子句中參考。

僅當查詢具有 LIMIT 子句時，才支援使用 SELECT * 語法來表示所有欄位。只有包含 LIMIT 子句的查詢才能使用 score 欄位。

例如，我們可以索引 Solr 的範例文件，然後建構如下的 SQL 查詢

SELECT name_exact as name, manu as mfr, price as retail FROM techproducts ORDER BY retail DESC

我們在 Solr 中使用的集合是 "techproducts"，我們要求傳回 "name_exact"、manu" 和 "price" 欄位，並依照別名 "retail" 排序，以顯示價格由高到低的產品。

Solr SQL 語法

Solr 支援廣泛的 SQL 語法。

SQL 剖析器不區分大小寫

Solr 用來轉換 SQL 陳述式的 SQL 剖析器不區分大小寫。但是，為了方便閱讀，此頁面上的所有範例都使用大寫關鍵字。
僅在使用 LIMIT 時才支援 SELECT *

一般來說，您應該明確指定每個查詢需要返回的欄位，並避免使用 select *。這通常被認為是不良做法，且當底層 Solr 綱要變更時，可能會導致意料之外的結果。不過，Solr 確實支援在包含 LIMIT 子句的查詢中使用 select *。所有儲存的欄位和 score 都會包含在結果中。如果沒有 LIMIT 子句，則只會返回啟用 docValues 的欄位。

跳脫保留字

如果 SQL 查詢中使用了保留字，SQL 解析器會傳回錯誤。可以使用反引號跳脫保留字，並將其包含在查詢中。例如：

select `from` from emails

SELECT 陳述式

Solr 支援有限制和無限制的 select 查詢。這兩種查詢類型的語法相同，唯一的區別在於 SQL 陳述式中的 LIMIT 子句。但是，它們有非常不同的執行計畫和不同的資料儲存需求。以下章節將探討這兩種查詢類型。

具有 LIMIT 的基本 SELECT 陳述式

有限制的 select 查詢遵循此基本語法：

SELECT fieldA as fa, fieldB as fb, fieldC as fc FROM tableA WHERE fieldC = 'term1 term2' ORDER BY fa desc LIMIT 100

我們已經在本範例中介紹了許多語法選項，讓我們來逐步了解以下可能的操作。

WHERE 子句和布林述詞

WHERE 子句的述詞一側必須有欄位。不支援兩個常數 (5 < 10) 或兩個欄位 (fielda > fieldb)。也不支援子查詢。

WHERE 子句允許將 Solr 的搜尋語法注入到 SQL 查詢中。在範例中：

WHERE fieldC = 'term1 term2'

上面的述詞將在 fieldC 中執行「term1 term2」片語的全文搜尋。

若要執行非片語查詢，只需在單引號內加入括號。例如：

WHERE fieldC = '(term1 term2)'

上面的述詞會在 fieldC 中搜尋 term1 或 term2。

Solr 範圍查詢語法可以使用如下方式：

WHERE fieldC = '[0 TO 100]'

可以按如下方式指定複雜的布林查詢：

WHERE ((fieldC = 'term1' AND fieldA = 'term2') OR (fieldB = 'term3'))

若要指定 NOT 查詢，您可以使用 AND NOT 語法，如下所示：

WHERE (fieldA = 'term1') AND NOT (fieldB = 'term2')

支援的 WHERE 運算子

SQL 查詢介面支援並向下推送大多數常見的 SQL 運算子，特別是：

運算子 描述 範例 Solr 查詢

=

等於

fielda = 10

fielda:10

<>

不等於

fielda <> 10

-fielda:10

>

大於

fielda > 10

fielda:{10 TO *]

>=

大於或等於

fielda >= 10

fielda:[10 TO *]

<

小於

fielda < 10

fielda:[* TO 10}

<=

小於或等於

fielda <= 10

fielda:[* TO 10]

IN

指定多個值（多個 OR 子句的簡寫）

fielda IN (10,20,30)

(fielda:10 OR fielda:20 OR fielda:30)

LIKE

字串或文字欄位的萬用字元比對

fielda LIKE 'day%'

{!complexphrase}fielda:"day*"

BETWEEN

範圍比對

fielda BETWEEN 2 AND 4

fielda: [2 TO 4]

IS NULL

比對具有 null 值的欄

fielda IS NULL

(*:* -field:*)

IS NOT NULL

比對具有值的欄

fielda IS NOT NULL

field:*

  • 針對不等於，使用 <> 而不是 !=

  • IN、LIKE、BETWEEN 支援 NOT 關鍵字，用於尋找條件不成立的列，例如 fielda NOT LIKE 'day%'

  • 字串常值必須以單引號括住；雙引號表示資料庫物件，而不是字串常值。

  • 簡單的 LIKE 可以搭配星號萬用字元使用，例如 field = 'sam*'；這是 Solr 特有的，而非 SQL 標準的一部分。

  • IN 子句的最大值數量受限於為您的集合設定的 maxBooleanClauses。

  • 當對多值欄位執行 AND 範圍查詢時，如果 AND 述詞看起來是不相交的集合，Apache Calcite 會短路並傳回零結果。例如，b_is <= 2 AND b_is >= 5 從單值欄位的角度來看，Calcite 認為這是不相交的集合。但是，多值欄位的情況可能並非如此，因為 Solr 可能會比對文件。變通方法是在括號中包覆的等於運算式內直接使用 Solr 查詢語法：b_is = '(+[5 TO *] +[* TO 2])'

ORDER BY 子句

ORDER BY 子句直接對應到 Solr 欄位。支援多個 ORDER BY 欄位和方向。

在指定了限制的查詢中，score 欄位會接受於 ORDER BY 子句中。

如果 ORDER BY 子句包含 GROUP BY 子句中的完全相同的欄位，則不會對傳回的結果設限。如果 ORDER BY 子句包含與 GROUP BY 子句不同的欄位，則會自動套用 100 的限制。若要增加此限制，您必須在 LIMIT 子句中指定一個值。

Order by 欄位區分大小寫。

具有 FETCH 的 OFFSET

指定 ORDER BY 子句的查詢也可以使用 OFFSET (從 0 開始的索引) 和 FETCH 運算子來瀏覽結果；不支援沒有 FETCH 的 OFFSET，並會產生例外狀況。例如，以下查詢會請求 10 個結果的第二頁：

ORDER BY ... OFFSET 10 FETCH NEXT 10 ROWS ONLY

使用 SQL 分頁時，會遇到與在 Solr 查詢中使用 start 和 rows 分頁相同的效能損失。其中分散式查詢必須從每個分片過度提取 OFFSET + LIMIT 文件，然後對每個分片的結果進行排序，以產生傳回給用戶端的結果頁面。因此，此功能僅應用於較小的 OFFSET/FETCH 大小，例如每個分片最多分頁 10,000 個文件。Solr SQL 不會強制執行任何硬性限制，但是您深入結果的程度越高，後續每個頁面請求需要花費的時間就越長，並消耗更多資源。Solr 的深層分頁 cursorMark 功能在 SQL 中不支援；請改用沒有 LIMIT 的 SQL 查詢，以透過 /export 處理常式串流大型結果集。SQL OFFSET 不適用於深層分頁類型的使用案例。

LIMIT 子句

將結果集限制為指定的大小。在上述範例中，LIMIT 100 子句會將結果集限制為 100 筆記錄。

有限制和無限制查詢之間有一些差異需要注意：

  • 有限制查詢支援欄位清單中的 score 和 ORDER BY。無限制查詢則不支援。

  • 有限制查詢允許欄位清單中的任何儲存欄位。無限制查詢要求欄位儲存為 DocValues 欄位。

  • 有限制查詢允許 ORDER BY 清單中的任何索引欄位。無限制查詢要求欄位儲存為 DocValues 欄位。

  • 如果欄位已編製索引但未儲存或沒有 docValues，您可以根據該欄位進行篩選，但無法在結果中傳回該欄位。

SELECT DISTINCT 查詢

SQL 介面支援 MapReduce 和 Facet 實作，用於 SELECT DISTINCT 查詢。

MapReduce 實作會將元組隨機發送到執行 Distinct 運算的 Worker 節點。此實作可以對極高基數的欄位執行 Distinct 運算。

Facet 實作會使用 JSON Facet API 將 Distinct 運算向下推送至搜尋引擎。此實作專為低到中度基數欄位的高效能、高 QPS 情境而設計。

JDBC 驅動程式和 HTTP 介面中都有 aggregationMode 參數可用，可選擇基礎實作 (map_reduce 或 facet)。兩種實作的 SQL 語法相同：

SELECT distinct fieldA as fa, fieldB as fb FROM tableA ORDER BY fa desc, fb desc

統計函式

SQL 介面支援對數值欄位計算的簡單統計資訊。支援的函式包括 COUNT(*)、COUNT(DISTINCT field)、APPROX_COUNT_DISTINCT(field)、MIN、MAX、SUM 和 AVG。

由於這些函式永遠不需要隨機發送資料，因此會將匯總向下推送至搜尋引擎，並由 Stats Component 產生。

SELECT COUNT(*) as count, SUM(fieldB) as sum FROM tableA WHERE fieldC = 'Hello'

APPROX_COUNT_DISTINCT 度量會使用 Solr 的 HyperLogLog (hll) 統計函式來計算給定欄位的近似基數，當查詢效能很重要且不需要精確計數時，應該使用此度量。

GROUP BY 匯總

SQL 介面也支援 GROUP BY 匯總查詢。

如同 SELECT DISTINCT 查詢，SQL 介面同時支援 MapReduce 實作和 Facet 實作。MapReduce 實作可以在極高基數的欄位上建構匯總。Facet 實作可以在基數中等程度的欄位上提供高效能的匯總。

具有匯總的基本 GROUP BY

以下是請求匯總的 GROUP BY 查詢的基本範例：

  SELECT fieldA as fa, fieldB as fb, COUNT(*) as count, SUM(fieldC) as sum, AVG(fieldY) as avg
    FROM tableA
   WHERE fieldC = 'term1 term2'
GROUP BY fa, fb
  HAVING sum > 1000
ORDER BY sum asc
   LIMIT 100

讓我們將其分解成幾個部分：

欄識別碼和別名

欄識別碼可以同時包含 Solr 索引中的欄位和匯總函式。支援的匯總函式包括：

  • COUNT(*)：計算一組儲存區中的記錄數。

  • SUM(field)：加總一組儲存區中的數值欄位。

  • AVG(field)：計算一組儲存區中的數值欄位的平均值。

  • MIN(field)：傳回一組儲存區中的數值欄位的最小值。

  • MAX(field)：傳回一組儲存區中的數值欄位的最大值。

欄位清單中的非函式欄位會決定計算匯總的欄位。

目前 Solr 不支援使用 COUNT(DISTINCT <field>) 計算每個群組中特定欄位的相異值數量；每個 GROUP BY 維度只能計算 COUNT(*)。

HAVING 子句

HAVING 子句可能包含欄位清單中列出的任何函式。支援像這樣的複雜 HAVING 子句：

  SELECT fieldA, fieldB, COUNT(*), SUM(fieldC), AVG(fieldY)
    FROM tableA
   WHERE fieldC = 'term1 term2'
GROUP BY fieldA, fieldB
  HAVING ((SUM(fieldC) > 1000) AND (AVG(fieldY) <= 10))
ORDER BY SUM(fieldC) ASC
   LIMIT 100

匯總模式

Solr 的 SQL 功能可以透過兩種方式使用匯總 (結果分組)：

  • facet：這是預設的聚合模式，它使用 JSON Facet API 或 StatsComponent 進行聚合。在這種情況下，聚合邏輯會下推到搜尋引擎中，只有聚合結果會透過網路傳送。這是 Solr 的正常運作模式。當 GROUP BY 欄位的基數低到中等時，這種模式速度很快。但是，當 GROUP BY 欄位中存在高基數欄位時，就會失效。

  • map_reduce：此實作會將元組洗牌到工作節點，並在工作節點上執行聚合。它涉及排序和分割整個結果集，並將其傳送到工作節點。在這種方法中，元組會依據 GROUP BY 欄位排序後到達工作節點。然後，工作節點可以一次彙總一個群組的聚合。這允許無限基數的聚合，但您需要付出將整個結果集透過網路傳送到工作節點的代價。

這些模式在將請求傳送到 Solr 時，會使用 aggregationMode 屬性來定義。

聚合模式之間的選擇取決於您所使用欄位的基數。如果您要分組的欄位中具有低到中等的基數，則 'facet' 聚合模式會提供更高的效能，因為只會傳回最終的群組，非常類似於目前 facet 的運作方式。但是，如果欄位中具有高基數，則具有工作節點的 "map_reduce" 聚合模式會提供效能更高的選項。

設定

用於 SQL 介面的請求處理程式設定為隱式載入，這表示要開始使用此功能幾乎不需要做任何事情。

/sql 請求處理程式

/sql 處理程式是平行 SQL 介面的前端。所有 SQL 查詢都會傳送到 /sql 處理程式進行處理。當在 map_reduce 模式下執行 GROUP BY 和 SELECT DISTINCT 查詢時，此處理程式還會協調分散式的 MapReduce 工作。預設情況下，/sql 處理程式會從其自己的集合中選擇工作節點來處理分散式作業。在這種預設情況下，/sql 處理程式所在的集合會作為 MapReduce 查詢的預設工作集合。

預設情況下，/sql 請求處理程式會設定為隱式處理程式，這表示它在每個 Solr 安裝中都會始終啟用，並且不需要進一步的設定。

SQL 請求的授權

如果您的 Solr 叢集已設定為使用基於規則的授權外掛程式，則您需要在您打算執行 SQL 查詢的所有集合的 /sql、/select 和 /export 端點上授予 GET 和 POST 權限。/select 端點用於 LIMIT 查詢，而 /export 處理程式用於沒有 LIMIT 的查詢，因此在大多數情況下，您會需要授予對兩者的存取權。如果您正在使用 /sql 處理程式的工作集合，則您只需要授予對工作集合的 /sql 端點的存取權，而不需要授予資料層中集合的存取權。在幕後，SQL 處理程式還會使用內部 Solr 伺服器身分向 /admin/luke 端點傳送請求，以取得集合的結構描述中繼資料。因此，您不需要為使用者授予 /admin/luke 端點的明確權限即可執行 SQL 查詢。

如下面最佳實務章節所述，您可能會想要為平行化的 SQL 查詢設定一個獨立的集合。如果您有高基數欄位和大量資料，請務必查看該章節並考慮使用獨立的集合。

/stream 和 /export 請求處理程式

Streaming API 是 SolrCloud 的可擴展平行運算架構。串流運算式為 Streaming API 提供查詢語言和序列化格式。

Streaming API 提供對快速 MapReduce 的支援，使其能夠在極大型資料集上執行平行關聯代數。在底層，SQL 介面會使用 Apache Calcite SQL Parser 解析 SQL 查詢。然後，它會將查詢轉換為平行查詢計畫。平行查詢計畫會使用 Streaming API 和串流運算式來表示。

與 /sql 請求處理程式一樣，/stream 和 /export 請求處理程式設定為隱式處理程式，並且不需要進一步的設定。

欄位

在某些情況下，SQL 查詢中使用的欄位必須設定為 DocValue 欄位。如果查詢沒有限制，則所有欄位都必須是 DocValue 欄位。如果查詢有限制 (使用 limit 子句)，則欄位不需要啟用 DocValues。

多值欄位

專案清單中的多值欄位將會以值的 List 形式傳回；使用 JDBC 時，請使用 getObject(col) 來擷取多值欄位，然後轉換為 List。一般來說，您可以投射、篩選和分組，但您不能依多值欄位排序。

傳送查詢

SQL 介面提供基本的 JDBC 驅動程式和 HTTP 介面來執行查詢。

JDBC 驅動程式

JDBC 驅動程式隨附於 SolrJ。以下是使用 JDBC 驅動程式建立連線和執行查詢的範例程式碼

Connection con = null;
try {
    con = DriverManager.getConnection("jdbc:solr://" + zkHost + "?collection=collection1&aggregationMode=map_reduce&numWorkers=2");
    stmt = con.createStatement();
    rs = stmt.executeQuery("SELECT a_s, sum(a_f) as sum FROM collection1 GROUP BY a_s ORDER BY sum desc");

    while(rs.next()) {
        String a_s = rs.getString("a_s");
        double s = rs.getDouble("sum");
    }
} finally {
    rs.close();
    stmt.close();
    con.close();
}

連線 URL 必須包含 zkHost 和 collection 參數。集合必須是指定 ZooKeeper 主機上的有效 SolrCloud 集合。集合也必須設定 /sql 處理程式。aggregationMode 和 numWorkers 參數是可選的。

HTTP 介面

Solr 接受透過 /sql 處理程式傳送的 SQL 查詢。

以下是在 facet 模式下執行 SQL 聚合查詢的範例 curl 命令

curl --data-urlencode 'stmt=SELECT to, count(*) FROM collection4 GROUP BY to ORDER BY count(*) desc LIMIT 10' https://127.0.0.1:8983/solr/collection4/sql?aggregationMode=facet

以下是範例結果集

{"result-set":{"docs":[
   {"count(*)":9158,"to":"pete.davis@enron.com"},
   {"count(*)":6244,"to":"tana.jones@enron.com"},
   {"count(*)":5874,"to":"jeff.dasovich@enron.com"},
   {"count(*)":5867,"to":"sara.shackleton@enron.com"},
   {"count(*)":5595,"to":"steven.kean@enron.com"},
   {"count(*)":4904,"to":"vkaminski@aol.com"},
   {"count(*)":4622,"to":"mark.taylor@enron.com"},
   {"count(*)":3819,"to":"kay.mann@enron.com"},
   {"count(*)":3678,"to":"richard.shapiro@enron.com"},
   {"count(*)":3653,"to":"kate.symes@enron.com"},
   {"EOF":"true","RESPONSE_TIME":10}]}
}

請注意，結果集是一個元組陣列，其中包含與 SQL 欄位清單相符的鍵/值對。最後一個元組包含 EOF 旗標，表示串流的結束。

最佳實務

獨立的工作集合

最好建立一個獨立的 SolrCloud 工作集合，專門用於 /sql 處理程式。可以使用 SolrCloud 的標準集合 API 來建立此集合。

由於此集合僅用於處理 /sql 請求並提供工作節點的集區，因此此集合不需要保留任何資料。工作節點會從 /sql 處理程式的集合中所有可用的節點隨機選取。因此，若要動態擴充此集合，可以將複本新增到現有的分片。新增後，新的複本將會自動投入工作。

平行 SQL 查詢

稍早的章節描述了 SQL 介面如何將 SQL 陳述式轉換為串流運算式。請求的其中一個參數是 aggregationMode，它定義查詢是否應該使用類似 MapReduce 的洗牌技術，還是將運算下推到搜尋引擎中。

平行化查詢

平行 SQL 架構包含三個邏輯層：SQL 層、工作層和資料表層。預設情況下，SQL 和工作層會摺疊到相同的實體 SolrCloud 集合中。

SQL 層

SQL 層是 /sql 處理程式所在的位置。/sql 處理程式會取得 SQL 查詢並將其轉換為平行查詢計畫。然後，它會選擇工作節點來執行計畫，並將查詢計畫傳送到每個工作節點以平行執行。

工作節點執行查詢計畫後，/sql 處理程式會執行工作節點傳回的元組的最終合併。

工作層

工作層中的工作節點會從 /sql 處理程式接收查詢計畫，並執行平行查詢計畫。平行執行計畫包括需要在資料表層上執行的查詢，以及滿足查詢所需的關聯代數。指派給查詢的每個工作節點都會將資料表中的元組洗牌 1/N。工作節點會執行查詢計畫並將元組串流回工作節點。

資料表層

資料表層是資料表所在的位置。每個資料表都是其自己的 SolrCloud 集合。資料表層會從工作節點接收查詢並發出元組 (搜尋結果)。資料表層還會處理傳送到工作節點的元組的初始排序和分割。這表示元組在進入網路之前會始終排序和分割。分割的元組會以正確的排序順序直接傳送到正確的工作節點，準備好進行縮減。

image
圖 1. 平行 SQL 查詢的分散方式

上圖為了清楚起見，將三個層分解為不同的 SolrCloud 集合。實際上，預設情況下，/sql 處理程式和工作集合會共用相同的集合。

此圖顯示單一平行 SQL 查詢 (MapReduce 上的 SQL) 的網路流程。當 map_reduce 聚合模式用於 GROUP BY 聚合或 SELECT DISTINCT 查詢時，會使用此網路流程。當使用 facet 聚合模式時，會使用傳統的 SolrCloud 網路流程 (沒有工作節點)。

以下是流程的描述

  1. 用戶端會將 SQL 查詢傳送到 /sql 處理程式。請求由單一 /sql 處理程式執行個體處理。

  2. /sql 處理程式會解析 SQL 查詢並建立平行查詢計畫。

  3. 查詢計畫會傳送到工作節點 (綠色)。

  4. 工作節點會平行執行計畫。圖表顯示每個工作節點都會連線到資料表層中的集合 (藍色)。

  5. 資料表層中的集合是 SQL 查詢中的資料表。請注意，集合有五個分片，每個分片有 3 個複本。

  6. 請注意，每個工作節點都會從每個分片連線一個複本。由於有 5 個工作節點，因此每個工作節點都會從每個分片傳回 1/5 的搜尋結果。分割是在資料表層內部完成的，因此在網路中不會重複資料。

  7. 另請注意，在此設計中，資料層中的所有複本都會同時洗牌 (排序和分割) 資料。隨著分片、複本和工作節點數量的增加，此設計允許將大量的運算能力應用於單一查詢。

  8. 工作節點會平行處理從資料表層傳回的元組。工作節點會執行滿足查詢計畫所需的關聯代數。

  9. 工作節點會將元組串流回 /sql 處理程式，在該處理程式中完成最終合併，最後將元組串流回用戶端。

SQL 用戶端和資料庫視覺化工具

SQL 介面支援從 SQL 用戶端和資料庫視覺化工具傳送的查詢。

本指南包含設定以下工具和用戶端的說明文件

通用用戶端

對於大多數基於 Java 的用戶端，需要將以下 jar 檔案放在用戶端類別路徑中

  • SolrJ 相依性 .jar 檔案位於 $SOLR_TIP/server/solr-webapp/webapp/WEB-INF/lib/* 和 $SOLR_TIP/server/lib/ext/*。在 Solr 發行版本中，這些相依性並未與 Solr 本身的相依性分開，因此您必須包含所有檔案，或手動選擇所需的確切集合。請參考 Maven 發行版本，以獲取您所使用版本所需的確切相依性。

  • SolrJ .jar 檔案位於 $SOLR_TIP/server/solr-webapp/webapp/WEB-INF/lib/solr-solrj-<version>.jar

如果您使用 Maven，則 org.apache.solr.solr-solrj 成品包含所需的 jar 檔案。

一旦這些 jar 檔案在類別路徑中可用，Solr JDBC 驅動程式名稱即為 org.apache.solr.client.solrj.io.sql.DriverImpl，並且可以使用以下連接字串格式建立連線

jdbc:solr://SOLR_ZK_CONNECTION_STRING?collection=COLLECTION_NAME

還有其他參數可以選擇性地添加到連接字串中，例如 aggregationMode 和 numWorkers。