Return a table location (path) using SQL query (from Python code)

I am trying to return a table location (path) using a SQL query (in my Python code). Is it possible?

I’m using Hive and the data is stored in hdfs.

I found this snippet of code elsewhere:

SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

But unfortunately I don’t understand it, so I don’t even know how to customise it to my problem.

I want to use this query in my unit tests in the following helper function:

 @classmethod
    def return_table_location(schema, table) -> str:
     """ Returns the location of the data files """

        table_location_query = (***the query***)
        return table_location_query

Could anyone shed some light on this problem?

Answer

The answer lied in understanding that finding a table location is database appliance-specific.

For Hive the answer is:

DESCRIBE FORMATTED schema_name.table_name

The topic can be closed, thanks for leading me to the correct solution.

Advertisement

Answer