How to use Parallel SQL Interface when basic auth is enabled on Solr cluster

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to use Parallel SQL Interface when basic auth is enabled on Solr cluster

Suril Shah
Hi,
I am using Solr Version 7.6.0 where Basic Authentication is enabled. I am
trying to use Parallel SQL to run some SQL queries.

This is the code snippet that I am using to connect to Solr and run some
SQL queries on it. This works when authentication is not enabled on the
Solr cluster.

   public Connection getSolrSqlJDBCConnection(String aggregationMode)
throws SQLException
{

    String solrZkConnString = "<zk_fqdn>:2181";

    String collection = "customer";

    String numWorkers = "2";

    Connection solrSqlClientConn = null;

    try {

      solrSqlClientConn = DriverManager.*getConnection*("jdbc:solr://" +
solrZkConnString + "?collection="

          + collection + "&aggregationMode=" + aggregationMode +
"&numWorkers=" + numWorkers);



    } catch (SQLException e) {

      throw e;

    }

    return solrSqlClientConn;

  }



  public ResultSet executeSolrSqlStatement(String querySqlString, String
aggregationMode) throwsSQLException {

    try {

      Connection solrSqlJdbcConn =
getSolrSqlJDBCConnection(aggregationMode);

      Statement stmt = solrSqlJdbcConn.createStatement();

      ResultSet rs = stmt.executeQuery(querySqlString);

      solrSqlJdbcConn.close();

      return rs;

    } catch (SQLException e) {

      throw e;

    }

  }



   public void testSelectOnIndex() throws SQLException {

     String owner_id = "3cfc7734-e4b4-4c9b-b91e-44c8c5943fb0";

    String solrSqlString = "select customer_id_s, customer_name_s,
country_s, city_s, postal_code_s, address_s from customer where owner_id_s
= '"+owner_id+"'";

    System.*out*.println("solrSqlString = "+solrSqlString);

    try {

      ResultSet sqlResultSet = executeSolrSqlStatement(solrSqlString,
"map_reduce");

      while (sqlResultSet.next()) {

        System.*out*.println("--- customer_id ---" +
sqlResultSet.getString("customer_id_s"));

        System.*out*.println("--- customer_name ---" +
sqlResultSet.getString("customer_name_s"));

        System.*out*.println("--- country ---" +
sqlResultSet.getString("country_s"));

        System.*out*.println("--- city ---" +
sqlResultSet.getString("city_s"));

        System.*out*.println("--- postalcode ---" +
sqlResultSet.getString("postal_code_s"));

        System.*out*.println("--- address ---" +
sqlResultSet.getString("address_s"));

      }

    } catch (SQLException e) {

      e.printStackTrace();

    }

  }


When authentication is enabled I tried adding the username and password
JDBC connection string.

Replaced one line in the getSolrSqlJDBCConnection() method:

       solrSqlClientConn = DriverManager.*getConnection*("jdbc:solr://" +
solrZkConnString + "?collection=" + collection + "&aggregationMode=" +
aggregationMode + "&numWorkers=" + numWorkers,"<UserName>","<Password>");


The <UserName> and <Password> here will be the username password for Solr.


On making the above change, we are getting the following error:



java.sql.SQLException: java.sql.SQLException: java.io.IOException: -->
http://<Solr_IP>:8983/solr/customer_shard1_replica_n2/: An exception has
occurred on the server, refer to server log for details.

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.sql.StatementImpl.executeQueryImpl(StatementImpl.java:74)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.sql.StatementImpl.executeQuery(StatementImpl.java:111)

at io.strati.search.Test.executeSolrSqlStatement(Test.java:54)

at io.strati.search.Test.main(Test.java:20)

Caused by: java.sql.SQLException: java.io.IOException: --> http://
<Solr_IP>:8983/solr/customer_shard1_replica_n2/: An exception has occurred
on the server, refer to server log for details.

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.sql.ResultSetImpl.<init>(ResultSetImpl.java:83)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.sql.StatementImpl.executeQueryImpl(StatementImpl.java:71)

