SAP HANA Cloud 学习教程之二： 如何往SAP BTP 上 HANA Cloud 实例的数据库表里插入数据

这个教程的前一篇文章，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, 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 的开发者：

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_GRAPH
  edge table LEARNING_RELATION
    source column SOURCE
    target column TARGET
    key column ID
  vertex table DEVS
    key 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.