spark ETL and spark thrift server

Question

Some details:

Spark SQL (version 3.2.1)
Driver: Hive JDBC (version 2.3.9)

ThriftCLIService: Starting ThriftBinaryCLIService on port 10000 with 5...500 worker threads

BI tool is connect via odbc driver

After activating Spark Thrift Server I'm unable to run pyspark script using spark-submit as they both use the same metastore_db

error:
Caused by: ERROR XJ040: Failed to start database 'metastore_db' with class loader org.apache.spark.sql.hive.client.IsolatedClientLoader$$anon$1@3acaa384, see the next exception for details.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 140 more
Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /tmp/metastore_db.

I need to be able to run PySpark (Spark ETL) while having spark thrift server up for BI tool queries. Any workaround for it?

Thanks!

The question should appear on top, details at the end. Please add a more descriptive title like "cannot run spark-submit while thrift server is up" :) — ggagliano, Mar 30 '22 at 21:16

score 0 · Accepted Answer · answered May 17 '22 at 14:14

In my case the solution was to move the metastore_db to a database server like MySql (in my case) or Postgresql.

You will have to configure $SPARK_HOME/conf/hive-site.xml and include your jdbc driver in $SPARK_HOME/jars path

hive-site.xml example for MySQL connection

<configuration>
  <!-- Hive Execution Parameters -->
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://XXX.XXX.XXX.XXX:3306/metastore?createDatabaseIfNotExist=true&amp;useSSL=FALSE&amp;autoReconnect=true&amp;nullCatalogMeansCurrent=true</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>YOUR_USER</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>YOUR_PASSWORD</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
  <name>hive.server2.transport.mode</name>
  <value>http</value>
</property>
<property>
  <name>hive.server2.thrift.http.port</name>
  <value>10000</value>
</property>
<property>
  <name>hive.server2.http.endpoint</name>
  <value>cliservice</value>
</property>


<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
<description/>
</property>
  <property>
    <name>datanucleus.autoCreateSchema</name>
    <value>true</value>
  </property>
  <property>
    <name>datanucleus.fixedDatastore</name>
    <value>true</value>
  </property>
  <property>
    <name>datanucleus.autoCreateTables</name>
    <value>true</value>
  </property>
  <property>
    <name>datanucleus.schema.autoCreateTables</name>
    <value>true</value>
  </property>

</configuration>

spark ETL and spark thrift server

1 Answers1