Hue: list tables containing a specified field

#1

I’m a new Hue user and want to execute a query that displays a list of the tables within a certain database that contain a certain field name (for example, “thenameoffield”).

I tried using this form the Hue browse help type:field(column): ‘thenameoffield’, but it doesn’t work. “AnalysisException: Syntax error in line2:undefined: type:field(column): /thenameoffield’”

I also tried SELECT * FROM INFORMATION_SCHEMA.COLUMNS; , but not working either.
“AnalysisException: Syntax error in line2:undefined: EXPLAIN SELECT * FROM,…Exception: Syntax error”

#2

I also attempted to use:
SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.columns.name LIKE ‘%thenameoffield%’

but received the error message: AnalysisException: Syntax error in line 1: undefined: SELECT sys.columns.name as ColumnName,…

Any help to resolve would be appreciated

#3

I don’t have a Hive 2.3 handy with a sys DB right now, do basic queries like

SELECT sys.columns.name AS ColumnName FROM sys.columns

work?

#4

Error message received from the suggested code: AnalysisException: Syntax error in line 1:undefined: SELECT sys.columns.name As ColumnName FROM sys.columns ^Encountered: COLUMNS Expected: DEFAULT, IDENTIFIER CAUSED BY:Exception: Syntax error

#6

I also tried this:
In the Hue User Guide under Data Catalogs, it suggests entering type:field name in the search bar, but when I do this there are no results found. There are no clear examples in the documentation, but I assume “name” is the name of the field I want to search for. The documentation does not state if wildcard can be used, such as “name”. I did try this but no results were produced.

type:field(column): ‘thenameoffield’ AND type:field ‘thenameoffield’ with and without quotes, but it doesn’t work. “AnalysisException: Syntax error in line2:undefined: type:field(column): /thenameoffield’”

#7

Romain, I have the database containing all the tables of interest in the window pane on the left side, but do I need to specify the database in the test code you sent? I assumed the query would be limited to just that database.