创建表:
创建内部表 create table dept ( deptno int, dname string, loc int ) row format delimated fields terminated by ',' ; --分隔符按照自己文件数据来设置 create table dept2 like dept; --拷贝表结构,不拷贝数据 create table dept3 as select * from dept; --拷贝表结构和数据导入数据
load data local inpath 'dept_data.txt' into table dept --从本地(linux)增量导入表中 load data local inpath 'dept_data.txt' overwrite table dept --从本地将数据覆盖写入表中 load data inpath 'hdfs://hadoop001:8020/data/dept_data.txt' into table --将数据从HDFS导入表中 创建外部表 create external table ( deptno int, dname string, loc int ) row format delimated fields terminated by ',' location '/data/' ; --指定数据存放路径,如果路径中有数据文件,会自动导入表中内部表与外部表的区别:删除内部表时,会将元数据与数据全部删除(大多用于临时表),而外部表删除时只会删除元数据,不会删除数据
分区表:数据会在一个表中越来越大,性能也会越来越低,这时候就要用到分区表 创建分区表 create table dept ( deptno int, dname string, loc int ) partition by (deptno int) row format delimated fields terminated by ',' ; load data local inpath 'dept_data' into table dept partition (dept='xxx'); --导入数据 -------创建多级分区------------------ create table order_mulit_partiton( order_no string, order_time string ) PARTITIONED BY (event_month string, step string) row format delimited fields terminated by '\t'; load data local inpath '/home/hadoop/data/order_created.txt' into table order_mulit_partiton PARTITION (event_month='2014-05', step='1'); ---导入数据动态分区:
-------创建分区表--------------- create table emp_dynamic_partition( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double ) PARTITIONED BY (deptno int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; insert overwrite table emp_dynamic_partition partition(deptno) --导入数据 order by,sort by,distribute by,cluster by的区别 order by:全局排序,只有一个reduce,执行数据慢,严格下用order by必须加limitsort by:局部排序distribute by:将一定规则的数据分发到某个reduce中