if(재능이 없으면 시간으로 극복하라){return 성공;}

Profile

Post

Project

SAP BTP - HANA Client를 이용하여 User, Table 만들고 데이터 삽입하기

User, Table을 만들고 Data를 Import 해보자

hdbsql의 명령어를 알아보자

hdbsql -h | more

image

cockpit의 EndPoint를 copy해서 명령어를 쳐보자

hdbsql -n ENDPOINT -u DBADMIN

점심시간이 끝나고 와서 보니 세션이 끝나서 디비도 내려갔음.. image

다시 재기동하고 나니 정상적으로 접속됨 image

스펙 확인

\s

image

USER 만들기

CREATE USER 유저명 PASSWORD 패스워드 no force_first_password_change;

데이터에 대한 영향은 없고 유저가 만들어 졌는지 확인 image

image

유저에다가 스키마를 생성 할 권한을 주고

GRANT CREATE SCHEMA TO USER1;

USER1로 계정 전환

CONNECT USER1 PASSWORD Password1;

SCHEMA 생성

CREATE SCHEMA BJSYSTEMS;

SCHEMA 셋팅

SET SCHEMA BJSYSTEMS;

현재 접속중인 유저와 스키마를 확인한다

SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;

image

아래와 같은 구조

image

스키마까지 만들었으니 일단 client는 종료하고 매번 저렇게 EndPoint, password 를 입력할 수 없으니 hdbuserstore에 저장하여 사용해보자.

명령어 입력

hdbuserstore Set 키닉네임 엔드포인트 유저명 패스워드

ex)

hdbuserstore Set Nuruhee hanacloud.ondemand.com:443 USER1 Password1

오류 발생 image

관리자 권한으로 다시 cmd창을 켜서 명령어를 입력하니 정상적으로 동작 image

만들어 둔 키를 이용하여 디비에 접근해보자

hdbsql -U 키닉네임

정상접속 완료 image

아까 만들어두었던 스키마로 셋팅 image

본인이 접근할 경로에 example.sql 파일 생성.

SET SCHEMA BJSYSTEMS;
CREATE COLUMN TABLE HOTEL(
    hno INTEGER PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    address NVARCHAR(40) NOT NULL,
    city NVARCHAR(30) NOT NULL,
    state NVARCHAR(2) NOT NULL,
    zip NVARCHAR(6)
);
CREATE COLUMN TABLE ROOM(
    hno INTEGER,
    type NVARCHAR(6),
    free NUMERIC(3),
    price NUMERIC(6,2),
    PRIMARY KEY (hno, type),
    FOREIGN KEY (hno) REFERENCES HOTEL
);
CREATE COLUMN TABLE CUSTOMER(
    cno INTEGER PRIMARY KEY,
    title NVARCHAR(7),
    firstname NVARCHAR(20),
    name NVARCHAR(40) NOT NULL,
    address NVARCHAR(40) NOT NULL,
    zip NVARCHAR(6)
);
CREATE COLUMN TABLE RESERVATION(
    resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    rno INTEGER NOT NULL,
    cno INTEGER,
    hno INTEGER,
    type NVARCHAR(6),
    arrival DATE NOT NULL,
    departure DATE NOT NULL,
    PRIMARY KEY (
        "RESNO", "ARRIVAL"
    ),
    FOREIGN KEY(hno) REFERENCES HOTEL,
    FOREIGN KEY(cno) REFERENCES CUSTOMER
);
CREATE COLUMN TABLE MAINTENANCE(
    mno INTEGER PRIMARY KEY,
    hno INTEGER,
    description NVARCHAR(100),
    date_performed DATE,
    performed_by NVARCHAR(40)
);

