hive 基础语法之建表查表

2. Hive基础sql语法

2.1.1 建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

释义：

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
              用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表
•COMMENT可以为表与字段增加描述
•PARTITIONED BY 分区
•CLUSTERED BY 数据汇总
•SORTED BY 按某列排序
•BUCKETS 分桶 #设置词句分桶才有效: set hive.enforce.bucketing=true;
•ROW FORMAT DELIMITED 按分割格式读取文件
	[fields terminated by char]           #每个列字段通过什么分割
	[collection items terminated by char] #array/struct每个键值之间分割符
	[map keys terminated by char]         #每个键值对分隔符
	[lines terminated by char]            #每行之间通过什么分割
	#用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。
	#Hive 通过指定自定义的 SerDe 确定表的具体的列的数据。
•STORED AS 存储为不同文件格式
	[textfile]     #查看：hadoop fs -text 
	[sequencefile] #序列化文件
	[rcfile]       #查看：hive -service rcfilecat path
	[INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname] #自定义的输入\输出流
•LOCATION 在建表的同时指定一个指向实际数据的路径

• 数据编码格式

hive默认通过^A(\001)、^B(\002)、^C(\003)分别对列、(array和struct)、map进行匹配；
# 行格式分隔的字段终止于
row format delimited fields terminated by '\001'
# 集合项终止于
collection items terminated by '\002'
# 映射键终止于
map keys terminated by '\003'
# 行终止于
lines terminated by '\n'	

2.1.2 创建简单表：

CREATE TABLE pokes (foo INT, bar STRING);

2.1.3 创建外部表：

CREATE EXTERNAL TABLE page_view (
viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’,
country STRING COMMENT ‘country of origination’ )
COMMENT ‘This is the staging page view table’
ROW FORMAT DELIMITED fields terminated by ‘\054’
STORED AS TEXTFILE
LOCATION ‘<hdfs_location>’;

2.1.4 建分区表

CREATE TABLE par_table (
viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’)
COMMENT ‘This is the page view table’
PARTITIONED BY(date STRING, pos STRING)
ROW FORMAT DELIMITED ‘\t’
fields terminated by ‘\n’
STORED AS sequencefile;

2.1.5 建分桶表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT ‘IP Address of the User’)
COMMENT ‘This is the page view table’
PARTITIONED BY(date STRING, pos STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED ‘\t’
fields terminated by ‘\n’
STORED AS sequencefile;

2.1.6 创建表并创建索引字段ds

CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

2.1.7 复制一个空表

LIKE 允许用户复制现有的表结构，但是不复制数据

• 1

CREATE TABLE empty_key_value_store LIKE key_value_store;#只是复制表结构

CREATE TABLE empty_key_value_store AS SELECT NAME1 FROM key_value_store;#复制表结构及数据

2.1.8 简单示例：

create table user_info(user_id int, cid string, ckid string, username string)
row format delimited
fields terminated by ‘\t’
lines terminated by ‘\n’;

2.1.9 查看所有数据表/分区/数据库：

SHOW table_name;
SHOW partitions ;
SHOW databases;
•按正条件（正则表达式）显示表
SHOW TABLES ‘.*s’;
•显示表的描述信息
DESC table_name；
DESC extented table_name; #查看扩展信息
DESC formatted table_name;#查看格式化信息
•显示建表的语句
SHOW CREATE TABLE tablename;