How to create HANA on SAP Cloud Platform and use
This article focuses on "how to create and use HANA on SAP Cloud Platform". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to create and use HANA on SAP Cloud Platform.
The csv file used in this article
SAP HANA XS) enables you to create database schema, tables, views, and sequences as design-time files in the repository.
In this exercise, we will use database schema,tables and views provided by SAP HANA Extended Application Services (XS) to achieve the effect of data import.
The HDBtable syntax is a collective term which includes the different configuration schema for each of the various design-time data artifacts, for example: schema (hdbschema), sequence (hdbsequence), table (hdbtable), and view (hdbview).
This is why we will be using the SAP HANA HDBtable syntax including Core Data Service (CDS) artifacts instead, which only requires the SAP HANA Web-based Development Workbench available with any SAP HANA MDC on the SAP Cloud Platform. All the objects will be created as design-time and will allow us to adapt the structure easily without reloading the data.
First, in the HANA MDC instance of the SAP Cloud Platform Neo environment, open HANA Web-based development workbench and switch to the Catalog view:
Click SQL and use SQL statement to create a new user: MOVIELENS_USER
DROP USER MOVIELENS_USER CASCADE;CREATE USER MOVIELENS_USER PASSWORD Welcome18Welcome18 NO FORCE_FIRST_PASSWORD_CHANGE;ALTER USER MOVIELENS_USER DISABLE PASSWORD LIFETIME;call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.ide.roles::CatalogDeveloper',' MOVIELENS_USER'); call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.ide.roles::Developer',' MOVIELENS_USER') Call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.ide.roles::EditorDeveloper',' MOVIELENS_USER'); call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.ide.roles::CatalogDeveloper',' MOVIELENS_USER'); call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.ide.roles::Developer',' MOVIELENS_USER') Call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.ide.roles::EditorDeveloper',' MOVIELENS_USER'); GRANT EXECUTE on _ SYS_REPO.GRANT_ACTIVATED_ROLE TO MOVIELENS_USER WITH GRANT OPTION;GRANT EXECUTE on _ SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT TO MOVIELENS_USER WITH GRANT OPTION;GRANT EXECUTE on _ SYS_REPO.GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT TO MOVIELENS_USER WITH GRANT OPTION GRANT EXECUTE on _ SYS_REPO.REVOKE_ACTIVATED_ROLE TO MOVIELENS_USER WITH GRANT OPTION;GRANT EXECUTE on _ SYS_REPO.REVOKE_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT TO MOVIELENS_USER WITH GRANT OPTION;GRANT EXECUTE on _ SYS_REPO.REVOKE_PRIVILEGE_ON_ACTIVATED_CONTENT TO MOVIELENS_USER WITH GRANT OPTION;GRANT "CREATE SCHEMA" TO MOVIELENS_USER;GRANT REPO.READ on "public" TO MOVIELENS_USER;GRANT REPO.MAINTAIN_IMPORTED_PACKAGES on "public" TO MOVIELENS_USER GRANT REPO.MAINTAIN_NATIVE_PACKAGES on "public" TO MOVIELENS_USER;GRANT REPO.EDIT_NATIVE_OBJECTS on "public" TO MOVIELENS_USER;GRANT REPO.EDIT_IMPORTED_OBJECTS on "public" TO MOVIELENS_USER;GRANT REPO.ACTIVATE_NATIVE_OBJECTS on "public" TO MOVIELENS_USER;GRANT REPO.ACTIVATE_IMPORTED_OBJECTS on "public" TO MOVIELENS_USER
After execution, the user created successfully:
Log out of the SYSTEM user and log in with the newly created user:
Switch to the Editor view:
Under the content node, right-click the menu and create a new Application:
Package is maintained as public.aa.movielens:
Create three new package, namely data, hdb and service:
Import the csv file provided in the previous link into data package:
HANA schema is a container for storing HANA database objects such as tables, views, stored procedures, etc.
Create a new .hdbschema file with the following contents:
Schema_name= "MOVIELENS":
Create another user.hdbrole file:
The contents are as follows:
Role public.aa.movielens.hdb::user extends catalog role "sap.pa.apl.base.roles::APL_EXECUTE", "AFLPM_CREATOR_ERASER_EXECUTE", "AFL__SYS_AFL_AFLPAL_EXECUTE" {schema public.aa.movielens.hdb:MOVIELENS.hdbschema: SELECT, EXECUTE, CREATE ANY;}
This role defines the permissions needed for the application we created to work:
Finally, create the CDS artifacts:
Create a new data.hdbdd file:
Namespace public.aa.movielens.hdb;@Schema: 'MOVIELENS'context "data" {@ Catalog.tableType: # COLUMN Entity LINKS {key MOVIEID: Integer; IMDBID: Integer; TMDBID: Integer;}; @ Catalog.tableType: # COLUMN Entity MOVIES {key MOVIEID: Integer; TITLE: String; GENRES: String;}; @ Catalog.tableType: # COLUMN Entity RATINGS {key USERID: Integer Key MOVIEID: Integer; RATING: hana.SMALLDECIMAL; TIMESTAMP: Integer;}; @ Catalog.tableType: # COLUMN Entity TAGS {key USERID: Integer; key MOVIEID: Integer; key TAG: String; TIMESTAMP: Integer;};}
Use the following SQL statement to assign the newly created user role to the user MOVIELENS_USER:
Call _ SYS_REPO.GRANT_ACTIVATED_ROLE ('public.aa.movielens.hdb::user','MOVIELENS_USER')
Create a table-import configuration file that specifies the data stored in the csv file and how to write it to HANA MDC's persistent objects, such as database tables.
Create a new file data.hdbti in hdb package:
Import = [{table = "public.aa.movielens.hdb::data.LINKS"; schema = "MOVIELENS"; file = "public.aa.movielens.data:links.csv"; header = true; delimField = ","; delimEnclosing= "\";}, {table = "public.aa.movielens.hdb::data.MOVIES"; schema = "MOVIELENS"; file = "public.aa.movielens.data:movies.csv"; header = true DelimField = ","; delimEnclosing= "\";}, {table = "public.aa.movielens.hdb::data.RATINGS"; schema = "MOVIELENS"; file = "public.aa.movielens.data:ratings.csv"; header = true; delimField = ","; delimEnclosing= "\";}, {table = "public.aa.movielens.hdb::data.TAGS"; schema = "MOVIELENS" File = "public.aa.movielens.data:tags.csv"; header = true; delimField = ","; delimEnclosing= "\";}]
By executing the following SQL statement, you can successfully read the data from the csv file from the database table of the HANA MDC instance:
Select 'links' as "table name", count (1) as "row count" from "MOVIELENS". "public.aa.movielens.hdb::data.LINKS" union allselect' movies' as "table name", count (1) as "row count" from "MOVIELENS". "public.aa.movielens.hdb::data.MOVIES" union allselect 'ratings' as "table name", count (1) as "row count" from "MOVIELENS". "public.aa.movielens.hdb::data.RATINGS" union allselect' tags' as "table name" Count (1) as "row count" from "MOVIELENS". Public.aa.movielens.hdb::data.TAGS "
At this point, I believe you have a deeper understanding of "how to create HANA on SAP Cloud Platform and use it". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