CREATE OR REPLACE PROCEDURE SHOW_RESERVATIONS(
    IN IN_HNO INTEGER, IN IN_ARRIVAL DATE)
    SQL SECURITY INVOKER
    READS SQL DATA
    AS BEGIN
        SELECT
            R.RESNO,
            R.ARRIVAL,
            DAYS_BETWEEN (R.ARRIVAL, R.DEPARTURE) as "Nights",
            H.NAME,
            CUS.TITLE,
            CUS.FIRSTNAME AS "FIRST NAME",
            CUS.NAME AS "LAST NAME"
        FROM
            RESERVATION AS R
            LEFT OUTER JOIN
            HOTEL as H
            ON H.HNO = R.HNO
            LEFT OUTER JOIN
            CUSTOMER AS CUS
            ON CUS.CNO = R.CNO
            WHERE R.ARRIVAL = :IN_ARRIVAL AND
                  H.HNO = :IN_HNO
        ORDER BY
            H.NAME ASC,
            R.ARRIVAL DESC;
    END;

INSERT INTO HOTEL VALUES(10, 'Congress', '155 Beechwood St.', 'Seattle', 'WA', '20005');
INSERT INTO HOTEL VALUES(11, 'Regency', '477 17th Avenue', 'Seattle', 'WA', '20037');
INSERT INTO HOTEL VALUES(12, 'Long Island', '1499 Grove Street', 'Long Island', 'NY', '11788');
INSERT INTO HOTEL VALUES(13, 'Empire State', '65 Yellowstone Dr.', 'Albany', 'NY', '12203');
INSERT INTO HOTEL VALUES(14, 'Midtown', '12 Barnard St.', 'New York', 'NY', '10019');
INSERT INTO HOTEL VALUES(15, 'Eighth Avenue', '112 8th Avenue', 'New York', 'NY', '10019');
INSERT INTO HOTEL VALUES(16, 'Lake Michigan', '354 OAK Terrace', 'Chicago', 'IL', '60601');
INSERT INTO HOTEL VALUES(17, 'Airport', '650 C Parkway', 'Rosemont', 'IL', '60018');
INSERT INTO HOTEL VALUES(18, 'Sunshine', '200 Yellowstone Dr.', 'Clearwater', 'FL', '33575');
INSERT INTO HOTEL VALUES(19, 'Beach', '1980 34th St.', 'Daytona Beach', 'FL', '32018');
INSERT INTO HOTEL VALUES(20, 'Atlantic', '111 78th St.', 'Deerfield Beach', 'FL', '33441');
INSERT INTO HOTEL VALUES(21, 'Long Beach', '35 Broadway', 'Long Beach', 'CA', '90804');
INSERT INTO HOTEL VALUES(22, 'Indian Horse', '16 MAIN STREET', 'Palm Springs', 'CA', '92262');
INSERT INTO HOTEL VALUES(23, 'Star', '13 Beechwood Place', 'Hollywood', 'CA', '90029');
INSERT INTO HOTEL VALUES(24, 'River Boat', '788 MAIN STREET', 'New Orleans', 'LA', '70112');
INSERT INTO HOTEL VALUES(25, 'Ocean Star', '45 Pacific Avenue', 'Atlantic City', 'NJ', '08401');
INSERT INTO HOTEL VALUES(26, 'Bella Ciente', '1407 Marshall Ave', 'Longview', 'TX', '75601');

