Use JDBC to query and analyze logs

Connection parameter	Example	Description
host	regionid.example.com	Service endpointThe access point, Currently, only the intranet access of classic network and Virtual Private Cloud (VPC) access are supported.
port	10005	Use 10005 as the port by default.
user	bq2sjzesjmo86kq	The AccessKey ID .
password	4fdO1fTDDuZP	The AccessKey Secret.
database	sample-project	The project under your account.
table	sample-logstore	The Logstore under project.

For example, use a MySQL command to connect to Log Service as follows:

mysql -hcn-shanghai-intranet.log.aliyuncs.com -ubq2sjzesjmo86kq -p4fdO1fTDDuZP -P10005
use sample-project; //Use a project.

Example:

• __date__ > '2017-08-07 00:00:00' and __date__ < '2017-08-08 00:00:00'
• __time__ > 1502691923 and __time__ < 1502692923

At least one of the preceding conditions must be contained.

status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20

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

Example:

import com.mysql.jdbc.*;
Import java. SQL .*;
import java.sql.Connection;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class testjdbc {
    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
                ResultSetMetaData data = rs.getMetaData();
                System.out.println(data.getColumnCount());
                for(int i = 0;i < data.getColumnCount();++i) {
                    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();