井底之蛙

学而知不足,思而得远虑

Elasticsearch学习--elasticearch sql插件安装与使用

1、Elasticsearch sql

es的查询虽然功能很强大,但是查询语言(DSL)很麻烦,不管是封装json还是通过python/java的api进行封装,都不方便。而elasticsearch-SQL可以用sql查询es,对于不熟悉es的DSL的人来说,更为简便和易读。
Elasticsearch-sql支持的功能:
(1)插件式的安装
(2)SQL查询
(3)超越SQL之外的查询

(4)对JDBC方式的支持

2、Elasticsearch sql安装方法

    目前elasticsearch sql的开源地址是https://github.com/NLPchina/elasticsearch-sql,其版本与ES的版本对应,一般需要选择与安装的ES版本一致。

    (1)在线安装

       在ES的bin目录下执行如下命令:elasticsearch-plugin.bat install https://github.com/NLPchina/elasticsearch-sql/releases/download/6.1.2.0/elasticsearch-sql-6.1.2.0.zip


(2)本地安装:可以直接将elasticsearch-sql-6.1.2.0.zip下载下来并解压放到ES的plugins目录下即可完成安装

安装成功之后可以在ES的plugins目录下看到sql文件,plugins目前是ES用来安装插件使用的。

3、Elasticsearch添加索引数据

    接下来我们通过代码在ES中添加一些索引数据

public class EsClient {

	private final static int port = 9300;
	
   @SuppressWarnings("unused")
   public static void main(String [] args) throws IOException{
	   TransportClient client = client = new PreBuiltTransportClient(Settings.EMPTY).addTransportAddresses(new TransportAddress(InetAddress.getByName("localhost"),port));
	   IndexResponse response = client.prepareIndex("msg", "tweet", "1").setSource(XContentFactory.jsonBuilder()
               .startObject().field("userName", "张三1")
               .field("sendDate", new Date())
               .field("msg", "msg1")
               .endObject()).get();
	   IndexResponse response2 = client.prepareIndex("msg", "tweet", "2").setSource(XContentFactory.jsonBuilder()
               .startObject().field("userName", "张三2")
               .field("sendDate", new Date())
               .field("msg", "msg2")
               .endObject()).get();
	   IndexResponse response3 = client.prepareIndex("msg", "tweet", "3").setSource(XContentFactory.jsonBuilder()
               .startObject().field("userName", "张三3")
               .field("sendDate", new Date())
               .field("msg", "msg3")
               .endObject()).get();
	   /*  String jsonStr = "{\"name\":\"tom zhang\",\"age\":19}";
	   IndexResponse responseJson = client.prepareIndex("msg", "tweet", "1").setSource(jsonStr).get();*/
	   //DeleteResponse dResponse = client.prepareDelete("msg", "tweet", "1").execute().actionGet();
	   //System.err.println(dResponse.getResult().getLowercase());
	   GetResponse getResponse = client.prepareGet("msg", "tweet", "1").get();
	   System.err.println(getResponse.getSourceAsString());
	   client.close();
   }

}

3、Elasticsearch sql查询数据

(1)可以通过http请求通过sql进行查询 http://127.0.0.1:9200/_sql?sql=select * FROM msg    

(2)通过代码进行查询

public class EsSQL {
	
	public static void main(String [] args) throws Exception{
		Properties properties = new Properties();
		properties.put("url", "jdbc:elasticsearch://localhost:9300/");
		DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory
		        .createDataSource(properties);
		dds.setInitialSize(1);
		Connection connection = dds.getConnection();
		String sql2 = "select * FROM msg";
		PreparedStatement ps = connection.prepareStatement(sql2);
		ResultSet set=ps.executeQuery();

		 ResultSetMetaData data= set.getMetaData();

		 System.out.println( JSON.toJSONString(data));
		 int count= set.getRow();
		 
		 System.out.println(count);
		 System.err.println(data.getColumnCount());
		 while (set.next()) {
			    //sql对应输出
			    System.out.println(set.getString("msg") );

			}

		ps.close();
		connection.close();
		dds.close();
	}
}
代码githu地址

        


    

阅读更多
版权声明:http://blog.csdn.net/qq924862077/ https://blog.csdn.net/qq924862077/article/details/79949765
个人分类: Elasticsearch
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

Elasticsearch学习--elasticearch sql插件安装与使用

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