這個教程的前一篇文章，[SAP HANA Cloud 學習教程之一： 如何在 SAP BTP 上建立 SAP HANA Cloud 例項](https://jerry.blog.csdn.net/article/details/119977580)，我們已經成功在 SAP BTP HANA Cloud Service 上建立了一個資料庫表。

本文介紹如何往這個資料庫表裡插入資料。

在 Business Application Studio 裡，點選 Open HDI Container，開啟 SAP HANA Database Explorer for SAP HANA Cockpit：

![](https://img-blog.csdnimg.cn/img_convert/ff53d09e9f1560028401e2617884b525.png)

![](https://img-blog.csdnimg.cn/img_convert/1371660aef6c5fb5a15af61219e286f6.png)

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

![](https://img-blog.csdnimg.cn/img_convert/616a492068b063076531d55fe2015d2a.png)

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

![](https://img-blog.csdnimg.cn/img_convert/b3eb0edfdf2cbbed2fd68424a910e66d.png)

手動開啟後，狀態處於 Running：
![](https://img-blog.csdnimg.cn/img_convert/f83612414b55ff51de505bedf7dbf051.png)

重新重新整理 SAP HANA Database Explorer for SAP HANA Cockpit：

![](https://img-blog.csdnimg.cn/img_convert/4d3f48ea63c5ed981fb9f8eb8fa96d20.png)

之後一切正常了。

開啟 SQL console：
![](https://img-blog.csdnimg.cn/img_convert/0633e7f2c6e182ee615a3e017d6490fc.png)

將下列 JSON 資料插入資料庫表：
```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 )" }');
```

![](https://img-blog.csdnimg.cn/img_convert/73eb0c33c95b304e4e95266994e3b053.png)

在 Messages 皮膚裡看到插入成功的訊息：
![](https://img-blog.csdnimg.cn/img_convert/234978f53812ab076b4aaf72440e0194.png)

帶有 JSON 列的表允許您在同一文件中儲存與同一記錄相關的所有資訊。 這些文件不像典型的關係表那樣具有預定義的格式或欄位數量。

當文件之間的關係不太相關並且資料結構需要靈活時，這尤其有用。 例如，可能不會輸入電話號碼等欄位並且可能根本不會儲存的使用者帳戶資料。 在相同的場景中，使用者記錄之間不需要外來鍵和關係。

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

使用如下 SQL 語句查詢 Maria 所在的辦公室：
```sql
SELECT *
	FROM JSON_TABLE(COMMUNITY.DATA, '$'
	COLUMNS
    (
        LOCATION NVARCHAR(200) PATH '$.office',
        NAME NVARCHAR(200) PATH '$.name'
    )
	) AS JT where NAME = 'Maria'
```

![](https://img-blog.csdnimg.cn/img_convert/17fd26853bf09499b47e3c68732dda6d.png)

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

新建一個 DEVS.hdbtable 檔案：

```sql
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 按鈕：
![](https://img-blog.csdnimg.cn/img_convert/91b73ca6a694e6aa3fa87a9064127d33.png)

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

![](https://img-blog.csdnimg.cn/img_convert/21549a1cddbfbb778ff7facc26fb27e2.png)

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

```sql
insert into DEVS
SELECT NAME, LEARNS_FROM, HINT, OFFICE, GEOLOCATION
	FROM 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 的開發者：

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

![](https://img-blog.csdnimg.cn/img_convert/9cea80131f18226afadab11954db84ae.png)

新建一個 LEARNING_FROM 資料庫表，原始碼如下：

```sql
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：

![](https://img-blog.csdnimg.cn/img_convert/58eba8ef568d8677e4edcbc02f570f9a.png)

將 COMMUNITY 和 DEVS 資料表的內容插入 LEARNING_RELATION:

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

![](https://img-blog.csdnimg.cn/img_convert/b9d9a9c7d7e7ab1dd01b154eabf2c878.png)

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

在 SAP HANA 中，圖由頂點（在本例中為開發人員）和邊（它們之間的連線，取自欄位 LEARNS_FROM）表示。
![](https://img-blog.csdnimg.cn/img_convert/d94b93188d4977ac6e7105d24e023be8.png)

建立一個新的 graph 模型：
```sql
graph workspace HANA_GRAPH
  edge table LEARNING_RELATION
    source column SOURCE
    target column TARGET
    key column ID
  vertex table DEVS
    key column DEVNAME
```

![](https://img-blog.csdnimg.cn/img_convert/64a9d0038d1f8a84d1e4fbe6b9fc936d.png)

檢視 graph：
![](https://img-blog.csdnimg.cn/img_convert/b99847d3fe4021821a1be3319abc7df1.png)

效果如下圖所示：
![](https://img-blog.csdnimg.cn/img_convert/1123cbaf5bcd3f7f858858cf2f961d1b.png)

找出離 Thomas 最近的開發者：

```sql
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
```

![](https://img-blog.csdnimg.cn/img_convert/abeec457056548ed03fa2034e9ff94aa.png)

答案是 Sam.

SAP HANA Cloud 學習教程之二：如何往SAP BTP 上 HANA Cloud 資料庫表裡插入資料

相關文章

SAP HANA Cloud 學習教程之二： 如何往SAP BTP 上 HANA Cloud 資料庫表裡插入資料

相關文章

SAP HANA Cloud 學習教程之二：如何往SAP BTP 上 HANA Cloud 資料庫表裡插入資料