金猪报财的游戏

書(shū)名： Real Time Analytics with SAP HANA
作者名： Vinay Singh
本章字?jǐn)?shù)： 884字
更新時(shí)間： 2021-07-09 21:20:50

Introducing SAP HANA SQL

As stated, you will not learn SQL as a whole new concept, but will just revise the traditional SQL concepts at a glance and focus on a few new topics that are of importance from SAP HANA perspective. Our key focus here will be on the SAP HANA SQL script, creating procedures, and learning to create SAP HANA specific JOINS.

Classical SQL

SQL is used to retrieve, store, and manipulate data in the database. SQL can be studied under three subheads:

These subheads are explained as follows:

DDL: These statement that are used to define the data: create, alter, drop tables
DML: These statements are used to manipulate the data, select, deselect, insert, and update
DCL: These statements that are used to control the table, grant, and revoke

The followings are the elements of SQL:

Identifiers: These are used to represent names in SQL statements including table/view name, column name, username, role name and so on. There are two types of Identifiers: ordinary and delimited.
Data types: These define the characteristics of the data and its value. Data types in SQL are as follows:
Expressions: These are clause evaluated to return values. We have different types of expressions in SQL. For example, if…then…..else (case expression) or nested queries (Select (Select ……)).
Functions: These are used in expressions for retrieving information from the database. We have a number of functions and data type conversion functions. The number functions take numeric values or alphanumeric/strings with numeric character values and return numeric values, whereas, data type conversion functions are used to convert arguments from one data type to another. For example, to_alphanum, concat, current_date, and so on.
Operators: These are used for value comparison, assigning values, or can also be used for calculation. We have different types of operators like Unary, Binary, arithmetic, and string operators to name a few. For example, +, =, subtraction, and or.
Predicates: A predicate is specified by combining one or more expressions or logical operators and returning one of the following logical or truth values: true, false, or unknown. Examples are null, in, and like.

In the upcoming chapters, we will learn how to work with SAP HANA studio and open SQL editor, so as to complete the concepts. I will show you how we work with the preceding SQL concepts. For our examples and exercises, we will use the following tables. We will create more tables in further chapters as we progress.

The following table shows you the sales_facts:

The following table shows you the CUSTOMERS data:

The following is a REGION table:

The following table shows you details of the PRODUCT table:

Let's see how we can create the preceding tables in SAP HANA:

In SAP HANA studio, right-clicking on your schema (here, HANA_DEMO) will display Open SQL Console; click on it.

We will cover some of the following SQL queries to create the tables:

Create a schema first, if it hasn't already been created for you—HANA_DEMO; you can choose any name.

A database schema is the skeleton structure that represents the logical view of the entire database (objects such as tables, views, and stored procedures). It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data, whereas Table is one of the objects contained in schema. It is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows:

CREATE SCHEMA "HANA_DEMO";
GRANT SELECT ON SCHEMA HANA_DEMO TO _SYS_REPO WITH GRANT OPTION; if you do not run Grant , later when you will activate your views it will give you erros.

The following command creates the SALES_FACTS table:

CREATE COLUMN TABLE "HANA_DEMO"."SALES_FACTS"(
"PRODUCT_KEY" INTEGER NOT NULL,
"REGION_KEY" INTEGER NOT NULL,
"AMOUNT_SOLD" DECIMAL NOT NULL,
"QUANTITY_SOLD" INTEGER NOT NULL,
PRIMARY KEY ("PRODUCT_KEY","REGION_KEY") );

The following command creates the CUSTOMER table:

CREATE COLUMN TABLE "HANA_DEMO"."CUSTOMER"(
"CUSTOMER_KEY" VARCHAR(8) NOT NULL,
"CUST_LAST_NAME" VARCHAR(100) NULL,
"CUST_FIRST_NAME" VARCHAR(30) NULL,
PRIMARY KEY ("CUSTOMER_KEY ") );

The following command creates the PRODUCTS table:

CREATE COLUMN TABLE "HANA_DEMO"."PRODUCTS" (
"PRODUCT_KEY" INTEGER NOT NULL,
"PRODUCT_NAME" VARCHAR(50) NULL,
PRIMARY KEY ("PRODUCT_KEY") );

The following command creates the REGION table:

CREATE COLUMN TABLE "HANA_DEMO"."REGION"(
"REGION_ID" INTEGER NOT NULL,
"REGION_NAME" VARCHAR(100) NULL,
"SUB_AREA" VARCHAR(30) NULL,
PRIMARY KEY ("REGION_ID") );

The following are sample insert queries:

insert into "<YOUR SCHEMA>"."TABLE NAME" values(columns1,Columns2,..,); 
insert into "HANA_DEMO"."SALES_FACTS" values(01,100,50000,500); 
insert into "HANA_DEMO"."PRODUCTS" values(01,'GasKit');
insert into "HANA_DEMO"."REGION" values(01,'Europe','Germany');

Tip

I am inserting single values, but you can insert or re-run the query with different values or download the Excel file from our website for demo data.

After executing the scripts, you should have three tables created. If there are no tables, try right-clicking on your schema and then refresh it.
In the following screenshot, you can see the tables we just created under the HANA_DEMO schema:

Tip

We need to Grant schema SELECT rights to _SYS_REPO user.

In SQL, the editor of our schema needs to execute the following command line:

GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
GRANT SELECT ON SCHEMA HANA_DEMO TO _SYS_REPO WITH GRANT OPTION

If we miss this step, an error will occur when you activate your views later.

官术网_书友最值得收藏!

Real Time Analytics with SAP HANA

Introducing SAP HANA SQL

Classical SQL

Tip

Tip