Hive使用集合

删除数据库

drop database tmp cascade;

合并函数

select product_id, concat_ws('_',collect_set(promotion_id)) as promotion_ids 
from product_promotion group by product_id;  

压缩设置

set hive.exec.compress.intermediate=true; 
set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec 
set hive.exec.compress.output=true; 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GZipCodec

#导入txt文件，以\t分割字段，不需要标题

load data local inpath 'tmp/tb1.txt' into table tb1;

#查看文件

dfs -ls /user/hive/warehouse/tb1;

#导入dfs里面的文件

vi tmp/tb1_2.txt
hadoop fs -put tmp/tb1_2.txt /user/hive/warehouse/tb1
hadoop fs -ls /user/hive/warehouse/tb1
load data inpath '/user/hive/warehouse/tb1/tb1_2.txt' into table tb1;

#查看数据存放位置

describe database test;

#创建taxdw数据库

create database taxdw;

#删除数据库加目录

DROP DATABASE test CASCADE;  

#显示数据库信息

describe database taxdw;

#删除表

DROP TABLE G3_DJ_NSRXX_0;

#导入表

/home/sqoop-1.4.6/bin/sqoop.sh import --connect jdbc:oracle:thin:@0.0.0.0:1521:orcl --username taxdwdev --password 1111 --verbose -m 1 --table G3_DJ_NSRXX_0 --hive-database taxdw --hive-import

#hive进入

/home/apache-hive-2.1.0/bin/hive
use taxdw;
show tables;
select * from G3_DJ_NSRXX_0 limit 5;

#导入命令

/home/sqoop-1.4.6/bin/sqoop.sh import --connect jdbc:oracle:thin:@0.0.0.0:1521:orcl --username taxdwdev --password 1111 --verbose -m 1 --table G3_DM_GY_SWRY --hive-database taxdw --hive-import
/home/sqoop-1.4.6/bin/sqoop.sh import --connect jdbc:oracle:thin:@0.0.0.0:1521:orcl --username taxdwdev --password 1111 --verbose -m 1 --table G3_DM_GY_SWJG --hive-database taxdw --hive-import

#命令界面

/usr/hadoop/hive/bin/hive
show databases;
use xx;
show tables;
create database test;
create table tb1(id int,name string)row format delimited fields terminated by '\t';
describe tb1;
desc formatted tb1;
exit;
vi kv.txt 
    zhang
    zhang2
    zhad3
load data local inpath '/usr/hadoop/kv.txt' overwrite into table tb1;
select * from tb1;

select t1.* from (select t.*,row_number() over (order by 1 desc) as rn from G3_DJ_NSRXX_0 t)t1 where rn between 10 and 20;

CREATE TABLE IF NOT EXISTS aa_1(a string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
vi /tmp/tax/aa_1.txt
load data local inpath '/tmp/tax/aa_1.txt' overwrite into table aa_1;

#查看视图内容

 desc formatted VM_387850;