INSERT INTO ROOM VALUES(10, 'single', 20, 135.00);
INSERT INTO ROOM VALUES(10, 'double', 45, 200.00);
INSERT INTO ROOM VALUES(12, 'single', 10, 70.00);
INSERT INTO ROOM VALUES(12, 'double', 13, 100.00);
INSERT INTO ROOM VALUES(13, 'single', 12, 45.00);
INSERT INTO ROOM VALUES(13, 'double', 15, 80.00);
INSERT INTO ROOM VALUES(14, 'single', 20, 85.00);
INSERT INTO ROOM VALUES(14, 'double', 35, 140.00);
INSERT INTO ROOM VALUES(15, 'single', 50, 105.00);
INSERT INTO ROOM VALUES(15, 'double', 230, 180.00);
INSERT INTO ROOM VALUES(15, 'suite', 12, 500.00);
INSERT INTO ROOM VALUES(16, 'single', 10, 120.00);
INSERT INTO ROOM VALUES(16, 'double', 39, 200.00);
INSERT INTO ROOM VALUES(16, 'suite', 20, 500.00);
INSERT INTO ROOM VALUES(17, 'single', 4, 115.00);
INSERT INTO ROOM VALUES(17, 'double', 11, 180.00);
INSERT INTO ROOM VALUES(18, 'single', 15, 90.00);
INSERT INTO ROOM VALUES(18, 'double', 19, 150.00);
INSERT INTO ROOM VALUES(18, 'suite', 5, 400.00);
INSERT INTO ROOM VALUES(19, 'single', 45, 90.00);
INSERT INTO ROOM VALUES(19, 'double', 145, 150.00);
INSERT INTO ROOM VALUES(19, 'suite', 60, 300.00);
INSERT INTO ROOM VALUES(20, 'single', 11, 60.00);
INSERT INTO ROOM VALUES(20, 'double', 24, 100.00);
INSERT INTO ROOM VALUES(21, 'single', 2, 70.00);
INSERT INTO ROOM VALUES(21, 'double', 10, 130.00);
INSERT INTO ROOM VALUES(22, 'single', 34, 80.00);
INSERT INTO ROOM VALUES(22, 'double', 78, 140.00);
INSERT INTO ROOM VALUES(22, 'suite', 55, 350.00);
INSERT INTO ROOM VALUES(23, 'single', 89, 160.00);
INSERT INTO ROOM VALUES(23, 'double', 300, 270.00);
INSERT INTO ROOM VALUES(23, 'suite', 100, 700.00);
INSERT INTO ROOM VALUES(24, 'single', 10, 125.00);
INSERT INTO ROOM VALUES(24, 'double', 9, 200.00);
INSERT INTO ROOM VALUES(24, 'suite', 78, 600.00);
INSERT INTO ROOM VALUES(25, 'single', 44, 100.00);
INSERT INTO ROOM VALUES(25, 'double', 115, 190.00);
INSERT INTO ROOM VALUES(25, 'suite', 6, 450.00);

INSERT INTO CUSTOMER VALUES(1000, 'Mrs', 'Jenny', 'Porter', '1340 N. Ash Street, #3', '10580');
INSERT INTO CUSTOMER VALUES(1001, 'Mr', 'Peter', 'Brown', '1001 34th St., APT.3', '48226');
INSERT INTO CUSTOMER VALUES(1002, 'Company', NULL, 'Datasoft', '486 Maple St.', '90018');
INSERT INTO CUSTOMER VALUES(1003, 'Mrs', 'Rose', 'Brian', '500 Yellowstone Drive, #2', '75243');
INSERT INTO CUSTOMER VALUES(1004, 'Mrs', 'Mary', 'Griffith', '3401 Elder Lane', '20005');
INSERT INTO CUSTOMER VALUES(1005, 'Mr', 'Martin', 'Randolph', '340 MAIN STREET, #7', '60615');
INSERT INTO CUSTOMER VALUES(1006, 'Mrs', 'Sally', 'Smith', '250 Curtis Street', '75243');
INSERT INTO CUSTOMER VALUES(1007, 'Mr', 'Mike', 'Jackson', '133 BROADWAY APT. 1', '45211');
INSERT INTO CUSTOMER VALUES(1008, 'Mrs', 'Rita', 'Doe', '2000 Humboldt St., #6', '97213');
INSERT INTO CUSTOMER VALUES(1009, 'Mr', 'George', 'Howe', '111 B Parkway, #23', '75243');
INSERT INTO CUSTOMER VALUES(1010, 'Mr', 'Frank', 'Miller', '27 5th St., 76', '95054');
INSERT INTO CUSTOMER VALUES(1011, 'Mrs', 'Susan', 'Baker', '200 MAIN STREET, #94', '90018');
INSERT INTO CUSTOMER VALUES(1012, 'Mr', 'Joseph', 'Peters', '700 S. Ash St., APT.12', '92714');
INSERT INTO CUSTOMER VALUES(1013, 'Company', NULL, 'TOOLware', '410 Mariposa St., #10', '20019');
INSERT INTO CUSTOMER VALUES(1014, 'Mr', 'Antony', 'Jenkins', '55 A Parkway, #15', '20903');
INSERT INTO RESERVATION VALUES(1, 100, 1000, 11, 'single', '2020-12-24', '2020-12-27');
INSERT INTO RESERVATION VALUES(2, 110, 1001, 11, 'double', '2020-12-24', '2021-01-03');
INSERT INTO RESERVATION VALUES(3, 120, 1002, 15, 'suite', '2020-11-14', '2020-11-18');
INSERT INTO RESERVATION VALUES(4, 130, 1009, 21, 'single', '2019-02-01', '2019-02-03');
INSERT INTO RESERVATION VALUES(5, 150, 1006, 17, 'double', '2019-03-14', '2019-03-24');
INSERT INTO RESERVATION VALUES(6, 140, 1013, 20, 'double', '2020-04-12', '2020-04-30');
INSERT INTO RESERVATION VALUES(7, 160, 1011, 17, 'single', '2020-04-12', '2020-04-15');
INSERT INTO RESERVATION VALUES(8, 170, 1014, 25, 'suite', '2020-09-01', '2020-09-03');
INSERT INTO RESERVATION VALUES(9, 180, 1001, 22, 'double', '2020-12-23', '2021-01-08');
INSERT INTO RESERVATION VALUES(10, 190, 1013, 24, 'double', '2020-11-14', '2020-11-17');

