How to verify, that a DB hive already exists?

Question

We create a hive database using expect script and with some other commands.

In the case if we run the expect script again on the machine that hive already created, we get this:

ERROR hive already exists

How can we check if database hive already created before creating it again?

And by this verification we can escape the expect script:

# su - postgres
 Last login: Sun Aug 13 11:12:03 UTC 2017 on pts/0
 -bash-4.2$ psql
 psql (9.2.13)
 Type "help" for help.

postgres=# CREATE DATABASE hive;
ERROR:  database "hive" already exists

My expect script: (from my bash script)

set timeout -1

#exec the scripts

spawn timeout 60 ssh root@IP
expect "#"
spawn su - postgres
expect "$"
send "psql\n"
expect "=#"
send "CREATE DATABASE hive;\n"
.
.
.
.

Other example:

postgres=# CREATE DATABASE [IF NOT EXISTS] hive;
ERROR:  syntax error at or near "["
LINE 1: CREATE DATABASE [IF NOT EXISTS] hive;
                    ^

I suggest to check the rules, when should you use "the" or "a". It would significantly improve your English (it doesn't make your posts incomprehensible, but anybody having a first language with articles, will have to think about what you tried to say. So you lose a lot of good answers.) — peterh, Aug 21 '17 at 16:08

Yaron · Accepted Answer · 2017-08-13T14:13:25.927

1

If you would like to use Hive database, try using hive directly (see below)

PostgreSQL answer

Stack overflow Q&A Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

Specify that PostgreSQL doesn't have build in support for IF NOT EXISTS (as hive have)

One of the simple answer mentioned there is: query the database, and only if the query fail - create the database.

psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'my_db'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE my_db"

Hive answer:

You should use the command with the option [IF NOT EXISTS]:

CREATE DATABASE [IF NOT EXISTS] <database name>

More info

Create Database Statement

Create Database is a statement used to create a database in Hive. A database in Hive is a namespace or a collection of tables. The syntax for this statement is as follows:
CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>
Here, IF NOT EXISTS is an optional clause, which notifies the user that a database with the same name already exists. We can use SCHEMA in place of DATABASE in this command. The following query is executed to create a database named userdb:
hive> CREATE DATABASE [IF NOT EXISTS] userdb;
or
hive> CREATE SCHEMA userdb;

edited Aug 13 '17 at 14:13

answered Aug 13 '17 at 14:00

Yaron

4,229
2
20
33

hi yaron , I create the hive DB from postgres not from hive – yael Aug 13 '17 at 14:04
@yael - why won't you create hive DB using `hive`? – Yaron Aug 13 '17 at 14:05
see the other example in my question – yael Aug 13 '17 at 14:05
@yael - why won't you create hive DB using `hive` instead of using `postgres`? – Yaron Aug 13 '17 at 14:06
because this is the code ( I only want to improve it in case of second running – yael Aug 13 '17 at 14:07
is there any other option only from postgres ? – yael Aug 13 '17 at 14:12
@yae - I've updated my answer to include two options - one using **PostgreSQL** and one with **hive** – Yaron Aug 13 '17 at 14:14
Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/63746/discussion-between-yael-and-yaron). – yael Aug 13 '17 at 14:15

How to verify, that a DB hive already exists?

1 Answers1