jooq扩展现有的方言。采用MySQL方言来apache Hive方言 [英] jooq extend existing dialect. Adopt MySQL dialect to apache Hive dialect

问题描述

我正尝试使用JOOQ来查询Hive。
Hive SQL方言对于MySQL方言来说是相当沉重的。
现在我遇到了这些问题：

• Hive支持LIMIT N，它不支持LIMIT N OFFSET K. Dummy
  solution - 覆盖 select.limit（limit）;

这样的问题在JOOQ？

解决方案

这是最脏的解决方案:) JOOQ用户组不幸回答：

  public class CountRatingQueryBuilder {
 
 private static final String SCORING_TABLE_NAME =web_resource_rating; 
 
 private final Connection连接; 
 private final ScoringMetadata scoringMetadata; 
 
 private final SelectSelectStep select; 
 private final Factory create; 
 $ b $ public CountRatingQueryBuilder（Connection connection ，ScoringMetadata scoringMetadata）{
 this.connection = connection; 
 this.scoringMetadata = scoringMetadata; 
 
 crea te = new Factory（this.connection，SQLDialect.MYSQL）; 
 select = create.select（）; 
 
 withSelectFieldsClause（）; 
 
 
 Public CountRatingQueryBuilder withLimit（int limit）{
 select.limit（limit）; 
返回此; 
} 
 
 public CountRatingQueryBuilder withRegionId（Integer regionId）{
 select.where（REGION_ID.field（）。equal（regionId））; 
返回此; 
} 
 
 public CountRatingQueryBuilder withResourceTypeId（int resourceTypeId）{
 select.where（RESOURCE_TYPE_ID.field（）。equal（resourceTypeId））; 
返回此; 
 
 
 CountRatingQueryBuilder withRequestTimeBetween（long beginTimestamp，long endTimestamp）{
 select.where（REQUEST_TIME.field（）。between（beginTimestamp，endTimestamp））; 
返回此; 
 
 
 public CountRatingQueryBuilder withResourceId（int resourceId）{
 select.where（RESOURCE_ID.field（）。equal（resourceId））; 
返回此; 
 
 
 
 
 protected void withGroupByClause（）{
 select.groupBy（REGION_ID.field（））; 
 select.groupBy（RESOURCE_TYPE_ID.field（））; 
 select.groupBy（RESOURCE_ID.field（））; 
 select.groupBy（CONTENT_ID.field（））; 
 
 
 protected void withSelectFieldsClause（）{
 select.select（REGION_ID.field（））; 
 select.select（RESOURCE_TYPE_ID.field（））; 
 select.select（CONTENT_ID.field（））; 
 select.select（RESOURCE_ID.field（））; 
 select.select（Factory.count（HIT_COUNT.field（））。as（SUM_HIT_COUNT.fieldName（）））; 
 
 
 protected void withFromClause（）{
 select.from（SCORING_TABLE_NAME）; 
 
 
 protected void withOrderByClause（）{
 select.orderBy（SUM_HIT_COUNT.field（）。desc（））; 
} 
 
 public String build（）{
 withGroupByClause（）; 
 withOrderByClause（）; 
 withFromClause（）; 
返回select.getSQL（）。replace（offset？，）; //对MySQL方言进行肮脏的破解。 TODO：我们可以尝试为Hive实现我们自己的SQL方言:) 
 
} 
 
 public List< ResultRow> buildAndFetch（）{
 String sqlWithPlaceholders = build（）; 
 
列表< ResultRow> scoringResults = new ArrayList< ResultRow>（100）; 
列表< Record> recordResults = create.fetch（sqlWithPlaceholders，ArrayUtils.subarray（select.getBindValues（）。toArray（new Object [select.getBindValues（）。size（）]），0，select.getBindValues（）。size（） -  1）） ; // select.fetch（）; （记录记录：recordResults）{
 ResultRowBuilder resultRowBuilder = ResultRowBuilder.create（）; 
 
 
 resultRowBuilder.withContentType（scoringMetadata.getResourceType（record.getValue（RESOURCE_TYPE_ID.fieldName（），Integer.class）））; 
 resultRowBuilder.withHitCount（record.getValue（SUM_HIT_COUNT.fieldName（），Long.class））; 
 resultRowBuilder.withUrl（record.getValue（CONTENT_ID.fieldName（），String.class））; 
 scoringResults.add（resultRowBuilder.build（））; 
} 
 return scoringResults; 
} 
 
} 
  

I'm trying to use JOOQ for quering Hive. Hive SQL dialect is pretty clode to MySQL dialect. Right now I've met these problems:

• Hive supports LIMIT N, it doesn't support LIMIT N OFFSET K. Dummy solution - override select.limit(limit);

What are best practices resolving such problems in JOOQ?

解决方案

Here is the dirtiest solution :) JOOQ user group didn't answer unfortunately :(

public class CountRatingQueryBuilder {

    private static final String SCORING_TABLE_NAME = "web_resource_rating";

    private final Connection connection;
    private final ScoringMetadata scoringMetadata;

    private final SelectSelectStep select;
    private final Factory create;

    public CountRatingQueryBuilder(Connection connection, ScoringMetadata scoringMetadata){
        this.connection = connection;
        this.scoringMetadata = scoringMetadata;

        create = new Factory(this.connection, SQLDialect.MYSQL);
        select = create.select();

        withSelectFieldsClause();
    }

    public CountRatingQueryBuilder withLimit(int limit){
        select.limit(limit);
        return this;
    }

    public CountRatingQueryBuilder withRegionId(Integer regionId){
        select.where(REGION_ID.field().equal(regionId));
        return this;
    }

    public CountRatingQueryBuilder withResourceTypeId(int resourceTypeId){
        select.where(RESOURCE_TYPE_ID.field().equal(resourceTypeId));
        return this;
    }

    public CountRatingQueryBuilder withRequestTimeBetween(long beginTimestamp, long endTimestamp){
        select.where(REQUEST_TIME.field().between(beginTimestamp, endTimestamp));
        return this;
    }

    public CountRatingQueryBuilder withResourceId(int resourceId){
        select.where(RESOURCE_ID.field().equal(resourceId));
        return this;
    }



    protected void withGroupByClause(){
        select.groupBy(REGION_ID.field());
        select.groupBy(RESOURCE_TYPE_ID.field());
        select.groupBy(RESOURCE_ID.field());
        select.groupBy(CONTENT_ID.field());
    }

    protected void withSelectFieldsClause(){
        select.select(REGION_ID.field());
        select.select(RESOURCE_TYPE_ID.field());
        select.select(CONTENT_ID.field());
        select.select(RESOURCE_ID.field());
        select.select(Factory.count(HIT_COUNT.field()).as(SUM_HIT_COUNT.fieldName()));
    }

    protected void withFromClause(){
        select.from(SCORING_TABLE_NAME);
    }

    protected void withOrderByClause(){
        select.orderBy(SUM_HIT_COUNT.field().desc());
    }

    public String build(){
        withGroupByClause();
        withOrderByClause();
        withFromClause();
        return select.getSQL().replace("offset ?","");//dirty hack for MySQL dialect. TODO: we can try to implement our own SQL dialect for Hive :)

    }

    public List<ResultRow> buildAndFetch(){
        String sqlWithPlaceholders = build();

        List<ResultRow> scoringResults = new ArrayList<ResultRow>(100);
        List<Record> recordResults = create.fetch(sqlWithPlaceholders, ArrayUtils.subarray(select.getBindValues().toArray(new Object[select.getBindValues().size()]),0, select.getBindValues().size()-1));//select.fetch();
        for(Record record : recordResults){
            ResultRowBuilder resultRowBuilder = ResultRowBuilder.create();

            resultRowBuilder.withContentType(scoringMetadata.getResourceType(record.getValue(RESOURCE_TYPE_ID.fieldName(), Integer.class)));
            resultRowBuilder.withHitCount(record.getValue(SUM_HIT_COUNT.fieldName(), Long.class));
            resultRowBuilder.withUrl(record.getValue(CONTENT_ID.fieldName(), String.class));
            scoringResults.add(resultRowBuilder.build());
        }
        return scoringResults;
    }

}

这篇关于jooq扩展现有的方言。采用MySQL方言来apache Hive方言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！