... 3 more

Caused by: java.io.IOException: --> http://
<Solr_IP>:8983/solr/customer_shard1_replica_n2/: An exception has occurred
on the server, refer to server log for details.

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.stream.SolrStream.read(SolrStream.java:218)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.stream.PushBackStream.read(PushBackStream.java:88)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.sql.ResultSetImpl.<init>(ResultSetImpl.java:73)

... 4 more

Caused by: io.strati.libs.forklift.org.noggit.JSONParser$ParseException:
JSON Parse Error: char=<,position=0 AFTER='<' BEFORE='html> <head> <meta
http-equiv="Content-'

at io.strati.libs.forklift.org.noggit.JSONParser.err(JSONParser.java:419)

at
io.strati.libs.forklift.org.noggit.JSONParser.handleNonDoubleQuoteString(JSONParser.java:775)

at io.strati.libs.forklift.org.noggit.JSONParser.next(JSONParser.java:981)

at
io.strati.libs.forklift.org.noggit.JSONParser.nextEvent(JSONParser.java:1026)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.stream.JSONTupleStream.expect(JSONTupleStream.java:97)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.stream.JSONTupleStream.advanceToDocs(JSONTupleStream.java:179)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.stream.JSONTupleStream.next(JSONTupleStream.java:77)

at
io.strati.libs.forklift.org.apache.solr.client.solrj.io.stream.SolrStream.read(SolrStream.java:187)

... 6 more


Has anyone experienced any similar issues? Any thoughts or suggestions on
this would be really helpful.


Thanks,

Suril
Reply | Threaded
Open this post in threaded view
|

Re: How to use Parallel SQL Interface when basic auth is enabled on Solr cluster

Kevin Risden-3
Pretty sure you are running into
https://issues.apache.org/jira/browse/SOLR-8213

Always looking for patches to help improve things :)

Kevin Risden


On Wed, Jul 24, 2019 at 4:50 AM Suril Shah <[hidden email]> wrote:

