Any method to get table schema from executing hive sql?

[changzong]

Hi there, in ThriftHive interface from older hiveserver, there is a method called .getSchema().fieldSchemas, which can get table schema like column names, column types and comment from the query you just executed, and it is quite handy. So I just wonder if there are any similar methods in PyHive interface that can get metadata of your query result as well.

Thanks.

[jingw]

It looks like TCLIService exposes a GetTables method. However, Python's DB-API doesn't specify such functionality, and I'm generally hesitant to add custom non-standard features.

If you want to do this using just pyhive, the easiest way is to just issue a DESCRIBE/SHOW query.

[mdeguzis]

I was interested in this as well. I really would like to be able to show the column headers as the first row, as many other programs do. I guess some kind of hive query could work, but only if a table is detected.

[changzong]

Hi, in current PyHive version, the description method can be used for getting column names and column types. Only to note that you have to convert column type IDs to type names according to the mapping here: https://github.com/dropbox/PyHive/blob/master/TCLIService/ttypes.py

[mdeguzis]

Great! I'll work my code up to take vantage of this. If you have a simple faux example, that would save me time (e.g. 'cursor.table_column_raw')

This kind of looks like the right usage:

    @with_cursor
    def test_description(self, cursor):
        cursor.execute('SELECT * FROM one_row')

        desc = [('number_of_rows', 'INT_TYPE', None, None, None, None, True)]
        self.assertEqual(cursor.description, desc)

I count the rows when I do the fetch all with a loop, so I can match up the row id with something, if that is what you meant.

[changzong]

OK. For example, if you call:
cursor.execute('select * from sometable')
and
cursor.description()
You will get a list of dictionaries, where each dictionary contains the information of one of your columns, like:
[{'scale': None, 'name': 'sometable.col0', 'display_size': None, 'precision': None, 'type_code': 'STRING_TYPE', 'null_ok': True, 'internal_size': None}, {'scale': None, 'name': 'sometable.col1', 'display_size': None, 'precision': None, 'type_code': 'STRING_TYPE', 'null_ok': True, 'internal_size': None}, {'scale': None, 'name': 'sometable.col2', 'display_size': None, 'precision': None, 'type_code': 'INT_TYPE', 'null_ok': True, 'internal_size': None}]
which represent three columns.

You should pick up from the fields 'name' and 'type_code' to get column names and column types respectively.

However, converting the type code to real type name is necesssary, like 'INT_TYPE' to 'int', 'STRING_TYPE' to 'string'.