除
Restful API外,您还可以使用JDBC + 标准SQL 92进行日志查询与分析。
连接参数
例如通过MySQL命令连接示例如下:
- mysql -hcn-shanghai-intranet.log.aliyuncs.com -ubq2sjzesjmo86kq -p4fdO1fTDDuZP -P10005
- use sample-project; // 使用某个Project
前提条件
访问JDBC接口,必须使用主账号的AK或者子帐号的AK。子帐号必须是Project owner的子帐号,同时子帐号具有Project级别的读权限。
语法说明
注意事项
在where条件中必须包含__date__或__time__来限制查询的时间范围。__date__是timestamp类型__time__是bigint类型。
例如:
- __date__ > '2017-08-07 00:00:00' and __date__ < '2017-08-08 00:00:00'
- __time__ > 1502691923 and __time__ < 1502692923
上述两种条件必须出现一个。
过滤语法
关于where下过滤(filter)语法如下:
计算语法
支持计算操作符参见
分析语法。
SQL92语法
过滤 + 计算组合为SQL92语法。
例如对于如下查询:
- status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20
我们可以将查询中过滤部分+ 时间条件组合成为查询的条件,变成标准SQL92语法:
- select avg(latency),max(latency) ,count(1) as c from sample-logstore where status>200 and __time__>=1500975424 and __time__ < 1501035044 GROUP BY method ORDER BY c DESC LIMIT 20
通过JDBC协议访问
程序调用
开发者可以在任何一个支持MySQL connector的程序中使用MySQL语法连接日志服务。例如使用JDBC或者Python MySQLdb。
使用样例:
- import com.mysql.jdbc.*;
- import java.sql.*;
- import java.sql.Connection;
- import java.sql.ResultSetMetaData;
- import java.sql.Statement;
- pmport java.sql.Statement;
- public static void main(String args[]){
- Connection conn = null;
- Statement stmt = null;
- try {
- //STEP 2: Register JDBC driver
- Class.forName("com.mysql.jdbc.Driver");
- //STEP 3: Open a connection
- System.out.println("Connecting to a selected database...");
- conn = DriverManager.getConnection("jdbc:mysql://cn-shanghai-intranet.log.aliyuncs.com:10005/sample-project","accessid","accesskey");
- System.out.println("Connected database successfully...");
- //STEP 4: Execute a query
- System.out.println("Creating statement...");
- stmt = conn.createStatement();
- String sql = "SELECT method,min(latency,10) as c,max(latency,10) from sample-logstore where __time__>=1500975424 and __time__ < 1501035044 and latency > 0 and latency < 6142629 and not (method='Postlogstorelogs' or method='GetLogtailConfig') group by method " ;
- String sql-example2 = "select count(1) ,max(latency),avg(latency), histogram(method),histogram(source),histogram(status),histogram(clientip),histogram(__source__) from test10 where __date__ >'2017-07-20 00:00:00' and __date__ <'2017-08-02 00:00:00' and __line__='abc#def' and latency < 100000 and (method = 'getlogstorelogS' or method='Get**' and method <> 'GetCursorOrData' )";
- String sql-example3 = "select count(1) from sample-logstore where __date__ > '2017-08-07 00:00:00' and __date__ < '2017-08-08 00:00:00' limit 100";
- ResultSet rs = stmt.executeQuery(sql);
- //STEP 5: Extract data from result set
- while(rs.next()){
- //Retrieve by column name
-
- System.out.println(data.getColumnCount());
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]for
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace](
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]int[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace] i
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]=[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]0
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace];[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]i
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]<[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace] data
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace].[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]getColumnCount
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]();++[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]i
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace])[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]
[font='YaHei Consolas Hybrid', Consolas, 'Meiryo UI', 'Malgun Gothic', 'Segoe UI', 'Trebuchet MS', Helvetica, monospace, monospace]{
- String name = data.getColumnName(i+1);
- System.out.print(name+":");
- System.out.print(rs.getObject(name));
- }
- System.out.println();
- }
- rs.close();
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- if (stmt != null) {
- try {
- stmt.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- if (conn != null) {
- try {
- conn.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- }
- }
工具类调用
在经典网内网/VPC环境通过MySQL Client进行连接。
[backcolor=transparent]注意:
- ①处填写您的Project。
- ②处填写您的Logstore。