CODEWITHSUNDEEP
sqlfilterhive
Laura
Laura

Reputation: 330

Hive: filter a table using another table

I am very new to hive and sql and I have a question about how I would go about the following:

I have table A:

Name    id

Amy     1
Bob     4
Josh    9
Sam     6

And I want to filter it using values from another table (table B):

Value  id

.2     4
.7     6

To get a new table that looks like table A but only contains rows with values in the id column that also appeared in the id column of table B:

Name    id

Bob     4
Sam     6

So I'm assuming I would write something that started like...

CREATE TABLE Table C AS
SELECT * FROM Table A
WHERE id....

Upvotes: 0

Views: 3574

Answers (4)

Abhi
Abhi

Reputation: 6568

Create the result table 

CREATE TABLE TABLE3 (Name STRING, id INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

Then insert into the new table using Join

INSERT INTO  TABLE TABLE3 SELECT t1.Name, t1.Id from Table1 t1 
JOIN Table2 t2 WHERE t1.id = t2.id;

will give you the desired result.

Upvotes: 0

Laura
Laura

Reputation: 330

The correct syntax for the result I wanted was:

CREATE TABLE tableC AS
SELECT tableA.*
FROM tableA LEFT SEMI JOIN tableB on (tableA.id = tableB.id);

Upvotes: 0

Kishore
Kishore

Reputation: 5881

just join it..

hive> CREATE TABLE TableC AS           
    > SELECT A.* FROM TableA as A,
    > TableB as B                       
    > WHERE A.id = B.id;

hive> SELECT * FROM TableC;
OK
Bob 4
Sam 6

or try this,

hive> CREATE TABLE TableD AS           
    > SELECT A.* FROM TableA as A join
    > TableB as B                       
    > on A.id = B.id;

hive> SELECT * FROM TableD;
OK
Bob 4
Sam 6

Upvotes: 1

Vamshi
Vamshi

Reputation: 1

Two tables were created with the below columns 

CREATE TABLE TABLE_1 
( NAMES VARCHAR2(10) NOT NULL,
ID_1 NUMBER (2) NOT NULL)

CREATE TABLE TABLE_2 
( VALUES_1 VARCHAR2(10) NOT NULL,
ID_1 NUMBER (2) NOT NULL)

and inserted values in these tables

Final table should be created as 

CREATE TABLE TABLE_3 AS (
SELECT T1.NAMES,T2.ID_1 FROM TABLE_1 T1,TABLE_2 T2
WHERE T1.ID_1(+)= T2.ID_1)

Upvotes: 0

Related Questions