Temporary tables in SQL Server are prefixed with “#” and table variables with “@,” thus they can have the same name as permanent tables and there will be no problem because the prefixes can be used to distinguish them, and they are stored in a temp database.
However, the temp tables in Apache Hive and MySQL are created with the “TEMPORARY” keyword but without any prefixes, so they appear identical.
In Apache Hive, the temp tables stores in a different session and the location of the temp tables is- /tmp/hive-<username>.
Permanent Table Creation in MySQL and Apache Hive:
CREATE TABLE tempEmp(empno INT, ename STRING, sal DOUBLE);
Temporary Table Creation in MySQL and Apache Hive:
CREATE TEMPORARY TABLE tempEmp(empno INT, ename STRING, sal DOUBLE);
Note: During that session, the user cannot access the permanent table unless the temporary table is dropped or renamed.
Hope you find this article helpful.
Please subscribe for more interesting updates.