How to get the value of the location for a Hive table using a Spark object?

Question

I am interested in being able to retrieve the location value of a Hive table given a Spark object (SparkSession). One way to obtain this value is by parsing the output of the location via the following SQL query:

describe formatted <table name>

I was wondering if there is another way to obtain the location value without having to parse the output. An API would be great in case the output of the above command changes between Hive versions. If an external dependency is needed, which would it be? Is there some sample spark code that can obtain the location value?

score 6 · Answer 1 · edited Jul 12 '19 at 09:17

6

Here is the correct answer:

import org.apache.spark.sql.catalyst.TableIdentifier

lazy val tblMetadata = spark.sessionState.catalog.getTableMetadata(new TableIdentifier(tableName,Some(schema)))

edited Jul 12 '19 at 09:17

RaLDeN

3
1

answered May 07 '19 at 02:22

Guillaume

1,277
2
13
21

1

@GuilhermedeLazari here it is `spark._jsparkSession.sessionState().catalog().getTableMetadata(spark.sparkContext._jvm.org.apache.spark.sql.catalyst.TableIdentifier('table', spark.sparkContext._jvm.scala.Some('database'))).storage().locationUri().get()` – pltc Oct 21 '19 at 22:29
I prefer this over submitting a new spark job (describe table). – Ravindra Jun 01 '22 at 10:46

score 6 · Answer 2 · answered Oct 30 '19 at 18:26

You can also use .toDF method on desc formatted table then filter from dataframe.

DataframeAPI:

scala> :paste
spark.sql("desc formatted data_db.part_table")
.toDF //convert to dataframe will have 3 columns col_name,data_type,comment
.filter('col_name === "Location") //filter on colname
.collect()(0)(1)
.toString

Result:

String = hdfs://nn:8020/location/part_table

(or)

RDD Api:

scala> :paste
spark.sql("desc formatted data_db.part_table")
.collect()
.filter(r => r(0).equals("Location")) //filter on r(0) value
.map(r => r(1)) //get only the location
.mkString //convert as string
.split("8020")(1) //change the split based on your namenode port..etc

Result:

String = /location/part_table

Thank you for the paste mode :) – Pavan_Obj Apr 23 '20 at 16:37 — Pavan_Obj, Apr 23 '20 at 16:37

Joe J · Answer 3 · 2020-02-06T04:28:16.863

5

Here is how to do it in PySpark:

 (spark.sql("desc formatted mydb.myschema")
       .filter("col_name=='Location'")
       .collect()[0].data_type)

edited Feb 06 '20 at 04:28

answered Feb 04 '20 at 21:40

Joe J

9,985
16
68
100

Kaushal · Accepted Answer · 2019-01-06T13:30:26.330

3

First approach

You can use input_file_name with dataframe.

it will give you absolute file-path for a part file.

spark.read.table("zen.intent_master").select(input_file_name).take(1)

And then extract table path from it.

Second approach

Its more of hack you can say.

package org.apache.spark.sql.hive

import java.net.URI

import org.apache.spark.sql.catalyst.catalog.{InMemoryCatalog, SessionCatalog}
import org.apache.spark.sql.catalyst.parser.ParserInterface
import org.apache.spark.sql.internal.{SessionState, SharedState}
import org.apache.spark.sql.SparkSession

class TableDetail {
  def getTableLocation(table: String, spark: SparkSession): URI = {
    val sessionState: SessionState = spark.sessionState
    val sharedState: SharedState = spark.sharedState
    val catalog: SessionCatalog = sessionState.catalog
    val sqlParser: ParserInterface = sessionState.sqlParser
    val client = sharedState.externalCatalog match {
      case catalog: HiveExternalCatalog => catalog.client
      case _: InMemoryCatalog => throw new IllegalArgumentException("In Memory catalog doesn't " +
        "support hive client API")
    }

    val idtfr = sqlParser.parseTableIdentifier(table)

    require(catalog.tableExists(idtfr), new IllegalArgumentException(idtfr + " done not exists"))
    val rawTable = client.getTable(idtfr.database.getOrElse("default"), idtfr.table)
    rawTable.location
  }
}

edited Jan 06 '19 at 13:30

answered Jan 06 '19 at 11:50

Kaushal

3,237
3
29
48

What if the hive table doesn't have any files? How can I get the location value for it? – code Jan 06 '19 at 12:12
@codeshark I have updated answer with second approach, hope this will work in your case. – Kaushal Jan 06 '19 at 13:32
What is "input_file_name" ? – Guillaume May 06 '19 at 23:14
1

It is a spark function. You can use with `import org.apache.spark.sql.functions._` – Kaushal May 07 '19 at 07:34
You can look for documentation for more detail. https://spark.apache.org/docs/2.0.0/api/scala/index.html#org.apache.spark.sql.functions$@input_file_name():org.apache.spark.sql.Column – Kaushal May 07 '19 at 07:38
@Kaushal is it possible to fetch sizes for each of these files fetched using input_file_name – Vikas Saxena Mar 15 '21 at 06:08

score 1 · Answer 5 · answered Jun 14 '20 at 12:56

USE ExternalCatalog

scala> spark
res15: org.apache.spark.sql.SparkSession = org.apache.spark.sql.SparkSession@4eba6e1f

scala> val metastore = spark.sharedState.externalCatalog
metastore: org.apache.spark.sql.catalyst.catalog.ExternalCatalog = org.apache.spark.sql.hive.HiveExternalCatalog@24b05292

scala> val location = metastore.getTable("meta_data", "mock").location
location: java.net.URI = hdfs://10.1.5.9:4007/usr/hive/warehouse/meta_data.db/mock

Ram Ghadiyaram · Answer 6 · 2020-05-01T20:12:12.853

Use this as re-usable function in your scala project

  def getHiveTablePath(tableName: String, spark: SparkSession):String =
    {
       import org.apache.spark.sql.functions._
      val sql: String = String.format("desc formatted %s", tableName)
      val result: DataFrame = spark.sql(sql).filter(col("col_name") === "Location")
      result.show(false) // just for debug purpose
      val info: String = result.collect().mkString(",")
      val path: String = info.split(',')(1)
      path
    }

caller would be

    println(getHiveTablePath("src", spark)) // you can prefix schema if you have

Result (I executed in local so file:/ below if its hdfs hdfs:// will come):

+--------+------------------------------------+-------+
|col_name|data_type                           |comment|
+--------+--------------------------------------------+
|Location|file:/Users/hive/spark-warehouse/src|       |
+--------+------------------------------------+-------+

file:/Users/hive/spark-warehouse/src

How to get the value of the location for a Hive table using a Spark object?

6 Answers6

Linked