Partitioning a database

Hi,

After creating a database in Omniscidb, can I partition the database into different groups e.g. say Developing, Testing etc. Not sure if I am using the term ‘partition’ correctly here, but the intention is to further divide a database into smaller environments.

Thank you
Dipanwita.

Hi @dipanwita2019,

With “partition the database,” do you mean to create schemas into a specific database, where you want to create a new set of objects (tables or views) as in DB2/Sql Server?

Right now, it’s not possible, while, if I remember correctly, this feature is on the radar.
You can get almost the same results creating multiple databases, one for each environment, and giving the users/groups the right permissions.

To do this, connect to omnisci database with omnisql
and create the database

omnisql> create database myapp_dev;
omnisql> create database myapp_tst;
omnisql> create database myapp_prd;

then create the roles for developers, testers, and users, and grant the needed privileges on databases

omnisql> create role r_myapp_dev;
omnisql> create role r_myapp_tst;
omnisql> create role r_myapp_usr;
omnisql> grant CONNECT, SELECT, INSERT, TRUNCATE, UPDATE, DELETE, CREATE TABLE, CREATE VIEW, SELECT VIEW, DROP, DROP VIEW on database myapp_dev to r_myapp_dev;
omnisql> grant CONNECT, SELECT, SELECT VIEW on database myapp_tst to r_myapp_dev;
omnisql> grant CONNECT, SELECT, INSERT, TRUNCATE, UPDATE, DELETE, CREATE TABLE, CREATE VIEW, SELECT VIEW, DROP, DROP VIEW on database myapp_tst to r_myapp_tst;
omnisql> grant CONNECT, SELECT, INSERT, UPDATE, DELETE, SELECT VIEW on database myapp_dev to r_myapp_tst;
omnisql> grant CONNECT, SELECT, SELECT VIEW on database myapp_prd to r_myapp_usr;

So we are granting almost everything (we could use ALL privilege) to the developer role into the dev database, the same for testers in the test database, and the possibility to read data between database, so the developers can take a look at what is reported by the testing group; to the normal user is grant the read-only access to the production database.

The administrators can move subsets of data between database with COPY FROM / COPY TO command or entire table with DUMP TABLE / RESTORE TABLE.

Regards

1 Like