As far as I know, there is no current plan for JDBC driver.

+1 for adding JDBC support to ES .... though not sure if JDBC4ES functionality should live under this project or be a separate project?

https://github.com/apache/incubator-calcite seems like a good tool to use for this...

+1
On 17 Jan 2015 4:41 am, "Omer Shelef" notifications@github.com wrote:

Reopened #28 #28.

—
Reply to this email directly or view it on GitHub
#28 (comment).

+1

com.alibaba.druid has JDBC built in, I believe, is is possible to use it?

+1 for jdbc support

+1

@otisg I have tried calcite to add this feature to es-sql. It's proved to be unsuitable. calcite kind like spark datasource API which provide scanTable,filterTabe,translateTable which means you cannot access original sql(Raw SQL) but some expressions . Also, this means join ,group by action are all done in calcite which really impact performance .

@ebuildy com.alibaba.druid has JDBC built in, I believe, is is possible to use it?

This is good, Have already support JDBC by rewriting some druid classes, check url bellow:

https://github.com/allwefantasy/elasticsearch-sql/tree/jdbc-support

Install Step

1. download project
2. package jar
3. add jar to your project

Example Code

public class JDBCTests {
    @Test
    public void testJDBC() throws Exception {
        Properties properties = new Properties();
        properties.put("url", "jdbc:elasticsearch://127.0.0.1:9300/twitter2");
        DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
        dds.setInitialSize(1);
        Connection connection = dds.getConnection();
        PreparedStatement ps = connection.prepareStatement("SELECT split(trim(concat_ws('dd',newtype,num_d)),'dd')[0] as nt from  twitter2");
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString("nt"));
        }

        ps.close();
        connection.close();
        dds.close();
    }

}

I run the JDBCTests, got error:

java.sql.SQLException: Error

