Hive join query to list columns from only one table

Question

I am writing a hive query to join two tables; table1 and table2. In the result I just need all columns from table1 and no columns from table2.

I know the solution where I can select all the columns manually by specifying table1.column1, table1.column2.. and so on in the select statement. But I have about 22 columns in table 1. Also, I have to do the same for multiple other tables ans its painful process.

I tried using "SELECT table1.*", but I get a parse exception.

Is there a better way to do it?

can you help me.? You said that you join two table, table1 and table2 and then you said you want all columns from table1 and no columns from table2. So why you joined ? — Ankur Singh
may be I am confused but can you help me to understand your question? — Ankur Singh
you should be able to do a table1.*, could you provide the stack ? — hlagos
@AnkurKumar.. Since Hive doesn't allow deletes, I am doing this join(and use the result to create new table) to remove entries from table1 corresponding table2. So the new table i create has rows from table 1 minus rows from table2(which match table1). I hope i made it understandable. — Abhilash Owk

Saurabh Kumar Saurabh Kumar · Accepted Answer · 2018-02-15T01:00:47

Hive 0.13 onwards the following query syntax works:

SELECT a.* FROM a JOIN b ON (a.id = b.id)

This query will select all columns from a. So instead of typing all the column names (making the query cumbersome), it is a better idea to use tablealias.*

Hive join query to list columns from only one table

1 Answers