常用命令
1、查看数据库 show databases;
2、使用某个数据库默认是default
use mydatabase;
3、查看数据表
show tables;
4、查询数据
select * from tmp limit 1;
内链接查询:SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);
左连接SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
右连接SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
全连接SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id);
5、创建数据表
CREATE TABLE `demo` (
`id` BIGINT,
`title` STRING,
`adddate` TIMESTAMP
)
PARTITIONED BY (date STRING)//以日期作为分区名字
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' //以\001为分隔符
STORED AS TEXTFILE;//以文本的形式存储
6、把本地文件的数据导入到demo表中
//注意:分隔符要保持一致,不然导入后全部都是null值
LOAD DATA LOCAL INPATH '/home/tmp/demo.hql' [OVERWRITE] INTO TABLE `demo`;
7、把查询结果写入新表里面
INSERT OVERWRITE TABLE ..SELECT:新表预先存在
FROM records2
INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year
INSERT OVERWRITE TABLE records_by_year SELECT year, COUNT(1) GROUP BY year
INSERT OVERWRITE TABLE good_records_by_year SELECT year, COUNT(1) WHERE temperature != 9999 AND (quality = 0 OR quality = 1 OR quality = 4 OR quality = 5 OR quality = 9) GROUP BY year;
CREATE TABLE ... AS SELECT:新表表预先不存在
CREATE TABLE target AS SELECT col1,col2 FROM source;
insert overwrite table rpt_chnl_visits_daily_dtl_1 partition(date='{$date}')
select
utm.partner_id partner_id,
utm.group_id group_id,
t1.utmid utm_id,
t1.site_id site_id,
nvl(t1.uv,0) uv_num,
nvl(t1.visit_num,0) visits_num,
nvl(t1.pv,0) pv_num,
nvl(t1.next_click_sids,0) next_click_sids,
nvl(t1.avg_page_num,0) avg_pv_num,
nvl(t1.avg_stay_time,0) avg_stay_time,
nvl(t3.reg_num,0) aegister_users,
nvl(t2.jump_person,0) jump_uv,
nvl(t2.jump_num,0) jump_pv
from tmp_pc_rpt_chnl_visits_daily_dtl_1_1 t1
left outer join tmp_pc_rpt_chnl_visits_daily_dtl_1_2 t2
on t1.utmid = t2.utmid and t1.site_id =t2.site_id
left outer join tmp_pc_rpt_chnl_visits_daily_dtl_1_3 t3
on t1.utmid = t3.utmid and t1.site_id =t3.site_id
left outer join default.js_partner_utm utm
on utm.id = t1.utmid;