at com.alibaba.druid.pool.ElasticSearchDruidDataSource.handleConnectionException(ElasticSearchDruidDataSource.java:1109)
at com.alibaba.druid.pool.DruidPooledConnection.handleException(DruidPooledConnection.java:127)
at com.alibaba.druid.pool.DruidPooledStatement.checkException(DruidPooledStatement.java:68)
at com.alibaba.druid.pool.ElasticSearchDruidPooledPreparedStatement.executeQuery(ElasticSearchDruidPooledPreparedStatement.java:62)
at org.nlpcn.es4sql.JDBCTests.testJDBC(JDBCTests.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Caused by: NoNodeAvailableException[None of the configured nodes are available: [{#transport#-1}{113.12.15.234}{113.12.15.234:9200}]]
at org.elasticsearch.client.transport.TransportClientNodesService.ensureNodesAreAvailable(TransportClientNodesService.java:290)
at org.elasticsearch.client.transport.TransportClientNodesService.execute(TransportClientNodesService.java:207)
at org.elasticsearch.client.transport.support.TransportProxyClient.execute(TransportProxyClient.java:55)
at org.elasticsearch.client.transport.TransportClient.doExecute(TransportClient.java:288)
at org.elasticsearch.client.support.AbstractClient.execute(AbstractClient.java:359)
at org.elasticsearch.action.ActionRequestBuilder.execute(ActionRequestBuilder.java:86)
at org.elasticsearch.action.ActionRequestBuilder.execute(ActionRequestBuilder.java:56)
at org.elasticsearch.action.ActionRequestBuilder.get(ActionRequestBuilder.java:64)
at org.nlpcn.es4sql.query.SqlElasticSearchRequestBuilder.get(SqlElasticSearchRequestBuilder.java:43)
at org.elasticsearch.plugin.nlpcn.QueryActionElasticExecutor.executeSearchAction(QueryActionElasticExecutor.java:23)
at org.elasticsearch.plugin.nlpcn.QueryActionElasticExecutor.executeAnyAction(QueryActionElasticExecutor.java:44)
at com.alibaba.druid.pool.ElasticSearchDruidPooledPreparedStatement.getObjectResult(ElasticSearchDruidPooledPreparedStatement.java:74)
at com.alibaba.druid.pool.ElasticSearchDruidPooledPreparedStatement.executeQuery(ElasticSearchDruidPooledPreparedStatement.java:47)
... 27 more

You should start a elasticsearch instance before running JDBCTests . Do not forget modify the code:

/Chagne 127.0.0.1 to your elasticsearch host.
properties.put("url", "jdbc:elasticsearch://127.0.0.1:9300/" + TestsConstants.TEST_INDEX);

Hi..I downloaded the project (latest version) and packaged it in eclipse. and then I have a test project in which I am adding the package as an external jar. and have created a test code -
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.ElasticSearchDruidDataSourceFactory;
import java.sql.*;
//import java.util.ArrayList;
//import java.util.List;
import java.util.Properties;

import org.nlpcn.es4sql.TestsConstants;

public class JDBCtests {
public static void main(String args[]) throws Exception {
try{
Properties properties = new Properties();
properties.put("url", "jdbc:elasticsearch://localhost:9300/" + TestsConstants.TEST_INDEX);
// properties.put("url", "jdbc:elasticsearch://localhost:9300/twitter2");
DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
Connection connection = dds.getConnection();
PreparedStatement ps = connection.prepareStatement("SELECT gender,lastname,age from " + TestsConstants.TEST_INDEX + " where lastname='Heath'");
//PreparedStatement ps = connection.prepareStatement("SELECT split(trim(concat_ws('dd',newtype,num_d)),'dd')[0] as nt from twitter2");
ResultSet resultSet = ps.executeQuery();
//List result = new ArrayList();
while (resultSet.next()) {
System.out.println(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"));
}
ps.close();
connection.close();
dds.close();
}
catch(SQLException e)
{
System.out.println(e.getErrorCode());
System.out.println(e.getMessage());
System.out.println(e.getSQLState());
e.printStackTrace();
}

	/*Properties properties = new Properties();
    properties.put("url", "jdbc:elasticsearch://localhost:9300/" + TestsConstants.TEST_INDEX);
    DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
    Connection connection = dds.getConnection();
    PreparedStatement ps = connection.prepareStatement("SELECT  gender,lastname,age from  " + TestsConstants.TEST_INDEX + " where lastname='Heath'");
    ResultSet resultSet = ps.executeQuery();
    //List<String> result = new ArrayList<String>();
    while (resultSet.next()) {
          System.out.println(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"));
    }
    ps.close();
    connection.close();
    dds.close();*/

}

}

I run the program and its throwing an exception showing -

ERROR StatusLogger No log4j2 configuration file found. Using default configuration: logging only errors to the console.
java.sql.SQLException: Error
at com.alibaba.druid.pool.ElasticSearchDruidDataSource.handleConnectionException(ElasticSearchDruidDataSource.java:1109)0

at com.alibaba.druid.pool.DruidPooledConnection.handleException(DruidPooledConnection.java:127)
at com.alibaba.druid.pool.DruidPooledStatement.checkException(DruidPooledStatement.java:68)
at com.alibaba.druid.pool.ElasticSearchDruidPooledPreparedStatement.executeQuery(ElasticSearchDruidPooledPreparedStatement.java:61)
at constructors.JDBCtests.main(JDBCtests.java:24)

Caused by: [elasticsearch-sql_test_index] IndexNotFoundException[no such index]
at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver$WildcardExpressionResolver.infe(IndexNameExpressionResolver.java:676)
at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver$WildcardExpressionResolver.innerResolve(IndexNameExpressionResolver.java:630)
at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver$WildcardExpressionResolver.resolve(IndexNameExpressionResolver.java:578)
at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver.concreteIndices(IndexNameExpressionResolver.java:168)
at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver.concreteIndices(IndexNameExpressionResolver.java:140)

(full trace not copied).

could you please help and why use port 9300 when we have 9200.

setInitialSize max is 8 ?