這個教程的前一篇文章，SAP HANA Cloud 學習教程之一： 如何在 SAP BTP 上創建 SAP HANA Cloud 實例，我們已經成功在 SAP BTP HANA Cloud Service 上創建了一個數據庫表。

本文介紹如何往這個數據庫表里插入數據。

在 Business Application Studio 里，點擊 Open HDI Container，打開 SAP HANA Database Explorer for SAP HANA Cockpit：

有時會遇到 Warning：Invalid Request Error 的警告信息，此時 retry 即可。

注意：SAP Business Technology Platform trial 賬號，其下創建的 HANA Cloud Service 實例，每天都會自動被關閉，需要開發者手動重新開啟。

手動開啟后，狀態處于 Running：

重新刷新 SAP HANA Database Explorer for SAP HANA Cockpit：

之后一切正常了。

打開 SQL console：

將下列 JSON 數據插入數據庫表：

insert into "COMMUNITY" (DATA) values('{"name" : "Sol" , "hint" :"I love using SAP HANA to develop applications", "learns_from" :"Sam", "office" :"Toronto", "tenure" :17, "geolocation" : "Point( -79.380186 43.647944 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Sam", "hint" :"I like developing in different languages and SQLScript", "learns_from" :"Sol", "office" :"Walldorf", "tenure" :3, "geolocation" : "Point( 8.636789 49.29487 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Jose", "hint" :"I use SAP Cloud platform to deploy cloud-native applications", "learns_from" :"Sol", "office" :"Palo Alto", "tenure" :5, "geolocation" : "Point( -122.146603 37.398989 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Charlotte", "hint" :"Developing apps with SAP HANA has been a game changer. I used to need several databases, now I only need one", "learns_from" :"Sam", "office" :"Australia", "tenure" :6, "geolocation" : "Point( 151.209092 -33.834509 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Maria", "hint" :"I am a coder. In my country, we say developing with SAP HANA is muito legal", "learns_from" :"Charlotte", "office" :"Sao Leopoldo", "tenure" :3, "geolocation" : "Point( -51.148393 -29.796256 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Wei", "hint" :"System administrator here, excited to learn you technologies", "learns_from" :"Sam", "office" :"Beijing", "tenure" :12, "geolocation" : "Point( 121.601862 31.20235 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Hiroshi", "hint" :"I developed many applications with both HANA and SQL Anywhere. I like both", "learns_from" :"Sol", "office" :"Fukuoka", "tenure" :8, "geolocation" : "Point( 130.399091 33.592314 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Saanvi", "hint" :"Developing apps from bangalore to the world", "learns_from" :"Sol", "office" :"Bangalore", "tenure" :7, "geolocation" : "Point( 77.637116 12.972402 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Rick", "hint" :"My team plays with databases regularly. HANA is one of the favorites", "learns_from" :"Maria", "office" :"Irving", "tenure" :11, "geolocation" : "Point( -96.938460 32.873744 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Ann", "hint" :"I like meeting other fellow coders", "learns_from" :"Casey", "office" :"San Ramon", "tenure" :1, "geolocation" : "Point( -121.961661 37.766586 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Hugo", "hint" :"I had never developed such cool apps before", "learns_from" :"Maria", "office" :"Monterrey", "tenure" :2, "geolocation" : "Point( -100.353643 25.64757 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Sofia", "hint" :"I connected SAP Analytics Cloud to HANA", "learns_from" :"Hiroshi", "office" :"Copenhagen", "tenure" :1, "geolocation" : "Point( 12.589387 55.710640 )" }'); insert into "COMMUNITY" (DATA) values('{"name" :"Muhammed", "hint" :"I used to prefer Excel spreadsheets but Lumira changed that for me", "learns_from" :"Charlotte", "office" :"Ra anana", "tenure" :11, "geolocation" : "Point( 34.882402 32.201905 )" }');

在 Messages 面板里看到插入成功的消息：

帶有 JSON 列的表允許您在同一文檔中存儲與同一記錄相關的所有信息。 這些文檔不像典型的關系表那樣具有預定義的格式或字段數量。

