Hi @Avinash . Sorry for the delay in responding. I was able to do what you were attempting - please see below for explanation.
I created the 2 tables (dg_1 and dg_2) and loaded them with data. By the way they needed to use datatype string - text won't work.
create table dg_1 (id int, name string);
create table dg_2(id int, phone string);
insert into dg_1 values (1, 'Bill P');
insert into dg_2 values (1, '952-555-5555');
Now, you were attempting to create a function that processes these 2 tables and drops/inserts data into a 3rd table. Functions aren't really designed for this type of processing. Functions are User-Defined Functions (UDFs) - designed to do simple processing & return a value. Designed to be executed as part of a SQL expression. http://www.hplsql.org/create-function
You should accomplish your task with a stored procedure instead. Here is a code sample that I just run inline (for ease of development).
CREATE procedure load_dg_3
as
BEGIN
DROP TABLE if exists default.dg_join3 ;
CREATE TABLE default.dg_join3 (name string, phone string) ;
INSERT INTO TABLE default.dg_join3 SELECT a.name, b.phone from default.dg_1 a,default.dg_2 b where a.id = b.id ;
SELECT * FROM default.dg_join3 ;
print 'Completed...';
END;
execute load_dg_3;
> hplsql -f "load_dg_3"
And the output.
16/06/30 22:15:11 INFO jdbc.Utils: Supplied authorities: localhost:10000
16/06/30 22:15:11 INFO jdbc.Utils: Resolved authority: localhost:10000
Open connection: jdbc:hive2://localhost:10000 (233 ms)
Starting SQL statement
SQL statement executed successfully (x.xx sec)
Starting SQL statement
SQL statement executed successfully (x.xx sec)
Starting SQL statement
SQL statement executed successfully (x.xx sec)
Starting query
Query executed successfully (xx ms)
Bill P 952-555-5555
Completed...
That should be what you're looking for. Hope this helps.
