持续更新中,是用来应对ETL基本操作的贴贴板和一点笔记。
旨在把变量名改掉快速使用语句结构。
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]
1.1 如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXIST 选项来忽略这个异常。COMMENT后接字段描述。
1.2 ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
1.3 如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE 。ORC hive给出的新格式,属于RCFILE的升级版。使用Stored as orc;
2.PARTITION:
基本操作:
1.概念
Hive分区更方便于数据管理,常见的有时间分区和业务分区。
分区建表分为2种,一种是单分区,也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区,表文件夹下出现多文件夹嵌套模式,即多分区的顺序性。
a. 单分区建表语句:create table day_table (id int, content string) partitioned by (dt string);单分区表,按天分区,在表结构中存在id,content,dt三列。这里分区字段不能和表中的字段重复,重复了会报错。
b. 双分区建表语句:create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表,按天和小时分区,在表结构中新增加了dt和hour两列。载入数据时必须两个分区标识都给,不然会报错。
2.操作
2.1 载入文件带着分区标识:
load data local inpath '/home/hadoop/Desktop/data' overwrite into table t1 partition ( pt_d = '201701');
2.2 基于分区的查询:
SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';
2.3 添加一个分区(分区文件):
alter table t1 add partition (pt_d = ‘333333’);
2.4 往指定分区中嵌入数据:
* 关键字
1)由于使用了OVERWRITE关键字,目标表中原来相同partition中的所有数据被覆盖,如果目标表中没有partition,则整个表会被覆盖。
如果把OVERWRITE关键字删掉,或者替换成INTO,则hive会追加而不是替代原分区或原表中的数据,这个特性在Hive v0.8.0之后才支持。
2)INSERT INTO SELECT
我们可以从一个表中复制所有的列插入到另一个已存在的表中:
INSERT INTO table2
SELECT * FROM table1;
或
INSERT INTO table2(column_name(s))
SELECT column_name(s) FROM table1;
*select into from 和 insert into select 都是用来复制表
两者的主要区别为: select into from 要求目标表不存在,因为在插入时会自动创建;insert into select from 要求目标表存在。
SELECT column_name