當文檔之間的關系不太相關并且數據結構需要靈活時，這尤其有用。 例如，可能不會輸入電話號碼等字段并且可能根本不會存儲的用戶帳戶數據。 在相同的場景中，用戶記錄之間不需要外鍵和關系。

這種類型的數據庫也稱為 NoSQL，因為它存儲 Not-only 結構化數據。 SAP HANA 將 SQL 用于 JSON 列中的 CRUD 操作。

使用如下 SQL 語句查詢 Maria 所在的辦公室：

SELECT *FROM JSON_TABLE(COMMUNITY.DATA, '$'COLUMNS(LOCATION NVARCHAR(200) PATH '$.office',NAME NVARCHAR(200) PATH '$.name')) AS JT where NAME = 'Maria'

下面我們再學習如何使用常規的 SQL 操作。

新建一個 DEVS.hdbtable 文件：

column table "DEVS"("DEVNAME" nvarchar(100) PRIMARY KEY,"LEARNS_FROM" nvarchar(100),"HINT_TEXT" nvarchar(1000),"CITY" nvarchar(100),"LON_LAT" nvarchar(200) )

SAP HANA 默認創建列式表。 column 關鍵字是可選的，但在示例中用于提醒 SAP HANA 中基于列的本地表存儲。

點擊 Deploy 按鈕：

Deploy 成功之后，回到 SAP HANA Database Explorer for SAP HANA Cockpit 之后，能看到剛才創建的 Column table：

我們使用如下 SQL 語句，將之前 COMMUNITY 數據庫里的 JSON 內容，解析出來，插入到該 colum table 中去：

insert into DEVS SELECT NAME, LEARNS_FROM, HINT, OFFICE, GEOLOCATIONFROM JSON_TABLE(COMMUNITY.DATA, '$'COLUMNS(LOCATION NVARCHAR(200) PATH '$.office',NAME NVARCHAR(200) PATH '$.name',LEARNS_FROM NVARCHAR(200) PATH '$.learns_from',HINT NVARCHAR(200) PATH '$.hint',OFFICE NVARCHAR(200) PATH '$.office',GEOLOCATION NVARCHAR(200) PATH '$.geolocation',TENURE NVARCHAR(30) PATH '$.tenure')) AS JT where to_bigint(TENURE) > 1

11行插入成功。

使用如下語句，查詢 hint_text 包含字符串 develop 的開發者：

select DEVNAME, TO_NVARCHAR(HINT_TEXT) as "testimony", LEARNS_FROM from DEVS where contains(HINT_TEXT, '%develop%')

新建一個 LEARNING_FROM 數據庫表，源代碼如下：

column table LEARNING_RELATION(ID int NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 1),SOURCE nvarchar(100) NOT NULL,TARGET nvarchar(100) NOT NULL )

重新部署后，打開 HANA Database Explorer：

將 COMMUNITY 和 DEVS 數據表的內容插入 LEARNING_RELATION:

insert into LEARNING_RELATION (SOURCE, TARGET) select LEARNS_FROM, DEVNAME from DEVS

既然我們已經有了一張表，其中包含社區中的學習關系和專家開發人員，您可以找出這些人之間的關系。 表示人員網絡的一種方法是使用圖形數據庫。

在 SAP HANA 中，圖由頂點（在本例中為開發人員）和邊（它們之間的連接，取自字段 LEARNS_FROM）表示。

創建一個新的 graph 模型：

graph workspace HANA_GRAPHedge table LEARNING_RELATIONsource column SOURCEtarget column TARGETkey column IDvertex table DEVSkey column DEVNAME

查看 graph：

效果如下圖所示：

找出離 Thomas 最近的開發者：

select DEVNAME, round(st_geomFromText('Point( 11.57548 48.13702 )', 4326).st_distance(st_geomFromtext(LON_LAT, 4326), 'kilometer'),0) as DISTANCE_KM from DEVS where contains(HINT_TEXT, '%develop%') order by DISTANCE_KM

答案是 Sam.

更多Jerry的原創文章，盡在：“汪子熙”:

總結

以上是生活随笔為你收集整理的SAP HANA Cloud 学习教程之二： 如何往SAP BTP 上 HANA Cloud 实例的数据库表里插入数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。