使用Jerry这篇文章在Google Cloud platform上的Kubernetes集群部署HANA Express里介绍的方法在Google Cloud Platform的Kubernetes cluster上安装SAP HANA Express.

文中介绍了一个yaml文件，里面声明了容器镜像文件store/saplabs/hanaexpress:2.00.033.00.20180925.2.

安装完成后，在启动的pod里有两个容器，分别运行着SQLPad和HANA Express.

SQLPad是一个基于Nodejs开发的直接在浏览器运行SQL查询并对结果进行可视化展示工具。SQLPad支持的数据库非常多，比如：MySQL, Postgres, SQL Server, Vertica, Crate, Presto等。

使用kubectl get services拿到sqlpad的external IP：

在浏览器里输入刚才获得的IP地址，后面加上默认的3000端口，打开sqlpad的web控制台：

注册一个帐户并登录：

选择admin-Connections：

新建一个数据库连接：

database driver从下拉菜单里选择SAP HANA：

回到Google Cloud Platform的cloud shell，使用kubectl get services获得hxe-connect的external IP：

把这个地址填到数据库创建向导里：

创建一个名为quotes的collection并插入一些数据：

create collection quotes;

insert into quotes values ( { "FROM" : 'HOMER', "TO" : 'BART',  "QUOTE" :  'I want to share something with you: The three little sentences that will get you through life. Number 1: Cover for me. Number 2: Oh, good idea, Boss! Number 3: It wai like that when I got here.', "MOES_BAR" : 'Point(  -86.880306 36.508361 )', "QUOTE_ID" : 1  });

insert into quotes values ( { "EPISODE" : 'GRADE SCHOOL CONFIDENTIAL', "FROM" : 'HOMER',   "QUOTE" :  'Wait a minute. Bart''s teacher is named Krabappel? Oh, I''ve been calling her Crandall. Why did not anyone tell me? Ohhh, I have been making an idiot out of myself!', "QUOTE_ID" : 2, "MOES_BAR" : 'Point( 2.161018 41.392641 )' });

insert into quotes values ( { "FROM" : 'HOMER',   "QUOTE" :  'Oh no! What have I done? I smashed open my little boy''s piggy bank, and for what? A few measly cents, not even enough to buy one beer. Weit a minute, lemme count and make sure…not even close.', "MOES_BAR" : 'Point( -122.400690 37.784366 )', "QUOTE_ID" : 3 });

注意这个生成的sql collection并不是数据库表，而是一种document store(noSQL)，实际上只是键值对-key value pair.

下面的SQL语句执行的操作是把document store里的值取出进行分析，将分析结果存放到新创建的column table里：

--Create a columnar table with a text fuzzy search index
create column table quote_analysis
(
	id integer,
	homer_quote text FAST PREPROCESS ON FUZZY SEARCH INDEX ON,
	lon_lat nvarchar(200)

);


-- Copy the quotes form the JSON store to the relational table
insert into quote_analysis
with doc_store as (select quote_id, quote from quotes)
select doc_store.quote_id as id, doc_store.quote as homer_quote, 'Point( 2.151255 41.354159 )'
from doc_store;

使用命令行访问hdbsql：

hdbsql -u SYSTEM -d SYSTEMDB -i 90

输入SYSTEM用户的密码。

在默认的HXE database tenant上启用document store：
ALTER DATABASE HXE ADD ‘docstore’;

使用下面的命令行创建一个document store collection：
CREATE COLLECTION food_collection;

注：hqlsql主要是用于Administration类型的工作使用，开发过程中最好还是用sqlpad等工具连接SAP HANA express.

往该document store里插入数据：

