http rest接口 使用方式：1
curl -X POST '<queryable_host>:<port>/druid/v2/?pretty' -H 'Content-Type:application/json' -d @<query_json_file>
json内容，例如：1
2
3
4
5
6
7
8
9
10
11
12
{
"queryType": "timeseries",
"dataSource": "sample_datasource",
"granularity": "day",
"aggregations": [
{ "type": "longSum", "name": "sample_name1", "fieldName": "sample_fieldName1" }
],
"intervals": [ "2012-01-01T00:00:00.000/2012-01-04T00:00:00.000" ],
"context" : {
"skipEmptyBuckets": "true"
}
}
plyql 部署 需要先行安装安装node+npm环境，node版本>= 4.x.x。这里plyql用的当时最新的0.8.13版本。1
2
3
npm install -g plyql@0.8.13
//添加环境变量
export PATH=/disk2/node-v4.2.2-linux-x64/bin:$PATH
执行以下操作之后，在本地相当于开起了一个druid-mysql形式的接口，端口为3307。后续操作就跟操作mysql几乎一样了。1
plyql -h druid-broker:port -c 8 --experimental-mysql-gateway 3307
一些可供配置的参数：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--help print this help message
--version display the version number
-v, --verbose display the queries that are being made
展示查询和转换之后的json
-h, --host the host to connect to
-s, --source use this source for the query (supersedes FROM clause)
-i, --interval add (AND) a __time filter between NOW-INTERVAL and NOW
-i P2Y 会不查询近6个小时的数据。
-tz, --timezone the default timezone
-tz Asia/Shanghai 为东八区，加不加一样，应该是默认时区
-q, --query the query to run
-o, --output specify the output format. Possible values: json (default), csv, tsv, flat
-a, --allow enable a behaviour that is turned off by default eternity allow queries not filtered on time select allow select queries
-t, --timeout the time before a query is timed out in ms (default: 60000)
超时时间，默认60s
-r, --retry the number of tries a query should be attempted on error, 0 = unlimited, (default: 2)
-c, --concurrent the limit of concurrent queries that could be made simultaneously, 0 = unlimited, (default: 2)
并发查询数，默认2
--rollup use rollup mode [COUNT() -> SUM(count)]
监控 这里进行简单的启动、监控。使用linux-crontab执行定时任务，启动并且监听plyql服务。后续可以接入graphite或者其他监控报警系统中。1
* * * * * /disk1/fangyq/start-plyql-cron.sh online >> /disk1/fangyq/log/cron.log
监听3307端口，当没有监听时，启动plyql服务。1
2
3
4
5
6
7
8
9
10
11
#!/bin/sh
logPath=$(cd `dirname $0`; cd ../log ; pwd)
listen=$(netstat -nlp|grep 3307)
#start plyql
if [ "$listen" == "" ]
then
echo 'INFO: start plyql service'
nohup plyql -h druid-broker:port -c 8 -v --experimental-mysql-gateway 3307 >>${logPath}/plyql.log &
fi
使用 客户端 与普通的mysql客户端一样，druid的一个datasource相当于一个table1
2
mysql -h 127.0.0.1 -P 3307 --default-character-set=utf8
show tables;
举例：datasource_a如下
维度为：id
度量为：impr(展示)，click(点击)1
2
3
4
5
6
7
8
9
desc datasource_a;
+----------------------------------+--------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+--------------+------+------+---------+-------+
| __time | timestamp | YES | | NULL | |
| id | varchar(255) | YES | | NULL | |
| impr | double | YES | | NULL | |
| click | double | YES | | NULL | |
+----------------------------------+--------------+------+------+---------+-------+
查询一天内所有的展示和点击。1
SELECT SUM(impr) AS impr,SUM(click) AS click FROM datasource_a WHERE __time BETWEEN '2016-05-19 00:00:00' AND '2016-05-20 00:00:00';
查询10天内，id为1的展示和点击，按天聚合。1
SELECT id,SUM(impr) AS impr,SUM(click) AS click FROM datasource_a WHERE __time BETWEEN '2016-05-10 00:00:00' AND '2016-05-20 00:00:00' AND id='1' GROUP BY id,TIME_BUCKET(__time,P1D,'Asia/Shanghai') HAVING impr>0 OR click>0;
程序 跟普通的访问mysql一样。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
import lombok.Cleanup;
import org.junit.Test;
import java.sql.*;
public class PlyqlTest {
@Test
public void plyql(){
String plyql_url="jdbc:mysql://plyql-host:3307/plyql1?useUnicode=true&characterEncoding=utf8";
try {
Class.forName("com.mysql.jdbc.Driver");
@Cleanup Connection con = DriverManager.getConnection(plyql_url);
@Cleanup Statement stmt = con.createStatement();
@Cleanup ResultSet rs = stmt.executeQuery(
"SELECT id,SUM(impr) AS impr,SUM(click) AS click FROM datasource_a WHERE __time BETWEEN '2016-05-10 00:00:00' AND '2016-05-20 00:00:00' GROUP BY id"
);
while (rs.next()) {
String id = rs.getString("id");
double impr = rs.getDouble("impr");
System.out.println(id+" "+impr);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
语法说明 1）聚合字段1
2
LONG_SUM ==> SUM
cardinality（hyperLogLog uniques） ==> COUNT_DISTINCT
例如，uv字段，COUNT_DISTINCT(uv)算出来为double类型,结果需要取整1
2
WHERE __time >='2016-05-19T00:00:00.000+08:00' AND __time<='2016-05-19T23:59:59.999+08:00'
WHERE __time >='2016-05-19 00:00:00' AND __time<='2016-05-19 23:59:59'
3）时间颗粒度：1
2
group by day ==> GROUP BY TIME_BUCKET(__time,P1D,'Asia/Shanghai')
group by hour ==> GROUP BY TIME_BUCKET(__time,PT1H,'Asia/Shanghai')
4）limit只有一个参数，因为在druid中只有topN查询。分页查询只能在程序中先查出总数，再取子集。1
2
limit offset,max ==> limit offset+max
lrs.subList(offset,max);
Sql4D 客户端 安装1
2
3
4
5
6
git clone https://github.com/fangyeqing/Sql4D.git
cd Sql4D/Sql4DClient
mvn clean install
vi dsql #修改如下:
java -jar target/Sql4DClient-4.1.0.jar -bh broker-host -bp broker-port -ch coordinator-host -cp 8050 -oh broker-host -op broker-port -i 100
./dsql #启动client
连接上druid之后，操作如下：1
2
3
4
5
6
7
show tables (显示datasources)
describe TableName (显示数据源的schema)
trace=[true|false] (打印转换后的json，默认false)
querymode=[sql|json] (查询模式，默认sql)
quit (退出)
select queries (GroupBy, TimeSeries, TopN, Select, Search). See wiki for examples: https://github.com/srikalyc/Sql4D/wiki/Sql4DCompiler
generatebean=BeanName (This command must be preceding a SQL, it generates a java source file BeanName.java which extends DruidBaseBean.
程序 maven依赖，可以直接用如下的，不过有一些bug。最好是用修改后的版本重新打包或者发布再添加依赖。https://github.com/fangyeqing/Sql4D 1
2
3
4
5
<dependency>
<groupId>com.yahoo.sql4d</groupId>
<artifactId>Sql4Ddriver</artifactId>
<version>4.1.0</version>
</dependency>
使用1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@CommonsLog
@Data
public class DruidDataSource {
private String bHost;
private int bPort;
private String cHost;
private int cPort;
private int maxConnsInPool;
private int maxBrokerConns;
private int maxCoordConns;
private int maxOverLordConns;
public static DDataSource driver;
@PostConstruct
public void init(){
DDataSource.adjustPoolSettings(maxConnsInPool,maxBrokerConns,maxCoordConns,maxOverLordConns);
driver = new DDataSource(bHost, bPort, cHost, cPort);
}
public List<DruidBaseBean> query(String sql, Class type) {
Either<String, Either<List<DruidBaseBean>, Map<Object, DruidBaseBean>>> response = driver.query(sql, null, type, null, false);
return response.right().get().left().get();
}
}
pydruid null
参考 http://plywood.imply.io/plyql#examples http://imply.io/post/2016/05/04/programmatic-plyql.html https://github.com/implydata/plyql https://github.com/srikalyc/Sql4D
