hive篇-----【Hive基础】_hive in 和like

hive数据类型

hive基本数据类型

hive复杂类型

常用命令

# 查询数据库
show databases;
 
# 查询表
show tables;
 
#使用数据库
use database_name ;
 
# 查看表结构
desc table_name;

创建表

创建表的格式

格式：
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]
 
# 字段解释：
[external] # 申明是否为外部表
 
[if not exists] # 如果表存在，则不创建了
 
[(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] # 在clustered by 里的字段上建立 num_buckets个桶，记录是由 sorted by里的字段排序的。
 
[ROW FORMAT row_format] # 指定分隔符，可以是以下几个：
        : DELIMITED [FIELDS TERMINATED BY char] 
                    [COLLECTION ITEMS TERMINATED BY char] 
                    [MAP KEYS TERMINATED BY char] 
 
[STORED AS file_format] # 指定load文件的类型，分为：
            textfile 是 纯文本文件
            sequence 是 压缩文件
 
[LOCATION hdfs_path] # 向表里加载数据，hdfs_path是一个hdfs上的目录，不能是文件，hive会依据默认配置的hdfs路径，自动将整个目录下的文件都加载到表中。

创建表的实例

创建一个简单的内部表

create table if not exists T1(
    id int,
    name string,
    salary double comment 'comment是字段注释'
)comment 'comment是表注释'
row format delimited
fields terminated by ','
stored as textfile;

创建外部表

create external table if not exists T1(
    id int,
    name string,
    salary double comment 'comment是字段注释'
)comment 'comment是表注释'
row format 
delimited  fields terminated by ','
stored as textfile;

使用复杂类型创建表

create external table if not exists T2(
    id int,
    course array<string>,
    score  map<string,int>
)
row format
delimited fields terminated by ',' 
          collection items terminated by '|'
          map keys terminated by ':'
stored as textfile;
# 数据文件内容
1001,语文|数学|英语,语文|56,语文:102|数学:2033|英语:30
1002,语文|数学|英语,语文|156,语文:120|数学:2033|英语:30
1003,语文|数学|英语,语文|1156,语文:210|数学:3320|英语:30
1004,语文|数学|英语,语文|1156,语文:2210|数学:203|英语:30
1005,语文|数学|英语,语文|5116,语文:22210|数学:230|英语:30
# 导入数据文件
load data local inpath '/home/datanode/hiveTest/test01' overwrite  into table t2;

创建一个带分区的内部表

create table if not exists T3(
    id int,
    name string
)
partitioned by (classid int)
row format
delimited fields terminated by ','
stored as textfile;

创建一个带桶的内部表

create table T4(
    id int ,
    name string,
    sex string,
    age int
)
partitioned by (city string)
clustered by(age) sorted by(name) into 5 buckets
row format 
delimited fields terminated by ','
stored as textfile;

加载数据

加载数据的格式

load 方式加载数据

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]  
 
#字段解释
[LOCAL] # 加载的是否是本地文件
 
[OVERWRITE] # 是否覆盖表里原来的数据
 
[PARTITION (partcol1=val1, partcol2=val2 ...)]   # 如果表存在分区，指定加载到那个分区

insert 方式加载数据

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement  
 
#字段解释
[PARTITION (partcol1=val1, partcol2=val2 ...)] # 如果表存在分区，指定加载到那个分区

加载数据的实例

加载本地文件

LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1  

•  

加载hdfs上的文件

LOAD DATA  INPATH '/user/admin/test/test.txt' OVERWRITE INTO TABLE test_1  

•  

用insert加载数据

insert overwrite table t5 select * from t2;
 
insert into table t5 select * from t2;

查询语句与常用命令

查询格式

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[   CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
 
# 字段解释
[ALL | DISTINCT] # 查询的字段
 
[WHERE where_condition] # where子句
                     # 类似我们传统SQL的where 条件
                     # 目前支持 AND,OR ,0.9版本支持between
                     # IN, NOT IN
                     # 不支持EXIST ,NOT EXIST
 
[GROUP BY col_list [HAVING condition]] # 字段分组和having子句
 
 [SORT BY| ORDER BY col_list]  # 排序
                        # ORDER BY与SORT BY的不同
                        # ORDER BY 全局排序，只有一个Reduce任务
                        # SORT BY 只在本机做排序
 
[LIMIT number] # 限制查询记录数

查询实例

# 查询t2表的数据
select * from t2;
 
# 查询10条t2表的数据
select * from t2 limit 10;

连接语句

hive 多表连接使用 join…on 语句
hive 只支持等值连接，即 on子句中只能使用等号连接
如果连接语句中有where子句，会先执行join子句，再执行where子句。

内连接

select * from t1 join t2 on t1.id = t2.id;

•  

左外连接

select * from t1 left join t2 on t1.id=t2.id;

•  

右外连接

select * from t1 right join t2 on t1.id=t2.id;

•  

全外连接

select * from t1 outer join t2 on t1.id=t2.id;

• 1

左半连接

select * from t1 left semi join t2 on t1.id=t2.id;
# 等价于
selct * from t1 where t1.id in (select t2.id from t2);

in / not in / like / not like

hive 0.9.0版本开始支持 in , not in , like . not like
用法与SQL的 in , not in , like . not like的作用相同，就不在多说了。

修改表结构 : alter

# 修改表名
ALTER TABLE t1 RENAME TO 3koobecaf;
 
# 增加一个字段
ALTER TABLE t1 ADD COLUMNS (new_col INT);
 
# 增加一个字段，带注释
ALTER TABLE t1 ADD COLUMNS (new_col2 INT COMMENT 'a comment');
 
# 将表的字段全部替换为新的字段
ALTER TABLE t1 REPLACE COLUMNS (foo INT, bar STRING, baz INT);

函数

数字函数

收集函数

日期函数

字符串函数

聚合函数

单次执行语句

# 不进入命令行，直接执行一次命令
hive -e 'select * from test05';
 
#将查询到的内容放到a.txt中
hive -S -e 'select * from test05' >> a.txt

应用文章：

HIVE函数参考手册
http://www.cnblogs.com/end/archive/2012/06/18/2553682.html

HIVE入门和实战
http://wenku.baidu.com/link?url=f07hgey5586Kw7HjN1y6gOW4m0ofmIoi7FI3RjXN1TxRglCnDApVVWgDS-gbqrMiLcgTsqm5Q1OCgCyVJswKCIrcGxrWL6ew4gcfQyXjPu3

HIVE函数
http://www.cnblogs.com/end/archive/2012/06/18/2553682.html