INSERT INTO food_collection VALUES('{ "name" : "Kiwi", "group" : "Fruits", "subgroup" : "Tropical fruits", "nutrients" : { "Carbohydrate" : 20945.0, "Energy" : 176.0, "Fat" : 1955.0, "Fiber" : 1925.0, "Proteins" : 1421.3 }, "description" : "The kiwifruit, often shortened to kiwi in many parts of the world, is the edible berry of a woody vine in the genus Actinidia. The most common cultivar group of kiwifruit (''Hayward'') is oval, about the size of a large hen''s egg (5?8 centimetres in length and 4.5?5.5 centimetres in diameter). It has a fibrous, dull greenish-brown skin and bright green or golden flesh with rows of tiny, black, edible seeds. The fruit has a soft texture and a sweet but unique flavor, and today is a commercial crop in several countries, such as Italy, New Zealand, Chile, Greece and France." }');
INSERT INTO food_collection VALUES('{ "name" : "Onion", "group" : "Vegetables", "subgroup" : "Onion-family vegetables", "nutrients" : { "Carbohydrate" : 18057.407, "Energy" : 345.333, "Fat" : 0.0, "Fiber" : 2296.154, "Proteins" : 2150.741 }, "description" : "Allium haematochiton is a species of wild onion known by the common name redskin onion. It is native to northern Baja California and southern California. It it grows on the slopes of the coastal hills and mountains, such as those of the Peninsular Ranges, Transverse Ranges, and southern California Coast Ranges." }');
INSERT INTO food_collection VALUES('{ "name" : "Garlic", "group" : "Herbs and Spices", "subgroup" : "Herbs", "nutrients" : { "Carbohydrate" : 50898.333, "Energy" : 770.333, "Fat" : 0.0, "Fiber" : 3850.000, "Proteins" : 8301.667 }, "description" : "With a history of human use of over 7,000 years, garlic (Allium sativum) is native to central Asia, and has long been a staple in the Mediterranean region, as well as a frequent seasoning in Asia, Africa, and Europe. There are two sub-varieties of allium sativum: Allium sativum var. sativum (soft-necked garlic) and Allium sativum var. ophioscorodon (hard-necked garlic). Soft-necked garlic is the most commonly found. Bulb garlic is available in many forms, including fresh, frozen, dried, fermented (black garlic) and shelf stable products (in tubes or jars)." }');
INSERT INTO food_collection VALUES('{ "name" : "Cashew nut", "group" : "Nuts", "subgroup" : "Nuts", "nutrients" : { "Carbohydrate" : 33540.993, "Energy" : 1622.625, "Fat" : 15466.667, "Fiber" : 2381.818, "Proteins" : 13402.857 }, "description" : "The cashew, or caju, is a small tree native to Brazil. Cashew nuts are the seeds of the cashew apple, and are found in the grey bump at the end of the fruit. The cashew apple itself is sweet, soft and juicy.Cashews are usually sold roasted and salted and eaten as a snack." }');
INSERT INTO food_collection VALUES('{ "name" : "Pineapple", "group" : "Fruits", "subgroup" : "Tropical fruits", "nutrients" : { "Carbohydrate" : 17650.476, "Energy" : 179.613, "Fat" : 2188.600, "Fiber" : 705.263, "Proteins" : 542.857 }, "description" : "The Pineapple (Ananas comosus), named for its resemblance to the pine cone, is a tropical plant with edible multiple fruit consisting of coalesced berries, and the most economically significant plant in the Bromeliaceae family. Pineapples may be cultivated from a crown cutting of the fruit, possibly flowering in 20–24 months and fruiting in the following six months. Pineapple does not ripen significantly post-harvest. Pineapples are consumed fresh, cooked, juiced, and preserved, and are found in a wide array of cuisines. In addition to consumption, in the Philippines the pineapple''s leaves are used to produce the textile fiber piña- employed as a component of wall paper and furnishings, amongst other uses." }');
INSERT INTO food_collection VALUES('{ "name" : "Peanut", "group" : "Nuts", "subgroup" : "Nuts", "nutrients" : { "Carbohydrate" : 19706.341, "Energy" : 1571.563, "Fat" : 14543.900, "Fiber" : 6891.892, "Proteins" : 22098.293 }, "description" : "The peanut, also known as the groundnut and the goober and taxonomically classified as Arachis hypogaea, is a legume crop grown mainly for its edible seeds. It is widely grown in the tropics and subtropics, being important to both small and large commercial producers. It is classified as both a grain legume and, because of its high oil content, an oil crop. World annual production of shelled peanuts was 42 million tonnes in 2014. Atypically among crop plants, peanut pods develop underground rather than aboveground. It is this characteristic that the botanist Linnaeus used to assign the specific name hypogaea, which means ''under the earth''." }');
INSERT INTO food_collection VALUES('{ "name" : "Horseradish", "group" : "Herbs and Spices", "subgroup" : "Spices", "nutrients" : { "Carbohydrate" : 22922.500, "Energy" : 229.000, "Fat" : 700.000, "Fiber" : 3600.000, "Proteins" : 4557.500 }, "description" : "Horseradish (Armoracia rusticana, syn. Cochlearia armoracia) is a perennial plant of the Brassicaceae family (which also includes mustard, wasabi, broccoli, and cabbage). The plant is probably native to southeastern Europe and western Asia. It is now popular around the world. It grows up to 1.5 metres (5 feet) tall, and is cultivated primarily for its large, white, tapered root. The intact horseradish root has hardly any aroma. When cut or grated, however, enzymes from the now-broken plant cells break down sinigrin to produce allyl isothiocyanate, which irritates the mucous membranes of the sinuses and eyes. Once exposed to air (via grating) or heat, if not used immediately or mixed in vinegar, the grated mash darkens, loses its pungency, and becomes unpleasantly bitter-tasting." }');
INSERT INTO food_collection VALUES('{ "name" : "Asparagus", "group" : "Vegetables", "subgroup" : "Shoot vegetables", "nutrients" : { "Carbohydrate" : 4725.000, "Energy" : 58.364, "Fat" : 2150.000, "Fiber" : 1276.471, "Proteins" : 2872.889 }, "description" : "Asparagus officinalis is a spring vegetable, a flowering perennial plant species in the genus Asparagus. It was once classified in the lily family, like its Allium cousins, onions and garlic, but the Liliaceae have been split and the onion-like plants are now in the family Amaryllidaceae and asparagus in the Asparagaceae. Asparagus officinalis is native to most of Europe, northern Africa and western Asia, and is widely cultivated as a vegetable crop." }');
INSERT INTO food_collection VALUES('{ "name" : "Brazil nut", "group" : "Nuts", "subgroup" : "Nuts", "nutrients" : { "Carbohydrate" : 11897.863, "Energy" : 2082.000, "Fat" : 67459.500, "Fiber" : 4266.667, "Proteins" : 11679.000 }, "description" : "The Brazil nut (Bertholletia excelsa) is a South American tree in the family Lecythidaceae, and also the name of the tree''s commercially harvested edible seed." }');
INSERT INTO food_collection VALUES('{ "name" : "Common beet", "group" : "Vegetables", "subgroup" : "Leaf vegetables", "nutrients" : { "Carbohydrate" : 9100.909, "Energy" : 102.500, "Fat" : 0.0, "Fiber" : 2327.273, "Proteins" : 1490.000 }, "description" : "The beet (Beta vulgaris) is a plant in the Chenopodiaceae family which is now included in Amaranthaceae family. It is best known in its numerous cultivated varieties, the most well known of which is the root vegetable known as the beetroot or garden beet. However, other cultivated varieties include the leaf vegetable chard, as well as the root vegetable sugar beet, which is important in the production of table sugar, and mangelwurzel, which is a fodder crop. Three subspecies are typically recognised. All cultivated varieties fall into the subspecies Beta vulgaris subsp. vulgaris, while Beta vulgaris subsp. maritima, commonly known as the sea beet, is the wild ancestor of these, and is found throughout the Mediterranean, the Atlantic coast of Europe, the Near East, and India. A second wild subspecies, Beta vulgaris subsp. adanensis, occurs from Greece to Syria. The roots are most commonly deep red-purple in color, but come in a wide variety of other shades, including golden yellow and red-and-white striped. Beta vulgaris is a herbaceous biennial or, rarely, perennial plant with leafy stems growing to 1–2 m tall. The leaves are heart-shaped, 5–20 cm long on wild plants (often much larger in cultivated plants). The flowers are produced in dense spikes; each flower is very small, 3–5 mm diameter, green or tinged reddish, with five petals; they are wind pollinated. The fruit is a cluster of hard nutlets." }');