> Hi,
> I am using Solr Version 7.6.0 where Basic Authentication is enabled. I am
> trying to use Parallel SQL to run some SQL queries.
>
> This is the code snippet that I am using to connect to Solr and run some
> SQL queries on it. This works when authentication is not enabled on the
> Solr cluster.
>
>    public Connection getSolrSqlJDBCConnection(String aggregationMode)
> throws SQLException
> {
>
>     String solrZkConnString = "<zk_fqdn>:2181";
>
>     String collection = "customer";
>
>     String numWorkers = "2";
>
>     Connection solrSqlClientConn = null;
>
>     try {
>
>       solrSqlClientConn = DriverManager.*getConnection*("jdbc:solr://" +
> solrZkConnString + "?collection="
>
>           + collection + "&aggregationMode=" + aggregationMode +
> "&numWorkers=" + numWorkers);
>
>
>
>     } catch (SQLException e) {
>
>       throw e;
>
>     }
>
>     return solrSqlClientConn;
>
>   }
>
>
>
>   public ResultSet executeSolrSqlStatement(String querySqlString, String
> aggregationMode) throwsSQLException {
>
>     try {
>
>       Connection solrSqlJdbcConn =
> getSolrSqlJDBCConnection(aggregationMode);
>
>       Statement stmt = solrSqlJdbcConn.createStatement();
>
>       ResultSet rs = stmt.executeQuery(querySqlString);
>
>       solrSqlJdbcConn.close();
>
>       return rs;
>
>     } catch (SQLException e) {
>
>       throw e;
>
>     }
>
>   }
>
>
>
>    public void testSelectOnIndex() throws SQLException {
>
>      String owner_id = "3cfc7734-e4b4-4c9b-b91e-44c8c5943fb0";
>
>     String solrSqlString = "select customer_id_s, customer_name_s,
> country_s, city_s, postal_code_s, address_s from customer where owner_id_s
> = '"+owner_id+"'";
>
>     System.*out*.println("solrSqlString = "+solrSqlString);
>
>     try {
>
>       ResultSet sqlResultSet = executeSolrSqlStatement(solrSqlString,
> "map_reduce");
>
>       while (sqlResultSet.next()) {
>
>         System.*out*.println("--- customer_id ---" +
> sqlResultSet.getString("customer_id_s"));
>
>         System.*out*.println("--- customer_name ---" +
> sqlResultSet.getString("customer_name_s"));
>
>         System.*out*.println("--- country ---" +
> sqlResultSet.getString("country_s"));
>
>         System.*out*.println("--- city ---" +
> sqlResultSet.getString("city_s"));
>
>         System.*out*.println("--- postalcode ---" +
> sqlResultSet.getString("postal_code_s"));
>
>         System.*out*.println("--- address ---" +
> sqlResultSet.getString("address_s"));
>
>       }
>
>     } catch (SQLException e) {
>
>       e.printStackTrace();
>
>     }
>
>   }
>
>
> When authentication is enabled I tried adding the username and password
> JDBC connection string.
>
> Replaced one line in the getSolrSqlJDBCConnection() method:
>
>        solrSqlClientConn = DriverManager.*getConnection*("jdbc:solr://" +
> solrZkConnString + "?collection=" + collection + "&aggregationMode=" +
> aggregationMode + "&numWorkers=" + numWorkers,"<UserName>","<Password>");
>
>
> The <UserName> and <Password> here will be the username password for Solr.
>
>
> On making the above change, we are getting the following error:
>
>
>
> java.sql.SQLException: java.sql.SQLException: java.io.IOException: -->
> http://<Solr_IP>:8983/solr/customer_shard1_replica_n2/: An exception has
> occurred on the server, refer to server log for details.
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .sql.StatementImpl.executeQueryImpl(StatementImpl.java:74)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .sql.StatementImpl.executeQuery(StatementImpl.java:111)
>
> at io.strati.search.Test.executeSolrSqlStatement(Test.java:54)
>
> at io.strati.search.Test.main(Test.java:20)
>
> Caused by: java.sql.SQLException: java.io.IOException: --> http://
> <Solr_IP>:8983/solr/customer_shard1_replica_n2/: An exception has occurred
> on the server, refer to server log for details.
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .sql.ResultSetImpl.<init>(ResultSetImpl.java:83)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .sql.StatementImpl.executeQueryImpl(StatementImpl.java:71)
>
> ... 3 more
>
> Caused by: java.io.IOException: --> http://
> <Solr_IP>:8983/solr/customer_shard1_replica_n2/: An exception has occurred
> on the server, refer to server log for details.
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .stream.SolrStream.read(SolrStream.java:218)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .stream.PushBackStream.read(PushBackStream.java:88)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .sql.ResultSetImpl.<init>(ResultSetImpl.java:73)
>
> ... 4 more
>
> Caused by: io.strati.libs.forklift.org.noggit.JSONParser$ParseException:
> JSON Parse Error: char=<,position=0 AFTER='<' BEFORE='html> <head> <meta
> http-equiv="Content-'
>
> at io.strati.libs.forklift.org.noggit.JSONParser.err(JSONParser.java:419)
>
> at
> io.strati.libs.forklift.org
> .noggit.JSONParser.handleNonDoubleQuoteString(JSONParser.java:775)
>
> at io.strati.libs.forklift.org.noggit.JSONParser.next(JSONParser.java:981)
>
> at
> io.strati.libs.forklift.org
> .noggit.JSONParser.nextEvent(JSONParser.java:1026)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .stream.JSONTupleStream.expect(JSONTupleStream.java:97)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .stream.JSONTupleStream.advanceToDocs(JSONTupleStream.java:179)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .stream.JSONTupleStream.next(JSONTupleStream.java:77)
>
> at
> io.strati.libs.forklift.org.apache.solr.client.solrj.io
> .stream.SolrStream.read(SolrStream.java:187)
>
> ... 6 more
>
>
> Has anyone experienced any similar issues? Any thoughts or suggestions on
> this would be really helpful.
>
>
> Thanks,
>
> Suril
>