sequoiadb四颗牙P 第五节 参考

1）切换到 sdbadmin 用户；


2）创建 company_domain 数据域，包含 group1、group2、group3 三个复制组；
sdb
var db = new Sdb("localhost", 11810);
db.createDomain("company_domain", [ "group1", "group2", "group3" ], { AutoSplit: true } );
 
3）创建 company 集合空间，所属 company_domain 数据域；
db.createCS("company", { Domain: "company_domain" } );


4）创建 employee 集合；
db.company.createCL("employee_bak", { "ShardingKey": { "empno": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );


5）创建实例 pginst，端口使用 5432；
cd /opt/sequoiasql/postgresql/
/opt/sequoiasql/postgresql/bin/sdb_sql_ctl addinst pginst -D database/5432
/opt/sequoiasql/postgresql/bin/sdb_sql_ctl start   pginst


6）配置连接 PostgreSQL 实例与 SequoiaDB 巨杉数据库存储引擎（transaction 设置为 on ，默认开启事务）；
 
/opt/sequoiasql/postgresql/bin/sdb_sql_ctl createdb company pginst
/opt/sequoiasql/postgresql/bin/psql -p 5432 company


CREATE EXTENSION sdb_fdw;
CREATE SERVER sdb_server FOREIGN DATA WRAPPER sdb_fdw 
OPTIONS 
(
address '127.0.0.1', 
service '11810', 
user '', 
password '', 
preferedinstance 'A', 
transaction 'on' 
);


7）在 PostgreSQL 创建 company 数据库和 employee 外部表 ( id INT, name TEXT, age INT ) 映射到 SequoiaDB 数据库的 company.employee 集合；


CREATE FOREIGN TABLE employee 
(
id INTEGER,
name TEXT,
age INTEGER
) 
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee', decimal 'on' );


8）向 employee 表插入数据 ( id: 10001, name: 'Jerry', age: 18 )；
INSERT INTO employee VALUES (10001, 'Jerry', 18); 


9）开始事务，向 employee 表插入数据 ( id: 10002, name: 'Tom', age: 20 )，回滚事务，然后查询 employee 表；
BEGIN;
INSERT INTO employee VALUES (10002, 'Tom', 20); 
rollback;


10）将 employee 表数据导出到 “/opt/sequoiasql/postgresql/employee.csv”，使用 “\! more /opt/sequoiasql/postgresql/employee.csv” 命令查看数据内容；
COPY (SELECT * FROM employee) TO '/opt/sequoiasql/postgresql/employee.csv' with delimiter ',' csv;
\! more /opt/sequoiasql/postgresql/employee.csv


11）在 company 集合空间创建集合 employee_bak, 并将上一步导出的数据文件导入到 employee_bak 中；


sdb
var db=new Sdb('localhost',11810);
db.company.createCL("employee_bak", { "ShardingKey": { "id": 1 }, "ShardingType": "hash", "ReplSize": -1, "Compressed": true, "CompressionType": "lzw", "AutoSplit": true, "EnsureShardingIndex": false } );


CREATE FOREIGN TABLE employee_bak 
(
id INTEGER,
name TEXT,
age INTEGER
) 
SERVER sdb_server
OPTIONS ( collectionspace 'company', collection 'employee_bak', decimal 'on' );


sdbimprt --hosts=localhost:11810 --type=csv --file=/opt/sequoiasql/postgresql/employee.csv --fields="id int, name string, age int"  -c company -l employee_bak