Hana Introduction
Basic introduction for HANA
1. Column based storage
2. In memory processing - cheaper availability of Hardware.
3. Insert only
Update is dealt in 2 steps
Delta storage
Delta merge
HANA is not just a database. It is also an application server.
https://www.sap.com/cmp/td/sap-hana-e...
https://developers.sap.com/tutorials/...
https://dbeaver.io/download/
Queries used in the video
-- Meta Queries.
SELECT * FROM M_CS_columns
SELECT * FROM schemas;
SELECT * FROM tables;
SELECT * FROM tables WHERE SCHEMA_NAME='OFFICE';
SELECT * FROM m_cs_tables;
SELECT * FROM procedures WHERE SCHEMA_NAME='Office';
SELECT * FROM functions;
SELECT TO_DATS('2019-12-13') FROM DUMMY;
SELECT 2+3 AS "Total" FROM Dummy;
-- Drop a schema.
DROP SCHEMA Office CASCADE
-- Create schema
CREATE SCHEMA office OWNED BY system;
-- DROP TABLE Office.EMPLOYEE
CREATE SEQUENCE Office.ADDRESS_SEQ START WITH 1 INCREMENT BY 1;
CREATE COLUMN TABLE Office.ADDRESS
("ID" bigint NOT NULL,
ADDRESS_LINE1 NVARCHAR(100),
ADDRESS_LINE2 NVARCHAR(100),
CITY NVARCHAR(100),
POSTAL_CODE NVARCHAR(10) ,
STATE NVARCHAR(100),
COUNTRY NVARCHAR(100),
CONSTRAINT ADDRESS_PRIMARY_KEY PRIMARY KEY ("ID")
)
INSERT INTO OFFICE.ADDRESS
(ID, ADDRESS_LINE1, ADDRESS_LINE2, CITY, POSTAL_CODE, STATE, COUNTRY)
VALUES(Office.ADDRESS_SEQ.NEXTVAL, '55 ISIDORO AVE','', 'MTN HOUSE','95392', 'CA','USA' )
INSERT INTO OFFICE.ADDRESS
(ID, ADDRESS_LINE1, ADDRESS_LINE2, CITY, POSTAL_CODE, STATE, COUNTRY)
VALUES(Office.ADDRESS_SEQ.NEXTVAL, '123 MAIN STREET','', 'DREAMCITY','9999', 'CA','USA' )
CREATE SEQUENCE Office.EMPLOYEE_SEQ START WITH 100 INCREMENT BY 1;
-- ("ID" bigint NOT NULL primary key GENERATED BY DEFAULT AS IDENTITY,
CREATE COLUMN TABLE Office.EMPLOYEE
("ID" bigint NOT NULL,
FIRST_NAME NVARCHAR(100),
LAST_NAME NVARCHAR(100) ,
ADDRESS_ID INTEGER NOT NULL,
TITLE VARCHAR(50),
MANAGER_ID INTEGER,
FOREIGN KEY("ADDRESS_ID")
REFERENCES "OFFICE"."ADDRESS" ("ID") ,
FOREIGN KEY("MANAGER_ID")
REFERENCES "OFFICE"."EMPLOYEE" ("ID"),
CONSTRAINT EMPLOYEE_PRIMARY_KEY PRIMARY KEY ("ID")
)
INSERT INTO Office.EMPLOYEE (ID, FIRST_NAME, LAST_NAME,TITLE, ADDRESS_ID)
VALUES (Office.EMPLOYEE_SEQ.NEXTVAL, 'Daniel','Craig','Manager', 1);
INSERT INTO Office.EMPLOYEE (ID, FIRST_NAME, LAST_NAME,TITLE, ADDRESS_ID, MANAGER_ID)
VALUES
(Office.EMPLOYEE_SEQ.NEXTVAL,
'John','Doe','Developer', 1,101);
select * from OFFICE.EMPLOYEE;
CREATE COLUMN TABLE ERROR_MESSAGE("ID" bigint NOT NULL primary key GENERATED BY DEFAULT AS IDENTITY, error_code NVARCHAR(100), error_message NVARCHAR(5000) );
CREATE OR REPLACE PROCEDURE getName(IN ID INT)
AS BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- INSERT INTO OFFICE.ERROR_MESSAGE (error_code, error_message) VALUES (::sql_error_code,::sql_error_message);
END;
SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME FROM OFFICE.EMPLOYEE WHERE ID=:ID;
END;
CALL getName(101);
SELECT * FROM ERROR_MESSAGE;
CREATE OR REPLACE PROCEDURE DECIDE_SALARIES(OUT EMP_SALARY TABLE(ID INTEGER, NAME NVARCHAR(200), SALARY INTEGER ))
AS BEGIN
--DECLARE EXIT HANDLER FOR SQLEXCEPTION
--BEGIN
-- INSERT INTO OFFICE.ERROR_MESSAGE (error_code, error_message) VALUES (::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE);
-- END;
EMP_SALARY = SELECT ID AS ID,FIRST_NAME || ' ' || LAST_NAME AS NAME , CASE TITLE WHEN 'Manager' THEN 5000.00 WHEN 'Developer' THEN 2500 END AS SALARY FROM OFFICE.EMPLOYEE;
END;
CALL DECIDE_SALARIES(?);