這個(gè)教程的前一篇文章，SAP HANA Cloud 學(xué)習(xí)教程之一： 如何在 SAP BTP 上創(chuàng)建 SAP HANA Cloud 實(shí)例，我們已經(jīng)成功在 SAP BTP HANA Cloud Service 上創(chuàng)建了一個(gè)數(shù)據(jù)庫表。

本文介紹如何往這個(gè)數(shù)據(jù)庫表里插入數(shù)據(jù)。

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

有時(shí)會(huì)遇到 Warning：Invalid Request Error 的警告信息，此時(shí) retry 即可。

注意：SAP Business Technology Platform trial 賬號(hào)，其下創(chuàng)建的 HANA Cloud Service 實(shí)例，每天都會(huì)自動(dòng)被關(guān)閉，需要開發(fā)者手動(dòng)重新開啟。

手動(dòng)開啟后，狀態(tài)處于 Running：

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

之后一切正常了。

打開 SQL console：

將下列 JSON 數(shù)據(jù)插入數(shù)據(jù)庫表：

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 列的表允許您在同一文檔中存儲(chǔ)與同一記錄相關(guān)的所有信息。 這些文檔不像典型的關(guān)系表那樣具有預(yù)定義的格式或字段數(shù)量。

當(dāng)文檔之間的關(guān)系不太相關(guān)并且數(shù)據(jù)結(jié)構(gòu)需要靈活時(shí)，這尤其有用。 例如，可能不會(huì)輸入電話號(hào)碼等字段并且可能根本不會(huì)存儲(chǔ)的用戶帳戶數(shù)據(jù)。 在相同的場(chǎng)景中，用戶記錄之間不需要外鍵和關(guān)系。

這種類型的數(shù)據(jù)庫也稱為 NoSQL，因?yàn)樗鎯?chǔ) Not-only 結(jié)構(gòu)化數(shù)據(jù)。 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'

下面我們?cè)賹W(xué)習(xí)如何使用常規(guī)的 SQL 操作。

新建一個(gè) 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 默認(rèn)創(chuàng)建列式表。 column 關(guān)鍵字是可選的，但在示例中用于提醒 SAP HANA 中基于列的本地表存儲(chǔ)。

點(diǎn)擊 Deploy 按鈕：

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

我們使用如下 SQL 語句，將之前 COMMUNITY 數(shù)據(jù)庫里的 JSON 內(nèi)容，解析出來，插入到該 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 的開發(fā)者：

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

新建一個(gè) LEARNING_FROM 數(shù)據(jù)庫表，源代碼如下：

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 數(shù)據(jù)表的內(nèi)容插入 LEARNING_RELATION:

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

既然我們已經(jīng)有了一張表，其中包含社區(qū)中的學(xué)習(xí)關(guān)系和專家開發(fā)人員，您可以找出這些人之間的關(guān)系。 表示人員網(wǎng)絡(luò)的一種方法是使用圖形數(shù)據(jù)庫。

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

創(chuàng)建一個(gè)新的 graph 模型：

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

查看 graph：

效果如下圖所示：

找出離 Thomas 最近的開發(fā)者：

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的原創(chuàng)文章，盡在：“汪子熙”:

總結(jié)

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

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。