TPCDS, and open it when started,tpcds project (aka workspace), e.g. via File -> Open Recent Workspace in the studio.eu10 region again; I like using the SSO option for that withcf login --sso -a https://api.cf.eu10.hana.ondemand.com,vital-hc-hana; and instead of going to the UI, I like using the command line for that withcf update-service vital-hc-hana -c '{"data":{"serviceStopped": false}}'.
db used to build and deploy the SAP HANA database artifacts.mta.yamlmta.yaml file (1).
db (2) that currently requires a single hdi_db resource. This is the target container service.hdi-db (3) is an HDI container that has been created by the wizard as well. The name of the container is not hard-coded but is coming from a variable ${service-name}. In turn, it is used as well as a property in the db module's requirements via a reference ~{hdi-container-name}.mta command-line tool (4).mta resolve -m db -p mta.yaml mta.yaml?db/.env.env file in the db folder.
db/.env (1) contains bound services using CF environment variable VCAP_SERVICES but might be difficult to read as-is (2).jq tool in the previous post, that will help us to read the formatted content of this file (3) coming after the VCAP_SERVICES=.cat db/.env | cut -d "=" -f 2- -z | ~/bin/jqtpcds-hdidb-ws-x4bb9 (5) known to the MTA as hdi_db thanks to the tag mta-resource-name (4)._DT for the design-time user (6), and _RT for the run-time user (7).SharedDevKey, which is used when opening the SAP HANA Database Explorer.
We can get the same content of the service key by executing the following command (make sure to provide your service names): cf service-key tpcds-hdidb-ws-x4bb9 SharedDevKey.SQL icon and the service key's database run-time user (ending with _RT) is authenticated and is used to run queries.SELECT 'Current user' as "Property", Current_User as "Value" FROM DUMMY
UNION ALL
SELECT 'Current schema', Current_Schema FROM DUMMY;

_DT postfix), but the working schema is the same.This is the schema (TPCDS_HDI_DB_1in this example) that the HDI target container in our project (tpcds-hdidb-ws-x4bb9) abstracts and its name can change, so it should not be hard-coded anywhere in your code!
db/node_modules that has only one npm package @sap/hdi-deploy installed now. As per the documentation, it requires a mandatory folder src for HDI deployment definition files and an optional folder cfg.db/.env file, let's use ~bin/jq tool to parse run-time database user credentials and add them to the HANA user store ~/sap/hdbclient/hdbuserstore under the key HDI_DB_RT (1).cat db/.env | cut -d "=" -f 2- -z \
| ~/bin/jq -r -c '.hana[] | select(.tags[]=="mta-resource-name:hdi_db") .credentials | .host +":"+ .port +" "+ .user +" "+ .password' \
| xargs ~/sap/hdbclient/hdbuserstore SET HDI_DB_RT
~/sap/hdbclient/hdbuserstore LIST
~/sap/hdbclient/hdbsql -U HDI_DB_RT -A "SELECT Current_User FROM DUMMY"jq to find the service entry with the tag "mta-resource-name:hdi_db" in db/.env file and construct hostname, port, user and password arguments to be passed to hdbuserstore command as arguments thanks to xargs.
HDI_DB_RT has been added in addition to already existing HANACLOUDTRIAL_DBADMIN and can be used to run queries with hdbsql.~/sap/hdbclient/hdbsql -A -U HDI_DB_RT \
"SELECT SCHEMA_NAME, VIEW_NAME, VIEW_TYPE FROM VIEWS WHERE SCHEMA_NAME NOT LIKE '%SYS%';" 
#DI schema contains objects and data required for the container's management. One specific view that will become more interesting for us is a M_OBJECTS, which shows the database objects in the run-time schema of an HDI container. But it is empty for now as we haven't deployed anything yet...TPCDS classic schema to be used in our calculation views. In Cloud Foundry programming it is possible using instances of User-Provided Services (aka UPS).TPCDS schema and JSON-formatted parameters required by such UPS are described in the "Enable Access to Objects in a Remote Classic Schema" documentation.ups_user_with_roles.sql in a new folder misc in our project...
--DROP USER TPC_USER;
CREATE USER TPC_USER PASSWORD "H3LL0C!oud" NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;
--DROP ROLE SELECT_TPCDS;
CREATE ROLE SELECT_TPCDS;
GRANT SELECT ON SCHEMA "TPCDS" TO SELECT_TPCDS;
GRANT SELECT_TPCDS TO "TPC_USER" WITH ADMIN OPTION;
--DROP ROLE SELECT_TPCDS_WITH_GRANT;
CREATE ROLE SELECT_TPCDS_WITH_GRANT;
GRANT SELECT ON SCHEMA "TPCDS" TO SELECT_TPCDS_WITH_GRANT WITH GRANT OPTION;
GRANT SELECT_TPCDS_WITH_GRANT TO "TPC_USER" WITH ADMIN OPTION;
CONNECT TPC_USER PASSWORD "H3LL0C!oud";
SELECT "TABLE_NAME", "RECORD_COUNT" FROM "M_TABLES" WHERE "SCHEMA_NAME"='TPCDS';
~/sap/hdbclient/hdbsql -U HANACLOUDTRIAL_DBADMIN \
-A -f -I misc/ups_user_with_roles.sql 
TPC_USER with the password H3LL0C!oud and two roles have been created, and the user can select data from tables in TPCDS schema. We can check the same in the Database Cockpit in SAP BTP.

host, port, certificate and driver from the existing HDI target container service in .env.cat db/.env | cut -d "=" -f 2- -z \
| ~/bin/jq -r -c '.hana[] | select(.tags[]=="mta-resource-name:hdi_db") .credentials | {host: .host, port: .port, driver: .driver, certificate: .certificate, user: "TPC_USER", password: "H3LL0C!oud", schema: "TPCDS", tags: "hana"}' \
| ~/bin/jq > /tmp/ups4tpcds_credentials.json
cat /tmp/ups4tpcds_credentials.json
cf create-user-provided-service UPS4TPCDS \
-p /tmp/ups4tpcds_credentials.json
cf service UPS4TPCDS
mta.yaml and db/.env files to bind this new UPS to our multitarget application in SAP Business Application Studio.
mta.yaml file.
db/.env file is not tracked in the git repository, so we cannot see differences similar to the above. But we can output the user-provided part of its JSON configuration.cat db/.env | cut -d "=" -f 2- -z | ~/bin/jq '."user-provided"'
git add --all
git commit -am "UPS for TPCDS added"
git hist
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 4243 | |
| 3355 | |
| 2602 | |
| 2152 | |
| 1982 | |
| 1255 | |
| 1164 | |
| 1122 | |
| 1100 |