前两章已经可以通过用户
SYSTEM
在
eclipse
中访问了
接下来将:
新建用户
HANA_TPCH
并将 HPC-H 中的数据导入该用户对应的
SCHEMA
中
SCHEMA就相当于 mysql中的数据库,oracle中的表空间
HPC-H中的数据
权限配置
选中一个文件夹,然后点击SQL,就能出现sql查询输入框
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnLhJjMhFTZ0kTN2QmY0gDZ4kTNmRzNwETOiJWNiZDNjR2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
一:新建用户建表
# 创建用户
CREATE USER HANA_TPCH PASSWORD Xing1234 NO FORCE_FIRST_PASSWORD_CHANGE;
登陆后,在Catalog下便会有对应用户的
SCHEMA
建表:
表的描述
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL );
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
表已建好
二:创建文件夹
1.在
Content
文件夹下没有查看权限;
错误提示:
insufficient privilege(EXECUTE on REPOSITORY_REST)
在管理员用户
SYSTEM
用户下执行:
# 授权
GRANT EXECUTE ON REPOSITORY_REST to HANA_TPCH;
右键文件夹刷新
Refresh
2.在
Content
包下创建文件夹
使用用户 SYSTEM 在 Content 下创建文件夹: com.it.hana
给用户
HANA_TPCH
赋权限:
GRANT REPO.READ ON _SYS_REPO."com.it.hana" TO HANA_TPCH;
GRANT REPO.EDIT_NATIVE_OBJECTS ON _SYS_REPO."com.it.hana" TO HANA_TPCH;
GRANT REPO.ACTIVATE_NATIVE_OBJECTS ON _SYS_REPO."com.it.hana" TO HANA_TPCH;
GRANT REPO.MAINTAIN_NATIVE_PACKAGES ON _SYS_REPO."com.it.hana" TO HANA_TPCH;
GRANT EXECUTE ON REPOSITORY_REST to HANA_TPCH;
GRANT REPO.ACTIVATE_IMPORTED_OBJECTS on _SYS_REPO."com.it.hana" to HANA_TPCH;
GRANT REPO.MAINTAIN_IMPORTED_PACKAGES on _SYS_REPO."com.it.hana" to HANA_TPCH;
GRANT REPO.EDIT_IMPORTED_OBJECTS on _SYS_REPO."com.it.hana" to HANA_TPCH;
GRANT SELECT ON SCHEMA "_SYS_BI" TO HANA_TPCH;
GRANT EXECUTE ON SCHEMA "_SYS_BI" TO HANA_TPCH;
GRANT SELECT ON SCHEMA "_SYS_BIC" TO HANA_TPCH;
GRANT EXECUTE ON SCHEMA "_SYS_BIC" TO HANA_TPCH;
在
HANA_TPCH
用户下执行
接下来我们就可以建模了;