HIVE - insert result of a select statement as multiple records into a hive table without overwriting existing content

Question

I have a table from following command:

CREATE TABLE treatment_costs AS SELECT * FROM 
(SELECT r.patient_id, r.transaction_date, r.paid_transaction_amount, o.dob, o.department_name, o.reason_of_visit FROM ReceiptTransactions r
LEFT OUTER JOIN OpdPatientQ o ON (r.patient_id = o.patient_id)
);

I now want to insert all records that are inserted today(on the given day) into the above table. To do this, I have written:

INSERT INTO TABLE treatment_costs SELECT * FROM
(SELECT r.patient_id, r.transaction_date, r.paid_transaction_amount, o.dob, o.department_name, o.reason_of_visit FROM ReceiptTransactions r WHERE timestamp_column = today_date
LEFT OUTER JOIN OpdPatientQ o ON (r.patient_id = o.patient_id)
);

is this the correct way to insert multiple queries into a table?

EDIT 1: For example my table treatment_costs's contents are these rows:

patient_id, transaction_date, paid_transaction_amount, dob, department_name, reason_of_visit
001 01/01/2014 30000 01/01/1985 Cardiology reason_1
002 01/01/2014 35000 01/01/1975 Cardiology reason_2
003 02/01/2014 40000 01/01/1965 Oncology   reason_3
004 02/01/2014 30000 01/01/1985 Cardiology reason_4
005 02/01/2014 20000 01/01/1975 Gynecology reason_5

and my doubt now is select statement in my insert query, which is:

SELECT * FROM
(SELECT r.patient_id, r.transaction_date, r.paid_transaction_amount, o.dob, o.department_name, o.reason_of_visit FROM ReceiptTransactions r WHERE timestamp_column = today_date
LEFT OUTER JOIN OpdPatientQ o ON (r.patient_id = o.patient_id)
);

, For example, gives below result:

patient_id, transaction_date, paid_transaction_amount, dob, department_name, reason_of_visit
011 01/01/2015 30000 01/01/1986 Cardiology reason_11
012 01/01/2015 35000 01/01/1976 Cardiology reason_21
013 02/01/2015 40000 01/01/1966 Oncology   reason_31
014 02/01/2015 30000 01/01/1986 Cardiology reason_41
015 02/01/2015 20000 01/01/1976 Gynecology reason_51

And, would the contents of my table after executing insert query be like this below?

patient_id, transaction_date, paid_transaction_amount, dob, department_name, reason_of_visit
001 01/01/2014 30000 01/01/1985 Cardiology reason_1
002 01/01/2014 35000 01/01/1975 Cardiology reason_2
003 02/01/2014 40000 01/01/1965 Oncology   reason_3
004 02/01/2014 30000 01/01/1985 Cardiology reason_4
005 02/01/2014 20000 01/01/1975 Gynecology reason_5
011 01/01/2015 30000 01/01/1986 Cardiology reason_11
012 01/01/2015 35000 01/01/1976 Cardiology reason_21
013 02/01/2015 40000 01/01/1966 Oncology   reason_31
014 02/01/2015 30000 01/01/1986 Cardiology reason_41
015 02/01/2015 20000 01/01/1976 Gynecology reason_51

You have not set the alias for `ReceiptTransactions`, `FROM ReceiptTransactions` should be `FROM ReceiptTransactions r`. — franklinsijo, Apr 14 '20 at 11:52
fixed it. so, above query is now completely correct, and will insert result of my select statement as multiple records, right.? — Naveen Reddy Marthala, Apr 14 '20 at 11:56

score 2 · Accepted Answer · answered Apr 14 '20 at 11:50

2

Excerpt from the Hive Language Manual,

INSERT INTO will append to the table or partition, keeping the existing data intact.

INSERT INTO TABLE ...

will not overwrite any data that is already present in the table. The INSERT query you have will run a MapReduce (based on the engine type) job which will write the newly generated files into the table location without deleting the existing ones.

answered Apr 14 '20 at 11:50

franklinsijo

17,784
4
45
63

so my above command has the correct syntax and will not overwrite, yes? – Naveen Reddy Marthala Apr 14 '20 at 11:55
Yes. They won't overwrite. Hive has a different statement if overwrite is required. `INSERT OVERWRITE TABLE ...` – franklinsijo Apr 14 '20 at 11:58
and it will also insert the result of my select statement as multiple records, right? – Naveen Reddy Marthala Apr 14 '20 at 11:59
Yes, if the select query produces multiple records, they will be inserted without affecting the existing data. – franklinsijo Apr 14 '20 at 12:00
I asked because i think results may be in different format than what needs to be sent for insert operation. – Naveen Reddy Marthala Apr 14 '20 at 14:18
By different format you mean? I see the columns are same in CTAS and in insert. – franklinsijo Apr 14 '20 at 14:21
Yes, your assumption is correct. if you do a plain select on the table after the insert, you would see such output. But the records will be different files under the table location. – franklinsijo Apr 15 '20 at 13:29
I couldn't understand. You are telling me that, result of insert query would be the result i gave as example? Or it would be different? – Naveen Reddy Marthala Apr 15 '20 at 13:40
It will be same as my example? – Naveen Reddy Marthala Apr 15 '20 at 13:43
How did you generate the example? – franklinsijo Apr 15 '20 at 13:48
I just made up everything. – Naveen Reddy Marthala Apr 15 '20 at 13:48
1

The only way to know whether the made up content will match the one which is going to be produced by hive is to test it directly on hive. It will be same, but if you are looking for proof, you got to let hive generate it for you. So far, your understanding is correct. – franklinsijo Apr 15 '20 at 13:51
Will you please be able to help me solve this hive issue. https://stackoverflow.com/questions/61228135/hive-make-hive-table-from-specific-columns-of-multiple-csv-files-in-hdfs – Naveen Reddy Marthala Apr 16 '20 at 05:13

HIVE - insert result of a select statement as multiple records into a hive table without overwriting existing content

1 Answers1