上述插入的数据可以用SELECT 查询出来：
SELECT “name”, “group”, “subgroup”, “nutrients”, “description” FROM food_collection;

指定where条件以获得更小的数据集：

SELECT "name", "group", "subgroup", "nutrients", "description" FROM food_collection WHERE "group" = 'Vegetables';

其实我们使用的是SAP针对SAP HANA Express Editor发布的nodejs SDK.

npm init创建一个新的nodejs应用：

执行下列命令行，从SAP的nodejs registry下载hana client：
npm config set @sap:registry https://npm.sap.com
npm install @sap/hana-client

安装完毕：

使用require导入sap hana client,在nodejs代码里使用其对HANA Express发起查询：

const hanaClient = require("@sap/hana-client");

const connection = hanaClient.createConnection();

const connectionParams = {
    host : "hxehost",
    port : 39013,
    uid  : "SYSTEM",
    pwd  : "********",
    databaseName : "HXE"
}

connection.connect(connectionParams, (err) => {
    if (err) {
        return console.error("Connection error", err);
    }

    const whereClause = process.argv[2] ? `WHERE "group" = '${process.argv[2]}'` : "";
    const sql         = `SELECT "name" FROM food_collection ${whereClause}`;

    connection.exec(sql, (err, rows) => {
        connection.disconnect();

        if (err) {
            return console.error('SQL execute error:', err);
        }

        console.log("Results:", rows);
        console.log(`Query '${sql}' returned ${rows.length} items`);
    });
});

执行该应用，返回从SAP HANA Express查询的结果：