Adding Structure with Hive - Microsoft Big Data Solutions - page 139

DROP DATABASE removes a database. This also removes the directory
associated with the database. By default, Hive does not let you drop a
database that contains tables. If you are sure that you want to remove the
database and its tables, you can use the CASCADE keyword. This tells Hive
to remove all contents of the directory. DROP DATABASE also supports the
IF EXISTS clause:
DROP DATABASE MsBigDataAlt;
DROP DATABASE MsBigDataAlt CASCADE;
Finally, you can use the USE command to control what database context will
be used if you don't specify one. You'll find this convenient if you work in a
particular database most of the time:
USE MsBigData;
Creating Tables
The basics of creating a table in Hive are similar to typical SQL, but there
are a number of extensions, particularly for dealing with different file and
record formats. A basic table can be created with the following:
CREATE TABLE MsBigData.customer (
name STRING,
city STRING,
state STRING,
postalCode STRING,
purchases MAP<STRING, DECIMAL>
);
This table holds some basic customer information, including a list of the
customer purchases in a MAP column, where the key is the product name,
and value is the amount paid. To copy the schema for an existing table, you
can use the LIKE keyword:
CREATE TABLE IF NOT EXISTS MsBigData.customer2 LIKE
MsBigData.customer;