Create, Alter, Delete Tables in Hive

Creating a Hive table is similar like creating a table in SQL like databases. However, the Hive offers a lot of flexibility while creating tables from where to store data to which format to use store data. In this blog, we will discuss many of these options and different operations that we can perform on Hive tables.

Creating Table

Like SQL conventions, we can create a Hive table in the following way. We can use the database name prefixed with a table in create a table in that database.

Using COMMENT With Tables

Like Databases, we can also use comments to give meaningful information about table columns as well as tables while creating it. Check it below query example.

Using Table properties in Create Statement

We can also set up hive table properties as we did for databases. However, Table Properties are far more powerful. Table properties can be used to tell hive details about underlying data and can also be used to integrate hive with other databases like HBase or DyanmoDB. In the next chapters, we will learn more about table properties. For now you can use the below query to attach simple properties to the table.

Issue With IF NOT EXISTS in Create table

If we run create table command and that table is already present then Hive will throw an error. To handle this gracefully we can add IF NOT EXISTS clause as we did in the creation of databases.

But there is a catch when using IF NOT EXISTS with a hive table. If the new table has a different schema from the existing table but the same name as the existing table, Hive will not warn you. If you want to give a new schema to this table then you will manually have to delete the old table.

Listing Tables

We can use the below queries to list tables. We can specify the database name in a query to list all tables from that database (query 2). We can also use like/rlike with a regular expression to list a subset of tables.

Note that using the database name and like/rlike statement (query 2 and query3) do not work together. You can use either of one in a single query.

Describing Table

In the hive, we can use describe command to see table structure, its location as well as its table properties. We can also use DESCRIBE TABLE_NAME, DESCRIBE EXTENDED TABLE_NAME, SHOW CREATE TABLE TABLE_NAME along with DESCRIBE_FORMATTED TABLE_NAME which gives table information in the well-formatted structure.

Alter Table

When it comes to the table, Alter Table is a versatile command which we can use to do multiple useful things like changing table name, changing column data type, etc. Let us see it in action below.

Renaming Hive Table

We can use Alter table command to rename table. Below is simple example.

Adding Columns to Table

Using Alter Table command we can also change the structure of the table. The below query is adding columns to “emp” table.

Changing Order of Columns in Hive Table

We can also change the order of columns in a Table using Alter command. We can see that after running the following alter query “id” column has changed its position with “address” column.

Changing Column Datatype in Hive

We can use similar query to change column data type.

Replacing All Columns in Hive Table

There might be some cases where you want to replace all column names and their data types with new columns. We Can achieve this with the ALTER query as well.

Changing Table Properties

With ALTER query, We can add new table properties or change the existing ones. We will dive deep into table properties in future chapters. For now we can learn how to add new table property in the hive.

Dropping Table In Hive

We can drop the table in hive with simple SQL like a command. We can also add IF EXISTS clause to make sure we do not get an error if the table is not present in Hive.

Conclusion

In this chapter, we have learned basic commands for managing tables in the hive. Now we will start diving deep into Hive concepts. See you there 🙂

manage tables in hive -2

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *