How to Create Vertica Schema on the Fly

In this short article i will go over the steps used to create a simple Vertica UDP (user defined procedure) that will help us create a new schema and also create the database roles in a single SQL command.

This will help you work faster and spend less time writing simple SQL code.

Normally when i create schema i make sure that the default role also come with it.

My Default roles:

  • this is optional, i only do this to avoid the headache of not being able to see who has access to what.
  • i never give direct access to a user, always prefer role to propagate.

<schema_name>_datareader – grant select rights to all objects in this schema.

<schema_name>_datawriter – grant insert,update,delete rights to all objects in this schema.

<schema_name>_schemausage – grant schema usage on this schema.

Here is the content of my .profile file.

  • the main reason i use this approach is to avoid clear text password and this is a serious security issue.
  • the file can only be accesed by the dbadmin user.


The content:

  • the location of the should be inside the default procedure folder of your catalog.

  • the script will user the username and password that is passed by the .profile file and will execute the SQL script attaching the parameter value we will pass to the Procedure at execution time.
Similar Article  Script to Extract a number from a text string in Vertica

Make sure you run the following command when you create the script:


Create the UDP using the admintools

Create the Procedure in your database

  • i normally have specific schema for my own use that i call dba, here i store all my procedures and all other tables & views used to manage the database.

  • i called my procedure add_schema, i am trying to make it as obvious as possible.

After the procedure add_schema was created try to see if it works.

  • See if schema was created.

  • See if the roles have been created.

  • See if the roles have the right grants on the new schema.

Ok, that looks good, so the procedure work well.

Similar Article  12 - Linux Processes Control

Feel free to use it and make it better.



I hope this was helpful and i appreciate if you would like, subscribe or share our posts. I it is just a click


No Comments
Leave a Comment

%d bloggers like this: