Druid 基础使用-操作篇（Pivot、plyql）

一、Pivot  --9095 端口
 

二、基本sql 使用

 。plysql--   http://plywood.imply.io/plyql  （下面的端口应该是8082，我这个地方做了端口转换） 

执行sql脚本（bin/plyql -h *.*.195.60:8085 -q 'SHOW TABLES'）
[teld@Druid imply-1.3.1]$ bin/plyql -h *.*.195.60:8085 -q 'SHOW TABLES'

  显示表结构（ bin/plyql -h *.*.*.60:8085 -q 'DESCRIBE pageviews'）

 简单查询（bin/plyql -h *.*.195.60:8085 -q 'SELECT user as us,count() as cnt FROM pageviews   GROUP BY user ORDER BY cnt DESC;'）

 聚合汇总（ bin/plyql -h *：:8085 -q 'SELECT user as us,count() as cnt FROM pageviews   GROUP BY user ORDER BY cnt DESC;'）

   

    时间过滤（bin/plyql -h *:8085 -q 'SELECT user as us,count() as cnt FROM pageviews where "2015-09-12T00:00:00" <= __time AND __time < "2019-09-13T00:00:00"GROUP BY user ORDER BY cnt DESC;'）

httppost
启动监听[teld@Druid imply-1.3.1]$ bin/plyql -h *:8085 -i P2Y --json-server 8096

PlyQL server listening on port: 8096

Got SQL: SELECT user as us,count() as cnt FROM pageviews

Got SQL: SELECT user as us,count() as cnt FROM pageviews GROUP BY user ORDER BY cnt DESC

查询：

[root@Druid imply-1.3.1]# curl -X POST 'http://*:8096/plyql' -H 'content-type: application/json' -d '{"sql": "SELECT user as us,count() as cnt FROM pageviews GROUP BY user ORDER BY cnt DESC"}'

c# 调用http进行数据查询

public void QueryData()
        {
            string sql = "{"sql": "SELECT user as us,count() as cnt FROM pageviews GROUP BY user ORDER BY cnt DESC"}";
            string url = "http://*:8096/plyql";
            string data = PostHttp(url, sql);
            DruiQueryResult result = JsonConvert.DeserializeObject<DruiQueryResult>(data);
            DataTable dt = new DataTable();
            foreach(var item in result.result)
            {
                foreach(var childItme in item.Keys)
                {
                    dt.Columns.Add(childItme,typeof(string));
                }
                break;
            }
            DataRow dr = null;
            foreach (var item in result.result)
            {
                dr = dt.NewRow();
                foreach (var childItme in item)
                {
                    dr[childItme.Key] = childItme.Value;
                }
                dt.Rows.Add(dr.ItemArray);
            }
            Assert.IsTrue(dt.Rows.Count>100);

        }