Hive Internal Tables Using CTAS

In addition to previous implementations of the creation of internal tables (a.k.a. managed tables), another approach for creating internal tables is using the CTAS (create-table-as-select) statement. One should be aware that until the query results are populated, the table will not be seen by other users since CTAS is atomic. In other words, other users will either see a table containing the full results of the query or will not see a table at all.

Below is an example to create a managed (internal) table using CTAS.

CREATE TABLE empinfo2 
AS
SELECT * FROM empinfo;

Please note that there is a limitation to create external tables using CTAS.
You can only create internal (managed) tables using CTAS statement.

CTAS_InternalTable_1
Now check what table the above statement created.

DESC EXTENDED empinfo2;

CTAS_InternalTable_desc

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s