SAP HANA 技能[一] 常用语法说明

前置说明

本篇基于SAP HANA的一些基础语法，大佬请饶～

SCHEMA相关

--创建schema:
--语法：CREATE SCHEMA <schema_name> [OWNED BY <user_name>]
/*OWNED BY：指定schema的拥有者，如果省略。当前用户将是这个shema的拥有者*/
create schema my_schema;
create schema my_schema OWNED BY system.
--删除schema:
--语法：DROP SCHEMA <schema_name> [<drop_option>]
drop_option： CASCADE | RESTRICT
/*默认的drop_option为：RESTRICT(限制约束)
RESTRICT：直接删除没有依赖的对象,如果对象有依赖关系，会抛出错误信息。
CASCADE：直接删除所有对象。*/
CREATE SCHEMA my_schema;
CREATE TABLE my_schema.t (a INT);
DROP SCHEMA my_schema CASCADE;
select * from tables  where schema_name='USER' 
--查询schema:USER下的所有表

TABLE

HANA数据库可以存储ROW TABLE（行表）和COLUMN TABLE（列表），分别适用于不同的场景。

创建表

行存储表适用于场景：

一次处理一条记录的情况
应用需要访问完整记录或记录的大部分（即一条记录中的所有字段或大多数字段）
不需要压缩率
没有或很少的聚集、分组等复杂操作
表中的记录行数不是很多

列存储表适用场景：

通常只是在一个或少量列上执行计算操作
表在进行搜索时通常基于少量列上的值
表有很多列
表有很多行，并且通常进行的是列式操作（比如：聚集计算和where中字段值查找）
需要很高的压缩率

代码实例

--行存储表示例:   
--例1
CREATE TABLE accounts
    ( act_no CHAR(18), 
      act_name NVARCHAR(100),
      balance DECIMAL(15,2),
      PRIMARY KEY (act_no)
    ) ;
--例2
CREATE TABLE R 
    (A INT PRIMARY KEY, 
     B NVARCHAR(10)
    ); 
    
--列存储表示例：
--例1
CREATE COLUMN TABLE tpch.nation
    (n_nationkey INT NOT NULL,
     n_name CHAR(25),
     n_regionkey INT,
     n_comment VARCHAR(152),
     PRIMARY KEY(n_nationkey)
    ) ;
--例2(分区，只适用于列表，分区键必须是主键的一部分)
CREATE COLUMN TABLE tpch.lineitem
    (l_orderkey INT NOT NULL,l_partkey INT,
     l_suppkey INT,l_linenumber INT NOT NULL,
     l_quantity DECIMAL(10,2),l_extendedprice DECIMAL(10,2),
     l_discount DECIMAL(10,2),l_tax DECIMAL(10,2),
     l_returnflag CHAR(1),l_linestatus CHAR(1),
     l_shipdate DATE,l_commitdate DATE,l_receiptdate DATE,
     l_shipinstruct CHAR(25),l_shipmode CHAR(10),
     l_comment VARCHAR(44),
     PRIMARY KEY INVERTED VALUE (l_orderkey,l_linenumber)
    )
    PARTITION BY HASH (l_orderkey,l_linenumber) PARTITIONS 4,
    RANGE (l_shipdate) (    
       PARTITION '2011/01/01'<= VALUES <'2011/04/01',
       PARTITION '2011/04/01'<= VALUES <'2011/07/01',
       PARTITION '2011/07/01'<= VALUES <'2011/10/01',
       PARTITION OTHERS) ; 

--HANA查看特定表的分区情况
select * from sys.M_CS_PARTITIONS where table_name =  'TABLE_NAME';

在上面的创建列表的例子中，我们指定的“UNIQUE”或“PRIMARY KEY”约束，HANA会自动创建相应的索引。这些索引分为两种类型(仅适用于列表)

INVERTED VALUE 适合于范围查询或like查询
INVERTED HASH 使用HASH对组合唯一键或组合主键进行编码和压缩。对于等值查询(点查询)，这种索引类型具有更好的性能；并且能够减少组合主键存储使用的内存数量。

