hive表定义

来源：互联网发布：网络征婚投资编辑：程序博客网时间：2024/06/17 19:11

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) ON ([(col_value, col_value, ...), ...|col_value, col_value, ...])
[STORED AS DIRECTORIES] ]
[ [ROW FORMAT row_format]
[STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] ]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement] (Note: not supported when creating external tables.)

显示所有表

hive>SHOW TABLES [in 数据库名];

hive>CREATE TABLE t2 LIKE t1;

实际开中，担心影响原来的表，可以用like命令，复制个表，然后再对复制表操作。

hive>DESC/DESCRIBE [formatted] t2;

hive (zmgdb)> describe formatted t1;
OK
col_name data_type comment
# col_name data_type comment

str string

# Detailed Table Information
Database: zmgdb
Owner: hadoop
CreateTime: Wed Sep 14 21:27:59 CST 2016
LastAccessTime: UNKNOWN
Retention: 0
Location: hdfs://hello110:9000/user/hive/warehouse/zmgdb.db/t1
Table Type: MANAGED_TABLE
Table Parameters:
numFiles 1
numRows 0
rawDataSize 0
totalSize 209
transient_lastDdlTime 1473860579

# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.065 seconds, Fetched: 29 row(s)

0 0