- 命令1.hive表中数据导出到MySQL表
/home/queue_ziyan/hive-0.11.0/bin/hive -e "use chengmaidb;set mapred.job.queue.name=queue_chengmai;select * from user_info_20151110;" > /zxinbak/userinfo/userinfo20151110
/home/queue_ziyan/sqoop-1.4.3.bin__hadoop-1.0.0/bin/sqoop export -Dmapred.job.queue.name=queue_chengmai --connect "jdbc:mysql://10.95.196.207/reportdb?useUnicode=true&characterEncoding=UTF-8" --username reportdb --password reportdb --table res_device_oper --export-dir /user/hive/warehouse_zhongxing/chengmaidb.db/res_device_result_20151224 --input-fields-terminated-by "\001"
注释:"\001" 为分隔符
dim_pay_rate mysql数据库reportdb中表,
dim_pay_rate_20150827 hive中表
两表结构相同;
- 命令2.hive表中数据导出,以txt文本格式保存
select * from dm_model_20150910; "> /home/queue_chengmai/temp/dm_model_20151123.txt
注释:hive中表dm_model数据导出到目录/home/queue_chengmai/temp中,以文本形式保存,文件名为dm_model_20150910.txt
导出跳板机 scp /home/queue_chengmai/temp/dm_model_20151123.txt ituser@10.95.196.63:/home/ituser/data/
用户:root 密码:*********
scp /home/queue_chengmai/temp/dm_model_20151110.txt ituser@140.206.176.10:/
注释:将文件dm_model_20150910.txt复制到跳板机,然后下载到本地;
修改后上传: (数据列之间用逗号,)
从跳板机向queue_chengmai发送文件:
scp /home/ituser/data/dm_model_20151122.txt queue_chengmai@10.95.98.201:/home/queue_chengmai/temp
密码:***************
向hive表中上传文件中数据:向hive导入数据(hive下命令)
hive>
load data local inpath '/home/queue_chengmai/temp/dm_model_20151122.txt' overwrite into table dm_model_20150910;
命令3. login列相关
insert into table $tablename_combine
select '$timestring' datadate,get_json_object(result,'$.appid') as appid,get_json_object(result,'$.game') as game,get_json_object(result,'$.sdkversion') as sdkversion,'login_times' tag,count(*) as count_1 from $table_new_log where get_json_object(result,'$.logtype')='login' and length(get_json_object(result,'$.appid'))<1000 and length(get_json_object(result,'$.sdkversion'))< 1000 and length(get_json_object(result,'$.game'))< 1000 group by get_json_object(result,'$.appid'),get_json_object(result,'$.game'),get_json_object(result,'$.sdkversion');
hive命令:hive
-e <quoted-query-string> SQL from command line-f <filename> SQL from files
-i <filename> Initialization SQL file
例子:执行一个sql语句:
$HIVE_HOME/bin/hive -e 'select a.col from tab1 a'
设置队列: use chengmaidb;
set mapred.job.queue.name=queue_chengmai;
1、显示表
hive> show tables;
模糊搜索表:show tables like '*name*';
2、创建表
hive> CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name;
例:create table blacklist03 (phone string,pay_date string) row format delimited fields terminated by ',' stored as textfile;
Load data local inpath '/home/queue_ziyan/temp/blacklist03.csv' Overwrite into table blacklist03;
注释:以逗号,为分隔符: row format delimited fields terminated by ','
保存为文本 stored as textfile;
3.创建外部表
表名:dw_cp_account_log_day
CREATE EXTERNAL TABLE dw_cp_account_log_day(
imsi string,
userid string,
appid string,
versioncode string,
versionname string,
result string,
sdkversion string,
errorcode string,
currstarttime string,
datetime string,
net string,
ip string,
productindex string,
cpid string)
PARTITIONED BY (
dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION 'hdfs://10.95.198.29:19000/user/hive/warehouse_ziyan/cp.db/dw_cp_account_log_day';
表中无数据,
执行:select * from dw_cp_account_log_day_test limit 20;
显示分区:show partitions dw_cp_account_log_day_test;均为空
增加分区:alter table dw_cp_account_log_day_test add partition (dt='20151209') location '/user/hive/warehouse_ziyan/cp.db/dw_cp_account_log_day/dt=20151209';
则有了所增加分区的数据;
4.查看表的结构,表中字段及数据类型
hive> desc table_name; 表结构
desc formatted table_name; 详细信息
5.复制表的结构,like
复制表dm_model的结构到dm_model_20150910;
create table dm_model_20150910 like dm_model;
6.查看分区信息
hive> show partitions table_name;
7.根据分区查询数据
hive>select table_coulm from table_name where partition_name =‘2014-02-25‘;
select * from dm_model_20150910 limit 200 ; ##显示表的前200行
8、创建分区表:
hive> create table logs(ts bigint,line string) partitioned by (dt String,country String);
9.删除分区
alter table table_name drop partition(dt='${etl_date}');
例:alter table dw_cp_login_log_day drop partition(dt='20151019');
10.增加分区
ALTER TABLE dw_cp_pay_log_day DROP IF EXISTS PARTITION (dt='$timed'); 增加前判断是否存在
alter table dw_cp_pay_log_day add partition (dt='$timed') location '/user/hive/warehouse_ziyan/cp.db/dw_cp_pay_log_day/dt=$timed';
11、加载分区表数据:
hive> load data local inpath '/home/Hadoop/input/file1' into table logs partition (dt='2014-03-11',country='CN');
12.显示hive提供的函数:
hive> show functions;
12.导入数据
load data local inpath '/home/queue_chengmai/temp/dm_model_20150910.txt' overwrite into table dm_model_20150910;
load data local inpath '/home/queue_chengmai/temp/channel_id.txt' overwrite into table channel_id;
13.将hive查询结果导出到本地文件
insert overwrite local directory '/home/queue_chengmai/bin/test'
select imsi,count(imsi) from device_log_oper where oper='other'and dt='20151103' group by imsi;
注释:导出到目录下/home/queue_chengmai/bin/test,下一句为查询语句