Creating schema

Nicola_Cambara

Hi! I have to create a schema in vertica, but I'm a noob of fedora..someone can help me please?
https://github.com/electrum/ssb-dbgen/blob/master/dss.ddl

[Deleted User]

Hi, 

Create schema xxxx;

Hope this is what you are looking for. 

Eugenia

Nicola_Cambara

if i go to terminal, su, then write Create schema xxx; i have this message:
"create: command not found"

[Deleted User]

Because you are tipping it in UNIX. You need to go to vsql. Are you logged in Unix as dbadmin? If so you can do vsql -At -c "create schema xxx;" or run vsql and when you see the prompt => run create schema xxx;

Eugenia

Nicola_Cambara

sorry but I'm too noob, how I can log in as dbadmin?

[Deleted User]

So, you install vertica with the install_vertica script?  Did you create a database using admintools? Can you access to admintools? Because you can also connect to the database using admintools. 

to login as dbadmin as root you have to do su - dbadmin. But if you don't know how to do that, I am not sure if you have a database created. 

I recommend to follow the getting start guide. 
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/GettingStartedGuide/Other/GettingStartedG...

Hope this helps, 

Eugenia

Nicola_Cambara

I've installed by rpm and I've 6.1.2 version..I didn't create a database and I don't know how to do it...unfortunately I'm not english and in the documentation I've found some difficulties

[Deleted User]

If you installed the RPM you just need to create the database. 

So login as dbadmin : su - dbadmin 
and then open admintools and you should be able to create a database, just follow the steps in the menu. To call admintools, when you are logged as dbadmin do /opt/vertica/bin/admintools

Below are the instructions, with this steps should be easier. 

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/Crea...

Good luck, 

Eugenia

Nicola_Cambara

I've tried but I get this: "su: user dbadmin does not exist"

Nicola_Cambara

I've installed by RPM without parameters, so I think I have to create dbadmin user...How I can do it?

Edit: I wrote "/opt/vertica/sbin/install_vertica -u dbadmin" and now I've got dbadmin
I'll try to go on now

Nicola_Cambara

Well, I'm creating table in schema, I've added 6 tables but now I get this error for the last 2:

vertica=> CREATE TABLE prova.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,-- R O_CLERK CHAR(15) NOT NULL,-- R O_SHIPPRIORITY INTEGER NOT NULL,O_COMMENT VARCHAR(79) NOT NULL);
vertica(> )
vertica-> CREATE TABLE prova.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, -- R L_SHIPMODE CHAR(10) NOT NULL, -- R L_COMMENT VARCHAR(44) NOT NULL);
vertica(> )

why?

[Deleted User]

glad that you made some progress.. 

What is this in your statement (-- R ),  Vertica does not understand it. 

Eugenia

Nicola_Cambara

Hi, sorry but I had problems with my internet connections...
I must do a benchmarking between Vertica and Monetdb and in a Vertica forum a boy linked this to me https://github.com/electrum/ssb-dbgen/blob/master/dss.ddl

[Deleted User]

Hi all,

If I may jump in briefly here:

The "-- R" bit is a comment.  In SQL, anything starting with "--" is a comment.  Everything between "--" and the end of the line is comment text, and SQL will ignore it.

Nicola, you have changed where the newlines are in the query.  You are trying to put the comments in the middle of a line.  That doesn't work in SQL; you've commented out most of the query.

Things like CREATE SCHEMA and SQL comments are part of the SQL standard.  They are not Vertica-specific.  You can probably find good SQL documentation online in your native language.  That might be helpful to you.


It sounds like you care about query performance?  Make sure that you run our Database Designer.  It can speed Vertica up a lot.

To run it, first *load some data*.  Then follow these instructions:

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/GettingStartedGuide/UsingDatabaseDeisgner...

(Substitute "VMart" for "TPCD", since your schema is "TPCD".)  We will inspect your data and optimize how it is compressed, sorted, and segmented.

Then load more data, and/or run your queries.  

You can achieve the same thing using CREATE PROJECTION commands.  But those are very complicated and Vertica-specific.  The Database Designer is automatic and much easier.

Adam