這個練習會使用SAP HANA Express Edition的文本語義分析引擎對JSON格式的documents進行語義分析。
首先創建一個column table,對其index開啟fuzzy text search(模糊搜索)功能。
上述描述的操作可以用下面的SQL語句來完成:
create column table food_analysis
(
name nvarchar(64),
description text FAST PREPROCESS ON FUZZY SEARCH INDEX ON
);
其中description字段開啟了模糊搜索功能。
將存儲於名為doc_store的document store collection裡的json key-value鍵值對拷貝到剛剛創建的數據庫表裡:
insert into food_analysis with doc_store as (select "name", "description" from food_collection) select doc_store."name" as name, doc_store."description" as description from doc_store;
執行上述的sql語句,確保數據全部拷貝到數據庫表food_analysis中:
使用下列的sql語句對description字段進行模糊搜索:
select name, score() as similarity, TO_VARCHAR(description) from food_analysis where contains(description, 'nuts', fuzzy(0.5,'textsearch=compare')) order by similarity desc
執行結果:
HANA Express Edition裡的linguistic 文本分析步驟也比較簡單。
首先還是創建一個數據庫表:
create column table food_sentiment ( name nvarchar(64) primary key, description nvarchar(2048) );
將document store裡的json數據拷貝到數據庫表裡:
insert into food_sentiment with doc_store as (select "name", "description" from food_collection) select doc_store."name" as name, doc_store."description" as description from doc_store;
針對description字段創建一個新的index:
CREATE FULLTEXT INDEX FOOD_SENTIMENT_INDEX ON "FOOD_SENTIMENT" ("DESCRIPTION") CONFIGURATION 'GRAMMATICAL_ROLE_ANALYSIS' LANGUAGE DETECTION ('EN') SEARCH ONLY OFF FAST PREPROCESS OFF TEXT MINING OFF TOKEN SEPARATORS '' TEXT ANALYSIS ON;
上述SQL語句會自動創建一個名為$TA_FOOD_SENTIMENT_INDEX的文本分析表:
該表裡的內容:
由此可以發現,之前我們導入到數據庫表裡的英文句子,被HANA text engine拆解成單詞,並且每個單詞的詞性也自動被HANA解析出來了。
閱讀更多 汪子熙的游泳故事 的文章