INSERT INTO MAINTENANCE VALUES(10, 24, 'Replace pool liner and pump', '2019-03-21', 'Discount Pool Supplies');
INSERT INTO MAINTENANCE VALUES(11, 25, 'Renovate the bar area.  Replace TV and speakers', '2020-11-29', 'TV and Audio Superstore');
INSERT INTO MAINTENANCE VALUES(12, 26, 'Roof repair due to storm', null, null);

SELECT SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE, OWNER_NAME FROM "PUBLIC"."OWNERSHOP" WHERE OWNER_NAME = 'USER1';

경로로 이동하여 sql을 실행

hdbsql -U Nuruhee -I example.sql

데이터를 조회해보니 잘 들어간 것 같다.
image

매개 변수를 선언하여 값을 전달해서 쿼리를 실행 시킬 수 있다. 매개 변수의 값은 커맨드라인에서 정해주고 Query 문에서 받아서 사용 가능하다.

sql을 하나 만들어서 변수를 받을 수 있도록 만들어보자. 아까 만들어준 sql과 같은 경로에 아래 쿼리를 적어서 만들어보자
image

SELECT * FROM BJSYSTEMS.CUSTOMER WHERE FIRSTNAME LIKE '&nameParam'

nameParam으로 변수를 받아 처리하는데, 커맨드 라인에서 nameParam에 값이 들어갈 수 있도록 명령어를 날려보자. CUSTOMER 테이블에서 FIRSTNAME이 J로 시작하는 row들을 출력해보자.
image

이번에는 직접 명령어를 쳐서 변수를 선언해서 사용해보자

db를 접속하면서 변수 선언

hdbsql -A -U Nuruhee -V nameParam=J%

추가로 변수 선언

hdbsql=> \vd titleParam Mr

변수 선언 확인

\vl

image

인자값을 받아서 쿼리 실행

SELECT * FROM BJSYSTEMS.CUSTOMER WHERE TITLE = '&titleParam' AND FIRSTNAME LIKE '&nameParam'

image

매개 변수를 입력 받을 수 있도록 쿼리 실행

SELECT * FROM HOTEL.CUSTOMER WHERE FIRSTNAME LIKE ?;

image