HANA和TeraData数据库迁移调研

1
2

CREATE FULLTEXT INDEX <index_name> ON <table_name> ( <column_name> ) 
   [ <fulltext_parameter_list> ]

1
2
3
4
5
6

CREATE [ UNIQUE ] [ BTREE | CPBTREE | INVERTED [ <composite_type> ] ] INDEX <index_name> ON <table_name>
   ( <column_name_order_entry>[, <column_name_order_entry> [,...]) 
   [ <global_index_order> ] 
   [ <fillfactor> ] 
   [ NOWAIT ] 
   [ ONLINE ]  

1
2
3
4
5
6

CREATE [ OR REPLACE ] PROJECTION VIEW <projection_view_name> [ ( <view_column_name_list> ) ]
 AS SELECT <table_column_list>
 FROM [ <schema>.]<source_table_name>
 [ <with_association_clause> ]
 [ <with_annotations_clause> ]
 [ WITH DDL ONLY ]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE [ OR REPLACE ] VIEW <view_name> [ COMMENT <string_literal> ]
 [ ( <column_name_list> ) ] [ ( <parameterized_view_clause> ) ] 
 <as_subquery> [ <with_association_clause> ]
 [ WITH MASK ( <column_name> USING <mask_expression> [,...] ) ]
 [ WITH EXPRESSION MACROS( <expression_macro_list> ) ]
 [ WITH <annotations> ]
 [ WITH STRUCTURED PRIVILEGE CHECK ]
 [ WITH [ <cache_type> ] CACHE ]
 [ RETENTION <minute_value> ]
 [ OF <projection_list> ] ]
 [ FILTER <filter_condition> ]
 [ <location_clause> ]
 [ FORCE ]
 [ WITH CHECK OPTION ]
 [ WITH DDL ONLY ]
 [ WITH READ ONLY ]
 [ WITH ANONYMIZATION ( ALGORITHM <algorithm_name> { [ <view_level_parameters> ] [ <column_level_parameters> ] } ) ]

1
2
3

CREATE SCHEMA <schema_name> [OWNED BY <user_name>]   

CREATE [OR REPLACE] SCHEMA SYNONYM <synonym_name> FOR <schema_reference>

