技术标签: java hive 查询语句
packageasia.wildfire.hive.service;
importjava.sql.*;
importjava.sql.Date;
importjava.text.SimpleDateFormat;
importjava.util.*;
/**
* User: liuxiaochen
* Date: 13-9-24
* Time: 下午5:47
* 修改描述
*/
publicclassHiveService {
privatestaticfinalString URLHIVE ="jdbc:hive://ip:50000/default";
privatestaticConnection connection =null;
publicstaticConnection getHiveConnection() {
if(null== connection) {
synchronized(HiveService.class) {
if(null== connection) {
try{
Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");
connection = DriverManager.getConnection(URLHIVE, "","");
} catch(SQLException e) {
e.printStackTrace();
} catch(ClassNotFoundException e) {
e.printStackTrace();
}
}
}
}
returnconnection;
}
publicstaticvoidcreateTable()throwsSQLException {
String tweetTableSql = "DROP TABLE IF EXISTS hive_crm_tweet2222";
String createTable1 = "CREATE EXTERNAL TABLE hive_crm_tweet2222(tweet_id string, cuser_id string, created_at bigint, year bigint, month bigint, day bigint, hour bigint, text string, comments_count bigint, reposts_count bigint, source string, retweeted_id string, post_type string, sentiment string, positive_tags_string string, predict_tags_string string, tags_string string) STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' TBLPROPERTIES (\"dynamodb.table.name\" = \"crm_tweet\",\"dynamodb.column.mapping\" = \"tweet_id:tweet_id,cuser_id:cuser_id,created_at:created_at,year:year,month:month,day:day,hour:hour,text:text,comments_count:comments_count,reposts_count:reposts_count,source:source,retweeted_id:retweeted_id,post_type:post_type,sentiment:sentiment,positive_tags_string:positive_tags_string,predict_tags_string:predict_tags_string,tags_string:tags_string\")";
String commentTableSql = "DROP TABLE IF EXISTS hive_tweet_comment2222";
String createTable2 = "CREATE EXTERNAL TABLE hive_tweet_comment2222(tweet_id string,comment_id string, cuser_id string, user_id string, created_at bigint, year bigint, month bigint, day bigint, hour bigint, text string, comments_count bigint, reposts_count bigint, source string, topic_id string, post_type string, sentiment string) STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' TBLPROPERTIES (\"dynamodb.table.name\" = \"crm_tweet_comment\",\"dynamodb.column.mapping\" = \"tweet_id:tweet_id,comment_id:comment_id,cuser_id:cuser_id,user_id:user_id,created_at:created_at,year:year,month:month,day:day,hour:hour,text:text,comments_count:comments_count,reposts_count:reposts_count,source:source,topic_id:tweet_id,post_type:post_type,sentiment:sentiment\")";
String retweetTableSql = "DROP TABLE IF EXISTS hive_tweet_retweet2222";
String createTable3 = "CREATE EXTERNAL TABLE hive_tweet_retweet2222(tweet_id string, cuser_id string, user_id string, retweet_id string, created_at BIGINT, year BIGINT, month BIGINT, day BIGINT, hour BIGINT, text string, comments_count BIGINT, reposts_count BIGINT, source string, topic_id string, verified_type BIGINT, post_type string, sentiment string) STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' TBLPROPERTIES (\"dynamodb.table.name\" = \"crm_tweet_retweet\",\"dynamodb.column.mapping\" = \"tweet_id:tweet_id,cuser_id:cuser_id,user_id:user_id,retweet_id:retweet_id,created_at:created_at,year:year,month:month,day:day,hour:hour,text:text,comments_count:comments_count,reposts_count:reposts_count,source:source,topic_id:tweet_id,verified_type:verified_type,post_type:post_type,sentiment:sentiment\")";
Statement stmt = getHiveConnection().createStatement();
stmt.executeQuery(tweetTableSql);
stmt.executeQuery(createTable1);
stmt.executeQuery(commentTableSql);
stmt.executeQuery(createTable2);
stmt.executeQuery(retweetTableSql);
stmt.executeQuery(createTable3);
}
publicstaticvoidselectTweet()throwsSQLException {
longaaa = System.currentTimeMillis();
longstart = DateUtils.getNDaysAgo(DateUtils.getMidNight(),15).getTime().getTime();
longend = DateUtils.getNDaysAgo(DateUtils.getMidNight(),13).getTime().getTime();
String sql = "select cuser_id, count(*) as tw_hour, year, month, day from hive_crm_tweet2222 where created_at > ? and created_at
PreparedStatement pstm = getHiveConnection().prepareStatement(sql);
pstm.setLong(1, start);
pstm.setLong(2, end);
pstm.setString(3,"2176270443");
ResultSet rss = pstm.executeQuery();
while(rss.next()) {
System.out.println("1: "+ rss.getString("cuser_id") +" 2: "+ rss.getInt("tw_hour") +" 3: "+ rss.getInt("year") +" 4: "+ rss.getInt("month") +" 5: "+ rss.getInt("day"));
}
System.out.println(System.currentTimeMillis() - aaa);
}
publicstaticvoidselectTweet22()throwsSQLException {
longaaa = System.currentTimeMillis();
longstart = DateUtils.getNDaysAgo(DateUtils.getMidNight(),15).getTime().getTime();
longend = DateUtils.getNDaysAgo(DateUtils.getMidNight(),13).getTime().getTime();
String sql = "select cuser_id, created_at, tweet_id from hive_crm_tweet2222 where created_at > ? and created_at
PreparedStatement pstm = getHiveConnection().prepareStatement(sql);
pstm.setLong(1, start);
pstm.setLong(2, end);
pstm.setString(3,"2176270443");
ResultSet rss = pstm.executeQuery();
SimpleDateFormat dateFormat = newSimpleDateFormat("yyyy-MM-dd HH");
while(rss.next()) {
longcc = Long.valueOf(String.valueOf(rss.getInt("created_at")) +"000");
java.util.Date date = newjava.util.Date(cc);
System.out.println(dateFormat.format(date));
System.out.println(rss.getString("cuser_id") +" "+ rss.getString("tweet_id"));
}
System.out.println(System.currentTimeMillis() - aaa);
}
publicstaticvoidmain(String[] args)throwsClassNotFoundException, SQLException {
// Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");
// String querySQL = "SELECT a.* FROM test_time a";
//
// Connection con = DriverManager.getConnection(URLHIVE, "", "");
// Statement stmt = con.createStatement();
// ResultSet res = stmt.executeQuery(querySQL); // 执行查询语句
//
// while (res.next()) {
// System.out.println("Result: key:" + res.getString(1) + " –> value:" + res.getString(2));
// }
selectTweet22();
// SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH");
// System.out.println(dateFormat.format(new java.util.Date()));
}
}
文章浏览阅读1.7k次。操作系统:ubuntu 11.04交叉编译工具链:arm-linux-gcc 4.3.2 源码:qt-everywhere-opensource-src-4.7.3.tar.gz移植过程:1 下载源码,解压,进入解压后文件夹:#tar zxvf qt-everywhere-opensource-src-4.7.3.tar.gz#cd qt-everywhere-o_qt lunix 通过网线复制到开发板
文章浏览阅读762次。阿里妹导读:忙不完的事情,解不完的bug,每次发版都得集体熬个大通宵。干得多,结果还不好。相信绝大部分技术工作者都有过这样的困扰。今天,阿里云效敏捷教练蔡春华以团队敏捷实..._干移动外包熬夜发版
文章浏览阅读897次。1. 后台返回JSON格式的数据,会报406错误。(fastjson)原因:Springframework框架版本过旧,最新的版本可以。2.请求报错:No converter found for return value of type: class com.alibaba.fastjson.JSONObject原因:报这种错误表示fastjson 或 jackson 转换SpringMVC错误,需_som.406
文章浏览阅读353次。1 .Android系统在超级终端下必会的命令大全(adb shell命令大全) 2 . 顶级外语学习资源[转] 近600个教学学习资料链接 3 .电脑故障维修判断指导大全(联想内部文件)[网络转载] 4 .11月热门下载资源TOP100强力推荐! 5 .电子书下载网址大全 6 .电脑知识大全 7 .电脑DOS命令大全 8 .Windows 2003安全设置大全 9 .电脑快捷键大_csdn工具站
文章浏览阅读1w次,点赞20次,收藏102次。阿里妹导读:大数据已然是当下的重要课题,大大小小的企业在重视大数据的同时,也渐渐重视大数据质量的问题。阿里巴巴测试开发专家小郅,今天会分享他对数据测试的系统性思考。文章内..._大数据测试工作规划
文章浏览阅读4.6k次,点赞14次,收藏60次。lvs+keepalived+nginx高性能负载均衡集群项目发布时候,别人还能访问呢?双机主从热备LVS作用LVS是一个开源的软件,可以实现传输层四层负载均衡。LVS是Linux Virtual Server的缩写,意思是Linux虚拟服务器。目前有三种IP负载均衡技术(VS/NAT、VS/TUN和VS/DR);八种调度算法(rr,wrr,lc,wlc,lblc,lblcr,dh..._lvs+keeplived、nginx工作原理
文章浏览阅读3.2k次。SQLite语法SQLite是一个小巧的嵌入式数据库,使用方便、开发简单,手机上最早由ios运用,后来android兴起同样也采用了sqlite。sqlite的多数sql语法与oracle是一样的,下面只列出不同的地方:1、建表时为避免重复操作,应加上“IF NOT EXISTS”关键词,例如:CREATE TABLE IF NOT EXISTS table_name2、同样的,删表时_"string create_sql = \"create table if not exists \" + table_name + \" (\" + \"_id"
文章浏览阅读706次。理论上能正常运行ifconfig的Linux操作系统都可适用这个脚本(当然一般默认会安装perl),打印实时网卡流量和丢包数,好久前当时为了验证输出流量随手写的,多次想改得漂亮点,一直没动手,就这样吧,以免用的时候找不到其实我还想评论自己一句:把Perl写得跟C语言新手一样,真是Perl的悲哀!!!#!/usr/bin/perl -w################################..._linux 所有流量使用指定网卡
文章浏览阅读2.2k次。OpenCV4萌新之路——详解图像显示三兄弟 “imshow”、“namedWindow” 和“waitKey”一、函数简析二、参数详解1.1 const String & winname1.2 InputArray mat2.1 int delay = 03.1 const String & winname3.2 int flags = WINDOW_AUTOSIZE三、测试代码四. 其他测试1.无限制缩放?2.支持OpenGL四、参考文献上一篇"OpenCV4萌新之路——详解图像读取函_opencv imshow
文章浏览阅读570次。给定n个书本的厚度,然后依次放到书架上,要么放在最左边要么放在最右边,或者不放,要求书的厚度是非递减的序列。然后问最多放置多少本书。数字范围10^5,但是只有100个数字,我们先离散化一下。然后对于放置一本书来言,就是要么增大下界,要么减小上界,或者上下界都不变。那么我们可以采用dp[i][j]代表当前下界为i上界为j最多放置多少本书。对于一个新的书,就是产生dp[a[i]][j]_pipioj1283
文章浏览阅读1.1w次,点赞18次,收藏48次。1:redis的安装1.1:redis在window系统的安装省略,可以自行百度我这里安装的压缩版1.2:redis在linux系统的安装现在还没有用到,用到了再补上TODO2:redis的服务启动2.1:在windows系统中启动redis服务端使用cmd进入到redis的安装目录redis-server.exe redis.windows.conf可以在windows系统中编写一个启动文件,可以把该指令写入到startup.cmd文件中启动redis客户端重新启用一个cmd会_redis.windows-service.conf和redis.windows.conf
文章浏览阅读375次。在进行sql注入测试中总会遇到各种厂商的waf,本篇文章将对waf的过滤机制,以及常见的绕过方法进行介绍,希望在以后的测试中会对大家有所帮助。0x01 WAF的过滤机制 WAF全称网站应用级***防御系统是Web Application Firewall的简称,主要有以下四个功能:1. 审计设备--用来截获所有HTTP数据或者仅仅满足某些规则的会话 ;2. 访问控制设..._%d6%5c