Showing tables from specific database with Pyspark and Hive

Question

Having some databases and tables in them in Hive instance. I'd like to show tables for some specific database (let's say 3_db).

+------------------+--+
|  database_name   |
+------------------+--+
| 1_db             |
| 2_db             |
| 3_db             |
+------------------+--+

If I enter beeline from bash-nothing complex there, I just do the following:

show databases;
show tables from 3_db;

When I'm using pyspark via ipython notebeook- my cheap tricks are not working there and give me error on the second line (show tables from 3_db) instead:

sqlContext.sql('show databases').show()
sqlContext.sql('show tables from 3_db').show()

What seems to be wrong and why's the same code works in one place and don't work in another?

David דודו Markovitz · Accepted Answer · 2017-02-28T12:31:53.327

34

sqlContext.sql("show tables in 3_db").show()

edited Feb 28 '17 at 12:31

answered Feb 27 '17 at 15:20

David דודו Markovitz

42,900
6
64
88

aelesbao · Answer 2 · 2019-09-05T13:46:29.740

17

Another possibility is to use the Catalog methods:

spark = SparkSession.builder.getOrCreate()
spark.catalog.listTables("3_db")

Just be aware that in PySpark this method returns a list and in Scala, it returns a DataFrame.

edited Sep 05 '19 at 13:46

answered Aug 15 '19 at 09:29

aelesbao

503
4
14

2

SparkSession is the more "modern" way, rather than sqlContext. Pls upvote. – Marcel Flygare Jul 23 '20 at 14:32

score 7 · Answer 3 · answered Sep 14 '20 at 18:11

There are two possible ways to achieve this, but they differ a lot in terms of efficiency.

Using SQL

This is the most efficient approach:

spark_session = SparkSession.builder.getOrCreate()
spark_session.sql("show tables in db_name").show()

Using catalog.listTables()

The following is more inefficient compared to the previous approach, as it also loads tables' metadata:

spark_session = SparkSession.builder.getOrCreate()
spark_session.catalog.listTables("db_name")

Showing tables from specific database with Pyspark and Hive

3 Answers3