1

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM <synonym_name> FOR <synonym_source_object_name>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE [ <table_type> ] { <table_name> | <replica_name> }
   [<table_contents_source>
   [<system_versioning_spec>]
   [<application_time_period_configuration>]
   [<bi_temporal_table_spec>]
   [<with_association_clause>] 
   [<with_annotation_clause>]
   [<with_mask_clause>]        
   [<logging_option>] 
   [<auto_merge_option>]
   [<unload_priority_clause>]
   [<schema_flexibility_option>] 
   [<partition_clause>] 
   [<persistent_memory_spec_clause>] 
   [<group_option_list>]
   [<location_clause>] 
   [<replica_clause>] 
   [<global_temporary_option>] 
   [<series_clause>] 
   [<unused_retention_period_option>] 
   [<record_commit_timestamp_clause>]            
   [COMMENT <comment_string>]
   [<numa_node_preference_clause>]
   [<load_unit>]

1
2

CREATE VIRTUAL TABLE <virtual_table_name> 
    AT <remote_location_clause> [ <remote_property_clause> ]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE [ OR REPLACE ] FUNCTION <function_name> 
  [ ( <input_parameter_clause> ) ] 
   RETURNS <return_type>
   [ LANGUAGE <lang> ]
   [ SQL SECURITY <mode> ] 
   [ DEFAULT SCHEMA <default_schema_name> ]
   [ DETERMINISTIC ] 
   [ <cache_clause> ]
   [ <variable_cache_clause> ]
   [ WITH ENCRYPTION ]
    AS
     { BEGIN 
          <statement_body> 
       END 
       | HEADER ONLY }

1
2
3

CREATE VIRTUAL FUNCTION <func_name> [(<parameter_clause>)]
   RETURNS <return_table_type> [SQL SECURITY <mode>] [<package_clause>]
   CONFIGURATION <remote_proc_properties> AT <source_name>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

CREATE [ OR REPLACE ] PROCEDURE <proc_name> [ (<input_output_parameter_clause>) ]
 [ LANGUAGE <lang> ]
 [ SQL SECURITY <mode> ]
 [ DEFAULT SCHEMA <default_schema_name> ]
 [ DETERMINISTIC ] 
 [ READS SQL DATA [ WITH RESULT VIEW <view_name> ] ] 
 [ <route_target_element> ] 
 [ <variable_cache_clause> ]
 [ WITH ENCRYPTION ]
 [ AUTOCOMMIT DDL { ON | OFF } ]
 AS 
   { BEGIN [ SEQUENTIAL EXECUTION ]
      <statement_body>
     END
     | HEADER ONLY }

1
2
3
4
5
6
7
8
9

CREATE VIRTUAL PROCEDURE <proc_name> [(<parameter_clause>)]
  [SQL SECURITY <mode>] 
  [<package_clause>] LANGUAGE <lang>
   AT <source_name>
[AS
  BEGIN 
          <procedure_body>
   END
   ]

1
2

CREATE TYPE <type_name>
   AS TABLE (<column_definition>[{,<column_definition>}...]) [ SQLSCRIPT SEARCH KEY( <column_name> [,…] ) ]

1
2

CREATE SEQUENCE <sequence_name> [ <sequence_parameter_list> ]
   [ RESET BY <subquery> ]

1
2
3

CREATE COLUMN TABLE "TEST" (
    "ID" bigint NOT NULL primary key GENERATED BY DEFAULT AS IDENTITY
);

1
2
3
4
5

CREATE CLIENTSIDE ENCRYPTION COLUMN KEY [ <schema_name>.]<key_name> 
   [ ALGORITHM <algorithm_name> ]
   { ENCRYPTED WITH KEYPAIR <keypair_name> | HEADER ONLY }

CREATE CLIENTSIDE ENCRYPTION KEYPAIR <keypair_name> [ ALGORITHM <algorithm_name> ]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE ADAPTER <adapter_name> [PROPERTIES <properties>] AT LOCATION 
{DPSERVER | AGENT <agent_name>}

CREATE AGENT <agent_name> PROTOCOL { 'HTTP' | 'TCP' HOST <agent_hostname> PORT <agent_port_number> [{ENABLE | DISABLE} SSL]} [AGENT GROUP <agent_group_name>]

CREATE AGENT GROUP <agent_group_name>

<audit_action_name> ::= CREATE AGENT                 
	| ALTER AGENT                  
	| DROP AGENT                   
	| CREATE AGENT GROUP           
	| DROP AGENT GROUP             
	| CREATE ADAPTER               
	| ALTER ADAPTER                
	| DROP ADAPTER                 
	| CREATE REMOTE SUBSCRIPTION   
	| ALTER REMOTE SUBSCRIPTION    
	| DROP REMOTE SUBSCRIPTION     
	| PROCESS REMOTE SUBSCRIPTION EXCEPTION

CREATE REMOTE SUBSCRIPTION [<schema_name>.]<subscription_name> 
{ 
   {ON [<schema_name>.]<virtual_table_name> } | 
   {AS (<subquery>)}
}
[ WITH SCHEMA CHANGES ]
{ TARGET TABLE <table_spec> <load_behavior> } | 
{ TARGET TASK <task_spec> } | 
{ PROCEDURE <proc_spec> } 

1
2
3
4
5
6
7

CREATE GRAPH WORKSPACE <workspace_name>
   EDGE TABLE <edge_table_name>
      SOURCE COLUMN <source_column_name>
      TARGET COLUMN <target_column_name>
      KEY COLUMN <edge_key_column_name>
   VERTEX TABLE <vertex_table_name>
      KEY COLUMN <vertex_key_column_name>

1
2
3
4

CREATE STATISTICS [ <data_statistics_name> ] ON <data_sources> 
 [ <data_statistics_type> ] 
 [ <data_statistics_properties> ]
 [ <initial_refresh> ]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE [ OR REPLACE ] TRIGGER <trigger_name> <trigger_action_time> <trigger_event_list>
   ON <subject_table_name>
   [ REFERENCING <transition_list> ] 
   [ <for_each> ] 
   [ <trigger_order_clause> ]
   [ ONLINE ]
      BEGIN 
         [ <trigger_decl_list> ]
         [ <proc_handler_list> ]
         <trigger_stmt_list>
      END

1
2
3
4
5
6

CREATE DATABASE <database_name>  [ AT [ LOCATION ] '<hostname>[:<port_number> ] ' ]
 { ADD '<servicetype>' [ AT [ LOCATION ] '<hostname>[:<port_number> ] ' ] }... 
 [ { AS REPLICA OF [ <source_database_name> ] AT [ LOCATION ] '<hostname>[:<port_number> ]' | SYSTEM USER PASSWORD <password> } ]
 [ OS USER '<username>' OS GROUP '<groupname>' ] 
 [ NO START ]
 [ <restart_mode> RESTART ]

1
2
3
4

CREATE WORKLOAD CLASS <workload_class_name> [ <property_list> ] [ ENABLE | DISABLE ]

CREATE WORKLOAD MAPPING <mapping_name> 
WORKLOAD CLASS <workloadclass_name> [ <property_list> [ <wildcard_option> ] ]

1
2
3
4
5
6
7
8
9

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30), 
   LastName VARCHAR(30), 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( 
   <sql statements> 
);

