Hana 常用命令

连接数据源
#连接参数
#docker 容器部署使用的命令
hdbsql -i 90 -d SystemDB -u SYSTEM -p 

#非容器部署
sudo /usr/sap/HXE/HDB90/exe/hdbsql -n 127.0.0.1:39013 -u SYSTEM
#输入密码
 my_pw
#查看所有数据库：
select * from m_databases;  #查看tenant数据库：
#创建schema: 
create schema HANASCHEMA; 
#创建全量表（测试全量字段时使用）
CREATE TABLE HANASCHEMA_LJ.LAS2HANA_TEST(id BIGINT,tinyint_info TINYINT,smallint_info SMALLINT,integer_info INTEGER,bigint_info BIGINT,double_info DOUBLE,decimal_info DECIMAL(34,10),smalldecimal_info SMALLDECIMAL,real_info REAL,varchar_info VARCHAR(1000),nvarchar_info NVARCHAR(1000),alphanum ALPHANUM,varbinary_info VARBINARY(1000),nclob_info NCLOB,clob_info CLOB,text_info TEXT,date_info DATE,time_info TIME,timestamp_info TIMESTAMP,second_date_info SECONDDATE,bintext_info BINTEXT,shorttext_info SHORTTEXT(1000),boolean_info BOOLEAN,blob_info BLOB,PRIMARY KEY (id));

#查询Schema下的表：
select * from public.tables where SCHEMA_NAME='HANASCHEMA' and table='DTS_SOURCE'
#插入数据：
INSERT INTO HANASCHEMA.DTS_SOURCE (id,name,address,create_time,event_time,price) VALUES (1,'张泽','北京市海淀区4单元401',1669452849,1669452549,-35898.5138815522)
#查询表schema：
select * from public.tables where SCHEMA_NAME='HANASCHEMA'
#查询表内数据
select * from 'TEST_JL'.'DTS_SOURCE' where id=5004;
select * from 'TEST_JL'.'DTS_SOURCE' where INT_INFO > 0;
#删除表：
 drop table "HANASCHEMA"."DTS_SOURCE";
#清除表内数据
DELETE FROM schema_name.table_name;
#创建主键自增的数据
#创建序列
CREATE SEQUENCE HANASCHEMA.DTS_SOURCE_SEQ INCREMENT BY 1 MAXVALUE 99999999 MINVALUE 1 NO CYCLE start with 1;
#查询序列
SELECT HANASCHEMA.DTS_SOURCE_SEQ.NEXTVAL FROM DUMMY;
SELECT HANASCHEMA.DTS_SOURCE_SEQ.CURRVAL FROM DUMMY;

#插入数据
insert into HANASCHEMA.DTS_SOURCE_PERF SELECT  HANASCHEMA.DTS_SOURCE_SEQ.NEXTVAL,NAME,ADDRESS,CREATE_TIME,EVENT_TIME,PRICE from HANASCHEMA.DTS_SOURCE_PERF limit 1373824

#查询数据总量
select count(1) from HANASCHEMA.DTS_SOURCE_PERF
SQL 拼接
SELECT concat('Q',HANASCHEMA.DTS_SOURCE_SEQ.NEXTVAL) FROM DUMMY;
#新建视图
CREATE VIEW TEST_JL.DTS_SOURCE_ALL_SINK_VIEW AS
SELECT ID,BIGINT_INFO
FROM TEST_JL.DTS_SOURCE_ALL;