如果不指定，缺省是 INVERTED VALUE。上面的INVERTED VALUE和INVERTED HASH 仅在UNIQUE和PRIMARY KEY约束中使用。下面是指定的示例

1 2	PRIMARY KEY INVERTED VALUE (l_orderkey,l_linenumber) PRIMARY KEY INVERTED HASH (l_orderkey,l_linenumber)

HANA除了直接建表外，也可以通过已存在的表创建新表，示例如下：

/*依据某个已经存在的表创建另外的新表。HANA SQL提供了两类方法*/
--方法一：创建的表与源表数据类型、约束完全相同。例如：
CREATE TABLE T_ACCOUNTS LIKE ACCOUNTS WITH NO DATA      
--方法二：创建的表字段类型和NULL/NOT NULL属性相同
CREATE TABLE T_ACCOUNTS1 AS (SELECT * FROM  ACCOUNTS) WITH DATA

修改表

修改表名

--01.语法：RENAME TABLE <old_table_name> TO <new_table_name>
--02.描述：RENAME TABLE 语句在同一个Schema下，将表名修改为 new_table_name。
--03.示例：
CREATE TABLE mySchema.A (A INTPRIMARYKEY, B INT);
--显示模式 mySchema 下表名的列表：
SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = 'MYSCHEMA';
--表 mySchema.A 重命名为 B：
RENAME TABLE mySchema.A TO B;--注：修改后B还是在mySchema里

修改表类型

1 2	ALTER TABLE accounts COLUMN THREADS 10 BATCH 10000; --行存储表转为列存储表 ALTER TABLE accounts ROW THREADS 10; --列存储表转为行存储表

参数说明：

THREADS：指定表转换时的并行执行线程数。线程数的最佳值是系统可用 CPU 内核数。如果未提供 THREADS，将使用 indexserver 中指定的 CPU 内核数的默认值。
BATCH：指定批量处理的行数。如果未指定 BATCH，将使用默认值 2000000。在每次达到批量处理的行数后将立即提交到列存储表中。BATCH 选项仅在从行转换为列存储时才能使用。

修改表约束

1
2
3

--删除/增加主键
ALTER TABLE "SCHEMA1"."TABLE1" DROP PRIMARY KEY;
ALTER TABLE "SCHEMA1"."TABLE1" ADD  PRIMARY KEY(TID, TNAME);

修改字段

--增加或删除字段
ALTER TABLE "SCHEMA1"."TABLE1" ADD("CREATETIME" DATE NULL) ;
ALTER TABLE "SCHEMA1"."TABLE1" DROP (COL1);
--修改字段类型
ALTER TABLE "SCHEMA1"."TABLE1" ALTER ("TYPE" NVARCHAR (10) NULL);

INDEXES

--SAP HANA的索引都是保存在内存中。
--创建索引：
--语法：
CREATE [UNIQUE] [BTREE | CPBTREE] INDEX <index_name> 
       ON <table_name> (<column_name_order>, ...) [ASC | DESC]
--创建测试表：
create row table test_index (id INT,name nvarchar(10), remark nvarchar(10));
create index indextest1 on test_index(name);
CREATE CPBTREE INDEX indextest2 ON test_index(id, name DESC);

--创建唯一键索引：
create unique index indextest4 on test_index(id);
create unique index indextest3 on test_index(name,remark);

--删除索引：DROP INDEX <index_name>
drop index indextest2;--删除索引indextest2
--查询索引：
select * from indexes where table_name ='EMP';  --查询员工表中使用的索引
select * from index_columns  where table_name ='EMP'; --查询索引列
select * from m_rs_indexes  where table_name = 'EMP'; --查询索引的统计信息(B-tree and CPB-tree)
select * from fulltext_indexes where table_name = 'EMP'; --查询Fulltext 索引
select * from m_fulltext_queues; --查看fulltext 索引队列的状态