private static void t1(DateTime yesterday)
{
string viewTable = "AdTxt" + yesterday.ToString("yyyy.MM.dd").Replace(".", "") + "View";
string clickTable = "AdTxt" + yesterday.ToString("yyyy.MM.dd").Replace(".", "") + "View";
StringBuilder sql = new StringBuilder();
sql.Append("select '" + yesterday.ToString("yyyy-MM-dd hh:mm:ss.fffffff") + "' as ReportDate,vn.DealerId,vn.KeywordPId,");
sql.Append("case when vn.num is null then 0L else cn.num end ViewNum,");
sql.Append("case when cn.num is null then 0L else cn.num end clicknum,");
sql.Append("case when vin.num is null then 0L else vin.num end viewIpnum,");
sql.Append("case when cin.num is null then 0L else cin.num end clickIpnum ");
//sql.Append("unix_timestamp('" + DateTime.Now.ToShortDateString() + "') as createtime ");
sql.Append("From (select a.VendorId as DealerId,v.KeywordPId,count(v.KeywordPId) Num from " + viewTable + " v join temp_adtxt a on a.Id=v.AdTxtId inner join temp_Keyword_L1 k on k.Id=v.KeywordId group by a.VendorId ,v.KeywordPId) vn ");
sql.Append("join (select a.VendorId as DealerId,v.KeywordPId,count(distinct v.Ip) Num from " + viewTable + " v join temp_adtxt a on a.Id=v.AdTxtId join temp_Keyword_L1 k on k.Id=v.KeywordId group by a.VendorId ,v.KeywordPId) vin on vn.DealerId=vin.DealerId and vn.KeywordPId=vin.KeywordPId ");
sql.Append("left outer join (select a.VendorId as DealerId,v.KeywordPId,count(v.KeywordPId) Num from " + clickTable + " v join temp_adtxt a on a.Id=v.AdTxtId join temp_Keyword_L1 k on k.Id=v.KeywordId group by a.VendorId ,v.KeywordPId) cn on vn.DealerId=cn.DealerId and vn.KeywordPId=cn.KeywordPId ");
sql.Append("left outer join (select a.VendorId as DealerId,v.KeywordPId,count(distinct v.Ip) Num from " + clickTable + " v join temp_adtxt a on a.Id=v.AdTxtId join temp_Keyword_L1 k on k.Id=v.KeywordId group by a.VendorId ,v.KeywordPId) cin on vn.DealerId=cin.DealerId and vn.KeywordPId=cin.KeywordPId limit 1");
int port = 10000;
var socket = new TSocket("192.168.87.64", port);
var transport = new TBufferedTransport(socket);
var proto = new TBinaryProtocol(transport);
var client = new ThriftHive.Client(proto);
transport.Open();
client.execute(sql.ToString());
var rows2 = client.fetchAll();
transport.Close();
rows2.ForEach(Console.WriteLine);
}
public static void t2()
{
var transport = new TBufferedTransport(new TSocket("hserver", 10000));
var protocol = new TBinaryProtocol(transport);
var client = new ThriftHive.Client(protocol);
transport.Open();
var database = new Database();
database.Name = "helloworldDB";
database.Description = "测试用的第一个DB";
var tables = client.get_all_tables("default");
//var tables = client.get_all_databases();
foreach (var table in tables)
{
System.Console.WriteLine(table);
}
transport.Close();
}
public static void query()
{
var transport = new TBufferedTransport(new TSocket("hserver", 10000));
var protocol = new TBinaryProtocol(transport);
var client = new ThriftHive.Client(protocol);
transport.Open();
//client.execute("CREATE TABLE r(a STRING, b INT, c DOUBLE)");
//client.execute("LOAD TABLE LOCAL INPATH '/path' INTO TABLE r");
client.execute("SELECT * FROM pokes where foo=180");
while (true)
{
var row = client.fetchOne();
if (string.IsNullOrEmpty(row))
{
break;
}
System.Console.WriteLine(row);
}
client.execute("SELECT * FROM pokes");
var all = client.fetchAll();
System.Console.WriteLine(all);
transport.Close();
}
public HiveService(string serverHostName)
{
var socket = new TSocket(HiveConfigure.Instance.GetHostServer(serverHostName), HiveConfigure.Instance.GetHostPort(serverHostName));
transport = new TBufferedTransport(socket);
var proto = new TBinaryProtocol(transport);
client = new ThriftHive.Client(proto);
}
public HiveService(string serverHostName, string sqoopServerHostName, string sqoopSqlServerHost, string sqoopDatabaseName, string sqoopUserName, string sqoopPassword)
{
var socket = new TSocket(HiveConfigure.Instance.GetHostServer(serverHostName), HiveConfigure.Instance.GetHostPort(serverHostName));
transport = new TBufferedTransport(socket);
var proto = new TBinaryProtocol(transport);
client = new ThriftHive.Client(proto);
sqoopSqlServer = new SqoopSqlServerService(sqoopServerHostName, sqoopSqlServerHost, sqoopDatabaseName, sqoopUserName, sqoopPassword);
}
public HiveService()
{
var socket = new TSocket(HiveConfigure.Instance.GetDefaultHost(), HiveConfigure.Instance.GetDefaultPort());
transport = new TBufferedTransport(socket);
var proto = new TBinaryProtocol(transport);
client = new ThriftHive.Client(proto);
transport.Open();
}
static void Main(string[] args)
{
int port = 10111;
var socket = new TSocket("ca1.newco.com", port);
var transport = new TBufferedTransport(socket);
var proto = new TBinaryProtocol(transport);
var client = new ThriftHive.Client(proto);
transport.Open();
client.execute("select userAgent from clicks limit 100");
var rows = client.fetchAll();
transport.Close();
rows.ForEach(Console.WriteLine);
}
public static void t()
{
var transport = new TBufferedTransport(new TSocket("hserver", 10000));
var protocol = new TBinaryProtocol(transport);
var client = new ThriftHive.Client(protocol);
transport.Open();
//client.execute("CREATE TABLE r(a STRING, b INT, c DOUBLE)");
//client.execute("LOAD TABLE LOCAL INPATH '/path' INTO TABLE r");
var database = new Database();
database.Name = "helloworldDB";
database.Description = "测试用的第一个DB";
client.create_database(database);
transport.Close();
}
