(7-8)hive数据库、表、字段定义

(7-8)hive数据库、表、字段定义

--------------------------------------------------------------1、数据库的定义-----------------------------------------------------------

1.1、创建数据库：

hive> show databases;
OK
default
Time taken: 1.205 seconds, Fetched: 1 row(s)
hive> create database mydb;
OK
Time taken: 0.845 seconds
hive> create database if not exists mydb;
OK
Time taken: 0.199 seconds
hive> show databases;
OK
default
mydb
Time taken: 0.039 seconds, Fetched: 2 row(s)
hive>


1.2、显示数据库字段
hive> describe database mydb;
OK
mydb            hdfs://192.168.1.10:9000/user/hive/warehouse/mydb.db    root    USER
Time taken: 0.052 seconds, Fetched: 1 row(s)
hive> describe database extended mydb;
OK
mydb            hdfs://192.168.1.10:9000/user/hive/warehouse/mydb.db    root    USER
Time taken: 0.04 seconds, Fetched: 1 row(s)
hive>



1.3、数据库里有表，删除数据库需要加上 cascade 。

hive> use mydb;
OK
Time taken: 0.053 seconds
hive> create table t1(name string);
OK
Time taken: 0.782 seconds
hive> drop database mydb;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database mydb is not empty. One or more tables exist.)
hive> drop database mydb cascade;
Moved: 'hdfs://192.168.1.10:9000/user/hive/warehouse/mydb.db/t1' to trash at: hdfs://192.168.1.10:9000/user/root/.Trash/Current
Moved: 'hdfs://192.168.1.10:9000/user/hive/warehouse/mydb.db' to trash at: hdfs://192.168.1.10:9000/user/root/.Trash/Current
OK
Time taken: 26.061 seconds
hive> show databases;
OK
default
Time taken: 0.033 seconds, Fetched: 1 row(s)
hive>



--------------------------------------------------------------2、表的定义-----------------------------------------------------------


hive> create table t1(name string);
OK
Time taken: 0.24 seconds
hive> describe t1;
OK
name                    string
Time taken: 0.231 seconds, Fetched: 1 row(s)
hive> describe extended t1;
OK
name                    string

Detailed Table Information      Table(tableName:t1, dbName:mydb, owner:root, createTime:1430225484, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:name, type:string, comment:null)], location:hdfs://192.168.1.10:9000/user/hive/warehouse/mydb.db/t1, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{transient_lastDdlTime=1430225484}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)
Time taken: 0.213 seconds, Fetched: 3 row(s)
hive>


只复制表结构，数据没有复制。适合做测试，不对原数据产生影响。
hive> show tables;
OK
t1
Time taken: 0.049 seconds, Fetched: 1 row(s)
hive> create table t2 like t1;
OK
Time taken: 0.178 seconds
hive> desc t2;
OK
name                    string
Time taken: 0.183 seconds, Fetched: 1 row(s)
hive> show tables;
OK
t1
t2
Time taken: 0.043 seconds, Fetched: 2 row(s)
hive>





--------------------------------------------------------------3、表字段的定义-----------------------------------------------------------

3.1、修改列的名称、类型、位置、注释。
把 t1表字段 name 改为 username，指定类型 string：
hive> alter table t1 change column name username string;
OK
Time taken: 0.362 seconds
hive> desc t1;
OK
username                string
Time taken: 0.172 seconds, Fetched: 1 row(s)
hive>


3.2、增加字段：
hive> alter table t1 add columns(age int,score int);
OK
Time taken: 0.482 seconds
hive> desc t1;
OK
username                string
age                     int
score                   int
Time taken: 0.176 seconds, Fetched: 3 row(s)
hive>


3.3、替换原表字段：
hive> alter table t1 replace columns(baozi string);
OK
Time taken: 0.477 seconds
hive> desc t1;
OK
baozi                   string
Time taken: 0.217 seconds, Fetched: 1 row(s)
hive>