hive 数据定义

1:  CREATE DATABASE IF NOT EXISTS financials;

2: script:

  2.1: hive> SHOWDATABASES;

default

financials

hive> CREATEDATABASE human_resources;

hive> SHOWDATABASES;

default

financials

human_resources

  2.2:hive> SHOWDATABASES LIKE'h.*';

human_resources

hive> ...

  2.3: hive> CREATE DATABASE financials  LOCATION '/my/preferred/directory';  (now the table should be saved in the location  path )

hive> CREATEDATABASE financials  COMMENT 'Holds all financial tables';

hive> DESCRIBEDATABASE financials;

financials Holds all financial tables

hdfs://master-server/user/hive/warehouse/financials.db

2.4: hive> CREATE DATABASE financials  WITH DBPROPERTIES ('creator' = 'Mark Moneybags', 'date' = '2012-01-02');
hive> DESCRIBE DATABASE financials;
financials hdfs://master-server/user/hive/warehouse/financials.db
hive> DESCRIBE DATABASE EXTENDED financials;
financials hdfs://master-server/user/hive/warehouse/financials.db
{date=2012-01-02, creator=Mark Moneybags);

  2.5: 

hive> set hive.cli.print.current.db=true;
hive (financials)> USE default;
hive (default)> set hive.cli.print.current.db=false;
hive> ...
Finally, you can drop a database:
hive> DROP DATABASE IF EXISTS financials;

2.6: 

By default, Hive won’t permit you to drop a database if it contains tables. You can either drop the tables first or append the CASCADE keyword to the command, which will cause the Hive to drop the tables in the database first: 

hive> DROP DATABASE IF EXISTS financials CASCADE;

3: Alter Database

  3.1: hive> ALTER DATABASE financials SET DBPROPERTIES ('edited-by' = 'Joe Dba');

  3.2: Creating Tables

CREATE TABLE IFNOT EXISTS mydb.employees(

name STRING COMMENT 'Employee name',

salary FLOATCOMMENT 'Employeesalary',

subordinates ARRAY<STRING>COMMENT 'Namesof subordinates',

deductions MAP<STRING,FLOAT>

COMMENT 'Keys aredeductions names, values are percentages',

address STRUCT<street:STRING,city:STRING, state:STRING,zip:INT>

COMMENT 'Home address')

COMMENT 'Description of thetable'

TBLPROPERTIES ('creator'='me','created_at'='2012-01-0210:00:00', ...)

LOCATION'/user/hive/warehouse/mydb.db/employees';

  3.3: You can also copy the schema (but not the data) of anexisting table:

CREATE TABLE IF NOT EXISTS mydb.employees2

LIKE mydb.employees;

  3.4: 

hive> USE mydb;

hive> SHOWTABLES;

employees

table1

table2

3.5:

 

hive> USE mydb;

hive> SHOW TABLES  'empl.*';

employees

3.6:

hive> DESCRIBE EXTENDED mydb.employees;

name string Employee name

salary float Employee salary

subordinates array<string> Names of subordinates

deductions map<string,float> Keys are deductionsnames, values are percentages

addressstruct<street:string,city:string,state:string,zip:int> Home address

Detailed Table Information Table(tableName:employees,dbName:mydb, owner:me,

...

location:hdfs://master-server/user/hive/warehouse/mydb.db/employees,

parameters:{creator=me, created_at='2012-01-02 10:00:00',

last_modified_user=me, last_modified_time=1337544510,

comment:Description of the table, ...}, ...)

3.7: 

hive> DESCRIBE mydb.employees.salary;

salary float Employee salary