查询kylin数据:java程序

灰太狼 2022-02-17 01:00 226阅读 0赞

数据源：　hbase表　－－> 映射到：hvie外表

#hbase(main):002:0> scan 't1'
    #ROW                        COLUMN+CELL
    # row1                      column=f:name, timestamp=1555401422261, value=a
    # row11                     column=f:name, timestamp=1555498650385, value=lisi
    
    CREATE EXTERNAL TABLE test1(rowkey string, name string )   
    STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'   
    WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f:name")   
    TBLPROPERTIES("hbase.table.name" = "t1");

# 1, 使用kylin提供的: RESTful API #

shell命令行发送post请求，命令如下：

UserName=ADMIN
    Password=KYLIN
    baseUsernamePasswd=`python -c "import base64; print base64.standard_b64encode('$UserName:$Password')"`
    
    curl -X POST \
    -H "Authorization: Basic $baseUsernamePasswd" \
    -H "Content-Type: application/json" \
    -d '{ "sql":"SELECT KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL ,count(*) as data FROM KYLIN_ACCOUNT  group by KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL  order by data desc", "project":"kylin_demo" }' \
    http://localhost:7070/kylin/api/query

*  base64加密：用户名，密码
 *  fastjson附带请求参数
 *  发送http请求

## a, 配置maven依赖 ##

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.56</version>
    </dependency>
    <dependency>
        <groupId>commons-codec</groupId>
        <artifactId>commons-codec</artifactId>
        <version>1.10</version>
    </dependency>
    <dependency>
        <groupId>org.apache.httpcomponents</groupId>
        <artifactId>httpclient</artifactId>
        <version>4.5.5</version>
    </dependency>

## b，java代码 ##

import com.alibaba.fastjson.JSONObject;
    import org.apache.commons.codec.binary.Base64;
    import org.apache.http.HttpEntity;
    import org.apache.http.client.methods.CloseableHttpResponse;
    import org.apache.http.client.methods.HttpPost;
    import org.apache.http.entity.StringEntity;
    import org.apache.http.impl.client.CloseableHttpClient;
    import org.apache.http.impl.client.HttpClients;
    import org.apache.http.util.EntityUtils;
    
    import java.io.IOException;
    import java.io.UnsupportedEncodingException;
    
    public class KylinRestApi {
        static String encoding = "UTF-8";
        static String username = "ADMIN";
        static String passwd = "KYLIN";
        static String url = "http://localhost:7070/kylin/api/query";
    
        public static void main(String[] args) throws IOException {
            requestByPostMethod();
        }
    
        /**
         * 使用httpcline 进行post访问
         *
         * @throws IOException
         */
        public static void requestByPostMethod() throws IOException {
            CloseableHttpClient httpClient = HttpClients.createDefault();
            try {
                //创建post方式请求对象
                // 接收参数json列表 (kylin 只接受json格式数据)
                HttpPost httpPost = new HttpPost(url);
                JSONObject jsonParam = new JSONObject();
    //          String sql="select * from KYLIN_ACCOUNT ";
                String sql = "SELECT KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL  ,count(*) as data FROM KYLIN_ACCOUNT  group by  KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL  order by data desc";
                jsonParam.put("sql", sql);
    //            jsonParam.put("limit", "20");
                jsonParam.put("project", "kylin_demo");
    
                StringEntity sentity = new StringEntity(jsonParam.toString(), encoding);//解决中文乱码问题
                sentity.setContentEncoding(encoding);
                sentity.setContentType("application/json");
                httpPost.setEntity(sentity);
    
                //设置header信息
                //指定报文头【Content-type】,【User-Agent】
                httpPost.setHeader("Content-type", "application/json;charset=utf-8");
                httpPost.setHeader("Authorization", getPWD(username,passwd));//Basic QURNSU46S1lMSU4=
    
                //执行请求
                CloseableHttpResponse httpResponse = httpClient.execute(httpPost);
                try {
                    HttpEntity entity = httpResponse.getEntity();
                    if (null != entity) {
                        //按指定编码转换结果实体为String类型
                        String body = EntityUtils.toString(entity, encoding);
                        JSONObject obj = JSONObject.parseObject(body);
                        System.out.println(body);
                        System.out.println(obj.get("results"));
                    }
                } finally {
                    httpResponse.close();
                }
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                httpClient.close();
            }
        }
    
        public static String getPWD(String username, String passwd) {
            String parm = username+":"+passwd;
            String result="";
            try{
                byte[] encodeBase64 = Base64.encodeBase64(parm.getBytes("UTF-8"));
                result = new String(encodeBase64);
                return "Basic "+result;
            } catch(UnsupportedEncodingException e){
                e.printStackTrace();
                return "";
            }
        }
    }

# 2, 使用kylin提供的JDBC接口 #

<dependency>
        <groupId>org.apache.kylin</groupId>
        <artifactId>kylin-jdbc</artifactId>
        <version>2.1.0</version>
    </dependency>

import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.util.Properties;
    
    public class KylinJDBC {
        public static void main(String[] args) throws Exception {
            Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
            Properties info = new Properties();
            info.put("user", "ADMIN");
            info.put("password", "KYLIN");
    
            Connection conn = driver.connect("jdbc:kylin://localhost:7070/kylin_demo", info);
            Statement state = conn.createStatement();
    //        String sql="select * from KYLIN_ACCOUNT";
            String sql="SELECT KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL ,count(*) as data FROM KYLIN_ACCOUNT  group by KYLIN_ACCOUNT.ACCOUNT_BUYER_LEVEL  order by data desc";
            ResultSet resultSet = state.executeQuery(sql);
    
            int count=0;
            while (resultSet.next()) {
              System.out.println(resultSet.getString(1)+","+ resultSet.getString(2));
              count++;
            }
            System.out.println("count == "+ count);
        }
    }