How To Adding Column Headers To Hive Result Set

Introduction to Hive Table

In the hive, the tables are consisting of columns and rows and store the related data in the table format within the same database. The table is storing the records or data in tabular format. The tables are broadly classified into two parts i.e.; external table and internal table.

The default storage location of the Table varies from the hive version. From HDP 3.0, we are using hive version 3.0 and more. The default Table location was changed from HDP 3.0 version / Hive version 3.0. The location for external hive Tabular array is "/warehouse/tablespace/external/hive/" and the location for manage Table is "/warehouse/tablespace/managed/hive".

In the older version of the hive, the default storage location of hive Table is "/apps/hive/warehouse/".

Syntax of Hive Tabular array

CREATE [TEMPORARY] [EXTERNAL] Table [IF Non EXISTS] [ database name ] table name
[( column name  data type [ COMMENT cavalcade comment], ...)] [ Comment tabular array comment] [ ROW FORMAT row format] [ STORED AS file format]

How to create Table in Hive?

As per the requirement, we can create the tables. We tin can broadly classify our table requirement in two different ways;

• Hive internal table
• Hive external table

Note: Nosotros have the hive "hql" file concept with the assist of "hql" files we can direct write the entire internal or external table DDL and directly load the data in the corresponding table.

1. Internal Table

The internal tabular array is also called a managed table and information technology is ain by "hive" only. Whenever we are creating the table without specifying the keyword "external" then the tables volition create in the default location.

If we volition drop the internal or manage table then the table DDL, metadata information, and table data will be lost. The tabular array data is available on HDFS it will also lose.  We should exist very conscientious while dropping whatsoever internal or manage the table.

DDL Code for Internal Table

create table emp.customer
(
idint,
first_name string,
last_name cord,
gender string,
company_name string,
job_title string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED Past ','
lines terminated past '\n'
location "/emp/table1"
tblproperties ("skip.header.line.count"="i");

Note: To load the data in hive internal or manage the table. We are using the "location" keyword in DDL Code. Form the same location, we take kept the CSV file and load the CSV file information in the table.

Output:

ii. External Tabular array

The best practice is to create an external tabular array. Many organizations are post-obit the same practice to create tables. It does non manage the data of the external tabular array and the table is non creating in the warehouse directory. We tin store the external tabular array information anywhere on the HDFS level.

The external tables having the facility to recover the data i.e. if nosotros will delete/drop the external table. Notwithstanding no impact on the external table data present on the HDFS. It will only driblet the metadata associated with the table.

If nosotros will drop the internal or manage table then the table DDL, metadata data, and tabular array information will be lost. The table data is available on HDFS information technology will besides lose.  We should be very careful while dropping any internal or manage the table.

DDL Lawmaking for External Table

create external table emp.sales
(
idint,
first_name string,
last_name string,
gender string,
email_id cord,
urban center string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
lines terminated past '\n'
location "/emp/sales"
tblproperties ("skip.header.line.count"="i");

Note: nosotros can directly shop the external table data on the cloud or any other remote machine in the network. It will depend on the requirement.

Output:

How to alter/alter the Tabular array?

Hither nosotros accept the facility to alter or alter the existing attributes of the Tabular array. With the help of "alter" functionality, we can change the column name, add the column, drop the column, change the cavalcade name, replace the cavalcade.

Nosotros can alter the below Table attributes

1. Alter/ rename the tablename

Syntax:

Alter TABLE [current table name] RENAME TO [new tabular array name]

Query to Change Table Name :

ALTER Table customer RENAME TO cust;

Output:

Before alter

After alter

2. Change/ add together column in the tabular array

Syntax:

Alter TABLE [current table proper name] Add together COLUMNS (column spec[, col_spec ...])

Query to add together Column :

ALTER TABLE cust ADD COLUMNS (dept STRING Comment 'Department');

Output:

Sample view of the table

Nosotros are adding a new cavalcade in the tabular array "department = dept"

3. Change/change the column name

Syntax:

ALTER TABLE [current table name] CHANGE [cavalcade proper noun][new name][new blazon]

Query to change column proper name :

ALTER Table cust CHANGE first_name name string;

Output:

Sample view of the client table.

Now we are changing the column proper noun "first_name" to "name"

How to drop the Table?

Here we tin drop or delete the table form the arrangement. When y'all will drop/delete the tabular array grade the hive database, the table entry will delete it from hive metastore.  If information technology is an internal table and so the table and data will complete delete. If it is an external tabular array and so the table entry will delete it from metastore but the data is available on HDFS Level.

Drop Internal or External Table

Syntax:

DROP TABLE [IF EXISTS] table proper noun;

Drop Query:

drop table cust;

Output:

Before drop query run

After driblet query run on "cust" table.

Conclusion

We have seen the uncut concept of "Hive Table" with the proper instance, explanation, syntax, SQL Query with different outputs. The tabular array is useful to shop the structure data. The table data is helpful for various analysis purposes like BI, reporting, helpful/piece of cake in data slicing and dicing, etc. The internal table is managed and the external tabular array is not managed past the hive. As per the requirement, nosotros can choose which blazon of tabular array we need to create.

Recommended Articles

This is a guide to Hive Table. Here we hash out the concept of "Hive Tabular array" with the proper example, caption, syntax, SQL Query. Y'all may also have a wait at the following articles to acquire more –

1. Hive Cluster By
2. Hive Versions
3. Indexes in Hive
4. Hive Data Types

Source: https://www.educba.com/hive-table/

Share this post