驱动包地址
链接:https://pan.baidu.com/s/1Nivkvze24hRH8pXOQleCgw
提取码:gp9z
使用dremio主要原因 :
1)springboot提供了es组件,前期先使用的boot提供的es模板查询数据,但是发现组装数据很麻烦。最致命的是在分组数据统计的时候,每个查询条件就需要循环一遍数据,网上找了很多资料都是循环遍历数据这样感觉效率太低。
2)由于ELK多半用于数据统计报表展现,并不支持数据导出(有可能支持反正没有找到好的方法),所以es里的数据通过dremio的导出
dremio 还支持支持数据csv或者json导出数据 。
api连接代码如下:
public static void jdbcDremio(){ String JDBC_DRIVER = "com.dremio.jdbc.Driver"; String DB_URL = "jdbc:dremio:direct=172.16.220.135:31010";//浏览器访问端口号9047 ,但是api端口号是31010 String USER = ""; String PASS = ""; String sql =" SELECT goodsName,count(orderSn) as totalSaleNum " + " FROM vem_order_info as order_info group by goodsName order by count(orderSn) desc "; try { Class.forName(JDBC_DRIVER); Connection cc = DriverManager.getConnection(DB_URL, USER, PASS); PreparedStatement ps = cc.prepareStatement(sql); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()) { System.out.println("------------------"); System.out.println(resultSet.getObject(1)); } resultSet.close(); ps.close(); cc.close(); } catch (Exception e) { e.printStackTrace(); } }
开始使用dremio的一些小坑:
- 如上api的调用的默认端口和浏览器默认端口是不一致的,主要错误信息并没有提示有关端口号相关内容,排查也是一个难啊!
10:06:58.018 [Client-1] DEBUG cdjd.io.netty.util.Recycler - -Dio.netty.recycler.maxCapacity.default: 32768 10:06:58.018 [Client-1] DEBUG cdjd.io.netty.util.Recycler - -Dio.netty.recycler.maxSharedCapacityFactor: 2 10:06:58.018 [Client-1] DEBUG cdjd.io.netty.util.Recycler - -Dio.netty.recycler.linkCapacity: 16 10:06:58.018 [Client-1] DEBUG cdjd.io.netty.util.Recycler - -Dio.netty.recycler.ratio: 8 10:06:58.056 [Client-1] ERROR cdjd.com.dremio.exec.rpc.RpcExceptionHandler - Exception in RPC communication. Connection: null <--> null (user client). Closing connection. cdjd.io.netty.handler.codec.CorruptedFrameException: Expected to read a tag of 10 but actually received a value of 84. Happened after reading 0 message. at cdjd.com.dremio.exec.rpc.MessageDecoder.checkTag(MessageDecoder.java:209) at cdjd.com.dremio.exec.rpc.MessageDecoder.decodeMessage(MessageDecoder.java:130) at cdjd.com.dremio.exec.rpc.MessageDecoder.decode(MessageDecoder.java:103) at cdjd.com.dremio.sabot.rpc.user.UserProtobufLengthDecoder.decode(UserProtobufLengthDecoder.java:35) at cdjd.io.netty.handler.codec.ByteToMessageDecoder.decodeRemovalReentryProtection(ByteToMessageDecoder.java:491) at cdjd.io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:430) at cdjd.io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:267) at cdjd.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) at cdjd.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) at cdjd.io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) at cdjd.io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86) at cdjd.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) at cdjd.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) at cdjd.io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:335) at cdjd.io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1294) at cdjd.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:356) at cdjd.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:342) at cdjd.io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:911) at cdjd.io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:131) at cdjd.io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:645) at cdjd.io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:580) at cdjd.io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:497) at cdjd.io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:459) at cdjd.io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:131) at java.lang.Thread.run(Thread.java:748) 10:06:58.073 [Client-1] INFO cdjd.com.dremio.sabot.rpc.user.UserClient - [USER]: Channel closed null <--> null (user client) 10:06:58.074 [Client-1] DEBUG cdjd.com.dremio.exec.rpc.BasicClient - Failure while initiating handshake cdjd.com.dremio.exec.rpc.ChannelClosedException: [USER]: Channel closed null <--> null (user client) at cdjd.com.dremio.exec.rpc.RpcBus$ChannelClosedHandler.operationComplete(RpcBus.java:157)
- api前期使用了es组件之后那么导入springboot版本和使用的es组件版本冲突错误信息如下:
2019-01-22 15:42:44.308 WARN 11600 --- [ main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'elasticsearchClient' defined in class path resource [org/springframework/boot/autoconfigure/data/elasticsearch/ElasticsearchAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.elasticsearch.client.transport.TransportClient]: Factory method 'elasticsearchClient' threw exception; nested exception is java.lang.BootstrapMethodError: java.lang.NoClassDefFoundError: org/elasticsearch/common/transport/InetSocketTransportAddress 2019-01-22 15:42:44.309 INFO 11600 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Unregistering JMX-exposed beans on shutdown 2019-01-22 15:42:44.314 INFO 11600 --- [ main] o.apache.catalina.core.StandardService : Stopping service [Tomcat] Process finished with exit code 1
pom配置信息:
<groupId>org.elasticsearch.client</groupId> <artifactId>x-pack-transport</artifactId> <version>6.5.4</version> </dependency> <dependency> <groupId>org.elasticsearch.plugin</groupId> <artifactId>core</artifactId> <version>6.5.4</version> </dependency> <dependency> <groupId>org.elasticsearch</groupId> <artifactId>elasticsearch</artifactId> <version>6.5.4</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-elasticsearch</artifactId> </dependency>-->
由于后面直接使用dremio -sql ,es-sql就用不到了,直接将spring-boot-starter-data-elasticsearch 删除就好了。