大数据Spark “蘑菇云”行动第86课:Hive分区表剖析与代码实战
HIVE的分区类似
如windows 的磁盘分区 C 、D 、E 提高了查询速度。找文件不必每次从C盘查询。D、E不同盘符的查找文件。
hive只加载数据,hadoop 和spark 谁更快?
只load文件,hadoop比spark更快,hadoop load文件时只map过程。
Hive的最佳实践1:采用partitioned分区表+Parquet文件存储的方式!!!
Hive的最佳实践2:一般情况下分区都是按照时间进行的
create external table demo_xxx(userid INT, name STRING, address STRING)
comment 'demo' PARTITIONED BY (txdate STRING, txhour STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' stored as parquet;
create external table person_partitioned(userid INT, name STRING, address STRING)
comment 'demo' PARTITIONED BY (date STRING, hour STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' stored as parquet;
例如 alter table partition_test add partition (stat_date='20110728',province='zhejiang');这个语句是在已经具有的表Table上添加分区字段;
alter table partiton_test DROP
alter table demo_xxx add if not exists partition (txdate='20160730',txhour='0730') location '/data/external/';
alter table partition_test add partition (stat_date='20110728',province='zhejiang') location ....
例如 alter table partition_test add partition (stat_date='20110728',province='zhejiang');这个语句是在已经具有的f分区表Table上添加分区字段数据;
我们创建表的时候说是parquet的格式,现在直接使用外部表location的方式就要求这个location目录下的表必须是parquet格式
create external table student_partitioned(userid INT, name STRING, address STRING)
comment 'demo' PARTITIONED BY (pdate STRING, phour STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' stored as textfile;