CREATE MACRO Get_Emp AS ( 
   SELECT EmployeeNo, FirstName, LastName 
   FROM  
   employee 
   ORDER BY EmployeeNo; 
);

-- 执行宏
EXEC <macroname>; 

1
2
3
4
5
6
7
8

CREATE VOLATILE TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no) 
ON COMMIT PRESERVE ROWS;

1
2
3
4
5
6
7

CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE JOIN INDEX <index name> 
AS 
<SELECT Query> 
<Index Definition>;

-- 创建索引
CREATE JOIN INDEX Employee_JI 
AS 
SELECT EmployeeNo,FirstName,LastName, 
BirthDate,JoinedDate,DepartmentNo 
FROM Employee 
PRIMARY INDEX(FirstName);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 唯一主索引
CREATE SET TABLE Salary ( 
   EmployeeNo INTEGER, 
   Gross INTEGER,  
   Deduction INTEGER, 
   NetPay INTEGER 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

-- 非唯一主索引
CREATE SET TABLE Employee _Accounts ( 
   EmployeeNo INTEGER, 
   employee_bank_account_type BYTEINT. 
   employee_bank_account_number INTEGER, 
   employee_bank_name VARCHAR(30), 
   employee_bank_city VARCHAR(30) 
) 
PRIMARY INDEX(EmployeeNo);

1
2
3
4
5

-- 唯一二级索引
CREATE UNIQUE INDEX(EmployeeNo) on employee;

-- 非唯一二级索引
CREATE INDEX(FirstName) on Employee;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE SET TABLE Orders (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE FORMAT 'YYYY-MM-DD', 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 压缩第1、2、3列
CREATE SET TABLE employee ( 
   EmployeeNo integer, 
   FirstName CHAR(30), 
   LastName CHAR(30), 
   BirthDate DATE FORMAT 'YYYY-MM-DD-', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD-', 
   employee_gender CHAR(1), 
   DepartmentNo CHAR(02) COMPRESS(1,2,3) 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

1
2
3
4
5
6
7
8
9

create table TEST.i2 (
i INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
),
STR VARCHAR(20)
) unique primary index i2 (i)

1
2
3
4
5
6
7

CREATE PROCEDURE <procedurename> ( [parameter 1 data type, parameter 2 data type..] ) 
BEGIN 
   <SQL or SPL statements>; 
END;

--调用
CALL <procedure name> [(parameter values)];

1
2
3
4

COLLECT STATISTICS COLUMN(EmployeeNo) ON Employee;   

-- 查看表的统计信息
HELP STATISTICS employee;

1
2
3

CREATE TABLE xxx (
   name VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC,
);

1
2
3
4
5

SELECT 
   EmployeeNo, 
   COALESCE(dept_no, 'Department not found') 
FROM  
   employee;