title: Pyspark: remote Hive warehouse location
body: <p>I need to read / write tables stored in remote Hive Server from Pyspark. All I know about this remote Hive is that it runs under Docker. From Hadoop Hue I have found two urls for an <code>iris</code> table that I try to select some data from:</p>
  
  <p>I have a table metastore url:</p>
  
  <pre><code>http://xxx.yyy.net:8888/metastore/table/mytest/iris
  </code></pre>
  
  <p>and table location url:</p>
  
  <pre><code>hdfs://quickstart.cloudera:8020/user/hive/warehouse/mytest.db/iris
  </code></pre>
  
  <p>I have no idea why last url contains <code>quickstart.cloudera:8020</code>. Maybe this is because Hive runs under Docker?</p>
  
  <p>Discussing access to Hive tables Pyspark tutorial writes:</p>
  
  <p><a href="https://spark.apache.org/docs/latest/sql-programming-guide.html#hive-tables" rel="nofollow noreferrer">https://spark.apache.org/docs/latest/sql-programming-guide.html#hive-tables</a></p>
  
  <blockquote>
    <p>When working with Hive, one must instantiate SparkSession with Hive support, including connectivity to a persistent Hive metastore, support for Hive serdes, and Hive user-defined functions. Users who do not have an existing Hive deployment can still enable Hive support. When not configured by the hive-site.xml, the context automatically creates metastore_db in the current directory and creates a directory configured by spark.sql.warehouse.dir, which defaults to the directory spark-warehouse in the current directory that the Spark application is started. Note that the hive.metastore.warehouse.dir property in hive-site.xml is deprecated since Spark 2.0.0. Instead, use spark.sql.warehouse.dir to specify the default location of database in warehouse. You may need to grant write privilege to the user who starts the Spark application.</p>
  </blockquote>
  
  <p>In my case <code>hive-site.xml</code> that I managed to get does not have neither <code>hive.metastore.warehouse.dir</code> nor <code>spark.sql.warehouse.dir</code> property.</p>
  
  <p>Spark tutorial suggests to use the following code to access remote Hive tables:</p>
  
  <pre><code>from os.path import expanduser, join, abspath
  
  from pyspark.sql import SparkSession
  from pyspark.sql import Row
  
     // warehouseLocation points to the default location for managed databases and tables
  val warehouseLocation = new File("spark-warehouse").getAbsolutePath
  
  spark = SparkSession \
      .builder \
      .appName("Python Spark SQL Hive integration example") \
      .config("spark.sql.warehouse.dir", warehouse_location) \
      .enableHiveSupport() \
      .getOrCreate()
  </code></pre>
  
  <p>And in my case, after running similar to the above code, but with correct value for <code>warehouseLocation</code>, I think I can then do:</p>
  
  <pre><code>spark.sql("use mytest")
  spark.sql("SELECT * FROM iris").show()
  </code></pre>
  
  <p>So where can I find remote Hive warehouse location? How to make Pyspark to work with remote Hive tables? </p>
  
  <p><strong>Update</strong></p>
  
  <p><code>hive-site.xml</code> has the following properties:</p>
  
  <pre><code>...
  ...
  ...
   &lt;property&gt;
      &lt;name&gt;javax.jdo.option.ConnectionURL&lt;/name&gt;
      &lt;value&gt;jdbc:mysql://127.0.0.1/metastore?createDatabaseIfNotExist=true&lt;/value&gt;
      &lt;description&gt;JDBC connect string for a JDBC metastore&lt;/description&gt;
    &lt;/property&gt;
  ...
  ...
  ...
    &lt;property&gt;
      &lt;name&gt;hive.metastore.uris&lt;/name&gt;
      &lt;value&gt;thrift://127.0.0.1:9083&lt;/value&gt;
      &lt;description&gt;IP address (or fully-qualified domain name) and port of the metastore host&lt;/description&gt;
    &lt;/property&gt;
  </code></pre>
  
  <p>So it looks like 127.0.0.1 is Docker localhost that runs Clouder docker app. Does not help to get to Hive warehouse at all.</p>
  
  <p>How to access Hive warehouse when Cloudera Hive runs as a Docker app.?</p>